罗罗编程学习记录
本页内容 展开查看

05.1.1.4 索引选择性统计与失效

所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.1 索引原理与设计

覆盖范围

  • 基数、选择性与采样估算
  • 常见索引失效场景的原理(函数、隐式转换、前导模糊、OR、否定条件)
  • 前缀索引长度选择与限制
  • 索引合并、唯一与普通索引的写入差异
  • 优化器选错索引的原因与干预手段
  • MySQL 与 PostgreSQL 统计机制差异对照

高频题速览

编号 题目 难度 频率
05.1.1.4-004 索引失效常见场景 进阶 ★★★★★
05.1.1.4-005 列上函数/运算为何失效 进阶 ★★★★★

选择性与失效主线

列不同值数 / 总行数 = 选择性
  选择性高 → 索引过滤力度强;选择性低 → 回表成本可能超过全表扫描
失效本质:索引存原值有序,条件无法用原值定位,或优化器估算索引路径更贵
  函数/运算、隐式转换、前导模糊、缺最左前缀、OR 非索引列、否定条件、统计过期

05.1.1.4-001 区分度和索引选择性是什么?如何评估?

频率 ★★★★☆ | 难度 进阶

一句话结论

区分度(Cardinality)是索引列不同值的数量,选择性为不同值数除以总行数,越接近 1 过滤力度越强;评估时结合命中行数、回表成本与查询频率,而不是只看单一数字。

概念解释

  • 基数(Cardinality):列不同值数量,SHOW INDEX 的 Cardinality 列。
  • 选择性:不同值数 / 总行数,唯一索引为 1,性别类列约 0.5 且实际命中分布可能更倾斜。
  • 经验边界:选择性很低时索引回表成本可能高于全表扫描;具体阈值随数据分布变化。

原理流程

  1. 统计列不同值数量与总行数,计算选择性。
  2. 估算等值条件命中行数与回表代价。
  3. 结合查询频率与写入成本,判断是否值得建索引。

代码 / 命令

-- 计算选择性
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;

-- 查看索引基数(采样估算)
SHOW INDEX FROM orders;

-- 更新统计信息
ANALYZE TABLE orders;

常见误区

  • 误区:区分度高就一定要建索引。正解:还要看查询频率、写入成本与能否被已有索引复用。
  • 误区:选择性数字可以脱离数据分布使用。正解:某值占绝大多数时,即使列整体选择性不低,特定条件仍可能命中大量行。

追问

  • 问:联合索引的基数怎么理解?答:是各级前缀组合的不同值数,按列前缀递增统计。
  • 问:基数是精确值吗?答:InnoDB 为采样估算,大量增删后可能过期,需 ANALYZE TABLE 更新。

05.1.1.4-002 索引基数是采样估算还是精确值?

频率 ★★★★☆ | 难度 进阶

一句话结论

InnoDB 的基数基于索引页采样估算,不是全表精确计数;采样页数由统计参数控制,数据分布倾斜或长期未更新时估算可能与实际偏差较大。

概念解释

  • 采样页:随机抽取若干索引页统计不同值,推算全表基数。
  • 持久化/非持久化统计:统计可持久化存储或每次启动重算,模式由参数控制。
  • 偏差来源:采样不足、分布倾斜、批量增删后未更新。

原理流程

  1. 引擎采样索引页,统计样本内不同值与密度。
  2. 按样本推算全表基数与选择率。
  3. 优化器据此估算命中行数,偏差大时可能选错路径。

代码 / 命令

-- MySQL:查看统计相关参数(口径以版本为准)
SHOW VARIABLES LIKE 'innodb_stats_persistent%';
SHOW VARIABLES LIKE 'innodb_stats_sample_pages';

-- 手动更新统计
ANALYZE TABLE orders;

常见误区

  • 误区:Cardinality 等于真实不同值数。正解:它是估算值,与精确 COUNT(DISTINCT) 可能有差距。
  • 误区:采样越多统计越准、没有代价。正解:提高采样页数增加统计开销,需在准确度与成本间权衡。

追问

  • 问:PG 的统计采样有何不同?答:PG 也采样收集直方图、MCV 与相关性,自动分析触发,详见 05.3.2。
  • 问:估算偏差如何暴露?答:计划 rows 与实际行数差距大,结合 EXPLAIN ANALYZE 类工具核对,见 05.1.3.1。

05.1.1.4-003 低区分度列为什么不适合单独建索引?

