MySQL中的联合索引、覆盖索引及最左匹配原则详解

在 MySQL 中，联合索引、覆盖索引和最左匹配原则是优化数据库查询性能的重要概念，下面详细介绍。

联合索引

定义

联合索引，也称为复合索引，是指在表的多个列上创建的一个索引。它允许数据库根据多个列的组合来进行快速查找，而不是仅基于单个列。例如，在 users 表的 last_name 和 first_name 列上创建联合索引：

 

CREATE INDEX idx_last_first_name ON users (last_name, first_name);

优点

• 提高查询效率：当查询条件涉及多个列时，联合索引可以减少磁盘 I/O 操作，提高查询速度。
• 减少索引占用空间：相比于为每个列单独创建索引，联合索引可以节省磁盘空间。

缺点

• 维护成本高：每次对表中的数据进行插入、更新或删除操作时，都需要更新联合索引，增加了维护成本。
• 适用范围有限：联合索引的使用需要遵循最左匹配原则，否则可能无法发挥其作用。

覆盖索引

定义

覆盖索引是指查询的列刚好是索引的一部分，数据库可以直接从索引中获取所需的数据，而不需要回表查询。回表查询是指先通过索引找到记录的主键，再根据主键到数据表中查找所需的其他列数据。

示例

假设在 orders 表上创建了一个联合索引 (order_date, total_amount)，如果查询语句如下：

 

SELECT order_date, total_amount FROM orders WHERE order_date > '2024-01-01';

由于查询的列 order_date 和 total_amount 都包含在索引中，数据库可以直接从索引中获取这些数据，而不需要回表查询，从而提高了查询性能。

优点

• 减少回表查询：避免了从索引到数据表的二次查找，减少了磁盘 I/O 操作，提高了查询速度。
• 提高缓存命中率：索引通常比数据表小，更容易被缓存，从而提高了缓存命中率。

最左匹配原则

定义

最左匹配原则是指在使用联合索引时，MySQL 会从联合索引的最左边的列开始，依次向右匹配查询条件，直到遇到范围查询（如 >、<、BETWEEN 等）或不满足条件的列为止。

示例

假设在 users 表上创建了一个联合索引 (last_name, first_name, age)：

• 完全匹配：

SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;

这个查询可以使用联合索引，因为查询条件从最左边的列开始，依次向右匹配。

• 部分匹配：

SELECT * FROM users WHERE last_name = 'Smith';

这个查询也可以使用联合索引，因为查询条件从最左边的列开始匹配。

• 不匹配：

SELECT * FROM users WHERE first_name = 'John' AND age = 30;

这个查询不能使用联合索引，因为查询条件没有从最左边的列开始匹配。

• 范围查询：

 

SELECT * FROM users WHERE last_name = 'Smith' AND first_name > 'A' AND age = 30;

这个查询可以使用联合索引，但只使用了 last_name 和 first_name 列，因为 first_name 列使用了范围查询，MySQL 会在遇到范围查询时停止匹配。

注意事项

• 在创建联合索引时，要根据查询的需求和频率，将最常使用的列放在最左边。
• 范围查询会影响最左匹配原则的使用，尽量避免在联合索引的中间位置使用范围查询。

综上所述，联合索引、覆盖索引和最左匹配原则是 MySQL 中重要的索引优化技术，合理使用这些技术可以显著提高数据库的查询性能。