這篇文章主要介紹“MySQL Online DDL知識(shí)點(diǎn)有哪些”，在日常操作中，相信很多人在MySQL Online DDL知識(shí)點(diǎn)有哪些問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MySQL Online DDL知識(shí)點(diǎn)有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！
成都創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站制作、做網(wǎng)站、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的關(guān)嶺網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
一：最初alter的復(fù)雜過(guò)程。
MySQL 5.5之前除了MySQL 5.1的 innodb plugin之外，對(duì)于索引的添加或刪除這類DDL操作，MySQL數(shù)據(jù)庫(kù)的操作過(guò)程為如下：
（1）首先創(chuàng)建新的臨時(shí)表，表結(jié)構(gòu)通過(guò)命令A(yù)LTAR TABLE新定義的結(jié)構(gòu)
（2）然后把原表中數(shù)據(jù)導(dǎo)入到臨時(shí)表（不能讀和寫）
（3）刪除原表
（4）最后把臨時(shí)表重命名為原來(lái)的表名
上述過(guò)程我們不難發(fā)現(xiàn)，若我們對(duì)一張大表進(jìn)行索引的添加或者刪除，需要很長(zhǎng)的時(shí)間，致命的是若有大量的訪問(wèn)請(qǐng)求，意味著無(wú)法提供服務(wù)，5.5已經(jīng)過(guò)時(shí)了，大家還是多關(guān)注5.6和5.7吧
二：快速索引創(chuàng)建：
官方文檔中說(shuō)明
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中，創(chuàng)建和刪除二級(jí)索引不需要復(fù)制整個(gè)表的數(shù)據(jù)來(lái)創(chuàng)建臨時(shí)表了，和之前的版本相比這類操作變得更加高效了；
innodb存儲(chǔ)引擎從1.0.x版本開始支持Fast index Creation（快速索引創(chuàng)建）。簡(jiǎn)稱FIC。對(duì)于輔助索引的創(chuàng)建，會(huì)對(duì)創(chuàng)建索引的表加一個(gè)S鎖。在創(chuàng)建的過(guò)程中，不需要重建表，因此速度有明顯提升。對(duì)于刪除輔助索引innodb存儲(chǔ)引擎只需要更新內(nèi)部視圖，并將輔助索引的空間標(biāo)記為可用，同時(shí)刪除MySQL 數(shù)據(jù)庫(kù)內(nèi)部視圖上對(duì)該表的索引定義即可。由于在創(chuàng)建輔助索引時(shí)加的是S鎖，所以在這過(guò)程中只能對(duì)該表進(jìn)行讀操作，若有事務(wù)需要對(duì)該表進(jìn)行寫操作，那么數(shù)據(jù)庫(kù)服務(wù)同樣不可用。需要注意的是，F(xiàn)IC方式只限定于輔助索引，對(duì)于主鍵的創(chuàng)建和刪除同樣需要重建一張表；快速索引創(chuàng)建語(yǔ)句和正常alter語(yǔ)句沒有什么不同；
三：online ddl （注意是針對(duì)innodb引擎而言的）
mysql 5.6以及以后的版本中，對(duì)于大多數(shù)我們?nèi)粘３Ｓ玫腄DL而言，是可以做到在線DDL的。
通常情況下，可以使用默認(rèn)的語(yǔ)法來(lái)進(jìn)行在線DDL，但你也可以通過(guò)選項(xiàng)來(lái)改變DDL的行為，有兩個(gè)選項(xiàng)
LOCK=
ALGORITHM=[INPLACE|COPY]
關(guān)于這兩個(gè)參數(shù)的介紹：
ALGORITHM=INPLACE，可以避免重建表帶來(lái)的IO和CPU消耗，保證ddl期間依然有良好的性能和并發(fā)。
ALGORITHM=COPY，需要拷貝原始表，所以不允許并發(fā)DML寫操作，可讀。這種copy方式的效率不如 inplace ，因?yàn)榍罢咝枰涗泆ndo和redo log，而且因?yàn)榕R時(shí)占用buffer pool引起短時(shí)間內(nèi)性能受影響。
LOCK 選項(xiàng)控制是否鎖表，根據(jù)不同的DDL操作類型有不同的表現(xiàn)：默認(rèn)mysql盡可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表，以下是具體的值的意義：
（1）NONE，執(zhí)行索引創(chuàng)建或者刪除操作時(shí)，對(duì)目標(biāo)表不添加任何鎖，即事務(wù)仍然可以進(jìn)行讀寫操作，不會(huì)收到阻塞，該模式可以獲得最大的并發(fā)。
（2）SHARE，和Fast index Creation類似，執(zhí)行索引創(chuàng)建或刪除操作時(shí)，對(duì)目標(biāo)表加一個(gè)S鎖。對(duì)于并發(fā)讀事務(wù)，依然可以執(zhí)行。但是遇到寫事務(wù)，將會(huì)發(fā)生等待操作，如果存儲(chǔ)引擎不支持SHARE模式，將返回一個(gè)錯(cuò)誤信息。
（3）EXCLUSIVE，執(zhí)行索引創(chuàng)建或刪除時(shí)，對(duì)目標(biāo)表加上一個(gè)X鎖。讀寫事務(wù)均不能進(jìn)行。會(huì)阻塞所有的線程。這和COPY方式類似，但是不需要像COPY方式那樣創(chuàng)建一張臨時(shí)表。
（4）DEFAULT，該模式首先會(huì)判斷當(dāng)前操作是否可以使用NONE模式，若不能，則判斷是否可以使用SHARE模式，最后判斷是否可以使用EXCLUSIVE模式。也就是說(shuō)DEFAULT會(huì)通過(guò)判斷事務(wù)的最大并發(fā)性來(lái)判斷執(zhí)行DDL的模式。
online ddl的語(yǔ)句：
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？
由參數(shù)old_alter_table控制，
old_alter_table=0，不啟用舊的copy the table 的模式來(lái)進(jìn)行ddl操作；
mysql 5.6默認(rèn) old_alter_table=0 ，就開啟了online ddl，可以使用默認(rèn)的語(yǔ)法來(lái)進(jìn)行在線DDL，
(題外話：關(guān)于set old_alter_table=0; 和 set global old_alter_table=0;的區(qū)別。前者只影響當(dāng)前session，后者作為全局的修改方式，只會(huì)影響修改之后打開的session；注意后者不能改變當(dāng)前session；）
實(shí)驗(yàn)一：
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 執(zhí)行dml操作，被阻塞。
mysql> update v_member_info set phone='1771002222' where id=1;
查看進(jìn)程，發(fā)現(xiàn)果然是用舊的copy the table 的模式來(lái)進(jìn)行ddl操作，然后update操作不能執(zhí)行，等待一個(gè)metadata lock ；
mysql> show processlist;
+----+------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------+
| 5 | root | localhost | liuwenhe | Query | 107 |copy to tmp table| alter table v_member_info modify register_ip varchar(50) |
| 6 | root | localhost | liuwenhe | Query | 17 |Waiting for table metadata lock| update v_member_info set phone='1771002222' where id=1 |
| 8 | root | localhost | NULL | Query | 0 | init | 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 執(zhí)行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;
+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
| 14 | root | localhost | liuwenhe | Query | 9 |altering table| alter table v_member_info add index inde_register (register_ip) |
| 18 | root | localhost | liuwenhe | Sleep | 6 | | NULL |
+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+
3 rows in set (0.07 sec)
實(shí)驗(yàn)一 表明：當(dāng)old_alter_table=0的時(shí)候，就表示不用舊的那種copy the table 的模式來(lái)進(jìn)行ddl操作，也就是開啟了online ddl。并且開啟online ddl之后，正常的alter命令添加索引，不會(huì)阻塞dml操作。由于不需要?jiǎng)?chuàng)建臨時(shí)表，online ddl效率很高；
實(shí)驗(yàn)二：當(dāng)old_alter_table=1的時(shí)候，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參數(shù)后面有個(gè)逗號(hào)。
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
查看進(jìn)程，發(fā)現(xiàn)沒有使用copy temp table的方式執(zhí)行ddl
mysql> show processlist;
+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+
| 20 | root | localhost | NULL | Sleep | 5053 | | NULL |
| 21 | root | localhost | liuwenhe | Query | 2 |altering table| alter table v_member_info ALGORITHM =INPLACE ,add index inde_register (register_ip) |
| 23 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
實(shí)驗(yàn)二：結(jié)果表明，在mysql5.6中，當(dāng)當(dāng)old_alter_table=1的時(shí)候，可以使用ALGORITHM=INPLACE來(lái)影響ddl的執(zhí)行方式，也就是說(shuō)ALGORITHM=INPLACE的參數(shù)的優(yōu)先級(jí)高，依舊按著online ddl的方式創(chuàng)建索引，不建立臨時(shí)表（盡管old_alter_table=1）。lock參數(shù)也肯定一樣，實(shí)驗(yàn)2本身就沒什么意義，因?yàn)闆]用人會(huì)把old_alter_table設(shè)置成1，而不用online ddl新特性。
實(shí)驗(yàn)三：
session 1 開啟 online ddl，然后執(zhí)行用copy的方式執(zhí)行添加索引的操作；
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; 查看進(jìn)程發(fā)現(xiàn)確實(shí)在等待Waiting for table metadata lock
+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+
| 28 | root | localhost | liuwenhe | Query | 60 |copy to tmp table| alter table v_member_info ALGORITHM =copy ,add index inde_register (register_date) |
| 29 | root | localhost | liuwenhe | Query | 20 |Waiting for table metadata lock| update v_member_info set phone='1771002222' where id=1 |
| 30 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+
3 rows in set (0.06 sec)
實(shí)驗(yàn)三證明開啟online ddl之后，也可以使用copy to tmp table的方式創(chuàng)建索引，依舊會(huì)阻塞其他的dml操作。但是應(yīng)該沒有這么無(wú)聊的dba吧；
實(shí)驗(yàn)四：驗(yàn)證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 查看進(jìn)程狀態(tài) ，會(huì)看到添加索引的過(guò)程，剛開始copy to tmp table（創(chuàng)建臨時(shí)表）
mysql> show processlist;
+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+
| 36 | root | localhost | liuwenhe | Query | 7 |copy to tmp table| alter table v_member_info add index inde_register (register_ip) |
| 37 | root | localhost | liuwenhe | Query | 5 | Waiting for table metadata lock | update v_member_info set phone='1771002222' where id=1 |
| 38 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)
實(shí)驗(yàn)四證明：mysql 5.6中myisam引擎是不支持online ddl的，添加索引依舊會(huì)創(chuàng)建臨時(shí)表，阻塞其他session的dml操作；
關(guān)于online ddl的原理層面：
innodb存儲(chǔ)引擎實(shí)現(xiàn)Online DDL的原理是在執(zhí)行創(chuàng)建或者刪除操作同時(shí)，將INSERT,UPDATE，DELETE這類DML操作日志寫入到一個(gè)緩存中，待完成索引創(chuàng)建后再將重做應(yīng)用到表上，以此達(dá)到數(shù)據(jù)的一致性。這個(gè)緩存的大小由參數(shù)innodb_online_alter_log_max_size控制，默認(rèn)大小為128MB。
需要注意的是：如果待更新的表比較大，并且創(chuàng)建過(guò)程中有大量的寫事務(wù)，如果遇到innodb_online_alter_log_max_size的空間不能存放日志時(shí)，會(huì)拋出相應(yīng)的錯(cuò)誤，如果遇到這個(gè)錯(cuò)誤，我們可以調(diào)大該參數(shù)，以此獲得更大的日志緩存空間，或者我們可以設(shè)置ALTER TABLE的lock模式為SHARE，這樣在執(zhí)行過(guò)程中不會(huì)有寫操作事務(wù)發(fā)生。因此不需要進(jìn)行DML日志的記錄。
但是有一點(diǎn)需要說(shuō)明，無(wú)論任何模式下，online ddl開始之前都需要一個(gè)短時(shí)間排它鎖(exclusive)來(lái)準(zhǔn)備環(huán)境，所以alter命令發(fā)出后，會(huì)首先等待該表上的其它操作完成，在alter命令之后的請(qǐng)求會(huì)出現(xiàn)等待waiting meta data lock。同樣在ddl結(jié)束之前，也要等待alter期間所有的事務(wù)完成，也會(huì)堵塞一小段時(shí)間。所以盡量在ALTER TABLE之前確保沒有大事務(wù)在執(zhí)行，否則一樣出現(xiàn)連環(huán)鎖表。你可以通過(guò)觀察執(zhí)行完DDL后的輸出： XX rows affected，來(lái)判斷是IN-PLACE 還是COPY數(shù)據(jù)，為0的話就是inplace。
copy和inplace方式的具體過(guò)程（添加index為例）
copy方式
(1).新建帶索引的臨時(shí)表
(2).鎖原表，禁止DML，允許查詢
(3).將原表數(shù)據(jù)拷貝到臨時(shí)表(無(wú)排序，一行一行拷貝)
(4).進(jìn)行rename，升級(jí)字典鎖，禁止讀寫
(5).完成創(chuàng)建索引操作
inplace方式（整個(gè)過(guò)程相對(duì)較快）
(1).新建索引的數(shù)據(jù)字典
(2).鎖表，禁止DML，允許查詢（這個(gè)過(guò)程非常短暫）
(3).讀取聚集索引，構(gòu)造新的索引項(xiàng)，排序并插入新索引
(4).等待打開當(dāng)前表的所有只讀事務(wù)提交
(5).創(chuàng)建索引結(jié)束
online ddl實(shí)現(xiàn)
online方式實(shí)質(zhì)也包含了copy和inplace方式，對(duì)于不支持online的ddl操作采用copy方式，比如修改列類型，刪除主鍵，修改字符集等，這些操作都會(huì)導(dǎo)致記錄格式發(fā)生變化，無(wú)法通過(guò)簡(jiǎn)單的全量+增量的方式實(shí)現(xiàn)online；對(duì)于inplace方式，mysql內(nèi)部以“是否修改記錄格式”為基準(zhǔn)也分為兩類，一類需要重建表(重新組織記錄)，比如optimize table、添加索引、添加/刪除列、修改列NULL/NOT NULL屬性等；另外一類是只需要修改表的元數(shù)據(jù)，比如刪除索引、修改列名、修改列默認(rèn)值、修改列自增值等。Mysql將這兩類方式分別稱為rebuild方式和no-rebuild方式
2. 實(shí)現(xiàn)過(guò)程（注意這里的rebuild是指從新組織記錄，是相對(duì)于只修改表的元數(shù)據(jù)而言的）
online ddl主要包括3個(gè)階段，prepare階段，ddl執(zhí)行階段，commit階段，rebuild(重新組織記錄)方式比no-rebuild（只需要修改表的元數(shù)據(jù)）方式實(shí)質(zhì)多了一個(gè)ddl執(zhí)行階段，prepare階段和commit階段類似。下面將主要介紹ddl執(zhí)行過(guò)程中三個(gè)階段的流程。
Prepare階段:
1）創(chuàng)建新的臨時(shí)frm文件(與InnoDB無(wú)關(guān))
2）持有EXCLUSIVE-MDL鎖，禁止讀寫
3）根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)
假如是Add Index，則選擇online-norebuild即INPLACE方式
1）更新數(shù)據(jù)字典的內(nèi)存對(duì)象
2）分配row_log對(duì)象記錄增量(僅rebuild類型需要)
3）生成新的臨時(shí)ibd文件(僅rebuild類型需要)
ddl執(zhí)行階段:
1）降級(jí)EXCLUSIVE-MDL鎖，允許讀寫
2）掃描old_table的聚集索引每一條記錄rec
3）遍歷新表的聚集索引和二級(jí)索引，逐一處理
4）根據(jù)rec構(gòu)造對(duì)應(yīng)的索引項(xiàng)
5）將構(gòu)造索引項(xiàng)插入sort_buffer塊排序
6）將sort_buffer塊更新到新的索引上
7）記錄ddl執(zhí)行過(guò)程中產(chǎn)生的增量(僅rebuild類型需要)
8）重放row_log中的操作到新索引上(no-rebuild數(shù)據(jù)是在原表上更新的)
9）重放row_log間產(chǎn)生dml操作append到row_log最后一個(gè)Block
commit階段:
1）當(dāng)前Block為row_log最后一個(gè)時(shí)，禁止讀寫，升級(jí)到EXCLUSIVE-MDL鎖
2）重做row_log中最后一部分增量
3）更新innodb的數(shù)據(jù)字典表
4）提交事務(wù)(刷事務(wù)的redo日志)
5）修改統(tǒng)計(jì)信息
6）rename臨時(shí)idb文件，frm文件
7）變更完成
從官方提供的這個(gè)表格來(lái)看，還是有很多操作不支持完全的在線DDL，包括增加一個(gè)全文索引，修改列的數(shù)據(jù)類型，刪除一個(gè)主鍵，修改表的字符集等。
| Operation | In-Place? | 是否重建表 | 允許并發(fā)DML | 只修改元數(shù)據(jù)? | 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，說(shuō)明ALGORITHM=COPY一定會(huì)發(fā)生拷貝表，只讀。
ALGORITHM=INPLACEE也要可能發(fā)生拷貝表，但可以并發(fā)DML:
添加、刪除列，改變列順序
添加或刪除主鍵
改變行格式ROW_FORMAT和壓縮塊大小KEY_BLOCK_SIZE
改變列NULL或NOT NULL
優(yōu)化表OPTIMIZE TABLE
強(qiáng)制 rebuild 該表
不允許并發(fā)DML的情況有：修改列數(shù)據(jù)類型、刪除主鍵、變更表字符集，即這些類型操作ddl是不能online的。
另外，更改主鍵索引與普通索引處理方式是不一樣的，主鍵即聚集索引，體現(xiàn)了表數(shù)據(jù)在物理磁盤上的排列，包含了數(shù)據(jù)行本身，需要拷貝表；而普通索引通過(guò)包含主鍵列來(lái)定位數(shù)據(jù)，所以普通索引的創(chuàng)建只需要一次掃描主鍵即可，而且是在已有數(shù)據(jù)的表上建立二級(jí)索引，更緊湊，將來(lái)查詢效率更高。
修改主鍵也就意味著要重建所有的普通索引。刪除二級(jí)索引更簡(jiǎn)單，修改InnoDB系統(tǒng)表信息和數(shù)據(jù)字典，標(biāo)記該索引不存在，標(biāo)記所占用的表空間可以被新索引或數(shù)據(jù)行重新利用。
到此，關(guān)于“MySQL Online DDL知識(shí)點(diǎn)有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！
重慶分公司
重慶分公司
