InnoDB存储引擎中主键的正确使用对插入性能会有很大的提升，本文简要介绍了innodb中主键和聚簇索引，以及建议自增列作为主键的理由。
1、InnoDB中的主键
主键（PRIMARY KEY）”的完整称呼是“主键约束”。MySQL主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键应该遵守下面的规则：
- 每个表只能定义一个主键
- 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
- 一个列名只能在复合主键列表中出现一次。
- 复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。
在系统参数中设置sql_require_primary_key参数可以指定表是否强制使用主键primary key，不过使用主键可以避免row-based复制带来的性能问题。假如一张表没有主键，并且更新或删除了多条记录，在replication server端这个操作可以使用单表扫描来完成，但是在使用row-based复制的时候，每一条记录的修改在复制端都会引起全表扫描。而使用主键的时候，则没有这种问题。当设置该参数后，新建表必须带有主键，否则会失败；已经存在主键的表当drop主键的时候会报错；将数据导入一个没有主键的表也会报错。
在《InnoDB存储引擎》一书中指出InnoDB表都有主键，如果没有显示定义主键，则innodb首先判断表中是否有非空的唯一索引，如果有该列即为主键。以下进行测试验证：
1）创建表和几个唯一索引
mysql> create table test01 (id int(11) null,b int(11) not null,c int(11) not null);
Query OK, 0 rows affected, 3 warnings (0.17 sec)
mysql> alter table test01 add unique(id),add unique(c),add unique(b);
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tango.test01;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test01 | CREATE TABLE `test01` (
`id` int DEFAULT NULL,
`b` int NOT NULL,
`c` int NOT NULL,
UNIQUE KEY `c` (`c`),
UNIQUE KEY `b` (`b`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2）插入数据并查询
mysql> insert into tango.test01 (b,c) values (1,2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tango.test01 (b,c) values (10,20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tango.test01 (id,b,c) values (10,20,30);
Query OK, 1 row affected (0.00 sec)
mysql> select id,b,c,_rowid from tango.test01;
+------+----+----+--------+
| id | b | c | _rowid |
+------+----+----+--------+
| NULL | 1 | 2 | 2 |
| NULL | 10 | 20 | 20 |
| 10 | 20 | 30 | 30 |
+------+----+----+--------+
3 rows in set (0.00 sec)
_rowid列是InnoDB存储引擎自动创建的一个6字节大小的指针，此时可看出_rowid是以c列为主键，因选择主键的条件是非空的唯一索引，之后才按照唯一索引的顺序来选择主键。
2、InnoDB中的Cluster Index
2.1 B+Tree结构
B+树是一颗多路平衡查找树，所有节点称为页，页就是一个数据块，里面可以放数据，页是固定大小的，在InnoDB中是16kb。页里边的数据是一些key值，n个key可以划分为n+1个区间，每个区间有一个指向下级节点的指针，每个页之间以双向链表的方式连接，一层中的key是有序的。以磁盘块1这个页为例，他有两个key，17,35，划分了三个区间（-无穷,17) p1,[17, 35) p2, [35, +无穷] p3三个区间，也称扇出为3. p1指向的下级节点里边的key都是比17小的；p2指向的下级节点里边的key大于等于17，小于35；p3指向的下级节点里边的key都大于等于35。
在B+树查找数据的流程：例如要在上边这棵树查找28，首先定位到磁盘1，通过二分的方式找到他属于哪个区间，发现是p2，从而定位到磁盘块3，在磁盘块3的key里边做二分查找，找到p2, 定位到磁盘块8，然后二分找到28这个key。对于数据库来说，查找一个key最终一定会定位到叶子节点，因为只有叶子节点才包含行记录或者主键key。
2.2 InnoDB中的B+tree
InnoDB存储引擎使用B+树作为索引结构，在原B+tree的基础上，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+tree，提高区间访问性能。InnoDB中的B+树具有以下特点：
- 非叶子节点不存储数据，只存储key信息，依key大小顺序排列
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问的效率
以下是innodb中B+索引示例：
2.3 聚簇索引与非聚簇索引
2.3.1 聚簇索引
在InnoDB引擎中，表数据的文件是按照B+树组织的一个索引结构，而聚簇索引就是按照每张表的主键构造出来的B+树，叶子节点就是整张表的行数据，所以聚簇索引的叶子节点也被称为数据页。由于表数据不可能有多份，所以聚簇索引在一张表中有且仅有一个。
由于InnoDB数据结构的关系，InnoDB存储引擎对每张表强制建了聚簇索引：当建了主键，自动以主键构建聚簇索引；没有主键时，自动选择一个可以唯一标识数据记录的列作为主键(唯一非空索引)。以上都不满足时，会自动生成一个长度为6字节的长整型隐含字段作为主键，作为聚簇索引。
使用聚簇索引有以下好处：
- 数据访问快，等值和范围查询都快，因为聚簇索引和数据保存在B+树上，能够直接在叶子节点找到数据，不需要回表查询
- 适合基于主键的排序场景，因为数据本身就是基于主键进行排序的
- 聚簇索引在取一个范围的数据的时候效率更高，当访问同一页数据中的不同行记录时，由于已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。如果按照主键Id来组织数据，获得数据更快
聚簇索引的缺点是插入的速度严重的依赖插入的顺序，按照主键顺序插入效率很高，反之将会产生页分裂，严重影响性能，因此主键的选择和使用显得尤为重要。
2.3.2 非聚簇索引
主键索引就是一种聚簇索引，而其他创建出来的前缀索引、联合索引，唯一索引，都属于非聚簇索引，也称为辅助索引、二级索引。非聚簇索引的叶子节点包含自身索引的键值以及聚簇索引的键(主键)。所有辅助索引的访问都需要经过两次索引的查找：先通过非聚簇索引找到主键值，再根据主键值通过聚簇索引找到对应的行数据，拿着主键key到聚簇索引找行数据的过程称为回表。相对聚簇索引而言，非聚簇索引占用的空间相比聚集索引小，因为它的叶子节点并不包含完整的行数据，只包含主键key。
下面看下使用聚簇索引和非聚簇索引在查询计划上的区别：
1）创建表并插入数据
mysql> create table test02(id int not null,b char(20),c varchar(100) not null, primary key(id));
Query OK, 0 rows affected (0.37 sec)
mysql> insert into tango.test02 values(1,'a','aaaaaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tango.test02 values(2,'b','bbbbbb');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tango.test02 values(3,'c','cccccc');
Query OK, 1 row affected (0.01 sec)
2）使用聚簇索引查询
mysql> explain select * from tango.test02 order by id;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | test02 | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tango.test02 order by b;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | test02 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
对比发现对主键做排序不需要使用filesort
3）使用非聚簇索引查询
#使用聚簇索引order by
mysql> explain select * from tango.test02 where b='a';
+----+-------------+--------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test02 | NULL | ref | idx_b | idx_b | 81 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from tango.test02 order by b;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | test02 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
第一个使用了非聚簇索引访问，第二个按非聚集索引的列做排序，发现会用到filesort，因为没法直接根据索引排序了，需要回表。
3、InnoDB中主键的选择
3.1 自增列作为InnoDB主键
InnoDB存储引擎在使用聚簇索引时，数据记录本身被存于主索引的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时InnoDB不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：
- 使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；
- 该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；
- 除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。
3.2 InnoDB主键的优化
以sysbench测试表为例，主键B树索引大小写在了Data_length，还有一个二级B树索引，即k_1，其大小由Index_length给出。这个sysbench表是按主键顺序插入的，原因是id列是自增的。当你按主键的顺序，有序插入时，即使是innodb_fill_factor设置为100，InnoDB也会最多只用15KB的数据填充其页（out of 16KB）。这允许在初始插入之后，通过更新进行一些行扩展，然后才需要拆分页。
mysql> select count(*), TABLE_NAME,INDEX_NAME, avg(NUMBER_RECORDS), avg(DATA_SIZE) from information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME='`sbtest`.`sbtest1`' group by TABLE_NAME,INDEX_NAME order by count(*) desc;
+----------+--------------------+------------+---------------------+----------------+
| count(*) | TABLE_NAME | INDEX_NAME | avg(NUMBER_RECORDS) | avg(DATA_SIZE) |
+----------+--------------------+------------+---------------------+----------------+
| 687 | `sbtest`.`sbtest1` | PRIMARY | 73.7787 | 15151.2635 |
| 24 | `sbtest`.`sbtest1` | k_1 | 1106.2500 | 14395.2500 |
+----------+--------------------+------------+---------------------+----------------+
2 rows in set (0.29 sec)
主键B树的页，平均有73条记录，并存储少于15KB的数据。索引k_1由sysbench以随机顺序插入。
参考资料：
- http://blog.itpub.net/12219480/viewspace-1733787/
- https://ruby-china.org/topics/26352
- https://blog.csdn.net/m0_46405589/article/details/113788256
- https://www.cnblogs.com/floor/p/13782223.html
转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/119632508
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！
2413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
