innodb性能改善方面:
--Users can add indexes and perform standard table alterations while the database remains available for application updates。
○支持在线操作(add index、alter table):
例子一:在线添加索引:
开启一个session,对italk库下的data_userinfo表做创建索引的操作,(该表大概70万数据):
mysql>>create index idx_groupid on data_userinfo(groupid);
Query OK, 0 rows affected (9.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
同时在另外一个session中,执行对该表的update操作:
mysql>>update data_userinfo set status=1 where id=22;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
update操作立即返回结果,在之前的版本中,由于创建索引以及alter table 是表级锁,该update只能等待添加索引的语句完成后才能执行,show full processlist状态显示为:
Waiting for table metadata lock | update data_userinfo set status=2 where id=22
Alter table 添加索引形式:
mysql>>alter table data_userinfo add index idx_groupid (groupid);
Query OK, 0 rows affected (12.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>>update data_userinfo set status=2 where id=23;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
例子二:在线添加列字段:
mysql>>alter table data_userinfo add age int unsigned not null default 0;
Query OK, 0 rows affected (57.71 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>>update data_userinfo set status=1 where id=23;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
例子三:改变列字段的数据类型:
Changing the data type of a column (takes substantial time and does require rebuilding all the rows of the table):
会花费大量时间,并且会重建表中所有行数据
Query OK, 668200 rows affected (1 min 35.54 sec)
当你对一个大表进行DDL操作,可以通过如下步骤来判断该操作是快速执行还是执行比较慢:
1、克隆原表的表结构
2、用原来的一小部分数据来填充克隆表
3、在克隆表上执行DDL操作
4、检查rows affected是0还是非0.如果是非0,意味着该DDL操作将要重建表中所有行数据,也就意味着你须要安排计划一个业务低峰值的时间或者在
slave上进行该DDL操作
○性能基准测试
可以通过在最新版本5.6和较老版本中对一个较大的innodb表执行相同的alter操作,测试在线DDL操作的相关性能
mysql--root@localhost:italk 17:45:09>>set old_alter_table=1; -------------设置为在旧模式下
Query OK, 0 rows affected (0.00 sec)
mysql--root@localhost:italk 17:45:30>>alter table data_userinfo add index idx_groupid (groupid);
Query OK, 668200 rows affected (42.05 sec) -------------performs a table copy通过copy表数据完成,受影响行数是所有行
Records: 668200 Duplicates: 0 Warnings: 0
mysql--root@localhost:italk 17:46:28>>set old_alter_table=0; -------------设置为在最新模式下
Query OK, 0 rows affected (0.00 sec)
mysql--root@localhost:italk 17:46:54>>alter table data_userinfo add index idx_groupid (groupid);
Query OK, 0 rows affected (10.67 sec) -------------changes in-place 就地更改,不影响行数据
Records: 0 Duplicates: 0 Warnings: 0
通过耗时我们可以看到操作时间大大减少,性能明显提高不少。
5.6的alter table语法中新增了算法选项和锁选项:
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
可以通过这2个选项来控制你的DDL操作是in-place模式还是旧的那种copy the table 的模式