频率 ★★★☆☆ | 难度 进阶

一句话结论

低区分度列等值命中大量行,索引扫描加回表的随机读成本可能超过全表顺序扫描;单独建索引收益小、写入放大不变,更适合作为联合索引的前缀或后续过滤列。

概念解释

  • 命中比例高:列值种类少,单个条件覆盖大范围记录。
  • 回表放大:命中行多,每行一次聚簇随机读。
  • 写入成本不变:无论选择性高低,索引维护开销都存在。

原理流程

  1. 估算条件命中行数占比。
  2. 比较索引路径(索引扫描 + 回表)与全表扫描代价。
  3. 低区分度单列索引常被优化器放弃,改为并入联合索引。

代码 / 命令

性别/状态类列:不同值 2~3 个,单条件命中近半表
单列索引:扫描大量索引项 + 大量回表,可能不如全表顺序扫描

常见误区

  • 误区:低区分度列加了索引就一定被使用。正解:优化器按代价判断,低命中过滤力度时可能放弃。
  • 误区:低区分度列完全不能进索引。正解:与高区分度列组成联合索引时,作为前缀或后续列仍有价值。

追问

  • 问:什么情况下低区分度单列索引仍有效?答:命中比例极端小(如某状态仅占 1%)且查询频繁时,结合实测计划判断。
  • 问:怎么量化命中比例?答:按条件分组计数占比,对照计划 rows/filtered 评估。

05.1.1.4-004 索引失效的常见场景有哪些?

频率 ★★★★★ | 难度 进阶

一句话结论

失效本质是条件无法用索引原值定位,或优化器估算索引路径更贵;常见于函数/运算包裹、隐式类型转换、前导模糊、缺最左前缀、OR 连接非索引列、范围后等值、否定条件与统计过期。

概念解释

  • 原值不可比较:列被转换后,索引存的原值无法参与定位。
  • 前缀断裂:联合索引缺少连续最左列。
  • 代价放弃:索引可用但估算回表成本更高,优化器改选全表扫描。
  • 改写方案:各场景的具体 SQL 改写见 05.1.3.2 与 05.1.3.3。

原理流程

  1. 检查条件是否以索引原值做比较。
  2. 检查最左前缀是否连续满足。
  3. 核对统计与命中比例,判断是定位失效还是代价放弃。

代码 / 命令

函数/运算:WHERE DATE(create_time) = '2026-01-01'
隐式转换:WHERE phone = 13800000000(phone 为 varchar)
前导模糊:LIKE '%abc'
前缀断裂:联合索引 (a,b,c),只给 b 条件
OR 混合:a 有索引、b 无索引,OR 连接
范围截断:a > 1 AND b = 2
否定条件:!= / NOT IN / NOT LIKE
统计过期:大量增删后未更新统计

常见误区

  • 误区:失效就一定全表扫描。正解:可能退化为全索引扫描、索引合并或覆盖索引,访问路径需看计划。
  • 误区:所有失效都靠加索引解决。正解:部分失效是写法问题,应先改写 SQL;统计过期则更新统计信息。

追问

  • 问:如何确认是否失效?答:EXPLAIN 看 type、key、rows、Extra,字段解读见 05.1.3.1。
  • 问:IS NOT NULL 一定走索引吗?答:取决于 NULL 比例与估算,大量非空时回表成本可能更高。

05.1.1.4-005 列上做函数或运算为什么会导致索引失效?

频率 ★★★★★ | 难度 进阶

一句话结论

索引保存的是列原值的有序序列,对列套函数或做运算后,比较对象变成函数结果,无法在原值有序结构中定位区间,因此索引前缀用不上。

概念解释

  • 原值有序:B+ 树按列原始值排序。
  • 结果无序:函数输出与原值顺序不保持一致。
  • 改写方向:把函数移到常量侧,对原始列做范围比较。

原理流程

  1. 条件形如 f(col) = const,索引项存的是 col 原值。
  2. 无法用原值范围匹配函数结果,定位断裂。
  3. 改写为 col 与常量边界比较,恢复索引定位。

代码 / 命令

-- 反例:列被函数包裹
SELECT * FROM orders WHERE DATE(create_time) = '2026-01-01';

-- 正例:对原始列做范围比较
SELECT * FROM orders
WHERE create_time >= '2026-01-01 00:00:00'
  AND create_time <  '2026-01-02 00:00:00';

