在實際工作中，經(jīng)常遇到需要給數(shù)據(jù)庫表添加索引的情況。雖然操作是由dba來執(zhí)行，但開發(fā)還是應(yīng)該了解在線添加索引會引起的性能問題。比如博主最近就遇到了線上添加索引導(dǎo)致業(yè)務(wù)報警的問題。

問題描述

出于業(yè)務(wù)需要，給一個表添加普通索引，但這個表有100個分表，因此需要給100個分表都加上。

平均每張表大概有500萬行的數(shù)據(jù)量。線上業(yè)務(wù)流量也比較高。mysql版本為5.7版本，在線添加索引問題不大。

跟dba商量好之后，提交sql腳本由dba實施。經(jīng)實踐發(fā)現(xiàn)，平均一張表添加索引大概需要3~5分鐘。在添加完20多張表之后，發(fā)現(xiàn)業(yè)務(wù)開始報警。查看業(yè)務(wù)日志，發(fā)現(xiàn)有不少接口超時。

根據(jù)經(jīng)驗判斷，應(yīng)該是服務(wù)端TCP連接滿了，查看監(jiān)控信息，果然如此，直接使用該數(shù)據(jù)庫表的服務(wù)的tcp連接溢出。很顯然，應(yīng)該是數(shù)據(jù)庫操作變慢導(dǎo)致接口響應(yīng)時間增加，從而導(dǎo)致吞吐量降低，而業(yè)務(wù)流量保持不變的情況下，大量請求造成堆積，進而導(dǎo)致TCP連接被占滿甚至溢出。

同時，即使沒有使用該數(shù)據(jù)庫表，使用其他庫的服務(wù)也出現(xiàn)同樣的問題。原因在于兩者使用的數(shù)據(jù)庫在同一個實例上，添加索引導(dǎo)致數(shù)據(jù)庫服務(wù)器負載增加(實際增加不算多，正常來說應(yīng)該不影響?)，影響到了其他庫。

這時候只能讓dba將索引添加操作暫停，等待晚上12點之后流量降低再執(zhí)行。晚上執(zhí)行時不再報警。

online ddl

通常情況下，對數(shù)據(jù)量大的表進行ddl操作時，一般都會選在流量低的時候進行。

但是在mysql5.6之后，引入了一些新的特性，支持DDL執(zhí)行期間DML語句的并行操作，提高了數(shù)據(jù)庫的吞吐量。

據(jù)mysql官方文檔介紹，online ddl操作不會加鎖，很快就能完成操作。

正是基于這一點考慮，所以才直接在白天加。結(jié)果就出現(xiàn)了上面的問題。

那么什么是online ddl呢？

其結(jié)構(gòu)圖如下：

Online DDL原理

oneline ddl大致分為3個部分：

• copy（ALGORITHM=COPY）這部分是offline的，ddl執(zhí)行時會阻塞dml,中間需要臨時表的中轉(zhuǎn)。這也是5.6版本前的DDL執(zhí)行方法。在innodb中不支持使用inplace的操作都會自動使用copy方式執(zhí)行，而MyISAM表只能使用copy方式。
• inplace（ALGORITHM=INPLACE）所有操作在innodb引擎層完成，不需要經(jīng)過臨時表的中轉(zhuǎn)。除上圖兩種特殊索引創(chuàng)建外，其他以inplace方式執(zhí)行的操作都是online的，執(zhí)行期間其他DML操作可以并行，其中又以是否重建表又分為兩個部分rebuild和no-rebuild。

rebuild部分涉及表的重建，在原表路徑下創(chuàng)建新的.frm和.ibd文件，消耗的IO會較多。

期間（原表可以修改）會申請row log空間記錄DDL執(zhí)行期間的DML操作，這部分操作會在DDL提交階段應(yīng)用新的表空間中。

no-rebuild部分由于不涉及表的重建，除創(chuàng)建添加索引，會產(chǎn)生部分二級索引的寫入操作外，其余操作均只修改元數(shù)據(jù)項，即只在原表路徑下產(chǎn)生.frm文件，不會申請row log，不會消耗過多的IO，速度通常很快。

• inplace but offline的幾種特殊DDL操作，本身是按inplace方式執(zhí)行，但是執(zhí)行期間DML語句卻不能并行。

如何區(qū)分DDL語句是使用了copy方式還是inplace方式，只需要查看語句執(zhí)行完成輸出結(jié)果中的 X rows

affected，如果X為0則是inplace（online）方式，如果不為0則是copy（offline）方式。

copy的整體執(zhí)行過程如下：

• 1.鎖表，期間DML不可并行執(zhí)行
• 2.生成臨時表以及臨時表文件（.frm .ibd）
• 3.拷貝原表數(shù)據(jù)到臨時表
• 4.重命名臨時表及文件
• 5.刪除原表及文件
• 6.提交事務(wù)，釋放鎖

