数据库索引

        你有没有过这样的经历？在一本厚厚的汉语字典里找一个生僻字，要是没有目录，只能一页页翻，运气不好可能要翻完整本书；但有了笔画、拼音索引，几分钟就能精准定位。其实，数据库里的 “索引”，就是数据表的 “字典目录”—— 它不存储数据本身，却能帮我们在海量数据中快速找到目标，堪称数据库性能的 “加速器”。今天就带大家从基础到实战，彻底搞懂 MySQL 索引的底层逻辑和使用技巧。

一、索引到底是什么？

先抛一个场景：假设你有一张user表，存了 100 万条用户数据。要查询 “id=10086” 的用户信息，如果没有索引，数据库会像 “翻字典没目录” 一样，从第一条数据开始逐行比对（这叫 “全表扫描”），100 万行数据可能要跑几秒；但有了索引，数据库会直接通过索引定位到数据的位置，毫秒级就能出结果。

所以，MySQL 索引本质是一种数据结构，它按照特定规则排序数据表的记录地址，让查询时不用扫描全表，而是通过索引快速 “跳转” 到目标数据 —— 就像字典目录通过 “页码” 指向具体的字，索引通过 “数据地址” 指向数据表中的行。

二、为什么 MySQL 偏爱 B + 树？

索引的核心是 “高效”，但不是所有数据结构都适合做索引。在 MySQL 的选择里，B + 树最终脱颖而出，我们先看看其他候选者为什么 “落选”。

1. 候选 1：HASH 表 —— 快，但有致命缺陷

HASH 表的查询时间复杂度是 O (1)，理论上很快，比如根据 “id=10086” 计算哈希值，能直接定位到数据。但它有个硬伤：不支持范围查询。比如要查 “id>10000 且 id<20000” 的用户，HASH 表就无能为力了，只能全表扫描 —— 这在实际业务中（比如查 “近 30 天注册的用户”）几乎无法使用，所以 MySQL 没把它作为默认索引结构。

2. 候选 2：二叉搜索树 —— 有序，但树太高

二叉搜索树的中序遍历是有序的，支持范围查询，但问题更明显：树高不可控。如果数据是按 “1,2,3,4...” 顺序插入，二叉树会变成一条 “链表”（最坏时间复杂度 O (N)），查询时还是要遍历大部分节点；就算用 AVL、红黑树（平衡二叉树），树高依然会随着数据量增长而飙升 —— 比如 100 万条数据，树高可能超过 20 层。

这里要插一个关键知识点：数据库查询的性能瓶颈是磁盘 IO。每次访问二叉树的一个节点，就要做一次磁盘 IO（从磁盘读数据到内存）。20 层的树意味着要做 20 次 IO，而磁盘 IO 的速度远慢于内存操作，这对性能来说是 “灾难”。

3. 最终选择：B + 树 —— 平衡、高效、还支持范围查询

B + 树是专门为数据库设计的 “N 叉平衡查找树”，它完美解决了前面两种结构的问题，核心优势有三个：

（1）树高极低，减少 IO 次数

B + 树是 “多叉” 的，一个节点可以存多个索引值（比如 MySQL 中，一个索引节点默认对应 16KB 的 “页”，能存上千个索引项）。比如三层高的 B + 树，能存多少数据？我们算笔账：

• 索引项大小：主键（BIGINT）8 字节 + 下一页地址 6 字节 = 14 字节
• 一个索引页（16KB=16384 字节）能存：16384÷14≈1170 个索引项
• 叶子节点（存真实数据）：假设一条用户数据 1KB，一个叶子页能存 16 条数据
• 三层 B + 树总容量：1170（根节点）×1170（二级节点）×16（叶子节点）≈2200 万条数据

也就是说，2200 万条数据，只需要 3 次磁盘 IO 就能查到目标—— 这就是 B + 树的 “降高” 魔力。

（2）叶子节点存数据，还能 “链表遍历”

B + 树的非叶子节点只存索引值（不存真实数据），所有真实数据都存在叶子节点；而且叶子节点之间是 “双向链表” 结构，按索引值有序排列。这意味着：

• 查单条数据：通过非叶子节点快速定位到叶子页，一次获取数据；
• 查范围数据：比如 “id>10000 且 id<20000”，找到起始叶子节点后，直接顺着链表遍历后续叶子节点，不用回退到上层节点 —— 这比其他结构高效太多。

（3）查询性能稳定

不管查哪个数据，都要从根节点走到叶子节点，路径长度相同（比如三层树就是 3 次 IO），不会出现 “某些数据快、某些数据慢” 的情况，性能非常均衡。