-- 反例:列参与运算
SELECT * FROM t WHERE id + 1 = 5;
-- 正例
SELECT * FROM t WHERE id = 4;

常见误区

  • 误区:函数索引可以解决所有函数包裹问题。正解:表达式索引只覆盖特定表达式形态,其他函数仍失效;MySQL 8.0 与 PG 支持程度不同。
  • 误区:运算在常量侧也会失效。正解:col = const + 1 中常量侧可先计算,列保持原值,不影响定位。

追问

  • 问:表达式索引怎么工作?答:对函数结果预先建有序索引,查询使用相同表达式时可命中;版本支持边界待确认。
  • 问:日期函数包裹还有哪些常见写法?答:YEAR()、MONTH()、LEFT() 包裹列,都应改为范围比较或表达式索引。

05.1.1.4-006 隐式类型转换为什么让索引失效?

频率 ★★★★☆ | 难度 进阶

一句话结论

字符串列与数字比较时,列被隐式转成数字再比较,等价于对列套了转换函数,索引原值无法定位;字符集/排序规则不一致的 JOIN 条件同理。

概念解释

  • 转换方向:MySQL 中字符串与数字比较时,字符串转数字(具体规则以版本为准)。
  • 函数等价:转换发生在列侧,破坏原值有序性。
  • JOIN 场景:两表连接列字符集不同也会隐式转换。

原理流程

  1. 比较两侧类型不一致,数据库选择转换规则。
  2. 列侧被转换后,索引原值无法直接匹配。
  3. 条件退化为扫描过滤,索引前缀失效。

代码 / 命令

-- phone 为 varchar,传入数字触发隐式转换
SELECT * FROM users WHERE phone = 13800000000;  -- 失效风险
SELECT * FROM users WHERE phone = '13800000000'; -- 正例:类型一致

-- JOIN 字符集不一致:连接列隐式转换,索引失效
-- 应保证两表连接列字符集与排序规则一致

常见误区

  • 误区:数字字符串传数字更高效。正解:类型不一致先触发转换,索引失效后代价更高。
  • 误区:隐式转换只影响等值查询。正解:范围比较与 JOIN 连接列同样受影响。

追问

  • 问:怎么发现隐式转换?答:计划中索引未命中,核对列类型与传入参数类型;CAST 方向在列侧即失效。
  • 问:PG 也有隐式转换失效吗?答:PG 类型更严格,不匹配常直接报错;可隐式转换的场景同样可能影响索引,详见 05.3.2。

05.1.1.4-007 前导模糊匹配为什么走不了索引?

频率 ★★★☆☆ | 难度 进阶

一句话结论

B+ 树按值前缀有序,LIKE 'abc%' 有确定前缀可定位起点;LIKE '%abc' 前缀不确定,无法在有序结构中找到起点,只能逐行扫描匹配。

概念解释

  • 前缀有序:索引叶子按完整值排序,前缀相同的值相邻。
  • 无前缀起点:前导通配时任意位置都可能匹配,不存在连续区间。
  • 替代方案:前缀索引服务后缀场景有限,全文检索/外置搜索见 05.1.1.1 与 05.5.2.3。

原理流程

  1. LIKE 'x%':按前缀定位起始叶子,沿链表扫描。
  2. LIKE '%x':起点未知,需扫描全部索引项或整行。
  3. 大文本模糊匹配应使用全文索引或搜索引擎。

代码 / 命令

SELECT * FROM users WHERE name LIKE 'Ann%';   -- 前缀确定,可走索引
SELECT * FROM users WHERE name LIKE '%Ann';  -- 前导模糊,无法定位

常见误区

  • 误区:前缀索引能加速 '%x%'。正解:前缀索引只保存前 N 字符,前后都模糊时同样无法定位。
  • 误区:前导模糊在小表上无所谓。正解:小表全扫成本低,但写法模式不应沿用到大表。

追问

  • 问:后缀匹配有没有索引办法?答:可建反转列索引(存 REVERSE(col)),用反转后前缀匹配;表达式索引支持程度随引擎版本。
  • 问:全文索引与模糊匹配什么关系?答:全文索引按分词倒排,解决关键词检索,不等价于子串模糊匹配。

05.1.1.4-008 OR 连接非索引列会怎样?

频率 ★★★☆☆ | 难度 进阶

一句话结论

OR 两侧条件需要并集结果;一侧无索引时,为取全量并集只能全表扫描,另一侧索引也无法单独使用;可改写为 UNION ALL 分别走索引,或为两侧都建索引。

