【技术干货】MySQL联合索引底层原理 | 最左前缀原则详解

用户8491371754716
0 阅读8分钟

目录

一、前言：为什么你的SQL不走索引？

大家好，今天来聊一个面试高频问题：联合索引的底层实现原理。

很多同学遇到过这种情况：明明建了索引，SQL却不走索引；或者建立了idx(a,b,c)索引，查询WHERE a=1 AND b=2能用索引，但WHERE b=2就不行了。

要搞清楚这个问题，我们需要深入到B+Tree的底层结构，理解联合索引是如何存储和检索数据的。

二、联合索引到底是什么？

2.1 定义

联合索引：指在多个列上同时创建的索引，本质上是对这些列的组合值建立索引。

-- 创建联合索引
CREATE INDEX idx_name_age_dept ON employee(name, age, dept_id);

这相当于对 (name, age, dept_id) 这个组合值建立B+Tree索引。

2.2 与单列索引的本质区别

特性单列索引联合索引
索引数量每个字段独立创建多字段组合成一个
存储方式单独的B+Tree拼接key的B+Tree
排序依据单列值多列值按顺序拼接

核心区别：联合索引的key是由多个列的值拼接而成的，排序时按照创建顺序依次比较。

索引 key 构成：key = name + age + dept_id
例如：('张三', 25, 10) 存储为 key = '张三_25_10'

三、B+Tree与联合索引的深度原理

3.1 InnoDB B+Tree结构

                    [非叶子节点层]
                    ┌─────────────┐
                    │  name='D'   │
                    │  name='L'   │
                    │  name='W'   │
                    └─────────────┘
                    /      |      \
        ┌─────────┐   ┌─────────┐   ┌─────────┐
        │ D-H     │   │ L-Q     │   │ W-Z     │
        │ 索引页  │   │ 索引页  │   │ 索引页  │
        └─────────┘   └─────────┘   └─────────┘
              ↓            ↓            ↓
        ┌─────────────────────────────────────┐
        │              叶子节点（双向链表）      │
        │  (name,age,dept) → (name,age,dept) │
        │  ...            →            ...   │
        └─────────────────────────────────────┘

关键特性：

  • 非叶子节点：只存储索引列，不存储完整数据
  • 叶子节点：存储完整索引数据和指向主键的指针
  • 双向链表：叶子节点间用双向链表连接，支持高效范围查询

3.2 联合索引的排序逻辑

联合索引 (name, age, dept_id) 的排序规则：

  1. 先按 name 排序
  2. name 相同的情况下，按 age 排序
  3. name 和 age 都相同的情况下，按 dept_id 排序

示例数据排序：

nameagedept_id索引key
张三2010'张三_20_10'
张三255'张三_25_5'
张三2510'张三_25_10'
李四228'李四_22_8'
王五3015'王五_30_15'

注意：'张三_25_5' 排在 '张三_25_10' 前面，因为 dept_id=5 < 10

四、最左前缀原则详解

4.1 原则定义

联合索引要生效，必须从最左边的列开始使用，并且不能跳过中间列。

4.2 实战验证

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    dept_id INT,
    INDEX idx_name_age_dept (name, age, dept_id)
);

-- ✅ 情况1：从最左列开始，使用索引
SELECT * FROM employee WHERE name = '张三';
SELECT * FROM employee WHERE name = '张三' AND age = 25;
SELECT * FROM employee WHERE name = '张三' AND age > 24 AND dept_id = 10;

-- ❌ 情况2：跳过最左列，不使用索引
SELECT * FROM employee WHERE age = 25;
SELECT * FROM employee WHERE dept_id = 10;

-- ⚠️ 情况3：跳过中间列，只使用部分索引
SELECT * FROM employee WHERE name = '张三' AND dept_id = 10;
-- 结果：只使用了 name 列的索引，age 和 dept_id 无法利用

4.3 执行计划分析

EXPLAIN SELECT * FROM employee WHERE name = '张三' AND age = 25;

-- 关键字段解读
+----+-------------+----------+------------+-------+------------------------+
| id | select_type| type     | key        | rows  | Extra                  |
+----+-------------+----------+------------+-------+------------------------+
| 1  | SIMPLE     | ref      | idx_name.. | 5     | Using index condition  |
+----+-------------+----------+------------+-------+------------------------+

-- 字段含义
key = idx_name_age_dept  -- 实际使用的索引
type = ref               -- 索引等值查询，性能优秀
rows = 5                 -- 预计扫描5行
Extra = Using index condition  -- 索引条件下推

4.4 范围查询导致索引断链

-- age 是范围查询，导致其后的 dept_id 无法使用索引
EXPLAIN SELECT * FROM employee 
WHERE name = '张三' AND age > 25 AND dept_id = 10;

-- 结果：name 使用索引，age 使用索引（范围），dept_id 不使用

优化建议：范围查询列尽量放在联合索引的最后面。

