新浦京81707con > 注册购买 > 新特性之自增主键的持久化,自增主键的持久化

原标题:新特性之自增主键的持久化,自增主键的持久化

浏览次数:162 时间:2019-07-17

前言

自增主键未有长久化是个比较早的bug,那点从其在官方bug网址的id号也可观察()。由PeterZaitsev(现Percona COO)于2004年建议。历史悠久且臭名昭著。 

 

自增主键未有悠久化是个比较早的bug,那点从其在官方bug网址的id号也可看到()。由PeterZaitsev(现Percona 主任)于二零零一年建议。历史长久且臭名昭著。

先是,直观的再次出现下。

MySQL的自增列(AUTO_INCREMENT)和其余数据库的自增列比较,有比很多风味和不相同点(乃至差异存储引擎、不相同版本也许有一对不一的性子),令人认为有一点点稍微复杂。下边我们从局地测量试验开头,来认知、了然一下那上头的奇怪知识点:

第一,直观的复发下。

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)

 

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 |
 ---- 
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 |
 ---- 
rows in set (0.01 sec)

 

 

虽说id为3的笔录删除了,但再次插入null值时,并不曾选取被删去的3,而是分配了4。

就算id为3的记录删除了,但再也插入null值时,并未选择被删除的3,而是分配了4。

自增列悠久化难题

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

 

 

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 |
 ---- 
rows in set (0.00 sec)

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

假定多个表具备自增列,当前最大自增列值为9, 删除了自增列6、7、8、9的笔录,重启MySQL服务后,再往表里面插入数据,自增列的值为6依然10呢?  借使表的积累引擎为MyISAM呢,又会是哪些情况? 下边实验意况为MySQL 5.7.21

能够看看,新插入的null值分配的是3,遵照重启前的操作逻辑,此处应该分配5哟。

 

 

那便是自增主键未有长久化的bug。究其原因,在于自增主键的分红,是由InnoDB数据字典里面一个计数器来调控的,而该计数器只在内存中维护,并不社长久化到磁盘中。当数据库重启时,该计数器会通过上边这种措施开头化。

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)

 

SELECT MAX(ai_col) FROM table_name FOR UPDATE; 

 

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)

MySQL 8.0的解决思路

能够见到,新插入的null值分配的是3,根据重启前的操作逻辑,此处应该分配5啊。

 

将自增主键的计数器长久化到redo log中。每一趟计数器产生转移,都会将其写入到redo log中。要是数据库爆发重启,InnoDB会遵照redo log中的计数器消息来初阶化其内部存款和储蓄器值。为了尽可能减小对系统质量的震慑,计数器写入到redo log中,并不会即时刷新。具体可参照:

 

重启MySQL服务后,然后大家插入一条记下,字段ID会从如何值初始吧? 如下所示,假使表的蕴藏引擎为InnoDB,那么插入的数据的自增字段值为6.

因自增主键未有悠久化而出现难点的周边景象:

那就是自增主键未有长久化的bug。究其原因,在于自增主键的分配,是由InnoDB数据字典里面一个计数器来支配的,而该计数器只在内部存款和储蓄器中保险,并不组织首领久化到磁盘中。当数据库重启时,该计数器会通过上边这种方法初步化。

 

  1. 专业将自增主键作为工作主键,同期,业务上又必要主键不可能重复。

  2. 多少会被归档。在归档的历程中有一点都不小可能率会生出主键争持。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

 

进而,刚毅建议不要使用自增主键作为工作主键。刨除这七个情景,其实,自增主键未有悠久化的难点并不是非常大,远未有想象中的”臭名昭著“。

 

图片 1

最后,给出三个归档场景下的解决方案,

MySQL 8.0的消除思路

 

开创一个存款和储蓄进度,依据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 ;

将自增主键的计数器长久化到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 8.0的关周全据库新特征详解
  • Mysql 8 新特性 window functions 的作用
  • MySQL 8 新本性之Invisible Indexes
  • MySQL8新特征:持久化全局变量的退换章程
  • MySQL8新特色:降序索引详解

因自增主键未有长久化而产出难题的普及景观:

 

  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服务都不会潜移暗化其值变化。

 

 

故此,刚烈提议不要选用自增主键作为工作主键。刨除这八个场景,其实,自增主键没有悠久化的主题材料并非相当大,远未有设想中的”臭名昭著“。

 

 

图片 2

最终,给出贰个归档场景下的消除方案,

 

 

 

创立四个囤积进度,遵照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 ;

自增列细节性情

 

 

本文由新浦京81707con发布于注册购买,转载请注明出处:新特性之自增主键的持久化,自增主键的持久化

关键词: 新浦京81707con

上一篇:MySQL子查询操作实例详解,MySQL单表查询常见操作

下一篇:没有了