面试题：MySQL索引性能优化之多列索引类型搭配

Question

假设你有一个包含用户信息的表，其中有姓名（VARCHAR类型）、年龄（INT类型）和注册时间（DATETIME类型）三个字段，经常会根据姓名和年龄联合查询用户。请设计合适的索引，并阐述选择索引列类型及顺序的原因，以及如何通过EXPLAIN语句来验证索引的有效性。

Answer 1

1. 设计索引：
   • 应该创建一个联合索引 CREATE INDEX idx_name_age ON your_table_name (name, age);
2. 选择索引列类型及顺序的原因：
   • 列类型：
     • name 为 VARCHAR 类型，VARCHAR 类型适用于存储可变长度的字符串，对于姓名这种长度不固定的文本数据很合适。
     • age 为 INT 类型，整数类型存储年龄占用空间小且查询效率高，符合年龄数据的特征。
   • 列顺序：
     • 把 name 放在前面，因为经常根据姓名和年龄联合查询，联合索引遵循“最左前缀原则”。如果查询条件是 WHERE name = 'xxx' AND age = 20，先按 name 索引查找，再在 name 相同的记录中按 age 查找，能快速定位到满足条件的记录。如果把 age 放在前面，当查询 WHERE name = 'xxx' AND age = 20 时，索引只能先按 age 查找，对于 name 无法利用索引，效率会降低。
3. 通过 EXPLAIN 语句验证索引有效性：
   • 使用 EXPLAIN 语句如下：EXPLAIN SELECT * FROM your_table_name WHERE name = 'xxx' AND age = 20;
   • 查看 EXPLAIN 结果中的 key 字段，如果显示为我们创建的索引名 idx_name_age，说明查询使用到了该索引，索引有效。如果 key 字段为 NULL，则说明索引未被使用，可能需要检查查询语句、索引设计等问题。另外，还可以结合 type 字段，理想情况下为 ref（如果是多表连接可能是 eq_ref 等更好的类型），表示使用索引进行了连接操作，进一步证明索引有效提高了查询效率。