面试题：MySQL索引下多个范围条件对执行计划的影响及优化策略

Question

假设有一张表`users`，包含字段`id`（主键）、`age`、`salary`，当查询语句为`SELECT * FROM users WHERE age > 30 AND salary < 50000;`时，MySQL的执行计划可能会怎样？如何通过优化索引或查询语句来提升性能？详细说明你的分析过程。

Answer 1

MySQL执行计划分析

1. 全表扫描：如果age和salary字段上均没有索引，MySQL可能会对users表进行全表扫描。它会逐行读取表中的数据，检查每一行数据的age是否大于30且salary是否小于50000，这种方式在数据量较大时性能较差。
2. 使用索引：
   • 单索引情况：
     • 如果只有age字段上有索引，MySQL可能会先使用age索引找到满足age > 30的记录，然后再对这些记录逐行检查salary < 50000条件，这可能比全表扫描快，但对于满足age > 30记录较多时，后续检查salary条件开销仍较大。
     • 同理，如果只有salary字段上有索引，MySQL可能先通过salary索引找到满足salary < 50000的记录，再逐行检查age > 30条件。
   • 复合索引情况：如果有复合索引(age, salary)，MySQL可以利用该复合索引快速定位到满足age > 30且salary < 50000的记录。因为复合索引是先按age排序，在age相同的情况下再按salary排序，所以可以快速过滤出符合条件的数据，性能较好。

优化方法

1. 优化索引：
   • 创建复合索引：可以创建复合索引CREATE INDEX idx_age_salary ON users(age, salary);。这样MySQL在执行查询时，能更有效地利用索引快速定位满足两个条件的数据，减少数据扫描量。在复合索引中，字段顺序很重要，这里将age放在前面是因为查询条件中age在前，MySQL会优先使用最左前缀原则匹配索引。
   • 避免冗余索引：避免创建不必要的索引，因为索引本身会占用额外的存储空间，并且在数据插入、更新和删除时，MySQL需要更新索引，增加额外的开销。例如，已经有了(age, salary)复合索引，就没必要再单独创建age或salary的单索引（除非有其他查询场景有单独使用age或salary索引的需求）。
2. 优化查询语句：
   • 确保查询条件简洁：确保查询条件没有不必要的函数调用或复杂的表达式。例如，如果查询条件写成SELECT * FROM users WHERE UPPER(name) = 'JOHN' AND age > 30 AND salary < 50000;，由于UPPER(name)函数调用，MySQL无法使用name字段上的索引（如果有的话），会导致性能下降。应尽量避免这种情况，保持查询条件简单直接。
   • 合理使用覆盖索引：如果查询只需要部分字段，如SELECT age, salary FROM users WHERE age > 30 AND salary < 50000;，并且创建了(age, salary)复合索引，MySQL可以直接从索引中获取数据，而不需要回表操作（即从索引找到数据对应的主键，再通过主键去聚簇索引中获取完整数据行），进一步提升性能。可以考虑调整查询，尽量利用覆盖索引。