如何优化音乐应用中含复杂WHERE逻辑的MySQL查询性能？

先看原查询：

SELECT id FROM tracks
WHERE 
(
    eligibility = 2 AND(
        artist IN(
            "A", "B", "C", "D", "E", "F"
        ) OR (
            rating >= 3 AND(
                artist IN(
                    "G", "H", "I"
                ) OR genre = "Rock"
            )
        )
    )
);

针对这个查询的性能优化方法如下：

• 拆分OR逻辑为UNION ALL组合查询
  原查询里的多层OR会让优化器难以高效利用索引，把逻辑拆成三个独立的查询，用UNION ALL合并结果。每个子查询的条件更单一，能精准匹配索引：

  SELECT id FROM tracks WHERE eligibility = 2 AND artist IN ("A", "B", "C", "D", "E", "F")
  UNION ALL
  SELECT id FROM tracks WHERE eligibility = 2 AND rating >= 3 AND artist IN ("G", "H", "I")
  UNION ALL
  SELECT id FROM tracks WHERE eligibility = 2 AND rating >= 3 AND genre = "Rock"
  

  如果结果里有重复的id，换成UNION去重，但UNION ALL的性能更高，优先用它。

• 创建针对性的复合索引
  单字段索引在多条件组合查询时作用有限，给每个拆分后的子查询建对应的复合索引：

  • 对应第一个子查询：CREATE INDEX idx_tracks_eligibility_artist ON tracks(eligibility, artist);
  • 对应第二个子查询：CREATE INDEX idx_tracks_eligibility_rating_artist ON tracks(eligibility, rating, artist);
  • 对应第三个子查询：CREATE INDEX idx_tracks_eligibility_rating_genre ON tracks(eligibility, rating, genre);
    这些复合索引能让查询直接从索引里拿到id，不用回表查原数据，速度会快很多。

• 用EXPLAIN排查索引使用情况
  执行EXPLAIN 原查询语句和EXPLAIN 优化后的查询语句，看优化器有没有用到创建的索引。如果没用到，可能是eligibility=2的记录占比太高（比如超过30%），优化器觉得全表扫描更快，这时可以试试强制索引（比如MySQL里用FORCE INDEX），或者调整索引顺序。

• 优化字段数据类型
  把artist、genre这类字符串字段换成整数ID关联（比如建artists表存艺术家名称和id，tracks表存artist_id），字符串索引比整数索引大，查询速度慢。如果必须用字符串，选VARCHAR而不是TEXT类型，减少索引存储开销。

• 定期维护表和索引
  定期清理表碎片、更新统计信息：MySQL用OPTIMIZE TABLE tracks;，PostgreSQL用VACUUM ANALYZE tracks;。统计信息过时的话，优化器会生成糟糕的执行计划，维护后能让优化器做出更优选择。

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