semijoin
从 MySQL 5.7 迁移到 MySQL 8.0.34 后,当位于on
optimizationr_switch 中时,我们的查询出现了非常奇怪的行为。
1.有问题的查询:
SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
SELECT s4_.id
FROM emails_history i5_
INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
WHERE i5_.rule_id IN (1517676 , 1517677)
)
在这种情况下,这是执行计划:
ID | 选择类型 | 桌子 | 分区 | 类型 | 可能的键 | 钥匙 | 密钥长度 | 参考 | 行 | 过滤的 | 额外的 |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 简单的 | s0_ | 无效的 | 指数 | 无效的 | 商店_购物者_商店_id_idx | 4 | 无效的 | 361627391 | 100.00 | 使用索引 |
1 | 简单的 | 无效的 | 等式引用 | <自动不同键> | <自动不同键> | 5 | remarkety_prod.s0_.id | 1 | 100.00 | 使用地点;不存在 | |
2 | 物化 | i5_ | 无效日期,p2022m03,... | 全部 | emails_history_rule_id_idx | 无效的 | 无效的 | 无效的 | 7526556120 | 100.00 | 使用地点 |
2 | 物化 | s4_ | 无效的 | 等式引用 | 基本的 | 基本的 | 4 | remarkety_prod.i5_.shopper_id | 1 | 100.00 | 使用索引 |
问题是i5_ JOIN类型是ALL,JOIN中没有使用索引。
2. IN 子句中只有一个值:
如果IN
查询中的子句仅包含一个值:
SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
SELECT s4_.id
FROM emails_history i5_
INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
WHERE i5_.rule_id IN (1517676) -- only one value
)
执行计划变为:
ID | 选择类型 | 桌子 | 分区 | 类型 | 可能的键 | 钥匙 | 密钥长度 | 参考 | 行 | 过滤的 | 额外的 |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 简单的 | s0_ | 无效的 | 指数 | 无效的 | 商店_购物者_商店_id_idx | 4 | 无效的 | 361632497 | 100.00 | 使用索引 |
1 | 简单的 | 无效的 | 等式引用 | <自动不同键> | <自动不同键> | 5 | remarkety_prod.s0_.id | 1 | 100.00 | 使用地点;不存在 | |
2 | 物化 | i5_ | 无效日期,p2022m03,... | 参考 | emails_history_rule_id_idx | emails_history_rule_id_idx | 5 | 常量 | 56944 | 100.00 | 无效的 |
2 | 物化 | s4_ | 无效的 | 等式引用 | 基本的 | 基本的 | 4 | remarkety_prod.i5_.shopper_id | 1 | 100.00 | 使用索引 |
在这种情况下,JOIN 使用索引,但执行计划仍然与 MySQL 5.7 中的执行计划不同
3. 使用 i5_.shopper_id 代替 s4_.id(应该是相同的)
SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
SELECT i5_.shopper_id -- using i5_.shopper_id instead of s4_.id
FROM emails_history i5_
INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
WHERE i5_.rule_id IN (1517676 , 1517677)
ID | 选择类型 | 桌子 | 分区 | 类型 | 可能的键 | 钥匙 | 密钥长度 | 参考 | 行 | 过滤的 | 额外的 |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 基本的 | s0_ | 无效的 | 指数 | 无效的 | 商店_购物者_商店_id_idx | 4 | 无效的 | 361632516 | 100.00 | 使用地点;使用索引 |
2 | 子查询 | i5_ | 无效日期,p2022m03,... | 范围 | emails_history_rule_id_idx | emails_history_rule_id_idx | 5 | 无效的 | 56945 | 100.00 | 使用索引条件;使用地点 |
2 | 子查询 | s4_ | 无效的 | 等式引用 | 基本的 | 基本的 | 4 | remarkety_prod.i5_.shopper_id | 1 | 100.00 | 使用索引 |
在这种情况下,执行计划与我们在 MySQL 5.7 中的执行计划相同
4.半连接=关闭
SET optimizer_switch = 'semijoin=off';
SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
SELECT s4_.id
FROM emails_history i5_
INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
WHERE i5_.rule_id IN (1517676 , 1517677)
)
ID | 选择类型 | 桌子 | 分区 | 类型 | 可能的键 | 钥匙 | 密钥长度 | 参考 | 行 | 过滤的 | 额外的 |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 基本的 | s0_ | 无效的 | 指数 | 无效的 | 商店_购物者_商店_id_idx | 4 | 无效的 | 361632550 | 100.00 | 使用地点;使用索引 |
2 | 子查询 | i5_ | 无效日期,p2022m03,... | 范围 | emails_history_rule_id_idx | emails_history_rule_id_idx | 5 | 无效的 | 56945 | 100.00 | 使用索引条件;使用地点 |
2 | 子查询 | s4_ | 无效的 | 等式引用 | 基本的 | 基本的 | 4 | remarkety_prod.i5_.shopper_id | 1 | 100.00 | 使用索引 |
执行计划与之前相同。
问题:
谁能向我解释一下发生了什么事?
这是优化器中的错误吗?
为什么连接在第一个查询中不使用索引?