MySQL 索引详解

解决方法：创建索引

alter table EMP add index(empno);

建立索引后再次查询，速度显著提升。

二、硬件基础

MySQL 数据存储于磁盘外设中。磁盘是机械设备，效率相对较低，IO 特征是性能优化的关键话题。

2.1 磁盘结构

磁盘包含主轴马达和盘片，磁头进行寻址定位。数据保存在扇区中，默认大小为 512 字节。

2.2 定位方式

• CHS: 磁头 (Heads)、柱面 (Cylinder)、扇区 (Sector)。实际系统软件使用 LBA (线性地址)。
• 数据块: 操作系统读取磁盘的基本单位通常为 4KB。文件系统基于此建立。

2.3 随机与连续访问

• 随机访问: 扇区地址不连续，磁头移动大，效率低。
• 连续访问: 扇区地址连续，效率高。OS 文件系统会尝试优化 IO 请求顺序。

三、软件交互

3.1 MySQL 与磁盘交互单位

MySQL InnoDB 引擎进行 IO 的基本单位是 16KB，称为 Page。

SHOW GLOBAL STATUS LIKE 'innodb_page_size';

3.2 Buffer Pool

MySQL 在内存中申请 Buffer Pool 空间用于缓存数据，减少磁盘 IO 次数。默认大小可通过配置调整。

四、索引结构

4.1 问题引入

乱序插入数据时，InnoDB 会自动排序。这是为了优化查询效率。

4.2 Page 管理

• Page 内部: 数据有序，利用局部性原理，一次 IO 加载整个 Page 到 Buffer Pool。
• Page 之间: 多个 Page 通过双向链表连接。单纯遍历效率低。

4.3 B+ 树结构

为解决多页查找效率问题，引入目录页（非叶子节点）和数据页（叶子节点），形成 B+ 树。

• 非叶子节点: 仅存键值和指针，不存数据，可容纳更多目录项，树更矮胖，减少 IO 次数。
• 叶子节点: 存储实际数据，并通过双链表连接，支持范围查找。

4.4 为什么选择 B+ 树

• 链表: 线性遍历，效率低。
• 二叉搜索树/AVL/红黑树: 树高较高，IO 次数多。
• Hash: 不支持范围查找。
• B 树 vs B+ 树: B+ 树非叶子节点不存数据，空间利用率更高；叶子节点相连，更适合范围查询。

4.5 聚簇索引与非聚簇索引

• InnoDB: 聚簇索引，数据文件与索引绑定在一起。若无主键，生成隐藏聚簇索引。
• MyISAM: 非聚簇索引，索引与数据分离，叶子节点存数据地址。
• 回表查询: InnoDB 辅助索引叶子节点只存 Key 值，需先查辅助索引得主键，再查主索引得记录。

五、索引管理

5.1 主键索引

• 一个表最多一个主键索引。
• 列值不能为 NULL 且唯一。
• 创建方式：建表时指定或 ALTER TABLE 添加。

5.2 唯一索引

• 允许一个表有多个唯一索引。
• 列值必须唯一，可为 NULL（除非定义 NOT NULL）。

5.3 普通索引

• 一个表可有多个普通索引。
• 适用于有重复值的字段。

5.4 全文索引

• 用于文章等大量文字检索。
• MyISAM 引擎支持较好，InnoDB 5.6+ 支持。
• 使用 MATCH ... AGAINST 语法。

5.5 查看与删除索引

show keys from 表名;
show index from 表名;
desc 表名;

alter table 表名 drop primary key;
alter table 表名 drop index 索引名;
drop index 索引名 on 表名;

六、索引创建原则

1. 频繁作为查询条件的字段应创建索引。
2. 唯一性太差的字段（如性别）不适合单独创建索引。
3. 更新非常频繁的字段不适合创建索引。
4. 不会出现在 WHERE 子句中的字段不该创建索引。

七、其他概念

• 复合索引: 以多列作为索引，遵循最左匹配原则。
• 索引覆盖: 查询所需列均在索引中，无需回表。