MySQL自增ID的知识点总结

MySQL自增ID的知识点总结

1. 使用自增ID的优缺点

• 优点

  1. 主键页以近乎顺序的方式填写，提升了页的利用率
  2. 索引更加紧凑，性能更好查询时数据访问更快
  3. 节省空间
  4. 连续增长的值能避免 b+ 树频繁合并和分裂
  5. 简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已

• 缺点

  1. 可靠性不高

  存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

  2. 安全性不高

  ID不够随机，对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少（泄露发号数量的信息），也可以非常容易地通过接口进行数据的爬取，因此不太安全。

  3. 性能差

  自增ID的性能较差，需要在数据库服务器端生成。对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。

  4. 交互多

  业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

  5. 局部唯一性

  最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

  不利于数据迁移与扩展

• 不适合以自增ID主键作为主键的情况

  1. 数据量多需要分库分表，可能会造成ID重复
  2. 经常会遇到数据迁移的情况
  3. 新数据需要和老数据进行合并

  参考文档链接：https://blog.csdn.net/qq_62982856/article/details/127963602

2. 自增id达到最大后继续写入数据测试

mysql> show create table test_count;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_count | CREATE TABLE `test_count` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `qid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'qid',
  `ip` varchar(100) NOT NULL DEFAULT '' COMMENT '操作者ip',
  `ip2` varchar(100) DEFAULT NULL COMMENT '操作者ip',
  PRIMARY KEY (`id`),
  KEY `idx_ip` (`ip`),
  KEY `idx_ip2` (`ip2`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='机构后台操作LOG表'                  |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test_count(id,qid) values (18446744073709551615,105);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_count;
+----------------------+-----+-----------+-----------+
| id                   | qid | ip        | ip2       |
+----------------------+-----+-----------+-----------+
|                    1 | 101 | 127.0.0.1 | 127.0.0.2 |
|                    2 | 102 | 127.0.1.1 | NULL      |
|                    3 | 103 | 127.0.1.3 | NULL      |
|                    4 | 104 |           | NULL      |
| 18446744073709551615 | 105 |           | NULL      |
+----------------------+-----+-----------+-----------+
5 rows in set (0.00 sec)

mysql> insert into test_count(qid) values (106);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

可以看到，MySQL在自增id使用完后，新数据就写不进去了。

删除第一条数据，再次测试：

mysql> select * from test_count;
+----------------------+-----+-----------+------+
| id                   | qid | ip        | ip2  |
+----------------------+-----+-----------+------+
|                    2 | 102 | 127.0.1.1 | NULL |
|                    3 | 103 | 127.0.1.3 | NULL |
|                    4 | 104 |           | NULL |
| 18446744073709551615 | 105 |           | NULL |
+----------------------+-----+-----------+------+
4 rows in set (0.00 sec)

mysql> insert into test_count(qid) values (106);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

一样写入不进去，跟是否存在数据无关（并不是受数据冲突影响）。（MySQL Server version: 5.7.39 ）

测试一下MySQL 8.0：

mysql> insert into test_count(id,qid) values (18446744073709551615,105);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_count;
+----------------------+-----+----+------+
| id                   | qid | ip | ip2  |
+----------------------+-----+----+------+
| 18446744073709551615 | 105 |    | NULL |
+----------------------+-----+----+------+
1 row in set (0.00 sec)

mysql> insert into test_count(qid) values (106);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

得到的结论一样。

3. 自增id回溯测试

MySQL 8.0 对自增id进行了持久化，会写入到idb文件中，所以重启后不会发生回溯的问题。下面仅对5.7版本进行测试复现。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.39    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from test_count;
+----------------------+-----+-----------+------+
| id                   | qid | ip        | ip2  |
+----------------------+-----+-----------+------+
|                    2 | 102 | 127.0.1.1 | NULL |
|                    3 | 103 | 127.0.1.3 | NULL |
|                    4 | 104 |           | NULL |
| 18446744073709551615 | 105 |           | NULL |
+----------------------+-----+-----------+------+
4 rows in set (0.00 sec)

mysql> delete from test_count where id = 18446744073709551615;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_count;
+----+-----+-----------+------+
| id | qid | ip        | ip2  |
+----+-----+-----------+------+
|  2 | 102 | 127.0.1.1 | NULL |
|  3 | 103 | 127.0.1.3 | NULL |
|  4 | 104 |           | NULL |
+----+-----+-----------+------+
3 rows in set (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

-- 重启数据库服务
systemctl start mysqld

-- 连接数据库进行测试
mysql> insert into test_count(qid) values (106);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_count;
+----+-----+-----------+------+
| id | qid | ip        | ip2  |
+----+-----+-----------+------+
|  2 | 102 | 127.0.1.1 | NULL |
|  3 | 103 | 127.0.1.3 | NULL |
|  4 | 104 |           | NULL |
|  5 | 106 |           | NULL |
+----+-----+-----------+------+
4 rows in set (0.00 sec)

可以看到，重启后的自增起始值等于当前表的 max(id)+1 。