面试题：MySQL创建高效B+树索引时如何考虑字段顺序

Question

在MySQL中创建联合B+树索引，字段顺序对索引的效率影响很大。请举例说明如何根据查询场景来确定联合索引中字段的顺序，以达到最优的查询性能。

Answer 1

假设我们有一个employees表，包含department（部门）、salary（薪资）和name（姓名）字段，有以下不同查询场景：

1. 仅基于department查询：

   CREATE INDEX idx_department ON employees (department);
   

   此时索引字段顺序为department，因为查询仅依赖该字段，这样索引效率最高。
2. 基于department和salary查询，且department过滤性强：

   CREATE INDEX idx_department_salary ON employees (department, salary);
   

   先列department，因为它过滤性强，在查询时department先筛选出较少的数据，再用salary进一步筛选，能充分利用索引提高效率。例如查询select * from employees where department = 'HR' and salary > 5000;，MySQL可以先通过department定位到HR部门员工，再用salary筛选。
3. 基于salary和department查询，且salary过滤性强：

   CREATE INDEX idx_salary_department ON employees (salary, department);
   

   这种情况下salary过滤性强，先按salary筛选，再用department，能提高查询效率。如查询select * from employees where salary > 8000 and department = 'Engineering';
4. 基于三个字段department、salary和name查询，department过滤性最强，salary次之：

   CREATE INDEX idx_department_salary_name ON employees (department, salary, name);
   

   按照过滤性从强到弱排序字段，查询如select * from employees where department = 'Finance' and salary > 6000 and name = 'John';时，先通过department筛选大量数据，再用salary，最后name，可达到最优查询性能。

总结来说，在创建联合B+树索引时，应将过滤性强（即能快速缩小结果集范围）的字段放在索引的前列，以提高查询性能。