EXPLAIN 读法与连接算法

• EXPLAIN 只做计划生成，不执行 SQL，输出的是优化器基于统计信息的估算值。
• EXPLAIN ANALYZE 会真正执行 SQL，输出每个节点的实际行数、实际耗时与循环次数。
• 两者结合可以暴露估算误差与执行瓶颈。

EXPLAIN 只生成并展示执行计划，不执行查询，因此输出的是优化器基于表统计信息估算的 cost、rows、width，其代价是"打印计划"而非"执行"。EXPLAIN ANALYZE 会真实执行该语句并测量每个节点实际处理的行数（actual rows）、实际耗时（actual time，毫秒）、循环次数（loops），同时在计划末尾输出 Planning Time、Execution Time 与总耗时。两者对比时，若 actual rows 与 estimated rows 差距很大，说明统计信息过旧或估算模型失效，需要 ANALYZE 更新统计或调整代价参数。实际工作中常用 EXPLAIN ANALYZE 定位慢节点，用 EXPLAIN 快速评估计划形态而不触发副作用。

EXPLAIN ANALYZE 会执行语句，因此对 DML（INSERT/UPDATE/DELETE）会真正修改数据，需要格外小心；只读查询可安全使用。它提供的"真实"信息能验证优化器估算是否准确，是调优的最直接手段。

• Startup Cost 是产生第一行输出前的预估开销。
• Total Cost 是产生全部输出后的预估总开销。
• Total Cost 由子节点累加并乘上父节点循环次数。

在 PostgreSQL 的 EXPLAIN 输出中，每个节点都形如 cost=startup..total。Startup Cost 指该节点开始输出第一行之前付出的代价（如 Sort 建排序堆、Hash Join 建哈希表、Limit 取数前的扫描），Total Cost 指生成全部输出行后付出的累计代价。两者之差即"输出全部行"阶段的增量代价。对 Sort 节点，Startup Cost 通常很高（要先读入全部输入并完成排序才输出第一行），而 Total Cost 在此基础上加上排序后输出行的代价。优化器比较多个候选计划时以 Total Cost 为总排序依据，Startup Cost 语义上对应"首次输出延迟"。

对只需要少量行的场景（如 LIMIT 1），Startup Cost 高但 Total Cost 更高的节点未必最优，因为 LIMIT 会及早截断，实际代价接近 Startup Cost。理解两者差异有助于读懂为何优化器在某些查询下选择索引扫描而非全表排序。

• cost 是优化器估算的执行代价（无量纲，反映 I/O 与 CPU 的相对开销）。
• rows 是估算返回的行数，与实际行数对比可发现估算误差。
• width 是估算的输出行平均字节宽度。

cost 是优化器自定义的代价单位，通常结合磁盘块读取、CPU 处理等权重，是优化器比较计划的核心指标，值越小代表整体代价越低。rows 是该节点预估返回的行数，由统计信息（表行数、直方图、相关性）推导，rows 与 EXPLAIN ANALYZE 的 actual rows 偏差越大，说明统计信息越可能失真。width 是预估输出每行平均字节数，影响排序、物化等内存占用估算，也用于估算总数据量。三者一起给出优化器对"多大工作量、多少行、多宽"的预期，是判断计划是否合理的基础。

三者的核心用途是"估算 vs 实际"对比。若 rows 与实际差距超过一个数量级，往往意味着缺少统计信息、索引失效或连接条件未更新，需要 ANALYZE。

• 各节点代表的数据访问或连接方式。
• 相互之间的代价与适用场景差异。
• 如何从计划树判断查询是否高效。

Seq Scan 顺序全表扫描，适合表小或过滤性差（要返回大部分行）的场景。Index Scan 用索引定位并回表取行，适合过滤性强的小结果集。Index Only Scan 直接从索引页取所需列，无需回表，代价最低。Bitmap Heap Scan 先用索引生成位图，再按物理顺序批量读取堆页，适合中等过滤率（返回较多行但非全表）。Hash Join 对较小表建哈希表再探测较大表，适合等值连接且无排序需求。Nested Loop 外层循环逐行驱动内层（通常走索引）查找，适合内层结果集很小、驱动行数少的场景。Sort 对输入排序，用于 ORDER BY、GROUP BY、merge join 等。Aggregate 对输入做聚合，通常配合 GroupAggregate（输入有序）或 HashAggregate（无序）。读计划时从根到叶概括，任何节点出现都说明对应的工作被真正执行。

