主键乱序插入对Innodb性能的影响

在平时的mysql文档学习中我们经常会看到这么一句话：

MySQL tries to leave space so that future inserts do not incur un-necessary page splits (and thus higher IO cost). In an "ideal" world, MySQL tries to keep the index pages at 15/16-th full, but depending on insert order, this fill factor can be as low as 1/2

大致含义就是当我们按照索引顺序插入时，page的填充率能达到15/16 ， 而乱序插入时只能到略大于 1/2 的填充率。

那么这个说法是否正确呢？是否有相应的理论依据呢？

本文将通过一些测试来验证这个观点的真伪。

测试数据准备

简介： 顺序数据通过sysbench --oltp-table-size = 8000000 生成，然后通过order by rand() 生成乱序数据。

mysql> desc sbtest;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

　　

#顺序文件
mysql> select * from sbtest into outfile '/xfs/mysql3311/order.txt' ;
#乱序文件
mysql> select b.* from rand_num a left join sbtest b on a.id = b.id into outfile '/xfs/mysql3311/random-order.txt';

　　

文件load 性能测试

从结果可以很明显的看出 53sec vs 719sec，加载速度慢了12倍之多。

通过B-tree的原理我们也可以知道，乱序插入时Innodb需要不停的申请新的page，并且进行tree的重新分布，导致插入速度变慢。

CREATE TABLE `sbtest_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> load data infile '/xfs/mysql3311/order.txt' into table sbtest_order;
Query OK, 8000000 rows affected (53.23 sec)
Records: 8000000 Deleted: 0 Skipped: 0 Warnings: 0

　　

CREATE TABLE `sbtest_rand` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> load data infile '/xfs/mysql3311/random-order.txt' into table sbtest_random;

Query OK, 8000000 rows affected (11 min 59.09 sec)
Records: 8000000 Deleted: 0 Skipped: 0 Warnings: 0

　　

页面填充率

通过xtrabackup的附带功能查看数据文件，可以知道page填充率。

从下文标红的数据可以看到：

• 顺序的page填充率= 92% = 15/16
• 乱序的page填充率= 65% = 10/16

基本和理论值一致

shell> xtrabackup --defaults-file=/usr/local/mysql3311/my.cnf --stats --tables="sbtest[.]sbtest_order*" --datadir=/xfs/mysql3311

<INDEX STATISTICS>
table: sbtest/sbtest_order, index: PRIMARY, space id: 11, root page: 3, zip size: 0
estimated statistics in dictionary:
key vals: 8000079, leaf pages: 109590, size pages: 109696
real statistics:
level 2 pages: pages=1, data=1196 bytes, data/pages=7%
level 1 pages: pages=92, data=1424670 bytes, data/pages=94%
leaf pages: recs=8000000, pages=109590, data=1664000000 bytes, data/pages=92%

shell> xtrabackup --defaults-file=/usr/local/mysql3311/my.cnf --stats --tables="sbtest[.]sbtest_random*" --datadir=/xfs/mysql3311
<INDEX STATISTICS>
table: sbtest/sbtest_random, index: PRIMARY, space id: 12, root page: 3, zip size: 0
estimated statistics in dictionary:
key vals: 8916256, leaf pages: 155403, size pages: 177920
real statistics:
level 2 pages: pages=1, data=2899 bytes, data/pages=17%
level 1 pages: pages=223, data=2020239 bytes, data/pages=55%
leaf pages: recs=8000000, pages=155403, data=1664000000 bytes, data/pages=65%

查询性能测试：

使用8并发的sysbench进行OLTP测试，查看两种方式的性能差异。

• TPQ：3078 vs 2803      约10%性能损耗
• Res：2.75ms vs 3.85ms  约40%性能损耗
• Data Size：1.8G vs 2.8G  1.5倍的空间损耗

sysbench --num-threads=8 --max-time=60 --max-requests=9999999 --test=oltp --oltp-table-size=8000000 --mysql-socket=/xfs/mysql3311/mysql.sock --mysql-user=root --mysql-password=password --mysql-table-engine=innodb --oltp-table-name=sbtest_order   run

transactions: 184697 (3078.18 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3509243 (58485.34 per sec.)
other operations: 369394 (6156.35 per sec.)

approx. 95 percentile: 2.75ms

sysbench --num-threads=8 --max-time=60 --max-requests=9999999 --test=oltp --oltp-table-size=8000000 --mysql-socket=/xfs/mysql3311/mysql.sock --mysql-user=root --mysql-password=password --mysql-table-engine=innodb --oltp-table-name=sbtest_random   run

transactions: 168213 (2803.44 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3196047 (53265.34 per sec.)
other operations: 336426 (5606.88 per sec.)

approx. 95 percentile: 3.85ms

总结

通过测试可以看出，按照主键的顺序插入可以带来10%的TPS提升，并能减少50%的空间浪费。

在平时的开发过程中，如果没有特别的业务需要，应该尽可能的使用自增列作为主键。

 相关链接：

1. http://stephen-tu.blogspot.com/2012/01/mysql-optimize-table-matters-after-bulk.html

2. http://www.debianadmin.com/top-84-mysql-performance-tips.html

3. http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/

4. http://www.codinglabs.org/html/theory-of-mysql-index.html#nav-4-4