面试题：MySQL查询优化之索引使用

Question

在MySQL中，假设有一张用户表users，包含字段id（主键）、name、age、email。现在要频繁执行查询语句：SELECT * FROM users WHERE name = '特定姓名' AND age > 30; 请描述如何通过索引来优化此查询，以及创建索引的语句是什么，并说明可能存在的索引陷阱。

Answer 1

索引优化方法

为 name 和 age 字段创建联合索引，这样在执行查询 SELECT * FROM users WHERE name = '特定姓名' AND age > 30; 时，MySQL 可以利用索引快速定位到符合条件的记录，减少全表扫描的开销。

创建索引语句

CREATE INDEX idx_name_age ON users (name, age);

上述语句在 users 表上创建了一个名为 idx_name_age 的联合索引，包含 name 和 age 字段。

可能存在的索引陷阱

1. 索引顺序问题：联合索引的字段顺序很关键，如果创建索引语句写成 CREATE INDEX idx_age_name ON users (age, name);，对于本查询 name = '特定姓名' AND age > 30 ，MySQL 可能无法有效利用索引，因为 age 字段的范围查询会导致索引的部分失效，只有在 name 字段在前，age 字段在后的顺序下，索引才能充分发挥作用。
2. 索引过多问题：过多的索引会占用额外的磁盘空间，并且在数据插入、更新和删除操作时，数据库需要维护索引，从而增加了操作的时间开销，降低了写性能。
3. 选择性问题：如果 name 字段的值重复度很高（即选择性差），即使创建了索引，其优化效果可能也不明显。例如，表中大部分用户姓名相同，索引就无法快速筛选出少量符合条件的数据。