Java面试速通（11）|MySQL 索引与 SQL 优化｜13 题速通

🔸 中级难度📚 MySQL

「一条 SQL 跑了 2 秒，你怎么在 5 分钟内定位？」

这是 MySQL 面试最常被追问的一句，也是最考真实调优经验的一句。本篇从底层讲到实操：B+ 树为什么是索引的唯一解、聚簇索引和回表是什么关系、最左前缀为什么一跳就失效、覆盖索引和索引下推各解决了什么、索引失效的八种典型场景、联合索引字段顺序怎么定、explain 到底该看哪四列、深分页和 count/order by/group by 怎么优化。13 题、每题约 330 字。

🗂️ 本篇速查索引（一屏总览）

题号	一句话结论
Q01	数据类型选型：小、定、简，金额用 decimal
Q02	char 定长、varchar 变长；尽量 NOT NULL + 默认值
Q03	inner 取交集、left 保左表；having 过滤分组
Q04	连接→分析→优化→执行，优化器选索引可能选错
Q05	B+ 树矮胖 + 叶子链表：3 层两千万，范围查询快
Q06	聚簇叶子存整行、二级叶子存主键，回表 = 多查一次
Q07	最左前缀：跳列失效，范围右侧也失效
Q08	覆盖索引免回表；ICP 把过滤下推到引擎层
Q09	索引失效八场景（函数/转换/左模糊/OR 等）
Q10	联合索引顺序：等值 → 排序 → 范围
Q11	explain 看 type / key / rows / Extra
Q12	深分页用延迟关联或游标分页
Q13	count/order by/group by 各自的优化要点

读法建议：Q05–Q09 是索引五连核心题，几乎逢面必问，建议连着读；Q11–Q13 是调优实操，答出来能直接体现你处理过慢 SQL；Q01–Q04 偏基础，用于建立完整链路认知。

① 基础与执行链路

Q01🔰 初级

🎙️ 面试官：MySQL 常见数据类型怎么选型？

💬 面试者：

三条原则：更小、更定、更简单——在满足业务的前提下，占用字节越少越好（索引树更矮、比较更快、内存命中率更高）。

整型：TINYINT(1B) / SMALLINT(2B) / INT(4B) / BIGINT(8B)。主键优先 BIGINT 自增（用 INT 到 21 亿就爆了，分库分表后更危险）；状态、类型这类枚举用小整型（TINYINT）。注意 INT(11) 里的 11 只是显示宽度，不影响存储范围——这是最容易误解的一点。

字符串：CHAR 定长、VARCHAR 变长（VARCHAR 额外需要 1~2 字节记长度）。VARCHAR(255) 里的 255 是字符数不是字节（UTF8mb4 下一个汉字 4 字节）。别无脑给 255——MySQL 建索引时按定义长度算，过长会导致索引超限或内存浪费；临时表和排序也按定义长度分配内存。

时间：DATETIME（8B，范围大、不受时区影响）vs TIMESTAMP（4B，范围到 2038 年、随时区转换）。推荐 DATETIME，除非你要跨时区自动转换。千万别用字符串存时间（无法比较、无法用时间函数、占空间）。

金额：必须用 DECIMAL，绝不能用 FLOAT/DOUBLE——浮点数是二进制近似表示，0.1 + 0.2 != 0.3，对账会出人命。如果追求极致性能，可用 BIGINT 存「分」（amount * 100），这是很多支付系统的做法。

大字段：TEXT/BLOB 不要放在主表——它们会让行变长、Buffer Pool 命中率暴跌，且排序时可能落磁盘。正确做法是拆到子表，主表只留 ID。

📌 速记：小、定、简：整型优先、长度够用即可、金额用 decimal 不用 float。

Q02🔰 初级

🎙️ 面试官：char 与 varchar 的区别？为什么建议字段设为 NOT NULL？

💬 面试者：

