ITPub博客

一 .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://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版》