一：最初alter的複雜過程。

MySQL 5.5之前除了MySQL 5.1的 innodb plugin之外，對於索引的新增或刪除這類DDL操作，MySQL資料庫的操作過程為如下：

（1）首先建立新的臨時表，表結構透過命令ALTAR TABLE新定義的結構

（2）然後把原表中資料匯入到臨時表（不能讀和寫）

（3）刪除原表

（4）最後把臨時表重新命名為原來的表名

上述過程我們不難發現，若我們對一張大表進行索引的新增或者刪除，需要很長的時間，致命的是若有大量的訪問請求，意味著無法提供服務，5.5已經過時了，大家還是多關注5.6和5.7吧

二：快速索引建立：

官方文件中說明

In MySQL 5.5 and higher, or in MySQL 5.1 with the InnoDB Plugin, creating and dropping secondary indexes does not copy the contents of the entire table, making this operation much more efficient than with prior releases.

翻譯：在mysql 5.5或者更高版本，或者是mysql 5.1的InnoDB Plugin中，建立和刪除二級索引不需要複製整個表的資料來建立臨時表了，和之前的版本相比這類操作變得更加高效了；

innodb儲存引擎從1.0.x版本開始支援Fast index Creation（快速索引建立）。簡稱FIC。對於輔助索引的建立，會對建立索引的表加一個S鎖。在建立的過程中，不需要重建表，因此速度有明顯提升。對於刪除輔助索引innodb儲存引擎只需要更新內部檢視，並將輔助索引的空間標記為可用，同時刪除MySQL 資料庫內部檢視上對該表的索引定義即可。由於在建立輔助索引時加的是S鎖，所以在這過程中只能對該表進行讀操作，若有事務需要對該表進行寫操作，那麼資料庫服務同樣不可用。需要注意的是，FIC方式只限定於輔助索引，對於主鍵的建立和刪除同樣需要重建一張表；快速索引建立語句和正常alter語句沒有什麼不同；

三：online ddl （注意是針對innodb引擎而言的）

mysql 5.6以及以後的版本中，對於大多數我們日常常用的DDL而言，是可以做到線上DDL的。

通常情況下，可以使用預設的語法來進行線上DDL，但你也可以透過選項來改變DDL的行為，有兩個選項

LOCK=

ALGORITHM=[INPLACE|COPY]

關於這兩個引數的介紹：

ALGORITHM=INPLACE，可以避免重建錶帶來的IO和CPU消耗，保證ddl期間依然有良好的效能和併發。

ALGORITHM=COPY，需要複製原始表，所以不允許併發DML寫操作，可讀。這種copy方式的效率不如 inplace ，因為前者需要記錄undo和redo log，而且因為臨時佔用buffer pool引起短時間內效能受影響。

LOCK 選項控制是否鎖表，根據不同的DDL操作型別有不同的表現：預設mysql儘可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表，以下是具體的值的意義：

（1）NONE，執行索引建立或者刪除操作時，對目標表不新增任何鎖，即事務仍然可以進行讀寫操作，不會收到阻塞，該模式可以獲得最大的併發。

（2）SHARE，和Fast index Creation類似，執行索引建立或刪除操作時，對目標表加一個S鎖。對於併發讀事務，依然可以執行。但是遇到寫事務，將會發生等待操作，如果儲存引擎不支援SHARE模式，將返回一個錯誤資訊。

（3）EXCLUSIVE，執行索引建立或刪除時，對目標表加上一個X鎖。讀寫事務均不能進行。會阻塞所有的執行緒。這和COPY方式類似，但是不需要像COPY方式那樣建立一張臨時表。

（4）DEFAULT，該模式首先會判斷當前操作是否可以使用NONE模式，若不能，則判斷是否可以使用SHARE模式，最後判斷是否可以使用EXCLUSIVE模式。也就是說DEFAULT會透過判斷事務的最大併發性來判斷執行DDL的模式。

online ddl的語句：

alter table | ALGORITHM [=] {DEFAULT|INPLACE|COPY}， | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

那麼如何開啟online ddl？

由引數old_alter_table控制，

