創(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： 操作在原表的副本表上進行，表數(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文件時，會阻止讀?。ú粌H是寫），丟棄舊文件，從表&表定義緩存中清除過期表結(jié)構(gòu)。這時，它必須獨占鎖。為此，要等待當(dāng)前讀結(jié)束，并阻止新的讀寫。
使用COPY算法的ALTER TABLE操作可防止并發(fā)DML，允許并發(fā)查詢。也就是說，table-copying操作至少限制在LOCK=SHARED（允許查詢，但不允許DML）。LOCK=EXCLUSIVE則阻止DML和查詢，進一步限制了支持LOCK子句的操作的并發(fā)性。
參見Concurrency Control
Note
MySQL 5.6，LOCK在分區(qū)表上不支持 ALTER TABLE 操作
要強制將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列上增加、刪除索引的操作。這些操作在線進行，無需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 語法進行在線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)鍵字進行在線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ā)讀，則強制排它訪問。 -
LOCK = NONE
允許并發(fā)讀寫。如果不支持并發(fā)讀寫，則報錯。 -
LOCK = SHARED
允許并發(fā)讀取，不允許塊寫入。即便給定的ALGORITHM子句和 ALTER TABLE操作支持并發(fā)寫，也不可以。如果不支持并發(fā)讀，則報錯。 -
LOCK = EXCLUSIVE
強制排它訪問。即便給定的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)致操作失敗，而進行回滾。
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
