移动云海山数据库 HaishanDB：关于MySQL多表UPDATE顺序赋值的不确定性分析

一、引子

在 MySQL 单表 UPDATE 中，SET 子句的赋值是从左到右顺序执行的，后面的表达式能看到前面赋值的新值。这是确定行为，文档也明确说明：

UPDATE t SET col1 = col1 + 1, col2 = col1;
-- col2 会得到 col1 的新值（col1 + 1），而不是旧值

但在多表 UPDATE 中，这个看似确定的行为，实际上取决于优化器选择的执行计划。同一条 SQL 语句，在不同数据分布下，可能产生完全不同的结果——而且不报任何错误，不产生任何警告。这种不确定性，对于海山数据库兼容MySQL能力可能会造成结果存在差异的问题，因此就该问题，深入分析下MySQL中造成这种情况的根因。

二、现象复现

建表与数据

CREATE TABLE t1 (id INT, a INT, b INT, c INT, INDEX(id));
CREATE TABLE t2 (id INT, x INT, INDEX(id));
CREATE TABLE t3 (id INT, y INT, INDEX(id));

INSERT INTO t1 VALUES (1, 100, 10, 0);
INSERT INTO t1 SELECT 100+seq, seq, seq, seq FROM (...) LIMIT 500;  -- 填充 500 行

INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 SELECT 100+seq, seq FROM (...) LIMIT 500;            -- 填充 500 行

INSERT INTO t3 VALUES (1, 1);                                        -- 只有 1 行

同一条 SQL，两次执行

UPDATE t1, t2, t3
SET t1.a = t1.b + 1, t1.c = t1.a
WHERE t1.id = t2.id AND t2.id = t3.id;

Round 1（t1 ≈ 500 行，t2 ≈ 500 行）：

EXPLAIN:
  t3  index  1   ← main_table
  t1  ref    1   ← UPDATE
  t2  ref    1

结果: a=11, c=100   ← t1.c 看到的是 t1.a 的旧值

Round 2（删除 t1 填充行后，t1 只有 1 行，t2 仍 500 行）：

EXPLAIN:
  t1  ALL    1   ← main_table
  t3  ref    1
  t2  ref    1

结果: a=11, c=11    ← t1.c 看到的是 t1.a 的新值

同一条 SQL，两次执行，t1.c 的值从 100 变成了 11。

三、源码分析

3.1 多表 UPDATE 的两条执行路径

MySQL 多表 UPDATE 的核心实现在 sql/sql_update.cc 的 Query_result_update 类中。根据优化器选择的执行计划，存在两条截然不同的路径：

路径 A：on-the-fly（直接更新）

// sql_update.cc:2303-2312
if (table == table_to_update) {
    store_record(table, record[1]);
    fill_record_n_invoke_before_triggers(thd, ...,
        *fields_for_table[offset],
        *values_for_table[offset],
        table, TRG_EVENT_UPDATE, 0);
    // 直接写原表 record[0]
}

SET 表达式通过 fill_record 按从左到右顺序求值，每个 value->save_in_field(rfield) 直接写入原表的 record[0]。后续表达式引用同表列时从 record[0] 读取 → 看到新值。

路径 B：临时表（延迟更新）

// sql_update.cc:2423-2426
fill_record(thd, tmp_table,
    tmp_table->visible_field_ptr() + ...,
    *values_for_table[offset], NULL, NULL);

SET 表达式的值被写入临时表的 record[0]，而表达式中引用同表列的 Item_field 仍指向原表的 record[0]（未被修改）→ 看到旧值。

3.2 谁决定走哪条路径？

initialize_tables()（sql_update.cc:2022）中有一个关键判断：

main_table = join->best_ref[0]->table();  // 优化器选出的第一张表
table_to_update = 0;

for (table_ref = update_tables; ...) {
    if (table == main_table) {             // 只有 main_table 有机会
        if (safe_update_on_fly(thd, join->best_ref[0], table_ref, all_tables))
            table_to_update = table;        // → on-the-fly
        continue;
    }
    // 非 main_table → 强制创建临时表 → 延迟更新
}

核心规则：只有 best_ref[0]（EXPLAIN 中的第一张表）才有机会走 on-the-fly 路径。其他所有表强制走临时表路径。

3.3 safe_update_on_fly() 的判断逻辑

