带WHERE子句与DISTINCT的MySQL慢查询如何创建有效索引

原有索引失效的原因 #

你创建的(job_level, total_employees, company_name)联合索引不符合最左匹配规则的生效逻辑：联合索引遇到范围查询条件后，后续的字段无法利用索引的有序性。你的查询中total_employees > 50是范围条件，所以索引只能用来过滤job_level和total_employees两个字段的结果，后续的company_name去重需要扫描所有符合条件的记录再做临时表去重，数据量大会非常慢，如果符合job_level = 'Manager'的记录占比过高，优化器甚至会直接选择全表扫描，看起来就像索引没生效。
另外你提到的普通SQL视图对性能没有帮助，普通视图只是虚拟表，执行时还是会运行原始查询逻辑，不会提升查询效率。

正确的索引创建方案 #

调整联合索引的顺序，创建如下索引：

CREATE INDEX idx_job_level_company_total ON contacts (job_level, company_name, total_employees);

这个索引的生效逻辑是：

• 第一个字段job_level是等值查询条件，直接定位到所有job_level = 'Manager'的索引条目
• 第二个字段是要去重的company_name，同个job_level下的company_name在索引里是有序排列的，可以直接用松散索引扫描（Loose Index Scan）快速跳过重复的公司名，不需要扫描所有记录
• 第三个字段total_employees是范围条件，只需要校验每个公司名对应的条目里是否有满足total_employees > 50的记录即可，不需要全量扫描

验证和优化补充 #

• 建完索引后执行EXPLAIN查看执行计划，只要Extra列出现Using index for group-by就说明松散索引扫描生效，通常600万条数据的查询耗时可以降到毫秒级
• 如果优化器没有自动选择这个索引，可以用FORCE INDEX强制指定，示例：

  SELECT DISTINCT company_name FROM contacts FORCE INDEX (idx_job_level_company_total) WHERE job_level = 'Manager' AND total_employees > 50
  

• 执行ANALYZE TABLE contacts;更新表统计信息，避免优化器因为统计数据不准误判索引成本
• 确认job_level字段的字符集和查询语句中'Manager'的字符集一致，避免隐式字符转换导致索引失效

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