有同學(xué)討論到MySQL 5.5下給大表加主鍵時(shí)會(huì)鎖住讀的問(wèn)題，懷疑與fast index creation有關(guān)，這里簡(jiǎn)單說(shuō)明下。

對(duì)照現(xiàn)象

         為了說(shuō)明這個(gè)問(wèn)題的原因，有興趣的同學(xué)可以做對(duì)比實(shí)驗(yàn)。

    1)  在給InnoDB表創(chuàng)建主鍵期間，會(huì)鎖住該表上的讀數(shù)據(jù)

    2) 但是同樣的表執(zhí)行刪除主鍵期間，不會(huì)鎖住該表上的讀操作

----這說(shuō)明與是否fast index creation無(wú)關(guān)，因?yàn)檫@兩個(gè)操作在數(shù)據(jù)層面的行為應(yīng)該是類似的，實(shí)際上，創(chuàng)建/刪除主鍵都必須copy data。

    3) 在創(chuàng)建主鍵期間，會(huì)鎖住該表上執(zhí)行的show create table

----用1、3的現(xiàn)象可以猜測(cè)出，實(shí)際上與meta data lock有關(guān)。

關(guān)于meta data lock(MDL)

         MySQL 5.5中引入了MDL，當(dāng)需要訪問(wèn)、修改表結(jié)構(gòu)時(shí)，都需要對(duì)meta data加鎖（讀或?qū)懀１热纾?dāng)一個(gè)線程需要修改表結(jié)構(gòu)的任意一部分時(shí)，此時(shí)需要阻塞對(duì)表結(jié)構(gòu)的訪問(wèn)，當(dāng)然也需要阻塞對(duì)數(shù)據(jù)行的訪問(wèn)。

加主鍵流程

         當(dāng)對(duì)一個(gè)表作加主鍵操作時(shí)，大致流程如下

        1) MDL加寫(xiě)鎖

       2) 操作數(shù)據(jù)，最耗時(shí)部分，注意需要copy data，因此流程上是

             a)創(chuàng)建一個(gè)臨時(shí)表A，表A定義為修改后的表結(jié)構(gòu)

             b)從原表讀取數(shù)據(jù)插入表A

             c)刪除原表，將表A重命名為原表名

       3)  MDL釋放寫(xiě)鎖

從這個(gè)流程可以看到，在最耗時(shí)的部分，meta data是被一個(gè)X鎖保護(hù)的。因此在此期間，show create table 或者select data都是會(huì)被阻塞。

這解釋了上面的1) 3)。

刪除主鍵流程

        1)  MDL加讀鎖

       2)  操作數(shù)據(jù)，最耗時(shí)部分

             a) 創(chuàng)建一個(gè)臨時(shí)表A，表A定義為修改后的表結(jié)構(gòu)

             b) 從原表讀取數(shù)據(jù)插入表A

        3) MDL將讀鎖升級(jí)為寫(xiě)鎖

            c) 刪除原表，將表A重命名為原表名

       4)  MDL釋放寫(xiě)鎖

   這個(gè)在最耗時(shí)的數(shù)據(jù)操作部分，加的是MDL的讀鎖，這樣不會(huì)影響訪問(wèn)原表的表結(jié)構(gòu)或數(shù)據(jù)（當(dāng)然要做更新是不行的）。而最后升級(jí)為寫(xiě)鎖的時(shí)間，只是做重命名表的操作，阻塞的時(shí)間就很短。

結(jié)論

        1) 顯然第二個(gè)流程更合理

        2) 這個(gè)可以認(rèn)為是MySQL一個(gè)可改進(jìn)的點(diǎn)，并且在5.6下已經(jīng)改進(jìn)

        3) 這個(gè)問(wèn)題與是copy data還是inplace方式執(zhí)行DDL無(wú)關(guān)，實(shí)際上由于InnoDB的聚集索引組織結(jié)構(gòu)，增、刪主鍵都是必須得copy data的。

==========更新====

 有同學(xué)問(wèn)說(shuō)為什么在5.5 set old_alter_table=on;之后是不會(huì)阻塞讀的？ 因?yàn)榇蜷_(kāi)old_alter_table之后，MySQL認(rèn)為這次無(wú)論如何是要copy data的，所以鎖用的是“刪除主鍵流程”的策略。

實(shí)際上無(wú)論old_alter_table是否打開(kāi)，對(duì)主鍵操作都是必須copy data的，5.6的改進(jìn)就是基于這個(gè)判斷。

更多的Metadata lock 信息 請(qǐng)見(jiàn)：http://www.rzrgm.cn/zuoxingyu/archive/2013/03/15/2960862.html