我就想加個(gè)索引,怎么就這么難?
領(lǐng)導(dǎo)讓我
?SQL優(yōu)化，我直接把服務(wù)干掛了...
前言
MySQL大表加字段或者加索引，是有一定風(fēng)險(xiǎn)的。
大公司一般有DBA，會(huì)幫助開發(fā)解決這個(gè)痛點(diǎn)，可是DBA是怎么做的呢？
小公司沒有DBA，作為開發(fā)我們的責(zé)任就更大了。那么我們?cè)趺床拍馨踩募觽€(gè)索引呢？
今天，我們通過模擬案例以及原理分析，去弄清楚MySQL中DDL的風(fēng)險(xiǎn)，以及如何避免事故發(fā)生。
準(zhǔn)備
軟件以及項(xiàng)目
- 安裝本地版本MySQL。
- 一個(gè)簡(jiǎn)單的增刪改查項(xiàng)目。
- 使用JMeter進(jìn)行并發(fā)請(qǐng)求測(cè)試。
創(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 '測(cè)試 id',
`create_time` timestamp NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
`modify_time` timestamp NULL DEFAULT NULL COMMENT '修改時(shí)間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='mysql ddl測(cè)試表';
創(chuàng)建存儲(chǔ)過程
# 如果存在test存儲(chǔ)過程則刪除
DROP PROCEDURE IF EXISTS `test`;
# 創(chuàng)建無參存儲(chǔ)過程，名稱為test
CREATE PROCEDURE test()
BEGIN
# 聲明變量
DECLARE i INT;
# 變量賦值
SET i = 0;
# 結(jié)束循環(huán)的條件: 當(dāng)i等于100萬時(shí)跳出while循環(huán)
WHILE i < 1000000 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)建存儲(chǔ)過程語句，是在
?IDE內(nèi)選擇代碼塊執(zhí)行的，如果在Terminal中執(zhí)行，需要使用DELIMITER關(guān)鍵字，更改語句結(jié)束標(biāo)志。
調(diào)用存儲(chǔ)過程，生成百萬數(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í)行時(shí)間 只有新session才生效
SET GLOBAL long_query_time = 1;
?通常情況下這些會(huì)在MySQL的配置文件中配置，啟動(dòng)時(shí)生效。
?
幾個(gè)有用的SQL語句
# 展示哪些線程正在運(yùn)行
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存儲(chǔ)引擎狀態(tài)的大量信息，包含死鎖日志
SHOW ENGINE INNODB STATUS ;
# 展示數(shù)據(jù)庫最大連接數(shù)的配置
SHOW VARIABLES LIKE 'max_connections';
# 查看存在哪些觸發(fā)器
SELECT * FROM information_schema.TRIGGERS;
# 查看MySQL版本
SELECT VERSION();
?后面我們會(huì)主要用前兩條。
?
事故現(xiàn)場(chǎng)
說明
- 我創(chuàng)建的
user表除了主鍵是沒有其他索引的。 - 測(cè)試的
user表數(shù)據(jù)量為一百萬。 - 測(cè)試
MySQL版本為5.7.28。 - 測(cè)試項(xiàng)目的邏輯：隨機(jī)get()、list()、update()、create()，每個(gè)操作都開啟事務(wù)，并且休眠500毫秒。
步驟
?運(yùn)行測(cè)試項(xiàng)目
?
項(xiàng)目啟動(dòng)圖
這里我們可以看到，項(xiàng)目已經(jīng)正常啟動(dòng)了。
??
postman調(diào)用一下接口
接口請(qǐng)求圖
這里我們隨便測(cè)試一個(gè)接口，請(qǐng)求時(shí)間2秒左右。
?執(zhí)行JMeter的Test Plan，觀察項(xiàng)目日志
?
JMeter配置圖
這里我們創(chuàng)建了四個(gè)線程組，每個(gè)線程組調(diào)用一個(gè)我們的接口。模擬10個(gè)人循環(huán)1000次的訪問。
正常項(xiàng)目日志圖
這里我們看到該請(qǐng)求頻率下，日志無異常。
?慢SQL日志
?
慢SQL日志圖
這里我們看到，百萬級(jí)的SQL，如果沒加索引SQL執(zhí)行時(shí)間還是比較長(zhǎng)的，有的已經(jīng)達(dá)到了2s。
?加個(gè)索引，再觀察項(xiàng)目日志
?
加索引過程日志圖
這里我們看到，項(xiàng)目已經(jīng)開始報(bào)錯(cuò)了，大量的Connection is not available, request timed out after 30001ms。
??
SHOW PROCESSLIST一下
PROCESSLIST圖
這里我們看到，有大量的Waiting for table metadata lock。
??
postman再次調(diào)用一下接口
請(qǐng)求接口報(bào)錯(cuò)圖
這個(gè)時(shí)候，調(diào)用接口已經(jīng)報(bào)錯(cuò)了，響應(yīng)時(shí)間也比較久。此時(shí)，服務(wù)對(duì)用戶來說，已經(jīng)基本不可用了。
為什么會(huì)這樣？
?我就想加個(gè)索引，怎么就這么難？
?
看吧，就因?yàn)槲壹恿藗€(gè)索引，服務(wù)就掛了，我沒加之前還是好好的。遇到問題，我們要冷靜，不是我們的鍋堅(jiān)決不能背，真的是我們的問題，下次一定要記得改正。那么，此刻的服務(wù)為什么就不可用了呢？
首先我們要知道，在InnoDB事務(wù)中，鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段鎖協(xié)議。
然后，在MySQL5.5版本中引入了MDL(Metadata Lock)，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加MDL讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加MDL寫鎖。
我們可以簡(jiǎn)單的嘗試一下下面的情況。
DDL鎖等待圖
Session A開啟一個(gè)事務(wù)，執(zhí)行了一個(gè)簡(jiǎn)單的查詢語句。此時(shí)，Session B，執(zhí)行另一個(gè)查詢語句，可以成功。接著，Session C執(zhí)行了一個(gè)DDL操作，加了個(gè)字段，因?yàn)?code style="font-size:14px;background-color:rgba(27,31,35,.05);font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;color:rgb(239,112,96);">Session A的事務(wù)沒有提交，而且Session A持有MDL讀鎖，Session C獲取不到MDL寫鎖，所以Session C堵塞等待MDL寫鎖。又由于MDL寫鎖獲取優(yōu)先級(jí)高于MDL讀鎖，因此Session D這個(gè)時(shí)候也獲取不到MDL讀鎖，等待Session C獲取到MDL寫鎖之后它才能獲取到MDL讀鎖。
我們發(fā)現(xiàn)，DDL操作之前如果存在長(zhǎng)事務(wù)，一直不提交，DDL操作就會(huì)一直被堵塞，還會(huì)間接的影響后面其他的查詢，導(dǎo)致所有的查詢都被堵塞。
這也就是為什么我們把服務(wù)干掛的原因了。
目前主流解決方案
針對(duì)上面出現(xiàn)的情況，我們?cè)趺唇鉀Q呢？
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ù)。
?實(shí)際上，
?ALTERT TABLE語句如果不加ALGORITHM參數(shù)，默認(rèn)就會(huì)選擇ALGORITHM=INPLACE的形式，如果執(zhí)行的語句支持INPLACE，否則，會(huì)使用ALGORITHM=COPY。
以前寫SQL只會(huì)ALTER TABLE不知道后面還可以加ALGORITHM參數(shù)，后來知道了Online DDL，知道了可以加ALGORITHM=INPLACE，結(jié)果兩種寫法有的時(shí)候是一樣的...
MySQL官網(wǎng)截圖
這里順便提一句，學(xué)習(xí)的途徑有很多，但是官網(wǎng)，的確可以多看看。
使用pt-online-schema-change
?簡(jiǎn)單說一下怎么安裝這個(gè)東西
?
首先官網(wǎng)下載，然后校驗(yàn)以及安裝，執(zhí)行下面命令
perl Makefile.PL
make
make install
然后使用CPAN安裝相關(guān)依賴（適用Unix），CentOS下直接yum更簡(jiǎn)單
perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
?我自己Mac安裝沒啥問題，公司Mac安裝失敗了，然后升級(jí)了一下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測(cè)試
pt-osc執(zhí)行圖
這里我們看到，pt-osc創(chuàng)建觸發(fā)器的時(shí)候卡在那了。實(shí)際上這里也是在等待鎖。
最終成功了，但是整個(gè)過程時(shí)間比較久。過程中我們也發(fā)現(xiàn)了一些死鎖的日志。
pt-osc死鎖日志
其實(shí)，這個(gè)跟我的代碼有一定的關(guān)系，我的測(cè)試代碼隨機(jī)數(shù)生成的范圍是[0, 20000]，然后我根據(jù)生成的隨機(jī)數(shù)，去查詢數(shù)據(jù)庫，鎖的沖突會(huì)比較多。把范圍修改為[0, 1000000]會(huì)好很多。
再看Online DDL
因?yàn)閯偛盼覀儼l(fā)現(xiàn)了，自己代碼寫的有一些問題，所以我們剛才的結(jié)論也有一些影響。我們把隨機(jī)數(shù)的范圍改到100萬，重新測(cè)試一遍。
Online DDL 成功
這次Online DDL也成功了。但是也是有一些連接超時(shí)的日志。之前的測(cè)試如果一直執(zhí)行下去，也會(huì)成功，只不過堵塞時(shí)間太長(zhǎng)，對(duì)用戶影響太大，我就停止算執(zhí)行失敗了。
?實(shí)際效果跟機(jī)器性能也是有一些關(guān)系的，這里的關(guān)鍵點(diǎn)在于拿
?MDL寫鎖的等待時(shí)間，這個(gè)時(shí)間稍微久一些就會(huì)對(duì)用戶造成很大的影響。
pt-osc執(zhí)行過程
- 創(chuàng)建一個(gè)和原表表結(jié)構(gòu)一樣的臨時(shí)表(
_tablename_new)，執(zhí)行alter修改臨時(shí)表表結(jié)構(gòu)。 - 在原表上創(chuàng)建3個(gè)與
insertdeleteupdate對(duì)應(yīng)的觸發(fā)器，用于copy數(shù)據(jù)的過程中，在原表的更新操作，更新到新表。 - 從原表拷貝數(shù)據(jù)到臨時(shí)表，拷貝過程中在原表進(jìn)行的寫操作都會(huì)更新到新建的臨時(shí)表。
rename原數(shù)據(jù)表為old表，把新表rename為原表名，并將old表刪除。- 刪除觸發(fā)器。
這里面創(chuàng)建、刪除觸發(fā)器和rename表的時(shí)候都會(huì)嘗試獲取DML寫鎖，如果獲取不到會(huì)等待。就是我們看到的Waiting for table metadata lock。
所以，這些時(shí)間段如果長(zhǎng)時(shí)間獲取不到鎖，就會(huì)一直堵塞，還是會(huì)出現(xiàn)問題的。
Online DDL執(zhí)行過程
- 拿
MDL寫鎖 - 降級(jí)成
MDL讀鎖 - 真正做
DDL - 升級(jí)成
MDL寫鎖 - 釋放
MDL鎖
1、4如果沒有鎖沖突，執(zhí)行時(shí)間非常短。第3步占用了DDL絕大部分時(shí)間，這期間這個(gè)表可以正常讀寫數(shù)據(jù)，因此稱為online。
但是，如果拿鎖的時(shí)候沒拿到，或者升級(jí)MDL寫鎖不能成功，就會(huì)等待，我們又會(huì)看到Waiting for table metadata lock，然后就接著的一系列問題了。
總結(jié)
加個(gè)索引，說難也難，說不難也不難。如果數(shù)據(jù)量大，又存在長(zhǎng)事務(wù)，加索引的過程又有用戶訪問，Online DDL和pt-osc都不能保證對(duì)業(yè)務(wù)沒有影響。但是如果我們SQL的執(zhí)行時(shí)間比較短，或者我們加索引的時(shí)候，對(duì)應(yīng)的業(yè)務(wù)沒有多少請(qǐng)求。那么我們就可以很快的加完索引。
加字段也是類似的過程，但是如果我們能保證沒有慢SQL，那么就不會(huì)存在長(zhǎng)事務(wù)，那么執(zhí)行時(shí)間就會(huì)很快，對(duì)用戶就可以做到幾乎沒有影響。至于選擇Online DDL還是pt-osc就要看他們的一些限制以及自己的場(chǎng)景需求了。感興趣的同學(xué)，自己嘗試一下。
最后想說
當(dāng)萬丈高樓崩塌的時(shí)候，超人也不能將它復(fù)原。我們應(yīng)該做的，是有一個(gè)好的規(guī)范，好的認(rèn)知，好的監(jiān)控，在問題沒有出現(xiàn)的時(shí)候，就將問題扼殺在搖籃中。而不是讓問題，日漸壯大，大到覆水難收...
參考文獻(xiàn)：
[1]:《MySQL實(shí)戰(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
文末福利
分享一套Springboot開發(fā)的博客系統(tǒng)源碼，為了讓更多的Java讀者能詳細(xì)理解這個(gè)項(xiàng)目，作者把開發(fā)這個(gè)項(xiàng)目過程寫成了文檔。
從0到1一步一步帶你從搭建項(xiàng)目框架，各種細(xì)節(jié)調(diào)整，以及如何開發(fā)各個(gè)模塊的功能，比如即時(shí)通知，群聊，分布式實(shí)時(shí)搜索等功能，一共寫了10+篇詳細(xì)的開發(fā)設(shè)計(jì)文檔，一步一步，一行一行代碼，讓你了解整個(gè)開發(fā)項(xiàng)目的過程，理解項(xiàng)目作者開發(fā)過程中的所有思考.
另外，長(zhǎng)達(dá)17小時(shí)的eblog完整講解視頻已在上線啦，非常詳細(xì)，一起來學(xué)eblog。
如何獲取項(xiàng)目地址與詳細(xì)的開發(fā)文檔？
我把它放在我的Java開發(fā)寶典里了，大家掃一下下面的二維碼，關(guān)注后回復(fù)關(guān)鍵字：eblog，即可獲取項(xiàng)目，以及作者的詳細(xì)開發(fā)文檔、以及完整項(xiàng)目講解視頻！無任何套路！
掃描上面二維碼，回復(fù)關(guān)鍵字：eblog
希望大家拿去好好學(xué)習(xí)，如果覺得不錯(cuò)，也可以把文章分享給其他小伙伴，一起學(xué)習(xí)！
點(diǎn)贊是最大的支持 