MySQL和Oracle的新增欄位的處理差別

昨天在微信群中有個朋友也是無意中問了一下，說資料庫中的表欄位想保持一種相對規範的順序，怎麼辦？要知道Oracle中這個操作就比較糾結了，因為是按照追加的方式來處理的。沒法在已有的欄位1，欄位2中間新增一個欄位3。但是MySQL卻可以，這個方面MySQL看起來要靈活的多，這個是什麼原因呢，他們在設計上有什麼差別呢。
MySQL中對每個表存在一個定義檔案，即frm檔案，我們來取出一個表，看看能不能簡單解析一下。
比如一個表欄位的內容如下：
> desc zd_warshrine_prostate;
+----------+--------------+------+-----+-------------------+----------------+
| Field    | Type         | Null | Key | Default           | Extra          |
+----------+--------------+------+-----+-------------------+----------------+
| id       | int(10)      | NO   | PRI | NULL              | auto_increment |
| proName  | varchar(100) | NO   | MUL | NULL              |                |
| TYPE     | varchar(10)  | NO   |     | NULL              |                |
| loaderr  | int(11)      | NO   |     | 0                 |                |
| loadTime | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+----------+--------------+------+-----+-------------------+----------------+
我們可以使用strings來簡單解析一下，可以通過上面的內容能夠讀到一些資訊。
# strings  zd_warshrine_prostate.frm
PRIMARY
in_ty_zyl_proName
InnoDB
)                                        
proName
TYPE
loaderr
loadTime
proName
TYPE
loaderr
loadTime
大體能夠看出，只解析出來了欄位名。而檢視MySQL中的資料字典columns，卻壓根看不到column_id這樣的欄位。

在MySQL要實現新增欄位的順序性，語句可以這樣寫：
ALTER TABLE test
    ADD COLUMN `amount_sum`  double(255,0) AFTER `amount_name`;
即在欄位amount_name後新增欄位amount_sum
難道是MySQL中的這種方式技高一籌,也不是了，對於新增欄位，修改資料型別這類的操作，MySQL在早期版本也是飽受詬病，因為會直接鎖表，而且實現起來的思路其實就是複製表資料，類似於重建。這個情況在後來的一些版本比如5.6有了一些改善，有了pt-osc的工具，這個改進可以線上修改了。而實現方式其實有點類似於Oracle中的線上重定義，MySQL中會建立一個臨時表，然後建立2個觸發器，然後同步資料到臨時表，然後觸發器同步操作。如果表資料不大，倒還不是什麼大問題，一旦資料量級上來了，業務關注度上來了，這個地方就值得好好挖掘挖掘。
Oracle中是怎麼做的呢。看起來還是有不小的差別。
比如我們檢視一個表users的資料。
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) as row#,a.* from test.USERS a where rownum<2;
     FILE#     BLOCK#       ROW#     USERID USER_NAME
---------- ---------- ---------- ---------- --------------------
        24    1569619          0       1278 user1278
通過上面的輸出可以看到是在24號檔案，資料塊1569619中，資料資訊也一併輸出出來了，這個表含有兩個欄位，userid,user_name；
那麼資料是如何儲存的呢。我們做一個dump
alter system dump datafile 24 block 1569619;        
為了圖省事，可以直接檢視select *from v$diag_info;得到trace檔案的路徑。
/U01/app/oracle/diag/rdbms/mbionline/mbionline/trace/mbionline_ora_15752.trc
我們輸出幾行trace檔案的內容，可以看到欄位都是存在一個column_id的字樣，即col 0,col 1這樣的。
tab 0, row 56, @0x134a
tl: 16 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 3]  c2 0e 23
col  1: [ 8]  75 73 65 72 31 33 33 34
tab 0, row 57, @0x135a
tl: 16 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 3]  c2 0e 24
col  1: [ 8]  75 73 65 72 31 33 33 35
tab 0, row 58, @0x136a
tl: 16 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 3]  c2 0e 25
col  1: [ 8]  75 73 65 72 31 33 33 36

Oracle中是按照這個column_id來進行欄位的順序管理的。關於欄位有兩個很相似的資料字典dba_tab_cols,tab_tab_columns。
裡面很重要的一個屬性就是column_id，同時也能夠看出還有資料型別為Long的欄位 DATA_DEFAULT ，這也算是Oracle為此問題付出的一個代價，為了保持相容性，這個long型別的欄位到了12c依舊是如此。
SQL> desc dba_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(60)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
而如果對一個大表新增欄位，如果涉及預設值，那就工作就很難了，除此之外新增欄位方面，Oracle處理起來還是要好很多，至少不會重建表資料。這也算是兩者在設計上的一些差別吧。而對於Oracle可以有不少的診斷方式，對於MySQL似乎方式和手段就少了一些，不過也有幾種方式，
比如驗證MySQL對於新增欄位，修改資料型別，可以檢視show processlist，找到一個執行緒會標示copy to tmp table
而同時在資料目錄下會建立兩個臨時檔案，類似下面的形式。
-rw-rw---- 1 mysql mysql        8860 Nov  4 19:15 #sql-2721_17a3a9.frm
-rw-rw---- 1 mysql mysql   549453824 Nov  4 19:16 #sql-2721_17a3a9.ibd
而更進一步想看到更多的內容，那就是原始碼了，其實還好了，已經看到有些牛人在解析這部分的內容了，不過我得自己讀一讀，消化一下，才能拿出來。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2127865/，如需轉載，請註明出處，否則將追究法律責任。