网易视频云：不同执行计划下 Mysql 多表更新结果不一致的现象

[shipinyun2016]

网易视频云是网易倾力打造的一款基于云计算的分布式多媒体处理集群和专业音视频技术，为客户提供稳定流畅、低时延、高并发的视频直播、录制、存储、转码及点播等音视频的 PaaS 服务。在线教育、远程医疗、娱乐秀场、在线金融等各行业及企业用户只需经过简单的开发即可打造在线音视频平台。现在，网易视频云与大家分享一下不同执行计划下 Mysql 多表更新结果不一致的现象。

前阵子时间偶然的情况下公司里的前辈们在代码注释中留下一条有趣的 SQL 引发了我的兴趣， 在机缘巧合下又发现是否建立索引，建立什么样的索引会导致更新语句的结果不一致。

接下来重现这个问题：

首先 在 mysql 中来建立两张表 t1, t2 两张表都有两个 int 字段 a 和 b ，为两张表各自插入一条记录(10, 20)

然后有以下这样的更新语句：

  update t1, t2 set t1.b = 300, t2.b =  t1.b where t2.a = t1.a and t1.a = 10;

一般认为这条更新语句能够将 t1 表和 t2 表中的记录同时更改为(10, 300), 而事实结果是不是这样的呢？

我们选用了 mysql-5.1.49 版本进行，并按照以下情形建表进行测试：

1. create table t1 (a int, b int);

   create table t2 (a int, b int);

2. create table t1 (a int, b int, primary key(a));

   create table t2 (a int, b int, primary key(a));

3. create table t1 (a int, b int);

   create index idx on t1(a);

   create table t2 (a int, b int);

   create index idx on t2(a);

结果 1 的结果： t2 表的数据没变，只有 t1 被更新成了(10, 300)。

这是怎么回事呢？

首先来看查询计划， 由于 5.1 版本不支持 update 语句的 explain ，因此我们根据 where 后的条件，改写成 select 语句：

 explain select * from t1, t2 where t2.a = t1.a and t1.a = 10;

更新记录存放在上层的 table->record 中，其中 record[1] 为更新前项， record[0] 为更新后项， 于是乎问题就在于更新 t2 表时， record[0] 是怎么赋值的

在存储引擎的接口层打上断点，可以初步分析以上三种情形下的执行步骤：

来分析 case 1 的执行逻辑：

1. 做 t1 的全表扫描

2. 做 t2 的全表扫描

3. 将满足条件的 t1 的 rowId 传给上层

4. 将满足条件的 t2 的 rowId 传给上层

5. 全表扫描内表 t2 ，没有符合条件的记录

6. 全表扫描外表 t1 ， 没有记录了

7. 根据 t1 的 rowId 进行 postionScan

8. 更新 t1 将其更新为(10. 300)

9. 根据 t2 的 rowId 进行 positionScan

10. 更新 t2 ，将其更新为（ 10 ， 20 ）

来分析 case 2 的执行逻辑：

1. 做 t1 的索引扫描，扫描获取 a,b 两列

2. 做 t2 的索引扫描，扫描 a,b 两列

3. 更新 t1 表 将其更新为(10,300)

4. 根据之前 join 返回的 ref 值（ rowid ），对 t2 表做 position scan

5. 更新 t2 表的记录，将其更新为(10,300)

来分析 case 3 的执行逻辑：

join 选择 t1 为外表 t2 为内表做 nestloop 查询

1. 做 t1 的索引扫描，扫描取 a,b 两列的值

2. 做 t2 的索引扫描，扫描 a ， b 两列的值

3. 更新 t1 表的记录，将其更新为（ 10 ， 300 ）

4. 将满足条件的 t2 的 rowId 传给上层的 ref

5. 继续走索引扫描查询内表 t2 ，看有没有符合条件的记录，发现没有

6. 再走索引扫描查询外表 t1 ，也没有记录

7. 根据之前返回的 ref 值(符合 join 条件的 rowId)，对 t2 表做 position scan

8. 跟新 t2 表的记录，将其更新为（ 10 ， 300 ）

情况 1 的执行计划较情况 2 ， 3 有较大的不同， 而情况 2 和情况 3 相比，两张表各省略了一次索引扫描（因为主键索引是唯一索引，不需要额外的去查看是否达到查询边界）。

接下来看 mysql 上层是如何处理这样的查询语句的。

sql 语句经过 yacc 解析层后， mysql 会将更新后项加入一个 values_for_table 数组， 在本例中，数组的第一个元素，即 t1 表的更新后项，为一个值为 1 的 Item_int 对象, 而第二个元素， t2 的更新后项，为一个指向 t1 表 record[0] 第二个属性的指针。

在 multi_update::initialize_tables 方法中判断出我的主表 t1 是否能在 join 过程中直接更新掉。而此处就是导致结果不同的关键。

相关代码如下：

if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables))

{

      table->mark_columns_needed_for_update();
      
      table_to_update= table;               // Update table on the fly
      
      continue;
      
}

safe_update_on_fly() 方法是判断是否这张表中的一行需要读两次进行更新，如果不需要的话，直接可以在 join 里就更新掉。

