MySQL慢查询未使用预期索引原因及优化方案咨询

咱们来一步步解决这个查询慢的问题，先从改写查询语句和优化索引两个核心方向入手：

一、改写查询语句，避免低效的左连接 #

原查询用LEFT JOIN加x.str_spid IS NULL的方式排除特定交易，但这种写法容易产生大量中间连接结果，拖慢查询速度。推荐改用NOT EXISTS子查询，逻辑更清晰，也更容易被优化器高效执行：

SELECT t.str_spid, COUNT(*) AS C
FROM transactions t
WHERE t.str_prid = 148
  AND t.str_amount = 2000
  AND t.str_desc IN ("Annual Rewards", "Annual Rewards (PRO)")
  AND NOT EXISTS (
    SELECT 1
    FROM transactions x
    WHERE x.str_spid = t.str_spid
      AND x.str_prid = 148
      AND x.str_amount = 2500
  )
GROUP BY t.str_spid
HAVING C = 3
LIMIT 0, 100;

NOT EXISTS的优势是：一旦找到符合条件的x记录，就会停止对该spid的查询，不需要遍历所有匹配的x记录，相比左连接的全量连接后过滤，效率提升明显。

二、创建针对性的复合索引 #

1. 针对主查询表t的索引 #

主查询的过滤条件是str_prid=148、str_amount=2000、str_desc IN(...)，最终要按str_spid分组计数。最合适的复合索引应该把过滤性强的字段放在前面，同时做成覆盖索引（包含查询需要的所有字段，避免回表）：

CREATE INDEX idx_prid_amount_desc_spid ON transactions (str_prid, str_amount, str_desc, str_spid);

这个索引的逻辑是：

• 先通过str_prid=148快速缩小范围
• 再过滤str_amount=2000的记录
• 接着匹配str_desc的两个值
• 最后直接从索引里获取str_spid用于分组，不需要访问表的实际数据（覆盖索引）

2. 针对子查询表x的索引 #

子查询需要快速判断某个str_spid是否存在str_prid=148且str_amount=2500的记录，所以创建以下复合索引：

CREATE INDEX idx_spid_prid_amount ON transactions (str_spid, str_prid, str_amount);

这个索引可以让数据库直接通过str_spid定位，再快速匹配str_prid和str_amount的条件，瞬间完成存在性检查。

三、为什么原索引没起作用？ #

你之前创建的复合索引顺序不太匹配查询的过滤逻辑：

• 比如SPID_DE_PRID是(str_spid, str_desc, str_prid)，但查询是先过滤str_prid，再是str_amount和str_desc，索引的前缀字段和查询条件顺序不匹配，优化器没法高效利用它
• SPID_DE_PRID_AMT的字段顺序也不符合查询的过滤优先级，所以优化器最终选择了单字段索引STR_PRID，但单字段索引过滤后的数据量依然很大，导致后续分组和连接操作变慢

额外建议 #

• 执行ANALYZE TABLE transactions;更新表的统计信息，确保查询优化器能基于最新的数据分布选择最优索引
• 如果你的MySQL版本支持，可以用EXPLAIN ANALYZE（注意不是普通的EXPLAIN）查看查询的实际执行过程，确认索引是否被正确使用

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