MySQL 派生表为什么阻止索引下推

MySQL 派生表并不是天然阻止所有“索引下推”。真正容易混淆的是两个名字：derived_condition_pushdown 负责把外层条件尽量推入派生查询块；Index Condition Pushdown（ICP） 则发生在存储引擎读取基表索引时。派生表一旦被物化，外层查询不能把 ICP 直接穿过临时结果推到基表，但仍可能先做派生条件下推，减少物化行数。

先分清派生条件下推和索引条件下推

MySQL 优化派生表时有两条路：把派生查询块合并到外层，或者先生成内部临时表再读取。能合并时，外层条件有机会直接作用于基表；不能合并时，优化器仍可尝试 derived_condition_pushdown。这一步优化的是查询块之间的过滤位置。

ICP 的对象不同。它要求 MySQL 已经通过 range、ref 等方式访问某个基表索引，然后用索引条目中的列先判断一部分条件，只有通过后才回表读完整行。因此，物化派生表的临时结果不是基表二级索引，不能期待外层过滤在它上面显示为 ICP。

哪些结构会让派生表走物化

下面的例子保留聚合，是为了观察“不能合并但可以继续下推”的边界：

-- 聚合派生表通常不能直接合并到外层查询块
SELECT *
FROM (
    SELECT customer_id, status, MAX(created_at) AS latest_at
    FROM orders
    GROUP BY customer_id, status
) AS dt
WHERE dt.status = 1
  AND dt.latest_at >= '2026-01-01';

GROUP BY、聚合函数、DISTINCT、HAVING、LIMIT、UNION 和部分子查询结构都可能阻止合并。此时 dt.status = 1 这类引用分组列的条件可能下推成派生表内部的 WHERE；dt.latest_at 是聚合结果，则更接近内部 HAVING。过滤越早，参与聚合和物化的数据通常越少，但这仍不是 ICP。

用开关和 hint 控制可下推的条件

MySQL 8.4 中，derived_condition_pushdown 默认开启。可以先查看当前会话设置，再针对单条语句控制，避免为了一个慢查询改动全局行为：

-- 只检查当前会话，避免把诊断动作扩大到全局
SELECT @@optimizer_switch;

-- 仅对本条语句打开派生条件下推
SELECT /*+ DERIVED_CONDITION_PUSHDOWN */ *
FROM (
    SELECT customer_id, status, MAX(created_at) AS latest_at
    FROM orders
    GROUP BY customer_id, status
) AS dt
WHERE dt.status = 1;

-- 仅对本条语句关闭它，用于对照计划
SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN */ *
FROM (
    SELECT customer_id, status, MAX(created_at) AS latest_at
    FROM orders
    GROUP BY customer_id, status
) AS dt
WHERE dt.status = 1;

如果目标是控制“合并还是物化”，对应的是 MERGE、NO_MERGE hint 或 optimizer_switch 中的 derived_merge。不要用 index_condition_pushdown 开关去解决派生查询块的过滤位置问题。

用 EXPLAIN 判断到底卡在哪里

先看派生查询是否仍作为独立节点存在，再看基表访问是否命中合适索引。示例检查命令如下：

-- 先看估算计划；重点比较派生层和基表层的字段
EXPLAIN FORMAT=TREE
SELECT *
FROM (
    SELECT customer_id, status, MAX(created_at) AS latest_at
    FROM orders
    GROUP BY customer_id, status
) AS dt
WHERE dt.status = 1;

如果派生表保留为物化节点，重点看外层条件是否减少了内部扫描或聚合输入；如果基表侧出现 Using index condition，说明 ICP 在基表访问处生效。两者可以同时出现，也可以只有其一。

改写前的检查清单

• 先确认慢的是派生表内部扫描、物化结果读取，还是基表回表。
• 用 EXPLAIN 对比开启和关闭 derived_condition_pushdown 后的计划，不凭单个 Extra 文本下结论。
• 只有在语义不变时才把外层条件手工移入子查询；涉及聚合结果的条件必须留在合适的 HAVING 语义上。

相关问题

派生表一定会阻止索引条件下推吗？

不一定。派生表可能被合并，或者基表访问部分仍使用 ICP；准确结论要看优化后的计划，而不是是否写了括号子查询。

为什么开启 derived_condition_pushdown 仍然很慢？

它只改变条件进入派生查询块的位置，不能替代合适的基表索引，也不能消除聚合、排序或物化本身的成本。

看到 Using index condition 就代表派生条件下推成功吗？

不是。该提示说明基表索引侧使用了 ICP；派生条件下推要结合派生节点、内部 WHERE/HAVING 和整体计划判断。