已命中INDEX的MySQL查询处理数十万条数据仍耗时过长如何优化

前置说明 #

原测试查询语句存在语法错误，WHERE条件中多个过滤条件之间缺少AND关键字，修正后正确的语句为：

SELECT id,transaction_date, currency, status
FROM merchant_bank_transaction 
WHERE merchant_id=1
AND transaction_date>='2020-04-01'
AND transaction_date<='2020-04-30'
AND transaction_type in ('D', 'W')
ORDER BY id desc, transaction_date desc
LIMIT 0, 50;

• 现有index_primary联合索引仅包含merchant_id、transaction_date两个字段，仅能完成这两个字段的条件过滤，剩余逻辑都需要回表操作：
  • 校验transaction_type是否符合IN条件需要回表读取聚簇索引数据
  • 读取返回结果需要的currency、status字段也需要回表，40-50万次随机IO是高耗时的主要原因
• 现有索引的存储顺序无法匹配ORDER BY id desc, transaction_date desc的排序要求，MySQL需要对过滤后的数十万条记录做文件排序（filesort），额外消耗大量CPU和IO资源

最优方案：创建覆盖联合索引 #

创建匹配查询全链路需求的覆盖联合索引，完全消除回表和文件排序开销，优化后查询可做到毫秒级返回：

CREATE INDEX idx_merchant_tran_date ON merchant_bank_transaction (merchant_id, transaction_type, transaction_date DESC, id DESC, currency, status);

索引设计逻辑： #

• 首列放等值过滤的merchant_id，快速定位到目标商户的所有记录
• 第二列放transaction_type，直接在索引层完成IN条件过滤，不需要回表
• 第三、四列按排序规则依次放transaction_date DESC、id DESC，索引本身按该顺序存储，直接按顺序取前50条即可，不需要额外排序
• 最后追加查询需要返回的currency、status字段，实现索引覆盖，全程无需访问聚簇索引

辅助优化 #

• 调整排序逻辑：自增主键id的大小和transaction_date的时间顺序天然正相关，业务允许的前提下可将排序规则简化为ORDER BY id desc，进一步降低排序开销
• 字段类型优化：status、transaction_type、currency都是短枚举值，可从varchar(255)改为char(1)或tinyint类型，大幅减小表和索引的存储空间，提升查询效率
• 大表场景优化：单表数据量超过千万时，可按transaction_date做范围分区，进一步缩小数据扫描范围

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