概念解释

  • 并集语义:OR 结果是两侧命中记录的并集,需去重合并。
  • 单侧索引不足:无索引侧必须扫描全表,已扫描全表后无需再走另一侧索引。
  • 改写路径:UNION ALL 让两侧各自走索引再合并。

原理流程

  1. 检查 OR 两侧列是否都有可用索引。
  2. 两侧都有索引可走索引合并;一侧无索引则倾向全表扫描。
  3. 必要时改写为 UNION ALL 或补建索引。

代码 / 命令

-- b 无索引:整体可能全表扫描
SELECT * FROM t WHERE a = 1 OR b = 2;

-- 改写:两侧分别走索引后合并
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2;

常见误区

  • 误区:OR 只要有一侧有索引就能命中。正解:并集要求覆盖全部匹配行,无索引侧迫使整体扫描。
  • 误区:UNION ALL 与 OR 语义完全等价。正解:UNION ALL 不去重,存在重复行时需用 UNION 或业务保证唯一。

追问

  • 问:索引合并能处理 OR 吗?答:可以,多个索引结果取并集(Using union),但合并与回表成本通常高于单一联合索引。
  • 问:怎么判断 OR 是否走了索引?答:看计划 type 与 key,index merge 会在 Extra 标注合并方式。

05.1.1.4-009 否定条件为什么常放弃索引?

频率 ★★★☆☆ | 难度 进阶

一句话结论

!=、NOT IN、NOT LIKE 通常命中排除之外的大部分行,过滤比例高,优化器估算回表成本后常选择全表扫描;这不是绝对规则,取决于排除后剩余比例与数据分布。

概念解释

  • 高命中比例:否定条件保留大多数记录,索引过滤力度弱。
  • 估算依赖分布:若被排除值占绝大多数,剩余小集合也可能走索引。
  • 非绝对结论:否定条件不必然失效,以计划估算为准。

原理流程

  1. 统计否定条件后剩余行占比。
  2. 高占比时索引扫描加回表不划算,改全表扫描。
  3. 低占比(被排除值为主)时索引路径仍可能被选择。

代码 / 命令

SELECT * FROM orders WHERE status != 'CLOSED';  -- 若 CLOSED 极少,结果集接近全表
SELECT * FROM orders WHERE id NOT IN (1, 2, 3); -- 排除少量值,剩余仍多

常见误区

  • 误区:否定条件一定不走索引。正解:是否走索引取决于剩余行比例,不能一概而论。
  • 误区:把 != 改写成范围就能稳定走索引。正解:改写只在值域连续时有效,需结合列类型与分布。

追问

  • 问:NOT IN 遇到 NULL 有什么风险?答:三值逻辑下结果可能为空,需先确认列非空或改写 NOT EXISTS。
  • 问:如何验证否定条件的实际路径?答:用计划看命中行数与访问类型,必要时对比改写后的代价。

05.1.1.4-010 什么是前缀索引?有什么限制?

频率 ★★★☆☆ | 难度 进阶

一句话结论

对长字符串列只索引前 N 个字符以缩小索引体积;限制是不能用于排序/分组、不能做覆盖索引,前缀太短区分度差,太长则体积接近完整索引。

概念解释

  • 前缀截断:索引项保存列前 N 字符。
  • 可用场景:前缀匹配等值与 LIKE 'x%'。
  • 限制来源:索引值不完整,无法还原完整列。

原理流程

  1. 长文本列评估完整索引体积。
  2. 选择前缀长度,使选择性接近完整列。
  3. 前缀索引服务前缀定位,排序与覆盖需求另寻方案。

代码 / 命令

ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

-- 前缀匹配可用
SELECT * FROM users WHERE email LIKE 'abc%';
-- 排序/覆盖需要完整值,前缀索引不支持

常见误区

  • 误区:前缀索引可以参与覆盖索引。正解:需要完整值时必须回表,叶子只存前缀。
  • 误区:前缀长度随意取 10。正解:长度应按选择性曲线选择,过短区分度差。

追问

  • 问:前缀索引能用于唯一约束吗?答:前缀唯一性只约束前缀部分,完整值仍可能重复;唯一语义需用完整唯一索引。
  • 问:表达式索引能否替代前缀索引?答:可对表达式结果建索引,覆盖的是特定表达式形态,与前缀截断不同。

