MySQL 索引
索引的定义
MySQL 索引是帮助数据库高效获取数据的排好序的数据结构,核心作用是:
- 将无序的原始数据,通过特定结构组织成'可快速检索'的形式;
- 避免全表扫描(Full Table Scan),直接定位目标数据的物理位置;
- 类比:书籍的目录(通过目录快速找到章节,而非逐页翻找)。
本文详解 MySQL 索引原理及底层数据结构。核心指出 InnoDB 与 MyISAM 基于 B+ 树实现,对比了 B 树、哈希表等结构的优劣,解释了 B+ 树在磁盘 I/O、范围查询及数据一致性上的优势。文章还梳理了主键、唯一、联合等常见索引类型的使用规则,分析了索引失效场景(如函数运算、模糊查询),并提供了区分度优化、覆盖索引及 EXPLAIN 验证等实战技巧,旨在帮助开发者高效设计数据库索引。
MySQL 索引是帮助数据库高效获取数据的排好序的数据结构,核心作用是:
| 核心价值 |
|---|
| 核心代价 |
|---|
| 极快的查询速度(SELECT) | 写入性能下降(INSERT/UPDATE/DELETE) |
| 减少磁盘 I/O 次数 | 占用额外磁盘空间(索引文件) |
| 排序/分组操作加速 | 索引维护成本(数据变更需更新索引) |
关键结论:索引是'空间换时间'的设计——用额外的磁盘空间和写入开销,换取查询性能的大幅提升。
MySQL 主流存储引擎(InnoDB/MyISAM)的索引底层均基于B+ 树实现,而非数组、链表、哈希表或红黑树。要理解这一点,需先对比常见数据结构的优劣,再拆解 B+ 树的设计精髓,尤其要搞懂:为什么 MySQL 选择 B+ 树而非 B 树?
| 数据结构 | 优点 | 缺点(不适合 MySQL 索引) |
|---|---|---|
| 数组(有序) | 二分查找效率高(O(logn)) | 插入/删除需移动大量数据,维护成本高 |
| 链表 | 插入/删除便捷 | 查询需遍历(O(n)),效率极低 |
| 哈希表 | 等值查询极致快(O(1)) | 不支持范围查询(如>、<、BETWEEN);无序 |
| 红黑树(二叉树) | 插入/查询均为 O(logn) | 树高过高(百万数据需 20 层),磁盘 I/O 次数多 |
| B 树(多路平衡树) | 树高更低 | 数据分散在所有节点,范围查询需回溯;叶子节点无链表 |
B 树和 B+ 树同属'多路平衡树',但 B+ 树针对数据库的'磁盘 I/O 特性'和'业务查询场景'做了关键优化,二者的核心差异及选择 B+ 树的原因如下:
核心优势:树高越低,磁盘 I/O 次数越少(每次访问节点需一次 I/O),查询效率呈指数级提升。
id BETWEEN 100 AND 200,找到 100 后需回溯父节点,再遍历子节点,多次 I/O 且逻辑复杂;业务适配:数据库中 70% 以上的查询包含范围条件(如时间范围、价格范围),B+ 树的链表设计完美适配这一核心场景。
基于上述优势,B+ 树成为 MySQL 索引的最优选择,其核心结构如下(以 InnoDB 的 B+ 树为例):
根节点 (索引值 + 子节点指针)
中间节点 1 (索引值 + 子节点指针)
中间节点 2 (索引值 + 子节点指针)
叶子节点 1 (完整索引值 + 数据指针/数据)
叶子节点 2 (完整索引值 + 数据指针/数据)
叶子节点 3 (完整索引值 + 数据指针/数据)
叶子节点链表
id BETWEEN 100 AND 200),只需找到起始节点,沿链表遍历即可;| 维度 | InnoDB(聚簇索引) | MyISAM(非聚簇索引) |
|---|---|---|
| 叶子节点存储内容 | 主键索引:直接存储整行数据;二级索引:存储主键值 | 所有索引:存储数据的物理磁盘地址(偏移量) |
| 索引与数据的关系 | 数据即索引,索引即数据(聚簇) | 索引和数据分开存储(非聚簇) |
| 主键要求 | 必须有主键(无则自动生成隐藏主键) | 无强制主键要求 |
Memory 引擎支持哈希索引,核心特性:
WHERE id = 100)效率极致(O(1));基于 B+ 树的底层结构,MySQL 衍生出多种索引类型,不同类型适配不同的查询场景:
WHERE category_id = 1 AND price < 100);(category_id, price) 优于 (price, category_id));(a,b,c),仅查 b=1 会导致索引失效)。MATCH(content) AGAINST('MySQL 索引'));| 失效场景 | 示例 SQL | 解决方案 |
|---|---|---|
| 字段类型不匹配 | WHERE phone = 13800138000(phone 是 VARCHAR) | 加引号:WHERE phone = '13800138000' |
| 使用函数/运算 | WHERE SUBSTR(name,1,1) = '张' | 改为前缀查询:WHERE name LIKE '张%' |
| 模糊查询以%开头 | WHERE name LIKE '%张三' | 改用全文索引,或调整查询逻辑 |
| 联合索引违反最左前缀 | 索引 (a,b),查询 WHERE b = 1 | 调整查询条件(加 a)或索引顺序 |
| OR 条件包含无索引字段 | WHERE a = 1 OR c = 2(仅 a 有索引) | 给 c 加索引,或拆分为两个查询 |
| NULL 值判断(某些场景) | WHERE age IS NULL | 改用默认值(如 0)代替 NULL |
区分度 = 唯一值数量 / 总数据量(越接近 1 越好):
(a,b),无需再建 (a))。查询的所有字段均在索引中,无需回表查询数据:
-- 索引`(id, name)`,查询仅需索引即可完成(覆盖索引)
SELECT id, name FROM user WHERE id = 100;
-- 对比:查询 age 需回表(索引仅含 id,name)
SELECT id, name, age FROM user WHERE id = 100;
-- 失效:索引列参与运算
SELECT * FROM order WHERE id + 1 = 100;
-- 有效:调整为常量运算
SELECT * FROM order WHERE id = 99;
通过 EXPLAIN 命令分析 SQL 执行计划,判断索引是否生效:
-- 分析查询语句的执行计划
EXPLAIN SELECT * FROM user WHERE phone = '13800138000';
关键字段解读:
type:最优为 const(常量),其次 range(范围)、ref(索引匹配),最差 ALL(全表扫描);key:显示实际使用的索引(NULL 表示未使用索引);Extra:Using index 表示覆盖索引,Using where; Using index 为最优。底层核心:MySQL 主流索引基于 B+ 树实现,选择 B+ 树而非 B 树的核心原因是:B+ 树非叶子节点仅存索引项(树高更低、I/O 更少)、叶子节点链表适配范围查询、数据访问路径统一;
索引类型:主键索引是 InnoDB 的核心(聚簇索引),联合索引需遵循'最左前缀匹配',全文索引/空间索引为特殊场景设计;
优化关键:避免索引失效场景(函数、%开头、类型不匹配),优先创建高区分度索引,用 EXPLAIN 验证索引有效性,避免过度索引。

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 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