选择哪种节点取决于行数、过滤率、索引与连接条件。乐观场景是能走 Index Only Scan / Index Scan 且用 Hash/Nested Loop 高效连接；若意外出现 Seq Scan 或 Hash Join 的表过大，通常提示索引缺失或统计信息陈旧。

• type 代表访问类型，从全表扫描到索引扫描的效率排序。
• key 表示实际使用的索引，rows 表示估算扫描行数。
• Extra 包含额外优化信息（Using index、Using filesort、Using temporary 等）。

type 是访问类型，按效率从高到低大致为 system、const、eq_ref、ref、range、index、ALL。ALL 表示全表扫描，index 表示全索引扫描，range 表示索引范围扫描，ref 表示非唯一索引等值匹配，eq_ref 表示唯一索引等值匹配（连接中每行至多一个），const 表示主键或唯一索引等值命中常量。key 是实际选用的索引名，NULL 表示未用索引。rows 是估算需要扫描的行数，越小越好。Extra 是附加信息：Using index 表示覆盖索引（无需回表）；Using filesort 表示需要额外排序（未利用索引序）；Using temporary 表示使用了临时表（常见于 GROUP BY 或去重）；Using where 表示在存储引擎层后再过滤。判断效率时优先看 type 是否从 ALL 提升到 range/ref/const，并尽量避免 Using filesort 与 Using temporary。

这是 MySQL 优化直接依据。type 由 ALL 变为 ref 往往意味着加上了合适的索引；rows 与 type 的估算配合可判断是否走错索引。Extra 中 filesort/temporary 是常见性能瓶颈信号。

• Buffers 显示节点读取/写入的缓存块数量。
• Planning Time 是计划生成耗时。
• Execution Time 是实际执行耗时。

在 EXPLAIN (ANALYZE, BUFFERS) 下，每个节点会显示 Buffers: shared hit=xx read=xx，表示在该节点读取的共享缓冲区块数：hit 为命中缓存（未触发磁盘 I/O），read 为实际从磁盘读取的块数。大量 read 表示存在磁盘 I/O 瓶颈。Planning Time 是优化器生成执行计划所花费的时间（毫秒），通常毫秒级，复杂查询或高基数统计下会略高。Execution Time 是计划实际执行的总耗时（毫秒），是衡量查询性能最直接的指标。两者相加才接近总耗时。结合 Buffers 可判断耗时是 CPU 计算还是磁盘 I/O 主导。

若 Execution Time 高而 Buffers 中 read 很多，说明缓存放不下，需扩大 shared_buffers 或优化索引；若 read 少而耗时高，则可能是 CPU 计算或排序/聚合开销大。

• work_mem 是单次排序/哈希操作可用的内存上限。
• Sort/Hash 超过 work_mem 会落盘到临时文件，代价剧增。
• Sort Method 与临时文件信息在 EXPLAIN 输出中的体现。

work_mem 控制单个 Sort、Hash、HashAggregate 等操作可用的内存上限（默认 4MB）。当排序或哈希的输入数据超过 work_mem 时，PostgreSQL 会将其写入临时文件（磁盘），表现为 EXPLAIN ANALYZE 中 Sort 节点显示 Sort Method: external merge Disk 或伴随临时文件，并出现 Memory: xxx kB；而 Sort Method: quicksort Memory 表示完全在内存完成。磁盘排序因 I/O 开销远高于内存排序，代价大增。优化手段是调大 work_mem（但要评估总内存，因多个并发操作会各自占用 work_mem）或减少排序数据量（如利用索引天然有序、减少返回列）。

判断是否落盘是看 Sort Method 是否含 Disk。若出现 disk 排序，优先考虑让查询走索引序（避免显式排序）或提升 work_mem。注意 work_mem 是每个操作实例的限额，不是全局，调大需谨慎。

• 优化器基于统计信息（行数、直方图、NULL 槽、相关性）做行数估算。
• 统计信息过旧、抽样不足、相关性假设会导致估算误差。
• 估算误差放大到连接、排序等节点会扭曲计划选择。

PostgreSQL/MySQL 的优化器通过 ANALYZE 采集的统计信息估算每个节点的行数，包括表行数（reltuples/pages）、列直方图（最常见值、桶占比）、NULL 占比等。当统计信息过期（表数据大变未重新 ANALYZE）、样本不足（小表或大表抽样失真）、或存在列间相关性（多列联合条件被当作独立相乘）时，估算行数会明显偏离实际。由于估算误差会沿计划树向上传播（连接行数放大、排序量放大），最终可能选错计划。缓解手段是定期 ANALYZE、提高 statistics 采样目标（default_statistics_target）、对常量表达式确保参数化一致性。

