簡介

在MySQL5.5以及之前的版本，通常更改數(shù)據(jù)表結(jié)構(gòu)操作（DDL）會阻塞對表數(shù)據(jù)的增刪改操作（DML）。
MySQL5.6提供Online DDL之后可支持DDL與DML操作同時執(zhí)行，降低了DDL期間對業(yè)務(wù)延遲帶來的影響。

下面進行案例演示。我們準(zhǔn)備了一個分?jǐn)?shù)表，有600萬的測試數(shù)據(jù)，接下來我們驗證一下我們?nèi)粘Ｊ褂玫腁LTER語句（不提交），看看是否會全程鎖表

#事務(wù)A 添加索引 不提交
begin;
ALTER TABLE scores ADD index idx_student_id (student_id);
# commit;

#事務(wù)B 查詢數(shù)據(jù)與修改數(shù)據(jù)
begin;
select id from scores where id= 1;
commit;begin;
update scores set course_name = '張三' where id = 1;
commit;

發(fā)現(xiàn)查詢事務(wù)和修改事務(wù)都是可以正常返回的，發(fā)現(xiàn)這條DDL語句不會全程鎖表，執(zhí)行過程中真的不會鎖表嗎？我們再看一種情況，首先刪除索引：

ALTER TABLE scores drop index idx_student_id;

然后開啟添加索引事務(wù)（不提交），然后再開啟查詢事務(wù)，并且也不提交，這個時候通過show processlist命令查看mysql的執(zhí)行信息，觀察加索引時ddl語句的執(zhí)行情況。

show processlist;

觀察到ddl語句正在執(zhí)行中：

再show一下，發(fā)現(xiàn)ddl語句變成了等待我們的元數(shù)據(jù)鎖釋放中，即查詢語句持有了我們的一個元數(shù)據(jù)鎖。這個時候我們提交查詢事務(wù)，可以發(fā)現(xiàn)索引就添加成功了。說明我們這條sql本質(zhì)上還是有個加鎖的過程。

再看一種情況，也先刪除索引，然后開啟查詢事務(wù)不提交，去持有元數(shù)據(jù)鎖，然后再執(zhí)行添加索引，然后show一下執(zhí)行信息，發(fā)現(xiàn)DDL語句已經(jīng)在等待元數(shù)據(jù)鎖釋放了。然后我們提交一下查詢事務(wù)，再show一下，發(fā)現(xiàn)DDL語句正在往下執(zhí)行，這個時候我們再開啟一個事務(wù)進行修改（也不提交），多show幾下直到DDL執(zhí)行完畢，發(fā)現(xiàn)我們開啟的修改事務(wù)阻塞了我們的DDL語句繼續(xù)執(zhí)行。提交修改事務(wù)，發(fā)現(xiàn)索引就添加成功了。那么也就是說我們這個DDL語句實際上有兩個加鎖的過程，并且在這個加鎖的時候如果說我們的元數(shù)據(jù)鎖被其他的事務(wù)給占有了，那么我們這個DDL語句就會被阻塞，第二次也是一樣的。

MySQL 5.6或更高版本上進行表結(jié)構(gòu)修改操作（如添加索引），MySQL默認(rèn)情況下會嘗試使用Online DDL來最小化操作對讀寫操作的影響，MySQL會選擇默認(rèn)的最佳方式進行操作。

Online DDL概念

概念：在不中斷現(xiàn)有數(shù)據(jù)讀寫操作的情況下，自動執(zhí)行 DDL語句（例如創(chuàng)建、修改、刪除表等）的機制。Online DDL 可以在MySQL進行表空間或數(shù)據(jù)文件的變化時，自動執(zhí)行 DDL語句，從而避免了傳統(tǒng)方式中，執(zhí)行 DDL 語句時對數(shù)據(jù)庫讀寫操作的干擾和中斷。

執(zhí)行過程：Online ddl 執(zhí)行大致可分為三個階段：初始化階段、執(zhí)行階段和提交表定義階段：

• 初始化階段：
• • 評估存儲引擎能力與DDL語句
• • 評估ALGORITHM 和 LOCK
• • 創(chuàng)建可升級的MDL讀鎖（元數(shù)據(jù)讀鎖）
• 執(zhí)行階段：
• • 此階段分為兩個步驟準(zhǔn)備和執(zhí)行DDL語句
• • 此階段是否需要MDL寫鎖取決于初始化階段評估的因素。如果需要MDL寫鎖的話，僅在準(zhǔn)備過程會短暫的使用MDL寫鎖，然后降級為MDL讀鎖
• • DDL執(zhí)行過程（最耗時）
• 提交表定義階段：
• • 此階段會將MDL讀鎖升級到MDL寫鎖，此階段一般較快，因此獨占鎖的時間也較短
• • 用新的表定義替換舊的表定義，釋放MDL鎖

Online DDL用法

區(qū)別與我們?nèi)粘Ｊ褂玫腄LL語句，多了兩個參數(shù)

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=INPLACE, LOCK=NONE;ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=COPY, LOCK=EXCLUSIVE;

ALGORITHM有三個可選項

ALGORITHM=DEFAULT：默認(rèn)算法，使用最高效的算法


