一 .Fast index Creation
- dba的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量线程处于“Waiting for meta data lock”状态的时候。因此mysql 5.6的online ddl特性是dba们最期待的新特性，这个特性解决了执行ddl锁表的问题，保证了在进行表变更时，不会堵塞线上业务读写，保障在变更时，库依然能正常对外提供访问
- innodb存储引擎从1.0.x版本开始支持Fast index Creation（快速索引创建）。简称FIC。对于辅助索引的创建，会对创建索引的表加一个S锁。在创建的过程中，不需要重建表，因此速度有明显提升。对于删除辅助索引innodb存储引擎只需要更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL 数据库内部视图上对该表的索引定义即可。特别需要注意的时，临时表的创建路径是通过参数tmpdir设置的。必须确保tmpdir有足够的空间，否则将会导致辅助索引创建失败。由于在创建辅助索引时加的是S锁，所以在这过程中只能对该表进行读操作，若有事务需要对该表进行写操作，那么数据库服务同样不可用。需要注意的是，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。
-
- 5.6 online ddl推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除辅助索引两种方式，对于主键的创建和删除同样需要重建一张表，而且与copy方式一样需要全程锁表，实用性不是很强。下面以加索引为例，简单介绍这两种方式的实现流程。
-
- copy方式
-
- (1).新建带索引的临时表
-
- (2).锁原表，禁止DML，允许查询
-
- (3).将原表数据拷贝到临时表(无排序，一行一行拷贝)
-
- (4).进行rename，升级字典锁，禁止读写
-
- (5).完成创建索引操作
-
- inplace方式
-
- (1).新建索引的数据字典
-
- (2).锁表，禁止DML，允许查询
-
- (3).读取聚集索引，构造新的索引项，排序并插入新索引
-
- (4).等待打开当前表的所有只读事务提交
-
- (5).创建索引结束
实验mysql5.5版本
mysql> alter table sbtest add column t varchar(20);
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+---------------------------------------------+
| 19 | root | localhost | test | Query | 7 | copy to tmp table | alter table sbtest add column t varchar(20) | ----inplace非增删索引也会和copy方式一样拷贝数据
| 20 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------------------+---------------------------------------------+
mysql> update sbtest set pad='adafad' where id>980009 ;
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
| 19 | root | localhost | test | Query | 7 | copy to tmp table | alter table sbtest add column t varchar(20) |
| 20 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 24 | root | localhost | test | Query | 3 | Waiting for table metadata lock | update sbtest set pad='adafad' where id>980009 | ---堵塞DML操作，允许select罢了
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
mysql> alter table sbtest add index idx_k(c);
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------+---------------------------------------+
| 19 | root | localhost | test | Query | 9 | manage keys | alter table sbtest add index idx_k(c) | ----inplace仅支持增删索引时不拷贝数据
| 20 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------------+---------------------------------------+
mysql> update sbtest set pad='adafad' where id>980009 ;
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
| 19 | root | localhost | test | Query | 7 | manage keys | alter table sbtest add index idx_k(c) |
| 20 | root | localhost | NULL | Query | 0 | NULL | show full processlist | ----也堵塞DML，除了select
| 26 | root | localhost | test | Query | 3 | Waiting for table metadata lock | update sbtest set pad='adafad' where id>980009 |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
总结就是5.6 onlineddl之前，会造成锁表的问题，但是copy和inplace两种方式有拷贝和不拷贝数据的区别
二.ONlineDDL
FIC可以让innodb存储引擎避免创建临时表，提高索引创建效率。虽然FIC不会阻塞读操作，但是DML操作还是照样阻塞的。MySQL 5.6版本开始支持Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如INSERT,UPDATE,DELETE这类DML操作。此外不仅是辅助索引，以下这几类DDL操作都可以通过”在线“的方式进行：
（1）辅助索引的创建于删除
（2）改变自增长值
（3）添加或删除外键约束
（4）列的重命名
具体的相互之间的影响如下：
Operation In-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes CREATE INDEX, ADD INDEX Yes* No* Yes Yes Some restrictions for FULLTEXT index; see next row. ADD FULLTEXT INDEX Yes No* No Yes Creating the first FULLTEXT index for a table involves a table copy, unless there is a user-supplied FTS_DOC_ID column. Subsequent FULLTEXT indexes on the same table can be created in-place. DROP INDEX Yes No Yes Yes Only modifies table metadata. OPTIMIZE TABLE Yes Yes Yes Yes Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. OPTIMIZE TABLEusing online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes. Set default value for a column Yes No Yes Yes Only modifies table metadata. Change auto-increment value for a column Yes No Yes Yes Only modifies table metadata. Add a foreign key constraint Yes* No* Yes Yes To avoid copying the table, disable foreign_key_checks during constraint creation. Drop a foreign key constraint Yes No Yes Yes The foreign_key_checks option can be enabled or disabled. Rename a column Yes* No* Yes* Yes To allow concurrent DML, keep the same data type and only change the column name. Add a column Yes* Yes* Yes* Yes Concurrent DML is not allowed when adding an auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Drop a column Yes Yes* Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Reorder columns Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Change ROW_FORMAT property Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Change KEY_BLOCK_SIZE property Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Make column NULL Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Make column NOT NULL Yes* Yes Yes Yes STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. For more information, see Section 13.1.7, “ALTER TABLE Syntax”. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. Change data type of column No Yes No Yes Add primary key Yes* Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. ALGORITHM=INPLACE is not allowed under certain conditions if columns have to be converted to NOT NULL. See Example 14.9, “Creating and Dropping the Primary Key”. Drop primary key and add another Yes Yes Yes Yes ALGORITHM=INPLACE is only allowed when you add a new primary key in the same ALTER TABLE; the data is reorganized substantially, so it is still an expensive operation. Drop primary key No Yes No Yes Restrictions apply when you drop a primary key primary key without adding a new one in the same ALTER TABLE statement. Convert character set No Yes No Yes Rebuilds the table if the new character encoding is different. Specify character set No Yes No Yes Rebuilds the table if the new character encoding is different. Rebuild with FORCE option Yes Yes Yes Yes Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes. Rebuild with “null” ALTER TABLE ... ENGINE=INNODB Yes Yes Yes Yes Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes. Set table-level persistent statistics options (STATS_PERSISTENT,STATS_AUTO_RECALCSTATS_SAMPLE_PAGES) Yes No Yes Yes Only modifies table metadata.
Operation In-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes
The following sections shows the basic syntax, and usage notes related to online DDL, for each of the major operations that can be performed with concurrent DML, in-place, or both:
通过新的ALTER TABLE，可以选择索引的创建方式
mysql [localhost] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 5.6.19 |
+-----------+
row in set (0.00 sec)
? 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 |
+-----------------+-------+
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。
如果待更新的表比较大，并且创建过程中有大量的写事务，如果遇到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) > alter table sbtest add key idx_pad ( pad );
session 2（可以发现并未锁表，一切正常）
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 |
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
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> alter table sbtest add address char(30) after pad;
另外一个会话查看
可以看见锁表了，并且在创建临时表。
不过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
可以看见已经导致锁表咯。 一个事务在操作表的时候，是不允许进行对表进行DDL变更的。所以，我们在上线的时候，一定要观察是否有某个慢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版》
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1816596/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-1816596/
139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
