面试题：MySQL单条查询剖析之中等难度：索引对单条查询的影响

Question

假设有一张名为`employees`的表，包含字段`id`（主键）、`name`、`department`和`salary`。现在要查询`department`为'Engineering'且`salary`大于50000的员工信息。请分析在`department`和`salary`字段上分别创建索引、联合索引对这条单条查询语句性能的影响，并写出对应的SQL创建索引语句。

Answer 1

分别创建索引

• 性能影响分析：
  • 在department字段上创建索引，查询时可以快速定位到department为'Engineering'的记录。但对于salary字段的条件判断，还需要全表扫描这部分记录来筛选出salary大于50000的。
  • 在salary字段上创建索引，查询时可以快速定位到salary大于50000的记录，但对于department字段的条件判断，还需要全表扫描这部分记录来筛选出department为'Engineering'的。因此，分别创建索引对性能提升有限，因为每次只能利用一个索引来筛选部分条件。
• SQL创建索引语句：

CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);

联合索引

• 性能影响分析：
  • 创建联合索引(department, salary)，查询优化器可以使用这个联合索引，先通过department字段快速定位到department为'Engineering'的记录，然后在这些记录中通过salary字段筛选出salary大于50000的记录。联合索引可以充分利用两个条件进行快速筛选，相比分别创建索引，性能会有显著提升。
• SQL创建索引语句：

CREATE INDEX idx_department_salary ON employees(department, salary);