前言 作为 Percona 的首席架构师,我的主要职责之一是对客户的数据库进行性能方面的优化,这使得工作复杂且非常有趣。 在这篇文章中,我想讨论一个最重要的问题: 选择最佳的 InnoDB 主键。 InnoDB 主键有什么特别之处? InnoDB 被称为索引组织型的存储引擎。 主键使用的 B-Tree 来存储数据,即表行。 这意味着 InnoDB 必须使用主键。 如果表没有主键,InnoDB 会向表中添加一个隐藏的自动递增的 6 字节计数器,并使用该隐藏计数器作为主键。 InnoDB 的隐藏主键存在一些问题。 您应该始终在表上定义显式主键,并通过主键值访问所有 InnoDB 行。 InnoDB 的二级索引也是一个B-Tree。 搜索关键字由索引列组成,存储的值是匹配行的主键。 通过二级索引进行搜索通常会导致主键的隐式搜索。 什么是 B-Tree? 一个 B-Tree 是一种针对在块设备上优化操作的数据结构。 块设备或磁盘有相当重要的数据访问延迟,尤其是机械硬盘。 在随机位置检索单个字节并不比检索更大的数据花费的时间更少。 这是 B-Tree 的基本原理,InnoDB 使用的数据页为 16KB。 让我们尝试简化 B-Tree 的描述。 B-Tree 是围绕这键来组织的数据结构。 键用于搜索 B-Tree 内的数据。 B-Tree 通常有多个级别。 数据仅存储在最底层,即叶子节点。 其他级别的页面(节点)仅包含下一级别的页面的键和指针。 如果要访问键值的数据,则从顶级节点-根节点开始,将其包含的键与搜索值进行比较,并找到要在下一级访问的页面。 重复这个过程,直到你达到最后一个级别,即叶子节点。 理论上,每个 B-Tree 级别的读取都需要一次磁盘读取操作。 在实践中,总是有内存缓存节点,因为它们数量较少且经常访问,因此适合缓存。作者:Yves Trudeau
![f17361d74d8247b5784ffe8ca7c85900.png](https://i-blog.csdnimg.cn/blog_migrate/b089a3ee1da04b1026cc69d3d880f894.png)
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2882954
Avg_row_length: 234
Data_length: 675282944
Max_data_length: 0
Index_length: 47775744
Data_free: 3145728
Auto_increment: 3000001
Create_time: 2018-07-13 18:27:09
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Data_length 值是 B-Tree 主键的大小。B-Tree 的二级索引,即 k_1 索引,Index_length 是其大小。因为 ID 主键自增,所以 sysbench 表数据是顺序插入的。当按主键顺序插入时,即使
innodb_fill_factor
设为 100,InnoDB 最多使用 15KB 的数据填充空间。这导致在初始插入数据之后,需要拆分页面。页面中还有一些页眉和页脚。如果页面太满且无法添加更多数据,则页面将拆分为两个。同样,如果两个相邻页面的填充率低于 50%,InnoDB 将合并它们。例如,这是以 ID 顺序插入的 sysbench 表:
mysql> select