問題背景
在MySQL 8.0上偶然發現一個比較奇怪的問題，在使用alter table tab auto_increment=N修改表的自增初始值時，information_schema.tables這個表的auto_increment列並沒有同步更新;
問題復現
8.0版本
<code>root@localhost:mysql8006.sock [wjq]>show create table replace_uniq1\\G;*************************** 1. row *************************** Table: replace_uniq1Create Table: CREATE TABLE `replace_uniq1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec) root@localhost:mysql8006.sock [wjq]>select * from replace_uniq1;+----+---------+| id | content |+----+---------+| 1 | aa || 2 | b || 3 | c || 4 | d || 5 | ee |+----+---------+5 rows in set (0.00 sec)/<code>
<code>root@localhost:mysql8006.sock [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';+----------------+| AUTO_INCREMENT |+----------------+| 6 |+----------------+1 row in set (0.00 sec) root@localhost:mysql8006.sock [wjq]>alter table replace_uniq1 auto_increment=10;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0 root@localhost:mysql8006.sock [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';+----------------+| AUTO_INCREMENT |+----------------+| 6 |+----------------+1 row in set (0.00 sec)/<code>
首先想到的是，數據在buffer中沒提交？於是重啟一下MySQL，auto_increment列仍是沒有變化
<code>root@localhost:mysql8006.sock [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql8006.sock' (2)ERROR: Can't connect to the server root@not_connected:not_connected [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';No connection. Trying to reconnect...Connection id: 8Current database: wjq +----------------+| AUTO_INCREMENT |+----------------+| 6 |+----------------+1 row in set (0.00 sec)/<code>
auto_increment還是沒有更新為10，難道是MySQL8.0把這個數據記錄到其他系統表了？在information_schema，sys，mysql等schema裡沒發現什麼。
於是跑去MySQL5.7版本的去測試了一下，發現當用alter table修改了auto_increment，tables表的auto_increment列是會同步更新的。奇怪了，難道是BUG？
5.7版本實例
<code>root@localhost:mysql3306.sock [wjq]>show create table replace_uniq1\\G;*************************** 1. row *************************** Table: replace_uniq1Create Table: CREATE TABLE `replace_uniq1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) root@localhost:mysql3306.sock [wjq]>select * from replace_uniq1;+----+---------+| id | content |+----+---------+| 1 | aa || 2 | b || 3 | c || 4 | d || 5 | ee |+----+---------+5 rows in set (0.00 sec)/<code>
<code>root@localhost:mysql3306.sock [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';+----------------+| auto_increment |+----------------+| 6 |+----------------+1 row in set (0.00 sec) root@localhost:mysql3306.sock [wjq]>alter table replace_uniq1 auto_increment=10;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0 root@localhost:mysql3306.sock [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';+----------------+| auto_increment |+----------------+| 10 |+----------------+1 row in set (0.00 sec)/<code>
<code>root@localhost:mysql3306.sock [wjq]>select * from replace_uniq1;+----+---------+| id | content |+----+---------+| 1 | aa || 2 | b || 3 | c || 4 | d || 5 | ee |+----+---------+5 rows in set (0.00 sec) root@localhost:mysql3306.sock [wjq]>insert into replace_uniq1(content) values('f');Query OK, 1 row affected (0.01 sec) root@localhost:mysql3306.sock [wjq]>select * from replace_uniq1;+----+---------+| id | content |+----+---------+| 1 | aa || 2 | b || 3 | c || 4 | d || 5 | ee || 10 | f |+----+---------+6 rows in set (0.00 sec)/<code>一番折騰之後，發現MySQL 8.0對自增列出瞭如下的優化：
在5.7及以前，alter table修改的auto_increment值確實是記錄到tables表的auto_increment列的，但是有一個缺點是，tables表的引擎是memory，也就是說，如果MySQL重啟了，這個表記錄的auto_increment值就丟失了。例如表當前的auto_increment值是30，使用alter table將其修改為50，那麼tables表的auto_increment列確實是記錄顯示了50，但如果表沒有插入任何數據，並重啟了MySQL（不管是正常還是異常重啟），下一次運行時，表的auto_increment屬性就會回退到30，同時tables表的auto_increment列顯示的也是30。
那麼既然tables是memory引擎表，重啟之後理應沒有數據才對，為什麼auto_increment列還顯示30呢？
原因是MySQL重啟後，通過select max(col) from tab for update的方式，來選出當前的最大值並賦值給auto_increment。
那麼到了8.0以後，有什麼變化呢？
將自增主鍵的計數器持久化到redo log中。每次計數器發生改變，都會將其寫入到redo log中。如果數據庫發生重啟，InnoDB會根據redo log中的計數器信息來初始化其內存值。為了儘量減小對系統性能的影響，計數器寫入到redo log中，並不會馬上刷新。
除了redo log，在系統表中也會記錄auto_increment的信息，例如mysql.tables表。那麼，在插入新行時，遞增列應該參考redo log還是系統表呢？
而且從上面的例子可以看到，tables記錄根本就不準確。其實答案就一句話，哪個是最新的，就用哪個。
那麼回到系統表的問題上，既然要在重啟後提供參考，總得靠譜一點吧？看一下8.0之前和之後版本對mysql.tables表定義：
5.7版本
<code>root@localhost:mysql3306.sock [wjq]>show create table information_schema.tables\\G;*************************** 1. row *************************** Table: TABLESCreate Table: CREATE TEMPORARY TABLE `TABLES` ( `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '', `ENGINE` varchar(64) DEFAULT NULL, `VERSION` bigint(21) unsigned DEFAULT NULL, `ROW_FORMAT` varchar(10) DEFAULT NULL, `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL, `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL, `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, `DATA_FREE` bigint(21) unsigned DEFAULT NULL, `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL, `CREATE_TIME` datetime DEFAULT NULL, `UPDATE_TIME` datetime DEFAULT NULL, `CHECK_TIME` datetime DEFAULT NULL, `TABLE_COLLATION` varchar(32) DEFAULT NULL, `CHECKSUM` bigint(21) unsigned DEFAULT NULL, `CREATE_OPTIONS` varchar(255) DEFAULT NULL, `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT '') ENGINE=MEMORY DEFAULT CHARSET=utf81 row in set (0.00 sec) /<code>
8.0版本
<code>root@localhost:mysql8006.sock [wjq]>show create table information_schema.tables\\G;*************************** 1. row *************************** View: TABLES Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TABLES` AS select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,internal_table_rows(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `TABLE_ROWS`,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `AVG_ROW_LENGTH`,internal_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_LENGTH`,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `MAX_DATA_LENGTH`,internal_index_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `INDEX_LENGTH`,internal_data_free(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_FREE`,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,internal_update_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `UPDATE_TIME`,internal_check_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,internal_checksum(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)/<code>
可以發現8.0之後，mysql.tables不再是memory表了，在8.0以後變成視圖了。再仔細看一下，auto_increment列引用自mysql.table_stats表的auto_increment列。
到這裡已經茅塞頓開了，馬上執行一下analyze，不出意外mysql.tables表的auto_increment列就變成最新的10了：
<code>root@localhost:mysql8006.sock [wjq]>analyze table replace_uniq1; +-------------------+---------+----------+----------+| Table | Op | Msg_type | Msg_text |+-------------------+---------+----------+----------+| wjq.replace_uniq1 | analyze | status | OK |+-------------------+---------+----------+----------+1 row in set (0.01 sec) root@localhost:mysql8006.sock [wjq]>select auto_increment from information_schema.tables where table_schema='wjq' and table_name='replace_uniq1';+----------------+| AUTO_INCREMENT |+----------------+| 10 |+----------------+1 row in set (0.01 sec)/<code>
由此可以看出，MySQL 8.0以後，auto_increment方面有了改進的地方，當然不止這裡所涉及的，同時在數據字典和系統表方面也做出了一些變化，由錶轉變為視圖，越來越像Oracle了。MySQL自從被Oracle收購以後，真是入鄉隨俗啊。
參考文章
https://yq.aliyun.com/articles/60885
https://blog.csdn.net/weixin_39004901/article/details/84346093
分享到:
閱讀更多 辛酸的人兒 的文章
