一个由INSERT INTO ON DUPLICATE KE

发表于

一、东窗事发

某日早，收到了许多接口报错的告警，虽涉及多个接口，但报错信息出奇一致。相关背景（模拟）交代如下：

1.错误信息：

1
2
sql复制代码Error attempting to get column 'id' from result set. Cause:    
com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '2.156220742E9' in column '1' is outside valid range for the datatype INTEGER.

2.代码信息：

a.建表sql

1
2
3
4
5
6
7
8
9
10
mysql复制代码CREATE TABLE `t_test`
(
    `id`               int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `biz_field`        varchar(255)     NOT NULL DEFAULT '' COMMENT '业务字段',
    `biz_field_unique` varchar(255)     NOT NULL DEFAULT '' COMMENT '业务字段（有唯一索引）',
    PRIMARY KEY (`id`),
    UNIQUE KEY `udx_t_test_biz_field_unique` (`biz_field_unique`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4 COMMENT ='测试表'
  • 主键 id 是 int(11) unsigned 类型
  • 字段 biz_field_unique 建了唯一索引

b.MyBatis的Mapper【报错的Mapper】

1
2
3
4
mysql复制代码<select id="getAllTest" resultType="TestPo">
    SELECT * FROM t_test
    WHERE biz_field = #{bizValue}
</select>
  • 查询字段使用了 select *
1
2
3
4
5
6
7
8
mysql复制代码<insert id="insertTest" parameterType="TestPo">
    -- 此处虽然有写id，但代码中 #{id} 未被赋值，所以一直都是用自增id
    INSERT INTO t_test(id, biz_field, biz_field_unique)
    VALUES (#{id}, #{bizField}, #{bizFieldUnique})
    ON DUPLICATE KEY
    UPDATE
        biz_field = #{bizField}
</insert>
  • 插入数据时使用了 INSERT INTO ON DUPLICATE KEY UPDATE

c.映射的Po

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
kotlin复制代码//kotlin
class TestPo(
    /**
     * 主键id
     */
    var id: Int = 0,

    /**
     * 业务字段
     */
    var bizField: String = "",

    /**
     * 业务字段（有唯一索引）
     */
    var bizFieldUnique: String = ""
)
  • 映射字段id使用了Int类型（对应java中的Integer）

二、简要分析

从错误信息的字面看，翻译过来大概就是：

1
2
sql复制代码原文：'2.156220742E9' in column '1' is outside valid range for the datatype INTEGER.
译文：第一列的值 '2.156220742E9' 超出 INTEGER 类型的取值范围。

从 Mapper 的 sql 可以看到报错的是一条查询的语句，通过查询表数据可知，’2.156220742E9’= 2156220742 数据就是表中id的值。因此不难理解，这是在MyBatis请求select语句时，拿到id字段的值超过了java中的Integer类型允许的最大值，无法被映射而报错。

  • MySql 中 id 值是 unsigned int，取值范围 [0,4294967295]
  • java 中 id 是 Integer，取值范围：[-2147483648,2147483648]
  • 2147483648(max java Integer) < 2156220742(db行记录值) < 4294967295(max mysql int unsigned)

虽然java与MySql中都称为int类型，但由于Mysql中是 unsigned 的，所以其上限值比java中的int高，因此自动生成超过java中int值上限的id值时，并不会报错

三、临时处理

在第二步我们明确了错误的直接原因，临时处理起来也比较简单

1.业务没有使用该字段

a.在Po中移除该字段

如果该字段没有在业务中使用，可以直接在Po中去掉该字段，这样即使Mapper的sql中有select出该字段，但是由于不会尝试将该字段映射字到Po，所以也不存在类型转换问题，当然也不会引起报错。

1
2
3
4
5
6
7
8
9
kotlin复制代码//kotlin
class TestPo(
    /**
     * 主键id，移除字段后，即使 select 出该字段，也不会报错
     */
    //var id: Int = 0,
    
    ...
)

b.在sql中移除该字段

同样的，该字段没有在业务中使用的话，也可以在直接在Mapper的sql中去掉该字段，这样mybatis处理的时候，会使用Po默认值填充，同样也不会报错。

1
2
3
4
5
6
mysql复制代码<select id="getAllTest" resultType="TestPo">
    --将select * 改成具体的字段，不包括有问题的字段id         
    SELECT biz_field,biz_field_unique 
    FROM t_test
    WHERE biz_field = #{bizValue}
</select>

如果在sql中使用了select * ，那就比较麻烦了，还要逐个字段名写上去

2.业务有使用该字段

a.修改po中该字段的类型

如果业务有使用该字段，那就需要修改Po中该字段的类型了，改为long类型即可。

1
2
3
4
5
6
7
8
9
10
kotlin复制代码//kotlin
class TestPo(
    /**
     * 主键id
     */
    var id: Long = 0
    
    ...

)

如果其它表有保存该字段，那也需要做相应的修改，并且如果该字段的存储类型值范围小于 int unsigned 的话，需要修改该字段的类型

四、寻找真凶

临时处理完成后暂时恢复了正常，但是真正原因仍有待进一步寻找

1.真有22亿的数据？当然不是

报错时我们看到该表的自增id高达21亿，但不代该表数据真有21亿（如果真的有，那你大概率会被运维请去喝茶），所以我们用select count 确认了一下，只有5万的数据。那么问题来了5万的数据，那自增id为何能增长到21亿呢？

1
2
mysql复制代码sql-> select count(1) from t_test;
res-> 50000

2.有人手动插了id很大的数据？也不是

我们都知道，自增id值是不会自动回填的，也就是我手动插了一个id=1亿的数据，那下一个自增id就是1亿01。所以我们手动插入一个id值为21亿的数据，那就有可能出现仅有5万数据的情况下自增id达到21亿的场景。（当然一般直接修改线上数据是不被允许的）

确认的方法：导出所有的id，然后看下区间分布即可

image.png
结果：id值分布均匀、没有高度集中且跨度较大，不符合推测，那跨度中间的id是怎样消失的呢？

思考：由于id是明显不连续的，所以推测肯定是有什么地方申请了id没有使用，并且mysql不会回收这部分id

问：不连续和跨度大是怎么看出来的？从图上看不是挺连续的吗？

答：图的横坐标是id的序号，而纵坐标是id值，在21亿里边均匀分布了5万的数据，其id肯定不是连续滴。（这点直接看真实id值可能会更直观）

3.那么，什么情况下会导致mysql自增id不连续呢？

a.有人删除了中间的id值

该表没有物理删除的操作，排除

b.代码指定了不连续的id进行插入

没有此逻辑，排除

c.带插入语句的事务回滚

没有此逻辑，排除

d.唯一建冲突

由表结构可知，存在 biz_field_unique 字段，是有唯一索引的，所以可能会产生冲突而消耗自增id值

回看代码，插入该表数据的就只有一个sql，并且明确了代码中不会指定id的值，也就保证了所有id均由mysql生成，所以必定是有某些操作影响了id值的生成！

4.INSERT INTO ON DUPLICATE KEY UPDATE ？ yes

经查阅 资料 可知，INSERT INTO ON DUPLICATE KEY UPDATE 在唯一索引冲突时，虽然执行的是更新操作，但仍然会使该表的自增id+1。通过逻辑排查发现该sql所在接口的调用频率很高，而且绝大部分都是更新数据，这样会导致频繁发生唯一索引冲突，消耗自增id，这也符合id相对连续且间隔较大的特点。

五、后续处理

1.为什么要用 INSERT INTO ON DUPLICATE KEY UPDATE ？

a.INSERT INTO ON DUPLICATE KEY UPDATE 的作用

顾名思义，使用该句式可以在发生唯一键冲突的时候，去更新某些字段，不冲突的时候，则插入一条记录。

需要注意的是，该语句包含了 判断是否存在唯一键冲突 及 插入或更新数据 两步操作的。

b.为什么不用 select ，然后再判断用 update 或 insert 呢？

既然 INSERT INTO ON DUPLICATE KEY UPDATE 在频繁冲突更新的情况下会浪费id，那我换种方式，先根据主键select一次，如果记录存在，我就执行update操作，如果记录不存在，我再执行insert操作？

I.朴素版

1
2
3
4
5
6
7
mysql复制代码//伪代码
var data = "select * from t_test where biz_field_unique = uniqueValue"
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue')"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue"
}

有经验的小伙伴一眼就能看出来，这是会存在并发问题的，如下：
image.png

request-2 的select语句在 request-1 的insert语句之前执行，导致重复插入报错

II.事务版

这时有些小伙伴就要问了，既然sql执行有并发问题，那我加个事务是否可以呢？

1
2
3
4
5
6
7
8
9
mysql复制代码//伪代码
transaction.open //打开事务
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
transaction.commit //提交事务

在 mysql RR级别下是不行滴，朴素版的问题依旧会存在，因为select的时候是没有互斥锁的，所以 request-2 的select语句仍有可能在 request-1 的insert语句之前执行。

III.互斥锁版

既然如此，那我手动给个互斥锁（for update）如何呢？

1
2
3
4
5
6
7
8
9
10
mysql复制代码//伪代码
transaction.open //打开事务
//select 语句加入 for update
var data = "select * from t_test where biz_field_unique = uniqueValue for update;" 
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
transaction.commit //提交事务

select然后insert或者update操作的并发问题_加入事务.jpg
从示意图可以看到，这样是可以的，但其执行过程相当于串行了，性能堪忧，并且其锁竞争发生在mysql中，给db造成了压力。

IV.分布式锁版

既然不想将压力放到db，其实也可以从服务侧解决这个问题：

1
2
3
4
5
6
7
8
9
mysql复制代码//伪代码
distributeLock.lock //获取分布式锁
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
distributeLock.unlock //释放分布式锁

通过分布式锁，可以将这串代码串行执行，但性能依旧堪忧

V.结合业务版

前面几个都是版本都是希望能得到通用解决方案的，那我们结合业务实际使用情况会不会好点呢？

首先明确一点，业务对该表时没有delete操作的，也就是记录一旦插入，往后所有操作都会是更新操作，根据表中数据，我们可以得到估算分支的执行频率，如下

1
2
3
4
5
6
7
8
9
10
mysql复制代码//伪代码
//select 执行次数 21亿 100%
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    //insert 执行次数 5w 0.0024%
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue');"
}else{
    //update 执行次数 21亿-5w 99.9976%
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}

如上计算可见，实际进入到 insert 的占比是非常少的，为了这部分的请求量而让整个流程串行执行是不妥的，因此，我们可以针对 insert 这里做一下优化，使用 INSERT INTO ON DUPLICATE KEY UPDATE 来代替insert，可以在id消耗和性能之间找到一个平衡。

1
2
3
4
5
6
7
8
9
10
mysql复制代码//伪代码
//select
var data = "select * from t_test where biz_field_unique = uniqueValue;"
if (data == null){
    //insert into on duplicate key update
    "insert into t_test(biz_field, biz_field_unique) values ('value','uniqueValue') on duplicate key update biz_field = value;"
}else{
    //update
    "update t_test set biz_field = value where  biz_field_unique = uniqueValue;"
}
  • 使用了 INSERT INTO ON DUPLICATE KEY UPDATE ，所以出现冲突的时候还是会有id值的浪费，但这个场景是少数的，其增长范围是可接受的

六、写在最后

遇到问题的时候，一般都需要了解好业务的使用场景，虽然很多问题都有通用的解决办法，但不一定是最优解，还是需要综合考虑业务场景。例如本文的问题就可以使用分布式锁，但是由于我们业务中不会物理删数据，因此我们可以舍弃一部分id，换来的是保证数据正确的情况下，仍保持较好的性能。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%