MySQL查询索引未生效排查：EXPLAIN全表扫描原因及修复方案

• 过滤条件使用了用户自定义变量@beginOfMonth、@beginNextMonth，这类变量是运行时才计算赋值的，MySQL优化器在生成执行计划阶段无法获取变量的具体值，无法准确评估索引范围扫描的过滤效果和成本，就会默认选择成本更低的全表扫描。
• WHERE子句中包含跨表的OR判断（c.ippis IS NOT NULL和a表的条件并列），优化器无法将所有条件下推到a表的索引扫描逻辑中，进一步降低了索引的优先级。
• 现有索引只包含date_updated, loan_status, current_loan, ippis四个字段，而查询需要返回a表的十多个字段，走索引后还需要回表查询聚集索引获取剩余字段，如果符合date范围的行占比超过20%左右，优化器会认为全表扫描的成本比索引+回表的成本更低，因此放弃走索引。

• 替换用户自定义变量为直接的日期常量计算，让优化器在生成执行计划时就能拿到准确的过滤范围：
  把WHERE条件里的@beginOfMonth <= a.date_updated AND a.date_updated < @beginNextMonth替换为：

  a.date_updated >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) 
  AND a.date_updated < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY), INTERVAL 1 MONTH)
  

  同理c表的日期过滤条件也做同样替换，删除原来的SqlVars子查询即可。
• 将OR条件拆分为两个子查询用UNION ALL合并，避免跨表OR导致的索引失效：

  -- 第一个子查询匹配c.ippis IS NOT NULL的情况
  SELECT -- 此处填入你需要查询的所有字段
  FROM loan_applications_tbl a
  LEFT JOIN topup_or_reapplication_tbl c ON 你的原有关联条件
  WHERE 日期过滤条件 AND c.ippis IS NOT NULL
  UNION ALL
  -- 第二个子查询匹配a表自有条件的情况
  SELECT -- 此处填入你需要查询的所有字段
  FROM loan_applications_tbl a
  LEFT JOIN topup_or_reapplication_tbl c ON 你的原有关联条件
  WHERE 日期过滤条件 
  AND a.loan_status IN (  'pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up')
  AND (a.current_loan = '1' OR (a.current_loan = '0' AND a.loan_status IN ('Approved', 'Closed')))
  

• 调整索引为覆盖索引，避免回表开销，把查询需要的a表所有字段都加到索引末尾：

  ALTER TABLE loan_applications_tbl DROP INDEX 原有索引名, ADD INDEX idx_date_status_loan_ippis ( date_updated, loan_status, current_loan, ippis, id, user_unique_id, loan_location, tel_no, organisation, branch, loan_agree, loan_type, appr, sold, top_up, date_created );
  

• 若确认走索引后性能确实更好，也可以临时使用FORCE_INDEX语法强制走目标索引：

  FROM loan_applications_tbl a FORCE INDEX (你的索引名)
  

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