mysql multi update_[转]两表join的multi update语句在MySQL中的执行流程分析

出自：http://hedengcheng.com/?p=209

两表join的multi update语句，执行结果与预计不一致的分析过程

— multi update结论

在实际应用中，不要轻易使用multi update更新，根据join的不同顺序，

更新的结果也会发生变化，multi update不是一个有稳定输出的语句，

并且输出结果很难理解，最好不用！

整个测试的准备与multi update的处理流程分析，请见下

— T1表

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

`aid` int(10) DEFAULT NULL COMMENT ‘编号’,

`name` varchar(32) DEFAULT NULL COMMENT ‘名称’,

`total` int(10) DEFAULT NULL COMMENT ‘剩余数量’,

`unit` varchar(32) DEFAULT NULL COMMENT ‘数量单位’

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` VALUES (1001,’商品a’,100,’个’),(1002,’商品b’,100,’个’),

(1003,’商品c’,100,’个’);

mysql> select * from t1;

+——+——-+——-+——+

| aid | name | total | unit |

+——+——-+——-+——+

| 1001 | 商品a | 100 | 个 |

| 1002 | 商品b | 100 | 个 |

| 1003 | 商品c | 100 | 个 |

+——+——-+——-+——+

3 rows in set (0.00 sec)

— T2表

DROP TABLE IF EXISTS `t2`;

CREATE TABLE `t2` (

`bid` int(10) DEFAULT NULL COMMENT ‘编号’,

`aid` varchar(32) DEFAULT NULL COMMENT ‘商品编号’,

`used` int(10) DEFAULT NULL COMMENT ‘使用数量’,

`status` varchar(32) DEFAULT NULL COMMENT ‘状态’

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t2` VALUES (1,’1001′,1,’WAIT’),(1,’1002′,3,’WAIT’),

(1,’1001′,9,’WAIT’),(2,’1003′,1,’DONE’);

mysql> select * from t2;

+——+——+——+——–+

| bid | aid | used | status |

+——+——+——+——–+

| 1 | 1001 | 1 | WAIT |

| 1 | 1002 | 3 | WAIT |

| 1 | 1001 | 9 | WAIT |

| 2 | 1003 | 1 | DONE |

+——+——+——+——–+

4 rows in set (0.00 sec)

— multi update语句

UPDATE t1,t2

SET t1.total = t1.total- t2.used, t2.status =’DONE’

WHERE t1.aid = t2.aid

AND t2.bid = ‘1’

AND t2.status = ‘WAIT’;

— multi update语句执行结果

Query OK, 5 rows affected (19 min 22.43 sec)

Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from t1;

+——+——-+——-+——+

| aid | name | total | unit |

+——+——-+——-+——+

| 1001 | 商品a | 99 | 个 |

| 1002 | 商品b | 97 | 个 |

| 1003 | 商品c | 100 | 个 |

+——+——-+——-+——+

3 rows in set (2.38 sec)

mysql> select * from t2;

+——+——+——+——–+

| bid | aid | used | status |

+——+——+——+——–+

| 1 | 1001 | 1 | DONE |

| 1 | 1002 | 3 | DONE |

| 1 | 1001 | 9 | DONE |

| 2 | 1003 | 1 | DONE |

+——+——+——+——–+

4 rows in set (0.00 sec)

— 执行结果简单分析

1. 根据update语句，此时t1表的aid=1001的项，total为什么=99，

而不是90，或者是91？

2. update执行结果，一共影响了5行记录，是哪5行？

3. update执行的顺序是怎么样的？

— multi update过程跟踪

表名                                 更新前项                    更新后项

update 1:  t1                 (1001, 商品a, 100)         (1001, 商品a, 99)

update 2:  t1                 (1002, 商品b, 100)         (1002, 商品b, 97)

update 3:  t2                 (1, 1001, 1, WAIT)         (1, 1001, 1, DONE)

update 4:  t2                 (1, 1001, 9, WAIT)         (1, 1001, 9, DONE)

update 5:  t2                 (1, 1002, 3, WAIT)         (1, 1002, 3, DONE)

— multi update流程分析(根据源码跟踪调试总结而来)

1. 判断出是multi update，为每一个可能更新的表，创建内存临时表

(http://fendou.org/2009/01/20/mysql-heap/)

此处需要为t1，t2表创建内存临时表(临时表使用的是heap引擎)

2. 对t1，t2表做join查询，t1作为join的外表，t2作为join的内表

3. 对于join到的t1，t2项，根据update条件构造更新后项，分别插入

对应的t1，t2内存临时表(调用ha_heap.cc::ha_heap::write_row方法)

3.1 对于t1表的第1条记录与t2表的第1条记录，join成功，因此将对应

的t1更新后项(rowid1_1, 99)(rowid1_1代表t1表的第1条记录，Unique)

与t2更新后项(rowid2_1, DONE)插入内存表

4. 插入内存表时，会进行unique判断，每条记录只能被插入内存表一次。

因此，对于t1的第1条记录与t2的第3条记录，join成功。但是由于t1

表的内存临时表中已经存在(rowid1_1, 99)，因此插入(rowid1_1, 91)失败，

Unique冲突。t2表的更新后项(rowid1_3, DONE)插入临时表能够成功。

5. 整个join过程完成，t1临时表中的记录为(rowid1_1, 99),(rowid1_2, 97)；

t2临时表中的记录为(rowid2_1, DONE),(rowid2_3, DONE),(rowid2_2, DONE)

6. sql_update.cc::multi_update::do_updates函数，遍历t1，t2的临时表，构造

完整的更新后项，分别对t1，t2表记录进行更新。最后的更新结果，就是t1表

更新了2行，t2表更新了3行，使用的是临时表中的更新后项。与最终的update

执行结果，完全一致。

— multi update结论

在实际应用中，不要轻易使用multi update更新，根据join的不同顺序，更新

的结果也会发生变化，multi update不是一个有稳定输出的语句，并且输出结果

很难理解，最好不用！