理解EXPLAIN SELECT：优化千万行级MySQL exchange表查询

问题1：为什么主查询选择exchange_platform_fk而非exchange_platform_id_created_at_id_index #

主要有三个核心原因：

• 优化器成本估算偏差：主查询需要返回所有字段，使用联合索引exchange_platform_id_created_at_id_index虽然能同时匹配platform_id等值条件和created_at范围条件，但最终仍需要回表读取整行数据；而exchange_platform_fk是单字段索引，体积更小、扫描速度更快，优化器估算认为用该索引过滤出一半数据后，再逐行判断created_at和id IN条件的总成本更低。
• 统计信息精度不足：从执行计划可以看到，优化器估算platform_id=1的行数约为695万，正好是总数据量的一半，没有结合created_at的过滤效果进一步缩小估算行数，低估了联合索引的收益。
• IN子查询的优化限制：当前使用的MySQL版本对IN子查询的物化结果和外层查询条件的合并优化支持有限，没有将id匹配的高过滤性纳入联合索引的成本估算。

问题2：优化方案 #

新增索引 #

新增以下覆盖索引即可同时优化子查询和整体查询效率：

CREATE INDEX idx_platform_createdat_productid_id ON exchange (platform_id, created_at, product_id, id);

这个索引的顺序完全匹配查询逻辑：

1. 最左的platform_id满足等值过滤条件
2. 第二列created_at满足范围过滤条件
3. 第三列product_id满足分组需求
4. 最后一列id满足MIN(id)的聚合需求
   整个子查询可以完全通过该索引完成，不需要回表、不需要临时表、不需要额外排序。

查询改写 #

建议将IN子查询改为JOIN写法，进一步避免MySQL对IN子查询的优化缺陷，改写后SQL如下：

SELECT e.*
FROM `exchange` e
INNER JOIN (
    SELECT MIN(`id`) AS min_id
    FROM `exchange`
    WHERE `platform_id` = 1 AND `created_at` >= '2021-09-17 22:36:11'
    GROUP BY `product_id`
) t ON e.id = t.min_id;

改写后子查询走上述新建的覆盖索引，主查询通过主键ID直接匹配，整体性能可以从原来的14秒以上降到1秒以内。

之前将条件移到子查询变慢的原因 #

是因为当时没有对应的覆盖索引，子查询只能走exchange_platform_fk单字段索引，需要扫描695万行数据，还要用临时表完成分组操作，所以性能反而更差，加上上述索引后该写法配合JOIN会比原始查询快很多。

内容的提问来源于stack exchange，提问作者Lito