char vs varchar：① 存储——CHAR(N) 是定长，不足 N 会在尾部补空格（读取时 MySQL 会自动去掉尾部空格，这也是 CHAR 的一个坑：'abc ' 和 'abc' 存进去查出来一样）；VARCHAR(N) 是变长，只存实际字符 + 1~2 字节的长度前缀。② 空间——CHAR 浪费空间但不易产生碎片；VARCHAR 省空间但更新变长会产生碎片。③ 性能——定长的 CHAR 读写更快（位置可直接算出来），所以「长度基本固定」的字段（如 MD5、手机号、身份证、状态码）用 CHAR；长度波动大的（如昵称、地址、标题）用 VARCHAR。

为什么建议 NOT NULL（这是面试高频追问）：① 语义更清晰——NULL 表示「未知」，和「空字符串」「0」是完全不同的含义，混用极易出 Bug（比如 COUNT(name) 不统计 NULL，但 COUNT(*) 统计）。② 索引效率——NULL 值会让索引、索引统计和值比较变得更复杂，优化器更难选对执行计划。③ 存储——可为 NULL 的列需要额外的「NULL 标志位」（每行 1 字节的 bitmap），且 NULL 值会占用索引空间。④ 查询陷阱——WHERE col != 'a' 永远查不到 col IS NULL 的行（因为 NULL 参与任何比较结果都是 NULL，即「未知」），这是线上最常见的「数据莫名丢失」原因；判断 NULL 必须用 IS NULL / IS NOT NULL。

实践建议：建表时尽量 NOT NULL + 默认值（字符串给 ''、数值给 0），只在「确实要表达未知」时才允许 NULL。另外 NULL 不进索引的说法不准确——InnoDB 的索引是允许存 NULL 的，只是处理更复杂。

📌 速记：char 定长补空格、varchar 变长多 1~2 字节；NULL 占标志位且索引更难优化。

Q03🔰 初级

🎙️ 面试官：JOIN 的几种类型？where 与 having 的区别？

💬 面试者：

JOIN 四类（记住「以谁为准」就行）：① INNER JOIN——取两表交集，只保留能匹配上的行。② LEFT JOIN——以左表为准，左表所有行都保留，右表匹配不上就填 NULL（最常用）。③ RIGHT JOIN——以右表为准（实际很少用，改成 LEFT JOIN 调换表序即可）。④ FULL OUTER JOIN——MySQL 不支持，要用 LEFT JOIN UNION RIGHT JOIN 模拟。

一个高频坑：LEFT JOIN 里把过滤条件写在 ON 还是 WHERE，结果完全不同——ON 里的条件在「生成临时表」时生效，右表不匹配只是填 NULL，左表行仍然保留；WHERE 里的条件是在临时表生成之后再过滤，会把「右表为 NULL」的行直接筛掉，LEFT JOIN 就退化成 INNER JOIN 了。写 SQL 时一定要想清楚你要哪种。

where vs having：① 作用对象——WHERE 过滤「行」（在分组前），HAVING 过滤「分组」（在分组后，通常配合 GROUP BY）。② 能否用聚合函数——WHERE 后面不能跟聚合函数（如 SUM/COUNT，因为此时还没分组），HAVING 可以（如 HAVING COUNT(*) > 5）。③ 能否用索引——WHERE 的条件通常能走索引；HAVING 是对结果集再过滤，用不上索引。

性能提醒：能用 WHERE 先用 WHERE——先过滤再分组，参与分组的数据量越小越好（HAVING 是等分组算完才过滤，白干了很多活）。

📌 速记：inner 取交集、left 保左表全量；where 过滤行、having 过滤分组。

Q04🔸 中级

🎙️ 面试官：一条 SQL 在 MySQL 内部是怎么执行的？

💬 面试者：

五层架构，两条链路：Server 层（连接器、分析器、优化器、执行器，所有存储引擎共用）+ 存储引擎层（InnoDB / MyISAM，插件式，负责真正的存取）。

一条 SELECT 的完整流程：① 连接器——管理连接、做身份鉴权和权限校验（注意：连接建立时读一次权限，之后改权限要重连才生效）。② 查询缓存（MySQL 8.0 已彻底移除）——因为表一更新缓存就全失效，命中率极低，弊大于利。③ 分析器——词法分析（识别出 SELECT、表名、列名）+ 语法分析（判断是否符合 SQL 语法）。④ 优化器——决定「怎么执行」：用哪个索引、JOIN 的顺序、是否转成 EXISTS 等，它基于「成本估算」（扫描行数、是否回表、是否排序）选一个它认为最优的方案。⑤ 执行器——先校验权限，再调用存储引擎的接口一行行取数据，把结果返回客户端。

