Files

90 lines
4.7 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
---
id: backend.mysql.index-explain
title: InnoDB 索引:B+ 树、聚簇索引、回表、最左前缀和 EXPLAIN
type: lesson
domain: backend
stack: [mysql]
tags: [索引, B+树, 聚簇索引, 回表, EXPLAIN]
level: basic
prerequisites: [backend.postgresql.schema-sql]
est_hours: 3
related: [backend.mysql.tx-locks-mvcc, backend.postgresql.index-tx-mvcc, backend.mysql.interview]
week: [2]
order: 1
---
# InnoDB 索引:B+ 树、聚簇索引、回表、最左前缀和 EXPLAIN
## 解释说明
- **B+ 树**:InnoDB 索引的数据结构。非叶子节点只存键,叶子节点存数据并用链表相连,所以树很矮(几千万行通常 3~4 层)、范围查询顺着叶子链表扫就行。
- **聚簇索引(主键索引)**:叶子节点存的是**整行数据**,表数据就是按主键组织的。所以主键最好是递增的整数(顺序插入,页分裂少),不要用随机 UUID 作主键。
- **二级索引(普通索引)**:叶子节点存“索引列 + 主键值”。通过二级索引查到主键后,再到聚簇索引里取整行,这一步叫**回表**。
- **覆盖索引**:查询需要的列都在二级索引里,就不需要回表,EXPLAIN 的 `Extra` 显示 `Using index`。
- **最左前缀**:联合索引 `(a, b, c)` 按 a、b、c 顺序排序。`WHERE a = ?`、`a = ? AND b = ?` 能用上;只有 `WHERE b = ?` 用不上。遇到范围条件(`a > ?`)后,后面的列通常无法继续用于定位。
- **索引下推(ICP)**:MySQL 5.6 起,联合索引中的后续列条件可以在存储引擎层先过滤,减少回表,`Extra` 显示 `Using index condition`。
**EXPLAIN 重点字段**
| 字段 | 看什么 |
| --- | --- |
| `type` | 访问方式,从好到差大致:`const` > `eq_ref` > `ref` > `range` > `index`(扫整个索引)> `ALL`(全表扫描) |
| `key` | 实际使用的索引;为 NULL 表示没用索引 |
| `rows` | 预估要扫描的行数 |
| `Extra` | `Using index`(覆盖索引)、`Using filesort`(额外排序)、`Using temporary`(临时表)要特别关注 |
MySQL 8.0.18+ 可以用 `EXPLAIN ANALYZE` 看实际执行耗时;MariaDB 对应的是 `ANALYZE SELECT ...`。
## 案例
订单表按“用户 + 状态”查最近订单:
- 只有主键时:`type = ALL`,全表扫描。
- 建 `(user_id, status, created_at)` 联合索引后:`type = ref`,并且按 `created_at` 排序不需要 filesort。
- 只查 `id, created_at` 时:变成覆盖索引,`Extra` 出现 `Using index`。
## 代码示例
```sql
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL,
amount DECIMAL(12, 2) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
INSERT INTO orders (user_id, status, amount, created_at)
SELECT seq % 1000, ELT(1 + seq % 3, 'pending', 'paid', 'done'), seq % 500, NOW() - INTERVAL seq MINUTE
FROM seq_1_to_50000; -- MariaDB 的序列表;MySQL 8 可以用递归 CTE 生成数据
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
-- type=ALL,Extra=Using where; Using filesort
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);
ANALYZE TABLE orders;
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
-- type=ref,key=idx_user_status_time,没有 filesort
EXPLAIN SELECT id, created_at FROM orders WHERE user_id = 42 AND status = 'paid';
-- Extra 含 Using index:覆盖索引(二级索引叶子里本来就有主键 id)
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- 不满足最左前缀:type=ALL
```
## 易错点
- **隐式类型转换**:`phone` 是 VARCHAR,写 `WHERE phone = 13800000000`(数字)会对列做转换,索引失效。参数类型要和列类型一致。
- **索引列上用函数**:`WHERE DATE(created_at) = '2026-10-01'` 用不上索引,改成范围条件。
- **`LIKE '%xx'`** 前面有通配符用不上索引;`LIKE 'xx%'` 可以。
- **`OR` 连接不同列**:可能导致全表扫描,可改为 `UNION ALL` 或分别建索引(index merge)。
- **区分度低的列单独建索引**(如性别、状态只有几个值):优化器往往不用它;放在联合索引里配合其他列使用。
- **UUID 作主键**:随机插入导致页分裂、索引膨胀;需要全局唯一 ID 时用雪花算法等趋势递增的 ID。
- **`SELECT *`**:失去覆盖索引的机会,也多传数据。
## 面试常问
见 [MySQL 面试题](interview.md):为什么用 B+ 树、聚簇索引和回表、最左前缀、索引失效场景、EXPLAIN 怎么看。