// sql_update.cc:1980-2010
static bool safe_update_on_fly(THD *thd, JOIN_TAB *join_tab,
                                TABLE_LIST *table_ref, TABLE_LIST *all_tables)
{
    if (unique_table(thd, table_ref, all_tables, 0))
        return 0;                          // 表自连接 → 不能 on-the-fly

    switch (join_tab->type()) {
    case JT_SYSTEM:
    case JT_CONST:
    case JT_EQ_REF:
        return TRUE;                       // 最多一行 → 无条件 on-the-fly

    case JT_REF:
    case JT_REF_OR_NULL:
        return !is_key_used(table,         // 更新列不是 ref 索引键 → on-the-fly
                            join_tab->ref().key, table->write_set);

    case JT_ALL:
        if (bitmap_is_overlapping(&table->tmp_set, table->write_set))
            return FALSE;                  // 读写列重叠 → 不能 on-the-fly
        if (join_tab->quick())
            return !join_tab->quick()->is_keys_used(table->write_set);
        if ((table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX)
            && table->s->primary_key < MAX_KEY)
            return !is_key_used(table,     // 更新列不是聚簇索引键 → on-the-fly
                                table->s->primary_key, table->write_set);
        return TRUE;
    }
    return FALSE;
}

3.4 tmp_set 的微妙行为

safe_update_on_fly() 中检查的 tmp_set 并不包含 SET 值表达式中引用的同表列。原因在 Query_result_update::prepare()（sql_update.cc:1753-1790）：

// 阶段1: 收集 SET 值表达式引用的列到 tmp_set
table->read_set = &table->tmp_set;
bitmap_clear_all(table->read_set);
setup_fields(thd, Ref_ptr_array(), *values, SELECT_ACL, ...);
// 此时 tmp_set = {t1.a, t1.b}

// 阶段2: 恢复 read_set，然后清空 tmp_set
table->read_set = &table->def_read_set;
bitmap_union(table->read_set, &table->tmp_set);
bitmap_clear_all(&table->tmp_set);   // ← tmp_set 被清空！

之后在 initialize_tables() 中，只重新收集了后续表条件中引用的 main_table 列：

// sql_update.cc:2069-2109
if (update_tables->next_local) {
    for (uint i = 1; i < join->tables; ++i) {
        JOIN_TAB *tab = join->best_ref[i];
        if (tab->condition())
            tab->condition()->walk(
                &Item::add_field_to_set_processor, ...);
    }
}

如果后续表的 condition() 为 NULL（ON 条件被优化为 ref access），tmp_set 保持为空 → bitmap_is_overlapping 返回 false → safe_update_on_fly() 对 JT_ALL 可能返回 TRUE。

四、完整测试用例

以下测试用例在 MySQL 5.7和8.0 上都验证通过：

-- ===== 建表 =====
DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1 (id INT, a INT, b INT, c INT, INDEX(id));
CREATE TABLE t2 (id INT, x INT, INDEX(id));
CREATE TABLE t3 (id INT, y INT, INDEX(id));

