面试题：MySQL索引优化策略 - 复合索引的最左匹配原则

Question

假设有一个复合索引 (col1, col2, col3)，在查询语句中，什么样的条件组合能够充分利用该复合索引？如果查询条件是WHERE col2 = 'value'，这个复合索引是否会被使用，为什么？如何优化这种查询场景？

Answer 1

能够充分利用复合索引 (col1, col2, col3) 的条件组合

1. 最左前缀原则：查询条件按照索引列顺序从左到右依次使用，如 WHERE col1 = 'value1' AND col2 = 'value2' AND col3 = 'value3'，这种情况下会充分利用整个复合索引。
2. 部分最左前缀：如 WHERE col1 = 'value1' AND col2 = 'value2'，会利用索引的前两列。又如 WHERE col1 = 'value1'，会利用索引的第一列。

查询条件为 WHERE col2 = 'value' 时复合索引的使用情况及原因

1. 不会被使用：因为复合索引遵循最左前缀原则，该查询缺少对 col1 的条件限制，数据库引擎无法从索引的最左列开始匹配，所以此复合索引不会被使用。

优化这种查询场景的方法

1. 添加最左列条件：如果业务允许，在查询中添加对 col1 的条件，如 WHERE col1 = 'known_value' AND col2 = 'value'，这样可以利用复合索引。
2. 创建新索引：若无法添加 col1 的条件，考虑创建一个新的以 col2 开头的复合索引，如 (col2, 其他列)，或者单独创建一个关于 col2 的单列索引 CREATE INDEX idx_col2 ON 表名 (col2)，以满足仅对 col2 进行查询的需求。