創(chuàng)建索引
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE
Performance and Space Requirements
ALTER TABLE操作有兩種算法（ALGORITHM）：
COPY： 操作在原表的副本表上進(jìn)行没龙，表數(shù)據(jù)逐行拷貝自原表移必。不支持并發(fā)DML孩等。
INPLACE： 操作避免復(fù)制表數(shù)據(jù)传惠，但可能會就地rebuild表。在準(zhǔn)備和執(zhí)行階段南用，可能會短暫地獨占元數(shù)據(jù)鎖耗跛。通常口柳，支持并發(fā)DML
ALGORITHM 子句是可選項缠俺。如果沒有指定ALGORITHM 显晶，或者指定ALGORITHM=DEFAULT，MySQL則在支持的引擎和alter操作使用 ALGORITHM=INPLACE晋修；否則使用 ALGORITHM=COPY 吧碾。
注意
分區(qū)表不支持ALGORITHM=DEFAULT,ALGORITHM=INPLACE, andALGORITHM=COPY凰盔。
MySQL 5.6.11及以后的版本墓卦，分區(qū)表使用ALGORITHM=1andALGORITHM=2來升級/降級。
參見Section 13.1.7.1, “ALTER TABLE Partition Operations”户敬。
指定ALGORITHM子句需要使用的操作和存儲引擎支持落剪，否則會報錯。指派ALGORITHM=DEFAULT等同于忽略配置尿庐。
使用COPY算法的ALTER TABLE操作忠怖，依賴其他表修改操作來完成。在副本表更改后抄瑟，將復(fù)制數(shù)據(jù)凡泣，刪除原始表，并將副本表重命名為原始表的名稱皮假。在執(zhí)行 ALTER TABLE 操作時鞋拟，其他會話可以讀取原始表 (with the exception noted shortly)。在ALTER table操作開始后惹资，對表的更新和寫入被暫停贺纲，直到新表準(zhǔn)備就緒，然后自動重定向到新表褪测。臨時表在原始表的同目錄中創(chuàng)建猴誊，除非RENAME TO到其他目錄。
前面提到的 exception 侮措，ALTER TABLE 在準(zhǔn)備安裝新版本的表.frm文件時懈叹，會阻止讀取（不僅是寫）分扎，丟棄舊文件澄成，從表&表定義緩存中清除過期表結(jié)構(gòu)。這時，它必須獨占鎖环揽。為此略荡，要等待當(dāng)前讀結(jié)束，并阻止新的讀寫歉胶。
使用COPY算法的ALTER TABLE操作可防止并發(fā)DML汛兜，允許并發(fā)查詢。也就是說通今，table-copying操作至少限制在LOCK=SHARED（允許查詢粥谬，但不允許DML）。LOCK=EXCLUSIVE則阻止DML和查詢辫塌，進(jìn)一步限制了支持LOCK子句的操作的并發(fā)性漏策。
參見Concurrency Control
Note
MySQL 5.6，LOCK在分區(qū)表上不支持 ALTER TABLE 操作
要強(qiáng)制將COPY算法用于本來不使用其的ALTER TABLE操作臼氨，需要開啟 old_alter_table 系統(tǒng)變量掺喻，或指定ALGORITHM=COPY。如果old_alter_table和非默認(rèn)值的ALGORITHM沖突储矩，則ALGORITHM子句優(yōu)先感耙。
對于InnoDB表，對駐留在共享表空間（如 system tablespace）中的表,使用COPY算法的ALTER TABLE操作會增加表空間的使用量持隧。這些操作需要同增加索引一樣多的額外空間即硼。對于駐留在共享表空間中的表，操作使用的額外空間不會釋放回操作系統(tǒng)屡拨，as it is for a table that resides in a file-per-table tablespace只酥。
關(guān)于空間需求參見Section 14.13.3, “Online DDL Space Requirements”
使用INPLACE算法的ALTER TABLE操作：
InnoDBonline DDL特性支持的ALTER TABLE操作。參見 Section 14.13.1, “Online DDL Operations”.重命名表呀狼。MySQL重命名與表
tbl_name相關(guān)的文件裂允，無需拷貝。也可以使用RENAME TABLE來重命名表赠潦。參見 Section 13.1.32, “RENAME TABLE Statement”.) 叫胖。權(quán)限需要手動修改，原表權(quán)限不會遷移到重命名后的表她奥。-
只修改表元數(shù)據(jù)的操作瓮增。這些是實時操作，因為服務(wù)器僅更改表
.frm文件哩俭，不創(chuàng)建表內(nèi)容绷跑。元數(shù)據(jù)操作包含：- 重命名列
- 改變列的默認(rèn)值 ，
NDB表不適用凡资。參見Limitations of NDB online operations砸捏。 - 修改枚舉
ENUMor集合SET列的定義谬运，通過在有效成員值列表的末尾添加新的枚舉或集合成員，只要數(shù)據(jù)類型的存儲大小不變垦藏。比如梆暖，添加一個成員到擁有8個成員的某SET列，會將每個值所需的存儲空間從1字節(jié)更改為2字節(jié)掂骏，那么需要table copy轰驳。 在列表中間添加成員會導(dǎo)致現(xiàn)有成員重新編號，也需要table copy弟灼。
在
InnoDB和NDB表级解，創(chuàng)建、刪除二級索引田绑。參見Section 14.13, “InnoDB and Online DDL”勤哗。對于
NDB表，在variable-width列上增加掩驱、刪除索引的操作芒划。這些操作在線進(jìn)行，無需table copying 昙篙，并且在大部分時間內(nèi)不會阻止并發(fā)DML操作腊状。參見 Section 18.6.11, “Online Operations with ALTER TABLE in NDB Cluster”诱咏。
從MySQL 5.6.16開始苔可，對于ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE操作，ALTER TABLE會將MySQL 5.5臨時列升級為5.6格式袋狞。無法使用 INPLACE算法完成此轉(zhuǎn)換焚辅，因為必須rebuild表，因此該情況下指定ALGORITHM=INPLACE會報錯苟鸯。如果需要同蜻，指定 ALGORITHM=COPY。
NDB Cluster支持在MySQL NDB Cluster 7.3及更高版本中早处，使用 ALGORITHM=INPLACE 語法進(jìn)行在線ALTER TABLE操作湾蔓。NDB集群還支持NDB特有的舊語法的ONLINE and OFFLINE關(guān)鍵字。這些關(guān)鍵字從MySQL NDB Cluster 7.3開始就被棄用砌梆；MySQL NDB Cluster 7.4中仍然支持它們默责，但在NDB Cluster的未來版本中可能會刪除它們。參見 Section 18.6.11, “Online Operations with ALTER TABLE in NDB Cluster”咸包。
根據(jù)KEY來對表分區(qū)的多列索引桃序，如果ALTER TABLE操作作用于該多列索引上變更了列的順序，則只能使用ALGORITHM=COPY烂瘫。
MySQL NDB Cluster 7.3（及更高版本）支持使用ALGORITHM=INPLACE語法（與標(biāo)準(zhǔn)MySQL服務(wù)器相同）的在線操作媒熊。NDB Cluster 7.3和7.4還繼續(xù)支持使用ONLINE and OFFLINE關(guān)鍵字進(jìn)行在線ALTER TABLE操作，以實現(xiàn)向后兼容性，但NDB 7.3和7.4中不推薦使用這些關(guān)鍵字芦鳍，并且從NDB 7.5起不再支持這些關(guān)鍵字嚷往。NDB不支持在線變更表空間。參見Section 18.6.11, “Online Operations with ALTER TABLE in NDB Cluster”柠衅。
ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION這些ALTER TABLE 操作不會創(chuàng)建臨時表 (NDB表除外);然而间影， 這些操作確實可以創(chuàng)建臨時分區(qū)文件。
對于RANGE or LIST分區(qū)茄茁，ADD or DROP是實時或幾乎實時的操作魂贬。
對于HASH or KEY分區(qū)，ADD or COALESCE操作從所有分區(qū)拷貝數(shù)據(jù)裙顽，除非使用了LINEAR HASH or LINEAR KEY付燥；這實際上與創(chuàng)建新表相同，盡管ADD or COALESCE操作是逐個分區(qū)執(zhí)行的愈犹。
REORGANIZE操作只復(fù)制已更改的分區(qū)键科，不接觸未更改的分區(qū)。
對于MyISAM表漩怎，通過將myisam_sort_buffer_size系統(tǒng)變量調(diào)高勋颖，可以加快索引的重建速度，這是alter過程中最慢的部分勋锤。
并發(fā)控制 Concurrency Control
對于支持Concurrency Control的ALTER TABLE操作饭玲，可以使用LOCK子句在表被alter時控制表上并發(fā)讀寫的級別。
非默認(rèn)值的指定叁执，使得在alter操作期間茄厘，您可以請求一定量的并發(fā)/獨占訪問，并可以在請求的鎖定程度不再可用時停止操作谈宛。
-
LOCK = DEFAULT
如果支持次哈，允許并發(fā)讀取。
如果不支持并發(fā)讀取吆录，支持并發(fā)讀窑滞，則允許并發(fā)讀。
如果也不支持并發(fā)讀恢筝，則強(qiáng)制排它訪問哀卫。 -
LOCK = NONE
允許并發(fā)讀寫。如果不支持并發(fā)讀寫滋恬，則報錯聊训。 -
LOCK = SHARED
允許并發(fā)讀取，不允許塊寫入恢氯。即便給定的ALGORITHM子句和 ALTER TABLE操作支持并發(fā)寫带斑，也不可以鼓寺。如果不支持并發(fā)讀，則報錯勋磕。 -
LOCK = EXCLUSIVE
強(qiáng)制排它訪問妈候。即便給定的ALGORITHM子句和 ALTER TABLE操作支持并發(fā)讀/寫，也要執(zhí)行此操作挂滓。
online DDL的空間要求：
由于online DDL執(zhí)行期間需要創(chuàng)建臨時表空間文件用于存儲數(shù)據(jù)苦银，以及申請row log記錄DML操作，所以在執(zhí)行DDL前應(yīng)該先確認(rèn)空間上是否滿足要求赶站，否則由于空間不夠很可能導(dǎo)致操作失敗幔虏，而進(jìn)行回滾。
1贝椿、row log空間：row log空間每次申請的大小由 innodb_sort_buffer_size決定想括，最大值由innodb_online_alter_log_max_size，該值默認(rèn)為128M烙博，支持動態(tài)修改瑟蜈。對于更新頻繁的表來講，如果預(yù)計在DDL期間對表的更新操作存儲可能超過128M時渣窜，需要為本次操作增大該值铺根。當(dāng)然如果不涉及rebuild操作時，不需要考慮該值乔宿。如果提示DB_ONLINE_LOG_TOO_BIG錯誤位迂，則是由innodb_online_alter_log_max_size空間不足造成的。
2予颤、索引排序空間：如果DDL操作涉及二級索引的創(chuàng)建囤官，會在MySQL臨時目錄產(chǎn)生臨時排序文件，將中間的排序結(jié)果寫入文件蛤虐，最終將內(nèi)容合并到最終表或索引中，然后自動刪除臨時排序文件肝陪。這個路徑默認(rèn)為mysql全局參數(shù)tmpdir指定（默認(rèn)值為/tmp驳庭，如果手動指定了innodb_tmpdir參數(shù)的路徑，則tmpdir會被覆蓋）氯窍，且不會在原始表的目錄中創(chuàng)建臨時排序文件饲常。tmpdir需要保證能夠容納要創(chuàng)建的二級索引，臨時排序文件最大可能需要的空間等于表中的數(shù)據(jù)量加上索引狼讨，否則執(zhí)行將報錯贝淤。（官方文檔的說明，實際測試200萬的表加索引政供，并未生成臨時排序文件播聪，這有點奇怪）
3朽基、中間表空間：如果DDL操作涉及rebuild表，則會在原表所在目錄創(chuàng)建臨時表空間文件（以#sql開頭）离陶，臨時表空間大小需要等于原表大小稼虎，重建完成后會自動重命名臨時表空間，刪除原表空間招刨。所以執(zhí)行rebuild操作時需要保證原表所在路徑下有足夠空間
參考資料 ：
https://c4ys.com/archives/1943
MySQL 5.6 Reference Manual / The InnoDB Storage Engine
InnoDB and Online DDL
Online DDL Operations
CREATE INDEX Statement
ALTER TABLE Statement
