联合索引(Composite Index),也称为复合索引或多列索引,是指在多个列上创建的索引。与单列索引不同,联合索引可以包含两个或更多的列,用于加速涉及这些列的查询。联合索引的设计和使用需要遵循最左前缀原则,以确保索引能够被有效利用。
概念解释
联合索引包含多个列,其索引键是由这些列的值组合而成的。例如,假设有一个联合索引包含列(A, B, C)
,即INDEX(A, B, C)
。联合索引的键值是按照列的顺序组合而成的,如(A1, B1, C1)
、(A2, B2, C2)
等。
作用
联合索引的主要作用是:
- 加速查询:对于涉及多个列的查询,联合索引可以显著提高查询性能。
- 减少索引数量:通过创建联合索引,可以减少数据库中的索引数量,从而节省存储空间和维护成本。
- 优化查询计划:数据库系统可以根据联合索引的结构,选择更优的查询计划,提高查询效率。
最左前缀原则
联合索引的使用需要遵循最左前缀原则,即查询条件必须从索引的最左列开始匹配,否则索引可能无法被使用。具体来说:
-
完全匹配:查询条件包含所有索引列,且顺序一致。
WHERE A = ? AND B = ? AND C = ?
-
最左列匹配:查询条件从最左列开始,可以包含部分列。
WHERE A = ? WHERE A = ? AND B = ? WHERE A = ? AND B = ? AND C = ?
-
范围查询:最左列进行范围查询,后续列可以是等值查询。
WHERE A > ? AND B = ? WHERE A BETWEEN ? AND ? AND B = ? AND C = ?
无法使用索引的查询模式
以下几种查询模式无法利用联合索引INDEX(A, B, C)
:
-
跳过最左列:查询条件不从最左列开始。
WHERE B = ? WHERE C = ? WHERE B = ? AND C = ?
-
中间列范围查询:最左列是等值查询,但中间列进行范围查询。
WHERE A = ? AND B > ? WHERE A = ? AND B > ? AND C = ?
示例
假设有一个名为employees
的表,包含以下列:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
hire_date DATE
);
CREATE INDEX idx_name_dept ON employees(last_name, first_name, department);
使用索引的查询
-
完全匹配:
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John' AND department = 'Sales';
-
最左列匹配:
SELECT * FROM employees WHERE last_name = 'Smith'; SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
-
范围查询:
SELECT * FROM employees WHERE last_name > 'Smith' AND first_name = 'John';
无法使用索引的查询
-
跳过最左列:
SELECT * FROM employees WHERE first_name = 'John'; SELECT * FROM employees WHERE department = 'Sales'; SELECT * FROM employees WHERE first_name = 'John' AND department = 'Sales';
-
中间列范围查询:
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name > 'John';
总结
联合索引是在多个列上创建的索引,用于加速涉及这些列的查询。联合索引的使用需要遵循最左前缀原则,确保查询条件从索引的最左列开始匹配,以最大限度地利用索引,提高查询性能。通过合理设计联合索引,可以减少索引数量,节省存储空间,优化查询计划,提高数据库的整体性能。