MySQL 8.0新特性：持久化自增列学习心得分享

文章目录[隐藏]

问题背景
问题复现
参考文章

问题背景

在MySQL 8.0上偶然发现一个比较奇怪的问题，在使用alter table tab auto_increment=N修改表的自增初始值时，information_schema.tables这个表的auto_increment列并没有同步更新;

问题复现

8.0版本

root@localhost:mysql8006.sock [wjq]>show create table replace_uniq1\G;
*************************** 1. row ***************************
       Table: replace_uniq1
Create 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_ci
1 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)

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)

首先想到的是，数据在buffer中没提交？于是重启一下MySQL，auto_increment列仍是没有变化

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 away
No 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:    8
Current database: wjq

+----------------+
| AUTO_INCREMENT |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

auto_increment还是没有更新为10，难道是MySQL8.0把这个数据记录到其他系统表了？在information_schema，sys，mysql等schema里没发现什么。

于是跑去MySQL5.7版本的去测试了一下，发现当用alter table修改了auto_increment，tables表的auto_increment列是会同步更新的。奇怪了，难道是BUG？

5.7版本实例

root@localhost:mysql3306.sock [wjq]>show create table replace_uniq1\G;
*************************** 1. row ***************************
       Table: replace_uniq1
Create 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
1 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)

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)

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)

一番折腾之后，发现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版本

root@localhost:mysql3306.sock [wjq]>show create table information_schema.tables\G;
*************************** 1. row ***************************
       Table: TABLES
Create 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=utf8
1 row in set (0.00 sec)

8.0版本

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: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

可以发现8.0之后，mysql.tables不再是memory表了，在8.0以后变成视图了。再仔细看一下，auto_increment列引用自mysql.table_stats表的auto_increment列。

到这里已经茅塞顿开了，马上执行一下analyze，不出意外mysql.tables表的auto_increment列就变成最新的10了：

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)

由此可以看出，MySQL 8.0以后，auto_increment方面有了改进的地方，当然不止这里所涉及的，同时在数据字典和系统表方面也做出了一些变化，由表转变为视图，越来越像Oracle了。MySQL自从被Oracle收购以后，真是入乡随俗啊。