05.1.1.4-011 前缀长度怎么选?

频率 ★★★☆☆ | 难度 进阶

一句话结论

对比不同前缀长度下的选择性与完整列选择性,取选择性接近完整列的最短长度;同时评估索引体积与前缀重复率,避免长度不足导致区分度不够。

概念解释

  • 选择性曲线:随前缀长度增加,不同值占比逐渐接近完整列。
  • 拐点选择:选择性增长明显放缓的最短长度。
  • 重复率:前缀过短时大量记录前缀相同,过滤力度弱。

原理流程

  1. 统计不同长度前缀的不同值数量与完整列数量。
  2. 绘制选择性随长度变化曲线。
  3. 选拐点长度,平衡区分度与索引体积。

代码 / 命令

SELECT
  COUNT(DISTINCT LEFT(email, 8))  / COUNT(*) AS sel8,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10,
  COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel12,
  COUNT(DISTINCT email)           / COUNT(*) AS sel_all
FROM users;

常见误区

  • 误区:前缀越长越安全。正解:过长则体积接近完整索引,失去前缀索引意义。
  • 误区:长度选一次就长期不变。正解:数据分布变化后选择性可能下降,需随数据演进复核。

追问

  • 问:前缀索引对 ORDER BY 有帮助吗?答:不能保证完整值有序,排序仍需 filesort。
  • 问:多列中的长字符串怎么处理?答:优先评估是否需要该列入索引,展示列可用包含列方案,见 05.1.1.3。

05.1.1.4-012 什么是索引合并(Index Merge)?

频率 ★★★☆☆ | 难度 高级

一句话结论

优化器对同表多个索引分别扫描后合并结果:intersect 取交集服务 AND,union/sort_union 取并集服务 OR;合并多路扫描与去重成本通常高于单一联合索引,出现时往往提示索引设计可优化。

概念解释

  • 交集合并:多个索引结果取公共记录(AND)。
  • 并集合并:多个索引结果合并去重(OR)。
  • 合并成本:多路索引扫描 + 排序去重 + 多次回表。

原理流程

  1. 优化器评估无法用单个索引覆盖条件。
  2. 分别扫描可用索引,得到主键集合。
  3. 按交集/并集合并,去重后回表取行。

代码 / 命令

SELECT * FROM t WHERE a = 1 OR b = 2;
-- Extra: Using union(idx_a, idx_b) 或 Using sort_union(...)

SELECT * FROM t WHERE a = 1 AND b = 2;
-- Extra: Using intersect(...)

常见误区

  • 误区:索引合并说明索引设计优秀。正解:它是兜底策略,多路扫描合并代价高,通常优先设计一个匹配查询的联合索引。
  • 误区:合并后不需要回表。正解:合并的是索引结果,整行仍需按主键回表。

追问

  • 问:如何避免依赖索引合并?答:按查询条件建联合索引,或改写 SQL 为 UNION ALL 分别走索引。
  • 问:合并一定比全表扫描慢吗?答:取决于命中比例与索引可用性,低命中场景合并可能仍优于全表扫描。

05.1.1.4-013 唯一索引和普通索引怎么选?

频率 ★★★★☆ | 难度 高级

一句话结论

业务需要唯一约束时必须用唯一索引;仅查询加速时比较读写特征:唯一索引等值查到即停,普通索引可借助 change buffer 缓冲随机写,写多读少场景普通索引可能更优。

概念解释

维度 唯一索引 普通索引
查询 等值命中即停止扫描 需扫到不匹配为止
写入 需读页校验唯一性,不能用 change buffer 可使用 change buffer,随机写更缓冲击
语义 保证唯一 不保证
适用 业务唯一键、幂等键 普通过滤列、范围列

原理流程

  1. 先判断业务是否要求唯一约束。
  2. 需要唯一则建唯一索引;否则按读写比例选择普通索引。
  3. 并发唯一约束不能靠应用层"先查再插",必须由数据库约束兜底。

代码 / 命令

-- 唯一索引:约束 + 查询
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 普通索引:仅查询加速
ALTER TABLE orders ADD INDEX idx_status (status);

常见误区

  • 误区:唯一索引一定比普通索引查询快。正解:等值查找差距很小,可能被缓冲池命中抵消;写入侧唯一索引校验成本更高。
  • 误区:应用层先查再插就能保证唯一。正解:并发下两个事务可能同时通过检查,必须靠唯一索引或约束拦截。

