联合索引（Composite Index）-SQL

联合索引（Composite Index），也称为复合索引或多列索引，是指在多个列上创建的索引。与单列索引不同，联合索引可以包含两个或更多的列，用于加速涉及这些列的查询。联合索引的设计和使用需要遵循最左前缀原则，以确保索引能够被有效利用。

概念解释

联合索引包含多个列，其索引键是由这些列的值组合而成的。例如，假设有一个联合索引包含列(A, B, C)，即INDEX(A, B, C)。联合索引的键值是按照列的顺序组合而成的，如(A1, B1, C1)、(A2, B2, C2)等。

作用

联合索引的主要作用是：

1. 加速查询：对于涉及多个列的查询，联合索引可以显著提高查询性能。
2. 减少索引数量：通过创建联合索引，可以减少数据库中的索引数量，从而节省存储空间和维护成本。
3. 优化查询计划：数据库系统可以根据联合索引的结构，选择更优的查询计划，提高查询效率。

最左前缀原则

联合索引的使用需要遵循最左前缀原则，即查询条件必须从索引的最左列开始匹配，否则索引可能无法被使用。具体来说：

1. 完全匹配：查询条件包含所有索引列，且顺序一致。

   WHERE A = ? AND B = ? AND C = ?
   

2. 最左列匹配：查询条件从最左列开始，可以包含部分列。

   WHERE A = ?
   WHERE A = ? AND B = ?
   WHERE A = ? AND B = ? AND C = ?
   

3. 范围查询：最左列进行范围查询，后续列可以是等值查询。

   WHERE A > ? AND B = ?
   WHERE A BETWEEN ? AND ? AND B = ? AND C = ?
   

无法使用索引的查询模式

以下几种查询模式无法利用联合索引INDEX(A, B, C)：

1. 跳过最左列：查询条件不从最左列开始。

   WHERE B = ?
   WHERE C = ?
   WHERE B = ? AND C = ?
   

2. 中间列范围查询：最左列是等值查询，但中间列进行范围查询。

   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);

使用索引的查询

1. 完全匹配：

   SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John' AND department = 'Sales';
   

2. 最左列匹配：

   SELECT * FROM employees WHERE last_name = 'Smith';
   SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
   

3. 范围查询：

   SELECT * FROM employees WHERE last_name > 'Smith' AND first_name = 'John';
   

无法使用索引的查询

1. 跳过最左列：

   SELECT * FROM employees WHERE first_name = 'John';
   SELECT * FROM employees WHERE department = 'Sales';
   SELECT * FROM employees WHERE first_name = 'John' AND department = 'Sales';
   

2. 中间列范围查询：

   SELECT * FROM employees WHERE last_name = 'Smith' AND first_name > 'John';
   

总结

联合索引是在多个列上创建的索引，用于加速涉及这些列的查询。联合索引的使用需要遵循最左前缀原则，确保查询条件从索引的最左列开始匹配，以最大限度地利用索引，提高查询性能。通过合理设计联合索引，可以减少索引数量，节省存储空间，优化查询计划，提高数据库的整体性能。