98.mysql DELETE语句优化

1.场景 

保单一条DELETE语句执行1.8小时还没有结束

保单
delete
from 
	underwriting.prpcautoprice
where
	UUID in(
	select
		p.UUID
	from
		underwriting.tmp_autoprice_20231204 t,
		underwriting.prpcmain p,
		underwriting.prpchead p3
	where
		p.CONTRACTNO = t.POLICYNO
		and p.UUID = p3.UUID
		and p3.ISSUEDATE >= '2021-12-27');

6467/60/60=1.8小时,还没有执行结束。

2.表数据分析

root@localhost [(none)]>select count(1) from underwriting.tmp_autoprice_20231204 t;
+----------+
| count(1) |
+----------+
|    96564 |
+----------+
1 row in set (0.04 sec)

root@localhost [(none)]>select count(1) from underwriting.prpcmain p;
select count(1) from underwriting.prpchead p3;
select count(1) from underwriting.prpcautoprice;
+----------+
| count(1) |
+----------+
|  8208712 |
+----------+
1 row in set (11.19 sec)

root@localhost [(none)]>select count(1) from underwriting.prpchead p3;
+----------+
| count(1) |
+----------+
|  6289070 |
+----------+
1 row in set (8.15 sec)

root@localhost [(none)]>select count(1) from underwriting.prpcautoprice;
+----------+
| count(1) |
+----------+
|  5113374 |
+----------+
1 row in set (1.72 sec)
执行计划分析

underwriting.prpcautoprice有几百万数据，但是删除时没有用到索引。

3.优化方法

表underwriting.prpcautoprice 主键是UUID,删除时也是根据UUID删除,但是现在没有用到索引，所以执行了快两个小时还没有结束，并且不清楚什么时候会结束。优化思路就是让删除能够使用所用。
(1)创建临时表 
root@localhost [underwriting]>create table t1(`UUID` varchar(40));
Query OK, 0 rows affected (0.08 sec)
(2)删除要插入数据的UUID
root@localhost [underwriting]>insert into t1 
    -> select p.UUID
    -> from
    -> underwriting.tmp_autoprice_20231204 t,
    -> underwriting.prpcmain p,
    -> underwriting.prpchead p3
    -> where
    -> p.CONTRACTNO = t.POLICYNO
    -> and p.UUID = p3.UUID
    -> and p3.ISSUEDATE >= '2021-12-27';
Query OK, 65714 rows affected (2.46 sec)
Records: 65714  Duplicates: 0  Warnings: 0
(2)删除目标数据
--删除时关联结果集:T1,此时可以用到主库。
root@localhost [underwriting]>DELETE   T from prpcautoprice AS T INNER JOIN T1 AS T1  WHERE T.UUID=T1.UUID;
Query OK, 6764 rows affected (0.68 sec)   --优化后0.68s删除结束。显然用到了索引。

root@localhost [underwriting]>commit;
Query OK, 0 rows affected (0.01 sec)

(4)结果集中UUID在要删除的目标表中为0代表全部删除结束。
root@localhost [underwriting]>select count(1) from underwriting.prpcautoprice u 
    -> ,t1 where u.uuid=t1.uuid;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.45 sec)

(5)删除临时表
Drop table t1;

4.总结

对于delete的优化，特别是针对复杂关联的删除，应该尽可能的使用索引。如果执行计划中没有用到索引，则会很慢，如上述SQL 1.8小时尚未运行结束，而使用索引，则只需要0.68s.

还有一种方法就是SQL改写，将原来的DELETE FROM XXX UUID IN; 修改为:

delete u
from
underwriting.prpcautoprice u
inner join
(
select
p.UUID
from
underwriting.tmp_autoprice_20231204 t,
underwriting.prpcmain p,
underwriting.prpchead p3
where
p.CONTRACTNO = t.POLICYNO
and p.UUID = p3.UUID
and p3.ISSUEDATE >= '2021-12-27') u2
on u.uuid=u2.uuid;

执行计划如下:

由此可见，改写后的SQL可以DELETE的表中用到了索引。所以性能会更改好。