用 EXPLAIN ANALYZE 对比 actual rows 与 estimated rows 是发现统计失真的最直接方式。若相差数量级，先 ANALYZE 再复测，仍不准则考虑列相关性或提高采样目标。

• EXPLAIN ANALYZE 会执行语句，DML 会真正修改数据。
• 对含副作用的函数、触发器等可能产生可观测影响。
• 生产环境应谨慎使用或加上事务回滚。

EXPLAIN ANALYZE 会真正执行被分析的语句以便测量实际结果。对只读的 SELECT 而言没有副作用，但若语句是 INSERT/UPDATE/DELETE，或 SELECT 中调用带有副作用的函数（如序列、随机、写入日志），EXPLAIN ANALYZE 会真正产生这些效果。因此在生产环境，对 DML 应避免直接执行 EXPLAIN ANALYZE，或包裹在事务中随后 ROLLBACK——但注意事务内 EXPLAIN ANALYZE 的锁等仍是真实行为。而 PostgreSQL 的 EXPLAIN（不带 ANALYZE）只生成计划不执行，是安全的。另一种方式是 EXPLAIN ANALYZE 仅用于只读场景，或在测试库执行。

核心是"ANALYZE = 执行"。理解这一点能避免误在生产库用 EXPLAIN ANALYZE 执行 UPDATE 造成数据变更。对只读 SELECT 与 DML 分别说明其安全性差异。

• EXPLAIN 是优化诊断的入口，用于定位瓶颈节点。
• 结合统计信息、索引、连接方式分析计划。
• 优化后复测计划对比验证。

EXPLAIN 是 SQL 优化的核心诊断工具。典型流程是：先对慢查询执行 EXPLAIN（ANALYZE）观察计划树，定位高代价节点（如 Seq Scan 大表、Hash Join 大表、external Sort、嵌套循环内层回表）；针对瓶颈分析原因——是否缺索引、统计信息是否过期、是否进行了不必要的排序或物化、连接顺序是否合理；随后通过加索引、拆分/改写 SQL、更新统计、调整 work_mem 等手段优化；最后重新 EXPLAIN ANALYZE 对比 Execution Time 与节点代价，验证优化效果。EXPLAIN 提供的是"优化器视角"的决策依据，与慢查询日志、行数估算结合，能系统性地指导索引设计、SQL 改写与参数调优。

EXPLAIN 的价值在于把"为什么慢"变成可定位的节点级信息。优化闭环是"测计划→定位→改动→复测"，EXPLAIN 贯穿始终，是优化工作的方法论基础。

• 估算误差的来源：统计信息过期、相关性、参数化。
• 误差放大到后续节点的后果。
• 修复手段：ANALYZE、提高采样、调整统计。

当 EXPLAIN ANALYZE 中某节点的 actual rows（实际处理行数）远大于 estimated rows（估算行数），通常意味着优化器严重低估了该处行数，导致选择了错误的计划（如低估导致选 Index Scan 而非更适合的 Seq Scan，或选择错误的连接方法）。根本原因多为统计信息过期、列相关性被忽略、NULL 分布异常、或绑定参数导致常量不同。修复手段包括：执行 ANALYZE 刷新统计；提高 default_statistics_target 或单列 statistics 采样；对相关列可建表达式索引或组合统计；必要时用 SQL 改写（如拆分条件）让优化器更准确。修复后复测，确认 actual 与 estimated 趋于一致。

这是最需要人工介入的"警告"信号。它提示的不是节点本身慢，而是计划选择基于错误输入。系统性解决统计失真问题通常会整体改善计划质量。

• Hash Join 由外层探测节点与内层 Hash 节点组成。
• 较小表建哈希表，较大表逐行探测。
• 判断 Hash 节点是否过大或触发落盘。

Hash Join 的执行计划通常呈现为：外层是探测表（一般较大），内层是 Hash 节点（一般较小表被全体读入并构建哈希表）。优化器按代价选择将较小表作为哈希表构建侧。执行时先读入内层表全部行，按连接键哈希建立内存表，然后逐行读取外层表，用哈希在内存表中查找匹配。EXPLAIN 中 Hash 节点会显示内存使用（如 Buckets: 1024），若超过 work_mem 会落盘到临时文件。判断合理性：两表都很小、等值连接、无需排序时 Hash Join 是高效选择；若哈希表过大触发磁盘，应调大 work_mem 或优化连接条件。

Hash Join 一次构建一次探测，适合大表等值连接，且不要求输入有序。若计划中 Hash 侧落盘，则性能下降，需要关注 work_mem。

