一、起源:数据量增长后查询变慢的根因

索引不是先有结构、后有场景的技术,而是被"在磁盘上找一行数据"的固有成本逼出来的解法。理解这一点,比记住索引的种类更重要。

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 的处理顺序为:
  1. 若存在非空唯一索引,选第一个作为聚簇索引;
  2. 都不存在时,创建一个 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; 的执行路径:

  1. 在二级索引 idx_user_status_created 上定位 user_id = 100 AND status = 1 的叶节点区间,得到一批主键值;
  2. 逐个用主键到聚簇索引查找完整行;
  3. 从行中取出 total 返回。

第 2 步即回表,代价为:

  • 每次回表都要在聚簇索引上走一次树高(通常 2 至 4 次页访问),其中上层节点常驻缓冲池,实际磁盘读取决于行是否已在缓冲池中。
  • 回表次数等于命中的行数,行数多时是批量随机读。
  • 索引列与查询列重叠度越高,回表读取的列越少,收益越明显;SELECT * 会把回表的收益抵消到最低。

2.2.4 二级索引与聚簇索引的对比

维度 聚簇索引 二级索引
叶节点内容 完整行数据 索引列值加主键值
数量 每表一个 可多个
建立依据 主键或首个非空唯一索引或隐藏列 显式创建的键
覆盖查询的能力 天然覆盖全部列 只能覆盖索引列与主键列
更新索引列的成本 需移动行(主键更新时) 需删除旧条目并插入新条目

2.3 覆盖索引与索引下推

2.3.1 覆盖索引

  • 覆盖索引指查询所需的全部列都能从索引中获得,无需回表。判定条件是查询涉及的列集合落在同一个索引的列集合之内。
  • 涉及的列包括四类,容易被遗漏后两类:
  1. SELECT 中出现的列;
  2. WHERE 中用于过滤的列;
  3. ORDER BY 中用于排序的列;
  4. 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:

  1. 排序涉及的所有列属于同一个索引,且构成定位用前缀之后的一段连续列;
  2. WHERE 中使用的等值条件构成该索引的前缀;
  3. 排序方向一致(全部升序或全部降序),或与索引声明顺序(含 8.0 的降序索引)一致;
  4. 排序列中间不能跳过索引列。

