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() 生成乱序数据。
文件load 性能测试
从结果可以很明显的看出53sec vs 719sec，加载速度慢了12倍之多。
通过B-tree的原理我们也可以知道，乱序插入时Innodb需要不停的申请新的page，并且进行tree的重新分布，导致插入速度变慢。
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
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%的空间浪费。
在平时的开发过程中，如果没有特别的业务需要，应该尽可能的使用自增列作为主键。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！