面试题：MySQL联合索引的最左匹配原则及优化应用

Question

假设有一张表`employees`，包含字段`id`、`name`、`department`、`salary`，现在创建了一个联合索引`index_emp (id, name, department)`。请说明最左匹配原则在这个联合索引中的具体表现，并且举例说明如何基于这个原则优化相关的查询语句，例如如何设计查询条件能够最大程度利用该联合索引来提升查询性能。

Answer 1

1. 最左匹配原则在联合索引 index_emp (id, name, department) 中的表现：
   • 联合索引在查询时，只有当查询条件从索引的最左边开始连续匹配时，才能充分利用索引。也就是说，查询条件必须先使用 id 字段，然后可以接着使用 name 字段，再接着可以使用 department 字段。如果跳过某个字段，索引的部分功能可能无法发挥。
   • 例如，索引结构类似一颗B - Tree，先根据 id 进行排序，id 相同的情况下再按 name 排序，name 相同的情况下再按 department 排序。所以查询时也需要从 id 开始匹配。
2. 基于该原则优化查询语句的示例：
   • 充分利用索引的查询：

     -- 先使用id字段，能充分利用索引
     SELECT * FROM employees WHERE id = 1; 
     -- 先使用id字段，接着使用name字段，能充分利用索引
     SELECT * FROM employees WHERE id = 1 AND name = 'John'; 
     -- 先使用id字段，接着使用name字段，再使用department字段，能充分利用索引
     SELECT * FROM employees WHERE id = 1 AND name = 'John' AND department = 'HR'; 
     

   • 未充分利用索引的查询（部分场景下索引无法完全发挥作用）：

     -- 跳过了id字段，索引无法充分利用
     SELECT * FROM employees WHERE name = 'John'; 
     -- 跳过了id和name字段，索引无法充分利用
     SELECT * FROM employees WHERE department = 'HR'; 
     -- 中间跳过字段，索引无法充分利用
     SELECT * FROM employees WHERE id = 1 AND department = 'HR'; 
     

通过确保查询条件遵循最左匹配原则，按照 id、name、department 的顺序依次出现在 WHERE 子句中，可以最大程度利用该联合索引来提升查询性能。