五、索引下推优化（ICP）

5.1 什么是ICP？

索引下推（Index Condition Pushdown）：MySQL 5.6+引入的优化，在存储引擎层直接过滤数据，减少回表次数。

5.2 ICP vs 非ICP

SELECT * FROM employee WHERE name = '张三' AND age > 24 AND dept_id = 10;

关闭ICP的执行流程：

1. 存储引擎：根据 name='张三' 筛选，找到所有匹配行
2. 回表：每行都回表获取完整数据
3. Server层：过滤 age > 24 AND dept_id = 10

开启ICP的执行流程（默认）：

1. 存储引擎：根据 name='张三' AND age > 24 筛选
2. 回表：只对 age > 24 的行回表
3. Server层：过滤 dept_id = 10

性能差异：ICP显著减少了不必要的回表操作，特别是当 age > 24 条件能过滤掉大量数据时。

5.3 ICP适用场景

场景ICP效果
联合索引中间列为范围查询✅ 有效减少回表
索引覆盖查询❌ 无需回表，ICP无效果
存储引擎层可下推的条件✅ 等值、范围、IN列表

六、实战代码演示

6.1 创建测试表

CREATE TABLE `orders` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `order_no` VARCHAR(32) NOT NULL,
    `user_id` BIGINT NOT NULL,
    `status` TINYINT DEFAULT 0,
    `amount` DECIMAL(10,2),
    `create_time` DATETIME,
    INDEX `idx_user_status_time` (`user_id`, `status`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6.2 索引使用分析

-- ✅ 使用完整索引
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 1 AND create_time > '2024-01-01';

-- ✅ 使用前两列索引
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 1;

-- ⚠️ 只使用第一列索引
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND create_time > '2024-01-01';
-- 结果：只使用 user_id 索引，status 和 create_time 无法利用

6.3 覆盖索引优化

-- ❌ 需要回表：gender 不在索引中
SELECT user_id, status, create_time, gender 
FROM orders WHERE user_id = 1001;

-- ✅ 覆盖索引：无需回表
CREATE INDEX idx_covering ON orders(user_id, status, create_time);
SELECT user_id, status, create_time FROM orders WHERE user_id = 1001;

6.4 深度分页优化

-- ❌ 低效：OFFSET大时性能极差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ✅ 优化：书签式分页，利用主键索引
SELECT * FROM orders 
WHERE id > 1000000 ORDER BY id LIMIT 10;

-- ✅ 优化：子查询延迟关联
SELECT t.* FROM orders t
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) AS tmp
ON t.id = tmp.id;

七、常见踩坑与优化建议

7.1 索引失效的常见场景

误区说明正确做法
跳过最左列WHERE b = 2从最左列开始使用
OR连接WHERE a = 1 OR b = 2改用 UNION 或 IN
函数运算WHERE YEAR(create_time) = 2024改写为范围查询
类型转换WHERE name = 123 (name是VARCHAR)使用正确类型
LIKE前缀通配WHERE name LIKE '%三'避免前缀通配符

7.2 联合索引设计原则

1. 区分度高的列放前面

-- ✅ 优化：区分度高的 user_id 在前
INDEX idx_1 (user_id, status, create_time)

-- ❌ 低效：区分度低的 status 在前
INDEX idx_2 (status, user_id, create_time)

2. 范围查询列放最后

-- ✅ 优化：范围查询列放最后
INDEX idx_range (user_id, status, create_time)

-- ❌ 低效：范围查询列在中间导致后续列无法使用
INDEX idx_bad (user_id, create_time, status)

3. 覆盖索引减少回表

-- 频繁查询字段
SELECT user_id, status, create_time FROM orders WHERE user_id = ?

-- 建立覆盖索引
INDEX idx_covering (user_id, status, create_time)

7.3 索引维护成本

操作单列索引联合索引
INSERT维护1个索引维护1个索引（但key更大）
UPDATE维护1个索引维护1个索引
DELETE维护1个索引维护1个索引
空间占用分散集中

注意：联合索引不是越多越好，过多索引会增加写入开销和存储空间。

八、总结

核心要点

  1. 联合索引本质：将多列值拼接为key，建立B+Tree索引
  2. 排序规则：从左到右依次比较，前面的列值相同才比较后面的列
  3. 最左前缀原则：必须从最左列开始且不能跳过中间列
  4. 索引下推（ICP）：在存储引擎层过滤数据，减少回表
  5. 覆盖索引：查询字段全在索引中时，无需回表

面试高频回答

联合索引底层使用B+Tree结构存储，key由多个列的值按顺序拼接而成。查询必须从最左列开始且不跳过中间列才能使用索引。索引按创建顺序从左到右排序，前面的列值相同才比较后面的列。此外，MySQL 5.6+的索引下推优化会在存储引擎层直接过滤数据，减少回表次数。

avatar
文章
阅读
粉丝