【MySQL】update语句的子查询优化

戒掉贪嗔痴(薛双奇)

于 2024-02-26 17:23:22 发布

阅读量561

点赞数 7

分类专栏：数据库运维-MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/136304623

版权

数据库运维-MySQL 专栏收录该内容

161 篇文章 8 订阅

订阅专栏

1.SQL查看

# Time: 2024-01-31T13:25:15.246348+08:00
# User@Host: claim[claim] @  [10.x.x.x]  Id: 820778
# Query_time: 122.872543  Lock_time: 0.000126 Rows_sent: 0  Rows_examined: 60321728
SET timestamp=1706678715;
update b_settlement_direct_cost t set t.isSettled='0' 
where t.Id in (select originalID 
from b_batch_settlement_direct_cost a 
where a.batchSettlementNo='J24000000299' and a.isSettled = '1');

2.分析

b_settlement_direct_cost 执行了全表扫描。
+----+--------------------+-------+------------+-------+--------------------+--------------------+---------+-------+--------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys      | key                | key_len | ref   | rows   | filtered | Extra       |
+----+--------------------+-------+------------+-------+--------------------+--------------------+---------+-------+--------+----------+-------------+
|  1 | UPDATE             | t     | NULL       | index | NULL               | PRIMARY            | 122     | NULL  | 188042 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | a     | NULL       | ref   | idx_lcharge_compno | idx_lcharge_compno | 93      | const |    225 |     1.00 | Using where |
+----+--------------------+-------+------------+-------+--------------------+--------------------+---------+-------+--------+----------+-------------+
2 rows in set (0.00 sec)

3.优化建议

--由 IN (半连接),修改为INNER JOIN;两个表都可以用到索引。

update b_settlement_direct_cost t ,b_batch_settlement_direct_cost a
set t.isSettled='0'  
where t.id =originalID 
and  a.batchSettlementNo='J24000000299' and a.isSettled = '1';
--优化后执行计划。
+----+-------------+-------+------------+--------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys      | key                | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ref    | idx_lcharge_compno | idx_lcharge_compno | 93      | const                |  225 |    10.00 | Using where |
|  1 | UPDATE      | t     | NULL       | eq_ref | PRIMARY            | PRIMARY            | 122     | hxappdb.a.originalID |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
2 rows in set (0.00 sec)

4.总结

对于UPDATE子查询优化，需要修改为 INNER JOIN 后修改。两个表都可以用索引。