MySQL 索引详解:从数据结构到 InnoDB 页,再到创建/查看/删除索引
索引本质上是在'表数据'之外,额外维护一套有序的数据结构,让查询不必每次都全表扫描;代价是:占空间、写入变慢、维护更复杂。来看这一整套逻辑怎么闭环。
1)索引是什么:数据库里的'目录'
索引可以理解成书的目录:不翻完整本书,先在目录里定位页码,再跳到具体内容。数据库里也是类似:索引按规则排列记录的'定位信息',用更少的比较和 I/O 快速找到目标行。
MySQL 索引是提升查询效率的关键机制,通过维护有序数据结构避免全表扫描。文章深入解析了索引的数据结构选型,重点阐述 B+ 树为何成为数据库主流,包括其节点存储、树高控制及范围查询优势。同时介绍了 InnoDB 引擎的页(Page)机制,说明 16KB 页内行记录的组织方式及页目录优化。内容涵盖主键、普通、唯一、全文等索引分类,区分聚集索引与二级索引的回表概念,并给出索引覆盖的优化场景。最后提供了索引创建、查看、删除的具体 SQL 命令及设计注意事项,帮助开发者合理构建索引以提升数据库性能。
索引本质上是在'表数据'之外,额外维护一套有序的数据结构,让查询不必每次都全表扫描;代价是:占空间、写入变慢、维护更复杂。来看这一整套逻辑怎么闭环。
索引可以理解成书的目录:不翻完整本书,先在目录里定位页码,再跳到具体内容。数据库里也是类似:索引按规则排列记录的'定位信息',用更少的比较和 I/O 快速找到目标行。
没有索引时经常只能全表扫;有索引时通常能走索引快速定位(效果可用 EXPLAIN 验证)。
EXPLAIN SELECT * FROM user WHERE phone = '13800000000';
核心目的就一个:提升检索效率。应用运行过程中,查询频率通常远高于插入/更新/删除,因此优化查询往往收益最大。
给高频过滤列建索引后,这类等值查询通常更有机会走索引。
CREATE INDEX idx_user_phone ON user (phone);
SELECT * FROM user WHERE phone = '13800000000';
HASH 的等值查询非常快(近似 O(1)),但不支持范围查找,这会直接卡住很多数据库常见需求(BETWEEN、排序、范围扫描)。
范围查询是典型痛点(HASH 索引不适配这种按区间扫描的访问方式)。
SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-01-31';
二叉搜索树中序遍历是有序数组没错,但存在:
只要数据量大,全表扫描或'层数太深的树'都会变慢,根因常常是 I/O。
SELECT * FROM big_table WHERE id = 50000000;
N 叉树能有效降低树高,从而减少 I/O;但 MySQL 最终还是选择了更适合磁盘与范围扫描的结构:B+ 树。
典型需求是'范围 + 排序',这正是 B+ 树强项。
SELECT * FROM user WHERE id BETWEEN 1000 AND 2000 ORDER BY id;
B+ 树是数据库/文件系统里常见的平衡查找树。关键特性:
范围查询能顺着叶子链表走,效率远高于散列结构。
SELECT * FROM orders WHERE order_id >= 100000 AND order_id < 101000;
只靠'B+ 树适合范围查询'还不够,来看它的工程细节为什么刚好贴合数据库:
只按二级索引列查整行,往往会触发'先索引、再回表'的路径。
SELECT * FROM student WHERE sno = '20250001';
数据库慢通常慢在磁盘 I/O。B+ 树让树高变低的原因是:分叉因子(fanout)很大——一个节点能挂很多孩子。 分叉因子大来自两点:
直觉结果就是:数据量到'千万级',树高依然可能只是 3~4 层,点查时需要读的页数就很少。
主键点查就是典型'沿树高向下'的路径。
SELECT * FROM t WHERE id = 5;
范围查询不需要每个值都重新从根开始找:
区间扫描基本就是'定位 + 顺扫'。
SELECT * FROM orders WHERE order_id >= 100000 AND order_id < 101000;
B+ 树会通过分裂/合并/重分配保持平衡:
大量写入时,索引越多,需要维护的结构越多。
INSERT INTO log_table (message, create_time) VALUES ('...', NOW());
InnoDB 的 .ibd 文件里最重要的结构就是 Page(页):它是内存与磁盘交互的最小单元,默认 16KB。一次 I/O 至少读一页,利用'局部性原理'(时间局部性、空间局部性)把可能马上要用到的数据一并载入内存,从而减少后续 I/O。
直接查看页大小(默认 16384 字节 = 16KB)。
SHOW VARIABLES LIKE 'innodb_page_size';
页的结构层面:不管是什么类型的页(数据页/索引页),都会包含页头(File Header)与页尾(File Trailer);页与页之间通过'上一页号/下一页号'连成双向链表。
新页会自动生成两条'伪记录':最小行 Infimum 和最大行 Supremum,它们不存真实数据,只作为链表头尾;每条真实记录通过 next_record 串起来形成单向链表。插入更多行后,会按主键从小到大链接。
主键有序插入能更贴合页内有序组织的目标。
INSERT INTO t (id, name) VALUES (1, 'a'), (2, 'b'), (3, 'c');
如果只靠链表逐行比对,一个页可能几百行,遍历成本太高。InnoDB 通过 页目录把页内记录分组:头/尾行各自成组,其它组最多 8 条,并把每组最后一条记录的位置按主键顺序存到'槽(slot)'里。查找时先对槽做二分定位,再在槽内最多 8 条记录里遍历,效率大幅提升。
走主键点查时,页目录机制就是关键支撑。
SELECT * FROM t WHERE id = 6;
B+ 树在索引中的经典布局:非叶子节点存索引项,叶子节点存真实数据。一次点查会经历'加载索引页 → 下探索引页 → 命中后加载数据页'的 I/O 路径。
点查主键是最标准的 B+ 树路径。
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。
InnoDB 定义 PRIMARY KEY 时使用其作为聚集索引;通常建议每表都有主键,不存在合适唯一非空列时可加自增列。
CREATE TABLE t_pk ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
如果没有主键,InnoDB 会选择第一个 UNIQUE 且 NOT NULL 的列作为聚集索引;若也没有合适的唯一索引,则会生成 6 字节 ROW_ID 作为索引标识(单调递增)。
普通索引无唯一性限制;多列组合叫复合索引。
CREATE INDEX idx_sno ON student (sno);
CREATE INDEX idx_sno_class ON student (sno, class_id);
定义 UNIQUE 会自动创建唯一索引,列值不允许重复。
CREATE TABLE t_uk ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) UNIQUE);
基于文本列(CHAR/VARCHAR/TEXT)用于全文搜索,仅 MyISAM 和 InnoDB 支持。
给内容列建全文索引:
ALTER TABLE article ADD FULLTEXT INDEX ft_content (content);
聚集索引之外的索引称非聚集索引/二级索引。二级索引记录里会包含该行的主键值以及二级索引列;用二级索引查到主键后,再去聚集索引取整行,这一步叫回表。
按二级索引列查询整行,通常可能发生回表。
SELECT * FROM student WHERE sno = '20250001';
当查询使用普通索引,且 SELECT 列表都被该索引覆盖(是索引列的全部或部分列),就可以直接从索引返回结果而不回表,这就是索引覆盖。
只查 sno、class_id,刚好被复合索引覆盖,通常更有机会避免回表。
SELECT sno, class_id FROM student WHERE sno = '20250001';
索引不是'建了就赚',要看查询模式与数据分布。
复合索引(a,b,c)在过滤条件里更常见的有效形态是'从左往右连续使用'。
如果常用条件是 sno=... AND class_id=...,复合索引通常比两个单列索引更直接。
SELECT * FROM student WHERE sno = '20250001' AND class_id = 2;
回表慢的关键原因是:索引页命中后还得再读一次聚集索引的数据页。
只取索引列,结果可直接从二级索引叶子节点返回,通常能减少一次 I/O。
SELECT sno, class_id FROM student WHERE sno = '20250001';
BETWEEN 本质上就是'范围走法'。
SELECT * FROM user WHERE id BETWEEN 1000 AND 2000;
当表加上主键、外键、唯一约束时,MySQL 会为对应列自动创建索引。
PRIMARY KEY 与 UNIQUE 都会触发索引。
CREATE TABLE t_auto ( id BIGINT PRIMARY KEY, email VARCHAR(64) UNIQUE);
建表时定义主键、或事后 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;
CREATE TABLE t_test_uk2 ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
ALTER TABLE t_test_uk2 ADD UNIQUE (name);
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);
CREATE INDEX index_name ON t_test_index2 (sno, class_id);
show keys from 表名\G 能看到索引是否唯一、索引名、列顺序、索引类型(BTREE)等信息。
SHOW KEYS FROM t_test_index6 \G;
SHOW INDEX FROM t_test_index6;
DESC t_test_index6;
如果主键列带 AUTO_INCREMENT,直接删主键会报错;需要先去掉自增属性,再删主键。
完整流程:
ALTER TABLE t_test_index6 DROP PRIMARY KEY; -- 可能报错(auto column)
ALTER TABLE t_test_index6 MODIFY id BIGINT; -- 先去掉自增
ALTER TABLE t_test_index6 DROP PRIMARY KEY; -- 再删主键
ALTER TABLE t_test_index6 DROP INDEX index_name;
索引不是越多越好,核心约束有四条:
写多读少的表,盲目加索引很容易得不偿失。
INSERT INTO log_table (message, create_time) VALUES ('...', NOW());

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
使用加密算法(如AES、TripleDES、Rabbit或RC4)加密和解密文本明文。 在线工具,加密/解密文本在线工具,online
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online