一 .Fast index Creation

MySQL 5.5和更高版本并且MySQL 5.1 innodb plugin支持Fast index Creation，對(duì)于之前的版本對(duì)于索引的添加或刪除這類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)，若我們對(duì)一張大表進(jìn)行索引的添加或者刪除，需要很長的時(shí)間，致命的是若有大量的訪問請(qǐng)求，意味著無法提供服務(wù)。

innodb存儲(chǔ)引擎從1.0.x版本開始支持Fast index Creation（快速索引創(chuàng)建）。簡稱FIC。對(duì)于輔助索引的創(chuàng)建，會(huì)對(duì)創(chuàng)建索引的表加一個(gè)S鎖。在創(chuàng)建的過程中，不需要重建表，因此速度有明顯提升。對(duì)于刪除輔助索引innodb存儲(chǔ)引擎只需要更新內(nèi)部視圖，并將輔助索引的空間標(biāo)記為可用，同時(shí)刪除MySQL 數(shù)據(jù)庫內(nèi)部視圖上對(duì)該表的索引定義即可。特別需要注意的時(shí)，臨時(shí)表的創(chuàng)建路徑是通過參數(shù)tmpdir設(shè)置的。必須確保tmpdir有足夠的空間，否則將會(huì)導(dǎo)致輔助索引創(chuàng)建失敗。由于在創(chuàng)建輔助索引時(shí)加的是S鎖，所以在這過程中只能對(duì)該表進(jìn)行讀操作，若有事務(wù)需要對(duì)該表進(jìn)行寫操作，那么數(shù)據(jù)庫服務(wù)同樣不可用。需要注意的是，F(xiàn)IC方式只限定于輔助索引，對(duì)于主鍵的創(chuàng)建和刪除同樣需要重建一張表。

二 . Oline Schema Change

Online Schema Change（在線架構(gòu)改變，簡稱OSC），最早是由Facebook實(shí)現(xiàn)的一種在線DDL的方式。所謂"在線"是指在添加字段，添加索引這類DDL操作時(shí)，事務(wù)對(duì)表的讀寫操作不會(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í)對(duì)表添加鎖的情況，可選擇的如下：
（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模式。也就是說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操作。是不是很爽？爽的話就升級(jí)吧。
如果我們?cè)趍ysql 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é)果的查詢?cè)诓樵兡硞€(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)致鎖表咯。所以，我們?cè)谏暇€的時(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版》