一、起源:数据量增长后查询变慢的根因
索引不是先有结构、后有场景的技术,而是被"在磁盘上找一行数据"的固有成本逼出来的解法。理解这一点,比记住索引的种类更重要。
1.1 数据量增长后查询变慢的表现
同一张表在数据量很小时查询很快,数据量增长若干倍后同一条 SQL 变慢,这一现象通常来自三处放大。
- 单次查询访问的数据量增长
- 执行器需要判断条件的行数随表行数增长,读取的数据页随之增长。
- 数据量较小时,整张表可以全部驻留在 InnoDB 缓冲池中,访问退化为内存遍历;数据量超出缓冲池容量后,每次查询都要从磁盘补页,耗时出现台阶式上升。
- 因此同一条 SQL 在不同数据量下的表现并不可比,性能结论必须标注数据量与缓冲池容量。
- 结果集之外的无效扫描
- 没有可用索引时,条件只能作为过滤条件在读取每一行之后判断,绝大多数被读取的行最终被丢弃。
- 扫描行数与返回行数的比值(
Rows_examined与Rows_sent之比)反映了这类浪费,是慢查询排序时的关键指标之一。 - 并发下的资源占用时间
- 单次查询耗时上升会延长连接、线程与缓冲池页的占用时间,同一份资源能服务的请求数下降,在并发场景中被进一步放大。
- 慢查询与锁等待、临时表落盘、排序缓冲不足等问题相互叠加时,表现为整体吞吐下降而非单条 SQL 变慢;锁等待的成因、加锁规则与处置流程见 mysql: 事务与锁。
1.2 磁盘 IO 次数是主要成本
InnoDB 的数据以页为单位在磁盘与内存之间搬运,默认页大小 16KB。
- 一次全表扫描需要读取的页数约为
行数 × 平均行宽 ÷ 16KB,随行数线性增长;缓冲池无法容纳时,每页至少对应一次磁盘读。 - 若数据页在磁盘上的物理位置连续,读取表现为顺序 IO;若条件命中分散的行,读取表现为随机 IO。机械磁盘的随机 IO 在毫秒量级,顺序 IO 与内存访问分别低一到数个数量级;固态盘显著改善了随机 IO,但单盘 IOPS 仍有上限,随机读仍是比顺序读更贵的路径。
- 索引的作用是把"读取全部数据页后逐行比较"换成"沿树定位到少量数据页":
- 定位成本与树高成正比,而树高随行数呈对数增长;
- 千万级数据行的 B+Tree 树高通常为 3 至 4 层,即定位一行需要 3 至 4 次页访问,其中上层节点常驻缓冲池,实际磁盘读通常只有 1 至 2 次。
- 索引带来的收益可以量化为"减少的页访问次数",而不是"加了索引就快"。当条件命中的行占表比例很高时,走索引反而需要访问更多页(含回表),此时全表扫描是更优选择,优化器也正是这样判断的。
1.3 索引的基本取舍
索引以空间与写入代价换取查询效率,三类代价需要在设计时明确。
| 代价 | 来源 | 影响面 |
|---|---|---|
| 空间 | 索引页占用磁盘与缓冲池 | 缓冲池中可用于数据页的内存减少,磁盘与备份体积增加 |
| 写入 | 每次增删改都要维护全部相关索引 | 写入延迟上升,事务持锁时间变长,页分裂与碎片增加 |
| 决策复杂度 | 可选的访问路径变多 | 优化器选错路径的概率上升,且执行计划更易随统计信息波动 |
由此得出设计的边界:索引的数量由"必须被高效支撑的查询模式"决定,而不是越多越好。存在大量低频、结构各异的查询时,用索引逐一覆盖的代价会超过收益,需要回到数据模型、分页方式或异步检索方案上解决。
二、原理
本节讨论与具体版本无关的通用机制:索引的结构、数据组织方式、回表与覆盖、联合索引的匹配规则、失效情形、优化器与统计信息、索引自身的代价以及特殊索引形态。
2.1 B+Tree 结构
2.1.1 与其他索引结构的比较
| 结构 | 等值查找 | 范围查找 | 排序支持 | 空间与并发特征 | 典型用途 |
|---|---|---|---|---|---|
| 平衡二叉树(AVL、红黑树) | 比较次数 O(log n),但每次比较可能对应一次磁盘访问 | 不支持物理有序的范围扫描 | 中序遍历有序 | 每个节点只存少量键,树高随行数增长快,磁盘访问次数不可接受 | 内存中的数据结构,不作为磁盘索引 |
| 哈希 | 单次定位,等值最快 | 不支持 | 不支持 | 需要预估桶数量,扩容代价高,范围与排序无法复用 | MEMORY 引擎的哈希索引、InnoDB 自适应哈希索引 |
| 位图 | 低基数列上按位运算,组合条件快 | 支持但依赖位图压缩 | 不支持 | 更新需要调整整个位图,锁粒度受限,不适合高并发写入 | 分析型场景,MySQL 原生未提供 |
| B+Tree | 按树高定位,层高稳定 | 叶节点有序且以双向链表连接,可顺序扫描 | 按键顺序天然支持 | 非叶节点只存键与指针,扇出大,树高低且稳定 | InnoDB 与 MyISAM 的默认索引结构 |
对比的结论是:磁盘索引的首要考虑是"比较次数能否不随数据量线性增长",以及"能否同时支撑范围查询与排序"。B+Tree 用较大的扇出把树高压在很小的常数范围内,又把全部数据放在同一层的叶节点上并用链表串联,同时满足这两个要求,这是它成为默认结构的原因。
2.1.2 页与层高
- 页是 InnoDB 读写磁盘的基本单位,默认
innodb_page_size = 16KB。 - 非叶节点只保存索引键值与指向下一层的指针,不保存行数据,因此扇出很高。
- 以 BIGINT 主键(8 字节)加 6 字节页号为例,单页可容纳约 1000 个指针。
- 2 层可寻址约 1000 页,3 层可达约百万页,按每页存放约 15KB 行数据估算,可覆盖千万级以上的数据行。
- 叶节点保存实际内容:聚簇索引的叶节点是整行数据,二级索引的叶节点是索引列值与主键值。
- 层高直接影响定位成本,因此压缩单行宽度、缩短主键长度都能实打实地降低层高与 IO 次数。
- 结构维护代价:插入到已满的页会触发页分裂,把一页拆成两页并调整父节点;删除到页利用率过低会触发页合并。这两个动作都会引起额外的写入与锁开销,是随机主键与批量乱序写入变慢的原因。
2.1.3 顺序读与随机读
- 叶节点按键值有序且相邻页通过双向链表连接,范围查询(
BETWEEN、>、LIKE '前缀%'、ORDER BY扫描)可以在叶层顺序推进,读取连续或接近连续的页。 - 通过二级索引取行时需要先得到主键,再回到聚簇索引按主键查找,这一步的访问位置由主键决定,与二级索引的扫描顺序无关,因此表现为随机读。扫描的行数越多,随机读次数越多,成本增长越快。
- 顺序与随机的差异说明一件事:索引消除的是"无效的页访问",但通过二级索引读取大量行时引入的随机访问是新的成本,这正是覆盖索引与索引下推要解决的问题。
2.2 聚簇索引与二级索引
2.2.1 索引组织表
- InnoDB 的表本身就是一棵按主键组织的 B+Tree,叶节点保存完整行数据,这种组织方式称为索引组织表(index-organized table)。数据即索引,索引即数据,两者不是两份独立结构。该组织方式在存储层的实现(表空间、段区页与行格式)见 mysql: InnoDB 存储引擎。
- 因此主键不只用于唯一标识,它还决定了数据在磁盘上的物理排列顺序。
- 按主键顺序插入(自增、单调的时间序列)时,新行总是追加到最右侧页,写入接近顺序写。
- 主键随机(如随机 UUID、哈希值)时,插入位置散布在整棵树中,会持续触发页分裂并产生大量随机写。
- 没有显式主键时,InnoDB 的处理顺序为:
- 若存在非空唯一索引,选第一个作为聚簇索引;
- 都不存在时,创建一个 6 字节的隐藏列作为行标识,聚簇索引建立在该列上。
- 隐藏列不对外可见,其取值由 InnoDB 内部生成,无法用于业务查询,也无法作为二级索引的值被稳定引用。这种表的设计缺少可预测的主键,复制与数据迁移时的行定位也更困难,因此规范上要求每张表都显式定义主键。
2.2.2 二级索引的叶节点内容
- 二级索引(非聚簇索引)的叶节点保存两部分内容:索引列的值与该行主键的值。
- 保存的是主键值而不是行在磁盘上的物理地址,原因是行位置会随页分裂与整理变化,物理地址无法稳定引用。
- 由此产生两个结论:
- 主键长度会同时影响所有二级索引的体积。主键从 8 字节变为 36 字节的字符串时,每个二级索引的每条记录都要多存一份主键,索引页数、缓冲池占用与 IO 次数同步上升。
- 二级索引能够回答的问题只到"索引列 + 主键"这一层,返回其他列必须回到聚簇索引取行。
2.2.3 回表过程与代价
以如下表结构为例:
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
status TINYINT NOT NULL,
total DECIMAL(12,2) NOT NULL,
created_at DATETIME NOT NULL,
remark VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_user_status_created (user_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
查询 SELECT id, total FROM orders WHERE user_id = 100 AND status = 1; 的执行路径:
- 在二级索引
idx_user_status_created上定位user_id = 100 AND status = 1的叶节点区间,得到一批主键值; - 逐个用主键到聚簇索引查找完整行;
- 从行中取出
total返回。
第 2 步即回表,代价为:
- 每次回表都要在聚簇索引上走一次树高(通常 2 至 4 次页访问),其中上层节点常驻缓冲池,实际磁盘读取决于行是否已在缓冲池中。
- 回表次数等于命中的行数,行数多时是批量随机读。
- 索引列与查询列重叠度越高,回表读取的列越少,收益越明显;
SELECT *会把回表的收益抵消到最低。
2.2.4 二级索引与聚簇索引的对比
| 维度 | 聚簇索引 | 二级索引 |
|---|---|---|
| 叶节点内容 | 完整行数据 | 索引列值加主键值 |
| 数量 | 每表一个 | 可多个 |
| 建立依据 | 主键或首个非空唯一索引或隐藏列 | 显式创建的键 |
| 覆盖查询的能力 | 天然覆盖全部列 | 只能覆盖索引列与主键列 |
| 更新索引列的成本 | 需移动行(主键更新时) | 需删除旧条目并插入新条目 |
2.3 覆盖索引与索引下推
2.3.1 覆盖索引
- 覆盖索引指查询所需的全部列都能从索引中获得,无需回表。判定条件是查询涉及的列集合落在同一个索引的列集合之内。
- 涉及的列包括四类,容易被遗漏后两类:
SELECT中出现的列;WHERE中用于过滤的列;ORDER BY中用于排序的列;GROUP BY中用于分组的列。
- 覆盖时
Extra列显示Using index。需要注意的是,Using index只表示"不需要回表",不表示"扫描行数少",覆盖索引仍然可能扫描大量索引条目。 - 覆盖与索引列顺序的关系:覆盖只要求列被这个索引包含,不要求满足最左前缀;但能否用这个索引做定位来减少扫描量,仍然受最左前缀约束。
- 常见的应用方式是把高频查询的返回列追加进联合索引尾部,用索引体积换取消除回表;代价是索引变大、写放大增加。
2.3.2 索引条件下推
- 索引条件下推(Index Condition Pushdown,ICP,MySQL 5.6 起支持)指把
WHERE中"属于该索引的列、但不能用于确定扫描区间"的判断条件下推到存储引擎层,在读取索引条目时就完成过滤,只对通过过滤的条目回表。 - 与覆盖索引的区别:覆盖索引消除回表,ICP 不消除回表,只是减少回表次数。
- 触发条件:查询使用了该索引,且
WHERE中存在索引中列的判断条件,这些条件不足以参与定位(例如位于范围条件之后)。 - 表现为
Extra显示Using index condition。 - 示例:索引
(user_id, status, created_at),查询WHERE user_id = 100 AND created_at > '2026-01-01' AND status = 1。定位只用到user_id与created_at的范围(status位于范围列之后,无法参与定位),但status = 1可以在引擎层用索引中的status值直接过滤,从而减少回表。
2.3.3 索引合并
- 一个查询的过滤条件分散在不同索引上时,优化器可能分别使用多个索引扫描,再对主键集合求并集或交集,称为索引合并(Index Merge)。
Extra显示Using union(...)、Using intersect(...)、Using sort_union(...)。 - 常见于
OR条件与不同列的等值条件组合。 - 索引合并需要维护主键集合,成本通常高于单个复合索引,属于可用但不是首选的执行方式。出现索引合并往往意味着缺乏一条能同时覆盖这些条件的联合索引。
2.4 联合索引与最左前缀
2.4.1 最左前缀原则的准确表述
联合索引 (a, b, c) 的排序规则是:先按 a 排序,a 相同时按 b 排序,a、b 都相同时按 c 排序。因此它可以被看作一组按前缀排列的有序序列,能够被有效利用的条件必须构成一个前缀。
| 查询条件 | 能否用于定位 | 说明 |
|---|---|---|
a = 1 | 可以 | 使用第一列,定位到连续区间 |
a = 1 AND b = 2 | 可以 | 使用前两列 |
a = 1 AND b = 2 AND c = 3 | 可以 | 使用全部三列 |
a = 1 AND c = 3 | 部分可用 | 只用 a 定位,c 可用于 ICP 过滤 |
b = 2 | 不可以 | 缺少最左列,无法确定扫描区间 |
b = 2 AND c = 3 | 不可以 | 同上 |
a = 1 AND b > 2 AND c = 3 | 部分可用 | a、b 用于定位,c 不能再缩小区间,只能用于 ICP 或覆盖 |
补充两点常见误读:
- "最左前缀"限制的是定位(决定扫描区间的那部分),不限制其余列参与过滤与覆盖。
a = 1 AND c = 3仍可从该索引读取数据、仍可能被覆盖,但c无法缩小扫描区间。 - 等值条件之间是等价的:
a = 1 AND b = 2与b = 2 AND a = 1都能使用该索引,书写顺序不影响匹配。
2.4.2 范围查询后的列失效
- 当某一列使用范围条件(
>、<、>=、<=、BETWEEN、LIKE '前缀%'、IN视优化器处理而定)后,其后的列无法继续用于缩小扫描区间。 - 原因是范围条件命中的是不连续或多个区间,后续列在区间内虽有序(同一前导值内有序),但跨区间不再有序,无法构成单一的连续扫描区间。
- 结论落到设计上:范围列应放在等值列之后。若把范围列放在前面,后面的列基本失去定位能力。
2.4.3 排序与分组对索引的利用
ORDER BY 使用索引避免排序需要同时满足以下条件,Extra 中不出现 Using filesort:
- 排序涉及的所有列属于同一个索引,且构成定位用前缀之后的一段连续列;
WHERE中使用的等值条件构成该索引的前缀;- 排序方向一致(全部升序或全部降序),或与索引声明顺序(含 8.0 的降序索引)一致;
- 排序列中间不能跳过索引列。
示例(索引 (user_id, status, created_at)):
-- 可以用索引顺序直接满足排序,无需额外排序
SELECT id, created_at FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC;
-- created_at 已作为范围条件,后续列无法参与排序,出现 filesort
SELECT id, status FROM orders
WHERE user_id = 100 AND created_at > '2026-01-01'
ORDER BY status;
GROUP BY 的机制与排序相同,可以利用索引有序性避免额外的排序与临时表(避免 Using temporary)。分组列的顺序同样需要与索引前缀匹配。
2.4.4 索引跳跃扫描
- 索引跳跃扫描(Index Skip Scan,MySQL 8.0.13 起支持)针对"查询条件缺少最左列"但最左列取值范围很少的情形。
- 优化器在最左列的几个不同取值上分别做一次范围扫描,再把结果合并,等价于把"缺少最左列"用多次区间扫描绕开。
- 触发条件:最左列的可选值数量很少(需要从统计信息判断),且后续列上的条件具备足够的选择性。
- 表现为
Extra显示Using index for skip scan(EXPLAIN的 TREE 格式中标注skip scan)。 - 该特性是优化器的自选行为,不改变"最左前缀"的设计原则:依赖跳跃扫描的查询在数据分布变化、最左列取值增多后会退化为全索引扫描,因此不能把它作为索引设计的前提。
2.4.5 联合索引列顺序的确定方法
- 汇总该表需要支撑的查询模式,按它们各自的条件列集合归类。
- 把出现频率最高、选择性最好的等值条件列放在最前。
- 多个等值条件之间,把选择性更高的列放前面;顺序对定位影响不大,但会影响跳跃扫描与部分覆盖场景。
- 等值条件之后放范围条件列。
- 若存在排序或分组需求,把排序或分组列接在等值条件列之后,让索引顺序直接满足排序。
- 检查是否能把高频查询的返回列追加到索引尾部以形成覆盖。
- 核对是否与既有索引构成前缀关系,避免冗余。
2.5 索引失效的常见情形
"失效"在实践中包含两种表现:索引完全不被使用(type = ALL),以及索引被使用但扫描量远超预期(key 命中而 rows 很大)。两者的定位方式不同。
| 情形 | 原因 | 表现与规避 |
|---|---|---|
| 隐式类型转换 | 列类型与比较值类型不一致,MySQL 把列转成另一类型比较,等价于对列施加了函数 | WHERE phone = 13800138000(phone 为字符串列)会使索引失效。规避方式是把字面量的类型写成与列一致(加引号) |
| 对列使用函数 | 索引按列的原始值排序,函数结果的顺序与原始值无关 | WHERE YEAR(created_at) = 2026、WHERE UPPER(name) = 'ABC' 无法定位。改写成范围条件(created_at >= '2026-01-01' AND created_at < '2027-01-01')或使用 8.0 的函数索引 |
| 列参与运算 | 同上,运算结果的顺序不可预测 | WHERE id + 1 = 100 改为 WHERE id = 99 |
| 前导通配的模糊匹配 | 通配符在最左侧时无确定前缀,无法确定扫描起点 | LIKE '%关键字'、LIKE '%关键字%' 不可定位,LIKE '关键字%' 可用。改写方案见 3.4.6 |
| 字符集或排序规则不一致 | 连接两侧的列字符集或排序规则不同,比较前需要转换,无法直接使用索引 | 常见于 utf8 与 utf8mb4 混用的表。统一表与列字符集、统一连接字符集 |
OR 条件混用索引列与非索引列 | OR 要求两侧都能独立求出结果集,一侧无法使用索引时整体退化为全表扫描 | 改为 UNION ALL 分别查询,或为另一侧也建立索引使其可走索引合并 |
| 否定条件 | !=、<>、NOT IN、IS NOT NULL 等在数据选择性强时无法有效缩小范围 | 视选择性而定,rows 接近总行数时优化器会直接选择全表扫描 |
| 条件选择性差 | 命中的行占比很高,回表成本超过全表扫描 | 这类"失效"是优化器的正确决策,应调整索引顺序或改变查询方式,而不是强制使用索引 |
| 统计信息偏差 | rows 估算与真实值差异过大,导致代价计算错误 | 见 2.6 与 3.6.4 |
| 索引列上存在类型或排序规则的可空性差异 | 参与连接的列可空性不同会影响连接方式与索引使用 | 设计中统一可空性 |
关于 IN 的说明:IN 在优化器中按等值展开,可以用于定位;但取值数量较多时优化器可能改为按索引区间逐次扫描或放弃索引,rows 会按取值数量放大。
2.6 优化器与统计信息
2.6.1 基于代价的优化
- MySQL 使用基于代价的优化器(Cost-Based Optimizer)。对每个候选执行计划,估算其 I/O 代价与 CPU 代价,取总代价最低者。
- 代价模型的可调参数存放在系统库的
engine_cost与server_cost表中,可按存储介质特性调整,但正态调优应以结构与统计信息为主。 - 估算依赖统计信息,包括表的行数、索引的基数(
Cardinality,即不同值的数量)、数据页数以及可选的直方图。 - 估算结果是近似值,尤其对多条件组合按独立性假设相乘计算,条件之间实际相关时偏差会放大。
2.6.2 统计信息的采集与更新
-- 触发统计信息重新采集
ANALYZE TABLE orders;
-- 查看索引统计信息(持久化在系统库中)
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'orders';
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'orders';
-- 查看索引基数(Cardinality 为估算值)
SHOW INDEX FROM orders;
-- MySQL 8.0 起可为列建立直方图,改善非索引列与倾斜分布的选择性估算
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, user_id WITH 64 BUCKETS;
SELECT * FROM information_schema.column_statistics WHERE table_name = 'orders';
- InnoDB 默认以随机采样方式估算索引基数,采样页数由
innodb_stats_persistent_sample_pages控制,采样页数越少,估算越快但波动越大。 innodb_stats_persistent控制统计信息是否持久化。持久化后统计信息存放在系统库中,重启不会丢失,避免每次重启后执行计划抖动。innodb_stats_auto_recalc控制表中数据变化超过一定比例(约 10%)时自动重新采集统计信息。大批量导入后自动采集可能尚未触发,需要手工执行。innodb_stats_transient_sample_pages用于非持久化模式下的采样页数。- 统计信息是快照,与真实分布之间总有差距。数据分布高度倾斜(少数值占绝大多数行)时,基数估算会显著偏离,直方图是目前改善这一问题的主要手段。
2.6.3 索引选择偏差的原因
- 采样与估算误差:基数由采样得出,页数由行宽推算,二者都是近似值。
- 独立性与均匀性假设:优化器按"各条件相互独立、值在范围内均匀分布"估算,实际数据往往同时违反这两条。
- 回表代价估算粗略:回表带来的随机读代价难以精确建模,优化器可能高估或低估走二级索引的总成本。
- 分布倾斜:某个值占表 90% 的行时,按基数平均估算出的
rows会严重偏低或偏高。 - 前缀索引的基数不准:前缀索引无法反映完整列的区分度,估算偏差更大。
- 统计信息陈旧:大批量写入、数据归档、分区切换后未重新采集。
2.6.4 优化器提示与执行计划的固化
-- 建议使用某个索引,优化器仍可选择不用
SELECT * FROM orders USE INDEX (idx_user_status_created) WHERE user_id = 100;
-- 强制使用某个索引
SELECT * FROM orders FORCE INDEX (idx_user_status_created) WHERE user_id = 100;
-- 忽略某个索引
SELECT * FROM orders IGNORE INDEX (idx_user_status_created) WHERE user_id = 100;
-- 固定连接顺序,按 SQL 中表的书写顺序决定驱动表
SELECT STRAIGHT_JOIN u.id, o.total FROM users u JOIN orders o ON o.user_id = u.id;
-- MySQL 8.0 起可在语句级临时改变优化器开关
SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */ * FROM orders WHERE user_id = 100;
高危:使用 FORCE INDEX、USE INDEX、STRAIGHT_JOIN 或优化器提示会把一次性的判断固化为硬编码。数据分布、数据量或表结构发生变化后,被强制的执行计划可能从最优变为最差,且不会随统计信息自动纠正。使用前确认该语句的访问模式长期稳定,并在变更后重新评审。兜底方式:提示的移除是语句级改动,可以随代码发布快速回退;建议用灰度开关或配置控制提示的启用,先在一小部分流量上验证执行计划与延迟,保留原语句以便回退;不得用提示掩盖统计信息或结构设计上的问题。
2.6.5 执行计划缓存
- MySQL 自 8.0 起移除了查询缓存(Query Cache),此前版本中"缓存 SQL 文本与结果集"的机制不再存在,因此不存在查询缓存失效引起的性能波动问题。
- 预处理语句(
PREPARE)与二进制协议调用会保存已优化的计划。MySQL 8.0 中的预处理语句在每次执行时都可能重新评估是否需要重新优化,具体取决于语句形态与参数传递情况。 - 计划缓存的影响是"同一 SQL 文本在不同参数下的表现可能不同":参数决定了选择性,而计划可能在首次执行时按另一组参数确定。定位这类问题时需要查看
EXPLAIN FOR CONNECTION <id>,观察正在执行语句实际的计划。
2.7 索引的代价
2.7.1 写入放大与页分裂
- 一次
INSERT需要写聚簇索引一次,并对涉及的每个二级索引各写一次,写入量随索引数量线性增长。 - 一次
UPDATE若修改了索引列,实际是"在索引中删除旧条目并插入新条目",代价高于同列的非索引字段更新。 - 随机位置插入会触发页分裂:页被拆成两页并向上调整父节点,放大写入量并降低页利用率,进而增大后续读取的页数。
- 非唯一二级索引的写入可以借助写缓冲(Change Buffer)延迟合并到磁盘,把随机写转为批量写,由
innodb_change_buffer_max_size控制占用比例。唯一索引必须读取页确认唯一性,无法使用这一优化,这是唯一索引写入略慢于普通索引的原因。
2.7.2 索引数量的影响
| 影响面 | 表现 |
|---|---|
| 写入延迟 | 每条写入语句需要维护全部索引,索引越多单次写入越慢 |
| 缓冲池占用 | 索引页与数据页竞争缓冲池,索引过多会挤压数据页的缓存空间 |
| DDL 时长 | 建表、加列、重建表涉及全部索引,索引越多 DDL 越慢 |
| 备份与存储 | 备份体积与恢复时间增加 |
| 优化器决策空间 | 候选计划增多,选择错误的风险上升 |
2.7.3 冗余与重复索引的识别
-- 找出可被其他索引替代的冗余索引
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'mydb';
-- 找出自上次统计重置以来从未被使用过的索引
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';
-- 重置使用计数,便于按周期观察(8.0 起索引使用统计持久化在 performance_schema 中)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND object_name = 'orders';
- 前缀冗余:索引
(a)与(a, b)同时存在时,(a)可被(a, b)的前缀覆盖,属于冗余。 - 反向不成立:
(a, b)不能替代(b),因为(b)无法被(a, b)的最左前缀利用。 - 尾部冗余:InnoDB 的二级索引会自动附加主键列,因此显式声明
(user_id, id)与(user_id)在存储上等价,前者属于冗余。 schema_unused_indexes的结果需要结合业务周期判断:报表、月度任务、灾备切换验证路径使用的索引可能在一个观察周期内不出现,直接删除会造成后续查询退化,需按 3.5 的流程处理。
2.7.4 选择性的衡量
- 选择性(区分度)指索引列上不同值的数量与总行数之比,越接近 1 说明区分度越高,越适合作为前导列。
-- 单列区分度估算
SELECT COUNT(DISTINCT user_id) / COUNT(*) AS selectivity_user_id FROM orders;
-- 联合列与最左列的组合区分度,用于比较列顺序的影响
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity_status,
COUNT(DISTINCT user_id, status) / COUNT(*) AS selectivity_user_status
FROM orders;
- 联合索引的整体区分度由全列组合决定,前导列主要影响"能缩小多少扫描区间",后续列影响"能过滤掉多少行"。
- 选择性与分布形态共同决定结论:某列区分度很高但查询值恰好落在占 90% 行的那个值上时,该查询的选择性依然很差。
2.8 特殊索引
2.8.1 唯一索引与主键
- 主键是唯一索引的特例:主键约束要求唯一且非空,且每表只能一个;唯一索引允许存在多个
NULL值(NULL不参与唯一性比较),因此"唯一索引保证列唯一"的说法需要补充"允许空值"这一限定。 - 唯一约束的检查需要在写入时读取目标页确认是否存在重复,因此唯一索引无法使用写缓冲优化。
- 主键除唯一性外还承担数据组织职责(见 2.2.1),因此主键的选择标准比普通唯一索引更严格。
2.8.2 前缀索引
-- 对长字符串列取前 10 个字符建索引
ALTER TABLE orders ADD KEY idx_remark_prefix (remark(10));
-- 比较前缀长度与完整列区分度的差距
SELECT COUNT(DISTINCT LEFT(remark, 10)) / COUNT(*) AS s10,
COUNT(DISTINCT LEFT(remark, 20)) / COUNT(*) AS s20,
COUNT(DISTINCT remark) / COUNT(*) AS sfull
FROM orders;
- 适用场景:列的取值很长(如 URL、备注文本、地址),完整列建索引体积过大。
- 代价与限制:
- 区分度低于完整列,扫描行数可能偏多;
- 无法用于覆盖索引,因为索引中不含完整值,返回该列或比较完整值都需要回表核对;
- 无法用于
ORDER BY与GROUP BY(按前缀排序与按完整值排序结果不同); - 无法用作唯一索引来替代完整列的唯一性约束。
- 前缀长度的确定方法:按区分度接近完整列的最小长度取值,同时兼顾索引体积。
2.8.3 函数索引
-- MySQL 8.0.13 起支持函数索引,可直接为表达式建立索引
ALTER TABLE orders ADD KEY idx_created_month ((CAST(DATE_FORMAT(created_at, '%Y%m') AS CHAR(6))));
-- 查询中使用同样的表达式才能命中
SELECT COUNT(*) FROM orders
WHERE CAST(DATE_FORMAT(created_at, '%Y%m') AS CHAR(6)) = '202609';
- 函数索引的实现基于隐藏的虚拟列加普通索引,8.0.13 起还支持直接使用函数键部分(functional key parts)。
- 使用限制:查询中的表达式必须与索引定义的表达式完全一致才能被使用;表达式需满足确定性要求,不能引用非确定性函数。
- 优先选择改写成范围条件。只有在无法改写(如表达式由框架统一生成)时才使用函数索引,因为表达式一旦调整,索引即失效。
2.8.4 全文索引
- 全文索引基于倒排结构,用于按词检索文本内容,通过
MATCH ... AGAINST使用,支持自然语言模式与布尔模式。 - 与 B+Tree 的分工:前缀匹配(
LIKE '前缀%')用 B+Tree,词级别的包含检索(LIKE '%词%')用全文索引。 - 关键参数:
innodb_ft_min_token_size限定最小词长,短于阈值的词不建索引;停用词表通过innodb_ft_server_stopword_table指定。 - 中文分词需要配置
ngram解析器,并按业务设定ngram_token_size。 - 全量建索引与增量维护的开销都较高,写入频繁的宽文本列需要评估维护成本。
2.8.5 空间索引
- 空间索引使用 R-Tree 结构,支持 GIS 数据类型的邻近查询与包含关系查询,要求索引列声明为空间数据类型(如
GEOMETRY、POINT),且列必须为NOT NULL。 - 结构上与 B+Tree 不同,适用于按地理范围过滤的场景。
- 与普通索引混用时,优化器可能同时使用空间索引与普通索引做索引合并,是否能合并需要看具体条件形态。
2.8.6 不可见索引
-- 将已有索引置为不可见,优化器不再选择,但索引仍被正常维护
ALTER TABLE orders ALTER INDEX idx_user_status_created INVISIBLE;
-- 恢复可见
ALTER TABLE orders ALTER INDEX idx_user_status_created VISIBLE;
-- 允许优化器考虑不可见索引(默认关闭)
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
- 用途:在删除索引之前做安全验证。先置为不可见并观察一个完整的业务周期,若没有任何查询退化,说明该索引确实无用,可以安全删除;若出现退化,改回可见即可立即恢复,无需重建索引。
- 约束:不可见索引仍会带来写入与空间代价,不能作为长期状态。
- 主键索引不能设为不可见。
三、实现
本节给出命令层面的判读方法与落地规范:执行计划的两种读法、从慢日志到执行计划的定位路径、索引设计与评审规范、典型场景的改写方式、索引变更的运维流程,以及常见问题的处置顺序。
3.1 EXPLAIN 判读
3.1.1 各列含义
EXPLAIN SELECT id, total FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC;
+----+-------------+--------+------------+------+-----------------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_user_status_created | idx_user_status_created | 9 | const,const | 12 | 100.00 | Backward index scan |
+----+-------------+--------+------------+------+-----------------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| 列 | 含义 | 判读要点 |
|---|---|---|
id | 查询块编号,数值越大越先执行 | 子查询与联合查询中用于判断执行次序 |
select_type | 查询块类型 | SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、MATERIALIZED |
table | 该行对应的表 | 派生表显示为 <derivedN> |
partitions | 命中的分区 | 分区剪枝是否生效据此判断 |
type | 访问类型,反映连接方式 | 核心判读列,见下表 |
possible_keys | 可供选择的索引 | 为空说明没有可用索引,需要检查条件写法 |
key | 实际选择的索引 | 与预期不符时进入索引失效排查 |
key_len | 使用的索引键长度(字节) | 用于推断联合索引用到了前几列 |
ref | 与索引列比较的对象 | const 表示常量,列名 表示连接列 |
rows | 估算需要扫描的行数 | 是估算值,需与 EXPLAIN ANALYZE 的实际值对照 |
filtered | 过滤后剩余行的百分比 | 与 rows 相乘得到估算的参与后续连接的行数 |
Extra | 附加信息 | 覆盖、下推、排序、临时表等关键信息在此 |
type 由优到劣的顺序:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge
> unique_subquery > index_subquery > range > index > ALL
type | 含义 | 是否可接受 |
|---|---|---|
system、const | 常量表,最多一行 | 优 |
eq_ref | 连接时被驱动表按唯一索引取值,一行对应一行 | 优 |
ref | 按非唯一索引的等值查找 | 良好 |
range | 按索引区间扫描 | 良好,需核对区间宽度 |
index | 全索引扫描 | 关注,需确认是否可以改为 range |
ALL | 全表扫描 | 大数据量下不可接受,需排查原因 |
key_len 的估算方法:把联合索引中实际用到的列的长度相加,可空列额外加 1 字节作为空值标志。以 idx_user_status_created (user_id BIGINT UNSIGNED NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL) 为例:
key_len | 使用的列 | 计算 |
|---|---|---|
| 8 | user_id | 8 |
| 9 | user_id、status | 8 + 1 |
| 14 | user_id、status、created_at | 8 + 1 + 5 |
key_len 比预期小,说明联合索引的后缀列未参与定位,通常由条件缺失或范围条件截断引起。
3.1.2 判读顺序
- 看
type:是否为ALL或index;连接场景下被驱动表是否为eq_ref或ref。 - 看
key与possible_keys:是否选用了预期的索引;possible_keys为空说明索引无法被使用,属写法问题。 - 看
key_len:确认联合索引用到了第几列,定位范围条件截断的位置。 - 看
rows与filtered:估算扫描量是否可接受,rows × 下一行 rows的量级决定了连接成本。 - 看
Extra:是否回表、是否排序、是否使用临时表。
3.1.3 常见 Extra 信息的含义
Extra | 含义 | 处理方向 |
|---|---|---|
Using index | 覆盖索引,不需回表 | 良好;仍需核对扫描行数 |
Using where | 读取后还需在服务层过滤 | 正常,但需确认过滤掉的比例 |
Using index condition | 索引条件下推生效 | 良好,回表次数已减少 |
Using index for skip scan | 索引跳跃扫描生效 | 良好但依赖分布,不宜作为设计前提 |
Using filesort | 无法用索引顺序满足排序,需要额外排序 | 检查排序列与索引前缀的关系;文件排序不一定落盘,但代价不可忽略 |
Using temporary | 需要临时表 | 常见于 GROUP BY、DISTINCT、UNION、派生表;核对是否可用索引有序性消除 |
Using join buffer (hash join) | 被驱动表无可用索引,使用哈希连接 | 为连接列补索引通常优于依赖哈希连接 |
Using join buffer (Block Nested Loop) | 被驱动表无可用索引,使用块嵌套循环 | 同上,且随数据量增长退化明显 |
Using MRR | 使用多范围读,把回表的随机读合并为顺序读 | 良好,需确认 optimizer_switch 中 mrr 已开启 |
Select tables optimized away | 查询可由索引元数据直接回答 | 优 |
Impossible WHERE | 条件恒不成立 | 检查条件逻辑 |
Backward index scan | 反向扫描索引(8.0 起支持降序与反向扫描) | 正常,用于 ORDER BY ... DESC |
3.1.4 EXPLAIN FORMAT=JSON 与 EXPLAIN ANALYZE 的差异
-- JSON 格式:暴露代价估算、使用的列、下推条件等细节
EXPLAIN FORMAT=JSON
SELECT id, total FROM orders WHERE user_id = 100 AND status = 1;
-- 查看正在执行的语句的计划
EXPLAIN FOR CONNECTION 12345;
JSON 输出的关键字段:
| 字段 | 含义 |
|---|---|
query_cost | 整个查询的估算代价 |
read_cost、eval_cost | 读取与计算部分的估算代价 |
rows_examined_per_scan、rows_produced_per_join | 每次扫描与参与连接的行数估算 |
used_columns | 该步骤实际需要的列,用于判断覆盖 |
attached_condition | 下推到该步骤的条件 |
index_condition | 通过索引条件下推使用的条件 |
using_index、using_filesort、using_temporary_table | 与文本格式的 Extra 对应 |
-- 实际执行并输出每一步的真实耗时与行数(MySQL 8.0.18 起支持)
EXPLAIN ANALYZE
SELECT id, total FROM orders WHERE user_id = 100 AND status = 1;
-> Index lookup on orders using idx_user_status_created (user_id=100, status=1) (cost=8.35 rows=12) (actual time=0.052..0.071 rows=11 loops=1)
三种用法的分工:
| 形式 | 是否执行语句 | 给出内容 | 适用场景 |
|---|---|---|---|
EXPLAIN | 不执行 | 估算行数、访问类型、附加信息 | 日常评审与快速判读 |
EXPLAIN FORMAT=JSON | 不执行 | 估算代价构成、使用的列与下推条件 | 需要判断优化器代价计算依据时 |
EXPLAIN ANALYZE | 实际执行 | 真实耗时、真实行数、迭代次数 | 估算与实际偏差大、需要量化每一步耗时时 |
高危:EXPLAIN ANALYZE 会真实执行语句。对多表 UPDATE、DELETE 使用时会实际修改数据,且没有事务保护之外的任何拦截。执行前确认语句是只读的 SELECT,或在只读副本上执行;确需对写语句使用时应显式包裹在事务中并在查看结果后回滚,且提前确认目标不涉及触发器与级联操作。兜底方式:写语句的分析优先在从库或预发环境执行;一旦误执行,只能依赖事务回滚或从备份恢复,因此执行前需确认回滚路径可用。
EXPLAIN ANALYZE 的判读要点:
- 比较
rows(估算)与actual ... rows(实际),偏差数量级较大说明统计信息不可靠,需要重新采集或建立直方图; - 关注
actual time的起止值,第一步与后续步骤之间是否出现明显台阶,台阶处即主要耗时; loops大于 1 时,实际总耗时为单次耗时乘以迭代次数;- 出现
never executed说明该分支未被执行,估算与真实路径发生了分歧。
3.2 慢查询定位流程
3.2.1 从慢日志到执行计划
慢查询日志的开启方式、long_query_time 阈值设置与日志文件位置参见 mysql: 使用命令 的日志部分,本节不重复命令,只给出定位流程。
- 确认慢日志已开启且阈值合理:
long_query_time取值过小会把正常查询写入日志造成噪声,过大则漏掉需要优化的语句;建议与业务延迟目标对齐。 - 聚合而不是逐条查看:慢日志条数多时先做聚合,按
Query_time总和、出现次数、Rows_examined与Rows_sent比值排序。 - 提取 SQL 模板:把同类语句归一为模板(常量替换为占位符),同一模板的参数差异会造成执行计划差异,需要分别验证极值参数。
- 获取执行计划:对模板做
EXPLAIN,必要时对具体参数值做EXPLAIN ANALYZE或查看EXPLAIN FOR CONNECTION。 - 判定问题类型:无可用索引、索引未被选中、回表过多、排序或临时表、扫描行数与返回行数比例失衡。
- 改写或调整索引:优先改写 SQL 让其可用既有索引,其次调整索引;两者都不可行时再评估结构与数据模型。
- 验证与回归:在同一数据量与同一参数下对比优化前后的
EXPLAIN与实测耗时;数据量差异会使结论失真。
聚合慢日志的常用手段(mysqldumpslow 随 MySQL 分发,pt-query-digest 属 Percona Toolkit,使用前需确认已安装):
# 按总耗时排序输出前 10 条模板
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# 按扫描行数排序,定位"扫描多而返回少"的语句
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log
Rows_examined 与 Rows_sent 的比值是重要线索:比值很大说明扫描了大量无效行,属于索引问题;两者接近但耗时仍长,说明命中行数本身很多,属于查询语义或分页方式问题。
3.2.2 压测与基线对照
索引调整属于变更,其效果需要以可对比的测量为依据,而不是以单次观察为准。压测方法、基线采集与回归对比的完整流程参见 linux: 压测与性能基线,在数据库场景下的对应关系如下。
| 基线要素 | 数据库侧对应内容 |
|---|---|
| 固定条件 | 数据量与数据分布、缓冲池容量与预热状态、并发数、连接池配置、是否开慢日志 |
| 采集指标 | QPS 与吞吐、延迟分位(关注 P95、P99)、Rows_examined 均值、慢查询条数、锁等待与临时表落盘次数 |
| 可控变量 | 一次只改一项(一条索引或一条 SQL),其余条件与基线采集时保持一致 |
| 环境差异 | 测试数据的分布与生产不同会放大或掩盖索引收益,压测结果只用于同环境内的相对对比 |
| 归档 | 保留变更前后的执行计划、索引定义、数据量、实测指标与执行时间 |
需要特别注意的是数据库侧的"缓存状态"变量:缓冲池的预热程度、索引变更后数据的物理分布、统计信息的采集时点都会显著影响结果。索引相关变更的对比至少包含三类数据:变更前后的执行计划(EXPLAIN 或 EXPLAIN ANALYZE 输出)、变更前后的实测耗时与扫描行数、变更期间的资源指标(IO、CPU、锁等待)。
3.3 索引设计与评审规范
3.3.1 主键选择
| 准则 | 说明 |
|---|---|
| 类型短 | 主键会被附加到每个二级索引的叶节点,长度直接影响所有索引的体积 |
| 单调递增 | 自增整数或时间序列表现在写入接近顺序写,避免页分裂 |
| 无业务含义 | 业务字段可能被修改,主键更新会导致行迁移与全部二级索引重建 |
| 不使用随机值 | 随机 UUID 或哈希值造成随机插入与大量页分裂,如必须使用应转为有序形式(如时间前缀加随机后缀) |
| 显式声明 | 避免依赖隐藏列作为聚簇索引,保证行定位与数据迁移的可预测性 |
3.3.2 联合索引列顺序的确定方法
- 列出该表所有高频查询模式,标注每个模式的条件列、排序分组列与返回列。
- 以"等值条件列在前、范围条件列在后"为基本排序。
- 等值条件列内部按选择性从高到低排列,选择性由
COUNT(DISTINCT col) / COUNT(*)估算;同时核对最频繁出现的列是否应作为最左列。 - 排序或分组列接在等值条件列之后,检查排序方向与索引声明是否一致。
- 判断是否追加返回列形成覆盖索引,权衡索引体积与回表收益。
- 与既有索引对比,确认不构成前缀冗余,也不存在可被替代的旧索引。
3.3.3 区分度评估
- 前导列应具备足够区分度,否则索引无法有效缩小扫描区间。
- 组合区分度需要整体评估:单列的区分度都低时,联合索引的前缀可能依然无法缩小范围。
- 分布比平均值更重要:某列上存在占比极高的热点值时,应把该列放在联合索引的后续位置,由更有区分度的列做前导。
3.3.4 避免冗余索引
- 前缀冗余、尾部冗余与功能重复(如同一列既有普通索引又有唯一索引)都属于冗余。
- 定期用
sys.schema_redundant_indexes与sys.schema_unused_indexes做交叉比对,结合业务周期判断后再处理。 - 删除索引前必须经过 3.5 的不可见验证流程。
3.3.5 上线前的执行计划评审检查清单
| 检查项 | 判据 |
|---|---|
| 访问类型 | 关键查询的 type 应达到 range、ref 或更好;不出现大表上的 ALL 与 index |
| 索引选择 | key 为预期索引;possible_keys 不为空 |
| 索引长度 | key_len 与预期使用的列数一致,无范围条件截断意外 |
| 扫描量 | rows 在可接受范围;并发场景下 rows 与调用频率的乘积可承受 |
| 连接方式 | 被驱动表使用 eq_ref 或 ref,不依赖 Using join buffer |
| 排序与临时表 | 关键查询不出现 Using filesort 与 Using temporary,或已确认其数据量可控 |
| 覆盖情况 | 高频查询是否需要 Using index,回表次数是否可接受 |
| 隐式转换 | 对比列的声明类型与查询中的字面量、连接列类型 |
| 字符集一致 | 表、列、连接及连接两侧的字符集与排序规则统一 |
| 估算偏差 | 必要时用 EXPLAIN ANALYZE 核对估算与实际行数 |
| DDL 影响 | 新增或删除索引的表大小、DDL 预计时长、锁级别与执行窗口 |
| 回滚方案 | 索引变更是否可逆,回滚需要多长窗口 |
3.4 典型场景优化
3.4.1 深分页
-- 问题写法:需要扫描并丢弃前 100000 行
SELECT id, total FROM orders ORDER BY id LIMIT 100000, 20;
-- 方案一:延迟关联,先在覆盖索引上定位主键,再按主键取行
SELECT o.id, o.total FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id = t.id;
-- 方案二:游标分页,用上次返回的最后一个主键作为下一页起点
SELECT id, total FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
- 问题根源:
LIMIT offset, size需要先产生 offset + size 行再丢弃前 offset 行,偏移量越大成本越高,与索引是否覆盖无关。 - 延迟关联的收益来自子查询中只读取主键,扫描发生在二级索引或覆盖索引上,避免了大偏移量下的回表浪费。
- 游标分页把偏移量换成"上一页的边界值",成本恒定,是唯一能长期承受大偏移量的方式;代价是无法直接跳到任意页,需要前端交互配合。
- 若必须支持随机跳页,可把深翻页限制在有限范围(如最大允许 100 页),超出后要求用户缩小筛选条件。
3.4.2 范围查询与排序
- 排序列必须位于范围条件列之前才能使用索引排序;排序列在范围列之后时,索引无法同时满足过滤与排序。
- 常见处理方式:
- 把范围条件改为等值条件加固化取值(如按日期分区或按天枚举),使排序列回到等值前缀之后;
- 为"过滤 + 排序"专门建立联合索引,接受另一个查询模式上的退化;
- 接受排序成本,控制返回行数(限制页码与条数),使文件排序的数据量可控。
- 排序缓冲由
sort_buffer_size控制,是会话级参数。增大缓冲只在排序数据量不大时有效,属于缓解手段而非解决方案。
3.4.3 JOIN 的驱动表与索引要求
EXPLAIN SELECT u.id, o.total
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.status = 1;
- 嵌套循环连接的基本要求:被驱动表的连接列必须有索引,否则每次外层取一行都要扫描一遍内层表,代价随行数乘积增长。
- 驱动表的选择原则是让外层产生的结果集尽量小。优化器通常自动选择,
STRAIGHT_JOIN可以强制顺序,属于 2.6.4 所述的高危手段。 - 连接算法的识别与条件:
| 算法 | 出现的条件 | Extra 表现 |
|---|---|---|
| 嵌套循环连接(NLJ) | 被驱动表连接列有可用索引 | 被驱动表行显示 ref 或 eq_ref |
| 块嵌套循环(BNL) | 被驱动表无可用索引,老版本或哈希连接未启用 | Using join buffer (Block Nested Loop) |
| 哈希连接 | MySQL 8.0.18 起,等值连接且被驱动表无可用索引时 | Using join buffer (hash join) |
- 哈希连接在无索引的连接上优于块嵌套循环,但不能替代索引:连接列仍适合建立索引,尤其是被驱动表数据量大时。
- 连接查询中被驱动表的
rows与外层rows相乘才是总扫描量,评估连接成本时不能只看单表。
3.4.4 子查询与临时表
IN与EXISTS子查询在 MySQL 8.0 中会经过半连接(semijoin)转换,可能被改写为连接、物化或FirstMatch形式。可通过EXPLAIN的select_type观察是否出现MATERIALIZED、DEPENDENT SUBQUERY等。- 派生表默认会被合并(derived merge)进外层查询;含聚合、
LIMIT、UNION等无法合并的派生表会被物化。物化后无法再使用外层索引,需要核对是否引入了额外扫描。 - 出现
Using temporary的常见原因与处置:
| 原因 | 处置方向 |
|---|---|
GROUP BY 列顺序与索引不匹配 | 调整索引列顺序或分组列顺序,使分组可利用索引有序性 |
DISTINCT 作用在大结果集上 | 评估业务是否真的需要去重,或缩小结果集后再去重 |
UNION 需要去重 | 确认是否可用 UNION ALL 替代(不需要去重的场景) |
| 排序或分组的数据量超过内存临时表上限 | 由 tmp_table_size、max_heap_table_size 与 8.0 的 TempTable 引擎参数决定是否落盘;落盘会带来磁盘 IO |
| 派生表无法合并 | 改写为可合并形式,或把中间结果落为实体表并建立索引 |
- 底层临时表参数属于 mysql: 关于 MySQL 服务配置 的范围,调大这些参数只是缓解落盘,不能消除产生临时表的根本原因。
3.4.5 计数与聚合
COUNT(*)无需读取行内容,InnoDB 会选择体积最小的二级索引做全索引扫描,因此建立一条窄的二级索引可以加速计数。COUNT(col)只统计非空值,与COUNT(*)语义不同,需要按业务含义选择。- InnoDB 不维护表的总行数,
COUNT(*)的成本随表增长;EXPLAIN的rows与SHOW TABLE STATUS的Rows都是估算值,不能用于精确计数。 - 需要高频精确计数的场景,通常采用计数器表维护(在事务中增减),或使用近似统计并向业务说明误差范围。
- 聚合查询尽量让其可利用索引的有序性(避免
Using temporary与Using filesort);对固定口径的统计可考虑预聚合的汇总表,按周期刷新。
3.4.6 模糊匹配场景的替代方案
| 匹配形态 | 示例 | 可用手段 |
|---|---|---|
| 前缀匹配 | LIKE 'abc%' | B+Tree 索引可直接使用,作为首选 |
| 后缀或包含匹配 | LIKE '%abc'、LIKE '%abc%' | 有限前缀无法定位,需要替代方案 |
| 固定位置匹配 | LIKE 'ab_%' | 前缀可用于定位,通配符位置之后的比较无法用索引 |
替代方案与取舍:
- 全文索引:适用于按词检索,支持自然语言与布尔模式;中文需配置
ngram解析器;词最小长度与停用词会影响召回。 - 冗余反转列:额外维护一列保存反转后的字符串,把后缀匹配转成前缀匹配;适用于固定的后缀查询,代价是维护额外列。
- 冗余拼音或编码列:把检索维度固化为额外列并建立前缀索引,适用于固定的检索方式。
- 外部检索组件:把包含匹配、分词与相关性排序交给专门的检索系统,数据库只保留主键回查。适用于检索需求复杂、数据量大的场景,代价是引入同步链路与一致性窗口。
- 改写条件:能拆成前缀条件的(如编号、日期、手机号段)尽量改写,避免通配符出现在最左侧。
3.5 索引变更的运维
3.5.1 创建与删除索引对在线业务的影响
- MySQL 8.0 起,添加与删除普通二级索引支持
ALGORITHM=INPLACE且LOCK=NONE,即不阻塞并发读写。算法与锁的完整语义、元数据锁的阻塞链路以及变更窗口与回滚设计见 mysql: 表结构变更与运维治理。
-- 显式声明在线算法与不锁表,若目标不支持则会直接报错而不是退化为锁表
ALTER TABLE orders
ADD KEY idx_status_created (status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;
- 即使不锁表,DDL 仍有其他影响:
- 需要扫描全表构建索引,产生大量顺序读与排序,消耗 IO 与 CPU;
- 构建过程中的索引页写入会污染缓冲池,挤占业务数据页;
- 需要短暂获取元数据锁(MDL),长事务或长查询持有 MDL 时 DDL 会等待,DDL 等待期间后续查询可能被阻塞,形成排队;
- 主库执行的 DDL 会在从库重放,单线程重放 DDL 期间从库延迟增加,读从库的业务可能受影响;
- 大表构建索引可能生成与索引体积相当的临时文件,需确认磁盘余量。
- 删除索引同样需要修改表定义,代价主要来自元数据操作与后续计划变化,而非数据重写。真正的风险是删除后查询退化。
3.5.2 在线 DDL 的注意点
- 确认 DDL 算法与锁级别:显式写出
ALGORITHM与LOCK,让不满足条件的情况直接失败,而不是静默退化为锁表。 - 确认长事务与长查询已结束,避免 MDL 等待造成连锁阻塞;DDL 执行前查看正在运行的事务与查询。
- 选择低峰窗口执行,并监控 IO、CPU、从库延迟、连接数与业务错误率。
- 大表按分区或按业务范围分批处理,避免单次长时间操作。
- 确认磁盘剩余空间大于索引体积的预估上限。
- 执行完成后核对索引是否生效(
SHOW INDEX)、执行计划是否符合预期,并触发一次统计信息采集。
3.5.3 索引变更的灰度与回滚
推荐流程:
- 新增索引:先创建,再验证。创建完成后用
EXPLAIN核对关键语句是否选中新索引,并在观察窗口内对比延迟与扫描行数。 - 删除索引前先置为不可见:
-- 第一步:置为不可见,观察一个完整业务周期(含日结、报表、批处理)
ALTER TABLE orders ALTER INDEX idx_user_status_created INVISIBLE;
-- 第二步:若确认无退化,执行删除
ALTER TABLE orders DROP INDEX idx_user_status_created, ALGORITHM=INPLACE, LOCK=NONE;
高危:DROP INDEX 是不可逆的结构变更。删除后相关查询会立即退化为全表扫描或更差的计划,且无法通过回滚语句恢复,只能重新执行 ADD INDEX 重建,重建期间的性能缺口无法消除。执行前必须完成不可见验证,并确认观察周期覆盖了所有低频路径(月度报表、批量任务、对账、灾备演练涉及的查询)。兜底方式:保留索引的完整定义(SHOW CREATE TABLE 输出)与重建语句;重建前确认磁盘空间、DDL 窗口与从库延迟承受能力;若在业务高峰期发现退化,优先考虑用查询改写或提示临时缓解,再安排重建。
- 变更期间的可逆性设计:
- 索引定义的原文与新的执行计划都要归档,作为回滚依据;
- 索引变更与应用发布解耦,避免"代码依赖新索引"与"索引尚未验证"同时存在;
- 大规模索引调整按"先加后删"推进,不先删除旧索引。
3.6 常见问题与处置
3.6.1 执行计划突然变化
现象:同一条 SQL 在数据、代码、索引均未变更的情况下耗时上升。
排查顺序:
- 确认 SQL 文本与参数是否变化(ORM 拼接条件、参数极值会改变选择性);
- 对比当前
EXPLAIN与历史归档的执行计划,定位发生变化的列(key、type、rows); - 查看统计信息的采集时间与基数,判断是否发生了自动重新采集或统计信息陈旧;
- 确认是否同时发生了索引变更、版本升级、参数调整等结构性变化;
- 确认数据分布是否变化(新增热点值、数据倾斜加剧)。
处置:优先重新采集统计信息并复测;若确认由统计信息引起且反复波动,考虑固定采样页数、建立直方图,或对关键语句使用提示(须遵守 2.6.4 的高危约束)。
3.6.2 索引未被使用
按以下顺序排除,多数情况在前两步即可定位:
- 写法问题:隐式类型转换、列上使用函数或运算、前导通配、
OR混用(对照 2.5 的表格逐项核对)。 - 结构问题:字符集或排序规则不一致,索引不存在或已被置为不可见。
- 代价问题:条件选择性差(命中行占比高),优化器判断全表扫描更便宜;此时不是"索引没被用上",而是索引对这条查询没有收益。
- 估算问题:
rows估算偏差导致代价计算错误,需要更新统计信息或建立直方图。 - 验证方式:用
possible_keys判断是否"没有可用索引",用EXPLAIN ANALYZE判断估算偏差,用EXPLAIN FORMAT=JSON判断代价构成。
3.6.3 连接与排序产生临时表
- 先区分临时表的用途:
GROUP BY、DISTINCT、UNION、派生表物化、排序。 - 能通过索引消除的,优先调整索引列顺序让分组或排序直接利用索引有序性。
- 无法消除的,判断数据量:内存临时表(由
tmp_table_size与max_heap_table_size界定,8.0 使用 TempTable 引擎)通常可接受,落盘临时表会带来明显 IO。 - 处置顺序:先减小参与排序分组的数据量(收紧条件、缩小分页、拆分查询),再考虑调整临时表参数。
3.6.4 统计信息陈旧导致的偏差
-- 查看统计信息的最后更新时间与基数
SELECT table_name, last_update, n_rows
FROM mysql.innodb_table_stats WHERE table_name = 'orders';
-- 重新采集(属高危操作,见下方说明)
ANALYZE TABLE orders;
高危:ANALYZE TABLE 与统计信息采集参数(innodb_stats_persistent_sample_pages、innodb_stats_auto_recalc、innodb_stats_persistent)的变更会立即改变多张表的执行计划,可能使原本稳定的批量任务与报表在无代码变更的情况下性能反转;对大表执行 ANALYZE TABLE 会带来持续的采样 IO 与短暂的元数据锁等待。执行前归档当前的统计信息快照(mysql.innodb_index_stats 与 mysql.innodb_table_stats 的相关行)、受影响的执行计划与当时的性能指标。兜底方式:参数类变更属于会话或全局变量,可回退到原值并重新采集一次统计信息以收敛;单次 ANALYZE TABLE 无法"撤销",只能等待下一次自动采集覆盖或再次手工采集,因此必须在低峰执行并预留观察窗口。
处置统计信息偏差的常规路径:
- 确认偏差:用
EXPLAIN ANALYZE对比估算行数与实际行数; - 更新统计信息:手工采集或等待自动采集触发;
- 若更新后仍偏差明显:为倾斜列建立直方图;
- 若采样本身不可靠:适当提高采样页数(须评估 IO 开销);
- 若偏差稳定存在且不可通过统计信息解决:改用查询改写或调整索引结构,而不是长期依赖提示。
四、总结
索引是被磁盘访问成本逼出来的解法。数据量增长后查询变慢的根因是无效的页访问次数随行数线性增长,索引把定位成本压到与树高成正比的对数级别,代价是额外的空间、额外的写入维护,以及更复杂的优化决策空间。
原理层面有四条主线。结构主线:B+Tree 依赖高位扇出把树高控制在很小的常数,叶节点有序并互联,同时支撑等值、范围与排序;哈希只在等值上更优,位图与平衡二叉树都不适合作为磁盘索引。组织主线:InnoDB 是索引组织表,聚簇索引即数据本身,主键决定物理顺序与所有二级索引的体积;二级索引的叶节点只存索引列与主键,由此产生回表代价。匹配主线:联合索引按前缀有序,定位只能使用连续前缀,范围条件会截断后续列,等值列在前、范围列在后、排序列接在其后是列顺序的基本依据。决策主线:优化器按代价与统计信息选择访问路径,估算依赖采样、独立性与均匀性假设,分布倾斜与统计信息陈旧会造成选择偏差,而提示与强制索引把判断固化并引入新的风险。
实现层面的结论可以收敛为几条。判读执行计划按 type、key 与 key_len、rows 与 filtered、Extra 的顺序推进,估算与实际不符时用 EXPLAIN ANALYZE 量化偏差,需要看代价构成时用 EXPLAIN FORMAT=JSON。定位慢查询从慢日志聚合与模板归一开始,结合压测与基线做变更前后对照,避免用单次观察下结论。索引设计按主键选择、联合索引列顺序、区分度评估、冗余消除、上线前执行计划评审的次序推进;深分页用游标或延迟关联,连接保证被驱动表的连接列有索引,分组与排序尽量利用索引有序性,包含匹配改用全文索引或外部检索。索引变更按"先加后删、删除前先不可见"的流程推进,删除索引、强制索引提示、变更统计信息采集按高危操作处理并准备回滚或兜底。具体命令与配置参数分别由 mysql: 使用命令 与 mysql: 关于 MySQL 服务配置 承载,压测与基线方法参见 linux: 压测与性能基线,主从复制的延迟与重放机制参见 mysql: 主从同步原理。