外观
13 - 明明有索引,数据库为什么选择全表扫描
摘要与阅读定位
给查询条件对应的列建了索引,执行计划却仍然显示全表扫描,这是索引失效了吗?要先区分三种原因:查询条件难以形成索引查找范围、优化器估错了工作量,或者索引访问确实比全表扫描更贵。本文先解释这三种情况,再用一组状态分布不均、客户与状态相关的订单数据比较访问方式,最后讨论统计信息和类型转换。
- 前置:11 回表成本、12 索引范围、14 计划与运行统计。
- 语义:03 类型与比较、06 时间过滤;后续:25 慢 SQL SOP。
- 基线:MySQL 8.0.18+ / 8.4、InnoDB。实验方法见 A;下列行数和匹配结果是推导预期,具体计划、估算与耗时取决于目标实例。
推荐先读 14 的小查询与静态计划,再回到本文。初次只需分类并核对真实匹配数量;强制候选、直方图和跟踪工具是可选进阶,不以修改统计作为开始排查的第一步。
先建立直觉:找到索引起点,不等于后面的工作少
假设 100 笔订单中 90 笔已支付。状态索引把相同状态的记录排在一起,能很快找到 PAID 的起点,但找到起点后,仍要读取 90 个匹配项。索引节省了定位工作,却没有让结果数量变少。这个数量示意不是某个实例的时间预测。
在 InnoDB 中,主键索引也称为聚簇索引,其中保存完整行;状态索引这样的二级索引保存索引列和主键。若查询还需要不在状态索引中的大字段,就要通过主键继续取完整行,这一步称为回表。匹配 90 笔订单时,索引访问可能既要读取大量索引项,又要回表取数;直接扫描整表则无需先经过状态索引。因此,命中比例很高且需要回表时,全表扫描有时反而更省工作。
反过来,查只有一笔的业务号,精确索引访问通常更有优势。决定成本的不只是“存在索引”,还有匹配比例、返回列、顺序、数据分布和缓存。
优化器负责选择查询的执行方式。它通常需要在执行前估计各个方案的工作量,依据之一就是统计信息:描述数据规模与分布的资料,而非每次查询的精确计数。某些值出现很多、其他值很少,称为数据倾斜;某个客户只出现特定状态,说明客户与状态存在列相关性。这些分布会影响匹配行数,也会影响索引访问是否划算。
1. 先分类:不能访问、估错,还是根本不值得访问?
| 类型 | 现象与假设 | 区分证据 |
|---|---|---|
| 访问范围难以形成 | 列与参数类型不一致、函数、前导通配符、索引前缀不匹配 | 比较等价且类型一致的查询,检查访问条件 |
| 成本估算偏差 | 数据倾斜、相关列、采样及统计陈旧 | 同节点 estimated 与 actual、参数分布、统计来源 |
| 索引真实代价更高 | 命中大比例且需回表、宽索引或排序/随机访问昂贵 | 对照完整结果、候选实际工作与并发写入代价 |
访问路径指获取候选记录的方式,例如全表扫描或索引范围查找。**CBO(基于成本的优化器)**在可用路径之间比较估算成本:预计扫描多少记录、是否需要回表、是否还要排序等,都会影响选择。成本使用内部单位,不能直接当作毫秒。
因此要分两步判断:先看条件能否形成有效的索引范围,再看这条路径是否值得选。即使索引可用,也可能因预计收益太低而被放弃;反过来,统计信息若低估或高估匹配数量,也可能使成本比较偏离实际。传统 EXPLAIN 的 type=ALL 表示全表扫描,但这个标记本身不能证明优化器选错了。
2. 初始化:九成 PAID,且与客户强相关
在隔离库中初始化 optimizer_* 教学表,先结束其他事务,并确认同名表属于本实验,因为下面会删除并重建它们。数字表的两次交叉连接组合出 1—100:客户 1 对应前 90 单,全部为 PAID;客户 2 对应后 10 单,全部为 CANCELLED。account_no 是保留前导零的业务号,payload 模拟查询可能需要的大字段。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS optimizer_order;
DROP TABLE IF EXISTS optimizer_digit;
CREATE TABLE optimizer_digit (digit INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO optimizer_digit VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE optimizer_order (
id BIGINT PRIMARY KEY,customer_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,created_at DATETIME NOT NULL,
account_no VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
payload VARCHAR(500) NOT NULL,
KEY idx_optimizer_status (status),
UNIQUE KEY uk_optimizer_account (account_no)
) ENGINE=InnoDB;
INSERT INTO optimizer_order
SELECT num,CASE WHEN num<=90 THEN 1 ELSE 2 END,
CASE WHEN num<=90 THEN 'PAID' ELSE 'CANCELLED' END,
TIMESTAMPADD(DAY,MOD(num-1,10),'2026-09-01'),
LPAD(num,6,'0'),REPEAT('x',500)
FROM (
SELECT 1+ones.digit+10*tens.digit AS num
FROM optimizer_digit AS ones CROSS JOIN optimizer_digit AS tens
) AS seq;
SELECT status,COUNT(*) FROM optimizer_order GROUP BY status ORDER BY status;
SELECT customer_id,status,COUNT(*) FROM optimizer_order
GROUP BY customer_id,status ORDER BY customer_id,status;预期 CANCELLED=10、PAID=90;客户 1/PAID=90,客户 2/CANCELLED=10,其余组合零行。这个数据不是均匀或独立分布,扩展压测要保留倾斜而非随机生成平均状态。
初始化不要求立即修改统计。若准备进入后面的候选对照,可以在记录初始计划后,单独执行 ANALYZE TABLE optimizer_order 更新教学表统计,并保存操作时间与新计划。统计更新属于管理操作,有负载和隐式提交边界,不是普通 SELECT。
初次先执行这些只读查询,把数量与语义对齐,再考虑计划:
sql
SELECT COUNT(*) AS matching_rows FROM optimizer_order WHERE status = 'PAID';
SELECT COUNT(*) AS matching_rows FROM optimizer_order WHERE status = 'CANCELLED';
SELECT COUNT(*) AS matching_rows FROM optimizer_order
WHERE customer_id = 2 AND status = 'PAID';
EXPLAIN FORMAT=TRADITIONAL
SELECT id, payload FROM optimizer_order WHERE status = 'PAID';三个 COUNT(*) 的预期输出分别为 90、10、0。普通 EXPLAIN 提供估算计划,不实际执行这个 SELECT 来返回业务行;具体选中哪个访问路径,应查看输出,不能预设为 ALL。其中 rows 是估计检查的行数,filtered 是估计通过过滤的百分比,两者不能代替上述精确计数。后续比较估计值与实际值时,要对齐同一个执行节点和统计单位,详见 14。
3. 索引与全扫描:同参数也要区分返回列
本节比较不同返回列以及同一查询的候选路径。先核对默认计划和普通查询结果,再逐条运行 EXPLAIN ANALYZE:它会实际执行查询,并报告执行节点的行数和耗时,也会增加观测开销。FORCE INDEX 限制候选索引,IGNORE INDEX 排除指定索引,用于比较不同路径;不要同时修改统计信息,否则难以判断计划变化由哪个因素造成。
sql
EXPLAIN ANALYZE SELECT id FROM optimizer_order WHERE status='PAID';
EXPLAIN ANALYZE SELECT id,payload FROM optimizer_order WHERE status='PAID';
EXPLAIN ANALYZE SELECT id,payload FROM optimizer_order WHERE status='CANCELLED';
EXPLAIN ANALYZE
SELECT id,payload FROM optimizer_order FORCE INDEX (idx_optimizer_status)
WHERE status='PAID';
EXPLAIN ANALYZE
SELECT id,payload FROM optimizer_order IGNORE INDEX (idx_optimizer_status)
WHERE status='PAID';第一条查询只需要 status 和 id,状态二级索引可以提供这两列,这称为覆盖查询;第二条还需要 payload,该列不在状态索引中,使用这个索引时需考虑回表。两条查询虽然条件相同,后续取数成本却不同。覆盖查询的可见性检查边界见 11。
对照中 PAID 查询的预期结果为 90 行,CANCELLED 为 10 行,不保证某条选择全表扫描或更快;ALL 是传统格式的标记,树形计划应看相应扫描节点。强制与忽略索引仅用于隔离实验。一百行不足以支持生产性能结论:数据已在缓存中还是需要从存储读取,以及观测工具本身的开销,都可能明显影响耗时。
保存默认、强制、忽略索引的三个计划,核对完整结果是否等价,并记录客户端读完全部结果的时间。FORCE INDEX 不会固定所有执行任务(算子)和连接顺序,指定索引无法使用时也不保证走它;一次更快不足以支持在业务 SQL 中永久绑定索引名。
4. 表统计与列直方图:不解决同一个问题
本节是可选进阶。直方图按值或值范围保存出现频率的估计,帮助辨认热点与长尾;它不是新的查找索引,也不直接减少扫描。先理解这个用途,再读桶数与估算优先级。
表和索引统计用于估计表规模、索引键的不同值数量及范围匹配量;列直方图进一步描述各值的频率。SHOW INDEX 的 Cardinality 是估计的不同值数量,不是某个值的匹配行数。例如状态只有两种,并不能据此知道 PAID 占九成还是一成。information_schema.TABLES 中的 TABLE_ROWS 也不是 InnoDB 表的精确业务计数。
sql
EXPLAIN FORMAT=TREE
SELECT id FROM optimizer_order WHERE customer_id=2 AND status='PAID';
ANALYZE TABLE optimizer_order UPDATE HISTOGRAM ON customer_id WITH 16 BUCKETS;
SELECT COLUMN_NAME,JSON_PRETTY(HISTOGRAM) AS histogram
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME='kb_database_lab' AND TABLE_NAME='optimizer_order'
AND COLUMN_NAME='customer_id';
EXPLAIN FORMAT=TREE
SELECT id FROM optimizer_order WHERE customer_id=2 AND status='PAID';
SELECT COUNT(*) AS matching_rows FROM optimizer_order
WHERE customer_id=2 AND status='PAID';最后一条计数查询的预期输出为 0。单列 customer_id 直方图可能改善“客户 2 占一成”的估算,但不能描述“客户 2 从不出现 PAID”这个两列组合的分布。假如把两列当作独立条件,就可能用客户 2 的比例 10% 乘以 PAID 的比例 90%,推得 100×10%×90%=9 行,与实际组合的 0 行不同。这只是说明独立性假设的局限,不是预报 MySQL 一定估算为 9 行。
直方图的桶是保存某个值或某段值范围及其频率的统计单元。增加桶数可以更细致地描述一列的分布,却不能让单列直方图表达两列之间的相关性。
索引范围估算可用时,MySQL 范围优化器的估算优先于相应直方图。因此,给已有索引的 status 列建立直方图,不保证改变它的访问路径。比较前后计划时,要保存采样率、桶类型、更新时间和维护方式。普通 ANALYZE TABLE 与显式 UPDATE HISTOGRAM 不是同一操作;目标版本若支持直方图自动更新,也要检查实际定义,不能默认所有 8.x 版本行为相同。
统计操作会改变后续计划,还可能因采样消耗 I/O,并涉及锁和隐式提交,边界见 03。用于线上之前,应保存基线计划,确认权限、资源预算和回退方案;不要在事故高峰随意对全库执行 ANALYZE TABLE。删除直方图也不保证旧计划原样恢复。
sql
ANALYZE TABLE optimizer_order DROP HISTOGRAM ON customer_id;本例清理时删除教学直方图;线上须按审批处理既有统计,不复制此删除语句到业务库。
5. 类型转换会改变结果,不只是让索引变慢
教学数据的第一笔订单为 account_no='000001'。这个字段是字符串,但比较参数可以是数字或字符串。参数类型不同,数据库采用的比较规则也可能不同,这称为隐式类型转换。分别比较:
sql
SELECT id,account_no FROM optimizer_order WHERE account_no=1 ORDER BY id;
SELECT id,account_no FROM optimizer_order WHERE account_no='1' ORDER BY id;
SELECT id,account_no FROM optimizer_order WHERE account_no='000001' ORDER BY id;
EXPLAIN SELECT id FROM optimizer_order WHERE account_no=1;
EXPLAIN SELECT id FROM optimizer_order WHERE account_no='000001';预期第一条按数字比较,'000001' 与数字 1 相等,因此匹配 ID 1;第二条比较字符串 '1',与保存的六位业务号不同,返回零行;第三条比较字符串 '000001',匹配 ID 1。第一条与第三条在这组数据中命中相同,不代表业务号应该按数字解释。
字符串索引按字符串规则组织,而数字比较需要按数字解释列值,不能简单地把它当作同一个字符串键来定位。排查这类问题时,要同时检查匹配结果和执行计划。业务号若需要区分前导零、非数字字符或避免数值精度损失,应按字符串绑定参数。非法转换还可能产生警告,可用 SHOW WARNINGS 查看;不能为了让索引更易利用,就把命中对象改错。
类似地,按某日过滤的 DATE(created_at) 条件,在日期边界和时区语义一致时,可改成“当日零点及以后、次日零点以前”的半开范围:包含起点,排除终点。它直接限定列值的上下界,更便于普通列索引形成范围;表达式索引或生成列索引在适用版本也可能支持函数条件,所以不能说函数一定不用索引。
LIKE '%x' 的开头不固定,通常无法形成普通 B+ Tree 索引的左前缀查找范围,但仍可能通过扫描覆盖索引取数。OR 也可能采用 Index Merge(索引合并),合并同一张表上多个索引的扫描结果。条件能否形成有效查找范围,称为可搜索性;范围难以形成不等于任何索引访问都不可能,相关边界见 12。
6. 参数与版本回归:一个 Digest 不是一种工作量
Digest 是把相似 SQL 归为一类的摘要,不能代表每次查询的具体参数。同样的 WHERE status=?,传入 PAID 与 CANCELLED,本例分别匹配 90 行和 10 行;只看这一类 SQL 的汇总指标,容易掩盖参数之间的工作量差异。
因此,改动后要分别检查热门与稀有客户、不同状态、宽窄日期区间,以及空结果和大量返回的情况,保存默认计划、实际行数和延迟。这就是参数回归:确认优化对代表性参数仍然有效。预处理语句、参数绑定及计划缓存的行为应按产品版本解释;Oracle 的 Bind Peeking(优化时查看绑定值)与 Adaptive Cursor Sharing(根据绑定值等条件调整游标选择)不能直接套到 MySQL 上。
升级、统计变化、数据增长、索引可见性或成本参数调整都可能改变选择。受控 Hint 若确实必要,记录适用范围、失效条件、回退和升级回归;“永远不用 Hint”同样不是工程结论。
7. 排障验收与清理
- 先验证查询语义、类型和完整结果,不拿快但错的改写当收益。
- 判断路径是否可形成,再比较估算与实际,最后判断索引真实成本。
- 每次只改统计、SQL 或索引中的一个因素;保留参数分布与运行证据。
- 验证读收益同时评估写维护、磁盘/缓存和其他 SQL 回退。
- 统计视图不足以解释选择时,再使用
optimizer trace查看优化器考虑候选和比较成本的过程。限制跟踪范围与记录量,检查文本和参数是否含敏感信息;可用字段及其含义以目标小版本为准。
清理前先结束事务,再在隔离库删除 optimizer_order、optimizer_digit;若尚未删除直方图,删除表也会清除相应教学统计。线上排查可继续阅读 25,把访问路径、估算和实际成本纳入完整的慢 SQL 诊断流程。
8. 名词解释
| 名词 | 含义 |
|---|---|
| 访问路径 | 获取候选记录的做法,例如全表扫描或某个索引范围 |
| CBO / 成本 | 基于成本选择计划的优化器 / 比较候选的内部估算,不等于毫秒 |
| 数据倾斜 / 列相关性 | 不同值出现频率差异大 / 两列组合分布不能当作相互独立 |
| 统计信息 / 直方图 | 估计表与键规模的资料 / 描述列值频率的统计,不是索引 |
| Cardinality | 索引统计中估计的不同值数量,不是某个参数的匹配行数 |
| Hint / FORCE INDEX | 影响计划选择的提示 / 指定索引候选的工具,不能保证所有步骤固定 |
| 隐式转换 | 数据库因比较类型不同而转换值,可能同时改变命中结果和访问路径 |
| Digest | 相似 SQL 的归类摘要,不包含每次参数,也不代表固定工作量 |
参考资料
- MySQL 8.4 Reference Manual:Optimizer Statistics、Range Optimization、Type Conversion in Expression Evaluation、EXPLAIN Statement。
- 适用条件:实际估算、转换警告、计划与性能应在目标版本执行核验,不能假定 ANALYZE 后必选索引。