当前位置:Gxlcms > mysql > 主键乱序插入对Innodb性能的影响_MySQL

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

时间:2021-07-01 10:21:17 帮助过:37人阅读

bitsCN.com

在平时的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   |     |         |                |+-------+------------------+------+-----+---------+----------------+
bitsCN.com

人气教程排行