MySQL5.5加主鍵鎖讀問題 背景 有同學討論到MySQL 5.5下給大表加主鍵時會鎖住讀的問題，懷疑與fast index creation有關，這裡簡單說明下。 對照現象 為了說明這個問題的原因，有興趣的同學可以做對比實驗。 1) 在給InnoDB表創建主鍵期間，會鎖住該表上的讀數據 2) 但是同樣的表執行刪除主鍵期間，不會鎖住該表上的讀操作 ----這說明與是否fast index creation無關，因為這兩個操作在數據層面的行為應該是類似的，實際上，創建/刪除主鍵都必須copy data。 3) 在創建主鍵期間，會鎖住該表上執行的show create table ----用1、3的現象可以猜測出，實際上與meta data lock有關。 關於meta data lock(MDL) MySQL 5.5中引入了MDL，當需要訪問、修改表結構時，都需要對meta data加鎖（讀或寫）。比如，當一個線程需要修改表結構的任意一部分時，此時需要阻塞對表結構的訪問，當然也需要阻塞對數據行的訪問。 加主鍵流程 當對一個表作加主鍵操作時，大致流程如下 1) MDL加寫鎖 2) 操作數據，最耗時部分，注意需要copy data，因此流程上是 a)創建一個臨時表A，表A定義為修改後的表結構 b)從原表讀取數據插入表A c)刪除原表，將表A重命名為原表名 3) MDL釋放寫鎖 從這個流程可以看到，在最耗時的部分，meta data是被一個X鎖保護的。因此在此期間，show create table 或者select data都是會被阻塞。 這解釋了上面的1) 3)。 刪除主鍵流程 1) MDL加讀鎖 2) 操作數據，最耗時部分 a) 創建一個臨時表A，表A定義為修改後的表結構 b) 從原表讀取數據插入表A 3) MDL將寫鎖升級為讀鎖 c) 刪除原表，將表A重命名為原表名 4) MDL釋放寫鎖 這個在最耗時的數據操作部分，加的是MDL的讀鎖，這樣不會影響訪問原表的表結構或數據（當然要做更新是不行的）。而最後升級為寫鎖的時間，只是做重命名表的操作，阻塞的時間就很短。 結論 1) 顯然第二個流程更合理 2) 這個可以認為是MySQL一個可改進的點，並且在5.6下已經改進 3) 這個問題與是copy data還是inplace方式執行DDL無關，實際上由於InnoDB的聚集索引組織結構，增、刪主鍵都是必須得copy data的。
