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

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的操作.

MySQL索引之隔離列 

MySQL索引之雜湊索引

MySQL索引之首碼索引和索引選擇性

MySQL索引使用率監控

MySQL索引最佳化和in or替換為union all

MySQL索引設計的原則

本文永久更新連結地址：