mysql碎片率影响在线ddl吗,MySQL 5.6对在线DDL的优化

目前我们线上统一用的MySQL 5.1系列，每次大表升级做DDL操作时，都会花费大量的时间，同时也会影响宕机时间，MySQL 5.6在这方面做了很大的改进，大大的缩短了DDL操作时间，详情见如下实验：

MySQL 5.6.16

窗口一：添加一个索引

mysql> ALTER TABLE task_new add key idx_company_id(company_id);

Query OK, 0 rows affected (15.65 sec)

Records: 0  Duplicates: 0  Warnings: 0

窗口二：update 该表中数据

mysql> update task_new set task_priority =125 where id = 4047015;

Query OK, 0 rows affected (11.30 sec)

Rows matched: 0  Changed: 0  Warnings: 0

update没有等待，也没有被锁。正常的update

窗口一：删除一个索引

mysql> alter table task_new drop key idx_user_id;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

drop index 在新版本中执行时间特别短，原因在于不用建表，只需要在原表中做drop index即可。

MySQL 5.1.57

窗口一：添加一个索引

root@mediawise 03:31:02>ALTER TABLE task_new add key idx_company_id(company_id);

Query OK, 1336140 rows affected (1 min 16.12 sec)

Records: 1336140  Duplicates: 0  Warnings: 0

窗口二：Update 该表中数据

root@mediawise 03:26:21>update task_new set task_priority = 126 where id = 5518630;

Query OK, 5 rows affected (1 min 11.34 sec)

Rows matched: 5  Changed: 5  Warnings: 0

存在lock现象，等alter结束才会做update操作

root@mediawise 05:34:15>alter table task_new drop key idx_status;

Query OK, 1336140 rows affected (1 min 6.21 sec)

Records: 1336140 Duplicates: 0 Warnings: 0

drop index 在老版本中相当慢，原因在于要新建临时表

综上所述：MySQL 5.6系列在DDL操作上做了新的更改，由原来的新建临时表，再把数据插入，再rename表的方式改变成直接在原表alter，最后时刻加锁，加快执行时间，不影响其他update，delete，select，insert 操作。

备注：MySQL 5.6 DDL 主要受old_alter_table控制，默认情况下参数为OFF

mysql> show global variables like '%old_alter_table%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| old_alter_table | OFF   |

+-----------------+-------+

1 row in set (0.12 sec)

mysql> alter table task_new add index user_id(user_id);

Query OK, 0 rows affected (12.19 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show global variables like '%old_alter_table%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| old_alter_table | ON    |

+-----------------+-------+

1 row in set (0.01 sec)

mysql> alter table task_new add index user_id(user_id);

Query OK, 1603368 rows affected (8 min 25.63 sec)

Records: 1603368  Duplicates: 0  Warnings: 0

思考题：

1.为什么删除index如此快速？

答：删除index时只是在辅助索引的空间上做个可用标志。并删除该表的索引定义，没有新建表。

2.innodb中index存哪里

分析innodb会发现,索引是存在于新的page中，不与数据一起存储在同一pageh中。辅助索引page中存的是索引行的值以及主键的值。

3.通过什么来保证在线添加index和update数据双方不受影响？

答：MySQL5.6中通过row_log来保证数据的一致性，且双方不受影响。

Online Add Index处理流程

1.判断DML是否可以用inplace进行(不新建表)

2.开始进行online操作前期准备

(1.修改表的数据字典信息

(2.等待后台所有线程停止操作此表

(3.online过程中，原表允许读写，所以需要将DML操作的数据记录到row_log中，不在索引上进行更新

3.开始进行真正的online add index操作

(1.读取聚簇索引、排序、并插入到新建索引中,并且将row log中的记录变化，更新到新建索引中

(2.插入新索引完成后，将这期间记录在row_log的记录变化重新运用

(3.新建索引短暂加锁