MySQL Online DDL（二）（r11筆記第88天）

對(duì)于Online DDL，之前簡(jiǎn)單分析了一些場(chǎng)景MySQL中的Online DDL(第一篇）(r11筆記第3天)，其實(shí)有一個(gè)很關(guān)鍵的點(diǎn)沒(méi)提到，那就是online DDL的算法，目前有三個(gè)操作選項(xiàng)，default,inplace,copy可選

具體可以參考  https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

> select count(*) from newtest;
+----------+
| count(*) |
+----------+
| 22681426 |
+----------+
1 row in set (45.76 sec)表結(jié)構(gòu)信息如下：

> show create table newtest\G
*************************** 1. row ***************************
       Table: newtest
Create Table: CREATE TABLE `newtest` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `game_type` int(11) NOT NULL DEFAULT '-1' ,
  `login_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `login_account` varchar(100) DEFAULT NULL ,
  `cn_master` varchar(100) NOT NULL DEFAULT '' ,
  `client_ip` varchar(100) DEFAULT '' ,
  PRIMARY KEY (`id`),
  KEY `ind_tmp_account1` (`login_account`),
  KEY `ind_login_time_newtest` (`login_time`)
) ENGINE=InnoDB AUTO_INCREMENT=22681850 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

比如我們運(yùn)行下面的SQL,添加一個(gè)字段，默認(rèn)情況下是使用copy的算法，即數(shù)據(jù)是平行復(fù)制一份。

alter table newtest add column newcol varchar(10) default '';這個(gè)變更過(guò)程會(huì)生成兩個(gè)臨時(shí)的文件.frm,.ibd
-rw-r----- 1 mysql mysql       8840 Dec  5 18:13 newtest.frm
-rw-r----- 1 mysql mysql 4353687552 Dec  5 18:45 newtest.ibd
...
-rw-r----- 1 mysql mysql       8874 Feb 27 22:25 #sql-6273_2980ab.frm
-rw-r----- 1 mysql mysql   41943040 Feb 27 22:25 #sql-ib280-3638407428.ibd
...在這個(gè)變更的過(guò)程中，是運(yùn)行DML操作的，而且沒(méi)有任何阻塞。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113');
Query OK, 1 row affected (0.05 sec)

因?yàn)槭褂昧酥麈I自增，所以我可以用同樣的語(yǔ)句再插入一條記錄，也是全然沒(méi)有阻塞。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113');
Query OK, 1 row affected (0.00 sec)這個(gè)時(shí)候查看show processlist的結(jié)果，相比就顯得有些簡(jiǎn)單了。不像之前的版本中會(huì)有table metadata lock的字樣了。

+---------+-----------------+-----------------------------+----------------+-------------+---------+--------------------------------
|Id      | User            | Host                        | db             | Command     | Time    | State                          
+---------+-----------------+-----------------------------+----------------+-------------+---------+--------------------------------
| 2719915 | root            | localhost                   | test           | Query       |      75 | altering table

我們簡(jiǎn)單看看上面列舉出來(lái)的配置文件.frm

可以通過(guò)strings的方式看到一個(gè)基本的結(jié)構(gòu)信息。

# strings newtest.frm
PRIMARY
ind_tmp_account1
ind_login_time_newtest
InnoDB
)                                        
game_type
login_time
login_account
cn_master
client_ip
game_type
login_time
login_account
cn_master
client_ip
而查看臨時(shí)創(chuàng)建的.frm文件

# strings "#sql-6273_2980ab.frm"
PRIMARY
ind_tmp_account1
ind_login_time_newtest
InnoDB
)                                        
game_type
login_time
login_account
cn_master
client_ip
newcol
game_type
login_time
login_account
cn_master
client_ip
newcol整個(gè)添加字段的操作持續(xù)時(shí)間為10分鐘左右。

> alter table newtest add column newcol varchar(10) default '';
Query OK, 0 rows affected (10 min 31.64 sec)
Records: 0  Duplicates: 0  Warnings: 0可以看到修改后的.ibd文件大小相比要大了一些。

-rw-r----- 1 mysql mysql       8874 Feb 27 22:25 newtest.frm
-rw-r----- 1 mysql mysql 4047503360 Feb 27 22:34 newtest.ibd而如果我們換一個(gè)角度來(lái)看，我們刪除一個(gè)字段。

--alter table newtest drop column newcol , ALGORITHM=INPLACE;  --這種方式是有問(wèn)題的，采用如下的方式，我們聲明使用inplace算法，而實(shí)際情況如何呢。

> alter table newtest drop column newcol , ALGORITHM=INPLACE;
Query OK, 0 rows affected (9 min 54.18 sec)
Records: 0  Duplicates: 0  Warnings: 0我們可以看到DML操作暢通無(wú)阻。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113');
Query OK, 1 row affected (0.15 sec)這個(gè)過(guò)程可以看到效果和啟用copy算法是一樣的，為什么呢。因?yàn)樘砑幼侄?，刪除字段是一個(gè)數(shù)據(jù)重組的過(guò)程，所以相比而言，這個(gè)操作的代價(jià)也是昂貴的。

然后我們添加索引，啟用inplace算法。

alter table newtest add index (client_ip) ,algorithm=inplace;這個(gè)過(guò)程就特別了，依舊會(huì)創(chuàng)建.frm的臨時(shí)文件，但是數(shù)據(jù)文件不會(huì)復(fù)制，而是現(xiàn)改。
-rw-r----- 1 mysql mysql       8840 Feb 27 22:49 newtest.frm
-rw-r----- 1 mysql mysql 4018143232 Feb 27 23:06 newtest.ibd
...
-rw-r----- 1 mysql mysql       8840 Feb 27 23:06 #sql-6273_2980ab.frm這個(gè)過(guò)程中，DML依舊是暢通的。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113');
Query OK, 1 row affected (0.04 sec)整個(gè)添加的過(guò)程相比而言，持續(xù)時(shí)間要短很多，大概是3分鐘左右。

> alter table newtest add index (client_ip) ,algorithm=inplace;
Query OK, 0 rows affected (3 min 42.84 sec)
Records: 0  Duplicates: 0  Warnings: 0
而如果此時(shí)刪除索引，這個(gè)過(guò)程就如同非一般的感覺(jué)，不到一秒即可完成。

> alter table newtest drop index  client_ip ,algorithm=inplace;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0整個(gè)過(guò)程中.frm和.ibd文件沒(méi)有任何大小變化。

-rw-r----- 1 mysql mysql       8840 Feb 27 23:13 newtest.frm
-rw-r----- 1 mysql mysql 4785700864 Feb 27 23:13 newtest.ibd而如果我們?yōu)榱藢?duì)比同樣的inpalce和copy操作場(chǎng)景下的代價(jià)，可以使用copy顯示創(chuàng)建一個(gè)索引，即可得到一個(gè)基本的對(duì)比情況。

alter table newtest add index (client_ip) ,algorithm=copy;整個(gè)過(guò)程因?yàn)?ibd文件較大，持續(xù)時(shí)間也會(huì)放大很多，這個(gè)環(huán)境中執(zhí)行時(shí)間是29分,差別已然非常明顯。

> alter table newtest add index (client_ip) ,algorithm=copy;
Query OK, 22681430 rows affected (29 min 13.80 sec)
Records: 22681430  Duplicates: 0  Warnings: 0

Online DDL還是存在著一些限定情況，很多場(chǎng)景還沒(méi)有完全測(cè)試到，需要結(jié)合具體的場(chǎng)景和需求來(lái)考量。