一 .Fast index Creation
MySQL 5.5和更高版本并且MySQL 5.1 innodb plugin支持Fast index Creation妄壶，對于之前的版本對于索引的添加或刪除這類DDL操作蘸泻，MySQL數(shù)據(jù)庫的操作過程為如下：
（1）首先創(chuàng)建新的臨時(shí)表诵棵，表結(jié)構(gòu)通過命令A(yù)LTAR TABLE新定義的結(jié)構(gòu)
（2）然后把原表中數(shù)據(jù)導(dǎo)入到臨時(shí)表
（3）刪除原表
（4）最后把臨時(shí)表重命名為原來的表名
上述過程我們不難發(fā)現(xiàn)尘应，若我們對一張大表進(jìn)行索引的添加或者刪除，需要很長的時(shí)間茉帅，致命的是若有大量的訪問請求齐佳，意味著無法提供服務(wù)。
innodb存儲(chǔ)引擎從1.0.x版本開始支持Fast index Creation（快速索引創(chuàng)建）填硕。簡稱FIC麦萤。對于輔助索引的創(chuàng)建，會(huì)對創(chuàng)建索引的表加一個(gè)S鎖扁眯。在創(chuàng)建的過程中壮莹，不需要重建表，因此速度有明顯提升姻檀。對于刪除輔助索引innodb存儲(chǔ)引擎只需要更新內(nèi)部視圖命满，并將輔助索引的空間標(biāo)記為可用，同時(shí)刪除MySQL 數(shù)據(jù)庫內(nèi)部視圖上對該表的索引定義即可绣版。特別需要注意的時(shí)胶台，臨時(shí)表的創(chuàng)建路徑是通過參數(shù)tmpdir設(shè)置的。必須確保tmpdir有足夠的空間杂抽，否則將會(huì)導(dǎo)致輔助索引創(chuàng)建失敗诈唬。由于在創(chuàng)建輔助索引時(shí)加的是S鎖，所以在這過程中只能對該表進(jìn)行讀操作缩麸，若有事務(wù)需要對該表進(jìn)行寫操作铸磅，那么數(shù)據(jù)庫服務(wù)同樣不可用。需要注意的是，F(xiàn)IC方式只限定于輔助索引愚屁，對于主鍵的創(chuàng)建和刪除同樣需要重建一張表济竹。
二 . Oline Schema Change
Online Schema Change（在線架構(gòu)改變，簡稱OSC）霎槐，最早是由Facebook實(shí)現(xiàn)的一種在線DDL的方式。所謂"在線"是指在添加字段梦谜，添加索引這類DDL操作時(shí)丘跌，事務(wù)對表的讀寫操作不會(huì)受到阻塞。
三 .Online DDL
FIC可以讓innodb存儲(chǔ)引擎避免創(chuàng)建臨時(shí)表唁桩，提高索引創(chuàng)建效率闭树。雖然FIC不會(huì)阻塞讀操作，但是DML操作還是照樣阻塞的荒澡。MySQL 5.6版本開始支持Online DDL（在線數(shù)據(jù)定義）操作报辱，其允許輔助索引創(chuàng)建的同時(shí)，還允許其他諸如INSERT,UPDATE,DELETE這類DML操作单山。此外不僅是輔助索引碍现，以下這幾類DDL操作都可以通過”在線“的方式進(jìn)行：
（1）輔助索引的創(chuàng)建于刪除
（2）改變自增長值
（3）添加或刪除外鍵約束
（4）列的重命名
通過新的ALTER TABLE，可以選擇索引的創(chuàng)建方式
mysql [localhost] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 5.6.19 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
? 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]
ALGORITHM指定了創(chuàng)建或刪除索引的算法米奸，COPY表示按照MySQL 5.1版本之前的方法昼接，即創(chuàng)建臨時(shí)表。INPLACE表示創(chuàng)建索引或刪除索引操作不需要?jiǎng)?chuàng)建臨時(shí)表悴晰。DEFAULT表示根據(jù)參數(shù)old_alter_table來判斷是通過INPLACE還是COPY的算法慢睡，改參數(shù)默認(rèn)為OFF，表示采用INPLACE的方式
mysql [localhost] {msandbox} ((none)) > show variables like '%old_alter%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| old_alter_table | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
LOCK部分為索引創(chuàng)建或刪除時(shí)對表添加鎖的情況铡溪，可選擇的如下：
（1）NONE漂辐，執(zhí)行索引創(chuàng)建或者刪除操作時(shí)，對目標(biāo)表不添加任何鎖棕硫，即事務(wù)仍然可以進(jìn)行讀寫操作髓涯，不會(huì)收到阻塞，該模式可以獲得最大的并發(fā)饲帅。
（2）SHARE复凳，和Fast index Creation類似，執(zhí)行索引創(chuàng)建或刪除操作時(shí)灶泵，對目標(biāo)表加一個(gè)S鎖育八。對于并發(fā)讀事務(wù)，依然可以執(zhí)行赦邻。但是遇到寫事務(wù)髓棋，將會(huì)發(fā)生等待操作，如果存儲(chǔ)引擎不支持SHARE模式，將返回一個(gè)錯(cuò)誤信息按声。
（3）EXCLUSIVE膳犹，執(zhí)行索引創(chuàng)建或刪除時(shí)，對目標(biāo)表加上一個(gè)X鎖签则。讀寫事務(wù)均不能進(jìn)行须床。會(huì)阻塞所有的線程。這和COPY方式類似渐裂，但是不需要像COPY方式那樣創(chuàng)建一張臨時(shí)表豺旬。
（4）DEFAULT，該模式首先會(huì)判斷當(dāng)前操作是否可以使用NONE模式柒凉，若不能族阅，則判斷是否可以使用SHARE模式，最后判斷是否可以使用EXCLUSIVE模式膝捞。也就是說DEFAULT會(huì)通過判斷事務(wù)的最大并發(fā)性來判斷執(zhí)行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。
mysql [localhost] {msandbox} ((none)) > show variables like '%online%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > select 134217728 / 1024 / 1024;
+-------------------------+
| 134217728 / 1024 / 1024 |
+-------------------------+
| 128.00000000 |
+-------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
如果待更新的表比較大北启，并且創(chuàng)建過程中有大量的寫事務(wù)卜朗，如果遇到innodb_online_alter_log_max_size的空間不能存放日志時(shí)，會(huì)拋出相應(yīng)的錯(cuò)誤咕村，這個(gè)我們后面進(jìn)行測試场钉。
如果遇到改錯(cuò)誤，我們可以調(diào)大該參數(shù)懈涛，以此獲得更大的日志緩存空間逛万。此外我們可以設(shè)置ALTER TABLE的模式為SHARE，這樣在執(zhí)行過程中不會(huì)有寫事務(wù)發(fā)生批钠。因此不需要進(jìn)行DML日志的記錄宇植。
通過上面的簡單說明，相信大家心里都有譜了埋心。那我們來實(shí)際測試一下指郁。我這里使用sysbench生成1000w行測試數(shù)據(jù)
[root@mysql-server ~]# sysbench --test=oltp --oltp-table-size=10000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=1800 --mysql-user=msandbox --mysql-socket=/tmp/mysql_sandbox5619.sock --mysql-password=msandbox --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex prepare
1.首先測試添加一個(gè)輔助索引
在session 1中執(zhí)行添加索引操作，在session 2中執(zhí)行DML操作拷呆；
session 1 （alter table選擇默認(rèn)的執(zhí)行方式闲坎，即讓innodb存儲(chǔ)引擎自行判斷該加什么鎖）
mysql [localhost] {msandbox} (sbtest) > select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.28 sec)
mysql [localhost] {msandbox} (sbtest) > show create table sbtest\G
*************************** 1. row ***************************
Table: sbtest
Create Table: CREATE TABLE `sbtest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=latin1
1 row in set (0.04 sec)
mysql [localhost] {msandbox} (sbtest) >
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add key idx_pad ( pad );
session 2（可以發(fā)現(xiàn)并未鎖表疫粥，一切正常）
mysql [localhost] {msandbox} (sbtest) > delete from sbtest where id=10;
Query OK, 1 row affected (0.16 sec)
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| 17 | msandbox | localhost | sbtest | Query | 4 | altering table | alter table sbtest add key idx_pad ( pad ) |
| 18 | msandbox | localhost | sbtest | Query | 0 | init | show processlist |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (sbtest) > update sbtest set k=11 where id=100;
Query OK, 1 row affected (1.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| 17 | msandbox | localhost | sbtest | Query | 53 | altering table | alter table sbtest add key idx_pad ( pad ) |
| 18 | msandbox | localhost | sbtest | Query | 0 | init | show processlist |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
2 rows in set (0.26 sec)
mysql [localhost] {msandbox} (sbtest) >
2.測試添加一個(gè)字段
session 1
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add age int after pad;
session 2
mysql [localhost] {msandbox} (sbtest) > delete from sbtest where id=20;
Query OK, 1 row affected (1.02 sec)
mysql [localhost] {msandbox} (sbtest) > update sbtest set k=101 where id=1111;
Query OK, 1 row affected (1.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
| 23 | msandbox | localhost | sbtest | Query | 120 | altering table | alter table sbtest add age int after pad |
| 24 | msandbox | localhost | sbtest | Query | 1 | init | show processlist |
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
2 rows in set (0.38 sec)
mysql [localhost] {msandbox} (sbtest) >
可以發(fā)現(xiàn)添加字段依然不會(huì)影響DML操作。是不是很爽腰懂？爽的話就升級吧梗逮。
如果我們在mysql 5.5中添加字段會(huì)是怎樣的情況呢？在mysql 5.5中添加字段是會(huì)鎖表的绣溜，讀寫都阻塞（增加慷彤，刪除索引會(huì)加S鎖，阻塞寫操作）怖喻。如果還沒有使用mysql 5.6的同學(xué)也不用擔(dān)心瞬欧，因?yàn)槟壳坝袃蓚€(gè)工具非常好用：oak-online-alter-table和pt-online-schema-change現(xiàn)在來看看mysql 5.5添加字段的情況
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.03 sec)
mysql>
mysql> alter table sbtest add address char(30) after pad;
另外一個(gè)會(huì)話查看
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+
| 9 | root | localhost | sbtest | Query | 6 | copy to tmp table | alter table sbtest add address char(30) after pad |
| 10 | root | localhost | sbtest | Query | 4 | Waiting for table metadata lock | delete from sbtest where id=100 |
| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
可以看見鎖表了，并且在創(chuàng)建臨時(shí)表罢防。
不過MySQL 5.6不是一定不會(huì)鎖表，有種特殊情況唉侄，那就是如果有一條大結(jié)果的查詢在查詢某個(gè)表咒吐，這時(shí)如果執(zhí)行ALTER TABLE時(shí)，是會(huì)鎖表的属划。我們做一個(gè)簡單測試恬叹。
session 1
mysql [localhost] {msandbox} (sbtest) > select * from sbtest;
session 2
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add age int after pad;
session 3
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+
| 27 | msandbox | localhost | sbtest | Query | 5 | Sending data | select * from sbtest |
| 28 | msandbox | localhost | sbtest | Query | 3 | Waiting for table metadata lock | alter table sbtest add age int after pad |
| 29 | msandbox | localhost | sbtest | Query | 0 | init | show processlist |
+----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+
3 rows in set (0.22 sec)
mysql [localhost] {msandbox} (sbtest) >
可以看見已經(jīng)導(dǎo)致鎖表咯。所以同眯，我們在上線的時(shí)候绽昼，一定要觀察是否有某個(gè)慢SQL或者比較大的結(jié)果集的SQL在運(yùn)行，否則在執(zhí)行ALTER TABLE時(shí)將會(huì)導(dǎo)致鎖表發(fā)生须蜗。當(dāng)然不清楚oak-online-alter-table和pt-online-schema-change是否有這個(gè)限制硅确。抽時(shí)間需要測試一下。
參考資料：
http://www.cnblogs.com/gomysql/p/3776192.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
http://www.mysqlperformanceblog.com/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/
《MySQL技術(shù)內(nèi)幕--innodb存儲(chǔ)引擎第2版》
