现在的位置: 首页 > 综合 > 正文

Mysql5.6特性之细解— DDL online operate

2018年01月22日 ⁄ 综合 ⁄ 共 2849字 ⁄ 字号 评论关闭

 

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.6alter table语法中新增了算法选项和锁选项:

algorithm_option:

    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

 

lock_option:

    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

可以通过这2个选项来控制你的DDL操作是in-place模式还是旧的那种copy the table 的模式

转自:http://hi.baidu.com/baojunonline/item/2491713963193297b80c0338

抱歉!评论已关闭.