問(wèn)題
最近維護(hù)MySQL數(shù)據(jù)庫(kù)時(shí)毕莱，有個(gè)業(yè)務(wù)報(bào)障說(shuō)所有的SQL語(yǔ)句都超時(shí)，執(zhí)行了5分鐘都執(zhí)行不完础浮。
我登錄到他的MySQL后嘀粱，執(zhí)行了Show Process就發(fā)現(xiàn)問(wèn)題了。
過(guò)程是他在一個(gè)表上執(zhí)行了一個(gè)大查詢爬迟，查詢沒(méi)有結(jié)束的情況下橘蜜，又在該表發(fā)起了一個(gè)DDL語(yǔ)句，然后后續(xù)在該表上的SQL語(yǔ)句執(zhí)行就一直在等待了付呕。
具體的細(xì)節(jié)先不分析计福，讓我們來(lái)重現(xiàn)這個(gè)場(chǎng)景，并在場(chǎng)景中進(jìn)行分析徽职。
環(huán)境準(zhǔn)備
首先我們準(zhǔn)備一張表象颖，并在表中插入一點(diǎn)數(shù)據(jù)。
mysql> CREATE TABLE `t` (
-> `id` int(11) NOT NULL,
-> `name` varchar(30) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t values ( 1, '123') ;
Query OK, 1 row affected (0.03 sec)
問(wèn)題重現(xiàn)
為了重現(xiàn)問(wèn)題姆钉，我們至少需要4個(gè)會(huì)話说订，因?yàn)樗械腟QL語(yǔ)句執(zhí)行都會(huì)堵住，我們按照會(huì)話執(zhí)行的先后順序來(lái)執(zhí)行潮瓶。
- 會(huì)話1
在會(huì)話1中執(zhí)行一條大查詢陶冷，這條查詢語(yǔ)句要執(zhí)行很長(zhǎng)時(shí)間。
mysql> select id , name , sleep(300) from t ;
- 會(huì)話2
發(fā)起一條DDL操作毯辅。
mysql> alter table t add ts timestamp;
因?yàn)镈DL語(yǔ)句會(huì)申請(qǐng)MDL鎖埂伦，MDL鎖是表級(jí)別獨(dú)占鎖，前面有一條查詢t表的SQL語(yǔ)句沒(méi)有結(jié)束思恐，所以該DDL語(yǔ)句會(huì)等待沾谜。
- 會(huì)話3
發(fā)起t表上的其他任何語(yǔ)句膊毁，都會(huì)堵住，這里執(zhí)行一條簡(jiǎn)單sql語(yǔ)句类早。
mysql> select * from t where id=1 ;
可以看到會(huì)話堵住了媚媒。
- 會(huì)話4
在該會(huì)話嗜逻，我們來(lái)通過(guò)show processlist 看看發(fā)生了什么涩僻？
mysql> show processlist ;
+--------+-------------+--------------------+------------+-------------+-------+-----------------------------------------------------------------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+--------------------+------------+-------------+-------+-----------------------------------------------------------------------------+---------------------------------------+
| 177915 | test | 127.0.0.1:27538 | testdb | Query | 17 | User sleep | select id , name , sleep(300) from t |
| 178701 | test | 127.0.0.1:28316 | testdb | Query | 15 | Waiting for table metadata lock | alter table t add column ts timestamp |
| 178751 | test | 127.0.0.1:28346 | testdb | Query | 12 | Waiting for table metadata lock | select * from t where id=1 |
| 188250 | test | 127.0.0.1:37604 | testdb | Query | 0 | init | show processlist |
+--------+-------------+--------------------+------------+-------------+-------+-----------------------------------------------------------------------------+---------------------------------------+
從show process結(jié)果中可以看到，第一條sql語(yǔ)句select id , name , sleep(300) from t 正在執(zhí)行中栈顷，而第二條和第三條sql語(yǔ)句都在等待MDL鎖逆日。
在這個(gè)案例中第一條大SQL是起因，結(jié)合DDL操作就會(huì)出現(xiàn)該問(wèn)題萄凤。
如何避免文中的問(wèn)題
如果我們按照上面問(wèn)題重現(xiàn)的步驟在一個(gè)MySQL上執(zhí)行那么基本上沒(méi)法避免出現(xiàn)MDL鎖的問(wèn)題室抽。
業(yè)務(wù)方問(wèn)咨詢的時(shí)候，我建議他盡量不要自己執(zhí)行DDL靡努，在數(shù)據(jù)庫(kù)管理平臺(tái)申請(qǐng)SQL坪圾，我們的平臺(tái)會(huì)審核后，對(duì)于DDL語(yǔ)句惑朦，會(huì)在底層通過(guò)pt工具執(zhí)行兽泄，依賴防止大表ALTER操作影響后續(xù)的SQL，而來(lái)漾月，pt工具在執(zhí)行DDL之前會(huì)判斷處理該表上的長(zhǎng)SQL病梢，如果超過(guò)10s，會(huì)直接kill掉sql語(yǔ)句后梁肿，再執(zhí)行DDL蜓陌。這樣就避免了MDL鎖的出現(xiàn)。
當(dāng)然最好的方式是大查詢SQL不要在主庫(kù)執(zhí)行吩蔑，在從庫(kù)中執(zhí)行影響就會(huì)很小了钮热，而且可以通過(guò)代理層(參考一步一步打造MySQL高可用平臺(tái))，把讀操作自動(dòng)負(fù)載均衡到從庫(kù)烛芬。