一条 UPDATE 的特殊之处：要额外走日志与事务链路——取数据 → 改内存 → 写 undo log（可回滚）→ 写 redo log（prepare 状态）→ 写 binlog → 提交 redo log（commit 状态），这就是两阶段提交（详见 S12 · MySQL 事务与日志）。

面试加分点：优化器会选错索引——它按「预估扫描行数」估算成本，而这个预估来自索引的基数（Cardinality）统计，统计不准就会选错。典型场景：数据量突增后没及时 ANALYZE TABLE、或者 SQL 里有复杂表达式导致估算失真。解法：ANALYZE TABLE 重算统计、加 FORCE INDEX 强制指定、或改写 SQL 让它更好估算。这也是为什么「我明明建了索引却没走」的常见原因之一。

📌 速记：连接器→分析器→优化器→执行器→存储引擎，优化器决定用哪个索引。

② 索引

Q05🔸 中级

🎙️ 面试官：为什么索引要用 B+ 树，而不是 B 树 / 红黑树 / 哈希？

💬 面试者：

先明确索引的目标：减少磁盘 IO 次数。数据库的数据在磁盘上，一次磁盘 IO 约 10ms，一次内存访问约 100ns，差十万倍。所以索引结构的优劣，几乎就等价于「树的高度」——树越矮，查一次要读的磁盘块越少。

为什么不用红黑树 / 二叉搜索树：它们是二叉树，树太高。存 1000 万行，树高约 24 层，最坏要 24 次磁盘 IO，不可接受。

为什么不用 B 树：B 树是多路平衡树（一个节点存多个 key，树高降到 3~4 层），但它有两个致命短板：① 每个节点都存数据（整行记录），导致一个磁盘页（16KB）能放的 key 变少，树就变高了；② 叶子节点之间没有链表，做范围查询（BETWEEN、>）时要在树里来回中序遍历，产生大量随机 IO。

B+ 树的两处关键改进（这就是它被选中的原因）：① 非叶子节点只存 key 不存数据——一个 16KB 的页能塞下约 1170 个 key（假设 key 8B + 指针 6B），3 层树就能索引 1170 × 1170 × 16 ≈ 2190 万行，而且非叶子页基本可以常驻内存，实际只需 1 次磁盘 IO。② 叶子节点之间用双向链表相连——范围查询时先在树里定位到起点，然后顺着链表顺序扫就行了，全是顺序 IO，极快。

为什么不用哈希：哈希的等值查询是 O(1)，比 B+ 树还快，但它只支持等值——不支持范围查询、不支持排序、不支持最左前缀，而且哈希冲突会退化为链表。所以只有「纯等值查询」场景才用哈希索引（如 InnoDB 的自适应哈希索引，由引擎自动建立）。

结构	树高（千万级）	范围查询	为何落选 / 入选
红黑树 / 二叉	约 24 层	支持（中序）	树太高，IO 次数爆炸
B 树	3~4 层	需回溯，随机 IO	非叶存数据，树高；叶子无链表
B+ 树	3~4 层	叶子链表，顺序 IO	✅ 矮胖 + 范围查询快
哈希	O(1) 等值	不支持	只适合纯等值场景

📌 速记：B+ 树矮胖、叶子链表相连：3 层存两千万，范围查询不用回溯。

Q06🔸 中级

🎙️ 面试官：聚簇索引与二级索引的区别？什么是回表？

💬 面试者：

聚簇索引（Clustered Index）：InnoDB 的主键索引就是聚簇索引——它的叶子节点存的是「整行数据」，数据文件本身就是按主键顺序组织的一棵 B+ 树。所以一张表只能有一个聚簇索引（数据只能按一种顺序物理存储）。没显式建主键时，InnoDB 会选第一个非空唯一索引，再没有就自动生成一个 6 字节的隐藏 ROWID。

