时间戳

首页 > 解决方案 > MySQL 删除子查询成本数百秒，而选择查询成本低得多

mysql - MySQL 删除子查询成本数百秒，而选择查询成本低得多

问题描述

我有两个表，定义如下：

CREATE TABLE `a`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `data` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 150 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

a中有10k行，b中有200k行，所有数据都是由随机函数生成的。下面是一个示例：

151 8VE6BU06    8VE6BU06    2019-09-12 23:07:39

这里有三个测试，

1.案例1花费2.889s

SELECT cid FROM `a` WHERE a.cid not in (select b.cid from b);

execution plan
1   PRIMARY            a    ALL                 10094   Using where
2   SUBQUERY           b    ALL                 199826

2. 案例 2 花费 628.699s

delete from `a` WHERE a.cid not in (select b.cid from b);

execution plan
1   PRIMARY             a   ALL                 10094   Using where
2   DEPENDENT SUBQUERY  b   ALL                 199826  Using where

3. case 3 耗时 0.036s 

alter table b add index cid(cid);
delete from `a` WHERE a.cid not in (select b.cid from b);

execution plan                           
1   PRIMARY             a   ALL                             10094   Using where
2   DEPENDENT SUBQUERY  b   index_subquery  cid cid 302 func    1   Using index

问题

  1. 为什么 test2 需要这么长时间？
  2. 为什么 test1 使用 SUBQUERY 而 test2 使用 DEPENDENT SUBQUERY？与 test2 相比，test1 执行得如此之快的原因是什么？
  3. 为什么 test3 比 test2 快？

MySQL 版本：5.6.20 innodb

表 b 中的行数据如下：

151 8VE6BU06    8VE6BU06      2019-09-12 23:07:39

标签： mysqlsqlperformanceinnodbmysql-5.5

解决方案

  • 5.5 解析和优化DELETE与. 更高版本（5.7？或 8.0）会更好。UPDATESELECT
  • DELETE必须创建撤消记录以防中途崩溃DELETE；相对于仅选择而言，这是相当昂贵的。
  • 注意前两个如何EXPLAINs说ALLand ALL。这意味着“对于一个表中的每一行，它会扫描另一个表的所有行”。第三个解释是速度快几个数量级，因为INDEX.
  • 在旧版本中，NOT IN ( SELECT ... )通常执行得非常差——例如在完整扫描等中。
  • DELETE考虑使用 aLEFT JOIN ... IS NULL而不是方法更改为“多表” IN。
  • 如果您正在为“冷”系统计时，那么会有很多 I/O。
  • 如果innodb_buffer_pool_size太小而无法容纳所有b，这将导致大量 I/O，因此查询 2 的时间很长。
  • 不知道 和 的平均大小，cid进一步data讨论 I/O 需求是不切实际的。

推荐阅读