老大，我就想加个索引，怎么就这么难？
領導讓我SQL優化，我直接把服務干掛了...
?前言
MySQL大表加字段或者加索引，是有一定風險的。
大公司一般有DBA，會幫助開發解決這個痛點，可是DBA是怎么做的呢？
小公司沒有DBA，作為開發我們的責任就更大了。那么我們怎么才能安全的加個索引呢？
今天，我們通過模擬案例以及原理分析，去弄清楚MySQL中DDL的風險，以及如何避免事故發生。
準備
軟件以及項目
安裝本地版本MySQL。
一個簡單的增刪改查項目。
使用JMeter進行并發請求測試。
創建表
#?如果存在user表則刪除 DROP?TABLE??IF?EXISTS?user;#?創建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?'創建時間',`modify_time`?timestamp?NULL?DEFAULT?NULL?COMMENT?'修改時間',PRIMARY?KEY?(`id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COMMENT='mysql?ddl測試表';創建存儲過程
#?如果存在test存儲過程則刪除 DROP?PROCEDURE?IF?EXISTS?`test`;#?創建無參存儲過程，名稱為test CREATE?PROCEDURE?test()BEGIN#?聲明變量DECLARE?i?INT;#?變量賦值SET?i?=?0;#?結束循環的條件:?當i等于100萬時跳出while循環WHILE?i?<?1000000?DO#?往t_test表添加數據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');#?循環一次,?i加1SET?i?=?i?+?1;#?結束while循環END?WHILE;END ?下面的創建存儲過程語句，是在IDE內選擇代碼塊執行的，如果在Terminal中執行，需要使用DELIMITER關鍵字，更改語句結束標志。
?調用存儲過程，生成百萬數據
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';#?查看執行多久的SQL才算慢SQL SHOW?VARIABLES?LIKE?'long_query_time';#?設置慢SQL執行時間?只有新session才生效 SET?GLOBAL?long_query_time?=?1; ?通常情況下這些會在MySQL的配置文件中配置，啟動時生效。
?幾個有用的SQL語句
#?展示哪些線程正在運行 SHOW?PROCESSLIST;#?查看正在執行的事務 SELECT?*?FROM?information_schema.INNODB_TRX;#?查看正在鎖的事務 SELECT?*?FROM?information_schema.INNODB_LOCKS;#?查看正在等待鎖的事務 SELECT?*?FROM?information_schema.INNODB_LOCK_WAITS;#?顯示innodb存儲引擎狀態的大量信息，包含死鎖日志 SHOW?ENGINE?INNODB?STATUS?;#?展示數據庫最大連接數的配置 SHOW?VARIABLES?LIKE?'max_connections';#?查看存在哪些觸發器 SELECT?*?FROM?information_schema.TRIGGERS;#?查看MySQL版本 SELECT?VERSION(); ?后面我們會主要用前兩條。
?事故現場
說明
我創建的user表除了主鍵是沒有其他索引的。
測試的user表數據量為一百萬。
測試MySQL版本為5.7.28。
測試項目的邏輯：隨機get()、list()、update()、create()，每個操作都開啟事務，并且休眠500毫秒。
步驟
?運行測試項目
? 項目啟動圖這里我們可以看到，項目已經正常啟動了。
?postman調用一下接口
? 接口請求圖這里我們隨便測試一個接口，請求時間2秒左右。
?執行JMeter的Test Plan，觀察項目日志
? JMeter配置圖這里我們創建了四個線程組，每個線程組調用一個我們的接口。模擬10個人循環1000次的訪問。
正常項目日志圖這里我們看到該請求頻率下，日志無異常。
?慢SQL日志
? 慢SQL日志圖這里我們看到，百萬級的SQL，如果沒加索引SQL執行時間還是比較長的，有的已經達到了2s。另外，關注公眾號Java技術棧可以獲取更多我整理的MySQL系列優化教程。
?加個索引，再觀察項目日志
? 加索引過程日志圖這里我們看到，項目已經開始報錯了，大量的Connection is not available, request timed out after 30001ms。
?SHOW PROCESSLIST一下
? PROCESSLIST圖這里我們看到，有大量的Waiting for table metadata lock。
?postman再次調用一下接口
? 請求接口報錯圖這個時候，調用接口已經報錯了，響應時間也比較久。此時，服務對用戶來說，已經基本不可用了。
為什么會這樣？
?我就想加個索引，怎么就這么難？
?看吧，就因為我加了個索引，服務就掛了，我沒加之前還是好好的。遇到問題，我們要冷靜，不是我們的鍋堅決不能背，真的是我們的問題，下次一定要記得改正。那么，此刻的服務為什么就不可用了呢？
首先我們要知道，在InnoDB事務中，鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。
然后，在MySQL5.5版本中引入了MDL(Metadata Lock)，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對表做結構變更操作的時候，加MDL寫鎖。
我們可以簡單的嘗試一下下面的情況。
DDL鎖等待圖Session A開啟一個事務，執行了一個簡單的查詢語句。此時，Session B，執行另一個查詢語句，可以成功。接著，Session C執行了一個DDL操作，加了個字段，因為Session A的事務沒有提交，而且Session A持有MDL讀鎖，Session C獲取不到MDL寫鎖，所以Session C堵塞等待MDL寫鎖。又由于MDL寫鎖獲取優先級高于MDL讀鎖，因此Session D這個時候也獲取不到MDL讀鎖，等待Session C獲取到MDL寫鎖之后它才能獲取到MDL讀鎖。
我們發現，DDL操作之前如果存在長事務，一直不提交，DDL操作就會一直被堵塞，還會間接的影響后面其他的查詢，導致所有的查詢都被堵塞。
這也就是為什么我們把服務干掛的原因了。
目前主流解決方案
針對上面出現的情況，我們怎么解決呢？
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參數和LOCK參數。
?實際上，ALTERT TABLE語句如果不加ALGORITHM參數，默認就會選擇ALGORITHM=INPLACE的形式，如果執行的語句支持INPLACE，否則，會使用ALGORITHM=COPY。
?以前寫SQL只會ALTER TABLE不知道后面還可以加ALGORITHM參數，后來知道了Online DDL，知道了可以加ALGORITHM=INPLACE，結果兩種寫法有的時候是一樣的...
MySQL官網截圖這里順便提一句，學習的途徑有很多，但是官網，的確可以多看看。
使用pt-online-schema-change
?簡單說一下怎么安裝這個東西
?首先官網下載，然后校驗以及安裝，執行下面命令
perl?Makefile.PL make make?install然后使用CPAN安裝相關依賴（適用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執行圖這里我們看到，pt-osc創建觸發器的時候卡在那了。實際上這里也是在等待鎖。
最終成功了，但是整個過程時間比較久。過程中我們也發現了一些死鎖的日志。
pt-osc死鎖日志其實，這個跟我的代碼有一定的關系，我的測試代碼隨機數生成的范圍是[0, 20000]，然后我根據生成的隨機數，去查詢數據庫，鎖的沖突會比較多。把范圍修改為[0, 1000000]會好很多。
再看Online DDL
因為剛才我們發現了，自己代碼寫的有一些問題，所以我們剛才的結論也有一些影響。我們把隨機數的范圍改到100萬，重新測試一遍。
Online DDL 成功這次Online DDL也成功了。但是也是有一些連接超時的日志。之前的測試如果一直執行下去，也會成功，只不過堵塞時間太長，對用戶影響太大，我就停止算執行失敗了。
?實際效果跟機器性能也是有一些關系的，這里的關鍵點在于拿MDL寫鎖的等待時間，這個時間稍微久一些就會對用戶造成很大的影響。
?pt-osc執行過程
創建一個和原表表結構一樣的臨時表(_tablename_new)，執行alter修改臨時表表結構。
在原表上創建3個與insert?delete?update對應的觸發器，用于copy數據的過程中，在原表的更新操作，更新到新表。
從原表拷貝數據到臨時表，拷貝過程中在原表進行的寫操作都會更新到新建的臨時表。
rename原數據表為old表，把新表rename為原表名，并將old表刪除。
刪除觸發器。
這里面創建、刪除觸發器和rename表的時候都會嘗試獲取DML寫鎖，如果獲取不到會等待。就是我們看到的Waiting for table metadata lock。
所以，這些時間段如果長時間獲取不到鎖，就會一直堵塞，還是會出現問題的。
Online DDL執行過程
拿MDL寫鎖
降級成MDL讀鎖
真正做DDL
升級成MDL寫鎖
釋放MDL鎖
1、4如果沒有鎖沖突，執行時間非常短。第3步占用了DDL絕大部分時間，這期間這個表可以正常讀寫數據，因此稱為online。
但是，如果拿鎖的時候沒拿到，或者升級MDL寫鎖不能成功，就會等待，我們又會看到Waiting for table metadata lock，然后就接著的一系列問題了。
總結
加個索引，說難也難，說不難也不難。如果數據量大，又存在長事務，加索引的過程又有用戶訪問，Online DDL和pt-osc都不能保證對業務沒有影響。但是如果我們SQL的執行時間比較短，或者我們加索引的時候，對應的業務沒有多少請求。那么我們就可以很快的加完索引。
加字段也是類似的過程，但是如果我們能保證沒有慢SQL，那么就不會存在長事務，那么執行時間就會很快，對用戶就可以做到幾乎沒有影響。至于選擇Online DDL還是pt-osc就要看他們的一些限制以及自己的場景需求了。感興趣的同學，自己嘗試一下。
最后想說
當萬丈高樓崩塌的時候，超人也不能將它復原。我們應該做的，是有一個好的規范，好的認知，好的監控，在問題沒有出現的時候，就將問題扼殺在搖籃中。而不是讓問題，日漸壯大，大到覆水難收...
?
參考文獻：
[1]:《MySQL實戰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
總結
以上是生活随笔為你收集整理的老大，我就想加个索引，怎么就这么难？的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 阿里团队高效沟通的秘密，全在这5点！
- 下一篇： 一文探讨 RPC 框架中的服务线程隔离