二级索引（Secondary Index / 辅助索引）：叶子节点存的不是整行，而是「索引列 + 主键值」。所以通过二级索引查到主键后，如果要拿其他列，还得再拿主键去聚簇索引里查一次——这第二次查询就叫「回表」。

为什么这么设计：① 省空间——如果每个索引叶子都存整行，数据会被复制 N 份；② 一致性——行移动或页分裂时，只需改聚簇索引，二级索引不用动（因为它存的是主键，不是物理地址，这也是 InnoDB 相比 MyISAM 的优势之一）。

回表的代价与优化：回表是随机 IO（主键值在聚簇索引里通常不连续），这就是「为什么有时候明明走了索引还是很慢」——回表次数太多。两条优化路径：① 用覆盖索引彻底避免回表（见 Q08）；② 让回表变成顺序 IO——MySQL 5.6 起的 MRR（Multi-Range Read）优化会先按主键排序再回表，把随机 IO 转成接近顺序的 IO。

补充：MyISAM 没有聚簇索引——它的索引叶子存的是数据文件的物理地址，主键索引和二级索引结构完全一样，这就是为什么 MyISAM 的主键不是必须的。

📌 速记：聚簇索引叶子存整行，二级索引叶子存主键；回表就是再查一次主键树。

Q07🔸 中级

🎙️ 面试官：什么是最左前缀原则？举一个违反后失效的例子？

💬 面试者：

原则：联合索引 (a, b, c) 在 B+ 树里是按「先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序」组织的。所以索引的「有序性」是逐级依赖的——只有在前面的列确定的情况下，后面的列才是有序的。这就决定了：查询条件必须从最左列开始、且不跳过中间的列，才能用上后续列的索引。

失效例子（重点记这两个）：① 跳过最左列——索引 (name, age)，查 WHERE age = 20，索引完全失效（因为 age 在全局是无序的，只有 name 相同时才有序）。② 范围查询右侧失效——索引 (a, b, c)，查 WHERE a = 1 AND b > 10 AND c = 5，c 用不上索引。原因是 b > 10 匹配到的是一批 b 值，在这些 b 值内部 c 才有序，但跨 b 值 c 就无序了，所以 c 只能被当作过滤条件逐行判断。

注意区分：= 和 IN 不算「范围」——WHERE a = 1 AND b IN (1,2,3) AND c = 5 三个列都能用上（因为 IN 本质是一组等值，b 被确定到有限几个值后，c 在每个 b 值内部仍有序）。真正造成右侧失效的是 >、<、BETWEEN、LIKE 'x%' 这类范围条件。

一句话记忆：最左前缀 = 从最左列开始、连续匹配、遇到范围就停。

-- 索引：idx_abc (a, b, c)

WHERE a = 1 -- ✅ 用上 a
WHERE a = 1 AND b = 2 -- ✅ 用上 a, b
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 用上 a, b, c（最完整）
WHERE a = 1 AND c = 3 -- ⚠️ 只用上 a（跳过了 b，c 失效）
WHERE b = 2 -- ❌ 索引失效（没从最左列开始）
WHERE a = 1 AND b > 10 AND c = 3 -- ⚠️ 用上 a, b；c 失效（范围右侧停止）
WHERE a = 1 AND b IN (1,2) AND c = 3 -- ✅ 三个都用上（IN 是等值不是范围）

📌 速记：最左前缀：跳过最左列索引直接失效，范围查询右侧列也失效。

Q08🔸 中级

🎙️ 面试官：什么是覆盖索引？索引下推（ICP）解决了什么问题？

💬 面试者：

覆盖索引（Covering Index）：查询需要的列，全都在这个索引里，不需要回表。比如有索引 (name, age)，查 SELECT age FROM user WHERE name = '张三'——二级索引的叶子已经含 name、age 和主键，不需要再回聚簇索引查整行，explain 的 Extra 里会出现 Using index。

为什么覆盖索引是重要的优化手段：它把「二级索引扫描 + N 次随机回表」变成「纯二级索引顺序扫描」，回表是随机 IO，往往是慢 SQL 的元凶。典型优化手法：把查询里频繁返回的少数几个列，加到索引里做成联合索引（即「冗余列进索引」），代价是索引变大、更新变慢，所以要权衡。注意：SELECT * 几乎永远无法走覆盖索引，这也是「不要写 SELECT *」的核心原因之一。

