MySQL添加索引后查询性能未提升，请求分析优化

现有索引无效的原因 #

1. idx_close_emas 索引顺序与查询需求不匹配
   该索引以 close 作为前缀，但你的查询过滤条件是 ema_200 > 0 AND close > ema_200 —— close > ema_200 是依赖于 ema_200 的动态范围条件，数据库无法利用 close 前缀快速定位符合条件的记录；同时索引未包含 symbol 和 date，完全无法支持窗口函数的 PARTITION BY symbol 和 ORDER BY date DESC 操作，导致索引基本失效。

2. unique_symbol_date 索引缺少过滤字段
   该索引包含 symbol 和 date，能支持窗口函数的分组排序，但未包含 ema_200 和 close 字段。数据库使用该索引时，需要频繁回表查询这两个字段进行过滤，相当于遍历全量数据后再做筛选，性能提升有限。

优化方案 #

1. 创建针对性覆盖索引 #

根据查询逻辑，最优索引需要同时满足：

• 快速按 symbol 分组（窗口函数分区需求）
• 快速过滤 ema_200 > 0 的记录
• 无需额外排序即可获取 date 降序的记录
• 无需回表即可获取 close 字段用于过滤

MySQL 8.0.19+ 版本（支持 INCLUDE）：

CREATE INDEX idx_symbol_ema200_date_inc_close ON indicators_5m (symbol, ema_200, date DESC) INCLUDE (close);

INCLUDE 用于将 close 作为附加字段存入索引，避免回表查询，同时不破坏索引的前缀顺序。

低版本 MySQL（不支持 INCLUDE）：

CREATE INDEX idx_symbol_ema200_close_date ON indicators_5m (symbol, ema_200, close, date DESC);

将 close 加入索引前缀，虽然会增加索引大小，但能实现过滤时无需回表。

2. 验证索引效果 #

创建索引后，重新执行 EXPLAIN 查看执行计划：

• 应显示使用上述新创建的索引
• Extra 列应包含 Using index（表示使用覆盖索引，无需回表）和 Using where（表示在索引层面完成过滤）
• 窗口函数的排序操作会被索引的 date DESC 顺序覆盖，避免额外的 filesort

3. 可选：SQL 微调（非必须） #

如果查询不需要返回所有字段，建议明确列出需要的字段，而非使用 SELECT *，这样可以进一步缩小覆盖索引的大小，提升性能。例如：

EXPLAIN SELECT i.symbol, i.date, i.close, i.ema_200
FROM (
    SELECT symbol, date, close, ema_200,
           ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC) AS row_num
    FROM indicators_5m
    WHERE ema_200 > 0 AND close > ema_200
) AS i
WHERE i.row_num <= 30;

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