三、MySQL 索引的几种类型

索引类型	核心特点	适用场景
主键索引（聚集索引）	基于主键创建，叶子节点存整行数据；一张表只有一个	按主键查询是最常用的索引
普通索引	无唯一性限制，叶子节点村主键值	高频查询的非主键列
唯一索引	列值唯一（允许NULL），自动创建	需要保证唯一性的列
复合索引	基于多列创建，遵循“最左前缀原则”	多列联合查询
全文索引	针对文本列，支持关键词搜索	文章标题，内容搜索

这里要特别提两个容易混淆的概念：

1. 聚集索引 vs 非聚集索引（二级索引）

• 聚集索引：就是主键索引，叶子节点直接存 “整行数据”，查询时找到叶子节点就拿到了所有数据，不用再查其他地方；
• 非聚集索引：除了主键索引之外的所有索引（普通索引、唯一索引等），叶子节点存的是 “主键值”—— 比如用普通索引 “name” 查数据，先找到叶子节点的主键值，再用主键值去聚集索引查整行数据，这个过程叫 “回表”。

2. 索引覆盖：避免 “回表” 的小技巧

如果查询的列刚好是索引包含的列，就不用 “回表” 了 —— 这就是 “索引覆盖”。比如给 “sno” 列建普通索引，查询 “SELECT sno FROM user WHERE sno=2023001”，因为索引里已经有 “sno” 的值，直接返回结果即可，性能比需要回表的查询快很多。

四、实战：索引的 “创建、查看、删除” 全流程

1. 自动创建：MySQL 帮你省力气

有些场景下，MySQL 会自动创建索引，不用你手动写 SQL：

• 给表加PRIMARY KEY（主键）时，自动创建主键索引；
• 给列加UNIQUE（唯一约束）时，自动创建唯一索引；
• 加FOREIGN KEY（外键）时，自动给外键列创建索引。

如果表没有主键和唯一约束，MySQL 会悄悄生成一个 6 字节的ROW_ID，用它作为隐藏的聚集索引 —— 所以建议每张表都手动指定主键（比如自增 id），避免 MySQL 用隐藏ROW_ID。

2. 手动创建：按需定制索引

1.主键索引

方法一：建表时指定主键

create table student(id int primary key auto_increment,
name varchar(20));

方法二：建表时单独指定主键列

create table student(
id int auto_increment,
name varchar(20) not null,
primary key (id));

方法三：建表后添加主键列

create table student(id int , name varchar(20) not null);
alter table student add primary key(id); 

2.普通索引

-- 方式1：建表时指定
CREATE TABLE t_user3 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  sno VARCHAR(10) NOT NULL,
  index idx_sno (sno) -- 普通索引，命名为idx_sno
);

-- 方式2：建表后添加
ALTER TABLE t_user3 ADD INDEX idx_sno (sno);
-- 或用CREATE INDEX语法
CREATE INDEX idx_sno ON t_user3 (sno);

3.复合索引

复合索引要注意 “列的顺序”，比如 “sno+class_id” 和 “class_id+sno” 是不同的索引，建议把查询频率高的列放前面：

-- 建表时创建复合索引
CREATE TABLE t_student (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  sno VARCHAR(10) NOT NULL,
  class_id BIGINT NOT NULL,
  index idx_sno_class (sno, class_id) -- 复合索引：sno在前，class_id在后
);

3. 查看索引：确认索引是否生效

-- 方式1：详细信息（推荐）
SHOW KEYS FROM t_student\G; -- \G让结果更易读

-- 方式2：简洁信息
SHOW INDEX FROM t_student;

-- 方式3：看表结构（只能看到主键和唯一索引）
DESC t_student;

执行SHOW KEYS后，重点看这几列：

• Key_name：索引名；
• Column_name：索引对应的列；
• Seq_in_index：复合索引中列的顺序（1 表示第一列）；
• Index_type：索引类型（MySQL 默认是 BTREE，即 B + 树）。

4. 删除索引：没用的索引要及时清理

索引不是越多越好，多余的索引会占用空间，还会拖慢插入 / 更新 / 删除的速度，所以要定期清理无用索引。

（1）删除主键索引

主键索引不能直接删（如果有自增属性），要先去掉自增：

-- 1. 先取消id的自增
ALTER TABLE t_student MODIFY id BIGINT;
-- 2. 再删除主键索引
ALTER TABLE t_student DROP PRIMARY KEY;

（2）删除普通 / 唯一 / 复合索引

-- 语法：ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE t_student DROP INDEX idx_sno_class;