中间件专栏之MySQL篇——MySQL的索引原理

MySQL的索引是MySQL这个体系中很重要的一环，并且它的概念很多人理解的都有偏差，因此本文对其做一个梳理。

一、索引是什么

首先，索引是数据库表中的一种数据结构，用于加速查询。索引类似于书籍的目录，可以快速找到所需的数据，提高查询效率。表面上索引就是用户自己选择和设置的一个或多个字段，但实际在创建索引时，MySQL会为我们创建一棵b+树（默认情况，也有其他存储结构），用于存储我们创建的索引。

二、索引的作用

✅ 加快查询速度：减少数据扫描，提高 SELECT 语句性能。
✅ 提高排序效率：支持 ORDER BY 更快排序。
✅ 优化 WHERE 语句：快速找到匹配的数据行。
❌ 缺点：

• 占用存储空间。
• 插入、更新、删除操作变慢（需要维护索引）。

通俗来讲，如果我们只创建了数据表但没有创建索引，能查询到我们指定的语句吗？可以，但是通过全表扫描得到的结果，如果数据多则非常耗时，而如果我们创建了索引，则直接去该索引的b+树查询，查询效率得到提高。 

 三、索引的分类

1. 按功能分类

2. 按存储结构分类

3. 按字段数分类

4. 按索引创建方式分类

还有很重要的索引是聚簇索引和辅助索引 ，这两种索引属于 存储结构分类，具体来说：

四、索引的存储结构

如前面提到的，每一个索引对应一棵b+树。

为什么使用b+树数据结构？b+树是多叉树，可以降低层高，减少磁盘io的访问数（redis是红黑树，是二叉树），并且b+树的叶子节点依次相连，方便范围查询。

b+树的特征：1、非叶子节点只存储索引信息；2、叶子节点还存储数据信息；3、叶子节点之间依次相连；4、节点大小为16kb，映射的是连续的磁盘页。

五、索引优化与索引失效

1.1 使用合适的索引

✅ 优化建议：

• 优先使用主键索引和唯一索引，它们查询效率最高。
• WHERE 条件涉及多个字段时，考虑组合索引，避免多个单列索引带来的回表查询。
• LIKE 查询尽量使用全文索引，提高匹配速度。

1.2 覆盖索引（Covering Index）

定义：如果索引已经包含查询所需的所有字段，就可以直接从索引中返回数据，而不需要回表查询，提高性能。

✅ 优化示例

CREATE INDEX idx_name_email ON users(name, email);
SELECT name, email FROM users WHERE name = '张三';

• idx_name_email 包含 name 和 email，查询时直接从索引中获取数据，无需回表，查询更快。

1.3 最左前缀匹配

规则：组合索引 查询时，必须按照索引字段的从左到右顺序使用，否则索引可能失效。

✅ 优化示例

CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = '张三';  -- ✅ 能用索引
SELECT * FROM users WHERE name = '张三' AND age = 30;  -- ✅ 能用索引
SELECT * FROM users WHERE age = 30;  -- ❌ 索引失效（未使用最左前缀）

*1.4 避免 SELECT ，只查询需要的列

✅ 优化示例

SELECT name, email FROM users WHERE name = '张三';

❌ 不推荐

SELECT * FROM users WHERE name = '张三';  -- 可能导致不必要的回表查询

 2. 索引失效的常见原因

可参考下图

六、bufferpool 的设计、空闲页与脏页的管

理策略、刷盘时机

MySQL 中 Buffer Pool 的设计及管理策略

在 MySQL InnoDB 存储引擎中，Buffer Pool（缓冲池）是用于缓存数据页、索引页的核心组件，能大幅提高数据库的性能。下文将详细介绍 Buffer Pool 的设计、空闲页与脏页的管理策略、以及刷盘时机。

1. Buffer Pool 的设计

Buffer Pool 是 InnoDB 的内存缓存区，主要作用是减少磁盘 I/O 操作，加速查询性能。其结构如下：

• 数据页缓存：缓存表的数据页，减少磁盘读取。
• 索引页缓存：缓存 B+ 树索引，提高查询速度。
• Undo 页缓存：用于事务回滚和 MVCC 版本管理。
• Adaptive Hash Index（AHI）：自适应哈希索引，提高查询效率。
• Flush List：存储脏页信息，决定刷盘策略。

Buffer Pool 结构

Buffer Pool 由多个 Page（页） 组成，每个页的大小通常是 16KB（可配置为 8KB 或 4KB）。当查询数据时，InnoDB 先从 Buffer Pool 查找，若未命中，则从磁盘读取，并可能淘汰旧数据。

2. 空闲页与脏页的管理策略

2.1 页面分类

2.2 页面管理策略

InnoDB 采用 LRU（最近最少使用）+ young/old 子列表 机制：

1. 新加载的页 先放入 LRU 列表的 middle 部分（old 列表），而非头部。
2. 访问次数多的页 被移动到 young 列表，减少被淘汰的概率。
3. 当 Buffer Pool 需要空间时，优先淘汰 old 列表的页（避免新加载的数据瞬间淘汰旧数据）。

此外，LRU 机制还会结合 Adaptive Flushing 进行优化，防止 Buffer Pool 被高频访问的数据填满。

3. 刷盘时机

脏页必须写回磁盘（即刷盘）以保证数据一致性，InnoDB 采用以下几种刷盘策略：

3.1 被动刷盘（Lazy Write）

• 当 Buffer Pool 空间不足，需要淘汰旧数据时，会将脏页写入磁盘。
• 触发条件：
  • innodb_max_dirty_pages_pct 限制脏页比例。
  • innodb_lru_scan_depth 影响 LRU 淘汰深度。

3.2 主动刷盘

3.3 刷盘方式

MySQL 提供以下几种刷盘策略（通过 innodb_flush_log_at_trx_commit 参数控制）：