面试题：MySQL索引列顺序选择原则的基础应用

Question

假设有一个 `employees` 表，包含 `employee_id`、`department`、`salary` 字段，查询语句为 `SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;`，请基于MySQL索引列顺序的选择原则，设计合适的索引并说明原因。

Answer 1

1. 设计索引：

   CREATE INDEX idx_department_salary ON employees (department, salary);
   

2. 原因：
   • 在MySQL中，索引列顺序遵循最左前缀原则。对于查询 SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;，将 department 放在索引的第一列，因为查询条件中首先是对 department 进行精确匹配。
   • 接着将 salary 放在索引的第二列，虽然 salary 是范围查询，但基于最左前缀原则，先通过 department 筛选出满足条件的部分数据，再对这些数据基于 salary 进行范围筛选，能有效利用索引，提升查询效率。如果将 salary 放在索引第一列，由于其是范围查询，MySQL很难有效地利用索引，会导致索引失效，查询效率降低。所以按照 department、salary 的顺序创建复合索引是合适的。