-- t1: 目标行 + 500 行填充
INSERT INTO t1 VALUES (1, 100, 10, 0);
INSERT INTO t1 SELECT 100+seq, seq, seq, seq
FROM (SELECT @row1:=@row1+1 AS seq
      FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
           (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
           (SELECT @row1:=0) r) t LIMIT 500;

-- t2: 目标行 + 500 行填充
INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 SELECT 100+seq, seq
FROM (SELECT @row2:=@row2+1 AS seq
      FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
           (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
           (SELECT @row2:=0) r) t LIMIT 500;

-- t3: 只有 1 行
INSERT INTO t3 VALUES (1, 1);

-- ===== Round 1: t1 ≈ 500 行, t2 ≈ 500 行 =====
ANALYZE TABLE t1, t2, t3;

EXPLAIN UPDATE t1, t2, t3
SET t1.a = t1.b + 1, t1.c = t1.a
WHERE t1.id = t2.id AND t2.id = t3.id;
-- 预期: t3(index) → t1(ref) → t2(ref)  →  t1 不是 main_table

UPDATE t1 SET a = 100, b = 10, c = 0 WHERE id = 1;
UPDATE t1, t2, t3
SET t1.a = t1.b + 1, t1.c = t1.a
WHERE t1.id = t2.id AND t2.id = t3.id;
SELECT 'Round1:' AS label, a, c FROM t1 WHERE id = 1;
-- 预期: a=11, c=100  (c = 旧 t1.a)

-- ===== 删除 t1 填充行 =====
DELETE FROM t1 WHERE id > 100;
ANALYZE TABLE t1;

-- ===== Round 2: t1 = 1 行, t2 ≈ 500 行 =====
EXPLAIN UPDATE t1, t2, t3
SET t1.a = t1.b + 1, t1.c = t1.a
WHERE t1.id = t2.id AND t2.id = t3.id;
-- 预期: t1(ALL) → t3(ref) → t2(ref)  →  t1 是 main_table

UPDATE t1 SET a = 100, b = 10, c = 0 WHERE id = 1;
UPDATE t1, t2, t3
SET t1.a = t1.b + 1, t1.c = t1.a
WHERE t1.id = t2.id AND t2.id = t3.id;
SELECT 'Round2:' AS label, a, c FROM t1 WHERE id = 1;
-- 预期: a=11, c=11  (c = 新 t1.a = t1.b + 1)

实测结果

Round1:  a=11,  c=100   ← c 看到旧值（t1 非 main_table，走临时表）
Round2:  a=11,  c=11    ← c 看到新值（t1 是 main_table，走 on-the-fly）

五、决策链路图

同一 SQL: UPDATE t1, t2, t3 SET t1.a = t1.b + 1, t1.c = t1.a WHERE ...
                │
                ▼
       优化器选择 JOIN 顺序
                │
    ┌───────────┴───────────┐
    ▼                       ▼
Round1: t3→t1→t2         Round2: t1→t3→t2
main_table = t3          main_table = t1
    │                       │
    ▼                       ▼
t1 ≠ main_table          t1 == main_table
    │                       │
    ▼                       ▼
强制临时表               safe_update_on_fly()
    │                       │
    ▼                       ▼
fill_record →            tmp_set 为空（prepare 后被清空）
写入临时表               bitmap 无重叠 → TRUE
    │                       │
    ▼                       ▼
t1.c = t1.a              on-the-fly
读原表 record[0]         fill_record → 写原表 record[0]
= 旧值 100                  │
    │                       ▼
    ▼                    t1.c = t1.a
c = 100                  读 record[0] 中新 a = 11
                            │
                            ▼
                         c = 11

六、生产环境中的触发条件

最危险的场景：应用上线初期表很小，UPDATE 走 on-the-fly 路径，SET 顺序赋值看到新值。随着业务增长，表行数变化触发统计信息更新，优化器翻转 JOIN 顺序，同一条 UPDATE 开始走临时表路径，SET 顺序赋值看到旧值——不报错、不告警、结果悄然改变。

七、规避建议

1. 不要在多表 UPDATE 的 SET 中依赖顺序赋值

-- 危险：t1.c 的值取决于执行计划
UPDATE t1, t2 SET t1.a = t1.b + 1, t1.c = t1.a WHERE t1.id = t2.id;

-- 安全：明确写出期望值
UPDATE t1, t2 SET t1.a = t1.b + 1, t1.c = t1.b + 1 WHERE t1.id = t2.id;

2. 拆分为多条 UPDATE

-- 安全：单表 UPDATE 的顺序赋值是确定的
UPDATE t1, t2 SET t1.a = t1.b + 1 WHERE t1.id = t2.id;
UPDATE t1 SET t1.c = t1.a WHERE ...;

3. 使用 EXPLAIN 验证执行计划

对于涉及多表 UPDATE 的关键业务 SQL，定期检查 EXPLAIN 输出的第一张表是否是被更新的表。如果不是，说明该表走临时表路径，SET 顺序赋值看到的是旧值。

4. 使用 STRAIGHT_JOIN 锁定 JOIN 顺序

-- 强制 t1 为 main_table → 保证 on-the-fly
UPDATE t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3
SET t1.a = t1.b + 1, t1.c = t1.a
WHERE t1.id = t2.id AND t2.id = t3.id;

八、总结

MySQL 多表 UPDATE 中 SET 顺序赋值的可见性，本质上不是 SQL 语义层面的问题，而是存储引擎层面的实现细节。它取决于 safe_update_on_fly() 的运行时判断，而该判断又依赖于优化器选择的执行计划。当执行计划因数据分布变化而翻转时，同一条 SQL 的语义会悄然改变。

核心结论：多表 UPDATE 中，永远不要依赖 SET 子句的顺序赋值行为。