MySQL 5.6 Online DDL.md

一 .Fast index Creation

MySQL 5.5和更高版本並且MySQL 5.1 innodb plugin支援Fast index Creation，對於之前的版本對於索引的新增或刪除這類DDL操作，MySQL資料庫的操作過程為如下：

（1）首先建立新的臨時表，表結構通過命令ALTAR TABLE新定義的結構

（2）然後把原表中資料匯入到臨時表

（3）刪除原表

（4）最後把臨時表重新命名為原來的表名

上述過程我們不難發現，若我們對一張大表進行索引的新增或者刪除，需要很長的時間，致命的是若有大量的訪問請求，意味著無法提供服務。

innodb儲存引擎從1.0.x版本開始支援Fast index Creation（快速索引建立）。簡稱FIC。對於輔助索引的建立，會對建立索引的表加一個S鎖。在建立的過程中，不需要重建表，因此速度有明顯提升。對於刪除輔助索引innodb儲存引擎只需要更新內部檢視，並將輔助索引的空間標記為可用，同時刪除MySQL 資料庫內部檢視上對該表的索引定義即可。特別需要注意的時，臨時表的建立路徑是通過引數tmpdir設定的。必須確保tmpdir有足夠的空間，否則將會導致輔助索引建立失敗。由於在建立輔助索引時加的是S鎖，所以在這過程中只能對該表進行讀操作，若有事務需要對該表進行寫操作，那麼資料庫服務同樣不可用。需要注意的是，FIC方式只限定於輔助索引，對於主鍵的建立和刪除同樣需要重建一張表。

二 . Oline Schema Change

Online Schema Change（線上架構改變，簡稱OSC），最早是由Facebook實現的一種線上DDL的方式。所謂"線上"是指在新增欄位，新增索引這類DDL操作時，事務對錶的讀寫操作不會受到阻塞。

三 .Online DDL

FIC可以讓innodb儲存引擎避免建立臨時表，提高索引建立效率。雖然FIC不會阻塞讀操作，但是DML操作還是照樣阻塞的。MySQL 5.6版本開始支援Online DDL（線上資料定義）操作，其允許輔助索引建立的同時，還允許其他諸如INSERT,UPDATE,DELETE這類DML操作。此外不僅是輔助索引，以下這幾類DDL操作都可以通過”線上“的方式進行：

（1）輔助索引的建立於刪除

（2）改變自增長值

（3）新增或刪除外來鍵約束

（4）列的重新命名

通過新的ALTER TABLE，可以選擇索引的建立方式

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指定了建立或刪除索引的演算法，COPY表示按照MySQL 5.1版本之前的方法，即建立臨時表。INPLACE表示建立索引或刪除索引操作不需要建立臨時表。DEFAULT表示根據引數old_alter_table來判斷是通過INPLACE還是COPY的演算法，改引數預設為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部分為索引建立或刪除時對錶新增鎖的情況，可選擇的如下：
（1）NONE，執行索引建立或者刪除操作時，對目標表不新增任何鎖，即事務仍然可以進行讀寫操作，不會收到阻塞，該模式可以獲得最大的併發。

（2）SHARE，和Fast index Creation類似，執行索引建立或刪除操作時，對目標表加一個S鎖。對於併發讀事務，依然可以執行。但是遇到寫事務，將會發生等待操作，如果儲存引擎不支援SHARE模式，將返回一個錯誤資訊。

（3）EXCLUSIVE，執行索引建立或刪除時，對目標表加上一個X鎖。讀寫事務均不能進行。會阻塞所有的執行緒。這和COPY方式類似，但是不需要像COPY方式那樣建立一張臨時表。

（4）DEFAULT，該模式首先會判斷當前操作是否可以使用NONE模式，若不能，則判斷是否可以使用SHARE模式，最後判斷是否可以使用EXCLUSIVE模式。也就是說DEFAULT會通過判斷事務的最大併發性來判斷執行DDL的模式。

innodb儲存引擎實現Online DDL的原理是在執行建立或者刪除操作同時，將INSERT,UPDATE，DELETE這類DML操作日誌寫入到一個快取中，待完成索引建立後再將重做應用到表上，以此達到資料的一致性。這個快取的大小由引數innodb_online_alter_log_max_size控制，預設大小為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)) > 

如果待更新的表比較大，並且建立過程中有大量的寫事務，如果遇到innodb_online_alter_log_max_size的空間不能存放日誌時，會丟擲相應的錯誤，這個我們後面進行測試。
如果遇到改錯誤，我們可以調大該引數，以此獲得更大的日誌快取空間。此外我們可以設定ALTER TABLE的模式為SHARE，這樣在執行過程中不會有寫事務發生。因此不需要進行DML日誌的記錄。

通過上面的簡單說明，相信大家心裡都有譜了。那我們來實際測試一下。我這裡使用sysbench生成1000w行測試資料

[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.首先測試新增一個輔助索引

在session 1中執行新增索引操作，在session 2中執行DML操作；

session 1 （alter table選擇預設的執行方式，即讓innodb儲存引擎自行判斷該加什麼鎖）

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（可以發現並未鎖表，一切正常）

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.測試新增一個欄位
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) > 

可以發現新增欄位依然不會影響DML操作。是不是很爽？爽的話就升級吧。
如果我們在mysql 5.5中新增欄位會是怎樣的情況呢？在mysql 5.5中新增欄位是會鎖表的，讀寫都阻塞（增加，刪除索引會加S鎖，阻塞寫操作）。如果還沒有使用mysql 5.6的同學也不用擔心，因為目前有兩個工具非常好用：oak-online-alter-table和pt-online-schema-change現在來看看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;         

另外一個會話檢視

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> 

可以看見鎖表了，並且在建立臨時表。

不過MySQL 5.6不是一定不會鎖表，有種特殊情況，那就是如果有一條大結果的查詢在查詢某個表，這時如果執行ALTER TABLE時，是會鎖表的。我們做一個簡單測試。

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) > 

可以看見已經導致鎖表咯。所以，我們在上線的時候，一定要觀察是否有某個慢SQL或者比較大的結果集的SQL在執行，否則在執行ALTER TABLE時將會導致鎖表發生。當然不清楚oak-online-alter-table和pt-online-schema-change是否有這個限制。抽時間需要測試一下。

參考資料：
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技術內幕--innodb儲存引擎第2版》