Mysql 从数据结构到执行计划

索引是 MySQL 查询性能的核心杠杆。理解索引在磁盘上如何存储、聚簇索引与二级索引的区别、多列索引的最左前缀规则，再结合 EXPLAIN 读懂执行计划，才能在开发中写出高效 SQL，也能在慢查询排查时快速定位问题。

示例表

后文统一使用下面这张 orders 表举例：

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT       NOT NULL,
    order_no    VARCHAR(32)  NOT NULL,
    status      TINYINT      NOT NULL DEFAULT 0,   -- 0待支付 1已支付 2已取消
    amount      DECIMAL(10,2) NOT NULL,
    city        VARCHAR(50),
    created_at  DATETIME     NOT NULL,
    updated_at  DATETIME     NOT NULL,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_status (user_id, status),
    KEY idx_city_created (city, created_at),
    KEY idx_status_amount (status, amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设表中有 100 万行数据， id 自增， user_id 分布较均匀， status 中 0 占 80%。

索引的存储结构

B+ 树

InnoDB 的索引底层都是 B+ 树 （不是 B 树）：

• 所有数据都存储在 叶子节点 ，叶子节点之间通过双向链表连接，适合范围扫描
• 非叶子节点只存索引键和子节点指针，一个节点通常对应一个 页（Page，默认 16KB）
• 树的高度通常只有 3~4 层：一层非叶子 + 两层叶子就能索引千万级数据

文件系统中的存储格式

InnoDB 不会为每个索引单独建一个文件， 所有索引（聚簇 + 二级）和数据行都存放在同一个表空间文件里 。

1. 表空间文件（.ibd）

   开启 innodb_file_per_table=ON （MySQL 8.0 默认）时，每张表对应一个独立文件：

   /var/lib/mysql/mydb/orders.ibd    ← orders 表的数据 + 全部索引
   

   文件	内容
   orders.ibd	聚簇索引 B+ 树 + 所有二级索引 B+ 树，共享同一表空间
   ibdata1	系统表空间（数据字典、undo、change buffer 等，非单表数据）
   undo_.ibu*	undo 日志（MySQL 8.0 独立 undo 表空间）
   #ib_16384_.ibd*	MySQL 8.0 部分系统表

   查看表空间与行格式：

   SELECT NAME, SPACE_TYPE, FILE_FORMAT
   FROM information_schema.INNODB_TABLESPACES
   WHERE NAME LIKE '%orders%';
   
   SHOW TABLE STATUS LIKE 'orders'\G
   -- Row_format: Dynamic（MySQL 8.0 默认）
   

   一个表的所有数据和索引确实都属于同一个 .ibd 文件，但 .ibd 内部不是一个连续的大数组，而是由很多页（Page）组成的。
   一个索引就是一棵 B+ 树，这些 B+ 树的节点分散存储在同一个 .ibd 的不同 Page 中。每个 Page 在 表空间文件中有固定的位置（偏移范围），可快速提取。
   

2. 页（Page）—— 磁盘 I/O 的最小单位

   表空间文件由固定大小的 页 顺序拼接而成，默认 16KB （ innodb_page_size ，安装后不可改）。

3. 二级索引页与聚簇索引页的差异

   页类型	非叶子节点内容	叶子节点内容
   聚簇索引（主键）	主键值 + 子页号	*完整行记录*（按主键排序）
   二级索引	索引键 + 子页号	*索引列值 + 主键*（按索引键排序）

   idx_user_status (user_id, status) 叶子页中的一条记录:
   
   [user_id=42][status=1][id=10086]
                                 ↑
                            主键，用于回表定位聚簇索引页
   
   回表时: 用 id=10086 在聚簇索引 B+ 树中再查一次，读到完整行。
   

   同一棵 B+ 树的叶子页通过 PAGE_NEXT / PAGE_PREV 指针组成双向链表，所以 WHERE id > 100 ORDER BY id 的范围扫描只需沿链表顺序读页，无需反复从根查找。

4. 从文件到查询的路径（串联）

   SELECT * FROM orders WHERE user_id = 42 AND status = 1;
   
   1. 优化器选择 idx_user_status
   2. 从该索引根页（某个 16KB FIL_PAGE_INDEX）开始向下查找
   3. 到达叶子页，在 User Records 链表中找到 (42, 1, id) 记录
   4. 对每个 id 回表：在聚簇索引 B+ 树叶子页读取完整行
   5. 若该行在 Buffer Pool 中已缓存，则跳过磁盘 I/O
   

   innodb_page_size 只能是 4K / 8K / 16K / 32K / 64K，且必须在初始化时设定。
   生产环境默认 16KB；更大的页适合顺序扫描，更小的页适合点查，但 16KB 是综合最优的通用选择。
   

聚簇索引（Clustered Index）

InnoDB 中， 表数据本身按主键顺序存储在一棵 B+ 树上 ，这棵树的叶子节点存放完整行记录，称为聚簇索引。

-- 主键查找：直接走聚簇索引，type = const
SELECT * FROM orders WHERE id = 10086;

EXPLAIN SELECT * FROM orders WHERE id = 10086;
-- type: const, key: PRIMARY, rows: 1

聚簇索引结构示意（简化）：

非叶子节点:  [10 | 50 | 100 | 200]
                ↓    ↓    ↓     ↓
叶子节点:  [1..9] [10..49] [50..99] [100..199] [200..]
           每片叶子存完整行: (id, user_id, order_no, status, ...)

二级索引（Secondary Index / 普通索引）

除聚簇索引外创建的索引都是二级索引（ UNIQUE KEY 、 KEY 、 INDEX 等）。

-- 二级索引 idx_user_status 的叶子节点存: (user_id, status, id)
SELECT * FROM orders WHERE user_id = 42 AND status = 1;

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 1;
-- type: ref, key: idx_user_status, key_len: 9, rows: ~少量
-- Extra: 可能含 Using index condition

查找过程：

1. 在 idx_user_status 的 B+ 树上定位 user_id=42, status=1
2. 叶子节点拿到主键 id 列表: [101, 205, 388, ...]
3. 对每个 id 回聚簇索引取完整行（若 SELECT * 则需要回表）

多列索引（联合索引 / Composite Index）

多列索引是将多个列按 定义顺序 组合成一棵 B+ 树，遵循 *最左前缀原则*。

KEY idx_user_status (user_id, status)
-- 等价于为以下前缀都建立了有序结构：
--   (user_id)
--   (user_id, status)
-- 但不为单独 status 建立有序结构

-- ✅ 能用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- key: idx_user_status, key_len: 8 (仅 user_id 部分)

-- ✅ 能用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- key: idx_user_status, key_len: 9 (user_id + status)

-- ❌ 不能用该索引（会全表扫描或换别的索引）
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- key: NULL 或 idx_status_amount（若优化器认为更优）
-- type: ALL 或 range

聚簇索引 vs 二级索引 vs 多列索引 对比

索引使用方式

何时建索引

-- 查看列区分度（Cardinality 越高越好）
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT city) / COUNT(*)   AS city_selectivity
FROM orders;
-- status 若只有 0/1/2 三值，区分度低；city 若上百个城市则适合索引

