1、问题发现
今天收到研发反馈，生产环境中一个UPDATE语句工作不正常。
问题大概是这样的
update t1 inner join t2 on t1.id = t2.id
set t1.col1 = t2.col1,t1.col2 = t1.col1
复制按照上面的语义，和MySQL的官方文档说明，上述语句执行后。t1.col1应该和t1.col2列值是相等的。但我们在生产环境出现了update执行后，t1.col1和t1.col2列值不相等的问题。
官网说明
2、问现复现
当时只在生产环境复现，但生产环境表中数据量大，字段多。不好跟踪，也不好反复执行，所以把数据导入测试环境，不断尝试，终于发现规律，下面可以用很简单的数据即可重现此问题
drop table if exists t1;
create table t1
(id int,
qty1 int,
qty2 int,
qty3 int,
qty4 int);
insert into t1
values(1,1,2,3,4);
drop table if exists t2;
create table t2
(id int,
qty1 int,
qty2 int);
insert into t2
values(1,10,20);
-- 执行下面的语句就会是错误的结果
update
/*+ join_prefix(b)*/
t1 as a inner join t2 as b on a.id = b.id
set a.qty1=b.qty1,a.qty2=b.qty2,a.qty3=a.qty1,a.qty4=a.qty2;
select * from t1;
复制按照SQL语义和MySQL官方说明 a.qty1与 a.qty3 值该相等，a.qty2与a.qty4值该相等
但update执行后 两者其实并不一致
结果如下图
3、排查过程
第一步：想到会不会是优化器改写了语句导致，所以在explain后 执行show warnings;发现优化器并没有改写我的语句。
陷入了卡壳，通过数据再仔细观察，实际上qty3与qty4字段是做了更新的。但是并没有按语句的书写顺序执行，感觉好像先执行了后两个字段，然后再执行前面字段的更新。
第二步：当时在测试环境数据量大，整个语句还有where 条件，发现执行计划走了索引合并，索引合并会引发一些其它问题，于是我就尝试指定强制索引，发现在一些索引下，更新结果是对的，一些索引下更新结果是错的，通过反复的查看执行计划，发现错的结果的执行计划和正确结果的执行计划的驱动表总是不一样。
第三步：在本地构造简单数据，通过强制指定驱动表。实现了即可复现正确结果也可复现错误结果
drop table if exists t1;
create table t1
(id int,
qty1 int,
qty2 int,
qty3 int,
qty4 int);
insert into t1
values(1,1,2,3,4);
drop table if exists t2;
create table t2
(id int,
qty1 int,
qty2 int);
insert into t2
values(1,10,20);
-- 执行下面的语句就会是正确的结果
update
/*+ join_prefix(a)*/
t1 as a inner join t2 as b on a.id = b.id
set a.qty1=b.qty1,a.qty2=b.qty2,a.qty3=a.qty1,a.qty4=a.qty2;
select * from t1;
复制上面这个update 执行的结果就是期望的结果。如下图。
两个update 执行计划的区别仅仅就只有驱动表不一样，下面两图，第一张是错误结果的执行计划，第二张是正确结果的执行计划
4、规范或建议
1、update 尽量单表执行2、多列同时更新的时候，不要让后面的列，依赖于前面的更新后的值，不要有书写列的顺序依赖
比如上述语句改这样
drop table if exists t1;
create table t1
(id int,
qty1 int,
qty2 int,
qty3 int,
qty4 int);
insert into t1
values(1,1,2,3,4);
drop table if exists t2;
create table t2
(id int,
qty1 int,
qty2 int);
insert into t2
values(1,10,20);
-- 执行下面的语句就会是正确的结果
update
t1 as a inner join t2 as b on a.id = b.id
set a.qty1=b.qty1,a.qty2=b.qty2,a.qty3=b.qty1,a.qty4=b.qty2;
select * from t1;
复制无论这个update语句的列顺序是如何执行的，都不会出错。
再次贴一次官方对此写法的说明
The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.
评论
0 点赞
