MySQL自增ID的知识点总结
1. 使用自增ID的优缺点
-
优点
- 主键页以近乎顺序的方式填写，提升了页的利用率
- 索引更加紧凑，性能更好查询时数据访问更快
- 节省空间
- 连续增长的值能避免 b+ 树频繁合并和分裂
- 简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已
-
缺点
- 可靠性不高
存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。
- 安全性不高
ID不够随机，对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少（泄露发号数量的信息），也可以非常容易地通过接口进行数据的爬取，因此不太安全。
- 性能差
自增ID的性能较差，需要在数据库服务器端生成。对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。
- 交互多
业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
- 局部唯一性
最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。
不利于数据迁移与扩展
-
不适合以自增ID主键作为主键的情况
- 数据量多需要分库分表，可能会造成ID重复
- 经常会遇到数据迁移的情况
- 新数据需要和老数据进行合并
参考文档链接：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 。
评论区