25. 什么是复合索引（Composite Index）？如何在MySQL中创建复合索引？

PangHu大约 3 分钟

复合索引（Composite Index），也称为组合索引，是指在数据库表中基于多个列创建的索引。与单列索引不同，复合索引能够优化对这些列组合的查询性能。复合索引可以在查询中同时使用多个列来加速数据检索，是处理复杂查询的一种有效手段。

复合索引遵循“最左前缀”原则，这意味着当查询条件使用复合索引中的前导列（或前导列的组合）时，MySQL可以有效利用该索引来优化查询。如果查询只使用复合索引中的部分后续列，则不能完全利用索引的全部优势。

复合索引的工作原理

假设我们有一个包含三个列（A、B、C）的复合索引 (A, B, C)，以下是如何利用该复合索引的情况：

索引可以用于：
- 查询中只包含列 A：如 WHERE A = ?
- 查询中包含列 A 和 B：如 WHERE A = ? AND B = ?
- 查询中包含列 A、B 和 C：如 WHERE A = ? AND B = ? AND C = ?
索引无法用于：
- 查询中只包含列 B 或 C：如 WHERE B = ? 或 WHERE C = ?
- 查询中包含列 B 和 C，但不包含 A：如 WHERE B = ? AND C = ?

这表明，复合索引在使用时必须从最左列开始，依次匹配索引中的列，才能被MySQL有效地利用。

如何在MySQL中创建复合索引？

在MySQL中，使用 CREATE INDEX 或在 CREATE TABLE 和 ALTER TABLE 语句中可以创建复合索引。

1. 使用 `CREATE INDEX` 创建复合索引

CREATE INDEX index_name ON table_name (column1, column2, column3);

例如：

CREATE INDEX idx_employee_name_dept ON employees (last_name, first_name, department_id);

这个语句在 employees 表中基于 last_name、first_name 和 department_id 创建了一个复合索引 idx_employee_name_dept。

2. 在 `CREATE TABLE` 中创建复合索引

在创建表时可以直接定义复合索引：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    department_id INT,
    INDEX idx_name_dept (last_name, first_name, department_id)
);

3. 使用 `ALTER TABLE` 创建复合索引

如果表已经存在，可以使用 ALTER TABLE 添加复合索引：

ALTER TABLE employees
ADD INDEX idx_employee_name_dept (last_name, first_name, department_id);

复合索引的最佳实践

选择性高的列放在前面：在复合索引中，将选择性高的列（即列中数据的唯一性较强）放在索引的最前面，这样可以更好地优化查询。
根据查询模式设计索引：创建复合索引时，应根据实际查询模式来设计。要考虑哪些列经常一起出现在 WHERE 子句中，并按“最左前缀”原则来确定列的顺序。
避免冗余索引：如果已经存在 (A, B) 的复合索引，则没有必要再创建单独的 (A) 或 (B) 索引，除非 B 在查询中单独使用非常频繁。

总结

复合索引是优化多列查询性能的有效工具。在创建复合索引时，遵循“最左前缀”原则，并根据查询需求合理设计列的顺序，能够显著提高查询效率。MySQL 提供了灵活的方式来创建和管理复合索引，通过这些手段，可以有效提升数据库的性能。