根据代码注释的说明来看，可以直接在 join 里更新的条件如下：

 1. 没有列在 set 中又需要读，又需要写
 
 2. 做 tableScan ，并且数据是单文件（ MYISAM ）或者我们不更新聚簇索引键
 
 3. 做一个 rangescan ， 并且不更新查找键或者主键
 
 4. table 不是自相交

针对我们遇到的情况来跟踪代码：

case 1 因为 join 类型为 JT_ALL 所以判断在 set 语句中 属性 b 又需要读，又需要写，因此，不能在 join 中直接更新主表

case 2 因为 join 类型为 JT_CONST 所以认为一定可以在 join 中直接更新主表

case 3 因为 join 类型为 JT_REF 所以判断属性键 a 是否被更新，因为没有被更新，因此可以在 join 中直接更新主表 （ join 类型就是我们查询计划中的 type ）

对于不能直接在 join 中更新的表， mysl 上层会为其建立一张对应的临时表来存储更新后项，因此：

case1 有两张临时表，而 case2 和 case3 只有一张 t2 表对应的临时表。

从大体的路径上来说 case 1 是一类， case 2 、 3 是一类，所以以下就按照 case 1 和 case 3 进行讨论

case 2 ：

case2 在实际更新时，即第 3 步之前做了如下的事情：

  store_record(table,record[1]);          // 将 getNext 获取到的记录 record[0] 拷贝到更新前项 record[1]中
  if (fill_record_n_invoke_before_triggers(thd, *fields_for_table[offset],          // 将更新后项填充到 record[0]中
  
                                           *values_for_table[offset], 0,
                                           table->triggers,
                                           TRG_EVENT_UPDATE))

可见在 t2 填充对应的 tmp_table[1]之前， t1 表已经完成了对 table->record[0] 的设置。

之后 t2 表根据新的 t1 表 table->record[0]的值创建临时表记录插入到 tmp_table[1]中。这条记录已经是 300 了 行至第 4 步做 rnd_pos 时填充了 record[0]（此时为旧项）然后通过 store_record(table, record[1]) 将 record[0]中的内容拷贝到 record[1] 中， 而真正的更新后项 record[0]是之后从临时表中拷贝过来的。 开被 通过 muti_update 类中的一个 copy_field 作为一个拷贝的桥梁，桥梁两端分别指向临时表 tmp_table 的字段和 table->record[0] 字段。 然后对 tmp_table 做一次全表扫描（ tmp_table 是一张 heap 引擎的数据表，临时存储一些数据）将取出来的值赋为 tmp_table->record[0].

case 3 与 case 2 非常类似，不再做详细讨论

case1 ：

case1 在第 4 步之后，会进入上层的 multi_update::send_data() 方法，此方法中首先将 values_for_table[0]中的后项 300 ，插入到临时表 tmp_table[0]中, 将 values_for_table[1]中记录的 t1 表 table->record[0]的内容 20 插入到临时表 tmp_table[1]中。至此，两张表的更新后项已经确定，之后无论 t1 表的 table->record[0]如何改变，都不会再影响到 t2 表的更新后项。行至第 8 步之前，开始进行实际更新时， t1 将对应的临时表中记录和 record[0]交换。 第 10 步之前只是将 t2 对应的临时表中记录和 t2 的 table->record[0]交换，得到更新后项值为 20 。

结论： 个人认为， MYSQL 在处理多表更新时在更新前去获取真正的更新后项才是一个靠谱的时机， 而不是在目前类似 case1 那样早早的存一个过程值。

现在我们知道了结果不同的原因，那可以构造更多的用例：

更新语句为 update t1, t2 set t1.b = 300, t2.b = t1.b where t2.a = t1.a and t1.a = 10 and t1.b = 20;

1. create table t1 (a int, b int);

   create index idx on t1(a, b);

   create table t2 (a int, b int);

   create index idx on t2(a, b);

1. create table t1 (a int, b int, );

   create unique index idx on t1(a, b);

   create table t2 (a int, b int);

   create unique index idx on t2(a, b);

case 4 和 case 3 的区别在于，表上的索引包含了属性 b, 而且 where 条件里也多了 b ，

case 4 和 case 5 的区别在于，一个是普通二级索引，一个是主键索引

基于以上理论：

case 4 在 safe_update_on_fly() 判断中因为是 JT_REF, 但是，属性键包含 b ， b 是在 set 语句中被更新的，不能在 join 中直接更新主表。

因此 case4 的结果和 case1 一样，只有 t1 表被更新成了(10, 300)。

case 5 case 4 在 safe_update_on_fly() 判断中因为是 JT_EQ_REF ,和 JT_CONST 一样处理，可以在 join 中直接更新主表，因此 case5 的结果和 case2 ， case3 一样，两张表都被更新成了(10,300)

总结： 这是一个不同执行计划导致更新结果不同的例子，是否能在 join 语句中直接更新主表记录成了一道分水岭。经过简单的验证这样的不同结果在 mysql-5.5.31 , 5.6.12 中一样存在。这样的不一致的结果从数据库基础理论上看过来不太可接受， 用户的一条更新语句的结果不能取决于表上加着什么索引或者走什么样的执行计划。至于这是不是一个 mysql server 层必须要修正的 BUG ，就仁者见仁了。

更多技术分享，请关注网易视频云官方网站（ http://vcloud.163.com/）

或者网易视频云官方微信（ vcloud163 ）进行交流与咨询