索引下推（ICP，Index Condition Pushdown，MySQL 5.6 引入）：把「本该由 Server 层做的 WHERE 条件过滤」，下推给存储引擎层去做。

它解决了什么：没有 ICP 时，二级索引只能用来定位「索引列的匹配项」，其余条件要等回表拿到整行后，由 Server 层判断——这意味着「不符合条件的行也白回表了一次」。有了 ICP 后，引擎在扫描二级索引时就把那些「索引里包含但不属于索引定位条件」的列判断掉，判断为 false 的行直接跳过，不回表。效果：回表次数大幅下降。

例子：索引 (name, age)，查 WHERE name LIKE '张%' AND age = 20。没有 ICP：name LIKE '张%' 定位到 1000 条，全部回表，Server 层再筛 age = 20，剩 10 条——回表 1000 次。有 ICP：引擎扫描索引时直接判断 age = 20（age 就在索引里），只有 10 条需要回表——回表 10 次。explain 里显示 Using index condition。

📌 速记：覆盖索引免回表；索引下推把过滤下推到引擎，减少回表次数。

Q09🔸 中级

🎙️ 面试官：索引失效有哪些典型场景？

💬 面试者：

八种高频失效场景（背下来基本够用）：

① 对索引列用函数或表达式——WHERE YEAR(create_time) = 2024、WHERE amount * 2 > 100。原因：索引存的是列的原始值，函数运算后的值无法定位。改法：把运算移到右边——WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'。注：MySQL 8.0 起支持函数索引（给 YEAR(create_time) 建索引），可解。

② 隐式类型转换——user_id 是 VARCHAR，却写 WHERE user_id = 123（数字不带引号）。MySQL 会把字符串列转成数字比较，等于对列施加了函数，索引失效。改法：类型写对，字符串加引号。这是极易踩的坑。

③ 隐式字符集/排序规则转换——两表 JOIN 时关联字段字符集不一致（如一个是 utf8mb4 一个是 utf8），会导致被驱动表的索引失效。

④ LIKE 以 % 开头——WHERE name LIKE '%张三'（左模糊）无法用索引（因为不知道前缀）；LIKE '张三%'（右模糊）可以用索引。

⑤ OR 连接非索引列——WHERE a = 1 OR b = 2，若 b 无索引，则整个条件都不走索引（优化器会全表扫）。

⑥ 违反最左前缀——跳列或范围查询右侧列（详见 Q07）。

⑦ 使用 !=、<>、NOT IN、IS NOT NULL——这些是「否定性」条件，匹配范围大，优化器通常判定全表扫更快。注：不绝对——若该列选择性极高（如 status 只有两个值且分布极不均），优化器仍可能选索引。

⑧ 优化器判定「全表更快」——当查询要返回的行数占比过高（通常经验值 >20%~30%）时，走索引要回表 N 次，反而不如顺序全表扫。这是正常的优化器行为，不是 Bug。

排查手段：一律用 explain 看 key 和 rows——key 为 NULL 就是没走索引，rows 是预估扫描行数。

📌 速记：失效八种：函数/隐式转换/左模糊/OR/不等于/最左缺失/范围右侧/低选择性。

Q10🔸 中级

🎙️ 面试官：联合索引的字段顺序怎么定？什么叫索引选择性？

💬 面试者：

三条排序规则（按优先级）：① 区分度（选择性）高的列放左边——让索引在第一层就过滤掉最多的数据。② 等值查询的列优先于范围查询的列——因为范围列之后的所有列都用不上索引（见 Q07），所以范围列要尽量靠后。③ 如果某列常用于排序/分组（ORDER BY/GROUP BY），放在适当位置以利用索引的有序性，避免 filesort。

一句话口诀：等值列 → 排序列 → 范围列，同类中区分度高的在前。

索引选择性（Selectivity / Cardinality）：选择性 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好（表示每行几乎唯一）。计算：SELECT COUNT(DISTINCT name) / COUNT(*) FROM user。经验标准：选择性 > 0.1 才值得建索引；像 gender（只有男/女）、is_deleted（0/1）这种选择性极低的列，单独建索引毫无意义（但作为联合索引的前缀仍可能有用，因为要利用最左前缀）。

