sqlserver 添加自增主键_技术分享 | 优化 InnoDB 的主键

作者：Yves Trudeau

前言 作为 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 级别的读取都需要一次磁盘读取操作。 在实践中，总是有内存缓存节点，因为它们数量较少且经常访问，因此适合缓存。 一个简单的三级 B-Tree 结构 有序的插入示例 让我们考虑以下 sysbench 表：

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