联合索引的使用

联合索引（Composite Index）是一个涵盖多个字段的索引，目的是同时优化多个列的查询。它比单列索引更高效，特别是在多条件查询中。以下是联合索引的详细使用方法和注意事项：

1. 联合索引的基本概念

• 联合索引是基于多个字段组合创建的索引，例如：

  CREATE INDEX idx_col1_col2 ON table_name(col1, col2);
  

  这个索引按照 col1 和 col2 的顺序进行排序。

• “最左前缀”原则： 联合索引的查询性能遵循“最左前缀”原则，即只有按照索引定义顺序访问字段，索引才能生效。

2. 创建联合索引

假设我们有一个 employees 表，包含以下字段：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

示例：创建联合索引

CREATE INDEX idx_department_hire_date ON employees(department_id, hire_date);

这个联合索引覆盖了 department_id 和 hire_date 两个字段。

3. 联合索引的使用场景

假设我们有一个联合索引 (department_id, hire_date)，以下是它的使用规则：

（1）完全匹配查询（索引生效）

SELECT * FROM employees WHERE department_id = 1 AND hire_date = '2024-12-19';

• 索引生效：查询同时使用了 department_id 和 hire_date，完全匹配索引的定义顺序。

（2）部分匹配查询（索引生效）

查询只包含索引的第一列：

SELECT * FROM employees WHERE department_id = 1;

• 索引生效：department_id 是索引的第一列，满足“最左前缀”原则。

（3）跳过第一列查询（索引不生效）

查询只包含索引的第二列：

SELECT * FROM employees WHERE hire_date = '2024-12-19';

• 索引不生效：因为 hire_date 不是索引的第一列，违反了“最左前缀”原则。

（4）排序优化

使用联合索引的字段进行排序：

SELECT * FROM employees WHERE department_id = 1 ORDER BY hire_date;

• 索引生效：ORDER BY hire_date 可以利用联合索引中的排序顺序。

（5）范围查询

范围查询在联合索引中也能生效，但范围条件之后的字段无法继续使用索引：

SELECT * FROM employees WHERE department_id = 1 AND hire_date > '2024-01-01

• 索引生效：范围条件 hire_date > '2024-01-01' 可以利用索引。
• 注意：如果在范围条件之后还涉及其他列的查询（如 salary），这些列将无法利用联合索引。

4. 联合索引 VS 多个单列索引

很多情况下，联合索引优于多个单列索引：

（1）查询条件涉及多列

例如：

SELECT * FROM employees WHERE department_id = 1 AND hire_date = '2024-12-19';

• 联合索引

  (department_id, hire_date)
  

  • 只需扫描一次索引即可完成查询。

• 单列索引

  (department_id)
  

  和

  (hire_date)
  

  • 数据库需要分别扫描两个索引，然后对结果集进行交集计算（成本更高）。

（2）避免重复的索引开销

创建联合索引 (col1, col2) 后，可以同时优化以下查询：

• WHERE col1 = ?
• WHERE col1 = ? AND col2 = ?

不需要再单独为 col1 创建单列索引。

5. 覆盖索引

覆盖索引指的是查询所需的所有字段都能从索引中获取，无需回表查询。例如：

CREATE INDEX idx_department_hire_date ON employees(department_id, hire_date);
SELECT department_id, hire_date FROM employees WHERE department_id = 1;

• 优势：因为查询的字段（department_id 和 hire_date）都包含在索引中，数据库可以直接从索引中返回结果，而无需回到数据表中读取数据，查询性能显著提升。

只要满足最左前缀法则索引就会生效。

6. 联合索引设计的注意事项

（1）字段的排列顺序

字段的顺序非常重要，应优先考虑以下规则：

1. 高频使用的字段放在前面：

   • 如果 department_id 比 hire_date 更常用于查询条件，应该将 department_id 放在索引的第一列。

   • 例如：

     CREATE INDEX idx_department_hire_date ON employees(department_id, hire_date);
     

2. 选择性高的字段放在前面：

   • “选择性”指的是字段的唯一值数量占总记录数的比例。例如，department_id 的值可能只有几个，而 hire_date 的值可能有成千上万种。
   • 如果字段的选择性低（如 gender），索引的效果会较差，因此应该放在后面。

（2）避免冗余索引

如果已经有联合索引 (department_id, hire_date)，就不需要单独再为 department_id 创建索引，因为联合索引已经可以支持 WHERE department_id = ? 的查询。

（3）考虑覆盖索引

如果查询经常访问某些固定字段，可以将这些字段也纳入联合索引，从而避免回表。例如：

CREATE INDEX idx_department_hire_date_salary ON employees(department_id, hire_date, salary);

这样可以优化以下查询：

SELECT department_id, hire_date, salary FROM employees WHERE department_id = 1 AND hire_date = '2024-12-19';

7. 联合索引常见的使用误区

（1）索引顺序设计不当

例如，索引 (hire_date, department_id) 无法加速查询：

SELECT * FROM employees WHERE department_id = 1 AND hire_date = '2024-12-19';

因为查询条件的顺序不符合索引定义的顺序。

（2）过多的联合索引

过多的联合索引会增加写操作（INSERT、UPDATE、DELETE）的成本，应根据实际查询需求合理设计。

总结

1. 查询优化：联合索引能同时优化多列查询，性能优于多个单列索引。
2. 最左前缀原则：联合索引只能在按照定义顺序访问字段时生效。
3. 合理设计：
   • 优先将高频查询和选择性高的字段放在索引的前列。
   • 避免不必要的冗余索引。
4. 覆盖索引：尽量设计联合索引覆盖查询所需的字段，避免回表操作。