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 后修改。两个表都可以用索引。