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

問題描述

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

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

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

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

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

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

online ddl

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

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

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

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

那么什么是online ddl呢？

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

Online DDL原理

oneline ddl大致分為3個(gè)部分：

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

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

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

no-rebuild部分由于不涉及表的重建，除創(chuàng)建添加索引，會(huì)產(chǎn)生部分二級(jí)索引的寫入操作外，其余操作均只修改元數(shù)據(jù)項(xiàng)，即只在原表路徑下產(chǎn)生.frm文件，不會(huì)申請(qǐng)row log，不會(huì)消耗過多的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.生成臨時(shí)表以及臨時(shí)表文件（.frm .ibd）
• 3.拷貝原表數(shù)據(jù)到臨時(shí)表
• 4.重命名臨時(shí)表及文件
• 5.刪除原表及文件
• 6.提交事務(wù)，釋放鎖

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

準(zhǔn)備階段：

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

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

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

執(zhí)行階段：

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

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

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

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

提交階段：

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

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

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

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

顯式online ddl參數(shù)

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

ALGORITHM有如下選項(xiàng)：

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

LOCK有如下選項(xiàng)：

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

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

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

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

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

總結(jié)分析

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

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

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

上面說到在inplace模式的online ddl操作時(shí)，會(huì)申請(qǐng)一個(gè)緩存空間，用于存放在此期間的dml操作。

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

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

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

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

最終的結(jié)論是：

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

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

最新評(píng)論