面试题：MySQL索引在多种过滤条件下的优化

Question

假设有一个用户表users，包含字段id（主键）、name、age、email，现在要执行查询语句SELECT * FROM users WHERE name = 'John' AND age > 30 AND email LIKE '%gmail.com'。请分析这条SQL语句在MySQL中如何利用索引来提高查询效率，如果现有索引无法满足，应如何创建合适的索引？

Answer 1

1. 分析现有索引利用情况：
   • 对于 SELECT * FROM users WHERE name = 'John' AND age > 30 AND email LIKE '%gmail.com' 这条语句。
   • 如果有索引，MySQL在查询时，对于 name = 'John' 条件，如果 name 字段上有索引，理论上可以快速定位到 name 为 John 的记录。
   • 然而，对于 age > 30 条件，由于范围查询（>），MySQL在使用联合索引时，范围查询之后的字段（如果有的话）无法再利用索引的有序性进行快速查找。
   • 对于 email LIKE '%gmail.com' 这种以通配符开头的模糊查询，MySQL无法有效利用索引，因为索引是基于有序数据结构，以通配符开头无法快速定位到数据。
2. 创建合适索引：
   • 要提高此查询效率，可以考虑以下索引创建策略。
   • 由于 name 是等值查询，age 是范围查询，为了能尽可能利用索引，我们可以创建一个联合索引：

   CREATE INDEX idx_name_age ON users (name, age);
   

   • 这样，MySQL可以先利用 name 字段的索引快速定位到 name 为 John 的记录，然后在这些记录中利用 age 字段索引筛选出 age > 30 的记录。虽然 email LIKE '%gmail.com' 无法有效利用索引，但通过前两个条件筛选出的记录集已经大幅减少，能在一定程度上提高查询效率。
   • 如果希望进一步优化 email 的查询，在 email 字段前半部分有一定区分度的情况下，可以考虑前缀索引：

   CREATE INDEX idx_email ON users (email(10));
   

   • 这里 10 表示使用 email 字段前10个字符创建索引，在一定程度上可以提高类似 LIKE 'xxx%'（xxx 长度小于等于10）查询的效率，但对于 LIKE '%gmail.com' 这种通配符开头的情况，前缀索引效果有限。