MySQL到底为什么在范围查询后用复合索引会失效？

问题：为什么范围查询后复合索引会失效？

一句话总结：
就像书架按“科目+书名”排序，查“数学书里书名以A开头的”，只能用科目分类，书名分类用不上。
复合索引在范围查询后无法继续使用后续字段，因为索引的有序性被打破了。

一、核心概念：复合索引的“排序规则”

1. 复合索引的结构

// 用PHP注释类比复合索引
/*
假设有复合索引 (subject, book_name)：
相当于书架先按科目分类，再按书名排序：

数学区 → A~Z书名
语文区 → A~Z书名
英语区 → A~Z书名

索引存储顺序：
[数学, Algebra]
[数学, Calculus]
[语文, 安徒生童话]
[语文, 本草纲目]
[英语, Apple]
[英语, Banana]
*/

2. 范围查询对索引的影响

/*
查询：WHERE subject = '数学' AND book_name > 'C'
1. 先定位到数学区（使用subject字段索引）
2. 在数学区内查找book_name > 'C'的书
   → 此时数学区内的书仍按书名有序排列 → 可继续用索引

但如果查询：WHERE subject > '数学' AND book_name = '安徒生童话'
1. 先定位subject > '数学'的区域（语文、英语区）
2. 这些区域的book_name是混合的（语文区的A~Z和英语区的A~Z）
   → 无法按book_name有序查找 → book_name索引失效
*/

二、底层原理：B+树的“有序性”

1. 复合索引的B+树结构

// 伪代码展示复合索引的B+树
/*
复合索引 (col1, col2) 的B+树：
- 先按col1排序
- col1相同的情况下按col2排序

叶子节点存储：
[col1=1, col2=10] → 行位置0x100
[col1=1, col2=20] → 行位置0x101
[col1=2, col2=5]  → 行位置0x102
[col1=2, col2=15] → 行位置0x103

索引顺序：1-10 → 1-20 → 2-5 → 2-15
*/

2. 范围查询如何打破有序性

/*
查询：WHERE col1 > 1 AND col2 = 15
1. 从B+树定位col1 > 1的位置 → 找到[col1=2, col2=5]
2. 继续扫描后续节点：
   [col1=2, col2=5]  → 不符合col2=15
   [col1=2, col2=15] → 符合条件
   [col1=3, col2=20] → col1=3，但col2=20不符合

此时：
- 虽然col2在col1相同的情况下有序（如col1=2时，col2是5→15）
- 但col1范围查询后，整体数据变成：2-5 → 2-15 → 3-20 → 4-10...
  col2不再全局有序 → 无法用col2索引快速定位
*/

三、复合索引的使用规则

1. 最左前缀匹配原则

-- 创建复合索引 (a, b, c)
CREATE INDEX idx_abc ON test (a, b, c);

-- 有效查询（符合最左前缀）
WHERE a = 1 AND b = 2 AND c = 3  -- 全使用索引
WHERE a = 1 AND b = 2           -- 使用a和b
WHERE a = 1                     -- 只使用a

-- 无效查询（跳过前缀）
WHERE b = 2                     -- 无法使用索引
WHERE c = 3                     -- 无法使用索引

2. 范围查询后的字段失效

-- 复合索引 (a, b, c)
CREATE INDEX idx_abc ON test (a, b, c);

-- 查询1：范围查询在中间字段
WHERE a = 1 AND b > 10 AND c = 5  -- a和b使用索引，c失效

-- 查询2：范围查询在第一个字段
WHERE a > 1 AND b = 2 AND c = 3  -- 只有a使用索引，b和c失效

四、使用场景与优化

1. 正确使用复合索引的场景

-- 创建复合索引 (country, city, street)
CREATE INDEX idx_address ON users (country, city, street);

-- 有效查询（范围查询在最后字段）
WHERE country = '中国' AND city = '北京' AND street > '中关村'
-- 索引使用：country → city → street（范围查询在末尾，不影响前面字段）

-- 优化：调整索引顺序，让范围查询在最后
-- 原索引 (a, b, c) → 改为 (a, c, b)，如果b经常用于范围查询

2. 范围查询后的字段无法优化的场景

-- 复合索引 (a, b, c)
CREATE INDEX idx_abc ON test (a, b, c);

-- 查询：b是范围，c是等值
WHERE a = 1 AND b > 10 AND c = 5  -- c无法使用索引

-- 优化方法：
-- 1. 创建单独的c字段索引
-- 2. 重构查询逻辑（如果可能）

五、避坑指南：常见的复合索引误区

1. 误区1：认为复合索引可以任意顺序使用

-- 复合索引 (a, b, c)
CREATE INDEX idx_abc ON test (a, b, c);

-- 错误认为可以跳过a
WHERE b = 2 AND c = 3  -- 无法使用索引

-- 正确：必须从最左字段开始
WHERE a = 1 AND b = 2 AND c = 3  -- 有效使用索引

2. 误区2：在范围查询后期望继续使用索引

-- 复合索引 (date, category, amount)
CREATE INDEX idx_date_cat_amt ON orders (date, category, amount);

-- 查询：按日期范围过滤，再按类别和金额筛选
WHERE date BETWEEN '2023-01-01' AND '2023-01-31' 
  AND category = '食品' 
  AND amount > 100;

-- 实际执行：只有date字段使用索引，category和amount无法使用

3. 误区3：过度创建复合索引

-- 反面示例：创建多个重复索引
CREATE INDEX idx_a ON test (a);
CREATE INDEX idx_ab ON test (a, b);
CREATE INDEX idx_abc ON test (a, b, c);

-- 正确做法：复合索引已包含前缀字段
-- 只需创建idx_abc，可满足a、a+b、a+b+c的查询

总结：复合索引的“有序性依赖”

核心原理：

• 复合索引的有序性是“逐层依赖”的，前一个字段的范围查询会打破后续字段的全局有序性。
• 最左前缀匹配原则是复合索引的基础，范围查询会导致其右侧的字段无法使用索引。

简单比喻：

• 复合索引就像多层书架，先按科目分类，再按书名排序。
• 如果科目范围太广（如“所有理科书”），书名的排序就用不上了！📚

下次设计复合索引时，记得让范围查询尽量出现在索引的最后字段哦！ 😊