通过shell脚本模拟MySQL自增列的不一致问题

MySQL的自增列问题其实很有意思,在重启数据库之后,会按照max(id)+1的方式来计算,这样一个看起来有些别扭的实现方式在早期版本就饱受诟病,在MySQL 5.7都没有解决掉,终于在8.0松口了,计划在这个版本中修复。

而重启会带来自增列一类的潜在问题,而如果不重启其实也有可能会有自增列的不一致问题。和两个参数table_definition_cache和table_open_cache还是密切相关的。

主要的原因是什么呢,引用阿里数据库内核团队的解释(https://www.kancloud.cn/taobaomysql/monthly/67171):一方面InnoDB表自增值是存储在表对象中的,表对象又是放在缓存中的,如果表太多而不能全部放在缓存中的话,老的表就会被置换出来,这种被置换出来的表下次再使用的时候,就要重新打开一遍,对自增列来说,这个过程就和实例重启类似,需要 select max(id) + 1 算一下自增值。

表对象缓存大小由 table_definition_cache 系统变量控制,最小值为400,表缓存相关的另一个系统变量是table_open_cache,这个控制的是所有线程打开表的缓存大小,这个缓存放在server层。

我在查看了5.6.14的环境之后,发现这个值已经提升到了500,而在MySQL 5.7中,提升到了1400,可见这方面了下了大功夫。

MySQL 5.6.14的参数值情况

# mysqladmin var|grep table_open_cache | table_open_cache | 256 | table_open_cache_instances | 1 # mysqladmin var|grep table_definition_cache | table_definition_cache | 500

MySQL 5.7中的参数值情况:

mysql> show variables like 'table_definition_cache'; | Variable_name | Value | | table_definition_cache | 1400 |

mysql> show variables like 'table_open_cache'; | Variable_name | Value | | table_open_cache | 2000 |

阿里的同学给出了testcase的伪代码,我就来实现以下,给出shell版本的测试脚本。

首先我们可以模拟一下这个测试的基线,把两个变量都修改为400.

SET GLOBAL table_definition_cache = 400; SET GLOBAL table_open_cache = 400;

然后使用如下的shell脚本,仔细来看,脚本逻辑很简单了。

生成500个表,然后插入一条数据,修改自增列值,然后查询表里的数据,使得数据能够刷出,稍作等待,查看show create table的结果。

for i in {1..500} do mysql test_new <<EOF CREATE TABLE t$i(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY(id)) ENGINE=InnoDB; INSERT INTO t$i(name) VALUES("InnoDB"); ALTER TABLE t$i AUTO_INCREMENT = 100; EOF done for i in {1..500} do mysql test_new <<EOF SELECT * FROM t$i; EOF done sleep 10; for i in {1..3} do mysql test_new <<EOF SHOW CREATE TABLE t$i; EOF done

测试完成之后,来查看自增列的值情况.

在5.6.14中效果很明显。

Table Create Table t1 CREATE TABLE `t1` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar(30) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

而在5.7中,发现这类问题竟然还复现不了了,至于是代码层级做了修复还是和其它参数有关,就需要深入一下了。

Table Create Table t1 CREATE TABLE `t1` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar(30) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-07-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PHP在线

Mysql存储引擎中InnoDB与Myisam的区别

1. 事务处理innodb 支持事务功能,myisam 不支持。 Myisam 的执行速度更快,性能更好。 2. select ,update ,insert...

32650
来自专栏PHP在线

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更...

35380
来自专栏IT开发技术与工作效率

MySQL数据库优化总结《高性能MySQL》指导其他指导

35240
来自专栏lgp20151222

mysql explain 的extra中using index ,using where,using index condition,using index & using where理解

using where:查找使用了索引,不需要回表去查询所需的数据,查询结果是索引的一部分

21610
来自专栏FreeBuf

如何对已损坏的SQLite数据库取证分析?

SQLite是当今最流行的数据库之一,许多移动应用台式计算机以及便携式笔记本上都用它来存储数据(例如桌面工具、浏览器以及社交媒体软件等),因此SQLite在电子...

47590
来自专栏Java后端技术栈

MySQL存储引擎MyISAM与InnoDB区别总结整理

在MySQL 5.1之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB。

41830
来自专栏ytkah

dedecms清空所有文章怎么操作?sql语句如何写?

  小C新建了一个站,确切的说是复制,出于seo考虑,决定清空所有文章,那么dedecms清空所有文章怎么操作?sql语句如何写呢?特别提醒:修改之前一定要先做...

31190
来自专栏杨建荣的学习笔记

MySQL和Oracle的添加字段的处理差别 (r10笔记第73天)

昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处...

38360
来自专栏杨建荣的学习笔记

关于primary key和unique index的奇怪问题 (58天)

今天一个dba交给我一个问题,让我帮忙查一下。说有个脚本运行的时候有错,让我看看是什么原因。 脚本的思路如下: 先drop PK,FK之类的constraint...

312120
来自专栏L宝宝聊IT

Mysql性能优化——索引

对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。

9520

扫码关注云+社区

领取腾讯云代金券