ALGORITHM=INPLACE：解決全程鎖表的一個方式，在原表上進行更改，不需要生成臨時表，不需要進行數(shù)據(jù)copy的過程。
添加索引步驟：
1.創(chuàng)建索引（二級索引）數(shù)據(jù)字典
2.加共享表鎖，禁止DML，允許查詢
3.讀取聚簇索引，構(gòu)造新的索引項，排序并插入新索引
4.等待打開當(dāng)前表的所有只讀事務(wù)提交
5.創(chuàng)建索引結(jié)束


ALGORITHM=COPY：最原始的方式，通過臨時表創(chuàng)建索引，需要多一倍存儲，還有更多的I0（類似5.6版本之前的處理過程）添加索引步驟：
1.新建帶索引（主鍵索引）的臨時表心
2.鎖原表，禁止DML，允許查詢
3.將原表數(shù)據(jù)拷貝到臨時表
4.禁止讀寫，進行rename，升級字典鎖
5.完成創(chuàng)建索引操作

LOCK有四種

LOCK=DEFAULT：默認(rèn)方式，MySQL自行判斷使用哪種LOCK模式，盡量不鎖表
LOCK=NONE：無鎖：允許Online DDL期間進行并發(fā)讀寫操作。通常與INPLACE搭配使用。如果Online DDL操作不支持對表的繼續(xù)寫入，則DDL操作失敗，對表修改無效
LOCK=SHARED：共享鎖：Online DDL操作期間堵塞寫入，不影響讀取
LOCK=EXCLUSIVE：排它鎖：Online DDL操作期間不允許對鎖表進行任何操作

接下來我們使用這兩種執(zhí)行方式來看一下是不是會全程鎖表。先是第一種的INPLACE：

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=INPLACE, LOCK=NONE;

先刪除剛剛的索引，然后開啟一個事務(wù)添加索引（不提交），執(zhí)行一下查詢事務(wù)（提交），是可以正常查詢的。再開啟一個修改事務(wù)（提交），是可以正常修改的。即，INPLACE方式是不會全程鎖表的。那么它在執(zhí)行的過程中會加鎖嗎？

還是刪除索引，然后開啟一個事務(wù)添加索引（不提交），執(zhí)行一下查詢事務(wù)（不提交），此時show一下執(zhí)行信息，發(fā)現(xiàn)DDL語句正在執(zhí)行ALTER語句，再show一下，發(fā)現(xiàn)DDL語句已經(jīng)被阻塞了，因為它在等待元數(shù)據(jù)鎖釋放，也就是說，DDL語句實際上還是會有個加鎖的過程。提交一下查詢事務(wù)，發(fā)現(xiàn)索引就添加成功了。

然后我們再次驗證一種場景，還是刪除索引，然后先開啟一個查詢事務(wù)（不提交）先持有元數(shù)據(jù)鎖，然后再開啟事務(wù)添加索引（不提交），這個時候show一下執(zhí)行信息，發(fā)現(xiàn)DDL語句正在等待元數(shù)據(jù)鎖釋放，提交一下查詢事務(wù)，show一下發(fā)現(xiàn)ddl語句正在往下執(zhí)行，這個時候再開啟一個修改事務(wù)（不提交），多show幾下直到DDL執(zhí)行完畢，發(fā)現(xiàn)我們的DDL語句也被這個修改事務(wù)阻塞了，因為它正在等待元數(shù)據(jù)鎖的釋放，這個時候我們再開一個修改事務(wù)（提交）、查詢事務(wù)（提交），show一下發(fā)現(xiàn)他們都被阻塞住。也就是說online ddl語句被阻塞后它就會進一步的將后續(xù)過來的DML事務(wù)全部阻塞住，將修改事務(wù)提交，索引就添加成功了。

所以日常使用的DDL語句的INPLACE方式執(zhí)行過程有兩次加鎖，加鎖過程中如果有其他事務(wù)持有了元數(shù)據(jù)鎖，DDL語句就會被阻塞，后續(xù)來的DML操作都會被阻塞住。

再看一下COPY方式：

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=COPY, LOCK=EXCLUSIVE;

還是刪除索引，然后開啟一個事務(wù)添加索引（不提交），然后執(zhí)行查詢事務(wù)（提交），修改事務(wù)（提交），執(zhí)行信息發(fā)現(xiàn)兩個DML語句都被阻塞。即COPY方式是全程鎖表的，它不允許DDL和DML的并發(fā)。

總結(jié)

從宏觀上看，Online DDL的事務(wù)相當(dāng)于會和其他事務(wù)并行執(zhí)行，只不過Online DDL會在表空閑時進行執(zhí)行，所以O(shè)nline DDL不會阻塞其他操作，在Online DDL執(zhí)行過程會兩次獲取MDL鎖（1.申請MDL寫鎖 2. 降級成MDL讀鎖 3.執(zhí)行DDL --耗時 4. 升級MDL寫鎖 5. 釋放MDL鎖），并且需要等待已經(jīng)持有MDL鎖的并發(fā)事務(wù)提交或回滾后才能繼續(xù)執(zhí)行，在實際執(zhí)行時需注意以下幾點：

• 進行DDL操作時盡量在業(yè)務(wù)低峰期進行操作。盡量的降低我們online ddll的阻塞時間，進而減少整個表的死鎖
• 在操作之前最好確認(rèn)對要操作的表沒有任何進行中的操作、沒有未提交事務(wù)、也沒有顯式事務(wù)中的報錯語句。
• 設(shè)置超時時間lock_wait_timeout，避免長時間的metedata鎖等待。

set global lock_wait_timeout = 60;
# 單位是s 默認(rèn)好像是一年show variables like '%timeout%';