mysql 5.7 in 的优化引起的bug

起因

有个1700w的表需要初始化 , 然后我们需要分批取id范围是[1 , 1000) , [1000 , 2000)的值

问题

很简单的sql

update  test set    a.value=1 where id in ( 1 , 2 , 7 , 9.... 1000);
update  test set    a.value=1 where id in ( 1001 , 1002 , 1005 , ... 2000);

这里的id大概有100个左右 ,id是单调递增,基本连续

测试环境很正常,非常快 , 通过这个sql , 我们可以一秒update 1w以上的行

但是生产环境这个update特别特别慢,update 1000 行大概需要 50s以上

排查

定位经过很多尝试,
定位到是update这个sql特别慢,而且是但是测试环境非常快,生产环境非常慢

尝试explain

explain 
update  test as a set    a.value=1 where id in ( 1 , 2 , 7 , 9....);

生产环境下是这样:

Using where; Using temporary

但是测试环境是:

Using where

开始搜索,找到了类似的原因: https://bugs.mysql.com/bug.php?id=80424 对比了一下版本: 生产环境:5.7.9-log 测试环境:5.7.22-log

确定binlog的记录形式:

SELECT @@binlog_row_image

结果是

FULL

这个bug被5.7.15以上修复,所以测试环境没有问题,生产环境有问题

解决

因为生产版本的mysql几乎没有升级的可能,这个批量的刷数据如果10条/s估计要刷一个星期,所以我们尝试了很多写法避免这个优化,最后使用了这个写法避免生产版本的mysql的bug 不使用in 而是使用join 防止这个优化器的bug

DESC
UPDATE `test` a JOIN (
SELECT id FROM test t   WHERE  `id` IN (516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,533,532)
) t ON a.id = t.id
SET a.isvisible = -1;

优化后不用temp了

"id"	"select_type"	"table"	"partitions"	"type"	   "possible_keys"	"key"	"key_len"	"ref"	"rows"	"filtered"	"Extra"
"1"	    "UPDATE"	     "a"	\N	            "range"	    "PRIMARY"	    "PRIMARY"	"4"	     \N	    "104"	"100.00"	"Using where"
"1"	    "SIMPLE"	     "b"	\N	            "eq_ref"	"PRIMARY"	   "PRIMARY"	"4"	   "a.id"	"1"	    "100.00"	"Using index"

事后扒代码

通过https://bugs.mysql.com/bug.php?id=80424 提供的patch大概定位到原因

为什么会使用temp表?

第六个参数是判断是否需要使用temp的 ,也就是 !using_filesort && (used_key_is_modified || order)


    Modification_plan plan(thd, MT_UPDATE, &qep_tab,
                           used_index, limit,
                           (!using_filesort && (used_key_is_modified || order)),
                           using_filesort, used_key_is_modified, rows);

查看Modification_plan这个类的定义:

  Modification_plan(THD *thd_arg,
                    enum_mod_type mt, QEP_TAB *qep_tab,
                    uint key_arg, ha_rows limit_arg, bool need_tmp_table_arg,
                    bool need_sort_arg, bool used_key_is_modified_arg,
                    ha_rows rows);

在这个问题中是 used_key_is_modified = true, 所以会产生temp表