06.5.8.5 MySQL 索引与执行计划
所属:全栈开发面试题库 / 06 后端框架与工程实践 / 06.5 PHP 后端 / 06.5.8 PHP 配套技术栈
06.5.8.5-001 为什么 InnoDB 索引选择 B+ 树而不是 B 树、哈希或二叉树?
一句话结论
B+ 树矮胖、叶子节点链表串联适合范围查询与排序,非叶子节点只存键不存数据,单页能放更多键、树高更低;B 树每个节点存数据导致扇出小,哈希只支持等值且范围无序,二叉树在大数据量下树高过大。
概念解释
InnoDB 索引以页(默认 16 KB,待确认版本默认值)为单位存储。B+ 树非叶子节点只放索引键与指针,单页容纳大量键,千万级数据树高通常 3~4 层;数据全在叶子节点并按序链表连接,范围扫描顺着链表走。哈希索引等值查找 O(1) 但不支持范围与排序;二叉树退化成链表时高度不可控。
原理流程
- 数据按主键有序组织为叶子页,页之间双向链表连接。
- 上层非叶子节点聚簇下层页的键边界,形成多叉树。
- 等值查询从根节点逐层定位到叶子页。
- 范围查询定位起点后沿叶子链表向后扫描。
代码或图示
// 对比索引结构适用场景(SQL 示意)
// 主键/唯一键:B+ 树,支持等值与范围
// CREATE TABLE item (id BIGINT PRIMARY KEY, title VARCHAR(100));
// 哈希索引:仅等值,Memory 引擎或自适应哈希,不作为通用索引方案
常见误区
- 以为 B+ 树与 B 树只是高度差异:关键区别是数据只在叶子、叶子链表支持范围扫描。
- 用哈希思路理解二级索引:二级索引同样是 B+ 树,支持范围与排序。
追问
- 树高怎么估算?答:按单页扇出(键数量)与总行数估算,3~4 层对应几次磁盘 IO;具体值以表数据量与页大小实测为准。
- 自适应哈希是什么?答:InnoDB 对热点索引页自动建哈希加速等值查找,由引擎内部维护,应用不直接控制。
06.5.8.5-002 聚簇索引与二级索引的区别,什么是回表?
一句话结论
聚簇索引的叶子节点直接存整行数据,主键即聚簇索引;二级索引叶子节点存主键值,查到主键后再回聚簇索引取整行,这一步叫回表;覆盖索引可避免回表。
概念解释
InnoDB 一张表只有一个聚簇索引(通常主键)。二级索引(普通索引)按索引列排序,叶子记录主键值。查询先用二级索引找到主键,再用主键查聚簇索引取完整行,两次 B+ 树查找。若查询列全部包含在二级索引中,无需回表即覆盖索引。
原理流程
- 二级索引 B+ 树按索引列定位,取出主键值。
- 拿主键到聚簇索引 B+ 树查找整行。
- 所需列都在二级索引中:直接返回,不回表。
- 回表次数随扫描行数增长,是慢查询常见开销。
代码或图示
// 二级索引 idx_title(title):按 title 查到 id,再回表取 row
// SELECT * FROM item WHERE title = '手机'; -- 需回表
// SELECT id, title FROM item WHERE title = '手机'; -- 覆盖索引,免回表
常见误区
- 以为所有索引叶子都存整行:只有聚簇索引存整行,二级索引存主键。
- 表没主键会怎样?答:InnoDB 选唯一非空键,否则生成隐藏主键作为聚簇索引。
追问
- 回表一定慢吗?答:扫描行数少时代价可控;大范围扫描时回表放大随机 IO,优化器可能转全表扫描。
- 联合索引叶子存什么?答:存联合索引列值 + 主键,顺序按索引列排序。
06.5.8.5-003 联合索引最左前缀原则如何理解?
一句话结论
联合索引 (a, b, c) 按列顺序排序,查询条件从最左列开始连续匹配才能用上索引;跳过左侧列、或范围列之后的列无法继续利用索引定位。
概念解释
B+ 树先按 a 排序,a 相同再按 b,b 相同再按 c。能用索引的条件:a 等值;a, b 等值;a, b, c 等值;a 等值 + b 范围。缺 a 直接查 b/c 无法有序定位。范围查询(>、<、BETWEEN、LIKE 前缀)之后的列只能在结果内过滤,不再用于索引定位。
原理流程
- 建索引时列顺序按「等值在前、范围在后、高区分度优先」排列。
- 查询条件从最左列开始匹配,连续命中索引列。
- 遇到范围条件,其后列退出索引定位。
- 跳过最左列的条件无法使用该联合索引。
代码或图示
// 索引:idx_user_status_time(user_id, status, created_at)
// 可用:WHERE user_id = 1 AND status = 1
// 可用:WHERE user_id = 1 AND status = 1 AND created_at > '2026-01-01'
// 不可用:WHERE status = 1(跳过 user_id)
// 范围后:WHERE user_id = 1 AND created_at > '...'(status 未用于定位)
常见误区
- 把列顺序随意排列:顺序决定排序优先级,等值列应放前面。
- 以为
IN也算中断最左前缀:IN等值集合仍可继续匹配后续列,按优化器计划确认。
追问
- 索引列顺序与 WHERE 书写顺序有关吗?答:无关,优化器按索引列顺序匹配,不按 SQL 中条件书写顺序。
- 联合索引能替代单列索引吗?答:最左列可单独被利用;非最左列的查询需要另建索引。
06.5.8.5-004 覆盖索引是什么,如何用 EXPLAIN 判断?
一句话结论
查询所需列全部包含在所选索引中,存储引擎直接从索引叶子返回数据,不必回表;EXPLAIN 的 Extra 列出现 Using index 即覆盖索引。
概念解释
覆盖索引减少回表随机 IO,是低成本优化手段。判断:SELECT 列表、WHERE 条件列、排序分组列都在索引内。Extra 显示 Using index 表示索引覆盖;若同时 Using where 表示在索引内再过滤。注意 SELECT * 基本无法覆盖。
原理流程
- 列出查询用到的全部列:投影、条件、排序、分组。
- 检查是否有索引覆盖这些列。
- EXPLAIN 确认 key 命中该索引且 Extra 含
Using index。 - 缺列时补索引列或收窄 SELECT 字段。
代码或图示
// 索引 idx_user_status(user_id, status)
// SELECT user_id, status FROM orders WHERE user_id = 1;
// EXPLAIN Extra: Using index(覆盖索引,不回表)
// SELECT * FROM orders WHERE user_id = 1; -- 无法覆盖,需回表
常见误区
- 把
Using index当成命中索引的唯一标志:命中索引用 key/type 判断,Using index额外表示覆盖。 - 为覆盖索引把所有列塞进索引:索引膨胀,写放大上升,需权衡。
追问
- 排序列也需要进索引吗?答:利用索引有序性避免 filesort 时,排序列也要在索引内并符合顺序。
- 覆盖索引能覆盖回表的关联吗?答:单表内覆盖;多表关联仍需按关联索引逐表评估。
06.5.8.5-005 索引下推(ICP)解决什么问题?
一句话结论
Index Condition Pushdown 把索引列上的过滤条件下推到存储引擎层,在索引遍历时先过滤,减少回表次数;没有 ICP 时存储引擎按索引取记录回表后再由服务层过滤。
概念解释
联合索引中部分条件列在最左前缀可用范围之外,老式流程是先回表取整行再过滤,回表量大。ICP 开启后,引擎在遍历索引时用索引上能判断的条件先过滤,只对通过的记录回表。EXPLAIN Extra 显示 Using index condition 表示使用了下推。
原理流程
- 索引定位到第一批候选记录。
- ICP 开启:索引列条件在引擎层先行过滤。
- 通过过滤的记录才回表取整行。
- Extra 出现
Using index condition确认生效。
代码或图示
// 索引 idx_name_age(name, age)
// WHERE name LIKE '张%' AND age > 18
// 无 ICP:按 name 前缀取出后回表,再过滤 age
// 有 ICP:索引遍历阶段先判 age,减少回表
// EXPLAIN Extra: Using index condition
常见误区
- 以为 ICP 改变索引选择:它只是减少回表,不决定走哪个索引。
- 非索引列条件也能下推:下推条件必须是索引包含的列。
追问
- ICP 默认开启吗?答:由系统变量
optimizer_switch的index_condition_pushdown控制,默认状态以版本为准,待确认后实测。 - ICP 与覆盖索引区别?答:ICP 减少回表次数但仍可能回表;覆盖索引完全不回表。
06.5.8.5-006 EXPLAIN 的 type 列有哪些取值层级?
一句话结论
type 描述访问类型,性能从好到差大致为 system > const > eq_ref > ref > range > index > ALL;生产查询至少应达到 range 及以上,出现 ALL 全表扫描需重点优化。
概念解释
const:主键/唯一键等值,单行;eq_ref:关联时主键/唯一键匹配单行;ref:普通索引等值匹配多行;range:索引范围扫描;index:遍历整棵索引树;ALL:全表扫描。type 越靠前扫描行数越少,优化空间越大。层级判断要结合 rows 与实际数据量。
原理流程
- 执行 EXPLAIN 查看每行 type。
- 对照层级定位全表扫描与索引扫描。
ALL/index优先检查条件列索引与过滤性。ref/range结合 rows 评估回表与扫描成本。
代码或图示
// EXPLAIN SELECT * FROM orders WHERE user_id = 1;
// type=ref:普通索引等值
// EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';
// type=range:范围扫描;type=ALL:全表扫描需优化
常见误区
- 看到
ref就认为没问题:ref 命中索引但扫描行数仍可能很大,要结合 rows。 index一定比range差:index 遍历索引树,覆盖索引场景下可能比回表全表扫描优。
追问
eq_ref出现在什么场景?答:多表关联时被驱动表用主键或唯一键匹配,每连接只取一行。system是什么?答:表只有一行记录的特殊 const,很少见。
06.5.8.5-007 EXPLAIN 的 key、key_len、rows、filtered 如何解读?
一句话结论
key 是实际选用的索引,key_len 显示索引使用的字节长度(判断联合索引用了几列),rows 是估算扫描行数,filtered 是过滤后剩余比例;四列结合判断索引利用程度与数据过滤性。
概念解释
key 为 NULL 表示未用索引。key_len 按索引列类型与长度计算,越长表示用到的索引列越多;字符集影响字节数(utf8mb4 每字符最多 4 字节,待确认表实际字符集)。rows 是优化器估算值,不是精确行数;filtered 越低说明回表/过滤后丢弃的行越多。
原理流程
- key 列确认命中索引,NULL 则排查索引缺失或失效。
- key_len 推算联合索引实际用到的列数。
- rows 评估扫描规模,过大需优化条件或索引。
- filtered 低说明索引过滤性差,考虑调整索引列顺序。
代码或图示
// EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1 AND status = 1;
// key: idx_user_status
// key_len: 判断 user_id、status 两列是否都参与索引定位
// rows: 估算扫描行数;filtered: 过滤比例
常见误区
- 把 rows 当精确行数:它是统计信息估算,仅供量级参考。
- 忽略 key_len:联合索引只用了前几列时,key_len 会暴露出来。
追问
- key_len 怎么算?答:按列类型长度、是否可空、字符集字节数累加;具体公式对照 InnoDB 行格式文档。
- filtered 高就一定好?答:还要看 rows 基数;rows 大、filtered 高的总数据量仍可能不小。
06.5.8.5-008 常见索引失效场景有哪些?
一句话结论
常见失效:索引列上用函数/运算、隐式类型转换、前置通配符 LIKE '%x'、不符合最左前缀、OR 连接非索引列、NOT/!= 过滤性差、前导空格或大小写转换未处理。
概念解释
索引按列值排序存储,对列做函数或算术运算后值顺序被破坏,优化器无法按原索引定位。字符串列与数字比较时发生隐式转换,相当于对列套函数。前置模糊匹配无法利用 B+ 树有序性。OR 两侧任一无索引,整句可能退化为全表扫描。
原理流程
- 查看 WHERE 条件列是否被函数、运算、类型转换包裹。
- 检查 LIKE 是否前置通配符、OR 是否含无索引列。
- 核对最左前缀与联合索引列顺序。
- 改写 SQL:条件移到参数侧、补索引或调整写法。
代码或图示
// 失效:对索引列用函数
// WHERE YEAR(created_at) = 2026
// 改写:WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
// 失效:字符串列隐式转换(phone 为 varchar)
// WHERE phone = 13800000000 -> WHERE phone = '13800000000'
// 失效:前置通配符 WHERE title LIKE '%手机'
常见误区
- 以为建了索引就一定走:条件写法破坏索引有序性时优化器不会用。
- 把
!=、NOT IN当等值条件:过滤性差时优化器倾向全表扫描。
追问
- 隐式转换怎么识别?答:EXPLAIN 中 key 为 NULL,对比字段类型与字面量类型;建表字段类型与传入参数保持一致。
- OR 怎么优化?答:两侧分别建索引用 UNION,或改为 IN;优化器是否合并执行计划以实测为准。
06.5.8.5-009 统计信息与优化器为什么会选错索引?
一句话结论
优化器基于统计信息(基数、页数、数据分布)估算成本选索引;统计信息过期、数据分布倾斜、索引区分度相近、回表成本估算偏差都会导致选错,表现为走了过滤性差的索引或全表扫描。
概念解释
InnoDB 统计信息记录索引基数估算,长时间未更新或大批量写入后失真。数据倾斜时少数值占大量行,按平均估算选错。优化器还要估算回表随机 IO 成本,覆盖度高的索引可能被误判。可更新统计信息、强制索引、调整索引设计来纠正。
原理流程
- 执行 EXPLAIN 对比各可选索引的估算成本与 rows。
- 检查统计信息是否过期:
ANALYZE TABLE更新。 - 分析数据分布倾斜,确认条件列区分度。
- 必要时用
FORCE INDEX临时引导,长期修复索引设计。
代码或图示
// 更新表统计信息(命令在数据库侧执行)
// ANALYZE TABLE orders;
// 临时引导索引:SELECT * FROM orders FORCE INDEX (idx_user) WHERE user_id = 1;
// 复查 EXPLAIN:key、rows、Extra 是否符合预期
常见误区
- 选错索引就怪优化器:先看统计信息与索引区分度,多数问题在数据分布与索引设计。
- 长期依赖 FORCE INDEX:数据增长后固定引导可能失效,应作为临时手段。
追问
- 统计信息什么时候更新?答:自动采样与手动 ANALYZE;采样参数与持久化策略以版本配置为准,待确认。
- 区分度怎么评估?答:
COUNT(DISTINCT col) / COUNT(*),接近 1 区分度高;低区分度列不适合单独建索引。
06.5.8.5-010 主键选型:自增主键与 UUID/业务主键对比?
一句话结论
自增主键顺序插入,B+ 树页填充连续、页分裂少;UUID 无序导致页分裂与碎片,索引体积更大;业务主键可能更新或复用,不适合做聚簇键;一般用自增或有序雪花 ID 做代理主键。
概念解释
聚簇索引按主键顺序组织数据。自增值顺序追加到叶子页末尾,写入友好。随机 UUID 插入位置分散,频繁触发页分裂与碎片,二级索引叶子也存主键,主键变长会放大所有二级索引。业务主键(手机号、订单号)若将来可能变更,会波及聚簇组织。
原理流程
- 评估主键是否有序、是否永不更新、长度大小。
- 高写入量表优先自增或有序生成 ID。
- UUID 作主键时评估碎片与索引体积,必要时用有序 UUID。
- 业务唯一约束放唯一索引,不承担聚簇键职责。
代码或图示
// 自增代理主键:有序写入
// CREATE TABLE orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) UNIQUE);
// UUID 随机无序:插入分散,页分裂多;有序 UUID 可缓解但仍有长度代价
常见误区
- 用长字符串业务号当主键:二级索引体积膨胀,写入碎片多。
- 以为 UUID 只是生成方式问题:它直接影响聚簇索引组织与写入性能。
追问
- 雪花 ID 是有序的吗?答:时间戳高位保证趋势递增,插入位置相对有序;具体生成规则按方案实现核对。
- 二级索引为什么受主键长度影响?答:二级索引叶子存主键,主键每字段变长,所有二级索引同步放大。
06.5.8.5-011 索引设计有哪些原则?
一句话结论
索引设计原则:高区分度列优先、联合索引等值在前范围在后、避免冗余与重复索引、控制索引数量、为高频查询量身定制;索引加速读但拖慢写并占存储。
概念解释
索引不是越多越好:每次 INSERT/UPDATE/DELETE 都要维护相关索引,写放大与存储成本上升。冗余索引(如已有 (a,b) 再建 (a))可评估删除;重复索引(同列同顺序多次建)直接清理。设计从高频 SQL 出发,覆盖条件、排序、分组列。
原理流程
- 收集高频查询:WHERE、ORDER BY、GROUP BY、关联列。
- 按区分度与查询顺序设计联合索引。
- 排查冗余/重复索引,合并可覆盖的索引。
- 上线后观察慢查询与写性能,定期复盘索引清单。
代码或图示
// 已有 idx(a,b),单列 idx(a) 属冗余,可评估删除
// SHOW INDEX FROM orders;
// 联合索引:等值列 user_id、status 在前,范围列 created_at 在后
// CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);
常见误区
- 每个查询列都单独建索引:联合索引可覆盖多条件,单独索引写放大高。
- 建完索引不复盘:表结构与查询变化后老索引变成冗余负担。
追问
- 怎么找冗余索引?答:对比各索引列前缀与顺序,
(a,b,c)覆盖(a)、(a,b);用工具或 SHOW INDEX 人工核对。 - 区分度低的列要不要建?答:低区分度列适合作为联合索引后续列,单独建索引收益小。
06.5.8.5-012 前缀索引适用什么场景,长度怎么定?
一句话结论
长字符串列(VARCHAR/TEXT)用前缀索引只索引前 N 个字符,节省索引空间;长度按区分度选取,使前缀区分度接近完整列;前缀索引不能用于覆盖索引与排序(按前缀排序语义不同)。
概念解释
长文本整列索引体积大,取前若干字符建索引可降低存储与写入成本。长度选择:取不同前缀数量与整列 distinct 数量的比值,接近 1 即可。前缀索引只解决前缀等值与前缀 LIKE,无法按完整列覆盖索引或利用索引排序。
原理流程
- 计算完整列 distinct 数量。
- 逐步增加前缀长度,计算各长度下 distinct 比值。
- 选比值接近 1 的最小长度建前缀索引。
- 评估是否需要覆盖索引或排序,需要则不用前缀索引。
代码或图示
// 评估前缀区分度(SQL 示意)
// SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(DISTINCT email) FROM users;
// CREATE INDEX idx_email_prefix ON users(email(10));
常见误区
- 前缀长度拍脑袋:过短区分度差,过长失去节省意义。
- 期望前缀索引做覆盖索引:索引只存前缀,完整列仍需回表。
追问
- 前缀索引能用于 ORDER BY 吗?答:按前缀排序与整列排序语义不一致,优化器一般不利用前缀索引排序。
- 哪些列适合前缀索引?答:邮箱、URL、长文本标题等长字符串且前缀区分度足够的列。
06.5.8.5-013 单列多索引与一个联合索引如何取舍?
一句话结论
多个单列索引在多数条件组合下只能选一个使用,无法合并利用(索引合并除外);常一起出现的条件应建联合索引,按等值在前排列;单列索引适合各自独立出现的查询。
概念解释
查询同时满足两个条件时,优化器通常选区分度最好的一个单列索引,另一个条件在结果内过滤。联合索引把多列组织在同一棵 B+ 树,条件连续命中。索引合并(index merge)可临时合并多个单列索引,但成本与稳定性不如联合索引,不作为常规设计目标。
原理流程
- 分析条件列是否总是同时出现在查询中。
- 总是组合出现:建联合索引,定好列顺序。
- 各自独立查询:保留对应单列索引。
- 检查是否存在可被联合索引替代的冗余单列索引。
代码或图示
// 常一起查:user_id + status,建联合索引
// CREATE INDEX idx_user_status ON orders(user_id, status);
// 仅按 status 独立统计的查询:再评估单列 status 索引是否必要
常见误区
- 给每个条件列都建单列索引指望组合生效:实际只走其一。
- 联合索引覆盖了独立查询后仍保留旧单列索引:冗余写放大。
追问
- 索引合并什么时候出现?答:优化器估算合并多个索引扫描成本更低时使用,EXPLAIN type 为 index_merge;具体计划以实测为准。
- 联合索引能替代多少单列索引?答:最左列可被独立查询利用,非最左列的独立查询仍需单列索引。
06.5.8.5-014 唯一索引与普通索引在写入性能上有差异吗?
一句话结论
唯一索引插入时必须检查唯一性(可能读索引页),普通索引只追加;但 InnoDB 变更缓冲(change buffer)对非唯一二级索引有优化,普通索引写入可批量合并;日常差异有限,唯一性约束优先保证业务正确。
概念解释
普通二级索引写入时若页不在缓冲池,可先记录到 change buffer,后续合并;唯一索引唯一性检查必须即时读页,无法利用 change buffer。读操作两者结构一致。业务需要防重复时唯一索引是硬约束,性能差异不应成为放弃约束的理由。
原理流程
- 写入记录时维护相关二级索引。
- 普通索引:页不在内存可先入 change buffer,延迟合并。
- 唯一索引:即时检查重复,冲突则报错。
- 评估时以业务唯一性需求为准,性能差异通过压测确认。
代码或图示
// 业务防重:唯一索引
// CREATE UNIQUE INDEX uk_request ON orders(request_key);
// 普通索引:辅助查询,不承担唯一性约束
// CREATE INDEX idx_user ON orders(user_id);
常见误区
- 为写入性能去掉唯一索引:重复数据靠应用层兜底,并发下极易漏防。
- 认为唯一索引读性能明显差:读路径结构相同,差异主要在写入检查。
追问
- change buffer 受什么参数控制?答:
innodb_change_buffer_max_size等,具体名称与默认值以版本为准,待确认。 - 唯一索引冲突报什么错?答:重复键错误,应用层捕获后转为业务提示;错误码按驱动与版本核对。
06.5.8.5-015 索引维护的写入放大与 DDL 代价是什么?
一句话结论
索引越多,每次写操作维护的 B+ 树越多,CPU、IO 与锁开销越大;在线加索引/改索引占用资源并可能锁表,需在低峰期执行或使用在线 DDL 工具,评估对主从延迟的影响。
概念解释
一条 INSERT 要写入聚簇索引与全部二级索引;DELETE/UPDATE 同样维护索引。索引数量直接决定写放大。DDL 建索引扫描全表并排序,大表执行时间长,期间锁与资源占用影响业务;在线 DDL 机制减少锁表但仍有拷贝与重建成本。
原理流程
- 统计表上索引数量与写入频率,评估写放大。
- 新增索引前确认对应查询确实高频且缺失索引。
- 大表 DDL 低峰执行,优先在线 DDL 或专用工具。
- 关注主从延迟与复制回放压力,必要时分批操作。
代码或图示
-- 在线加索引(具体算法与锁行为以 MySQL 版本为准,待确认)
-- ALTER TABLE orders ADD INDEX idx_user (user_id), ALGORITHM=INPLACE, LOCK=NONE;
-- 大表评估:先看表行数、现有索引与写入峰值
常见误区
- 随手加索引不评估写入代价:读少写多的表索引收益低。
- 高峰期大表 DDL:锁与 IO 压力叠加业务高峰,事故风险高。
追问
- ALGORITHM=INPLACE 一定不锁表吗?答:仍可能有元数据锁与 LOCK 子句控制;具体支持范围按版本核对。
- pt-online-schema-change 思路?答:影子表 + 触发器同步 + rename,第三方工具方案,详见分库分表与慢查询页面。
06.5.8.5-016 覆盖索引避免回表的实际案例怎么写?
一句话结论
把高频查询的投影列、条件列、排序列补进联合索引,使 EXPLAIN 出现 Using index;案例:按用户查订单列表只返回展示字段,索引包含用户 ID、状态与展示列,避免逐行回表。
概念解释
回表成本 = 扫描行数 × 随机 IO。覆盖索引把所需列都放在二级索引叶子,一次索引扫描返回全部数据。设计时只补真正高频返回的列,避免索引过宽。案例中列表页字段固定,适合覆盖;详情页整行返回则必须回表。
原理流程
- 拆解高频列表 SQL:WHERE 列、SELECT 列、ORDER BY 列。
- 设计联合索引覆盖这些列,顺序按定位优先。
- EXPLAIN 验证
Using index与扫描行数。 - 字段调整后复查索引覆盖度。
代码或图示
// 列表 SQL:SELECT id, status, amount, created_at FROM orders
// WHERE user_id = ? AND status = ? ORDER BY created_at DESC
// 索引:idx_user_status_cover(user_id, status, created_at, id, amount)
// EXPLAIN Extra: Using index(覆盖,不回表)
常见误区
- 把
SELECT *改成覆盖索引:整行字段不可能都进索引,应收窄投影列。 - 覆盖索引列无限加宽:写放大与存储上升,只覆盖高频列表字段。
追问
- 排序列放索引什么位置?答:等值列之后、与 ORDER BY 方向一致;否则仍可能 filesort。
- 覆盖索引对关联查询有用吗?答:逐表评估,被驱动表关联列与返回列都可覆盖时同样免回表。
06.5.8.5-017 排序与分组如何利用索引,filesort 与临时表怎么识别?
一句话结论
ORDER BY/GROUP BY 列与索引顺序一致且方向匹配时,可直接按索引有序返回;否则 Extra 出现 Using filesort(排序)或 Using temporary(临时表分组去重),数据量大时成本高。
概念解释
B+ 树叶子按索引列有序,排序条件命中索引顺序即可省去排序。filesort 不是磁盘文件排序的字面含义,而是引擎需额外排序步骤;temporary 指分组/去重使用临时表。优化器在索引有序性与回表成本间权衡,可能放弃索引顺序。
原理流程
- 查看 EXPLAIN Extra 是否含 filesort / temporary。
- 检查排序列是否在索引内、顺序与方向是否一致。
- 分组列与索引对齐,避免临时表。
- 无法利用索引时评估分页范围与数据量,控制排序规模。
代码或图示
-- 索引 idx_user_time(user_id, created_at)
-- WHERE user_id = 1 ORDER BY created_at DESC -- 索引有序,免 filesort
-- WHERE status = 1 ORDER BY created_at -- 未命中索引顺序 -> Using filesort
-- GROUP BY 列不在索引 -> Using temporary
常见误区
- 以为 filesort 一定很慢:小结果集排序代价可控;大结果集才需重点优化。
- 排序列方向不匹配:升序索引与降序排序混合时,索引有序性利用受限(版本支持反向扫描除外)。
追问
- 联合索引多列排序怎么匹配?答:前面等值列固定后,后续排序列按索引顺序;多列混合升降序的利用规则按版本核对。
- GROUP BY 优化手段?答:对齐索引顺序、减少分组基数;仍用临时表时评估结果集大小。
06.5.8.5-018 深分页为什么慢,如何优化?
一句话结论
LIMIT 100000, 20 要扫描并丢弃前 10 万行,越翻越慢;优化方式:延迟关联(先用覆盖索引定位主键再回表)、游标/上一页末尾 ID 作为条件继续向后扫,避免大偏移量。
概念解释
深分页偏移量大时,数据库先按索引取前 offset+size 行,丢弃 offset 行,回表与排序成本随偏移增长。延迟关联:内层用覆盖索引只取主键与排序列,定位到目标范围后再关联回表取整行。游标方案记住上一页最后一条的排序键,下一页用 WHERE key < lastKey 继续扫描。
原理流程
- 识别高偏移 LIMIT 查询,评估扫描行数。
- 改写为延迟关联:子查询覆盖索引取主键,外层回表。
- 可翻页场景改用游标条件,按排序键定位下一页。
- 禁止跳页的业务接受游标模式;必须跳页时限制最大页码。
代码或图示
// 深分页原写法:LIMIT 100000, 20(扫描丢弃 10 万行)
// 延迟关联:
// SELECT o.* FROM orders o
// JOIN (SELECT id FROM orders WHERE user_id = 1 ORDER BY created_at DESC, id DESC
// LIMIT 100000, 20) t ON o.id = t.id;
// 游标:WHERE created_at < :lastTime AND id < :lastId ORDER BY ... LIMIT 20
常见误区
- 偏移量加索引就快:索引仍要遍历丢弃前 offset 行。
- 游标排序键不唯一:并列值导致翻页漏行/重行,需加唯一键做 tie-break。
追问
- 跳页需求怎么折中?答:限制最大页码,或按页码范围预计算;超深跳页单独走检索方案。
- 延迟关联为什么快?答:内层走覆盖索引不回表,只对最终 20 行回表。
06.5.8.5-019 EXPLAIN Extra 常见值怎么解读?
一句话结论
Extra 常见值:Using index 覆盖索引、Using where 服务层过滤、Using index condition 索引下推、Using filesort 额外排序、Using temporary 临时表、Using join buffer 关联用缓冲;逐个结合 type 与 rows 判断。
概念解释
Extra 补充执行细节:Using where 表示存储引擎返回后服务层再过滤;join buffer 表示关联未利用索引,用缓冲块批量匹配。出现 temporary、filesort 且 rows 大时重点优化;多个值同时出现时按执行流程叠加理解。
原理流程
- 阅读 Extra 各标志,区分索引利用、过滤、排序、临时表。
- 关注高成本标志:temporary、filesort、join buffer。
- 结合 type/rows 判断标志对应的实际代价。
- 改写 SQL 或补索引后复查 EXPLAIN。
代码或图示
// EXPLAIN 输出解读示例
// Extra: Using index; Using where -> 覆盖索引 + 索引内过滤
// Extra: Using filesort -> 需要额外排序
// Extra: Using temporary; Using filesort -> 分组临时表 + 排序,大结果集需优化
常见误区
- 看到
Using where就判失败:它只表示存在条件过滤,命中索引时常见。 - 忽略
join buffer:关联缺索引时缓冲匹配,数据量大时性能差。
追问
Impossible WHERE是什么?答:条件恒假,优化器判断无可行行,检查 SQL 条件是否写错。Select tables optimized away?答:聚合查询可直接从索引读取结果,无需遍历表,属良好计划。
06.5.8.5-020 PHP 侧如何配合索引写出友好查询?
一句话结论
PHP 应用层配合:禁止 SELECT *、控制 IN 列表大小、分页用游标、参数类型与字段一致、避免在代码里拼函数条件;查询构造器生成的 SQL 也要用 EXPLAIN 复核,ORM 默认查询可能带上多余列与关联。
概念解释
索引优化不只在数据库侧:应用传入参数类型不匹配会引发隐式转换;SELECT 列过宽导致无法覆盖;大 IN 列表使优化器计划不稳;循环单条查询叠加 N+1。ORM 生成的 SQL 需开启日志复核,预加载与字段选择显式控制。
原理流程
- 代码中显式指定查询列,不取全表。
- 入参与字段类型一致,条件表达式不包裹索引列。
- 批量查询控制 IN 大小,列表用延迟关联或游标分页。
- 慢 SQL 日志捞取后逐条 EXPLAIN,反馈到代码改写。
代码或图示
// 友好:指定列、类型一致、IN 受控
// $db->prepare("SELECT id, status, amount FROM orders WHERE user_id = ? LIMIT 20");
// 避免:SELECT *、WHERE phone = ?(参数传数字)、IN (上百个 id)
// ORM 场景:显式 select 字段与 with 关联,关闭不必要的预加载
常见误区
- 依赖 ORM 自动优化:生成 SQL 可能循环查询、带多余关联,必须看实际 SQL 与执行计划。
- 在 PHP 里做内存分页/排序:大数据量应下推数据库,避免全量取到应用层。
追问
- 怎么拿到 ORM 实际 SQL?答:开启数据库查询日志或框架调试栏,记录绑定参数后 EXPLAIN 复核。
- 大 IN 列表怎么拆?答:分批查询后在应用层合并,或改为临时表/关联;具体方案按数据量评估。