• JSON 格式包含各节点的详细成本、访问类型、索引信息。
• 包含 read_cost、eval_cost、prefix_cost 等精细成本。
• 包含是否使用索引、回表、临时表等结构化信息。

普通 EXPLAIN 表格输出信息有限，而 EXPLAIN FORMAT=JSON 提供结构化、更细粒度的执行计划信息，包括：每个查询块（query block）的访问类型、使用的索引、可用的索引、rows 估算、filtered 比例、缓存大小（如 BNL 的 join buffer）、是否使用 index condition pushdown、cost 明细（read_cost、eval_cost、prefix_cost、sort 等）。JSON 中还能看到 materialized 与 from clause 的详细信息，以及 optimizer hints 的采纳情况。它以层级结构呈现子查询与派生表的完整计划，便于程序化解析与自动化分析。相比表格输出，JSON 更完整、更精确，适合复杂多表查询的深度诊断。

对于复杂查询，表格 EXPLAIN 可能丢失子查询细节，JSON 则完整呈现嵌套结构。虽然可读性差，但适合工具解析与精细成本分析。

• Using index 表示覆盖索引，无需回表。
• Using filesort 表示需要额外排序，可能性能瓶颈。
• Using temporary 表示使用了临时表（通常伴随额外开销）。

Extra 字段提供查询执行的附加信息。常见取值：Using index 表示覆盖索引，查询列全部在索引中，无需回表，效率最高；Using where 表示在存储引擎取回行后 MySQL 服务器层又进行过滤（可能因索引未覆盖全部过滤条件）；Using filesort 表示 MySQL 需要额外排序（可能走临时文件或内存），未利用索引顺序，通常应尽量避免；Using temporary 表示使用了临时表，常见于 GROUP BY、DISTINCT、ORDER BY 与 OR 引起的去重，会带来额外内存/磁盘开销；Using index condition 表示启用了索引条件下推（ICP）；Using join buffer 表示使用了连接缓冲（如 BNL）。Combining 多个值（用逗号分隔）表示兼具多种情况。调优时重点关注 filesort 与 temporary。

Extra 是判断"是否走了最优路径"的关键。出现 filesort/temporary 往往提示排序或分组无法利用索引，考虑为相应列建联合索引（索引顺序匹配 ORDER BY/GROUP BY）。

• type 代表访问类型，从最优到最差排列。
• const/eq_ref 针对唯一索引等值，效率最高。
• ALL 是全表扫描，效率最低。

type 表示访问类型，从优到劣大致为：system（表仅一行，MyISAM 的系统表）、const（主键或唯一索引等值命中常量，最多一行）、eq_ref（连接中按唯一索引等值查找，每个驱动行至多匹配一行）、ref（非唯一索引等值匹配，可能多行）、range（索引范围扫描，如 BETWEEN、IN、>）、index（全索引扫描，遍历整个索引树）、ALL（全表扫描）。一般而言 type 越靠前查询越快。优化目标是让 type 尽量为 ref/eq_ref/const 或 range，避免 ALL。ALL 或 index 通常意味着可考虑加索引或调整查询条件。

这是 MySQL 判断查询质量的第一眼指标。type 从 ALL 提升到 ref/range 通常伴随明显性能提升，是索引优化最直接的验证。

• Nested Loop 由外层驱动表与内层查找表组成。
• 内层通常走索引加速每次探测。
• 适合外层行数少、内层有索引的场景。

Nested Loop 的执行计划呈现为两层：外层节点（驱动表/驱动查询）逐行输出，内层节点（被查找表）对每个外层行执行一次查找。若内层有过滤性索引，每次查找是索引定位（Index Scan），总体代价近似 O(外层行数 × 内层单次索引查找代价)。EXPLAIN 中 Nested Loop 节点下会显示 inner 与 outer 两个子节点，内层通常带索引条件。适用场景是外层结果集小、内层有匹配索引且过滤性强。若外层行数巨大或内层无索引，则退化为 O(M×N) 全扫描，代价极高。优化方式是让内层走索引、减少外层驱动行数。

Nested Loop 的代价对驱动行数敏感，EXPLAIN 中若看到内层 Seq Scan 且外层行数大，就是典型"内层无索引"问题，应加索引。

• VERBOSE 输出完整的目标列、子计划、输出表达式。
• 显示每个节点输出的列名与表达式。
• 对理解复杂查询与函数求值有帮助。

EXPLAIN (VERBOSE) 在默认输出基础上增加更多细节：显示每个节点的输出列（Output）及其表达式，展示子计划（SubPlan）与物化列，展开函数调用与表达式，标记每个节点是否被并行执行等。它把计划树中"输出什么"以可读形式呈现，便于理解诸如 SELECT 列展开、函数求值发生在哪个节点。对调试复杂查询、函数、子查询、表达式化简很有价值。VERBOSE 可与 ANALYZE、BUFFERS、COSTS 等选项组合使用。