追问

  • 问:唯一索引冲突怎么处理?答:INSERT ... ON DUPLICATE KEY UPDATE(MySQL)或捕获唯一冲突异常后重试,业务保持幂等。
  • 问:唯一索引能被普通索引冗余替代吗?答:不能,两者语义不同;删除唯一索引会丢失约束保护。

05.1.1.4-014 唯一索引为什么不能用 change buffer?

频率 ★★★☆☆ | 难度 进阶

一句话结论

change buffer 把二级索引的随机写先缓冲、稍后合并;唯一索引必须在写入时读取页校验唯一性,无法延迟到合并时判断,因此不能使用 change buffer。

概念解释

  • change buffer:缓冲非唯一二级索引的页修改,减少立即随机读。
  • 唯一性校验:唯一索引插入前必须确认页内无重复值。
  • 立即读页:校验需要页内容在内存或读盘,缓冲失去意义。

原理流程

  1. 普通索引:索引页不在内存时先记录 change buffer,后台合并。
  2. 唯一索引:必须读入目标页检查重复,不能延迟。
  3. 写多读少表上,唯一索引的随机读成本更明显。

代码 / 命令

-- MySQL:查看 change buffer 相关配置(口径以版本为准)
SHOW VARIABLES LIKE 'innodb_change_buffer%';

常见误区

  • 误区:change buffer 对所有二级索引生效。正解:仅非唯一二级索引可用,唯一索引需即时校验。
  • 误区:change buffer 缓存的是数据行。正解:缓冲的是索引页变更,合并时才写入索引页。

追问

  • 问:change buffer 什么时候合并?答:后台线程按策略合并,或该页被读入缓冲池时合并;合并行为见 05.2.1。
  • 问:写多读少表如何降低唯一索引成本?答:保证唯一约束前提下评估业务键设计,避免不必要的唯一二级索引。

05.1.1.4-015 优化器为什么会选错索引?如何干预?

频率 ★★★★☆ | 难度 高级

一句话结论

选错索引通常源于统计不准、分布倾斜、区分度低估或多索引成本估算误差;优先通过更新统计、改写 SQL、调整索引解决,FORCE INDEX 只作为短期兜底,长期绑定索引名有维护风险。

概念解释

  • 统计偏差:采样不足或长期未 ANALYZE,行数估算失真。
  • 数据倾斜:某值占绝大多数,通用估算不代表实际分布。
  • 估算误差:多路径代价模型在复杂条件下偏差放大。

原理流程

  1. 用执行计划对比候选索引的估算行数与代价。
  2. 更新统计信息后重新生成计划。
  3. 仍不合理再评估索引调整或语句级强制提示。

代码 / 命令

EXPLAIN SELECT ...;            -- 看 type/key/rows/filtered
ANALYZE TABLE t;               -- 更新统计信息

SELECT ... FORCE INDEX (idx_a);  -- 强制索引(谨慎,短期兜底)
SELECT ... USE INDEX (idx_a);    -- 建议索引
SELECT ... IGNORE INDEX (idx_b); -- 忽略索引

-- 查看优化器决策过程(MySQL)
SET optimizer_trace = 'enabled=on';
SELECT ...;
SELECT * FROM information_schema.optimizer_trace;
SET optimizer_trace = 'enabled=off';

常见误区

  • 误区:选错索引就直接 FORCE INDEX 固化。正解:数据分布变化后强制索引可能更慢,且绑定索引名维护困难;应先修统计与索引。
  • 误区:rows 估算与实际差很多是统计唯一问题。正解:也可能是列间相关、复杂条件或倾斜导致选择率估算失真。

追问

  • 问:optimizer_trace 能看出什么?答:各候选路径的代价比较与选择理由,用于定位估算偏差环节,详见 05.1.3.4。
  • 问:PG 怎么干预计划?答:调整 enable_* 开关、SET LOCAL 代价参数或建合适索引;语句级提示能力与 MySQL 不同。

05.1.1.4-016 FORCE/USE/IGNORE INDEX 分别怎么用?

频率 ★★★☆☆ | 难度 高级

一句话结论

FORCE INDEX 强制走指定索引,USE INDEX 建议优化器优先考虑,IGNORE INDEX 排除指定索引;三者都是语句级提示,覆盖优化器自动选择,使用前必须确认计划与数据分布稳定。

