MySQL联合索引使用指南

核心概念：

• 定义： 一个索引包含两个或更多列。

• 作用： 优化涉及多个列的查询条件 (WHERE)、连接条件 (JOIN)、排序 (ORDER BY) 和分组 (GROUP BY) 的性能。

• 数据结构： 与单列索引一样，通常是 B+树。关键区别在于索引键是由多个列的值按定义的顺序拼接而成的一个复合键。

工作原理：

1. 最左前缀原则：

1. 这是理解和使用联合索引最重要的原则。

2. 含义： MySQL 使用联合索引时，只能从索引定义的最左边列开始，并且连续地向右使用索引列。不能跳过左边的列直接使用右边的列。

3. 例子： 假设你有一个联合索引 idx_name_age (last_name, first_name, age)。

1. 有效使用（能利用索引）：

1. WHERE last_name = 'Smith' (使用了最左列 last_name)

2. WHERE last_name = 'Smith' AND first_name = 'John' (使用了 last_name + first_name)

3. WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30 (使用了全部三列)

4. WHERE last_name = 'Smith' AND age = 30 (使用了 last_name，但 age 的使用受限 - 见下一点)

5. ORDER BY last_name, first_name (排序可以利用索引)

6. WHERE last_name LIKE 'Sm%' (前缀匹配可以利用 last_name 的索引)

2. 无效或部分无效使用（不能充分利用索引）：

1. WHERE first_name = 'John' (跳过了最左列 last_name) -> 无法使用索引

2. WHERE age = 30 (跳过了最左列 last_name 和 first_name) -> 无法使用索引

3. WHERE first_name = 'John' AND age = 30 (跳过了最左列 last_name) -> 无法使用索引

4. WHERE last_name = 'Smith' AND age = 30 (使用了 last_name，但跳过了 first_name):

1. last_name 列可以使用索引进行快速查找。

2. 对于 age = 30 这个条件，无法利用索引中 age 的排序性进行高效过滤，因为 age 在索引中是按 (last_name, first_name, age) 排序的。当 last_name='Smith' 时，first_name 不同的行对应的 age 值是乱序的。MySQL 只能扫描所有 last_name='Smith' 的行（这部分利用索引定位到了），然后在这些行中逐行检查 age=30。这比同时利用 first_name 定位后再用 age 过滤效率低。

5. ORDER BY first_name (排序未从最左列 last_name 开始) -> 无法利用索引排序

6. ORDER BY last_name, age (跳过了中间的 first_name) -> 只能部分利用索引 (last_name 部分)，age 的排序无法高效利用索引。

2. 排序优化：

1. 联合索引可以显著优化包含 ORDER BY 子句的查询。

2. 索引顺序必须与 ORDER BY 子句中列的顺序一致，并且所有列的排序方向（ASC/DESC）也要一致（或MySQL能优化成一致），才能避免昂贵的文件排序 (filesort)。

3. 例子：

1. 索引 (a, b, c) 可以优化：

1. ORDER BY a

2. ORDER BY a, b

3. ORDER BY a, b, c

4. ORDER BY a DESC, b DESC, c DESC (方向一致)

5. WHERE a = const ORDER BY b, c (部分条件+排序

2. 索引 (a, b, c) 无法优化：

1. ORDER BY b (未从 a 开始)

2. ORDER BY a, c (跳过了 b)

3. ORDER BY a ASC, b DESC (排序方向不一致，除非索引定义为 (a ASC, b DESC))

3. 覆盖索引：

1. 如果一个查询只需要从索引中获取数据，而不需要回表（根据索引指针去主键索引或数据文件中读取完整行），那么这个索引就称为“覆盖索引”。

2. 联合索引由于包含了多个列，更容易成为覆盖索引，特别是对于只需要查询索引列的场景。

3. 优势： 速度极快，因为避免了访问实际数据行（减少 I/O）。

4. 例子： 有索引 idx_customer (country, city)

1. SELECT country, city FROM customers WHERE country = 'USA'; -> 索引 idx_customer 包含了查询需要的所有列 (country, city)，是覆盖索引，性能极佳。

sql语法：

CREATE INDEX index_name ON table_name (column1, column2, column3, ...);
ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3, ...);

设计联合索引的考虑因素：

1. 查询模式： 分析你的应用程序中最频繁、最慢的查询。它们的 WHERE、JOIN、ORDER BY、GROUP BY 子句中使用了哪些列？这些列通常就是联合索引的候选列。

2. 列选择性：

1. 选择性高的列（列值几乎唯一，如 user_id、email）放在索引前面通常过滤效果更好。

2. 选择性低的列（列值重复度高，如 gender、status）放在前面可能过滤掉的行数较少。

3. 权衡： 有时需要平衡选择性和查询频率。如果某个低选择性列在 WHERE 中几乎总是出现，而高选择性列不常出现，把低选择性列放前面也可能更优。需要结合具体场景分析。

3. 排序和分组： 如果查询经常需要按 (A, B) 排序，那么索引 (A, B) 就比 (B, A) 更能优化这个排序。

4. 覆盖索引： 如果可能，尽量让索引包含 SELECT 列表中需要的所有列（或最常查询的列），以实现覆盖索引查询的巨大性能提升。

5. 索引大小： 索引越多、越大，会占用更多磁盘空间和内存，也会降低 INSERT、UPDATE、DELETE 的速度（需要维护索引）。避免创建不必要的索引或包含过多列的巨型索引。

6. 最左前缀原则： 牢记这个原则！列的顺序直接影响哪些查询能使用该索引。

使用 EXPLAIN 命令查看 MySQL 的执行计划，重点关注：

• type: 显示访问类型（ref, range, index, const 等通常表示使用了索引）。

• key: 显示 MySQL 实际决定使用的索引。

• key_len: 显示使用的索引长度，可以判断使用了联合索引的几列。

• Extra: 注意 Using index（覆盖索引），Using filesort（需要额外排序），Using temporary（需要临时表）。