MySQL新增索引偶遇waiting for table metadata lock

今天抽時間把前段時間出現的故障進行梳理。

8月5日晚對某生產庫相關表加索引

alter table user_address add index idx_create_time(create_time);

主庫正常新增完畢，觀察到從庫出現較高的延遲，show processlist檢視各個執行緒狀態，發現出現大批量的Waiting for table metadata lock

由於是第一次遇到，所以上網查 metadata lock（MDL）的相關資訊。

select * from information_schema.innodb_trx\G

看到有個對user_address表的訪問一直沒有結束

根據經驗判斷這是一個異常的SQL，於是kill掉這個執行緒

然後show processlist：

Waiting for table metadata lock全部消失，並且show slave status \G延遲逐漸降低並消失。

經查閱資料：

1）MySQL5.6之後的版本支援online DDL，建立索引本身不會對錶的增刪改查造成影響，但是由於獲取不到MDL（後設資料鎖），從庫進行索引建立的session就會一直處於Waiting for table metadata lock狀態，導致後續對此表的訪問造成影響。

2）後期操作alter table，可以通過提前檢查information_schema.innodb_trx的事務提交情況，以避免此類情況發生。

參考文件：

https://www.cnblogs.com/digdeep/p/4892953.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30135314/viewspace-2654508/，如需轉載，請註明出處，否則將追究法律責任。