Cardinality 是什么：SHOW INDEX FROM t 里的 Cardinality 就是「该索引不重复值的预估数量」。注意它是采样估算的，不是精确值——InnoDB 随机采样若干页来推算，所以可能不准，这会导致优化器选错索引。修正方法：ANALYZE TABLE t（重新采样计算，会加锁，注意在低峰执行）。

实战提醒：用 SELECT COUNT(DISTINCT a, b) / COUNT(*) 算联合索引的选择性，而不是分别算两列——因为 (a, b) 的组合区分度才是这个联合索引真正的过滤能力。

📌 速记：区分度高的放左边；联合索引顺序 = 等值列 → 排序列 → 范围列。

③ 优化实战

Q11🔸 中级

🎙️ 面试官：explain 重点看哪些字段？

💬 面试者：

四看：type、key、rows、Extra。

① type（访问类型，最重要）——从好到坏：system > const（主键/唯一索引等值，最多一行）> eq_ref（JOIN 时主键/唯一索引关联）> ref（普通索引等值）> range（范围查询）> index（全索引扫描，扫整个索引树）> ALL（全表扫描）。优化目标：至少到 range，最好到 ref；出现 ALL 或大数据表的 index 就要警惕。

② key（实际用到的索引）——NULL 表示完全没用索引。possible_keys 是「理论上可能用的索引」，key 是优化器最终选的，两者不一致往往说明优化器基于成本做了取舍（也可能选错）。

③ rows（预估扫描行数）——这是判断成本最直观的指标。注意它是估算值（基于统计信息）。优化目标：让 rows 尽量接近实际返回行数——如果 rows=100000 但实际只返回 10 行，说明索引没起到足够的过滤作用。

④ Extra（额外信息，藏着最多线索）——重点看这几个：Using index（好事：走了覆盖索引，没回表）；Using index condition（好事：走了索引下推 ICP）；Using where（正常：Server 层再次过滤）；Using filesort（红灯）——需要额外排序，无法利用索引有序性，考虑给 ORDER BY 的列建索引；Using temporary（红灯）——用了临时表，常见于 GROUP BY / DISTINCT / 子查询，性能差，重点优化对象。

补充字段：key_len（实际用到的索引字节数，可判断联合索引到底用了几列）；filtered（存储引擎返回的数据中，经 Server 层过滤后剩余的比例，百分比越大越好）。

📌 速记：explain 四看：type、key、rows、Extra（Using filesort、Using temporary 是红灯）。

Q12🔺 高级

🎙️ 面试官：慢 SQL 怎么优化？深分页 limit 1000000, 10 怎么解决？

💬 面试者：

排查四步法：① 抓到慢 SQL——开慢查询日志（slow_query_log=ON，long_query_time=1），或用 SHOW PROCESSLIST 看当前在跑的。② explain 看执行计划——按 Q11 的四看定位（80% 的问题在这一步就能看出来）。③ 定位瓶颈——是没走索引、走了错索引、回表太多、还是排序/临时表太重。④ 改写或加索引后再 explain 验证，并用 SHOW PROFILES 或 performance_schema 量化对比。

深分页为什么慢：LIMIT 1000000, 10 的执行方式是——先读出前 1000010 行，然后丢掉前 1000000 行，只返回最后 10 行。代价全在「白读的这一百万行」（而且是回表读的，随机 IO）。

三種解法：① 延迟关联（覆盖索引 + 自关联，最通用）——先用覆盖索引只查主键（不回表），拿到 10 个 ID，再用这 10 个 ID 回表取整行。把「一百万次回表」压缩成「10 次回表」，通常能快 10 倍以上。② 游标分页 / 上一页最大 ID（性能最好）——记下上一页最后一条的 ID，下一页用 WHERE id > last_id LIMIT 10，直接走主键定位，与页码无关，性能恒定。限制：不能跳页（只能「上一页/下一页」），适合信息流、App 下拉刷新。③ 业务上限制深翻页——大多数产品其实不需要精确的深翻页（搜索引擎也只给前 10 页），这是最省事的解法。

