我就想加個索引，怎么就這么難？

?

領(lǐng)導讓我SQL優(yōu)化，我直接把服務(wù)干掛了...

?

前言

MySQL大表加字段或者加索引，是有一定風險的。

大公司一般有DBA，會幫助開發(fā)解決這個痛點，可是DBA是怎么做的呢？

小公司沒有DBA，作為開發(fā)我們的責任就更大了。那么我們怎么才能安全的加個索引呢？

今天，我們通過模擬案例以及原理分析，去弄清楚MySQL中DDL的風險，以及如何避免事故發(fā)生。

準備

軟件以及項目

1. 安裝本地版本MySQL。
2. 一個簡單的增刪改查項目。
3. 使用JMeter進行并發(fā)請求測試。

創(chuàng)建表

#?如果存在user表則刪除
DROP?TABLE??IF?EXISTS?user;

#?創(chuàng)建user表
CREATE?TABLE?`user`?(
??`id`?bigint?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??`name`?varchar(10)?DEFAULT?NULL?COMMENT?'姓名',
??`age`?int(2)?DEFAULT?NULL?COMMENT?'年齡',
??`address`?varchar(30)?DEFAULT?NULL?COMMENT?'地址',
??`description`?varchar(100)?DEFAULT?NULL?COMMENT?'描述',
??`test_id`?bigint?DEFAULT?NULL?COMMENT?'測試?id',
??`create_time`?timestamp?NULL?DEFAULT?NULL?COMMENT?'創(chuàng)建時間',
??`modify_time`?timestamp?NULL?DEFAULT?NULL?COMMENT?'修改時間',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COMMENT='mysql?ddl測試表';

創(chuàng)建存儲過程

#?如果存在test存儲過程則刪除
DROP?PROCEDURE?IF?EXISTS?`test`;

#?創(chuàng)建無參存儲過程，名稱為test
CREATE?PROCEDURE?test()

BEGIN
????#?聲明變量
????DECLARE?i?INT;
????#?變量賦值
????SET?i?=?0;
????#?結(jié)束循環(huán)的條件:?當i等于100萬時跳出while循環(huán)
????WHILE?i?DO
????#?往t_test表添加數(shù)據(jù)
????INSERT?INTO?`test`.user?(`name`,?`age`,?`address`,?
?????????????????????????????`description`,?`test_id`,?`create_time`,?`modify_time`)
????VALUES?('iisheng',?26,?'北京',?'如逆水行舟',?LAST_INSERT_ID()?+?1,?
????????????'2020-05-17?16:01:44',?'2020-05-17?16:01:51');

????#?循環(huán)一次,?i加1
????SET?i?=?i?+?1;
????#?結(jié)束while循環(huán)
????END?WHILE;

END

?

下面的創(chuàng)建存儲過程語句，是在IDE內(nèi)選擇代碼塊執(zhí)行的，如果在Terminal中執(zhí)行，需要使用DELIMITER關(guān)鍵字，更改語句結(jié)束標志。

?

調(diào)用存儲過程，生成百萬數(shù)據(jù)

CALL?test();

開啟慢SQL日志

#?查看MySQL是否開啟慢日志記錄
SHOW?VARIABLES?LIKE?'slow_query_log';

#?開啟慢SQL日志記錄
SET?GLOBAL?slow_query_log?=?'ON';

#?查看慢SQL日志位置
SHOW?VARIABLES?LIKE?'slow_query_log_file';

#?查看執(zhí)行多久的SQL才算慢SQL
SHOW?VARIABLES?LIKE?'long_query_time';

#?設(shè)置慢SQL執(zhí)行時間?只有新session才生效
SET?GLOBAL?long_query_time?=?1;

?

通常情況下這些會在MySQL的配置文件中配置，啟動時生效。

?

幾個有用的SQL語句

#?展示哪些線程正在運行
SHOW?PROCESSLIST;

#?查看正在執(zhí)行的事務(wù)
SELECT?*?FROM?information_schema.INNODB_TRX;

#?查看正在鎖的事務(wù)
SELECT?*?FROM?information_schema.INNODB_LOCKS;

#?查看正在等待鎖的事務(wù)
SELECT?*?FROM?information_schema.INNODB_LOCK_WAITS;

#?顯示innodb存儲引擎狀態(tài)的大量信息，包含死鎖日志
SHOW?ENGINE?INNODB?STATUS?;

#?展示數(shù)據(jù)庫最大連接數(shù)的配置
SHOW?VARIABLES?LIKE?'max_connections';

#?查看存在哪些觸發(fā)器
SELECT?*?FROM?information_schema.TRIGGERS;

#?查看MySQL版本
SELECT?VERSION();

?

后面我們會主要用前兩條。

?

事故現(xiàn)場

說明

1. 我創(chuàng)建的user表除了主鍵是沒有其他索引的。
2. 測試的user表數(shù)據(jù)量為一百萬。
3. 測試MySQL版本為5.7.28。
4. 測試項目的邏輯：隨機get()、list()、update()、create()，每個操作都開啟事務(wù)，并且休眠500毫秒。

步驟

?

運行測試項目

?

這里我們可以看到，項目已經(jīng)正常啟動了。

?

postman調(diào)用一下接口

?

這里我們隨便測試一個接口，請求時間2秒左右。

?

執(zhí)行JMeter的Test Plan，觀察項目日志

?

這里我們創(chuàng)建了四個線程組，每個線程組調(diào)用一個我們的接口。模擬10個人循環(huán)1000次的訪問。

這里我們看到該請求頻率下，日志無異常。

?

慢SQL日志

?

這里我們看到，百萬級的SQL，如果沒加索引SQL執(zhí)行時間還是比較長的，有的已經(jīng)達到了2s。

?

加個索引，再觀察項目日志

?

這里我們看到，項目已經(jīng)開始報錯了，大量的Connection is not available, request timed out after 30001ms。

?

SHOW PROCESSLIST一下

?

這里我們看到，有大量的Waiting for table metadata lock。

?

postman再次調(diào)用一下接口

?

這個時候，調(diào)用接口已經(jīng)報錯了，響應時間也比較久。此時，服務(wù)對用戶來說，已經(jīng)基本不可用了。

為什么會這樣？

?

我就想加個索引，怎么就這么難？

?

看吧，就因為我加了個索引，服務(wù)就掛了，我沒加之前還是好好的。遇到問題，我們要冷靜，不是我們的鍋堅決不能背，真的是我們的問題，下次一定要記得改正。那么，此刻的服務(wù)為什么就不可用了呢？

首先我們要知道，在InnoDB事務(wù)中，鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時才釋放。這個就是兩階段鎖協(xié)議。

然后，在MySQL5.5版本中引入了MDL(Metadata Lock)，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對表做結(jié)構(gòu)變更操作的時候，加MDL寫鎖。

我們可以簡單的嘗試一下下面的情況。

Session A開啟一個事務(wù)，執(zhí)行了一個簡單的查詢語句。此時，Session B，執(zhí)行另一個查詢語句，可以成功。接著，Session C執(zhí)行了一個DDL操作，加了個字段，因為Session A的事務(wù)沒有提交，而且Session A持有MDL讀鎖，Session C獲取不到MDL寫鎖，所以Session C堵塞等待MDL寫鎖。又由于MDL寫鎖獲取優(yōu)先級高于MDL讀鎖，因此Session D這個時候也獲取不到MDL讀鎖，等待Session C獲取到MDL寫鎖之后它才能獲取到MDL讀鎖。

我們發(fā)現(xiàn)，DDL操作之前如果存在長事務(wù)，一直不提交，DDL操作就會一直被堵塞，還會間接的影響后面其他的查詢，導致所有的查詢都被堵塞。

這也就是為什么我們把服務(wù)干掛的原因了。

目前主流解決方案

針對上面出現(xiàn)的情況，我們怎么解決呢？

MySQL5.6的Online DDL

MySQL從5.6開始，支持Online DDL。類似于這種的語句ALTER TABLE user ADD INDEX idx_test_id (test_id), ALGORITHM=INPLACE, LOCK=NONE在普通的ALTER TABLE或者CREATE INDEX語句后面添加ALGORITHM參數(shù)和LOCK參數(shù)。

?

實際上，ALTERT TABLE語句如果不加ALGORITHM參數(shù)，默認就會選擇ALGORITHM=INPLACE的形式，如果執(zhí)行的語句支持INPLACE，否則，會使用ALGORITHM=COPY。

?

以前寫SQL只會ALTER TABLE不知道后面還可以加ALGORITHM參數(shù)，后來知道了Online DDL，知道了可以加ALGORITHM=INPLACE，結(jié)果兩種寫法有的時候是一樣的...

這里順便提一句，學習的途徑有很多，但是官網(wǎng)，的確可以多看看。

使用pt-online-schema-change

?

簡單說一下怎么安裝這個東西

?

首先官網(wǎng)下載，然后校驗以及安裝，執(zhí)行下面命令

perl?Makefile.PL
make
make?install

然后使用CPAN安裝相關(guān)依賴（適用Unix），CentOS下直接yum更簡單

perl?-MCPAN?-e?shell
cpan>?install?DBI
cpan>?install?DBD::mysql

?

我自己Mac安裝沒啥問題，公司Mac安裝失敗了，然后升級了一下Perl版本就可以了。

?

語法

pt-online-schema-change?--charset=utf8?--no-check-replication-filters?--no-version-check?--user=user?--password=pass?--host=host_addr??P=3306,D=database,t=table?--alter?"ADD?INDEX?idx_name(field_name)"?--execute

我的腳本添加索引

pt-online-schema-change?--charset=utf8?--no-check-replication-filters?--no-version-check?--user=root?--password=mGy6GAzdawFPTJ7R?--host=127.0.0.1??P=3306,D=test,t=user?--alter?"add?INDEX?idx_test_id(test_id)"?--execute

使用pt-osc測試

這里我們看到，pt-osc創(chuàng)建觸發(fā)器的時候卡在那了。實際上這里也是在等待鎖。

最終成功了，但是整個過程時間比較久。過程中我們也發(fā)現(xiàn)了一些死鎖的日志。

其實，這個跟我的代碼有一定的關(guān)系，我的測試代碼隨機數(shù)生成的范圍是[0, 20000]，然后我根據(jù)生成的隨機數(shù)，去查詢數(shù)據(jù)庫，鎖的沖突會比較多。把范圍修改為[0, 1000000]會好很多。

再看Online DDL

因為剛才我們發(fā)現(xiàn)了，自己代碼寫的有一些問題，所以我們剛才的結(jié)論也有一些影響。我們把隨機數(shù)的范圍改到100萬，重新測試一遍。

這次Online DDL也成功了。但是也是有一些連接超時的日志。之前的測試如果一直執(zhí)行下去，也會成功，只不過堵塞時間太長，對用戶影響太大，我就停止算執(zhí)行失敗了。

?

實際效果跟機器性能也是有一些關(guān)系的，這里的關(guān)鍵點在于拿MDL寫鎖的等待時間，這個時間稍微久一些就會對用戶造成很大的影響。

?

pt-osc執(zhí)行過程

1. 創(chuàng)建一個和原表表結(jié)構(gòu)一樣的臨時表(_tablename_new)，執(zhí)行alter修改臨時表表結(jié)構(gòu)。
2. 在原表上創(chuàng)建3個與insert delete update對應的觸發(fā)器，用于copy數(shù)據(jù)的過程中，在原表的更新操作，更新到新表。
3. 從原表拷貝數(shù)據(jù)到臨時表，拷貝過程中在原表進行的寫操作都會更新到新建的臨時表。
4. rename原數(shù)據(jù)表為old表，把新表rename為原表名，并將old表刪除。
5. 刪除觸發(fā)器。

這里面創(chuàng)建、刪除觸發(fā)器和rename表的時候都會嘗試獲取DML寫鎖，如果獲取不到會等待。就是我們看到的Waiting for table metadata lock。

所以，這些時間段如果長時間獲取不到鎖，就會一直堵塞，還是會出現(xiàn)問題的。

Online DDL執(zhí)行過程

1. 拿MDL寫鎖
2. 降級成MDL讀鎖
3. 真正做DDL
4. 升級成MDL寫鎖
5. 釋放MDL鎖

1、4如果沒有鎖沖突，執(zhí)行時間非常短。第3步占用了DDL絕大部分時間，這期間這個表可以正常讀寫數(shù)據(jù)，因此稱為online。

但是，如果拿鎖的時候沒拿到，或者升級MDL寫鎖不能成功，就會等待，我們又會看到Waiting for table metadata lock，然后就接著的一系列問題了。

總結(jié)

加個索引，說難也難，說不難也不難。如果數(shù)據(jù)量大，又存在長事務(wù)，加索引的過程又有用戶訪問，Online DDL和pt-osc都不能保證對業(yè)務(wù)沒有影響。但是如果我們SQL的執(zhí)行時間比較短，或者我們加索引的時候，對應的業(yè)務(wù)沒有多少請求。那么我們就可以很快的加完索引。

加字段也是類似的過程，但是如果我們能保證沒有慢SQL，那么就不會存在長事務(wù)，那么執(zhí)行時間就會很快，對用戶就可以做到幾乎沒有影響。至于選擇Online DDL還是pt-osc就要看他們的一些限制以及自己的場景需求了。感興趣的同學，自己嘗試一下。

最后想說

當萬丈高樓崩塌的時候，超人也不能將它復原。我們應該做的，是有一個好的規(guī)范，好的認知，好的監(jiān)控，在問題沒有出現(xiàn)的時候，就將問題扼殺在搖籃中。而不是讓問題，日漸壯大，大到覆水難收...

_{參考文獻：
[1]:《MySQL實戰(zhàn)45講》
[2]: https://dev.mysql.com/doc/refman/5.7/en/
[3]: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html}