MySQL索引

索引是数据库查询性能的关键，合理的索引设计能大幅提升检索效率。InnoDB 将索引和数据存放在同一文件（*.ibd），MyISAM 则分开存储（索引 *.MYI，数据 *.MYD）。

适用场景：数据量超过 10 万行的表、高频 WHERE 条件查询、多表 JOIN 的关联字段、ORDER BY / GROUP BY 排序字段。索引不是越多越好，写入密集型表需权衡索引带来的查询加速与写入开销。

核心要点

MySQL索引原理与优化，包括B+树结构、索引分类、Explain执行计划、最左前缀原则

B+Tree 索引原理

B+Tree 是 MySQL InnoDB 引擎的默认索引结构，兼顾查询效率和磁盘 IO 优化。

与 B-Tree 的区别

对比项	B-Tree	B+Tree
数据存储	所有节点都存储数据	只有叶子节点存储数据
查询效率	稳定但不一定找到叶子	一定到叶子，稳定最优
范围查询	需要递归回溯	叶子节点链表，O(1)
磁盘 IO	非叶子节点也存储数据，树较高	非叶子节点只存索引，树较矮
适用场景	随机查询	范围查询 + 随机查询

查询过程

以查找 id=99 为例：

加载根节点（第1次磁盘IO），比较找到中间指针
通过指针加载中间节点（第2次磁盘IO），再比较定位
加载叶子节点（第3次磁盘IO），链表遍历找到目标

几百万数据只需 3-4 次 IO 即可找到，因为磁盘块越大、数据项越小，树的高度越低。

聚簇索引 vs 非聚簇索引

维度	聚簇索引（InnoDB）	非聚簇索引（MyISAM）
叶子节点	存储完整数据行	存储数据行地址
主键查询	直接定位	一次定位
二级索引查询	先查主键，再查数据（回表）	直接定位
插入性能	主键顺序插入最优	无影响
空间	数据紧凑，省空间	索引和数据分离

索引分类

类型	说明
主键索引	主键自带索引效果，聚簇索引
普通索引	为普通列创建的索引，非聚簇
唯一索引	列中数据唯一，性能优于普通索引
组合索引	一次性为多个字段创建索引
全文索引	用于全文检索，生产环境建议用 Elasticsearch

最左前缀原则

组合索引 (A, B, C) 会建立三个索引：(A)、(A,B)、(A,B,C)。

-- 命中索引
WHERE A = 1           -- ✅ (A)
WHERE A = 1 AND B = 2 -- ✅ (A,B)
WHERE A = 1 AND C = 3 -- ❌ 无对应索引

-- 不命中索引
WHERE B = 2          -- ❌ 无前导列
WHERE C = 3          -- ❌ 无前导列

索引失效场景：

左模糊 %xxx：无法利用 B+Tree 有序性
索引列参与运算：WHERE YEAR(create_time) = 2024
隐式类型转换：WHERE phone = 13800138000（phone 是 varchar）

Explain 执行计划

使用 EXPLAIN 分析 SQL 语句性能。

EXPLAIN SELECT * FROM table_name;
SHOW WARNINGS;  -- 查看优化后的 SQL

select_type 列

类型	说明
derived	子查询在 from 后面，生成衍生表
subquery	select 之后 from 之前的子查询
primary	最外部的 select
simple	不包含子查询的简单查询
union	使用 union 进行的联合查询

type 列（性能从优到劣）

null > system > const > eq_ref > ref > range > index > all

类型	说明
const	主键或唯一索引与常量比较，性能最优
eq_ref	多表连接，使用主键关联
ref	普通列索引查询
range	索引范围查找
index	全索引扫描
all	全表扫描，需优化

对于 SQL 优化来说，要尽量保证 type 列的值是属于 range 及以上级别。

key_len 计算

-- 字符串
char(n): n 字节
varchar(n): 3n + 2 字节（utf-8，含长度前缀）

-- 数值
tinyint: 1 字节
smallint: 2 字节
int: 4 字节
bigint: 8 字节

-- 时间
date: 3 字节
timestamp: 4 字节
datetime: 8 字节

-- 可为 NULL 字段额外 1 字节

通过 key_len 可以判断命中了联合索引中的哪几列。

extra 列

值	含义
Using index	使用覆盖索引，无需回表
Using where	使用普通索引作为查询条件
Using index condition	可用覆盖索引优化
Using filesort	文件排序，需优化
Using temporary	使用临时表，需优化
Select tables optimized away	直接在索引列上进行聚合函数操作

Trace 工具

使用 Trace 分析优化器决策：

SET SESSION optimizer_trace="enabled=on", end_markers_in_json=on;
SELECT * FROM dept;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

索引设计规范

强制规则

数据量超过 100 万行的表应考虑分表
禁止给每条记录都建索引
VARCHAR 字段建索引需指定长度
组合索引区分度高的列放左侧
禁止左模糊或全模糊查询
insert、update、delete 操作会导致数据页变化，建立索引后需评估对性能的影响