常州 网站设计,wordpress keyshot,网站开发后端所需要的语言,城乡建设工程信息网前几天偶然看到大家在讨论一道面试题#xff0c;而且答案也不够统一#xff0c;我感觉蛮有意思#xff0c;在此就做一个解读#xff0c;整个过程中确实会有几处反转。
作者#xff1a;杨建荣的学习笔记来源#xff1a;今日头条
前几天偶然看到大家在讨论一道面试题而且答案也不够统一我感觉蛮有意思在此就做一个解读整个过程中确实会有几处反转。
作者杨建荣的学习笔记来源今日头条
前几天偶然看到大家在讨论一道面试题而且答案也不够统一我感觉蛮有意思在此就做一个解读整个过程中确实会有几处反转。 我们先来看下题目
一张表里面有ID自增主键当insert了17条记录之后删除了第15,16,17条记录再把MySQL重启再Insert一条记录这条记录的ID是18还是15.
和后面的一些题目整体来看难度不大都是一些看起来很基础的问题但是这道题目引起了我的注意因为这道题目的背景过于开放所以答案也是不固定的而这也是我们在技术学习中需要保持的严谨态度。
首先这道题整体来看想表达的是对于MySQL中自增列的理解。
按照我们常规理解的逻辑ID自增应该是18按照这个逻辑怎么都不应该是15吧?
但是这个答案对吗?显然不是我们进入第一轮反转。
确实对于自增列的问题这个是MySQL里面饱受诟病的老问题了。如果节点重启会从数据列中按照max(id)1的方式来处理在多环境历史数据归档的情况下如果主库重启很可能会出现数据不一致的情况记得在MySQL bug中很多人留言说十多年前的老问题了怎么还不解决。
而在OpenWorld上面Percona CEO Peter也再次提到了这个问题。 我认真查了一下这个bug的历史巧合的是这个问题是Peter在十几年前提出的时光荏苒一直没有修复。 好的按照MySQL bug的思路来理解答案应该是15了。
但是这个答案对吗?显然不是我们进入第二轮反转。
这个题目的背景是不够清晰的这个表的存储引擎没有说是InnoDB还是MyISAM,所以存在不确定性这么说的意义在于自增列的信息在MyISAM和InnoDB中的维护逻辑是不大一样的在MyISAM中是存储持久化在文件中的当数据库重启之后是可以通过持久化的信息持续对ID进行自增的而InnoDB的自增列信息既不在.frm文件也不在.ibd文件中所以在此启动的时候会按照max(id)1的算法进行修复。
所以如果是MyISAM,则答案应该是18而如果是InnoDB则答案是15。
我们可以综合对比用一个小的测试来模拟复现我们选择的是MySQL 5.7环境。
为了对比明显我们创建两张表test_innodb和test_myisam分别对应InnoDB和MyISAM存储引擎来做同样的操作看看重启后的差异情况。
create table test_innodb(id int primary key auto_increment,name varchar(30))
engineinnodb;
create table test_myisam(id int primary key auto_increment,name varchar(30)) enginemyisam;
插入几行数据查看数据
insert into test_innodb(name) values(aa),(bb),(cc);Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
insert into test_myisam(name) values(aa),(bb),(cc); Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看两张表的数据情况数据是完全一样。
select *from test_innodb;----------| id | name |----------| 1 | aa || 2| bb || 3 | cc |----------3 rows in set (0.00 sec)
select *from test_myisam;----------| id | name |----------| 1 | aa || 2| bb || 3 | cc |----------3 rows in set (0.00 sec) 在1,23的基础上我们继续插入值为5跳过id值为4。
insert into test_innodb(id,name) values(5,ee);Query OK, 1 row affected (0.00 sec)
insert into test_myisam(id,name) values(5,ee); Query OK, 1 row affected (0.00 sec)
此时查看test_innodb自增列已经开始增长值为6。
show create table test_innodb\G CREATE TABLE test_innodb
( id int(11) NOT AUTO_INCREMENT, name varchar(30) DEFAULT , PRIMARY KEY (id)) ENGINEInnoDB
AUTO_INCREMENT6 DEFAULT CHARSETutf81 row in set (0.00 sec)
删除id5的记录
delete from test_innodb where id5;Query OK, 1 row affected (0.01 sec)
删除记录之后自增列还是保持不变。
show create table test_innodb\G CREATE TABLE
test_innodb ( id int(11) NOT AUTO_INCREMENT, name varchar(30) DEFAULT ,
PRIMARY KEY (id)) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf81 row in set (0.00 sec)
同理test_myisam也做同样的测试结果是完全一样的在此略过日志。
我们停止数据库
shutdown;Query OK, 0 rows affected (0.00 sec)
重启数据库
#mysqld_safe --defaults-file/data/mysql_5723/my.cnf
此时查看test_innodb和test_myisam的自增列就开始出现差异了。
MyISAM存储引擎的表test_myisam的自增列还是不变为6。
show create table test_myisam\G CREATE TABLE test_myisam ( id int(11) NOT AUTO_INCREMENT,
name varchar(30) DEFAULT , PRIMARY KEY (id)) ENGINEMyISAM AUTO_INCREMENT6DEFAULT CHARSETutf81 row in set (0.00 sec)
而InnoDB存储引擎的表test_innodb的自增列却变为了4。
show create table test_innodb\G ***************************
1. row *************************** Table: test_innodbCreate Table:CREATE TABLE test_innodb( id int(11) NOT AUTO_INCREMENT, name varchar(30) DEFAULT ,
PRIMARY KEY (id)) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8
我们继续插入一条数据保持id列自增。
insert into test_innodb(name) values(ee);Query OK, 1 row affected (0.00 sec)
insert into test_myisam(name) values(ee); Query OK, 1 row affected (0.00 sec)
可以看到两张表的id列已经分道扬镳了。
select *from test_innodb; ----------| id | name |----------| 1 | aa || 2 | bb || 3 | cc || 4 | ee |----------4 rows in set (0.00 sec)
select *from test_myisam; ----------| id | name |----------| 1 | aa || 2 | bb || 3 | cc || 6 | ee |----------4 rows in set (0.00 sec)
小结对于MyISAM和InnoDB的表因为存储引擎对于自增列的实现机制不同ID值也可能会有所不同对于InnoDB存储引擎的表ID是按照max(id)1的算法来计算的。 但是这个答案对吗?显然不是因为还是不够严谨我们进入第三轮反转。
这个问题不够严谨是因为技术是逐步发展的这个问题在MySQL 8.0中有了答案对于InnoDB的自增列信息如果断电之后会直接丢失很可能造成级联从库间的数据同步出现问题而在MySQL 8.0之后这个信息写入了共享表空间中所以服务重启之后还是可以继续追溯这个自增列的ID变化情况的。
限于篇幅因为测试日志是很相似的我就直接给出测试后的日志这是在数据库重启之后的自增列情况可以看到test_innodb和test_myisam的自增列是完全一样的。
mysql show create table test_myisam\G***************************
1. row *************************** Table: test_myisamCreate Table:
CREATE TABLE test_myisam( id int(11) NOT AUTO_INCREMENT, name varchar(30) DEFAULT , PRIMARY KEY (id)) ENGINEMyISAM AUTO_INCREMENT6 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1
row in set (0.00 sec)
mysql show create table test_innodb\G ***************************
1. row *************************** Table: test_innodbCreate Table: CREATE TABLE test_innodb
( id int(11) NOT AUTO_INCREMENT, name varchar(30) DEFAULT , PRIMARY KEY (id)) ENGINEInnoDB AUTO_INCREMENT
6 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1 row in set (0.00 sec)
我们做一个小结
在MySQL 8.0之前
1)如果是MyISAM表则数据库重启后ID值为18
2)如果是InnoDB表则数据库重启后ID值为15
在MySQL 8.0开始
1)如果是MyISAM表则数据库重启后ID值为18
2)如果是InnoDB表则数据库重启后ID值为18
此处需要补充的是对于ID自增列在MySQL 5.7中可以使用sys schema来进行有效监控了,可以查看视图schema_auto_increment_columns 来进行列值溢出的有效判断。
更难能可贵的是如果是MySQL 5.7版本以下虽然没有sys schema特性但是可以复用MySQL 5.7中的schema_auto_increment_columns 的视图语句也是可以对列值溢出进行有效判断的。
阅读目录置顶)(长期更新计算机领域知识https://blog.csdn.net/weixin_43392489/article/details/102380691
阅读目录置顶)(长期更新计算机领域知识https://blog.csdn.net/weixin_43392489/article/details/102380882
阅读目录置顶)(长期科技领域知识https://blog.csdn.net/weixin_43392489/article/details/102600114