inplace(rebuild)的整體執(zhí)行過程如下：

準備階段：

1.對表加元數(shù)據(jù)共享升級鎖，并升級為排他鎖（此時DML不能并行）

2.在原表所在的路徑下創(chuàng)建.frm和.ibd臨時中轉(zhuǎn)文件（no-rebuild除創(chuàng)建二級索引外只創(chuàng)建.frm文件，其中添加二級索引操作最為特殊，該操作屬于no-rebuild不會生成.ibd，但實際上對.ibd文件卻做了修改，該操作會在參數(shù)tmpdir指定路徑下生成臨時文件，用于存儲索引排序結(jié)果，然后再合并到.ibd文件中）

3.申請row log空間，用于存放DDL執(zhí)行階段產(chǎn)生的DML操作（no-rebuild不需要）

執(zhí)行階段：

1.釋放排他鎖，保留元數(shù)據(jù)共享升級鎖（此時DML可以并行）

2.掃描原表主鍵以及二級索引的所有數(shù)據(jù)頁，生成 B+ 樹，存儲到臨時文件中

3.將所有對原表的DML操作記錄在日志文件row log中

如果只修改元數(shù)據(jù)部分（no-rebuild），該階段只是修改.frm文件，不需要其他操作，也不需要申請row log

提交階段：

1.升級元數(shù)據(jù)共享升級鎖，產(chǎn)生排他鎖鎖表（此時DML不能并行）。

2.重做row log中的內(nèi)容。（no-rebuild不需要）

3.重命名原表文件，將臨時文件改名為原表文件名，刪除原表文件

4.提交事務(wù)，變更完成。

顯式online ddl參數(shù)

可以在執(zhí)行online DDL語句的時候，使用ALGORITHM和LOCK關(guān)鍵字，這兩個關(guān)鍵字在DDL語句的最后面，用逗號隔開。

ALGORITHM有如下選項：

• INPLACE：直接在原表上面執(zhí)行DDL的操作。
• COPY：使用臨時表。這期間需要多出一倍的磁盤空間來支撐這樣的 操作。執(zhí)行期間，表不允許DML的操作。
• DEFAULT：默認方式，由MySQL自己選擇，優(yōu)先使用INPLACE的方式。

LOCK有如下選項：

• SHARE：共享鎖，執(zhí)行DDL的表可以讀，但是不可以寫。
• NONE：沒有任何限制，執(zhí)行DDL的表可讀可寫。
• EXCLUSIVE：排它鎖，執(zhí)行DDL的表不可以讀，也不可以寫。
• DEFAULT：默認值，也就是在DDL語句中不指定LOCK子句的時候使用的默認值，由MySQL自動判斷，優(yōu)先使用NONE的方式。

例句如下，參數(shù)間使用逗號隔開：

alter table test add col int,ALGORITHM=INPLACE,LOCK=DEFAULT;

執(zhí)行DDL操作時，顯式參數(shù)可以不指定，mysql會自動選擇合適的方式去執(zhí)行，優(yōu)先使用inplace，none的方式，效果與指定ALGORITHM=DEFAULT,LOCK=DEFAULT一樣。

但是如果顯式指定了這兩個參數(shù)，則必須按照指定的方式來執(zhí)行，不支持的話則直接報錯。

總結(jié)分析

從上面的內(nèi)容來看，online ddl添加索引正常應(yīng)該是很快的，但并不是完全不會加鎖。

在對表元數(shù)據(jù)加互斥鎖的時候，會阻塞dml操作。

但從整體來看，這個時間應(yīng)該是極短的，所以官方才有online ddl操作不會加鎖的說法。

上面說到在inplace模式的online ddl操作時，會申請一個緩存空間，用于存放在此期間的dml操作。

這個緩存大小由參數(shù)innodb_online_alter_log_max_size控制，默認為128mb，支持動態(tài)修改。

如果更新的表比較大，并且在ddl過程中有大量的寫操作，就可能遇到空間不足的情況，會拋出相應(yīng)的錯誤。

另外，如果ddl操作的目標表上有未結(jié)束的事務(wù)或者有鎖沒有釋放，那么在加元數(shù)據(jù)獨占鎖(mdl)時就會等待前面的鎖釋放，這個時候的狀態(tài)為：waiting for table metadata lock。

又因為獨占鎖的優(yōu)先權(quán)限，后面的DML操作都要排隊等待。從而導(dǎo)致db操作阻塞。

最終的結(jié)論是：

• 如果表數(shù)據(jù)量較小，或者加索引的表數(shù)量較少，online ddl操作是可以接受的，建議顯式指定algorithm和lock參數(shù)。
• 但如果數(shù)據(jù)量較大，或者加索引的表比較多，那么就需要充分考慮上面說到的問題，最好在業(yè)務(wù)流量低的時候執(zhí)行。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論