侯体宗的博客
  • 首页
  • Hyperf版
  • beego仿版
  • 人生(杂谈)
  • 技术
  • 关于我
  • 更多分类
    • 文件下载
    • 文字修仙
    • 群聊
    • 九宫格抽奖
    • 拼图
    • 消消乐
    • 相册

mysql中用INSERT IGNORE避免InnoDB上的自动增量漏洞(自增列上数据不连续问题)

数据库  /  管理员 发布于 4个月前   269

你是否在MySQL 5.1.22版或更新的版本上使用InnoDB表?

如果是这样,你可能在你的自动增量列中有间隙。

一个简单的INSERT IGNORE查询会为每个被忽略的插入创建间隙,但这是没有记录的行为。

这个文档错误已经提交了。


首先,我们将从一个简单的问题开始。为什么我们在自动增加的列上有间隙?

其次,我将向你展示一个模仿INSERT IGNORE行为而不丢失自动增量值的技巧。

让我们开始吧!


为什么我们会有空隙?


InnoDB检查表上的一个自动增加计数器,如果需要一个新的值,就增加该计数器并将新的值分配给该列。

在MySQL 5.1.22之前,InnoDB使用一种叫做 "传统 "的方法来访问该计数器的值。

这个方法使用了一个叫做AUTO-INC的特殊表锁,一直到查询或事务结束。

由于这个原因,两个查询不能同时拥有AUTO-INC锁,所以我们失去了并发性和性能。

对于像INSERT INTO table1 ... SELECT ... FROM table2这样的长时间运行的查询,问题就更严重了。


在5.1.22及以后的版本中,自动增加值的锁算法是可配置的,你可以使用innodb_autoinc_lock_mode选择不同的算法。

默认值是1,这是一种新的算法,叫做 "连续"。

由于这个新值,一个简单的插入查询,如单行或多行的INSERT/REPLACE,在AUTO-INC上使用一个轻量级的mutex而不是表锁。

我们已经恢复了并发性和性能,但代价很小。

像INSERT ... ON DUPLICATE KEY UPDATE这样的查询在auto_increment列上产生空隙。


为了避免这种小小的不便,我们可以回到传统的方法,

将innodb_autoinc_lock_mode改为0,但是会损失性能和并发性。


我怎样才能解决INSERT IGNORE的这个问题?

正如我之前告诉你的,INSERT IGNORE会产生间隙,这一点并没有记录在案,所以也许你多年来一直没有意识到这个问题。

你可以使用一个特殊的突变表来模仿INSERT IGNORE的行为,正如Baron的博客中所解释的那样,来摆脱间隙问题。


"互斥 "表是一个聪明的技巧,它允许连接表,同时在查询中保持它们彼此独立。

这一属性允许进行有趣的查询,否则是不可能的。


这就是我们的互斥表。我们只需要插入一个整数值:

create table mutex(
i int not null primary key
);
insert into mutex(i) values (1);


我们的InnoDB表带自动增量列将是这样的:

CREATE TABLE foo (
id int(11) NOT NULL AUTO_INCREMENT,
name int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uniqname (name)
) ENGINE=InnoDB;


使用LEFT OUTER JOIN插入一个值:

insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 1 row affected (0.00 sec)


多次插入相同的值。

正如你将看到的,INSERT被忽略,没有行被插入。

与INSERT IGNORE的行为相同:

insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)


现在检查自动递增计数器:

show create table foo\G
*************************** 1. row ***************************
Table: foo
Create Table: CREATE TABLE foo (
id int(11) NOT NULL AUTO_INCREMENT,
name int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uniqname (name)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1


完全没有缝隙,没有不连贯的数据。

这个技巧是由Michael Rikmas发现的:

https://www.percona.com/about-us/our-team/michael-rikmas/

当时我们正在为一个客户做一个咨询案例:

https://www.percona.com/mysql-consulting/overview/

所以,如果这能让你不用做ALTER TABLE来改变自动增量列的大小,那就送他一杯啤酒吧 


转:

https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/

  • 上一条:
    在go语言中数据结构之队列链表实现示例代码
    下一条:
    PHP异常的HTTP状态代码库推荐:HTTP Exceptions
  • 昵称:

    邮箱:

    0条评论 (评论内容有缓存机制,请悉知!)
    最新最热
    • 分类目录
    • 人生(杂谈)
    • 技术
    • linux
    • Java
    • php
    • 框架(架构)
    • 前端
    • ThinkPHP
    • 数据库
    • 微信(小程序)
    • Laravel
    • Redis
    • Docker
    • Go
    • swoole
    • Windows
    • Python
    • 苹果(mac/ios)
    • 相关文章
    • mysql5.7中实现分区表及分区where in查询示例及分区分表对比浅析(0个评论)
    • mysql中sql_mode的各模式浅析(0个评论)
    • 预处理之SQL参数化查询是如何防止SQL注入的浅析(0个评论)
    • 使用Navicat把mysql5.7版本的数据库导入至mysql8.1版本中流程步骤(0个评论)
    • 在mysql中设置表字段中COLLATE、CHARSET详解(0个评论)
    • 近期文章
    • 在go语言中使用GoPDF包把html生成PDF文件示例(0个评论)
    • 在go语言中创建和解析(读取)符号链接示例(0个评论)
    • ubuntu 22.04系统中报错:Python 3.6 is no longer supported by the Python core team...解决方式(0个评论)
    • Laravel 10.4版本发布(0个评论)
    • mysql5.7中实现分区表及分区where in查询示例及分区分表对比浅析(0个评论)
    • nginx + vue配置实现同域名下不同路径访问不同项目(0个评论)
    • 在laravel框架中的5个HTTP客户端技巧分享(0个评论)
    • 在go语言中使用FFmpeg库实现PCM音频文件编码为mp3格式文件流程步骤(0个评论)
    • gopacket免安装Pcap实现驱动层流量抓包流程步骤(0个评论)
    • 在laravel项目中实现密码强度验证功能推荐扩展包:password-strength(0个评论)
    • 近期评论
    • 博主 在

      2023年国务院办公厅春节放假通知:1月21日起休7天中评论 @ xiaoB 你只管努力,剩下的叫给天意;天若有情天亦老,..
    • xiaoB 在

      2023年国务院办公厅春节放假通知:1月21日起休7天中评论 会不会春节放假后又阳一次?..
    • BUG4 在

      你翻墙过吗?国内使用vpn翻墙可能会被网警抓,你需了解的事中评论 不是吧?..
    • 博主 在

      go语言+beego框架中获取get,post请求的所有参数中评论 @ t1  直接在router.go文件中配就ok..
    • Jade 在

      如何在MySQL查询中获得当月记录中评论 Dear zongscan.com team, We can skyroc..
    • 2017-06
    • 2017-08
    • 2017-09
    • 2017-10
    • 2017-11
    • 2018-01
    • 2018-05
    • 2018-10
    • 2018-11
    • 2020-02
    • 2020-03
    • 2020-04
    • 2020-05
    • 2020-06
    • 2020-07
    • 2020-08
    • 2020-09
    • 2021-02
    • 2021-04
    • 2021-07
    • 2021-08
    • 2021-11
    • 2021-12
    • 2022-02
    • 2022-03
    • 2022-05
    • 2022-06
    • 2022-07
    • 2022-08
    • 2022-09
    • 2022-10
    • 2022-11
    • 2022-12
    • 2023-01
    • 2023-03
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客