默认 EXPLAIN 只显示节点与代价，VERBOSE 补充输出列与表达式细节，帮助理解优化器如何组织表达式求值，尤其对含函数/聚合的查询排障有用。

• work_mem 是单个 Hash 操作可用的内存上限。
• 超过后哈希表分批落盘，构建/探测需多次读写。
• 调大 work_mem 或优化连接数据量。

Hash Join 构建哈希表的内存受 work_mem 限制（默认 4MB）。当内层表太大，哈希表占满 work_mem 后，PostgreSQL 会采用"分批哈希"（batch hash join）：将哈希表按桶分批写入临时文件，外层探测时也按相同批次读取，分多次完成匹配，每一次都可处理内存内的部分批次。这会显著增加磁盘 I/O，EXPLAIN ANALYZE 中 Hash 节点会显示 Buckets 与 Batches（batches>1 表示发生落盘）。应对策略：适当调大 work_mem（注意是多操作实例共享内存，整体内存需评估）；优化连接条件减少被连接的数据量；或为内层表建更好的索引/过滤条件。核心是避免 batches>1 造成的磁盘溢出。

判断是否落盘看 Hash 节点 Batches 是否大于 1。若发生落盘，性能明显下降，优先减少需哈希的数据量或调大 work_mem。

• 构建阶段：读入较小表建哈希表。
• 探测阶段：读大表逐行在哈希表中查找。
• 复杂度约 O(M+N)，不要求输入有序。

Hash Join 分两个阶段。构建阶段（Build）：优化器选择较小的输入表作为构建侧，将其全部行读入，按连接键计算哈希值，组织成哈希表（通常为桶数组）。探测阶段（Probe）：逐行读取较大的输入表，对每行计算连接键的哈希值，在哈希表中定位桶，比较键值确认匹配，输出满足条件的连接结果。由于哈希查找摊销为 O(1)，总复杂度近似 O(M+N)（M、N 为两表行数），且不要求输入有序，因此对大表等值连接非常高效。缺点是需要额外内存存储哈希表，且只能处理等值连接（非等值连接不适用）。

哈希表的构建与探测是 Hash Join 的核心。它把连接从 O(M×N) 降为 O(M+N)，代价是内存。适合等值连接、无排序要求的大表场景。

• 并行哈希：多个 worker 并行构建哈希表的不同部分。
• 多 worker 并行探测。
• 并行度的代价与适用场景。

PostgreSQL 支持并行 Hash Join（Parallel Hash Join）。在并行构建阶段，多个 worker 进程各自读取输入表的一部分，通过共享哈希表（SharedHash）并行构建不同桶，最终在工作进程间共享整个哈希表。随后各 worker 并行执行探测阶段，各自处理外层输入的一部分，访问共享哈希表完成匹配。Gather 节点负责汇总各 worker 的部分结果。并行哈希能显著利用多核 CPU 加速大表连接，但受 max_parallel_workers_per_gather 限制，且部署时需权衡 worker 协调开销与内存（共享哈希表内存占用）。数据量足够大、连接代价高时，并行哈希才划算；小查询并行反而因协调开销变慢。

并行哈希把单一 Hash Join 拆成多 worker 协作，适合大表连接。但并行度不是越高越好，需权衡 worker 协调与内存，通常由优化器根据代价自动决定是否并行。

• 外层循环驱动每行，内层为每个外层行执行查找。
• 内层利用索引定位，避免全表扫描。
• 性能取决于外层行数与内层查找代价。

Nested Loop Join 采用双重循环：外层查询（驱动表）逐行输出一行，内层查询（被驱动表）针对该行的连接键值执行一次查找，找到所有匹配行并输出连接结果。理想情况下，内层查找利用索引（Index Scan 或 Index Only Scan），将单次查找代价降到接近 O(log n) 或 O(1)，使总代价近似 O(外层行数 × 内层单次查找代价)。性能关键在于内层必须有匹配索引且过滤性强，外层驱动行数尽量少。若内层无索引，则每次查找都全表扫描，总代价 O(M×N)，性能灾难。因此 Nested Loop 常用于外层小、内层有索引的连接。

判断 Nested Loop 是否高效，看内层是否为 Index Scan。若内层是 Seq Scan，通常代表缺索引，应补索引以加速内层查找。

• 无索引时内层全扫描，代价 O(M×N)。
• 有索引时内层索引查找，代价 O(M×logN) 或 O(M×常数)。
• 优化器按此估算选择连接方法。

Nested Loop Join 的代价估算核心是外层行数 M 乘以内层每次查找的代价。若内层无索引，每次查找需全表扫描内层 N 行，代价为 O(M×N)。若内层有匹配索引，单次查找降为索引 B+ 树查找 O(log N) 加回表，总代价约 O(M×logN)（或 O(M×常数) 当过滤率极强）。因此优化器计算 Nested Loop 总代价时，会乘以内层节点的启动代价与单次查找代价。当 M 很小（驱动表过滤后行数少）且内层有索引时，Nested Loop 是最优选择；当 M 很大时，即便有索引，M×logN 也可能超过 Hash Join 的 O(M+N)，优化器会改选 Hash Join。这就要求评估时考虑双向行数。

代价模型解释了为什么 Nested Loop 适合"小驱动大"且内层有索引。理解 O(M×N) 与 O(M×logN) 的差异，能解释计划选择逻辑。

• Sort Merge Join 需要两个输入按连接键有序。
• 若输入已有序（索引序），则无需排序，接近线性扫描。
• 已排序输入时 Sort Merge Join 代价最低。

Sort Merge Join 的核心是先对两个输入按连接键排序，然后像归并排序一样并行扫描两个有序序列，逐步比较并输出匹配行。它最大的优势是：如果某个输入已经天然有序（例如由索引扫描产生，或上层已按连接键排序），则可以跳过该输入的排序步骤，节省 O(n log n) 的排序代价。当两个输入都已有序时，Sort Merge Join 只需一次线性扫描（O(M+N)），代价非常低，甚至可能优于 Hash Join（无需建哈希表内存）。它也能处理非等值连接（如范围连接）。因此，当查询利用索引序且连接键一致时，Sort Merge Join 是高效选择。

"已排序输入零（额外）代价"是 Sort Merge Join 的关键优势。它适合输入天然有序或需要范围连接的场景，且避免了大哈希表内存。

• BNL 用 join buffer 缓存外层多行，减少内层扫描次数。
• 内层表每次只扫描一次，与缓存中所有行匹配。
• 相比普通 Nested Loop 减少内层 I/O。

普通 Nested Loop 中，每读一个外层行就对内层表扫描一次，若外层有 M 行，内层表被扫描 M 次，代价高。Block Nested Loop（BNL）用 join buffer 一次性缓存外层多个行（块），然后对内层表只扫描一次，让缓存中的所有外层行与内层当前行依次匹配，从而把内层表扫描次数从 M 次降为 ⌈M/缓冲容量⌉ 次。这减少了内层表的磁盘 I/O，尤其当内层表大且无法走索引时效果显著。MySQL 8.0 之后默认使用 hash join 替代 BNL。BNL 的局限是 join buffer 大小有限，仍可能多次扫描，且对内存占用有要求。

BNL 的核心是用 join buffer 块读取外层行，换取内层扫描次数的减少。理解"扫描次数"从 M 降到 M/缓冲 是解答关键。

• BNL 用 join buffer 减少内层扫描，但内层无索引时仍低效。
• BKA 在 BNL 基础上把缓存行的索引键批量传给内层，用 MRR 批量回表。
• 局限：join buffer 大小、索引要求、内存占用。

BNL（Block Nested Loop）通过 join buffer 缓存多个外层行，减少内层表扫描次数，但内层仍可能是全表扫描，代价高。Batched Key Access（BKA）是 BNL 的索引化增强：它首先把外层行缓存在 join buffer，然后从中提取连接键，批量生成内层索引查找请求，再结合 Multi-Range Read（MRR）对结果按主键排序后批量回表，从而把多次随机 I/O 合并为顺序 I/O，显著提升内层走索引时的连接效率。BKA 依赖内层建有索引，且需要开启相关参数（如 optimizer_switch 中 bka/mrr）。局限：join buffer 容量有限，缓存行过多仍需多次处理；BKA 与 MRR 在内存与排序上有额外开销；对非索引连接条件无效。总体而言，MySQL 8.0 已用 hash join 取代 BNL，BKA 主要用于有索引的大表连接场景。

BNL 解决"内层无索引仍全扫"，BKA 解决"内层有索引但随机 I/O 多"。两者相互配合，但都受 join buffer 与索引条件的限制。

• 内层索引使每次查找变为索引定位。
• 覆盖索引可进一步免回表。
• 索引列顺序需匹配连接键。

