MySQL · 主键

发表于 | 更新于 | 分类于 | 阅读次数：

背景

问题：

有一张表，里面有 id 自增主键，当 insert 了 17 条记录之后，删除了第 15 16 17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 id 是 18 还是 15 ？

答案：

MySQL 8.0 之前

  • 如果是 MyISAM 表，数据库重启后，id 值为 18
  • 如果是 InnoDB 表，数据库重启后，id 值为 15

MySQL 8.0 开始

  • 如果是 MyISAM 表，数据库重启后，id 值为 18
  • 如果是 InnoDB 表，数据库重启后，id 值为 18

原理

不同存储引擎自增主键实现机制不同，同一存储引擎不通版本实现机制不同。

MyISAM：自增主键的最大 id 记录在数据文件里，重启后不会消失。

InnoDB

  • MySQL 5.7 及之前版本，自增主键最大 id 保存在内存里，重启数据库或者是对表进行 optimize 操作，都会导致最大 id 丢失，重启后将 max(id)+1 作为这个表当前的自增值。
  • MySQL 8.0 版本，自增主键最大 id 记录在了 redo log 中，重启后依靠 redo log 恢复重启之前的值。

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
mysql> select * from employees_innodb;
+----+------------+--------------------+-----+---------------------+---------------------+
| id | name       | address            | age | gmt_create          | gmt_modified        |
+----+------------+--------------------+-----+---------------------+---------------------+
|  1 | xujinliang | 山东省青岛市       |  32 | 2019-09-24 17:13:00 | 2019-09-24 17:13:00 |
|  2 | maofang    | 山东省青岛市       |  32 | 2019-09-24 17:32:00 | 2019-09-24 17:40:49 |
|  3 | raohui     | 陕西省西安市       |  29 | 2019-09-24 17:39:28 | 2019-09-24 17:39:28 |
|  4 | yangnan    | NULL               |  22 | 2019-09-24 17:39:52 | 2019-09-24 17:39:52 |
|  5 | yuzhiqiang | 山东省青岛市       |  29 | 2019-10-24 10:39:53 | 2019-10-24 10:39:53 |
+----+------------+--------------------+-----+---------------------+---------------------+
5 rows in set (0.00 sec)

# 删除id为5的记录，重启MySQL服务，再次插入id变成5（max(id)+1）
mysql> select * from employees;
+----+------------+--------------------+-----+---------------------+---------------------+
| id | name       | address            | age | gmt_create          | gmt_modified        |
+----+------------+--------------------+-----+---------------------+---------------------+
|  1 | xujinliang | 山东省青岛市       |  32 | 2019-09-24 17:13:00 | 2019-09-24 17:13:00 |
|  2 | maofang    | 山东省青岛市       |  32 | 2019-09-24 17:32:00 | 2019-09-24 17:40:49 |
|  3 | raohui     | 陕西省西安市       |  29 | 2019-09-24 17:39:28 | 2019-09-24 17:39:28 |
|  4 | yangnan    | NULL               |  22 | 2019-09-24 17:39:52 | 2019-09-24 17:39:52 |
|  5 | liu        | 山东省烟台市       |  22 | 2019-10-24 11:03:14 | 2019-10-24 11:03:14 |
+----+------------+--------------------+-----+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> select * from employees_myisam;
+----+------------+--------------------+-----+---------------------+---------------------+
| id | name       | address            | age | gmt_create          | gmt_modified        |
+----+------------+--------------------+-----+---------------------+---------------------+
|  1 | xujinliang | 山东省青岛市       |  32 | 2019-09-24 17:13:00 | 2019-09-24 17:13:00 |
|  2 | maofang    | 山东省青岛市       |  32 | 2019-09-24 17:32:00 | 2019-09-24 17:40:49 |
|  3 | raohui     | 陕西省西安市       |  29 | 2019-09-24 17:39:28 | 2019-09-24 17:39:28 |
|  4 | yangnan    | NULL               |  22 | 2019-09-24 17:39:52 | 2019-09-24 17:39:52 |
|  5 | yuzhiqiang | 山东省青岛市       |  29 | 2019-10-24 10:39:53 | 2019-10-24 10:39:53 |
+----+------------+--------------------+-----+---------------------+---------------------+
5 rows in set (0.00 sec)

# 删除id为5的记录，重启MySQL服务，再次插入id变成6
mysql> select * from employees_myisam;
+----+------------+--------------------+-----+---------------------+---------------------+
| id | name       | address            | age | gmt_create          | gmt_modified        |
+----+------------+--------------------+-----+---------------------+---------------------+
|  1 | xujinliang | 山东省青岛市       |  32 | 2019-09-24 17:13:00 | 2019-09-24 17:13:00 |
|  2 | maofang    | 山东省青岛市       |  32 | 2019-09-24 17:32:00 | 2019-09-24 17:40:49 |
|  3 | raohui     | 陕西省西安市       |  29 | 2019-09-24 17:39:28 | 2019-09-24 17:39:28 |
|  4 | yangnan    | NULL               |  22 | 2019-09-24 17:39:52 | 2019-09-24 17:39:52 |
|  6 | yuzhiqiang | 山东省青岛市       |  29 | 2019-10-24 10:58:32 | 2019-10-24 10:58:32 |
+----+------------+--------------------+-----+---------------------+---------------------+
5 rows in set (0.00 sec)

主键的选择

① 从性能和存储空间方面考量，自增主键往往是更好的选择：

  • 存储空间少，自增主键一般用整型做主键，则二级索引叶子节点只要 4 个字节；而如果使用业务字段做主键，比如字符串类型的身份证号，每个二级索引的叶子节点得占用约 20 个字节。
  • 性能好，插入数据时，自增主键能保证有序插入，避免了页分裂。

② 自增 id 是整型字段，推荐使用 unsigned int 类型来定义增长主键，可以存储 42 亿数据。

类型 大小 范围（有符号） 范围（无符号）
int 4 byte (-2147483648, 2147483647) (0, 4294967295)
bigint 8 byte (-9223372036854775808, 9223372036854775807) (0, 18446744073709551615)

③ 自增 id 是增长的，但不一定连续，造成自增 id 不连续的情况可能有：

  • 唯一键冲突
  • 事务回滚
  • insert ... select 语句批量申请自增 id