MySQL查询性能优化求助：现有索引未被使用的问题排查

问题分析 #

当前查询存在两个核心问题：

• 触发全表扫描（type=ALL），现有索引idx_transactions_dd未被使用，根源是OR条件导致MySQL无法高效利用原有索引的字段顺序；
• 出现Using filesort，因为排序字段transaction_id不在索引中，需要额外内存/磁盘排序。

优化方案 #

一、索引优化（优先推荐） #

1. 创建适配查询逻辑的联合索引

针对查询的两个分支，创建包含排序字段的联合索引，让索引同时覆盖过滤条件和排序需求：

-- 覆盖DEPOSIT分支的索引：先按type过滤，再按时间范围，接着匹配reason，最后包含排序字段
CREATE INDEX idx_trans_type_created_reason_id ON transactions(type, created_at, transaction_reason, transaction_id);

这个索引的优势：

• 先通过type的等值过滤快速缩小数据集；
• 再利用created_at的范围筛选进一步过滤；
• 最后匹配transaction_reason的IN条件，且索引末尾的transaction_id直接支持排序，消除filesort。

2. 调整现有索引（若不想新建）

修改现有idx_transactions_dd的字段顺序，并补充排序字段：

ALTER TABLE transactions DROP INDEX idx_transactions_dd;
CREATE INDEX idx_transactions_dd ON transactions(created_at, type, transaction_reason, transaction_id);

调整后type前置，能让MySQL先通过等值条件过滤，再结合时间范围和reason匹配，同时覆盖排序字段。

二、查询语句调整（无索引修改权限时使用） #

将OR条件拆分为两个独立查询，用UNION ALL合并（无需去重，性能更优）：

SELECT *
FROM transactions 
WHERE created_at BETWEEN '2022-08-25 01:03:21' AND '2022-12-13 01:03:21'
  AND transaction_reason IN ('ORIGINAL','REVERSAL_OF_ADJUSTMENT') 
  AND type = 'DEPOSIT'
UNION ALL
SELECT *
FROM transactions 
WHERE created_at BETWEEN '2022-08-25 01:03:21' AND '2022-12-13 01:03:21'
  AND transaction_reason IN ('ADJUSTMENT','REVERSAL_OF_ORIGINAL') 
  AND type = 'WITHDRAWAL'
ORDER BY transaction_id ASC;

拆分后两个子查询可分别匹配索引，减少扫描行数，最后统一排序。

三、额外优化建议 #

• 替换SELECT *为具体需要的字段，让索引成为覆盖索引，避免回表查询；
• 确认created_at字段类型与查询条件的字符串格式匹配（如datetime类型对应正确的字符串格式），类型不匹配会导致索引失效；
• 执行EXPLAIN验证优化效果：检查key字段是否显示使用目标索引，rows字段数值大幅降低，且Extra中无Using filesort。

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