澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:新特性之自增主键的持久

2019-11-24 作者:数据库网络   |   浏览(91)

自增主键未有长久化是个相比早的bug,那点从其在官方bug网址的id号也可看出(卡塔尔。由PeterZaitsev(现Percona 高管卡塔尔于二零零二年提议。历史持久且臭名昭着。 

 

澳门新萄京官方网站 1

悲剧啊!Mysql的上古BUG!!!

导读那是MySQL8.0修复的上古bug之一,在二零零四年由Percona的高管(那时候应有还未Percona吧卡塔尔国提出的bug#199,光看这bug号就扑面而来一股上古时期的沧海桑田气息。

澳门新萄京官方网站 2
标题标庐山真面目在于InnoDB最先化AUTO_INCREMENT的艺术,在历次重启时,总是算出表上最大的自增值作为最大值,下叁遍分配从该值初步。那表示风度翩翩旦在btree左边叶节点一大波去除记录,重启后,自增值可能被收音和录音。那在广大气象下只怕招致难题,包罗但不防止:主备切换、历史数据迁移等景色。在bug#199下边一大堆的上涨里,可以看来大量的同行抱怨。

官方的修补就相比高尚了,不转移任何现成的积攒,而是经过redo log来开展复原。该补丁基于WL#7816的框架完毕的,要想搞懂这一个补丁,得先看看WL#7816做了何等改革,为了消除这么些标题,InnoDB使用二个斯特林发动机私有的连串表 特殊redo log的方法,在发动机内部团结消弭corruption标志长久化的难点。其概况思路为:

  1. 当开掘索引损坏时,写入一条redo log,但不立异数据词典
  2. 引进二个innodb引擎私有的系统表,称为DD Buffer Table,每回checkpoint早前会将索引corruption bit存入个中。
  3. 在崩溃恢复生机时,同期从redo log和DD Buffer Table中读取索引 corruption bit, 合併结果,并标志内部存款和储蓄器中的表和索引对象。

初始化Persister

现阶段Persister的体系唯有三种,一个用来corruption bit的长久化,二个用以自增列的长久化,对应的类为:

Persister:    |-- CorruptedIndexPersister    |-- AutoIncPersister

Persister对应全局对象dict_persist_t::persisters,能够经过品种persistent_type_t来找到相应的Persister,近来唯有PM_INDEX_CORRUPTED及PM_TABLE_AUTO_INC,但从注释来看,将来早晚上的集会做越来越多的扩张,Persister在运转时调用函数dict_persist_init举办初始化。

新的系统表

新的种类表名叫SYS_TABLE_INFO_BUFFE景逸SUV,对应管理类为DDTableBuffer,指针存款和储蓄在dict_澳门新萄京官方网站,persist->table_buffer中。

系统表富含多少个列:TABLE_ID及BLOB类型的METADATA(ref DDTableBuffer::init卡塔尔国,METADATA列包涵了独具必要长久化的元数据。

回写DDTableBuffer

有三种状态会将内部存款和储蓄器更改回写到DDTableBuffer中:

  1. 在做checkpoint(log_checkpoint)之前,所有在dirty_dict_tables链表上的表对象,对应persist metadata都须要回写到DDTableBuffer中(dict_persist_to_dd_table_buffer)
  2. 从内部存款和储蓄器中驱逐多少个表对象时(dict_table_remove_from_cache_low),假若须要的话也会去尝尝回写。
  3. 在对含有自增列的表做DDL后,要求持久化counter,在如下函数中,会调用dict_table_set_and_persist_autoinc:
ha_innobase::commit_inplace_alter_tablecreate_table_info_t::initialize_autoinc()// for example: alter table..auto_increment = ??row_rename_table_for_mysql;// rename from temporary table to normal table

回写的历程也比较轻便(dict_table_persist_to_dd_table_buffer_low):

  1. 由此表对象起头化必要回写的Metadata数据: corrupt index及autoinc值(dict_init_dynamic_metadata)
  2. 塑造记录值,插入DDTableBuffer系统表(DDTableBuffer::replace(), 要是记录存在的话,则实行悲观更新操作
  3. 表对象的diry_status修改成 METADATA_BUFFERED,表示有buffer的元数据

Recovery and Startup

在崩溃苏醒时,当深入分析到日志MLOG_TABLE_DYNAMIC_META时(MetadataRecover::parseMetadataLog),博览会开深入分析并将深入分析获得的数额存储到集合中(MetadataRecover::m_tables),如若存在相似table-id的项,就进展交流,确认保证总是最新的。

在做到recovery后,网罗到的meta音信一时半刻积累到srv_dict_metadata中, 随后展开apply(srv_dict_recover_on_restart), apply的进程也比较轻便,载入表对象,然后对表对象开展更新(MetadataRecover::apply卡塔尔,例如对于autoinc列,就三回九转挑肥拣瘦更加大的老大值。

最后

其风流洒脱bug已经挂了相当短的岁月,不消亡把那一个bug当做InnoDB的“性子”的同校,一定要留心到那些改变...


原著来自:

正文地址:

导读 这是MySQL8.0修复的上古bug之大器晚成,在二零零四年由Percona的老总(这时应该还未有Percona吧卡塔 尔(英语:State of Qatar)提议的bug#澳门新萄京官方网站:新特性之自增主键的持久化,相关知识点总结。199,光看这bug号就...

第大器晚成,直观的复出下。

MySQL的自增列(AUTO_INCREMENT卡塔 尔(英语:State of Qatar)和别的数据库的自增列相比较,有过多表征和分裂点(以致不一致存款和储蓄引擎、分歧版本也会有部分不等的风味卡塔 尔(英语:State of Qatar),令人倍感稍稍稍稍复杂。上边大家从一些测量检验开始,来认知、了然一下那地点的特别知识点:

    在二回宕机之后重启Mysql服务器并上涨数据的长河中窥见了自增主键列的自增数值会回落,引致有多少上有冲突。经过生龙活虎番的排查之后察觉原本是主键自增值回落了,导致自增主键有再次招致外键关联失效引起的。

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
 ---- 
3 rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.35 sec)

mysql> select * from t1;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  4 |
 ---- 
3 rows in set (0.01 sec)

 

小编们来举个例证说澳优(Ausnutria Hyproca卡塔尔国(Karicare卡塔尔下切实可行的风貌,比方,创制八个个InNoDB引擎表:

 

 

CREATE TABLE `bsession`  (

固然id为3的笔录删除了,但再度插入null值时,并未引用被剔除的3,而是分配了4。

自增列长久化难题

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

 

 

`aname` varchar(80) NOT NULL DEFAULT ‘’,

除去id为4的笔录,重启数据库,重新插入一个null值。

假如四个表具备自增列,当前最大自增列值为9, 删除了自增列6、7、8、9的记录,重启MySQL服务后,再往表里面插入数据,自增列的值为6依然10吗?  假如表的存储引擎为MyISAM呢,又会是什么样状态? 上边实验遭遇为MySQL 5.7.21

PRIMARY KEY (`id`)

 

 

) ENGINE=InnoDB AUTO_澳门新萄京官方网站:新特性之自增主键的持久化,相关知识点总结。INCREMENT=0 DEFAULT CHARSET=utf8;

mysql> delete from t1 where id=4;
# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  3 |
 ---- 
3 rows in set (0.00 sec)

 

澳门新萄京官方网站 3

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.08 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

 

 

mysql> insert into test(name)

    -> select 'kkk1' from dual union all

    -> select 'kkk2' from dual union all

    -> select 'kkk3' from dual union all

    -> select 'kkk4' from dual union all

    -> select 'kkk5' from dual union all

    -> select 'kkk6' from dual union all

    -> select 'kkk7' from dual union all

    -> select 'kkk8' from dual union all

    -> select 'kkk9' from dual;

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

 

mysql> select * from test;

 ---- ------ 

| id | name |

 ---- ------ 

|  1 | kkk1 |

|  2 | kkk2 |

|  3 | kkk3 |

|  4 | kkk4 |

|  5 | kkk5 |

|  6 | kkk6 |

|  7 | kkk7 |

|  8 | kkk8 |

|  9 | kkk9 |

 ---- ------ 

9 rows in set (0.00 sec)

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

始建表测验表

可以看来,新插入的null值分配的是3,遵照重启前的操作逻辑,此处应该分配5呀。

 

方今安顿10条数据,再删除最终的几条,

 

重启MySQL服务后,然后大家插入一条记下,字段ID会从哪些值在此以前吧? 如下所示,要是表的囤积引擎为InnoDB,那么插入的多寡的自增字段值为6.

INSERT INTO `bsession` (`aname`) values ('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a’);

那就是自增主键未有长久化的bug。究其原因,在于自增主键的分配,是由InnoDB数据字典里面贰个计数器来决定的,而该计数器只在内部存款和储蓄器中维护,并不会持久化到磁盘中。当数据库重启时,该流速計会通过下面这种格局开端化。

 

DELETE FROM `bsession` where `id` in (8,9,10);

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

 

澳门新萄京官方网站 4

 

澳门新萄京官方网站 5

有样学样数据操作

MySQL 8.0的化解思路

 

除去操作之后,表中的数目独有7条,最大的’id’ = 7。现在开展数据库重启,并再度插入10条数据。那时自增列是从8起初计数,依旧从11发端计数呢?作者想许三个人皆认为会从11开端计数,生成新记录,但事实上景况会超越大家的料想,上边咱们实际验证一下:

 

 

澳门新萄京官方网站 6

将自增主键的计数器持久化到redo log中。每一遍流速计产生转移,都会将其写入到redo log中。假使数据库发生重启,InnoDB会依照redo log中的流速计新闻来初阶化其内部存款和储蓄器值。为了尽也许减小对系统质量的震慑,流速計写入到redo log中,并不会应声刷新。具体可参谋:

接下去,大家创制一个MyISAM类型的测量检验表。如下所示:

知情者神跡

 

 

您早晚上的集会问了,那是干什么吧?

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment  primary key, name varchar(32)) engine=MyISAM;

Query OK, 0 rows affected (0.02 sec)

 

mysql> 

 

insert into test(name)

select 'kkk1' from dual union all

select 'kkk2' from dual union all

select 'kkk3' from dual union all

select 'kkk4' from dual union all

select 'kkk5' from dual union all

select 'kkk6' from dual union all

select 'kkk7' from dual union all

select 'kkk8' from dual union all

select 'kkk9' from dual;

 

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

长期以来笔者也认为到很想拿到,于是就精通MySql的InnoDB引擎是何等管理自增列的:

因自增主键未有漫长化而现身难点的常见景观:

 

原因是InnoDB引擎对AUTO_INCREMENT计数器是贮存在到主内存中的,并不是硬盘。所以当重启后内部存款和储蓄器数据就吐弃了!

  1. 事务将自增主键作为职业主键,同期,业务上又须求主键不能够再度。

  2. 数码会被归档。在归档的历程中有望会时有爆发主键冲突。

去除了id>=6的笔录后,重启MySQL服务,如下所示,测量检验结果为id =10, 那么为何现身分歧的多少个结实吧?那些是因为InnoDB存款和储蓄引擎中,自增主键没有悠久化,而是位于内部存款和储蓄器中,关于自增主键的分配,是由InnoDB数据字典里面三个流速計来决定的,而该流速计只在内部存款和储蓄器中爱护,并不会悠久化到磁盘中。当数据库重启时,该流速计会通过SELECT MAX(ID) FROM TEST FOR UPDATE这样的SQL语句来初叶化(差别表对应分歧的SQL语句卡塔尔国, 其实那是贰个bug来着, 对应的链接地址为: 8.0 ,才将自增主键的计数器长久化到redo log中。每便流量计爆发转移,都会将其写入到redo log中。假如数据库发生重启,InnoDB会依据redo log中的计数器消息来起始化其内存值。 而对应与MySIAM存款和储蓄引擎,自增主键的最大值存放在数据文件个中,每一次重启MySQL服务都不会潜濡默化其值变化。

小编们来看官方文书档案:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html,中的【InnoDB AUTO_INCREMENT Counter Initialization】黄金时代节的亲力亲为表达:

 

 

澳门新萄京官方网站 7

于是,刚强提出不要选择自增主键作为专业主键。刨除那四个现象,其实,自增主键未有长久化的难点并非非常的大,远未有想象中的”臭名昭着“。

 

MySQL官方网址表达

 

澳门新萄京官方网站 8

只是那特性子将要Mysql的下三个版本8.0中改动,自增流速计每一趟改造时,当前的最大自增流速计值将会被写入redo log中,并保存到种种检查点的 InnoDB引擎的私有系统表中,实现自增流量计的长久化,重启后会保持豆蔻年华致。

最终,给出一个归档场景下的解决方案,

 

当性格很顽强在艰难险阻或巨大压力面前不屈器在Crash中的恢复生机重启进程中,InnoDB使用存储在系统字典表里的眼下最大自增值早先化到内部存款和储蓄器,并且从最后贰个检查点开头扫描Redo Log中写入的流速計值。假若Redo Log中的值超越内部存款和储蓄器中的流速計值,Redo Log中的值将会被利用。

 

 

有关后续版本中对于 自增列的管理体制 请查看官方文书档案的亲力亲为表明,这里不在赘述。https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

始建三个仓库储存进度,依照table2(归档表卡塔尔国自增主键的最大值来开首化table1(在线表卡塔尔国。那么些蕴藏进程可放置init_file参数钦定的文件中,该公文中的SQL会在数据库运维时推行。

 

DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat('SELECT @max1 := (`id`   1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
    prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
    set @qry = concat('SELECT @max2 := (`id`   1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
    prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1 < @max2 THEN
    set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT 'updated' as `status`;
else
SELECT 'no update needed' as `status`;
END IF;
END ;;
DELIMITER ;

自增列细节性格

 

 

1:SQL模式的NO_AUTO_VALUE_ON_ZERO值影响AUTO_INCREMENT列的一颦一笑。

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> select @@sql_mode;

 ------------------------------------------------------------------------------------------------------------------------------------------- 

| @@sql_mode                                                                                                                                |

 ------------------------------------------------------------------------------------------------------------------------------------------- 

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

 ------------------------------------------------------------------------------------------------------------------------------------------- 

1 row in set (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> 

 

如上所示,假使在SQL方式里面未有设置NO_AUTO_VALUE_ON_ZERO的话,那么在暗许设置下,自增列暗许通常从1发端自增,插入0或然null代表生成下一个自增进值。纵然顾客期望插入的值为0,而该列又是自增加的,那么那些选项就必需设置

 

mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  0 | kerry |

|  1 | kerry |

 ---- ------- 

2 rows in set (0.00 sec)

 

mysql> 

 

 

 

2:如若把二个NULL值插入到八个AUTO_INCREMENT数据列里去,MySQL将自动生成下三个行列编号。如下所示,这么些语法对于熟识SQL Server中自增字段的人来来看,几乎就是难以置信的专门的学业。

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.03 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into test(id , name) value(null, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

 

 

3:获取当前自增列的值

 

    获取当前自增列的值,能够动用 LAST_INSERT_ID函数,注意,那么些是三个系统函数,可获取自增列自动生成的末段二个值。但该函数只与服务器的此次对话进度中变化的值有关。如果在与服务器的此番对话中尚无生成AUTO_INCREMENT值,则该函数再次来到0

 

mysql> select last_insert_id();

 ------------------ 

| last_insert_id() |

 ------------------ 

|                1 |

 ------------------ 

1 row in set (0.00 sec)

 

mysql> insert into test(name) value('jimmy');

Query OK, 1 row affected (0.00 sec)

 

mysql> select last_insert_id();

 ------------------ 

| last_insert_id() |

 ------------------ 

|                2 |

 ------------------ 

1 row in set (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

|  2 | jimmy |

 ---- ------- 

2 rows in set (0.00 sec)

 

就算要博得自增列的下一个值,那么可以使用show create table tablename查看。如下截图所示

 

澳门新萄京官方网站 9

 

 

4:自增列跳号

 

MySQL中,自增字段能够跳号:能够插入一条钦命自增列值的记录(即便插入的值超过自增列的最大值卡塔 尔(英语:State of Qatar),如下所示,当前自增列最大值为1,笔者插入贰个200的值,然后就能以200为底工继续自增,何况本人还足以世襲插入ID=100的记录,没有必要任何附加设置。

 

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> insert into test value(200, 'test');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 200 | test  |

 ----- ------- 

2 rows in set (0.00 sec)

 

mysql> insert into test(name) value('test2');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 200 | test  |

| 201 | test2 |

 ----- ------- 

3 rows in set (0.00 sec)

 

mysql> 

mysql> insert into test(id, name) value(100, 'ken');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

 ----- ------- 

4 rows in set (0.00 sec)

 

 

除此以外贰个是关于自增列逻辑跳号难题,在二个业务里面,使用境遇事情回滚,自增列就能够跳号,如下所示,id从201 跳到 203了。

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 202 | kkk   |

 ----- ------- 

5 rows in set (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 203 | kkk   |

 ----- ------- 

5 rows in set (0.00 sec)

 

当然,不论MySQL依然任何关系型数据库,都会境遇这种逻辑跳号的气象,比如ORACLE的行列也会存在此种逻辑跳号难点。为升高自增列的生作用率,都将生成自增值的操作设计为非事务性操作,表现为当事情回滚时,事务中变化的自增值不会被回滚。

 

5:truncate table操作会唤起自增列从头初步计数

 

mysql> truncate table test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test(name) value('kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> 

 

6:修改AUTO_INCREMENT的值来改正自增初始值。

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> alter table test auto_increment=100;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into test(name) value('k3');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | k3    |

 ----- ------- 

2 rows in set (0.00 sec)

 

自然MySQL还应该有生机勃勃对连锁知识点,这里未有做总括,主借使未有遭遇过有关意况。未来碰到了再做总计,别的一面,写技巧小说,很难眼观四处,那样太耗费时间也太累人了!

 

 

 

参谋资料:

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:新特性之自增主键的持久

关键词: