MySQL 索引与查询优化

一、概述

索引是关系型数据库中用于加速数据检索的核心结构，其原理类似于书籍的目录——通过建立有序的映射关系，将查询的时间复杂度从全表扫描的 O(n) 降至 O(log n) 级别。在 MySQL 中，索引与约束紧密相关，主键、唯一键等约束的实现都依赖于索引。掌握索引的存储结构、使用规则以及优化技巧，是写出高性能 SQL 的必备能力。

二、示例表结构

为便于理解，我们创建一个学生成绩相关的表，并建立多种索引：

CREATE TABLE `in_failure_t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `class_id` int NOT NULL,
  `score` int DEFAULT NULL,
  `lucky_number` int DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_class` (`name`, `class_id`),     -- 组合索引
  KEY `idx_lucky` (`lucky_number`),               -- 普通索引
  UNIQUE KEY `uk_name` (`name`)                   -- 唯一索引（示例）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

主键索引：id 列，InnoDB 会自动为其创建聚簇索引。
组合索引：(name, class_id)，可用于 name 或 name+class_id 的查询。
普通索引：lucky_number，用于加速对该列的查询。
唯一索引：uk_name，保证 name 值唯一，同时加速查询。

三、索引的分类与存储结构

1. 按数据结构分类

B+ 树索引：InnoDB 默认且最常用的索引结构，适用于等值查询和范围查询。
哈希索引：InnoDB 内部实现的自适应哈希索引，用于热点数据的快速查找（O(1)），对用户透明。
全文索引：用于文本字段的关键词检索，实际生产中常被 Elasticsearch 等专用引擎替代。

2. 按物理存储分类

类型	特点	数量限制
聚簇索引	叶子节点存储完整行数据，表数据按主键顺序存放	每表仅有一个
辅助索引	叶子节点存储索引列 + 主键值，查询时需回表（先查辅助索引获取主键，再查聚簇索引）	可以创建多个

聚簇索引：InnoDB 中主键就是聚簇索引。若未显式定义主键，则会选择第一个非空唯一索引作为聚簇索引；若也没有，则自动生成一个隐藏的 row_id 作为主键。
辅助索引：所有非主键索引都是辅助索引，它们不包含完整数据，只存储索引列和主键值。因此，通过辅助索引查询时，通常需要回表（两次 B+ 树查找）。

3. 按列属性分类

主键索引：非空且唯一，每表只有一个。
唯一索引：值唯一，但允许 NULL（一个表可以有多个 NULL）。
普通索引：允许重复值，仅用于加速查询。
组合索引：多个列组成的索引，B+ 树键值为多字段拼接，比较时按声明顺序逐字段比较。

四、B+ 树索引原理

1. 为什么选择 B+ 树

在数据库中，数据量巨大时无法全部放入内存，必须借助磁盘存储。磁盘 I/O 的耗时远高于内存访问（毫秒级 vs 纳秒级），因此索引结构应尽可能减少磁盘 I/O 次数。B+ 树具有以下优势：

矮胖结构：每个节点可存储多个键值（多路），树高通常为 2~4 层，查询时仅需几次磁盘 I/O。
叶子节点有序链表：叶子节点之间通过双向链表连接，支持高效的范围查询（无需回溯到父节点）。
非叶子节点只存键值：不存储数据，使得每个节点能容纳更多索引项，进一步降低树高。

2. B+ 树与红黑树的对比

对比维度	B+ 树	红黑树
节点分支数	多路（通常上百）	二叉（仅两个分支）
树高	低（适合磁盘）	高（适合内存）
磁盘 I/O	少	多
范围查询	叶子节点链表，效率高	需中序遍历，效率低

3. B+ 树的磁盘存储细节

InnoDB 中，B+ 树的每个节点对应一个页（Page），大小固定为 16KB。页是 InnoDB 的最小 I/O 单元，一次磁盘 I/O 读取一个完整的页。页内可以存储多行记录或索引键。

非叶子节点：只存储索引键和指向下一层节点的指针。
叶子节点：存储完整的行数据（聚簇索引）或索引列+主键值（辅助索引），并通过双向链表串联。

4. 范围查询示例

-- 查询主键范围 18 ~ 40 之间的记录
SELECT * FROM user WHERE id BETWEEN 18 AND 40;

执行过程：

从根节点开始，通过二分查找定位到键值 18 所在的叶子节点页。
沿叶子节点的链表顺序扫描，依次读取页中满足条件的记录，直到遇到 >40 的键值停止。

由于叶子节点在物理上可能不连续，但通过链表逻辑连续，仍然能避免多次随机 I/O。

5. 辅助索引的查询过程

-- 通过辅助索引查询
SELECT * FROM user WHERE lucky_number = 33;

执行过程：

在辅助索引的 B+ 树中找到 lucky_number=33 对应的叶子节点，获取主键 ID（例如 47）。
根据主键 ID 到聚簇索引中回表查询完整行数据。

可见，辅助索引的查询多了一次回表操作，效率低于直接使用主键。

五、InnoDB 的存储结构与缓存

1. 段、区、页

InnoDB 将数据组织为段（Segment）、区（Extent）、页（Page） 三级结构：

页：16KB，最小 I/O 单元。
区：连续 64 个页，共 1MB。InnoDB 以区为单位向磁盘申请空间，保证物理连续性，减少随机 I/O。
段：由若干个区组成，分为数据段、索引段、回滚段等。

2. Buffer Pool

Buffer Pool 是 InnoDB 在内存中维护的一块区域，用于缓存磁盘数据页，减少磁盘 I/O。其内部通过 LRU 算法 管理页的淘汰。

LRU 分区：将链表分为新生代（5/8）和老生代（3/8）。新加载的页插入新生代头部，热点数据前移，老生代尾部页被淘汰。
脏页：被修改但尚未写回磁盘的页，会通过后台线程或 checkpoint 机制刷新。

3. Change Buffer

Change Buffer 是 InnoDB 针对辅助索引的写优化。当对辅助索引进行更新操作时，若目标页不在 Buffer Pool 中，InnoDB 不会立即读取该页，而是将变更暂存到 Change Buffer 中，待后续该页被读取时再合并应用。

作用：减少随机 I/O，提高写入性能。
适用场景：写多读少的辅助索引。

4. 自适应哈希索引

InnoDB 会监控对索引的查询模式，如果发现某个索引页被频繁访问，会自动在内存中为其建立哈希索引（自适应哈希索引），实现 O(1) 查找。此功能对用户透明，可通过参数 innodb_adaptive_hash_index 控制。

5. 写操作的双写缓冲

InnoDB 采用 Doublewrite Buffer 机制防止页的“部分写”问题：在将脏页写入磁盘前，先将其复制到连续的双写缓冲区，再写入实际位置。如果写入过程中发生宕机，可以从双写缓冲区恢复。

六、索引的设计与优化原则

1. 覆盖索引

定义：查询所需的所有列都包含在一个辅助索引中，此时查询只需访问该辅助索引，无需回表，称为索引覆盖。

示例：

-- 组合索引 idx_name_class (name, class_id)
SELECT name, class_id, id FROM in_failure_t WHERE name = 'Mark';
-- 索引包含了 name、class_id 和主键 id，可直接返回结果

优化建议：

业务查询尽量只返回必要字段，避免 SELECT *，以增加覆盖索引的可能性。
可将高频查询的字段组合成一个索引，使其覆盖查询。

2. 最左匹配原则

对于组合索引，MySQL 会从左到右匹配查询条件，遇到范围查询（>、<、BETWEEN、LIKE 前缀）或非等值查询时，后续列无法使用索引。

示例：组合索引 (name, class_id)

查询条件	是否使用索引	原因
`WHERE name = 'Mark'`	是	匹配最左列
`WHERE name = 'Mark' AND class_id = 1`	是	完全匹配
`WHERE class_id = 1`	否	缺失最左列
`WHERE name LIKE 'Mark%' AND class_id = 1`	是	前缀匹配，class_id 可用
`WHERE name LIKE '%Mark%' AND class_id = 1`	否	通配符开头，name 无法使用索引

开发建议：将区分度高、经常作为查询条件的列放在组合索引左侧。

3. 索引下推（ICP）

定义：Index Condition Pushdown，MySQL 5.6 引入的优化。在查询辅助索引时，将部分 WHERE 条件（那些可以使用索引列的条件）下推到存储引擎层进行过滤，从而减少回表次数。

示例：组合索引 (name, class_id)，查询条件为 WHERE name LIKE 'Mark%' AND class_id = 1

无 ICP：先找到所有 name 以 'Mark' 开头的记录的主键，回表取完整行，再判断 class_id。
有 ICP：在辅助索引 B+ 树中，直接在叶子节点上判断 class_id，只将满足条件的记录主键回表，大幅减少回表次数。

4. 索引失效的常见场景

场景	示例（导致失效）	正确写法或原因
对索引列使用函数或表达式	`WHERE id + 1 = 2`	`WHERE id = 1`
隐式类型转换	`WHERE phone = 123`（phone 是 varchar）	`WHERE phone = '123'`
LIKE 以通配符开头	`WHERE name LIKE '%Mark'`	无法利用索引
OR 连接的列有非索引列	`WHERE id = 1 OR name = 'Mark'`（name 无索引）	可拆分为 UNION 或重建索引
组合索引未使用最左列	`WHERE class_id = 1`（组合索引为 name,class_id）	无法使用
使用 `NOT IN`、`<>`、`!=`	`WHERE id <> 2`	这类操作一般会使索引失效
索引列参与运算	`WHERE id * 2 = 4`	`WHERE id = 2`

5. 索引设计原则

小表可不用索引：当表记录很少（如 < 1000）时，全表扫描可能更快。
区分度低的列不建索引：例如性别列，只有男/女，建索引效率不高。
频繁更新的列少建索引：每次更新都会维护索引树，增加开销。
组合索引优先：多条件查询时，尽量用组合索引代替多个单列索引。
短索引优先：对于长字符串（如 URL），可考虑前缀索引，但需保证区分度。
单表索引不超过 6 个：过多索引会严重影响写入性能。
避免 SELECT *：明确字段有助于覆盖索引和减少网络传输。

七、SQL 优化实战

1. 慢查询定位

查看当前正在执行的线程：
```
SHOW PROCESSLIST;
```

开启慢查询日志：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;   -- 设置阈值 2 秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

使用 EXPLAIN 分析执行计划：

EXPLAIN SELECT * FROM in_failure_t WHERE name = 'Mark';

2. EXPLAIN 关键字段解读

字段	含义	优化目标
`type`	访问类型：`const` > `eq_ref` > `ref` > `range` > `index` > `ALL`	至少达到 `range` 级别
`key`	实际使用的索引	不为 NULL
`rows`	估算需要扫描的行数	越小越好
`Extra`	额外信息：`Using index`（覆盖索引），`Using where`，`Using filesort`（需排序）等	避免 `Using filesort`

3. 常见优化手段

多表 JOIN 优化：
- 确保 JOIN 字段都有索引。
- 用小表驱动大表（MySQL 优化器会自动选择，但可人为控制 JOIN 顺序）。
子查询优化：
- 将 IN 子查询改写为 JOIN。
- 将 NOT IN 改写为 LEFT JOIN ... WHERE ... IS NULL。
分页优化：
- 避免 LIMIT 100000,10 的大偏移量，可使用“延迟关联”或记录上次查询的最大主键。
避免在 WHERE 子句中使用函数：
- 如果必须用，可考虑添加冗余列（如日期拆分）并建索引。

posted @ 2026-03-26 21:49 xggx 阅读(26) 评论(0) 收藏举报

刷新页面返回顶部

xggx

MySQL 索引与查询优化

MySQL 索引与查询优化

一、概述

二、示例表结构

三、索引的分类与存储结构

1. 按数据结构分类

2. 按物理存储分类

3. 按列属性分类

四、B+ 树索引原理

1. 为什么选择 B+ 树

2. B+ 树与红黑树的对比

3. B+ 树的磁盘存储细节

4. 范围查询示例

5. 辅助索引的查询过程

五、InnoDB 的存储结构与缓存

1. 段、区、页

2. Buffer Pool

3. Change Buffer

4. 自适应哈希索引

5. 写操作的双写缓冲

六、索引的设计与优化原则

1. 覆盖索引

2. 最左匹配原则

3. 索引下推（ICP）

4. 索引失效的常见场景

5. 索引设计原则

七、SQL 优化实战

1. 慢查询定位

2. EXPLAIN 关键字段解读

3. 常见优化手段

公告