跳到主要内容 MySQL 索引原理：数据结构、InnoDB 页及操作指南 | 极客日志
SQL 算法
MySQL 索引原理：数据结构、InnoDB 页及操作指南 MySQL 索引是提升查询效率的关键机制，通过维护有序数据结构避免全表扫描。文章深入解析了索引的数据结构选型，重点阐述 B+ 树为何成为数据库主流，包括其节点存储、树高控制及范围查询优势。同时介绍了 InnoDB 引擎的页（Page）机制，说明 16KB 页内行记录的组织方式及页目录优化。内容涵盖主键、普通、唯一、全文等索引分类，区分聚集索引与二级索引的回表概念，并给出索引覆盖的优化场景。最后提供了索引创建、查看、删除的具体 SQL 命令及设计注意事项，帮助开发者合理构建索引以提升数据库性能。
Stephaine Walsh 发布于 2026/2/4 更新于 2026/4/18 MySQL 索引详解：从数据结构到 InnoDB 页，再到创建/查看/删除索引
索引本质上是在'表数据'之外，额外维护一套有序的数据结构 ，让查询不必每次都全表扫描；代价是：占空间、写入变慢、维护更复杂。来看这一整套逻辑怎么闭环。
1）索引是什么：数据库里的'目录'
索引可以理解成书的目录：不翻完整本书，先在目录里定位页码，再跳到具体内容。数据库里也是类似：索引按规则排列记录的'定位信息'，用更少的比较和 I/O 快速找到目标行。
没有索引时经常只能全表扫；有索引时通常能走索引快速定位（效果可用 EXPLAIN 验证）。
EXPLAIN SELECT * FROM user WHERE phone = '13800000000' ;
2）为什么要用索引：查询远比增删改更频繁 核心目的就一个：提升检索效率 。应用运行过程中，查询频率通常远高于插入/更新/删除，因此优化查询往往收益最大。
给高频过滤列建索引后，这类等值查询通常更有机会走索引。
CREATE INDEX idx_user_phone ON user (phone);
SELECT * FROM user WHERE phone = '13800000000' ;
3）索引的数据结构怎么选：HASH、二叉树、N 叉树、B+ 树
3.1 HASH：O(1) 很香，但不擅长范围查询 HASH 的等值查询非常快（近似 O(1)），但不支持范围查找 ，这会直接卡住很多数据库常见需求（BETWEEN、排序、范围扫描）。
范围查询是典型痛点（HASH 索引不适配这种按区间扫描的访问方式）。
SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-01-31' ;
3.2 二叉搜索树：最坏 O(N) + 磁盘 I/O 成本太高
最坏退化成链表（O(N)）
即便 AVL/红黑树更平衡，仍是二叉结构，树高难以压得很低
数据库检索每下探一层就可能触发一次磁盘 I/O，而 I/O 是性能瓶颈：减少 I/O 次数比减少 CPU 比较更关键 。
只要数据量大，全表扫描或'层数太深的树'都会变慢，根因常常是 I/O。
SELECT * FROM big_table WHERE id = 50000000 ;
3.3 N 叉树：树高能降，但还不够'工程化友好' N 叉树能有效降低树高，从而减少 I/O；但 MySQL 最终还是选择了更适合磁盘与范围扫描的结构：B+ 树。
典型需求是'范围 + 排序'，这正是 B+ 树强项。
SELECT * FROM user WHERE id BETWEEN 1000 AND 2000 ORDER BY id;
3.4 B+ 树：数据库索引的主力 B+ 树是数据库/文件系统里常见的平衡查找树。关键特性：
数据稳定有序，插入/修改时间复杂度稳定
非叶子节点只存索引，不存真实数据 真实数据都在叶子节点 叶子节点形成有序链表 ，非常利于区间查询与顺序遍历
SELECT * FROM orders WHERE order_id >= 100000 AND order_id < 101000 ;
B+ 树为什么既快又稳 只靠'B+ 树适合范围查询'还不够，来看它的工程细节为什么刚好贴合数据库：
A）B+ 树节点里到底放什么
内部节点（非叶子） ：放'分隔键 + 子指针'。分隔键像路标：告诉接下来往哪个子节点走。叶子节点 ：放真正的'有序记录'。在 InnoDB 里常见两种形态：
聚集索引（主键索引）叶子 ：叶子里就是整行数据（所以主键查整行很直接）。二级索引（普通索引）叶子 ：叶子里是'二级索引列 + 主键值'，想拿整行通常还要再用主键去聚集索引找一次（这一步就是回表）。
只按二级索引列查整行，往往会触发'先索引、再回表'的路径。
SELECT * FROM student WHERE sno = '20250001' ;
B）为什么树高能压得很低（这才是 I/O 变少的关键） 数据库慢通常慢在磁盘 I/O。B+ 树让树高变低的原因是：分叉因子（fanout）很大 ——一个节点能挂很多孩子。
分叉因子大来自两点：
内部节点不存整行，只存键与指针，单条索引项很小；
InnoDB 以页为单位组织（默认 16KB），一个索引页能容纳大量索引项。
直觉结果就是：数据量到'千万级'，树高依然可能只是 3~4 层，点查时需要读的页数就很少。
SELECT * FROM t WHERE id = 5 ;
C）为什么范围查询特别快（叶子链表是关键武器）
先定位到范围起点（一次点查路径）；
然后沿着叶子节点的有序链表顺序向后扫，直到越过范围终点。
SELECT * FROM orders WHERE order_id >= 100000 AND order_id < 101000 ;
D）插入/删除为什么仍然'稳定'（不会越用越退化）
插入导致页满：触发分裂，把一半记录挪到新页，并把分隔键上推到父节点；
删除导致页太空：触发合并或向兄弟页借记录。
这类维护成本就是索引'写入变慢'的来源之一，但换来的是查询时稳定的树高与稳定性能。
INSERT INTO log_table (message, create_time) VALUES ('...' , NOW());
4）InnoDB 的'页'机制：16KB 为单位读写磁盘 InnoDB 的 .ibd 文件里最重要的结构就是 Page（页） ：它是内存与磁盘交互的最小单元，默认 16KB。一次 I/O 至少读一页，利用'局部性原理'（时间局部性、空间局部性）把可能马上要用到的数据一并载入内存，从而减少后续 I/O。
直接查看页大小（默认 16384 字节 = 16KB）。
SHOW VARIABLES LIKE 'innodb_page_size' ;
页的结构层面：不管是什么类型的页（数据页/索引页），都会包含页头（File Header）与页尾（File Trailer）；页与页之间通过'上一页号/下一页号'连成双向链表 。
5）页内怎么组织行：Infimum/Supremum + 单向链表 + 页目录（二分定位）
5.1 页主体：行记录的链表结构 新页会自动生成两条'伪记录'：最小行 Infimum 和最大行 Supremum ，它们不存真实数据，只作为链表头尾；每条真实记录通过 next_record 串起来形成单向链表。插入更多行后，会按主键从小到大链接。
INSERT INTO t (id, name) VALUES (1 , 'a' ), (2 , 'b' ), (3 , 'c' );
5.2 页目录 Page Directory：把'页内查找'从遍历升级成二分 + 小范围遍历 如果只靠链表逐行比对，一个页可能几百行，遍历成本太高。InnoDB 通过 页目录 把页内记录分组：头/尾行各自成组，其它组最多 8 条，并把每组最后一条记录的位置按主键顺序存到'槽（slot）'里。查找时先对槽做二分定位，再在槽内最多 8 条记录里遍历，效率大幅提升。
SELECT * FROM t WHERE id = 6 ;
6）B+ 树在 MySQL 索引中的落地：三次 I/O 点查两千多万行 B+ 树在索引中的经典布局：非叶子节点存索引项，叶子节点存真实数据 。一次点查会经历'加载索引页 → 下探索引页 → 命中后加载数据页'的 I/O 路径。
SELECT * FROM t WHERE id = 5 ;
还有一个非常硬核的估算（用于建立直觉）：假设用户行数据约 1KB，忽略页内额外开销，则一个 16KB 数据页约能放 16 行；索引记录用 BIGINT 主键 8B + 指针 6B 共 14B，则一个索引页约能放 16*1024/14 ≈ 1170 条索引项。三层 B+ 树容量约为 1170 * 1170 * 16 = 21,902,400 行——也就是两千多万行点查大致 3 次 I/O。
7）索引分类：主键/普通/唯一/全文/聚集/非聚集/覆盖
7.1 主键索引 & 聚集索引（Clustered Index） InnoDB 定义 PRIMARY KEY 时使用其作为聚集索引 ；通常建议每表都有主键，不存在合适唯一非空列时可加自增列。
CREATE TABLE t_pk ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20 ));
如果没有主键，InnoDB 会选择第一个 UNIQUE 且 NOT NULL 的列作为聚集索引；若也没有合适的唯一索引，则会生成 6 字节 ROW_ID 作为索引标识（单调递增）。
7.2 普通索引（可单列/复合） CREATE INDEX idx_sno ON student (sno);
CREATE INDEX idx_sno_class ON student (sno, class_id);
7.3 唯一索引 定义 UNIQUE 会自动创建唯一索引，列值不允许重复。
CREATE TABLE t_uk ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20 ) UNIQUE );
7.4 全文索引 基于文本列（CHAR/VARCHAR/TEXT）用于全文搜索，仅 MyISAM 和 InnoDB 支持。
ALTER TABLE article ADD FULLTEXT INDEX ft_content (content);
7.5 非聚集索引（二级索引）与回表 聚集索引之外的索引称非聚集索引/二级索引。二级索引记录里会包含该行的主键值 以及二级索引列；用二级索引查到主键后，再去聚集索引取整行，这一步叫回表 。
SELECT * FROM student WHERE sno = '20250001' ;
7.6 索引覆盖（Covering Index） 当查询使用普通索引，且 SELECT 列表都被该索引覆盖（是索引列的全部或部分列），就可以直接从索引返回结果而不回表，这就是索引覆盖 。
只查 sno、class_id，刚好被复合索引覆盖，通常更有机会避免回表。
SELECT sno, class_id FROM student WHERE sno = '20250001' ;
加更：索引设计再讲细一点（什么时候建、怎么建才'值'）
A）复合索引的使用规律：先匹配左边再往右 复合索引（a,b,c）在过滤条件里更常见的有效形态是'从左往右连续使用'。
如果常用条件是 sno=... AND class_id=...，复合索引通常比两个单列索引更直接。
SELECT * FROM student WHERE sno = '20250001' AND class_id = 2 ;
B）覆盖索引为什么能省掉回表（本质：只在索引页里就能拿到答案） 回表慢的关键原因是：索引页命中后还得再读一次聚集索引的数据页。
只取索引列，结果可直接从二级索引叶子节点返回，通常能减少一次 I/O。
SELECT sno, class_id FROM student WHERE sno = '20250001' ;
C）等值查询 vs 范围查询：B+ 树两种'走法'
等值：沿树一路下探到叶子，定位到目标键；
范围：定位起点后顺着叶子链表扫。
SELECT * FROM user WHERE id BETWEEN 1000 AND 2000 ;
8）索引如何创建：自动 vs 手动（主键/唯一/普通/复合）
8.1 自动创建 当表加上主键、外键、唯一约束时，MySQL 会为对应列自动创建索引。
PRIMARY KEY 与 UNIQUE 都会触发索引。
CREATE TABLE t_auto ( id BIGINT PRIMARY KEY , email VARCHAR (64 ) UNIQUE );
8.2 手动创建主键索引（多种方式） 建表时定义主键、或事后 ALTER TABLE 添加主键。
CREATE TABLE t_test_pk2 (id BIGINT , name VARCHAR (20 ));
ALTER TABLE t_test_pk2 ADD PRIMARY KEY (id);
ALTER TABLE t_test_pk2 MODIFY id BIGINT AUTO_INCREMENT;
8.3 手动创建唯一索引 CREATE TABLE t_test_uk2 ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20 ));
ALTER TABLE t_test_uk2 ADD UNIQUE (name);
8.4 手动创建普通索引（含指定索引名） CREATE TABLE t_test_index2 ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20 ), sno VARCHAR (10 ));
CREATE INDEX index_name ON t_test_index2 (sno);
8.5 复合索引 CREATE INDEX index_name ON t_test_index2 (sno, class_id);
9）如何查看索引：show keys / show index / desc show keys from 表名\G 能看到索引是否唯一、索引名、列顺序、索引类型（BTREE）等信息。
SHOW KEYS FROM t_test_index6 \G;
SHOW INDEX FROM t_test_index6;
DESC t_test_index6;
10）如何删除索引：删主键 vs 删普通索引
10.1 删除主键索引（自增列要先处理） 如果主键列带 AUTO_INCREMENT，直接删主键会报错；需要先去掉自增属性，再删主键。
ALTER TABLE t_test_index6 DROP PRIMARY KEY ;
ALTER TABLE t_test_index6 MODIFY id BIGINT ;
ALTER TABLE t_test_index6 DROP PRIMARY KEY ;
10.2 删除普通/唯一等其它索引 ALTER TABLE t_test_index6 DROP INDEX index_name;
11）创建索引的注意事项：该建的建，不该建的别硬来
建在高频查询 的列上
索引占额外存储空间
插入/更新/删除会维护索引，可能影响写性能
过多或不合理索引会拖慢整体性能，需要规划
INSERT INTO log_table (message, create_time) VALUES ('...' , NOW());
微信扫一扫，关注极客日志 微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
相关免费在线工具 加密/解密文本 使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。 在线工具，加密/解密文本在线工具，online
SQL 美化和格式化 在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
SQL转CSV/JSON/XML 解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 工具包 CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
Base64 字符串编码/解码 将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
Base64 文件转换器 将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