概念解释

  • FORCE:候选路径中排除未被强制的索引,绑定较强。
  • USE:仅作为建议,优化器仍可按代价选择其他路径。
  • IGNORE:把指定索引从候选中排除,常用于排除明显误选路径。

原理流程

  1. 先确认被提示索引在当前数据分布下确实更优。
  2. 在语句后追加对应提示,复跑计划验证。
  3. 评估提示的长期维护成本,能通过索引/统计解决则移除提示。

代码 / 命令

SELECT * FROM t FORCE INDEX (idx_a) WHERE a = 1;
SELECT * FROM t USE INDEX (idx_a)  WHERE a = 1;
SELECT * FROM t IGNORE INDEX (idx_b) WHERE a = 1;

常见误区

  • 误区:提示写上去就一劳永逸。正解:数据分布与查询形态变化后,被绑定索引可能不再合适,需定期复核。
  • 误区:USE INDEX 一定会走该索引。正解:它只是建议,优化器仍可能选其他路径。

追问

  • 问:索引改名后提示会怎样?答:提示按索引名引用,改名需同步修改语句,否则提示失效或报错。
  • 问:长期依赖提示说明什么?答:通常反映统计、索引设计或查询形态存在根因问题,应回到方案层面解决。

05.1.1.4-017 统计信息过期如何更新?

频率 ★★★☆☆ | 难度 进阶

一句话结论

大批量增删改后统计信息可能过期,MySQL 用 ANALYZE TABLE 手动更新,PG 由 autovacuum 触发 ANALYZE 或手动执行;更新后重新生成计划,再核对估算与实际行数偏差。

概念解释

  • 过期表现:基数/直方图与当前分布不符,估算行数偏差大。
  • 手动更新:显式触发统计重算。
  • 自动机制:变更比例达到阈值后后台自动收集。

原理流程

  1. 识别偏差:计划估算行数与实际差距明显。
  2. 手动更新统计信息。
  3. 复跑计划,确认路径与估算改善;未改善再排查分布倾斜与条件复杂度。

代码 / 命令

-- MySQL
ANALYZE TABLE orders;

-- PostgreSQL
ANALYZE orders;
-- 自动分析由 autovacuum 按变更比例触发

常见误区

  • 误区:更新统计后所有计划立即刷新。正解:缓存计划何时失效重建由引擎机制决定,部分场景需触发重优化,边界行为随版本(待确认)。
  • 误区:统计越新计划一定越好。正解:统计只改善估算,索引缺失或写法问题无法靠统计解决。

追问

  • 问:什么操作后容易统计过期?答:批量导入、大批量删除、数据分布大幅倾斜调整之后。
  • 问:PG 扩展统计解决什么问题?答:建模列间相关与多列组合分布,详见 05.3.2。

05.1.1.4-018 MySQL 与 PostgreSQL 的统计机制有何差异?

频率 ★★★☆☆ | 难度 进阶

一句话结论

两者都采样收集基数、分布与相关性用于代价估算;MySQL InnoDB 统计可持久化、采样页数可配,PG 维护直方图、MCV 与扩展统计,随 autovacuum 更新,具体参数与结构以当前版本文档为准。

概念解释

  • MySQL:索引基数采样估算,持久化/非持久化模式,样本页数参数可调。
  • PG:列级直方图、最常见值(MCV)、相关性,支持多列扩展统计。
  • 更新触发:MySQL 手动 ANALYZE 或自动重算阈值;PG 由 autovacuum 按变更比例触发。

原理流程

  1. 采样数据页,收集分布与密度信息。
  2. 优化器按统计估算选择率与行数。
  3. 数据大幅变化后重新收集,避免估算失真。

代码 / 命令

-- MySQL:查看索引统计
SHOW INDEX FROM orders;

-- PostgreSQL:查看列统计与扩展统计
SELECT attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'orders';

常见误区

  • 误区:两引擎统计结构一一对应。正解:MySQL 偏重索引基数采样,PG 提供更细的直方图/MCV/扩展统计,口径不能直接套用。
  • 误区:统计信息只影响索引选择。正解:连接顺序、JOIN 算法与排序代价估算同样依赖统计。

追问

  • 问:PG 扩展统计解决什么单统计解决不了的问题?答:列间相关性与多列组合分布,避免独立估算的选择率偏差,详见 05.3.2。
  • 问:统计偏差在计划里怎么暴露?答:估算行数与 EXPLAIN ANALYZE 实测行数差距大,是判断统计失真的直接信号。

参考来源