关于InnoDB表的page利用率和optimize table

上一篇我们介绍了ibd_used这个工具，我们用来量化看表数据文件的page使用率。这里用来说明optimize table这个命令的问题和优化。

 

实例准备

建一个这样的表

CREATE TABLE `tb` (

`seq_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`a` varchar(32) DEFAULT NULL,

`b` varchar(32) DEFAULT NULL,

`c` varchar(32) DEFAULT NULL,

`d` char(255) DEFAULT NULL,

Primary key (seq_id),
KEY a (a),

KEY bc (b,c),

KEY cb (c,b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

执行语句为“insert into tb(a,b,c) values(randstr, randstr, randstr);” randstr是客户端程序生成的长度30字节的随机字符串。30个线程并发，每个线程插入1w条记录。

等待更新完成后（包括purge完成，从系统的vmstat上看无任何io），执行./ibd_used tb.ibd 0 100000000，可以从最后4行看到各个索引的page平均利用率如下图。

 

 

说明： 你会发现即使是主键索引，利用率也不一定很高。原因是什么？

Optimize table 效果

我们知道Optimize table是用来作表整理的， 执行一下 optimize table tb，再看ibd_used的结果。

说明：这里我们发现，pk的page利用率明显提升，是optimize效果，但是其他几个索引的page利用率却没有明显效果。为什么呢？

 

1)       首先是上面没有提的那个“异常”，既然是自增主键，为什么在optimize之前，pk的利用率不高？原因是多线程插入，虽然seq_id是递增申请，但不能保证是递增更新到page上。而通过optimize后，等于是单线程重新整理了。

 

2)       为什么其他索引的page利用率没有提升，这个就涉及到optimize table的内部执行过程。如下：

a)       建一个临时表，表结构与tb相同

b)       按照tb主键顺序将tb数据一行行的插入到临时表中

c)       删掉tb，临时表重命名为tb

所以我们看到对于其他索引，插入的值仍然是随机的过程。

 

改进的思路

我们知道InnoDB在5.1的时候innodb_plugin里面就有fast index creatation了，上述过程如果改成如下：

a)       建一个临时表，表结构与tb相同

b)       删掉临时表的所有非聚簇索引

c)       按照tb主键顺序将tb数据一行行的插入到临时表中

d)       建立临时表的所有非聚簇索引

e)       删掉tb，临时表重命名为tb

这样在执行步骤d)时，每个非聚簇索引都是按照排序好方式构建，则能让所有的索引page都很“紧凑”。

 

Percona版本的 expand_fast_index_creation参数

在Percona版本中新增了这个参数，默认值是OFF，需要配置文件设置ON或者通过set命令热修改。

当设置为ON时，则optimize table tb实现的就是上述我们说到的改进流程。从ibd_used看到执行结果看到的效果如下：

 

 

小结

所以当你需要通过optimze table优化表空间，

若是使用percona版本则最好先打开expand_fast_index_creation;

若是官方版本，则建议自己写脚本建临时表，按照上述的过程a~e来执行，达到最优的效果。

评论

3 楼 lenaLee 2012-04-30  

丁林.tb 写道

lenaLee 写道

丁哥好，我是MySql的新兵。
关注你的文章，发现您一直使用的是InnoDB引擎。现在好像NDB比较火，便于横向扩展。请问你觉得哪个将来会发展得比较好？
我以前用的是SqlServer，SQL语句还比较熟，能给我些怎么学好MySql的建议吗，谢啦！

NDB目前官方在主推，我觉得以后会越来越完善。只是目前很多应用需要的功能都可以用InnoDB + 应用层的分库分表解决，可控性也更好，所以使用得比较多。

从SqlServer要切换到MySQL应该还ok的，你可以试着将SQLServer里面的建表过程、索引、存储过程这些在自己试验着转到MySQL里面，来熟悉两者的差别。

受益匪浅，非常感谢！

2 楼 丁林.tb 2012-04-29  

lenaLee 写道

丁哥好，我是MySql的新兵。
关注你的文章，发现您一直使用的是InnoDB引擎。现在好像NDB比较火，便于横向扩展。请问你觉得哪个将来会发展得比较好？
我以前用的是SqlServer，SQL语句还比较熟，能给我些怎么学好MySql的建议吗，谢啦！

NDB目前官方在主推，我觉得以后会越来越完善。只是目前很多应用需要的功能都可以用InnoDB + 应用层的分库分表解决，可控性也更好，所以使用得比较多。

从SqlServer要切换到MySQL应该还ok的，你可以试着将SQLServer里面的建表过程、索引、存储过程这些在自己试验着转到MySQL里面，来熟悉两者的差别。

1 楼 lenaLee 2012-04-29  

丁哥好，我是MySql的新兵。
关注你的文章，发现您一直使用的是InnoDB引擎。现在好像NDB比较火，便于横向扩展。请问你觉得哪个将来会发展得比较好？
我以前用的是SqlServer，SQL语句还比较熟，能给我些怎么学好MySql的建议吗，谢啦！