mysql主键乱序_主键乱序插入对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 beas 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

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

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%的空间浪费。

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

相关链接：