覆盖索引（Covering Index）

查询所需列全部在索引中，优化器直接从索引返回结果，*不回表* 。

-- idx_user_status 含 (user_id, status, id)，id 是主键自动附加在二级索引叶子中
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 42;
-- Extra: Using index  ← 覆盖索引，不回表

EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 42;
-- Extra: 无 Using index，需要回表取 amount

若该查询极频繁，可建覆盖索引：

ALTER TABLE orders ADD KEY idx_user_status_amount (user_id, status, amount);
-- 再查 user_id, status, amount 就会出现 Using index

索引列顺序设计

联合索引列顺序的一般原则：

1. 等值查询列放前面 ，范围查询列放后面

2. 区分度高的列放前面 （优化器更容易缩小扫描范围）

3. 若查询含 ORDER BY ，把排序列放在索引合适位置

   – 查询: WHERE status = 1 AND amount > 100 ORDER BY amount – idx_status_amount (status, amount) 是合理设计 EXPLAIN SELECT * FROM orders WHERE status = 1 AND amount > 100 ORDER BY amount; – type: range, key: idx_status_amount – Extra: Using index condition（可能）

   – 反例：若索引是 (amount, status) – status 在范围列 amount 之后，status 条件难以高效利用索引

导致索引失效的常见写法

1. 对索引列使用函数或运算

   -- ❌ 索引失效
   EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
   -- key: NULL, type: ALL
   
   -- ✅ 改写为范围查询
   EXPLAIN SELECT * FROM orders
   WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
   -- 可走 idx_city_created 或其他含 created_at 的索引
   

2. 隐式类型转换

   -- order_no 是 VARCHAR，传入数字会触发转换，索引失效
   EXPLAIN SELECT * FROM orders WHERE order_no = 20260101001;
   -- 可能 type: ALL
   
   -- ✅ 使用字符串
   EXPLAIN SELECT * FROM orders WHERE order_no = '20260101001';
   -- type: const, key: uk_order_no
   

3. 前导模糊匹配

   -- ❌ LIKE '%ABC' 无法用 B+ 树有序性
   EXPLAIN SELECT * FROM orders WHERE order_no LIKE '%ABC';
   
   -- ✅ 后导模糊可用索引
   EXPLAIN SELECT * FROM orders WHERE order_no LIKE 'ORD2026%';
   -- type: range, key: uk_order_no
   

4. OR 连接不同列

   -- 两个列各走各的索引，可能 Index Merge；也可能直接全表扫描
   EXPLAIN SELECT * FROM orders WHERE user_id = 1 OR status = 2;
   
   -- ✅ 若业务允许，改写为 UNION
   EXPLAIN
   SELECT * FROM orders WHERE user_id = 1
   UNION
   SELECT * FROM orders WHERE status = 2;
   

5. 不等于、NOT IN、IS NULL（视情况）

   -- != 和 <> 通常导致范围扫描或放弃索引（优化器根据选择性判断）
   EXPLAIN SELECT * FROM orders WHERE status != 0;
   
   -- IS NULL：MySQL 8.0 对索引列 IS NULL 可以走 ref/range（取决于版本和统计信息）
   EXPLAIN SELECT * FROM orders WHERE city IS NULL;
   

索引下推（ICP, Index Condition Pushdown）

MySQL 5.6+ 特性：在 存储引擎层 先过滤索引条件，减少回表次数。

-- idx_user_status (user_id, status)
EXPLAIN SELECT * FROM orders WHERE user_id > 1000 AND status = 1;
-- Extra: Using index condition
-- 含义：在索引层先按 user_id 范围扫描，同时用 status=1 过滤，再回表

索引合并（Index Merge）

当单个索引无法覆盖查询，但多个索引各覆盖一部分条件时，优化器可能合并结果。

EXPLAIN SELECT * FROM orders WHERE user_id = 42 OR order_no = 'ORD001';
-- type: index_merge
-- key: idx_user_status,uk_order_no
-- Extra: Using union(idx_user_status,uk_order_no)

Index Merge 不一定比全表扫描快，可用 optimizer_switch 或 USE INDEX hint 对比测试。

执行计划详解（EXPLAIN）

基本用法

EXPLAIN SELECT * FROM orders WHERE user_id = 42
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42  -- MySQL 8.0.18+，真实执行

核心字段解读

type 访问类型（由好到坏）

Extra 常见值

key_len 计算示例

idx_user_status (user_id BIGINT, status TINYINT) ：

EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- key_len: 8

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 0;
-- key_len: 9

VARCHAR 列还需加上长度前缀（1 或 2 字节）及是否允许 NULL（+1）。

执行计划实战举例

例 1：主键点查 — const

EXPLAIN SELECT * FROM orders WHERE id = 50000

id: 1
select_type: SIMPLE
table: orders
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: NULL

最优路径：聚簇索引直接定位一行。

例 2：唯一索引 — const

EXPLAIN SELECT * FROM orders WHERE order_no = 'ORD20260709001'

type: const
key: uk_order_no
rows: 1

例 3：联合索引等值 — ref

EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 1

type: ref
key: idx_user_status
key_len: 9
ref: const,const
rows: 50        -- 预估该用户已支付订单约 50 条
Extra: NULL

例 4：范围查询 — range

EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-06-30'
  AND city = '上海'

type: range
key: idx_city_created
key_len: 203    -- city 全用 + created_at 部分
rows: 12000
Extra: Using index condition

例 5：索引失效 — 全表扫描

EXPLAIN SELECT * FROM orders WHERE amount > 500

type: ALL
key: NULL
rows: 1000000
Extra: Using where

amount 单独无索引；即使有 idx_status_amount ，条件未带最左列 status 时通常用不上。若此类查询频繁，考虑单独建 KEY idx_amount (amount) 或调整业务查询。

例 6：覆盖索引 — Using index

EXPLAIN SELECT user_id, status, id FROM orders WHERE user_id = 200

type: ref
key: idx_user_status
Extra: Using index

无需回表，只扫二级索引叶子。

例 7：排序优化 — 避免 filesort

-- ✅ 利用索引有序性
EXPLAIN SELECT * FROM orders
WHERE city = '北京'
ORDER BY created_at DESC
LIMIT 20

key: idx_city_created
Extra: NULL   -- 无 Using filesort

-- ❌ 排序列与索引不一致
EXPLAIN SELECT * FROM orders
WHERE city = '北京'
ORDER BY amount DESC
LIMIT 20

Extra: Using filesort

例 8：GROUP BY 与临时表

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status

type: index
key: idx_status_amount
rows: 1000000
Extra: Using index

若 GROUP BY 列无索引或无法利用索引顺序，Extra 可能出现 Using temporary; Using filesort 。

例 9：JOIN 执行计划

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50)
);

EXPLAIN SELECT o.order_no, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1

-- orders 表（驱动表或被驱动表由优化器决定）
table: o
type: ref
key: idx_user_status 或 idx_status_amount
-- users 表
table: u
type: eq_ref
key: PRIMARY
ref: o.user_id

JOIN 列 o.user_id 应有索引；被关联表 users.id 是主键， eq_ref 最优。

例 10：子查询与派生表

EXPLAIN SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE name LIKE '张%'
)

MySQL 8.0 可能对 IN 子查询做 半连接（semi-join） 优化，*select_type* 可能显示 SUBQUERY 或合并为 JOIN。用 EXPLAIN FORMAT=JSON 可看 attached_condition 等细节。

例 11：EXPLAIN ANALYZE 对比预估与实际

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 0

-> Index lookup on orders using idx_user_status (user_id=42, status=0)
   (cost=120 rows=800) (actual time=0.05..2.1 rows=750 loops=1)

若 rows 预估 800 实际 750，说明统计信息较准；若预估 50 实际 50000，需 ANALYZE TABLE orders 更新统计信息。

排查与优化流程

标准排查步骤

1. 开启慢查询日志，抓取慢 SQL

2. EXPLAIN / EXPLAIN ANALYZE 看 type*、*key*、*rows*、*Extra

3. 确认是否回表、是否 filesort / temporary

4. 检查索引是否缺失、联合索引顺序是否合理、是否有隐式转换

5. SHOW INDEX FROM orders 查看索引 cardinality

6. ANALYZE TABLE orders 更新统计信息后再对比

   SHOW INDEX FROM orders; SHOW TABLE STATUS LIKE ‘orders’

   – 查看优化器跟踪（MySQL 5.6+） SET optimizer_trace=‘enabled=on’; SELECT * FROM orders WHERE status = 1 AND amount > 100 LIMIT 1; SELECT * FROM information_schema.optimizer_trace SET optimizer_trace=‘enabled=off’;

实用原则小结