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 即可找到，因为磁盘块越大、数据项越小，树的高度越低。
以 InnoDB 默认 16KB 数据页算一笔账：非叶子节点每项存"主键(8B)+指针(6B)"共 14B，一页可容纳约 1170 个指针；叶子节点每页约存 16 行记录（按 1KB/行）。那么树高 2 层可覆盖 1170×16≈1.9 万行，树高 3 层约 2190 万行，树高 4 层可到 250 亿行——这就是百万级数据仅需 3~4 次磁盘 IO的来源：树的高度≈磁盘 IO 次数。
聚簇索引 vs 非聚簇索引
聚簇与非聚簇的对比主要发生在 InnoDB 内部：主键索引是聚簇的，叶子节点直接存完整数据行；所有二级索引都是非聚簇的，叶子节点只存主键值，查询二级索引需先拿主键再回表。
| 维度 | 聚簇索引（InnoDB 主键） | 非聚簇索引（InnoDB 二级索引） |
|---|---|---|
| 叶子节点 | 存储完整数据行 | 只存储主键值 |
| 主键查询 | 直接定位，无需回表 | 查二级索引拿到主键后需回表 |
| 每表数量 | 仅 1 个（主键即聚簇索引） | 可有多个 |
| 插入性能 | 主键顺序插入最优；随机插入可能引发页分裂 | 无影响 |
| 空间 | 数据紧凑，省空间 | 仅存索引列+主键，更小 |
MyISAM 单独说明：其所有索引（含主键）都是非聚簇，叶子节点存的是数据行的磁盘地址，因此主键查询也要先走索引再按地址取数据，不需要"回表"概念。
索引分类
| 类型 | 说明 |
|---|---|
| 主键索引 | 主键自带索引效果，是 InnoDB 唯一的聚簇索引 |
| 普通索引 | 为普通列创建的二级索引（非聚簇） |
| 唯一索引 | 列中数据唯一，除约束外可让优化器更快确定唯一性 |
| 组合索引 | 一次为多个字段创建的索引，遵循最左前缀原则 |
| 全文索引 | 用于全文检索，生产环境建议用 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 -- ✅ 用到 A 列（C 列无法用，B 列断档）
-- 不命中索引
WHERE B = 2 -- ❌ 缺少前导列 A
WHERE C = 3 -- ❌ 缺少前导列 A、BWHERE A = 1 AND C = 3 会用到 A 列索引但无法用 C 列，因为中间缺了 B 列——索引列必须连续命中，这就是"最左前缀"的由来。
索引失效场景：
- 左模糊
%xxx：无法利用 B+Tree 有序性 - 索引列参与运算：
WHERE YEAR(create_time) = 2024 - 隐式类型转换：
WHERE phone = 13800138000（phone 是 varchar）
Explain 执行计划
使用 EXPLAIN 分析 SQL 语句性能。
EXPLAIN SELECT * FROM table_name;
SHOW WARNINGS; -- 查看优化后的 SQLselect_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): n × 字符集字节数 + 2（含长度前缀）
utf8 = 3n + 2，utf8mb4 = 4n + 2（MySQL 8.0 默认 utf8mb4）
-- 数值
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 | 直接在索引列上进行聚合函数操作 |
索引下推（ICP）
索引下推（Index Condition Pushdown）是 MySQL 5.6+ 的优化：把二级索引上的 WHERE 过滤条件下推到存储引擎层，在索引遍历时就过滤掉不满足的行，减少回表次数。
组合索引 (name, age) 查询 WHERE name LIKE '张%' AND age > 30：没有 ICP 时，引擎按 张% 找到所有匹配的主键后逐个回表，再用 age > 30 过滤；有 ICP 时，引擎在索引遍历中直接用 age > 30 排除记录，只对剩余少量主键回表。EXPLAIN 的 extra 列出现 Using index condition 即表示命中了 ICP。
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 操作会导致数据页变化，建立索引后需评估对性能的影响
索引失效的本质
上述失效场景的根源是破坏了 B+Tree 的有序性前提：
- 左模糊
%xxx：B+Tree 按前缀有序排列，无法从通配符起点定位，只能全扫 - 索引列参与运算
YEAR(create_time) = 2024：引擎按原值存索引，运算后的结果无法匹配节点顺序，须对每行算完再比较 - 隐式类型转换
phone = 13800138000（phone 为 varchar）：字符串列与数字比较时被转为数字，等价于对列做函数运算
推荐规则
- 区分度最高的列放联合索引最左侧
- 避免建立过多索引，影响写入性能
- 使用
LIMIT 1优化确定只有一条记录的场景