-- ❌ 原始：需扫描并回表 1000010 行，耗时数秒
SELECT * FROM orders WHERE status = 1 LIMIT 1000000, 10;

-- ✅ 方案一：延迟关联（覆盖索引只取 ID，10 次回表）
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders WHERE status = 1 LIMIT 1000000, 10) t
ON o.id = t.id;
-- 前提：建索引 (status, id)，子查询走覆盖索引不回表

-- ✅ 方案二：游标分页（性能恒定，推荐信息流场景）
SELECT * FROM orders WHERE status = 1 AND id > 1234567
ORDER BY id LIMIT 10; -- 用上一页最后的 id 作为游标

⚠️ 注意：延迟关联必须配合覆盖索引才有效——如果子查询 (status, id) 没有联合索引，它同样要回表一百万次，优化等于白做。建索引后再 explain 确认 Extra 出现 Using index。

📌 速记：深分页用「延迟关联」或「上一页最大 ID」，别让引擎扫掉百万行再丢。

Q13🔺 高级

🎙️ 面试官：count(*) / order by / group by 的优化要点？

💬 面试者：

count 三兄弟的区别与选型：count(*) —— 专门优化过，直接统计行数，不取值，最快；count(1) —— 与 count(*) 几乎等价（InnoDB 内部对二者处理一致）；count(col) —— 只统计该列非 NULL 的行，要取值判断，更慢。结论：统计总行数就用 count(*)，MySQL 5.7+ 已对它做了专门优化，不要再信「count(1) 更快」的旧说法。优化手段：InnoDB 的 count(*) 需要扫全表（因为 MVCC 使得行数对事务不唯一），所以大表计数要么用二级索引（二级索引比聚簇索引小，扫起来快），要么单独维护计数表或用 Redis。

order by 优化：核心目标是避免 filesort（explain 里出现 Using filesort 就是没利用索引有序性）。做法：给 ORDER BY 的字段建索引，且排序方向要一致（都是 ASC 或都是 DESC，MySQL 8.0 前不支持混合方向走索引）。关键限制：当 WHERE 条件与 ORDER BY 共存时，两者要能组成联合索引的最左前缀（如 WHERE a=1 ORDER BY b 需要索引 (a, b)）。另外：filesort 不一定慢——如果排序数据量小于 sort_buffer_size，是在内存中排的，很快；只有超过阈值落磁盘用临时文件归并时才慢。

group by 优化：它本质是「先排序再分组」，所以优化思路同 order by——给分组字段建索引，让它利用索引有序性，避免临时表。关键技巧：如果不需要排序，显式加 ORDER BY NULL，可以省掉一次默认排序（MySQL 5.7 及之前 GROUP BY 会隐式排序，8.0 起已移除隐式排序，加不加都一样了）。explain 里出现 Using temporary 表示用了临时表，是要重点优化的信号。

补充：group by 的另一个优化是「用索引直接分组」——如果分组列上有索引，MySQL 可以顺序扫描索引，遇到新值就分一组（Loose Index Scan），完全不用建临时表，这是最快的路径。

📌 速记：count 用二级索引更小；order by 走索引免排序；group by 加 order by null 免排序。

📝 本篇小结

① B+ 树是唯一解的因果链：目标是减少磁盘 IO → 所以要矮（多路而非二叉）→ 非叶只存 key 让页能装 1170 个索引项（3 层 2190 万行）→ 叶子加链表让范围查询变顺序 IO。这条因果讲出来，比背「B+ 树特点」强十倍。

② 回表是慢 SQL 的第一元凶——覆盖索引（免回表）和索引下推（少回表）都是围绕它做文章。记住：SELECT * 几乎不可能走覆盖索引，只查需要的列。

③ 调优落地三件套：explain 四看（type/key/rows/Extra）；索引失效八场景（函数、隐式转换、左模糊、OR、最左缺失、范围右侧、否定条件、全表更快）；深分页用延迟关联或游标分页。

关注 + 在看，更新不错过 👀

📌 点赞 · 在看 · 转发，助力更多小伙伴通关