Nested Loop Join 的高效性高度依赖内层表的索引。外层每输出一行，内层就要按连接键执行一次查找；若内层有匹配的索引，则查找是 B+ 树索引定位（O(log n)），配合回表或覆盖索引，单次代价极低。若内层无索引，则每次查找都全表扫描，形成 O(M×N) 灾难。覆盖索引（索引包含查询所需列）可让内层用 Index Only Scan，免去回表，进一步降低代价。同理，索引列顺序需与连接键匹配（如连接键为 user_id，索引应建在 user_id 上）。因此设计内层索引（特别是复合索引覆盖连接键与 SELECT 列）是 Nested Loop 优化的核心手段。

判断 Nested Loop 是否高效，直接看内层是否为 Index Scan/Index Only Scan。若内层出现 Seq Scan，应补建索引或考虑换连接方法。

• Materialize 物化子查询结果，避免重复计算。
• Subquery Scan 包装子查询结果。
• Append 合并多个子计划（UNION、分区表）。

Materialize 节点将子查询/子计划的结果物化到内存（必要时落盘），供父节点重复访问，避免每次重复执行；也用于 Nested Loop 内层重复扫描的缓存。Subquery Scan 是包装子查询结果的节点，把子查询的输出作为一张"表"供上层访问。Append 节点将所有子计划（如 UNION ALL 的多个分支、分区表的各个分区、或 OR 展开的多个扫描）的结果合并输出。Limit 节点在读到足够行数后提前终止，截断输出。Gather 节点是并行查询的汇总点，收集各并行 worker 进程的部分结果并交给上层。理解这些节点有助于读懂复杂计划（子查询、分区、并行、UNION）。

这些节点是计划树的"胶水"节点，理解它们的作用能把复杂查询的计划拆解为可读的步骤，尤其对子查询、分区表、并行查询的排障重要。

• Bitmap Heap Scan 适合中等过滤率（返回较多但非全部行）。
• 先建位图再按物理顺序批量读页，减少随机 I/O。
• 相比单行 Index Scan 减少回表次数。

Bitmap Heap Scan 用于返回较多行（中等过滤率）但非全表的情况。它分两步：先用索引（Bitmap Index Scan）生成满足条件的行位图，再按物理磁盘顺序读取涉及的堆页（Bitmap Heap Scan），减少随机 I/O。相比普通 Index Scan（每行单独回表，随机 I/O 多），当结果行数较多时，Bitmap 能显著减少随机 I/O；相比 Seq Scan，当结果行只占表的一部分时，Bitmap 只读需要的页，避免全表扫描。因此它适合"过滤率中等（如 10%-50%）"的场景。若结果行数极少，Index Scan 更优；若几乎全表，Seq Scan 更优。优化器按代价自动选择。

Bitmap Heap Scan 是 Index Scan 与 Seq Scan 之间的折中，适合中等过滤率。理解其"位图+按序批量读页"的机制是关键。

• Index Scan 需要回表取堆页数据。
• Index Only Scan 直接用索引覆盖所需列，免回表。
• 覆盖索引（包含查询列）是 Index Only Scan 的前提。

Index Scan 通过索引定位到记录指针后，还需要回到堆表（heap）读取实际数据行，涉及两次 I/O（索引页 + 堆页），是非覆盖查询的常见方式。Index Only Scan 则当查询所需的所有列都包含在索引中（即覆盖索引）时，直接从索引页读取数据，无需回表，只有一次 I/O，代价更低。两者的代价差异在数据量大时非常明显。要让查询走 Index Only Scan，可建立包含查询所需列的复合索引，但要注意索引膨胀与写放大。优化器根据查询列是否全被索引覆盖自动选择 Index Only Scan。

Index Only Scan 免回表是其主要优势，前提是覆盖索引。理解"回表 vs 免回表"是分析两者差异的关键。

• 过滤率决定取舍：返回比例高时 Seq Scan 更优。
• 小表、无索引、大量行返回时 Seq Scan 合理。
• 索引扫描有随机 I/O 与回表开销。

Seq Scan（全表扫描）与 Index Scan（索引扫描）的取舍主要取决于过滤率（返回行数占总行数比例）。当查询返回大部分行（如过滤率低、无 WHERE 或 WHERE 不具选择性）时，Seq Scan 顺序读取整表，I/O 连续，通常比 Index Scan 更优——因为 Index Scan 需要逐行回表，随机 I/O 多反而更慢。当返回行数少时，Index Scan 能跳过大量无关行，明显更快。此外，表很小（甚至只有一页）、无合适索引、或统计信息提示全扫代价更低时，优化器也会选 Seq Scan。优化器会综合估算两种代价选择。因此"出现 Seq Scan"不一定是问题，需结合返回行数判断。