old_alter_table=0，不啟用舊的copy the table 的模式來進行ddl操作；

mysql 5.6預設 old_alter_table=0 ，就開啟了online ddl，可以使用預設的語法來進行線上DDL，

(題外話：關於set old_alter_table=0; 和 set global old_alter_table=0;的區別。前者隻影響當前session，後者作為全域性的修改方式，只會影響修改之後開啟的session；注意後者不能改變當前session；）

實驗一：

1.1

session 1

mysql> set old_alter_table=1;

Query OK, 0 rows affected (0.45 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | ON |

+-----------------+-------+

1 row in set (0.00 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

session 2 執行dml操作，被阻塞。

mysql> update v_member_info set phone='1771002222' where id=1;

檢視程式，發現果然是用舊的copy the table 的模式來進行ddl操作，然後update操作不能執行，等待一個metadata lock ；

mysql> show processlist;

+----+------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------+

1.2

session 1

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | OFF |

+-----------------+-------+

1 row in set (0.15 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

Query OK, 0 rows affected, 2 warnings (13.42 sec)

Records: 0 Duplicates: 0 Warnings: 2

session 2 執行dml操作，並沒有被阻塞，

mysql> update v_member_info set phone='1771002222' where id=1;

Query OK, 0 rows affected (0.02 sec)

Rows matched: 1 Changed: 0 Warnings: 0

mysql> show processlist;

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+

3 rows in set (0.07 sec)

實驗一表明：當old_alter_table=0的時候，就表示不用舊的那種copy the table 的模式來進行ddl操作，也就是開啟了online ddl。並且開啟online ddl之後，正常的alter命令新增索引，不會阻塞dml操作。由於不需要建立臨時表，online ddl效率很高；

實驗二：當old_alter_table=1的時候，ALGORITHM=INPLACE還有效嗎？

mysql> set old_alter_table=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | ON |

+-----------------+-------+

1 row in set (0.00 sec)

session 1 ：注意新增ALGORITHM =INPLACE引數後面有個逗號。

mysql> alter table v_member_info ALGORITHM =INPLACE,add index inde_register (register_ip) ;

session 2 並沒有阻塞dml操作；

mysql> update v_member_info set phone='1771002222' where id=1;

Query OK, 0 rows affected (0.40 sec)

Rows matched: 1 Changed: 0 Warnings: 0

檢視程式，發現沒有使用copy temp table的方式執行ddl

mysql> show processlist;

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

實驗二：結果表明，在mysql5.6中，噹噹old_alter_table=1的時候，可以使用ALGORITHM=INPLACE來影響ddl的執行方式，也就是說ALGORITHM=INPLACE的引數的優先順序高，依舊按著online ddl的方式建立索引，不建立臨時表（儘管old_alter_table=1）。lock引數也肯定一樣，實驗2本身就沒什麼意義，因為沒用人會把old_alter_table設定成1，而不用online ddl新特性。

實驗三：

session 1 開啟 online ddl，然後執行用copy的方式執行新增索引的操作；

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | OFF |

+-----------------+-------+

1 row in set (0.03 sec)

mysql> alter table v_member_info ALGORITHM =copy ,add index inde_register (register_date) ;

session 2阻塞dml操作；

mysql> update v_member_info set phone='1771002222' where id=1;

mysql> show processlist; 檢視程式發現確實在等待Waiting for table metadata lock

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+

3 rows in set (0.06 sec)

實驗三證明開啟online ddl之後，也可以使用copy to tmp table的方式建立索引，依舊會阻塞其他的dml操作。但是應該沒有這麼無聊的dba吧；

實驗四：驗證myisam引擎是否可以 online ddl

session 1

mysql> alter table v_member_info engine=myisam;

Query OK, 1804082 rows affected (1 min 50.33 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.03 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

Query OK, 1804082 rows affected (1 min 57.77 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

session 2 被阻塞

mysql> update v_member_info set phone='1771002222' where id=1;

session 3 檢視程式狀態，會看到新增索引的過程，剛開始copy to tmp table（建立臨時表）

mysql> show processlist;

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+

3 rows in set (0.00 sec)

實驗四證明：mysql 5.6中myisam引擎是不支援online ddl的，新增索引依舊會建立臨時表，阻塞其他session的dml操作；

關於online ddl的原理層面：

innodb儲存引擎實現Online DDL的原理是在執行建立或者刪除操作同時，將INSERT,UPDATE，DELETE這類DML操作日誌寫入到一個快取中，待完成索引建立後再將重做應用到表上，以此達到資料的一致性。這個快取的大小由引數innodb_online_alter_log_max_size控制，預設大小為128MB。

需要注意的是：如果待更新的表比較大，並且建立過程中有大量的寫事務，如果遇到innodb_online_alter_log_max_size的空間不能存放日誌時，會丟擲相應的錯誤，如果遇到這個錯誤，我們可以調大該引數，以此獲得更大的日誌快取空間，或者我們可以設定ALTER TABLE的lock模式為SHARE，這樣在執行過程中不會有寫操作事務發生。因此不需要進行DML日誌的記錄。

但是有一點需要說明，無論任何模式下，online ddl開始之前都需要一個短時間排它鎖(exclusive)來準備環境，所以alter命令發出後，會首先等待該表上的其它操作完成，在alter命令之後的請求會出現等待waiting meta data lock。同樣在ddl結束之前，也要等待alter期間所有的事務完成，也會堵塞一小段時間。所以儘量在ALTER TABLE之前確保沒有大事務在執行，否則一樣出現連環鎖表。你可以透過觀察執行完DDL後的輸出： XX rows affected，來判斷是IN-PLACE 還是COPY資料，為0的話就是inplace。

copy和inplace方式的具體過程（新增index為例）

copy方式

(1).新建帶索引的臨時表

(2).鎖原表，禁止DML，允許查詢

(3).將原表資料複製到臨時表(無排序，一行一行複製)

(4).進行rename，升級字典鎖，禁止讀寫

(5).完成建立索引操作

inplace方式（整個過程相對較快）

(1).新建索引的資料字典

(2).鎖表，禁止DML，允許查詢（這個過程非常短暫）

(3).讀取聚集索引，構造新的索引項，排序並插入新索引

(4).等待開啟當前表的所有隻讀事務提交

(5).建立索引結束

online ddl實現

online方式實質也包含了copy和inplace方式，對於不支援online的ddl操作採用copy方式，比如修改列型別，刪除主鍵，修改字符集等，這些操作都會導致記錄格式發生變化，無法透過簡單的全量+增量的方式實現online；對於inplace方式，mysql內部以“是否修改記錄格式”為基準也分為兩類，一類需要重建表(重新組織記錄)，比如optimize table、新增索引、新增/刪除列、修改列NULL/NOT NULL屬性等；另外一類是隻需要修改表的後設資料，比如刪除索引、修改列名、修改列預設值、修改列自增值等。Mysql將這兩類方式分別稱為rebuild方式和no-rebuild方式

2. 實現過程（注意這裡的rebuild是指從新組織記錄，是相對於只修改表的後設資料而言的）

online ddl主要包括3個階段，prepare階段，ddl執行階段，commit階段，rebuild(重新組織記錄)方式比no-rebuild（只需要修改表的後設資料）方式實質多了一個ddl執行階段，prepare階段和commit階段類似。下面將主要介紹ddl執行過程中三個階段的流程。
Prepare階段:
1）建立新的臨時frm檔案(與InnoDB無關)
2）持有EXCLUSIVE-MDL鎖，禁止讀寫
3）根據alter型別，確定執行方式(copy,online-rebuild,online-norebuild)

假如是Add Index，則選擇online-norebuild即INPLACE方式
1）更新資料字典的記憶體物件
2）分配row_log物件記錄增量(僅rebuild型別需要)
3）生成新的臨時ibd檔案(僅rebuild型別需要)
ddl執行階段:
1）降級EXCLUSIVE-MDL鎖，允許讀寫
2）掃描old_table的聚集索引每一條記錄rec
3）遍歷新表的聚集索引和二級索引，逐一處理
4）根據rec構造對應的索引項
5）將構造索引項插入sort_buffer塊排序
6）將sort_buffer塊更新到新的索引上
7）記錄ddl執行過程中產生的增量(僅rebuild型別需要)
8）重放row_log中的操作到新索引上(no-rebuild資料是在原表上更新的)
9）重放row_log間產生dml操作append到row_log最後一個Block
commit階段:
1）當前Block為row_log最後一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖
2）重做row_log中最後一部分增量
3）更新innodb的資料字典表
4）提交事務(刷事務的redo日誌)
5）修改統計資訊
6）rename臨時idb檔案，frm檔案
7）變更完成

從官方提供的這個表格來看，還是有很多操作不支援完全的線上DDL，包括增加一個全文索引，修改列的資料型別，刪除一個主鍵，修改表的字符集等。

Operation	In-Place?	是否重建表	允許併發DML	只修改後設資料?	Notes
CREATE INDEX, ADD INDEX	Yes*	No*	Yes	No	Restrictions apply for FULLTEXT indexes; see next row.
ADD FULLTEXT INDEX	Yes*	No*	No	No	Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.
DROP INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
OPTIMIZE TABLE	Yes*	Yes	Yes	No	Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXTindexes.
Set column default value	Yes	No	Yes	Yes	Only modifies table metadata.
Change auto-increment value	Yes	No	Yes	No*	Modifies a value stored in memory, not the data file.
Add foreign key constraint	Yes*	No	Yes	Yes	The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.
Drop foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks can be enabled or disabled.
Rename column	Yes	No	Yes*	Yes	To permit concurrent DML, keep the same data type and only change the column name.
Add column	Yes	Yes	Yes*	No	Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.
Drop column	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Reorder columns	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change ROW_FORMATproperty	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change KEY_BLOCK_SIZEproperty	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Make column NULL	Yes	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make column NOT NULL	Yes*	Yes*	Yes	No	Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.
Change column data type	No	Yes	No	No	Only supports ALGORITHM=COPY
Add primary key	Yes*	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.
Drop primary key and add another	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Drop primary key	No	Yes	No	No	Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.
Convert character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Specify character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Rebuild with FORCE option	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
“null” rebuild using ALTER TABLE ... ENGINE=INNODB	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statistics options	Yes	No	Yes	Yes	Only modifies table metadata.

從表看出，In-Place為No，DML一定是No，說明ALGORITHM=COPY一定會發生複製表，只讀。

ALGORITHM=INPLACEE也要可能發生複製表，但可以併發DML:

新增、刪除列，改變列順序
新增或刪除主鍵
改變行格式ROW_FORMAT和壓縮塊大小KEY_BLOCK_SIZE
改變列NULL或NOT NULL
最佳化表OPTIMIZE TABLE
強制 rebuild 該表

不允許併發DML的情況有：修改列資料型別、刪除主鍵、變更表字符集，即這些型別操作ddl是不能online的。

另外，更改主鍵索引與普通索引處理方式是不一樣的，主鍵即聚集索引，體現了表資料在物理磁碟上的排列，包含了資料行本身，需要複製表；而普通索引透過包含主鍵列來定位資料，所以普通索引的建立只需要一次掃描主鍵即可，而且是在已有資料的表上建立二級索引，更緊湊，將來查詢效率更高。

修改主鍵也就意味著要重建所有的普通索引。刪除二級索引更簡單，修改InnoDB系統表資訊和資料字典，標記該索引不存在，標記所佔用的表空間可以被新索引或資料行重新利用。

綜上所述：
儘管mysql 5.6開始以及之後版本中，innodb引擎支援所謂的 online ddl，但是並不是全部的ddl操作都支援online的，例如修改列資料型別、刪除主鍵、變更表字符集，即這些型別操作ddl是不能online的，需要注意的是單獨刪除主鍵雖不支援 online ddl，但是alter table v_member_info drop primary key , ADD PRIMARY KEY ( login_id ) ;這樣在一個語句中刪除原來的主鍵並且新增另一個列的主鍵，就支援online ddl，允許併發dml操作；

dba準則：操作生產環境之前應該先在測試環境做測試，DDL永遠不要在業務高峰期間執行。

MySQL Online DDL 概述

相關文章