面试题：MySQL多列索引与单列索引在搜索场景下的性能对比及适用情况

Question

假设在一个MySQL表中有多个经常用于搜索的列，说明多列索引和单列索引分别在什么样的搜索条件下性能更优，以及应该如何根据实际业务场景选择合适的索引类型，并分析原因。

Answer 1

多列索引性能更优的搜索条件

1. 全列匹配：当查询条件使用了索引中所有列，并且顺序与索引列顺序一致时，多列索引能发挥最大功效。例如，表有 CREATE INDEX idx_name_age ON users (name, age)，查询 SELECT * FROM users WHERE name = 'John' AND age = 30，此时索引可高效定位数据。
2. 前缀匹配：如果查询只使用了多列索引的前部分列。如上述索引，查询 SELECT * FROM users WHERE name = 'John'，MySQL 可以利用多列索引的 name 列进行快速查找。

单列索引性能更优的搜索条件

1. 单个列查询：当查询仅涉及单个列，且该列有单列索引时，单列索引性能较好。例如，表有 CREATE INDEX idx_age ON users (age)，查询 SELECT * FROM users WHERE age = 30，单列索引可直接用于查找。
2. 不同列的独立查询：如果业务中不同查询频繁针对不同列进行，为每个列创建单列索引更合适。比如，有时查询 name，有时查询 age，且频率相当，单列索引能满足各自查询需求。

如何根据实际业务场景选择合适的索引类型

1. 分析查询模式：
   • 若大部分查询是针对多个列的组合条件，多列索引优先。例如电商系统中，经常按商品类别和价格区间查询商品，多列索引可优化此类查询。
   • 若查询以单个列条件为主，单列索引更适用。如社交平台按用户 ID 查找用户信息，单列索引即可。
2. 考虑列的选择性：
   • 选择性高的列（列中不同值的比例高）适合做索引列。在多列索引中，将选择性高的列放在前面可提高索引效率。
   • 若某列选择性极低（如性别列只有两种值），即使创建索引对查询性能提升可能也有限。
3. 避免索引膨胀：
   • 创建过多索引会增加存储开销和数据修改时的维护成本。若业务中某些查询频率极低，可不为此类查询创建索引。
   • 权衡索引数量，保证常用查询性能的同时，控制数据库整体负担。

原因分析

1. 多列索引：
   • 多列索引能利用索引的有序性，快速定位满足多个条件的数据行。在全列匹配和前缀匹配场景下，通过索引可直接找到目标数据，减少磁盘 I/O 操作。
   • 但多列索引对查询条件顺序敏感，若查询条件顺序与索引列顺序不一致，可能无法充分利用索引。
2. 单列索引：
   • 单列索引简单直接，适用于单个列的查询。多个单列索引在不同列查询时能各自发挥作用。
   • 然而，当查询涉及多个列组合条件时，单列索引可能无法提供像多列索引那样的高效组合查找能力，数据库可能需要进行多次索引扫描和结果合并，增加查询成本。