MySQL联合索引

联合索引是指多个列组合创建的索引，适用于多列查询条件。合理适用联合索引可以显著提升查询性能

一、联合索引的使用

创建联合索引

CREATE INDEX idx_name ON table_name (col1, col2, col3);

• idx_name是索引名称

• col1，col2，col3是联合索引的列，顺序很重要

适用场景

• 多列查询条件

• 排序或分组操作

• 覆盖索引（索引包含查询所需的所有列）

二、联合索引的原理

索引结构

• 联合索引按列顺序构建B+树

• 先按col1排序，col1相同再按col2排序，以此类推

最左前缀原则

• 查询条件必须从联合索引的最左列开始，否则索引可能失效

• 例如，索引（col1，col2，col3）

  • 有效查询：WHERE col1 = 'value'、WHERE col1 = 'value' AND col2 = 'value'。

  • 无效查询：WHERE col2 = 'value'（未使用 col1）。

索引覆盖

• 如果查询列都在联合索引中，MySQL可以直接从索引中获取数据，无需回表

三、联合索引的优化

1、遵循最左前缀原则

• 确保查询条件从联合索引的最左列开始

• 例如，索引（col1，col2，col3）

  • 有效：WHERE col1 = 'value' AND col2 = 'value'。

  • 无效：WHERE col2 = 'value'。

2、避免冗余索引

• 如果已有（col1、col2），再创建（col1）就是冗余的，因为前者已覆盖后者

3、选择性高的列放在前面

• 选择性高的列（唯一值多）放在联合索引的前面，能更快过滤数据

• 例如，（col1，col2）比（col2，col1）更高效，如果col1选择性更高

4、覆盖索引

• 尽量让查询列都在联合索引中，避免回表

• 例如：

CREATE INDEX idx_covering ON table_name (col1, col2, col3);
SELECT col1, col2 FROM table_name WHERE col1 = 'value';

5、排序和分组优化

• 联合索引可用于优化ORDER BY和GROUP BY

• 例如

CREATE INDEX idx_order ON table_name (col1, col2);
SELECT * FROM table_name ORDER BY col1, col2;

6、避免索引列上适用函数或计算

• 在索引列上适用函数或计算会导致索引失效

• 例如

-- 不推荐
SELECT * FROM table_name WHERE YEAR(col1) = 2023;
​
-- 推荐
SELECT * FROM table_name WHERE col1 >= '2023-01-01' AND col1 < '2024-01-01';

7、适用EXPLAIN分析查询

• 使用EXPLAIN查看查询是否使用了联合索引

• 例如

EXPLAIN SELECT * FROM table_name WHERE col1 = 'value' AND col2 = 'value';

检查key字段是否使用了联合索引

8、避免过度索引

• 过度的索引会增加写操作的开销（如INSERT、UPDATE、DELETE）

• 只为高频查询创建联合索引

四、联合索引的示例

示例1：基本查询

CREATE INDEX idx_name ON users (last_name, first_name);
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

• 索引（last_name,first_name）有效

示例2：排序优化

CREATE INDEX idx_order ON orders (user_id, order_date);
SELECT * FROM orders WHERE user_id = 1 ORDER BY order_date DESC;

• 索引（user_id,order_date）支持查询和排序

示例3：覆盖索引

CREATE INDEX idx_covering ON products (category_id, price);
SELECT category_id, price FROM products WHERE category_id = 5;

• 查询列都在索引中，无需回表

五、总结

• 联合索引适用于多列查询、排序和分组操作。

• 最左前缀原则是关键，查询条件必须从索引的最左列开始。

• 优化建议：

  • 选择性高的列放在前面。

  • 避免冗余索引。

  • 使用覆盖索引减少回表。

  • 使用 EXPLAIN 分析查询计划。