主键乱序插入对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 | | | | + -------+------------------+------+-----+---------+----------------+ |
#顺序文件<br>mysql> select * from sbtest into outfile '/xfs/mysql3311/order.txt' ;<br>#乱序文件 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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 周边上新:园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源!
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试?测试工程师会被淘汰吗?