参考资料
explain的官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
status的官方文档：https://dev.mysql.com/doc/refman/8.0/en/show-status.html
Mysql explain使用概述 - 大脸猫 - OSCHINA - 中文开源技术交流社区
MySQL中的全表扫描和索引树扫描 - Garrett_Wale - 博客园
-- 速度极慢的sql
DELETE FROM xxx_quote_parameter WHERE quote_num IN (SELECT quote_id AS quote_num FROM xxx_quote WHERE project_num IN ('2307310020','2212010015','2210090011','2210090009','2210080028'));
通过使用explain关键词，得出这个删除语句用的都是全表扫描（type是ALL）的，DEPENDENT SUBQUERY 是quote这张表，而这张表的数据比较大，又是全表扫描，所以会非常的慢。
可以通过 show status命令得到
可以通过随便找一张表来验证一下，SELECT COUNT(*) AS quote FROM xxx_quote_parameter，这里count出来的总数量和Handler_read_rnd_next一致。
-- quote部分
SELECT COUNT(*) AS parameter FROM xxx_quote_parameter UNION SELECT COUNT(*) AS quote_num FROM xxx_quote;
SQL -- 问题sql会经过优化器优化成类似下面的sql DELETE FROM xxx_quote_parameter WHERE EXISTS (SELECT quote_id FROM xxx_quote WHERE xxx_quote_parameter.quote_num = xxx_quote.quote_id AND quote_id IN ("Q221008007","Q221008012","Q221009003","Q221009012","Q221009013","Q221009014","Q2211020014","Q221201044","Q230113011","Q230209008","Q230601022","Q230612015","Q230729035")); |
理论上执行的数量：12908 * 1207 + 1207 = 15,581,163，实际的数量15543644，相差37,519条，和上面的博客中的情况类似，会有一些差距（具体原因我也不清楚）。
SQL SELECT * FROM xxx_quote_parameter WHERE quote_num IN (SELECT quote_id FROM xxx_quote WHERE project_num IN ('2307310020','2212010015','2210090011','2210090009','2210080028')); |
虽然说这里的sql和上面的sql差别就是搜索语句和删除语句的区别，其他内容都是一样，但是优化器对搜索就有优化，对删除就没有优化。这就是我犯的错误，我之前认为delete和select执行的过程是一样的。
SQL -- 使用临时表的sql DELETE FROM xxx_quote_parameter WHERE quote_num IN ( SELECT quote_num FROM ( SELECT quote_id AS quote_num FROM xxx_quote WHERE project_num IN ( '2307310020', '2212010015', '2210090011', '2210090009', '2210080028' )) AS tmp ); |
与上面的相比，这里的sql语句，子查询多了一个临时表，这时候的DEPENDENT SUBQUERY使用的是临时表，而临时表是经过筛选的数据并不会很大。
理论上：12908 + 1207 = 14,115，实际执行了 14117条，比理论上多了两条（找不到资料解释，我认为应该是临时表的原因）。
xxx_quote新增索引
SQL -- 新增索引 ALTER TABLE `xxx_quote` ADD INDEX quote_id (`quote_id`); -- 删除新增的索引 ALTER TABLE `xxx_quote` DROP INDEX quote_id; |
SQL DELETE FROM xxx_quote_parameter WHERE quote_num IN (SELECT quote_id FROM xxx_quote WHERE project_num IN ('2307310020','2212010015','2210090011','2210090009','2210080028')); |
理论上查1207条就行了，但是实际上1208条，多了一条(我猜测这多出来的一条是查索引的)。
经过测试，索引版本的比临时表版本还是要快不少的。和这个Handler_read_rnd_next的情况也算一致（Handler_read_rnd_next越高，执行速度越慢）。