示例(索引 (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 联合索引列顺序的确定方法

  1. 汇总该表需要支撑的查询模式,按它们各自的条件列集合归类。
  2. 把出现频率最高、选择性最好的等值条件列放在最前。
  3. 多个等值条件之间,把选择性更高的列放前面;顺序对定位影响不大,但会影响跳跃扫描与部分覆盖场景。
  4. 等值条件之后放范围条件列。
  5. 若存在排序或分组需求,把排序或分组列接在等值条件列之后,让索引顺序直接满足排序。
  6. 检查是否能把高频查询的返回列追加到索引尾部以形成覆盖。
  7. 核对是否与既有索引构成前缀关系,避免冗余。

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 索引选择偏差的原因

  1. 采样与估算误差:基数由采样得出,页数由行宽推算,二者都是近似值。
  2. 独立性与均匀性假设:优化器按"各条件相互独立、值在范围内均匀分布"估算,实际数据往往同时违反这两条。
  3. 回表代价估算粗略:回表带来的随机读代价难以精确建模,优化器可能高估或低估走二级索引的总成本。
  4. 分布倾斜:某个值占表 90% 的行时,按基数平均估算出的 rows 会严重偏低或偏高。
  5. 前缀索引的基数不准:前缀索引无法反映完整列的区分度,估算偏差更大。
  6. 统计信息陈旧:大批量写入、数据归档、分区切换后未重新采集。

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 判读顺序

  1. 看 type:是否为 ALL 或 index;连接场景下被驱动表是否为 eq_ref 或 ref。
  2. 看 key 与 possible_keys:是否选用了预期的索引;possible_keys 为空说明索引无法被使用,属写法问题。
  3. 看 key_len:确认联合索引用到了第几列,定位范围条件截断的位置。
  4. 看 rows 与 filtered:估算扫描量是否可接受,rows × 下一行 rows 的量级决定了连接成本。
  5. 看 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 的判读要点:

  1. 比较 rows(估算)与 actual ... rows(实际),偏差数量级较大说明统计信息不可靠,需要重新采集或建立直方图;
  2. 关注 actual time 的起止值,第一步与后续步骤之间是否出现明显台阶,台阶处即主要耗时;
  3. loops 大于 1 时,实际总耗时为单次耗时乘以迭代次数;
  4. 出现 never executed 说明该分支未被执行,估算与真实路径发生了分歧。

3.2 慢查询定位流程

3.2.1 从慢日志到执行计划

慢查询日志的开启方式、long_query_time 阈值设置与日志文件位置参见 mysql: 使用命令 的日志部分,本节不重复命令,只给出定位流程。

  1. 确认慢日志已开启且阈值合理:long_query_time 取值过小会把正常查询写入日志造成噪声,过大则漏掉需要优化的语句;建议与业务延迟目标对齐。
  2. 聚合而不是逐条查看:慢日志条数多时先做聚合,按 Query_time 总和、出现次数、Rows_examined 与 Rows_sent 比值排序。
  3. 提取 SQL 模板:把同类语句归一为模板(常量替换为占位符),同一模板的参数差异会造成执行计划差异,需要分别验证极值参数。
  4. 获取执行计划:对模板做 EXPLAIN,必要时对具体参数值做 EXPLAIN ANALYZE 或查看 EXPLAIN FOR CONNECTION。
  5. 判定问题类型:无可用索引、索引未被选中、回表过多、排序或临时表、扫描行数与返回行数比例失衡。
  6. 改写或调整索引:优先改写 SQL 让其可用既有索引,其次调整索引;两者都不可行时再评估结构与数据模型。
  7. 验证与回归:在同一数据量与同一参数下对比优化前后的 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 联合索引列顺序的确定方法

  1. 列出该表所有高频查询模式,标注每个模式的条件列、排序分组列与返回列。
  2. 以"等值条件列在前、范围条件列在后"为基本排序。
  3. 等值条件列内部按选择性从高到低排列,选择性由 COUNT(DISTINCT col) / COUNT(*) 估算;同时核对最频繁出现的列是否应作为最左列。
  4. 排序或分组列接在等值条件列之后,检查排序方向与索引声明是否一致。
  5. 判断是否追加返回列形成覆盖索引,权衡索引体积与回表收益。
  6. 与既有索引对比,确认不构成前缀冗余,也不存在可被替代的旧索引。

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 范围查询与排序

  • 排序列必须位于范围条件列之前才能使用索引排序;排序列在范围列之后时,索引无法同时满足过滤与排序。
  • 常见处理方式:
  1. 把范围条件改为等值条件加固化取值(如按日期分区或按天枚举),使排序列回到等值前缀之后;
  2. 为"过滤 + 排序"专门建立联合索引,接受另一个查询模式上的退化;
  3. 接受排序成本,控制返回行数(限制页码与条数),使文件排序的数据量可控。
  • 排序缓冲由 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
派生表无法合并 改写为可合并形式,或把中间结果落为实体表并建立索引

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_%' 前缀可用于定位,通配符位置之后的比较无法用索引

替代方案与取舍:

  1. 全文索引:适用于按词检索,支持自然语言与布尔模式;中文需配置 ngram 解析器;词最小长度与停用词会影响召回。
  2. 冗余反转列:额外维护一列保存反转后的字符串,把后缀匹配转成前缀匹配;适用于固定的后缀查询,代价是维护额外列。
  3. 冗余拼音或编码列:把检索维度固化为额外列并建立前缀索引,适用于固定的检索方式。
  4. 外部检索组件:把包含匹配、分词与相关性排序交给专门的检索系统,数据库只保留主键回查。适用于检索需求复杂、数据量大的场景,代价是引入同步链路与一致性窗口。
  5. 改写条件:能拆成前缀条件的(如编号、日期、手机号段)尽量改写,避免通配符出现在最左侧。

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 仍有其他影响:
  1. 需要扫描全表构建索引,产生大量顺序读与排序,消耗 IO 与 CPU;
  2. 构建过程中的索引页写入会污染缓冲池,挤占业务数据页;
  3. 需要短暂获取元数据锁(MDL),长事务或长查询持有 MDL 时 DDL 会等待,DDL 等待期间后续查询可能被阻塞,形成排队;
  4. 主库执行的 DDL 会在从库重放,单线程重放 DDL 期间从库延迟增加,读从库的业务可能受影响;
  5. 大表构建索引可能生成与索引体积相当的临时文件,需确认磁盘余量。
  • 删除索引同样需要修改表定义,代价主要来自元数据操作与后续计划变化,而非数据重写。真正的风险是删除后查询退化。

3.5.2 在线 DDL 的注意点

  1. 确认 DDL 算法与锁级别:显式写出 ALGORITHM 与 LOCK,让不满足条件的情况直接失败,而不是静默退化为锁表。
  2. 确认长事务与长查询已结束,避免 MDL 等待造成连锁阻塞;DDL 执行前查看正在运行的事务与查询。
  3. 选择低峰窗口执行,并监控 IO、CPU、从库延迟、连接数与业务错误率。
  4. 大表按分区或按业务范围分批处理,避免单次长时间操作。
  5. 确认磁盘剩余空间大于索引体积的预估上限。
  6. 执行完成后核对索引是否生效(SHOW INDEX)、执行计划是否符合预期,并触发一次统计信息采集。

3.5.3 索引变更的灰度与回滚

推荐流程:

  1. 新增索引:先创建,再验证。创建完成后用 EXPLAIN 核对关键语句是否选中新索引,并在观察窗口内对比延迟与扫描行数。
  2. 删除索引前先置为不可见:
-- 第一步:置为不可见,观察一个完整业务周期(含日结、报表、批处理)
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 窗口与从库延迟承受能力;若在业务高峰期发现退化,优先考虑用查询改写或提示临时缓解,再安排重建。

  1. 变更期间的可逆性设计:
  • 索引定义的原文与新的执行计划都要归档,作为回滚依据;
  • 索引变更与应用发布解耦,避免"代码依赖新索引"与"索引尚未验证"同时存在;
  • 大规模索引调整按"先加后删"推进,不先删除旧索引。

3.6 常见问题与处置

3.6.1 执行计划突然变化

现象:同一条 SQL 在数据、代码、索引均未变更的情况下耗时上升。

排查顺序:

  1. 确认 SQL 文本与参数是否变化(ORM 拼接条件、参数极值会改变选择性);
  2. 对比当前 EXPLAIN 与历史归档的执行计划,定位发生变化的列(key、type、rows);
  3. 查看统计信息的采集时间与基数,判断是否发生了自动重新采集或统计信息陈旧;
  4. 确认是否同时发生了索引变更、版本升级、参数调整等结构性变化;
  5. 确认数据分布是否变化(新增热点值、数据倾斜加剧)。

处置:优先重新采集统计信息并复测;若确认由统计信息引起且反复波动,考虑固定采样页数、建立直方图,或对关键语句使用提示(须遵守 2.6.4 的高危约束)。

3.6.2 索引未被使用

按以下顺序排除,多数情况在前两步即可定位:

  1. 写法问题:隐式类型转换、列上使用函数或运算、前导通配、OR 混用(对照 2.5 的表格逐项核对)。
  2. 结构问题:字符集或排序规则不一致,索引不存在或已被置为不可见。
  3. 代价问题:条件选择性差(命中行占比高),优化器判断全表扫描更便宜;此时不是"索引没被用上",而是索引对这条查询没有收益。
  4. 估算问题:rows 估算偏差导致代价计算错误,需要更新统计信息或建立直方图。
  5. 验证方式:用 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 无法"撤销",只能等待下一次自动采集覆盖或再次手工采集,因此必须在低峰执行并预留观察窗口。

处置统计信息偏差的常规路径:

  1. 确认偏差:用 EXPLAIN ANALYZE 对比估算行数与实际行数;
  2. 更新统计信息:手工采集或等待自动采集触发;
  3. 若更新后仍偏差明显:为倾斜列建立直方图;
  4. 若采样本身不可靠:适当提高采样页数(须评估 IO 开销);
  5. 若偏差稳定存在且不可通过统计信息解决:改用查询改写或调整索引结构,而不是长期依赖提示。

四、总结

索引是被磁盘访问成本逼出来的解法。数据量增长后查询变慢的根因是无效的页访问次数随行数线性增长,索引把定位成本压到与树高成正比的对数级别,代价是额外的空间、额外的写入维护,以及更复杂的优化决策空间。

原理层面有四条主线。结构主线:B+Tree 依赖高位扇出把树高控制在很小的常数,叶节点有序并互联,同时支撑等值、范围与排序;哈希只在等值上更优,位图与平衡二叉树都不适合作为磁盘索引。组织主线:InnoDB 是索引组织表,聚簇索引即数据本身,主键决定物理顺序与所有二级索引的体积;二级索引的叶节点只存索引列与主键,由此产生回表代价。匹配主线:联合索引按前缀有序,定位只能使用连续前缀,范围条件会截断后续列,等值列在前、范围列在后、排序列接在其后是列顺序的基本依据。决策主线:优化器按代价与统计信息选择访问路径,估算依赖采样、独立性与均匀性假设,分布倾斜与统计信息陈旧会造成选择偏差,而提示与强制索引把判断固化并引入新的风险。

实现层面的结论可以收敛为几条。判读执行计划按 type、key 与 key_len、rows 与 filtered、Extra 的顺序推进,估算与实际不符时用 EXPLAIN ANALYZE 量化偏差,需要看代价构成时用 EXPLAIN FORMAT=JSON。定位慢查询从慢日志聚合与模板归一开始,结合压测与基线做变更前后对照,避免用单次观察下结论。索引设计按主键选择、联合索引列顺序、区分度评估、冗余消除、上线前执行计划评审的次序推进;深分页用游标或延迟关联,连接保证被驱动表的连接列有索引,分组与排序尽量利用索引有序性,包含匹配改用全文索引或外部检索。索引变更按"先加后删、删除前先不可见"的流程推进,删除索引、强制索引提示、变更统计信息采集按高危操作处理并准备回滚或兜底。具体命令与配置参数分别由 mysql: 使用命令 与 mysql: 关于 MySQL 服务配置 承载,压测与基线方法参见 linux: 压测与性能基线,主从复制的延迟与重放机制参见 mysql: 主从同步原理。