MySQL在建立索引之前一定要想到的事情

紫翼龍王夜發表於2015-02-05
MySql索引
MySQL在5.5.3版本引入了metadata lock
他的本意是解決之前版本事務隔離特性的幾個bug,但是引入的問題也不小.

先說說MySQL的事務吧.
Oracle的事務指的是需要分配回滾段的SQL語句,也就是說select並不是oracle事務的一部分.
比如執行一個查詢,然後在另外一個會話查詢v$transaction,並不會有任何相關的資訊.直到事務中出現insert,update,delete。
而innodb的事務包括select查詢.
無論事務隔離級別是可重複讀,還是讀提交,只要有查詢,事務就開始了
下圖證明了在5.6.15,設定了autocommit=0之後,執行一個查詢就可以開啟一個事務.
第一個會話執行查詢.

第二個會話,執行 show engine innodb status\G 檢視事務情況

可以看到id為1的執行緒,已經開始了一個事務.

為什麼Oracle的事務僅包括insert,update和delete的語句,而innodb的事務包括所有的語句呢?
我覺得這個和廠商支援的隔離級別有很大的關係.
眾所周知,Oracle僅僅支援讀提交和序列化兩種事務隔離級別,而讀提交是絕大多數資料庫的選擇.
讀提交意味著可以出現幻讀和不可重複讀,那麼從實現原理的角度,Oracle可以在語句(Statement級別)開始的時候,記錄SCN然後應用MVCC查詢.每個查詢只需要記錄自己開始的SCN即可.而語句開始的SCN和事務並沒有關係.所以Oracle的事務,並不包括查詢.

而innodb支援可重複讀隔離級別,也就是說在一個事務中,無論執行多少次查詢,結果都必須是一致的.
(innodb不僅支援可重複讀,並且使用間隙鎖在可重複讀級別避免了幻讀,當然這也帶來了很多問題..)
所以它記錄的不是每個查詢語句的LSN,而是事務第一個語句發生時的LSN,無論第一個語句是查詢,還是修改.
innodb在可重複讀的級別下,查詢用事務開始時的LSN應用MVCC,與Oracle不同的是,innodb查詢回滾段中小於事務開始的LSN的資料版本,
而oracle查詢回滾段中小於語句SCN的資料版本.
也就是說,同樣都是MVCC,oracle是語句級的,innodb是事務級的

這裡有一個問題,按說事務包括查詢是因為可重複讀隔離級別的需要,但是innodb讀提交隔離級別同樣也將查詢作為了事務的一部分.
可能是因為架構或者程式碼實現層面的問題吧.
不管怎麼樣,Innodb就是這麼做了.

然後再說說metadata lock
在5.5.3之前,metadata lock是語句級的,這實際上破壞了事務的一致性.
比如一個事務,在可重複讀隔離級別,執行兩次查詢,居然結果不一致.

這正是因為metadata lock是語句級造成的問題,
在兩個查詢的間隔,另外一個會話執行了truncate table.
所以再次執行查詢,沒有任何結果.

MySQL為了解決這個問題,在5.5.3將metadata lock提升為事務級別的鎖.
任何DDL都需要先獲得metadata lock,但是這個鎖需要等事務結束的時候釋放.
同樣的實驗,在5.6.13就變成這樣的了.
第一個會話的事務沒有結束,那麼第二個會話的DDL就被阻塞

使用show processlist可以看到DDL語句在等待第一個會話事務的metadata lock

通過這種方式,就保證了可重複讀隔離級別下,事務的一致性.

和之前提到的查詢也作為事務的一部分一樣,innodb並沒有為讀提交量身定製一些東西,
比如讀提交併不需要查詢作為事務的一部分
和讀提交併不需要事務級別的metadata lock.
可能是出於架構層面的問題,很多可重複讀的特性強加在了讀提交上,
所以一旦這些特性出現問題,即使將隔離級別降為讀提交也不能避免.

接下來問題來了,
剛才的DDL被metadata lock阻塞,這個DDL還會進一步阻塞其他的事務.甚至是查詢(查詢是innodb事務的一部分.)

這就有點抓狂了,因為這個時候,系統其實已經Hung了.
假設id為1的執行緒持有metadata lock 沒有提交,
id為2的執行緒進行DDL,然後被阻塞線上程1的metadata鎖上,
這時,資料庫依次來了8個查詢,他們都阻塞在了執行緒2上.
假如執行緒1的事務不結束,其他的執行緒都被阻塞.
即使執行緒1的事務結束了..也是後面8個事務依次獲得metadata鎖,與此同時,這個DDL可能又阻塞了80個事務..

這時候,系統的併發為1,這個DDL可能永遠不能執行.並且這種情況不在死鎖檢測的範圍內.
它的鎖超時時間,由lock_wait_timeout引數控制,預設是31536000(一年,坑爹吧)

MySQL雖然保證了事務的一致性,避免了bug,但是引入的問題卻可能讓我這樣的初級dba丟了飯碗..

最後梳理一下可能引發metadata lock連環阻塞的情況
1.在有其他事務執行的時候,進行DDL操作(alter table;truncate;)
2.在mysqldump執行的時候,進行DDL操作.(想想就覺得坑爹)
3.在Master-Slave複製環境,在Slave執行查詢,會導致Master傳過來的DDL阻塞.導致複製延遲增大.
4.建立索引(...)

作為初級dba來說,為了保住飯碗,可以有兩個動作
1.將lock_wait_timeout引數調低
2.在執行DDL之前,檢視事務是否頻繁,在執行DDL之後,開啟另外一個會話,使用show processlist檢視是否被metadata lock阻塞.
一旦阻塞,先Kill ddl的操作.

參考:
http://blog.csdn.net/wzy0623/article/details/42149525
http://blog.csdn.net/wzy0623/article/details/8679457
http://blog.itpub.net/26515977/viewspace-1208250/

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

相關文章