判断"为何 Seq Scan"要看返回行数。若返回大量行，Seq Scan 合理；若返回极少行却 Seq Scan，则提示缺索引或统计过期。

• 聚合需遍历输入并维护聚合状态。
• 分组聚合（GroupAggregate/HashAggregate）的代价。
• 输入行数、分组数、排序与否影响代价。

Aggregate 节点的代价主要来自对输入行的遍历与聚合状态的维护。普通聚合（无 GROUP BY）只需一次扫描，维护一个或多个聚合状态，代价近似 O(N)。分组聚合有两种实现：GroupAggregate 要求输入按分组键有序，利用顺序遍历连续分组，代价 O(N) 但需先排序（若输入无序则加 Sort 节点，代价 O(N log N)）；HashAggregate 用哈希表按分组键分组，代价 O(N) 且不要求有序，但需内存存哈希表（超过 work_mem 落盘）。影响聚合性能的因素包括输入行数、分组数量、是否需排序、聚合函数复杂度、内存占用等。优化时可通过让输入有序（索引序）避免额外排序，或减少分组数量。

聚合代价主要是"遍历 + 分组组织"。GroupAggregate 依赖排序，HashAggregate 依赖哈希，理解两者取舍是分析聚合计划的关键。

• 物化子查询结果，避免重复执行。
• Nested Loop 内层重复扫描时缓存。
• 内存/磁盘物化的权衡。

Materialize 节点将下层子计划的结果物化（缓存到内存，必要时落盘），供上层节点重复访问。典型场景：一是 Nested Loop 中，若内层结果小且被外层每行重复扫描，Materialize 一次性物化内层结果，避免每次扫描都重新执行；二是子查询（SubPlan）被多次引用时，物化避免重复计算。物化后父节点可顺序读取缓存，降低重复执行的开销。代价是物化需要内存/磁盘空间与一次性构建时间。优化器只在"重复访问收益 > 物化代价"时选择物化。理解 Materialize 有助于读懂为何某些子查询/内层只执行一次但有缓存。

Materialize 的核心是"以空间换重复计算"。出现它说明某结果被复用，判断其是否合理看物化是否减少了重复执行。

• 内存排序：一次性加载，quicksort，代价低。
• 磁盘归并：超过 work_mem 时落盘，外部归并排序，代价高。
• Sort Method 显示 quicksort（内存）或 external merge（磁盘）。

Sort 节点的核心是排序输入数据。当输入数据量小于 work_mem 时，PostgreSQL 使用内存排序（quicksort），一次性读入数据，排序在内存完成，代价低、无磁盘 I/O，EXPLAIN ANALYZE 显示 Sort Method: quicksort Memory: xxxkB。当输入数据超过 work_mem 时，改为外部归并排序（external merge sort），将数据分块写入临时文件，再通过多路归并完成排序，产生大量磁盘 I/O，代价显著高于内存排序，EXPLAIN ANALYZE 显示 Sort Method: external merge Disk: xxxkB。Sort Method 直接对应执行时间：external merge 通常比 quicksort 慢一个数量级。优化方向是让查询走索引序（避免显式排序）、减少排序数据量、或调大 work_mem。

判断 Sort 是否落盘看 Sort Method 是否含 Disk。external merge 是磁盘 I/O 密集的代价来源，是排序性能瓶颈的关键信号。

• cost 是优化器自定义的无量纲代价单位，综合 CPU 与 I/O。
• sequence_page_cost、cpu_tuple_cost 等参数决定权重。
• total cost 用于整体比较，start-up cost 反映首次输出延迟。

EXPLAIN 的 cost 是 PostgreSQL 优化器自定义的代价单位，无绝对物理含义，只反映相对代价。它由多种代价参数加权得出：random_page_cost/sequence_page_cost（随机或顺序读页代价）、cpu_tuple_cost（处理每行 CPU 代价）、cpu_index_tuple_cost、cpu_operator_cost（每算子执行代价）等，即"cost = 读页次数×页代价 + 处理行数×CPU代价 + 算子次数×算子代价"。优化器比较候选计划时主要看 total cost，值越小计划越优。start-up cost 是该节点产出第一行前的累积代价，对"LIMIT 少量行"场景，start-up cost 相比 total cost 更能反映实际延迟。解读时，total cost 是优化器决策依据，start-up cost 适合判断"首次返回"场景。

cost 是"相对代价"而非毫秒，不能直接当耗时。理解其构成（I/O 权 + CPU 权）与 total/start-up 的分工，才能正确比较计划。