谓词改写、JIT 与执行优化

• 子查询展开为 JOIN 的等价条件（无重复、无聚合）。
• 优化器通常自动展开，但需注意去重与 NULL 语义。
• 性能差异取决于优化器能否正确转换。

IN 与 EXISTS 子查询在语义上都可以改写为 JOIN，但改写时需保证等价：IN 要求子查询结果不重复（若子查询可能重复行，结果会重复，需加 DISTINCT 或用 EXISTS 替代）；EXISTS 只做存在性判断，天然去重；改写为 JOIN 时若子查询列有重复，需用 DISTINCT JOIN 或改用 EXISTS。现代优化器（PostgreSQL、MySQL 8.0）通常能自动将可展开的 IN/EXISTS 子查询改写为 JOIN（称为子查询展开/unnesting），从而共享索引、连接优化。性能差异主要体现在：计数上 IN 需去重、EXISTS 只需半连接；若优化器无法展开（如子查询含聚合、LIMIT、相关引用复杂），则退化为逐行执行子查询，性能差。手工改写时用 EXISTS 或 JOIN 更易被优化。

关键在"等价性"与"能否展开"。优化器能自动展开时两者接近，不能展开时 EXISTS（半连接）通常优于 IN 的逐行执行。

• NOT IN 在子查询含 NULL 时的语义变为"未知"，导致返回空结果。
• NOT EXISTS 天然处理 NULL 正确。
• 建议用 NOT EXISTS 或对 NULL 做显式处理。

NOT IN 与 NOT EXISTS 的差异源自 NULL 的三值逻辑。NOT IN（subquery）等价于 NOT (x = any(子查询值))，当子查询结果集包含 NULL 时，x = NULL 的结果是"未知（UNKNOWN）"，NOT 之后仍是"未知"，导致该行不满足条件而被过滤掉，最终可能返回空结果集——这是典型的 NULL 陷阱。而 NOT EXISTS 只检查是否存在匹配行，NULL 不会影响"存在性"判断，语义正确。因此当子查询列可能含 NULL 时，应使用 NOT EXISTS 或 JOIN 反连接，或显式处理 NULL（如 NOT IN (SELECT ... WHERE col IS NOT NULL)）。这也是 SQL 优化与正确性中常被考察的点。

核心是 NULL 的三值逻辑。NOT IN 在遇到 NULL 时可能返回空集，NOT EXISTS 不受影响，这决定了改写时的正确性选择。

• 将子查询转为 JOIN 或半连接，扩展优化空间。
• 使优化器能共享索引、选择连接顺序与方法。
• 展开条件与失败场景。

子查询展开（unnesting）是优化器将标量子查询、IN/EXISTS 子查询等改写为 JOIN 或半连接（semi-join）/反连接（anti-join）的技术。展开后，子查询不再作为独立的"逐行执行"单元，而是作为一个普通关系参与连接优化，优化器可以应用索引、选择连接顺序、选择 Hash/ Nested Loop 等方法，显著提升计划质量。例如 WHERE id IN (SELECT a_id FROM b) 可展开为半连接，让优化器选择高效的连接算法。展开并非总是可行：当子查询含聚合、LIMIT、非等价谓词、或相关引用复杂时，优化器可能保留子查询逐行执行。PostgreSQL 与 MySQL 8.0 都具备子查询展开能力。

子查询展开把"逐行执行子查询"变为"关系连接"，是优化器提升计划质量的关键手段。理解其适用边界有助于解释为何某些子查询计划差异巨大。

• 标量子查询逐行执行，可能重复扫描。
• 改写为 LEFT JOIN 可整体连接，避免逐行执行。
• 注意去重与 NULL 语义。

标量子查询（如 SELECT (SELECT name FROM b WHERE b.id = a.b_id) FROM a）优化器可能逐行执行子查询，若外层行数多，子查询被执行多次，性能差。改写为 LEFT JOIN 后，SELECT a.x, b.name FROM a LEFT JOIN b ON a.b_id = b.id 用一次连接完成，可共享索引、选择连接算法，通常更快。但改写需注意两点：一是若 b 中某 a.b_id 对应多行，JOIN 会产生重复行，需保证 b 的键唯一或加 DISTINCT；二是 LEFT JOIN 的 NULL 语义与标量子查询一致（无匹配返回 NULL），这点也是等价的。整体上，将标量子查询改写为 LEFT JOIN 能利用连接优化，是常见性能优化手段。

标量子查询"逐行执行"是瓶颈，LEFT JOIN "一次连接"是优化。改写时保唯一性与 NULL 语义即可等价。

• 相关子查询引用外层列，逐行执行代价高。
• 去相关把相关子查询转为连接/半连接，一次执行。
• 去相关可能引入去重、临时计算等代价。

相关子查询（correlated subquery）引用外层查询的列，朴素执行是外层每行执行一次子查询，代价 O(外层行数 × 子查询代价)，可能很高。去相关（decorrelation）将相关子查询改写为等价的连接/半连接/反连接，使子查询只执行一次（或成为连接的一部分），显著降低代价。例如 WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id AND b.x > 10) 去相关为半连接。去相关的代价：一是可能引入去重（若子查询结果不唯一需 DISTINCT），二是子查询替代为连接后，连接本身有代价（排序、哈希、索引），三是某些情况下去相关后需要额外的中间计算（如对子查询预聚合）。优化器权衡去相关收益与引入代价后决定是否改写。

去相关是"一次执行替代逐行执行"，但伴随去重与连接代价。理解其收益与代价的权衡，是分析相关子查询优化的关键。

• 把 WHERE 条件下推到扫描/连接之前。
• 尽早过滤减少中间结果与 I/O。
• 在视图、分区、连接中的体现。

谓词下推（predicate pushdown）是优化器把 WHERE 过滤条件尽量下推到计划树的底层（如扫描节点、连接内层、视图、分区内部），使数据在最早阶段被过滤，减少后续节点处理的数据量。例如对 SELECT * FROM a JOIN b ON a.id=b.a_id WHERE a.status='active'，优化器会把 a.status='active' 下推到 a 的扫描节点，先过滤 a 再连接，减少连接输入。对分区表，谓词下推可实现分区裁剪（只扫描匹配分区）。对视图，谓词下推可穿透视图直达基表。下推带来的收益是减少 I/O、减少中间结果、降低连接与聚合负载。它是优化器最基础也最重要的优化手段之一。

谓词下推的本质是"越早过滤越好"。理解它能解释为何带 WHERE 的查询比先全量再过滤高效得多。

• 派生表是 FROM 子句中的子查询。
• 优化器可把派生表合并（merge）进外层查询，消除物化。
• 合并条件与物化选择的权衡。

派生表（derived table）是 FROM 子句中的子查询，如 SELECT * FROM (SELECT ... ) d。优化器有两种处理方式：一是合并（derived merge），把派生表的内容展开进外层查询，与外层表一起做连接、过滤、索引优化，避免物化，通常更高效；二是物化（materialization），把派生表结果先计算并缓存成临时表，再供外层查询。MySQL 8.0 与 PostgreSQL 都支持派生表合并。合并并非总是可行：当派生表含聚合、LIMIT、DISTINCT、窗口函数或相关引用时，可能无法合并而需物化。优化器权衡合并的收益（共享索引、避免物化）与物化（简单但多一次中间计算）后选择。合并通常能提升性能，因为它让外层可利用派生表内部的索引与过滤。

派生表合并 vs 物化是优化器的重要决策。合并消除物化、扩大优化空间，但受聚合/LIMIT 等限制。理解其适用边界是考察重点。

• 标量子查询返回单值，改写为 JOIN 需保证一对一。
• 注意去重与 NULL 语义。
• 性能通常提升。

标量子查询（scalar subquery）在 SELECT 或 WHERE 中返回单个值，如 (SELECT name FROM b WHERE b.id = a.b_id)。改写为 JOIN 即将子查询与外层表连接，如 SELECT a.x, b.name FROM a LEFT JOIN b ON a.b_id = b.id。改写需保证等价：一是子查询必须返回单行（若 b 中匹配多行则需加 DISTINCT 或保证唯一性，否则 JOIN 会放大行数）；二是 NULL 语义要一致（LEFT JOIN 无匹配返回 NULL，与标量子查询一致）。改写后优化器可共享索引、选择连接算法，通常提升性能。若子查询返回多行，标量语义会报错或取第一行，改写为 JOIN 时就需谨慎去重。总体而言，将标量子查询改写为 JOIN 是常见优化。

改写的关键在"单值保证"与"NULL 语义"。一对一（或去重后一对一）时 JOIN 等价且更快。

• 相关子查询通常每外层行执行一次。
• 未去相关时执行次数等于外层行数。
• 去相关后执行次数可降为一次或成为连接。

相关子查询引用外层列，其执行次数取决于优化器是否去相关。若未去相关，子查询被放入计划中的 SubPlan 节点，外层每输出一行就执行一次子查询，因此执行次数等于外层处理的行数（loops 反映这一点）。当外层行数很大时，子查询被反复执行，代价极高。若优化器去相关，将子查询改写为连接/半连接，则子查询（或连接）整体执行一次，执行次数大幅下降。EXPLAIN ANALYZE 中 SubPlan 的 loops 字段可观察实际执行次数。优化思路是尽力让优化器去相关，或手工改写为连接。

执行次数是相关子查询性能的根源。loops 大意味着逐行重复执行，去相关是治理手段。

• HashAggregate 用哈希表按分组键存储聚合状态。
• 哈希表大小受 work_mem 限制。
• 超出后落盘到临时文件，性能下降。

HashAggregate 使用哈希表按分组键组织聚合状态，哈希表内存占用受 work_mem 限制。当分组数量多、聚合状态大，哈希表超过 work_mem 时，PostgreSQL 会将哈希表分批落盘到临时文件（与 batch hash join 类似），通过分批次处理分组，代价是大量磁盘 I/O，性能显著下降。EXPLAIN ANALYZE 中 HashAggregate 节点可能显示 Peak Memory Usage 或落盘信息。优化手段：调大 work_mem（评估总内存）；减少分组数量（更精确的过滤）；或改用 GroupAggregate（若输入有序，可顺序聚合，内存占用小）。判断是否落盘对评估聚合性能至关重要。

HashAggregate 的内存与 work_mem 直接相关，落盘是性能瓶颈。优化方向是调大 work_mem 或减少分组/改用有序聚合。

• work_mem 默认 4MB，控制单次排序/哈希内存。
• 复杂查询超出后落盘，性能下降。
• 上调需权衡全局内存与并发。

work_mem 是 PostgreSQL 中控制单个 Sort、Hash、HashAggregate 等操作可用内存的参数，默认 4MB。对于简单小查询，4MB 足够，排序在内存完成；但复杂查询（大表排序、大分组聚合、大表哈希连接）所需内存远超 4MB 时，会落盘到临时文件，性能剧降。此时需要上调 work_mem（如 64MB、256MB）。但 work_mem 不是全局上限，而是每个操作实例的限额，若大量并发连接同时执行大排序，总内存占用会成倍放大，可能耗尽内存。因此上调 work_mem 需结合服务器内存与并发度评估，或使用 SET LOCAL 对特定大查询单独调大。优化原则是按需上调、评估并发。

work_mem 默认 4MB 偏小，复杂查询易落盘。上调它提升单查询性能，但需防范并发内存放大。

• Sort 节点的 cost、Sort Key、Sort Method。
• Sort Method 决定内存/磁盘排序。
• 判断是否可利用索引避免排序。

EXPLAIN 中 Sort 节点关键信息包括：cost（估算代价，startup 高因为要先读入全部输入排序）、Sort Key（排序键，反映 ORDER BY 列）、Sort Method（quicksort 表示内存排序，external merge 表示磁盘归并排序，对应 Memory 与 Disk 大小）。解读时先看 Sort Method——若是 external merge Disk，说明排序超过 work_mem 落盘，性能瓶颈，应优化（调大 work_mem 或让查询走索引序）；再看是否有必要排序——若 ORDER BY 列已有索引，优化器通常会用 Index Scan 直接产生有序输出，避免 Sort 节点。出现不必要的 Sort 常提示索引不到位或 ORDER BY 与索引顺序不匹配。

Sort 节点解读的核心是 Sort Method 与是否有必要排序。Sort Key 与索引顺序匹配时可避免 Sort，external merge 则是落盘信号。

• MySQL 8.0 引入了 InnoDB 并行 read（并行扫描）。
• 并行主要用于大表只读扫描。
• SELECT ... FOR UPDATE 涉及锁，不适合并行。

MySQL 8.0 引入了 InnoDB 并行 read，可将大表的扫描任务分片给多个 worker 线程并行执行，提升大表全表扫描、聚合等只读操作的吞吐。其适用场景是纯只读的大表扫描。但 SELECT ... FOR UPDATE 会对扫描的行加锁，锁的获取与释放与读取顺序、事务隔离强相关，无法安全地并行分片处理（或并行会引入锁语义混乱），因此涉及锁的查询（FOR UPDATE、FOR SHARE）不适合并行 read。此外，写操作、或依赖严格顺序的查询也一般不并行。MySQL 的并行能力相比 PostgreSQL 的并行查询（并行聚合、并行连接、并行排序）较有限，主要用于只读扫描类场景。

MySQL 并行 read 面向只读大表扫描。加锁的 SELECT ... FOR UPDATE 因锁语义与顺序相关，不适合并行，这是其应用边界。

• JIT 用 LLVM 把热路径编译为机器码。
• 受益于表达式求值、聚合、tuple deforming。
• 只在足够复杂的查询上开启，避免编译开销。

PostgreSQL JIT 通过 LLVM 将查询执行中的热路径（如表达式求值、聚合函数、tuple deforming，即把磁盘上的元组转换为 C 结构）编译为高效机器码，替代解释执行，从而降低每次执行的解释开销。JIT 在以下场景尤其受益：表达式求值（大量 WHERE 条件、SELECT 表达式）、聚合函数（多次调用）、tuple deforming（大量行转换）。JIT 由 jit、jit_above_cost 等参数控制，仅在优化器估算代价超过阈值时才开启，因为编译本身有开销（LLVM 编译耗时），对简单查询反而得不偿失。在复杂 OLAP 查询、大表扫描中 JIT 收益明显。这是 PostgreSQL 在 CPU 密集查询上的优势。

JIT 是编译换解释，适合 CPU 密集、重复执行的表达式/聚合/行转换。阈值控制避免对小查询的编译开销。

• max_parallel_workers 是全局并行 worker 上限。
• max_parallel_workers_per_gather 是单个 Gather 节点的 worker 上限。
• 两者共同约束并行度。

max_parallel_workers 是 PostgreSQL 全局并行 worker 进程数量的上限，所有并发查询共享这些 worker；max_parallel_workers_per_gather 是单个查询（单个 Gather 节点）最多能使用的 worker 数量。单个查询的并行度受 per_gather 限制，而全局所有查询的并行 worker 总和受 max_parallel_workers 限制。还有 max_parallel_maintenance_workers 控制维护操作（如 CREATE INDEX、VACUUM）的并行度。调优时，若要提升单查询并行度，调大 max_parallel_workers_per_gather；但要保证全局并发不高，需调大 max_parallel_workers 与 max_worker_processes 配合。此外，优化器还受表大小、并行成本阈值（min_parallel_table_scan_size）影响是否实际并行。合理设置这些参数能充分利用多核，但过大会加剧 worker 竞争与内存压力。

两个参数是单查询上限与全局上限的双层约束。理解它们的分工对配置并行度与防止资源争抢至关重要。

• CTE 被多次引用时物化可避免重复计算。
• 物化本身有一次构建代价。
• 权衡重复计算与物化代价。

当 CTE（WITH 子句）在查询中被多次引用时，若每次引用都重新执行 CTE 的查询，会重复计算多次，代价高。物化（materialize）将 CTE 结果首次计算后缓存为临时表，后续引用直接读取缓存，避免重复计算。但物化需要一次性构建代价（计算并存储 CTE 结果），且若 CTE 结果很大，还需占用内存/磁盘。因此优化器权衡重复计算的代价与物化一次的代价。若 CTE 被引用多次且计算代价高，物化划算；若只引用一次，物化可能是多余开销（此时应内联）。PostgreSQL 12+ 默认对只引用一次的 CTE 做内联，对多次引用的 CTE 才物化。

物化是以一次构建换多次复用。它是否划算取决于引用次数与 CTE 计算/结果大小。多次引用且代价高时物化收益明显。

• PostgreSQL 12+ 默认对只引用一次的 CTE 内联。
• 多次引用或含副作用时物化。
• 可通过 MATERIALIZED/NOT MATERIALIZED 强制。

在 PostgreSQL 12 之前，CTE 总是被物化（作为优化屏障），这可能阻止优化器将 CTE 与外层合并优化。PostgreSQL 12+ 改进了行为：默认对只被引用一次的 CTE 做内联（inline），将其内容展开进外层查询，与外层共享优化空间（索引、连接、谓词下推），避免不必要的物化；对被多次引用或含副作用（volatile 函数等）的 CTE 才物化。用户也可用 MATERIALIZED / NOT MATERIALIZED 关键字显式强制物化或内联。内联通常更优，但若 CTE 计算昂贵且被多次引用，物化反而能避免重复计算。因此选择取决于引用次数与代价。

PostgreSQL 12+ 默认内联单次引用的 CTE，这是显著改进。理解内联 vs 物化的启发式与 EXPLAIN 中 CTE 的体现是核心。

• 子查询通常会被展开/合并进外层查询。
• CTE 在 PostgreSQL 11 前是优化屏障，12+ 默认内联。
• 差异取决于内联还是物化。

在执行计划层面，CTE 与等价的子查询（如 FROM 子句中的派生表）差异取决于优化器如何处理。普通子查询（derived table）通常被优化器展开合并进外层查询，与外层共享索引、连接优化。CTE 在 PostgreSQL 11 及之前总是被物化，成为优化屏障（optimization fence），与外层查询隔离，无法下推谓词或共享连接优化；PostgreSQL 12+ 默认对单次引用的 CTE 内联，行为与子查询接近。因此现代 PostgreSQL 中，单次引用的 CTE 与子查询计划差异不大；但 CTE 被多次引用时物化可避免重复计算，而子查询被多次引用通常会重复执行。理解差异有助于判断用 CTE 还是子查询。

CTE 与子查询的差异本质是物化 vs 内联/展开。PostgreSQL 12+ 后两者趋同，但多次引用场景 CTE 物化有优势。

• CTE 物化时出现 CTE Scan 节点。
• CTE 内联时直接展开，无 CTE Scan。
• 通过节点形态判断优化器策略。

在 EXPLAIN 中，若 CTE 被物化，查询计划中会出现 CTE Scan 节点，它读取物化后的 CTE 临时表；CTE 的实际计算在计划中单独显示（如 CTE cte 子计划）。若 CTE 被内联，则不会出现 CTE Scan，而是把 CTE 的内容直接展开进外层查询，表现为正常的扫描/连接节点。因此通过是否出现 CTE Scan 节点可判断 CTE 是物化还是内联。物化时 CTE 结果被一次计算并缓存，多处引用共享；内联时 CTE 逻辑融入外层优化。解读时还需注意 CTE 子计划的代价与 loops，评估其是否成为瓶颈。理解 CTE 节点形态有助于判断优化器是否做了正确的内联/物化决策。

CTE Scan 存在即物化，不存在即内联。这是解读 CTE 计划的直接判据。

• 12+ 默认对单次引用 CTE 内联。
• 优化屏障被消除，计划质量提升。
• 可用 MATERIALIZED/NOT MATERIALIZED 控制。

PostgreSQL 12 之前，CTE 总是被物化，作为优化屏障，优化器无法将 CTE 与外层查询合并优化（如下推谓词、共享索引），可能导致计划低效。PostgreSQL 12+ 改变了默认行为：对只被引用一次的 CTE 默认内联（inline），将其展开进外层查询，外层可对 CTE 内容应用谓词下推、索引、连接优化，从而产生更优计划；对被多次引用的 CTE 或含副作用（volatile 函数等）的 CTE 才物化。用户可通过 WITH cte AS MATERIALIZED 或 NOT MATERIALIZED 显式控制。这一改进让 CTE 不再是优化屏障，显著提升了含 CTE 查询的计划质量。

PostgreSQL 12+ 的核心变化是默认内联单次引用 CTE。这消除了 CTE 作为优化屏障的缺陷，是 CTE 优化的重要改进。

• 直方图由 ANALYZE 采集，更新频率取决于 ANALYZE 时机。
• 数据分布变化后直方图会失真。
• 定期 ANALYZE 或自动 analyze 保持准确。

直方图（histogram）是统计信息中描述列值分布的重要部分，由 ANALYZE 命令采样采集。其更新频率取决于 ANALYZE 的执行时机：ANALYZE 需在数据分布显著变化后重新执行，否则直方图反映的是旧分布。PostgreSQL 有 autovacuum 自动 analyze，MySQL 有自动统计更新，但都有触发阈值。若数据分布变化快而 ANALYZE 不及时，直方图失真，优化器基于旧直方图估算行数会严重偏差，导致选错计划。因此应根据数据变更频率安排定期 ANALYZE，或调低自动分析阈值。对分布变化敏感的高基数列，可提高 default_statistics_target（PostgreSQL）或 statistics 采样目标，使直方图更精细。直方图准确度直接影响行数估算与计划质量。

直方图是行数估算的重要依据，其准确度取决于更新频率。数据变化后需及时 ANALYZE，否则估算失真。

• LRU-K 记录页的访问历史，避免单次访问污染缓存。
• K 次访问内才进入缓存，保护热点页。
• 相比普通 LRU 更适合数据库工作负载。

LRU-K 是改进的 LRU 缓存淘汰算法，用于数据库 Buffer Pool（如 InnoDB）。普通 LRU 只记录最近一次访问，可能导致顺序扫描等一次性访问把热点页挤出缓存（缓存污染）。LRU-K 记录每个页最近 K 次访问的历史时间戳，只有当某页在 K 次访问内被再次访问时，才将其提升到缓存中的热区，从而避免被单次访问污染，保护真正频繁访问的热点页。InnoDB 的 LRU 算法通过 young 区与 old 区（二次分代）实现类似思想，并在扫描时把新页放入 old 区头部，避免顺序扫描污染 young 区。LRU-K 让缓存更有效地服务于热点数据，提升命中率。

LRU-K 的核心是按访问频次保护热点，避免单次访问污染。InnoDB 的 old/young 分代是 LRU-K 思想的工程实现。

• Result Cache 按查询参数哈希缓存结果。
• 重复相同参数查询时直接命中缓存。
• 适用于参数少、重复高的查询。

Result Cache 是 PostgreSQL 14+ 与 Oracle 等提供的查询结果缓存优化。它把某个查询（如 Nested Loop 内层的相关子查询）按输入的参数键（外层列值）哈希后缓存结果：当后续执行遇到相同参数键时，直接返回缓存结果，避免重复执行内层查询。Hash Join 探测、相关子查询、参数化查询等重复执行的场景尤其受益。EXPLAIN 中 Result Cache 节点显示缓存命中与否（Hits/Misses）。实现上用内存哈希表存储参数键到结果的映射，受内存限制，缓存满了会淘汰。它适用于参数组合重复率高、结果小的场景；若参数组合几乎不重复，缓存命中率低，反而增加哈希开销，优化器会权衡是否使用。

Result Cache 是按参数键缓存结果，把重复的参数值查询变成内存命中。命中率低时反而有害，优化器谨慎启用。

• 物化视图预聚合结果，报表查询直接读取。
• 增量刷新（基于增量数据）与全量刷新（重算全部）的代价差异。
• 与列存、实时数仓（如 ClickHouse 物化视图）互补。

物化视图把常用复杂聚合（如按日/按区域的汇总）预先计算并存储，报表查询直接读取预聚合结果，避免每次实时聚合大量明细，显著提升 OLAP 报表响应速度。刷新策略有两种：全量刷新（REFRESH MATERIALIZED VIEW 重算全部数据），代价高、会阻塞查询；增量刷新（若支持，如基于增量日志或触发器），只更新变化部分，代价低但实现复杂。PostgreSQL 物化视图仅支持全量刷新（CONCURRENTLY 可避免阻塞但需主键），Oracle/MySQL 支持增量刷新。物化视图与列存（OLAP 列式存储）互补：列存本身加速明细扫描，物化视图进一步消除重复聚合；与实时数仓（如 ClickHouse 物化视图通过触发器自动聚合写入）互补，实时数仓把物化视图从定时刷新变为实时增量，兼顾实时性与预聚合性能。选择时权衡数据新鲜度、刷新成本与查询加速收益。

物化视图的核心是预聚合换查询速度。增量刷新 vs 全量刷新是代价权衡；与列存/实时数仓互补在于减少重复计算加实时性。

• 全量刷新与增量刷新。
• 全量刷新是否阻塞查询（CONCURRENTLY）。
• 刷新时机与数据新鲜度权衡。

物化视图刷新策略主要分全量刷新与增量刷新。全量刷新（如 PostgreSQL REFRESH MATERIALIZED VIEW）重算并替换全部数据，代价高、耗时长，且默认会持有锁阻塞查询；REFRESH MATERIALIZED VIEW CONCURRENTLY 可与查询并发（需唯一索引），但代价更高。增量刷新（支持增量聚合的数据库，如 Oracle、MySQL 的 ON DEMAND/ON COMMIT）只应用基表变化，代价低、时效性好，但实现复杂（需维护增量日志）。刷新时机上，ON DEMAND（手动/定时）与 ON COMMIT（基表提交即刷新）权衡数据新鲜度与开销。优化器会直接选择物化视图作为查询来源（若匹配查询），从而利用预聚合。选择策略需权衡刷新成本、数据新鲜度与查询加速收益。

刷新策略核心是全量 vs 增量与阻塞 vs 并发。CONCURRENTLY 提供并发刷新但需唯一索引，增量刷新实时但代价高。

• OR 条件可能无法利用多个索引。
• UNION 各分支可分别走索引。
• 改写成本与索引合并的权衡。

当查询含 WHERE a = 1 OR b = 2 且 a、b 各有索引时，OR 条件可能无法同时利用两个索引（优化器可能退化为全表扫描，或使用 index merge）。改写为 UNION（或 UNION ALL）后，每个分支分别匹配一个索引，可能利用上两个索引，提升性能。但改写有代价：UNION 会去重（UNION ALL 不去重但需保证结果不重复），且需分别执行两个分支再合并，增加了执行与合并开销。若优化器本身支持 index merge（MySQL 的 index_merge 优化），OR 条件已能利用多个索引，则无需改写。改写是否更优取决于索引覆盖、结果集重合度、去重代价。一般结果集小、索引滤波强时改写受益；否则可能不划算。优化器或人工权衡后决定。

OR 改写为 UNION 的目的是让各分支走索引，但引入去重与合并代价。是否存在 index merge 影响改写必要性。

• GroupAggregate 要求输入有序，顺序聚合。
• HashAggregate 用哈希表，不要求有序。
• 权衡排序代价与内存代价。

GroupAggregate 与 HashAggregate 是实现 GROUP BY 聚合的两种方式。GroupAggregate 要求输入按分组键有序，利用顺序遍历，每个分组连续出现时输出聚合结果，内存占用小（只维护当前分组状态），但若输入无序需先排序（O(N log N)）。HashAggregate 用哈希表按分组键分组，不要求输入有序，代价 O(N)，但需内存存储哈希表（超过 work_mem 落盘）。优化器取舍：若输入已有序（如索引序）或分组数少，GroupAggregate 更优（无需哈希内存）；若输入无序且分组多，HashAggregate 一次哈希即可，但需评估内存。当数据量巨大导致哈希落盘时，可能反而用 GroupAggregate 加排序更划算。EXPLAIN 中可看到是 GroupAggregate 还是 HashAggregate。

取舍核心是有序输入的 GroupAggregate vs 哈希内存的 HashAggregate。输入有序时 Group 优，无序且哈希可容纳时 Hash 优。

• 内存排序无磁盘 I/O，代价低。
• 外部归并排序需磁盘读写，代价高。
• work_mem 阈值决定使用哪种。

Sort 节点的代价取决于数据能否在内存完成。内存排序（quicksort 等）一次性读入全部待排序数据，在内存中完成排序，无磁盘 I/O，代价主要由 CPU 比较与内存拷贝构成，接近 O(N log N) 的 CPU 代价。外部归并排序（on-disk）：当数据超过 work_mem 时，排序分两阶段——先创建多个有序临时块（run）写入磁盘，再用多路归并合并这些 run，期间产生大量磁盘写与读 I/O，代价远高于内存排序（磁盘 I/O 比内存慢几个数量级）。因此同一排序任务，外部归并可能慢一个数量级甚至更多。优化目标是避免外部归并：调大 work_mem、让查询走索引序（ORDER BY 匹配索引）、或减少排序数据量。

内存排序 vs 外部归并的差异是 CPU vs 磁盘 I/O。work_mem 决定分界，避免落盘是排序优化的关键。

• 外部排序分 run 生成与归并两阶段。
• 数据量翻倍 I/O 近似翻倍。
• 归并轮数影响 I/O 倍率。

外部排序（external sort）在数据超过 work_mem 时进行。其 I/O 代价估算：第一阶段生成 run，把数据分块排序后写入磁盘，写入量约为数据总量；第二阶段多路归并，把多个 run 合并成有序结果，读回所有 run 并写出结果，读写量约为数据总量的 2 倍（读一次加写一次）。若归并一次不能完成（run 数太多），需多轮归并，则每一轮都增加一次全量读写，I/O 倍率随归并轮数增长。粗略估算为：总 I/O 约等于数据量乘以（生成的 run 写入加归并轮数乘 2）。work_mem 越大，run 越少、归并轮数越少，I/O 越少。因此外部排序的 I/O 代价近似与数据量成正比，且随归并轮数倍增，是磁盘密集操作。

外部排序 I/O 近似数据量乘（写入加归并轮数乘 2）。提升 work_mem 减少 run 数，归并轮数下降，I/O 减少。

• HashAggregate 哈希表受 work_mem 限制。
• 超出后落盘或改用其他方式。
• 内存限制影响分组聚合性能。

HashAggregate 通过哈希表按分组键组织聚合状态，哈希表的内存占用受 work_mem 限制。当分组数很大或聚合状态较大，哈希表超出 work_mem 时，PostgreSQL 会采用分批哈希（batch）方式，将哈希表分批落盘到临时文件，通过多轮处理完成聚合，代价是大量磁盘 I/O，性能下降。EXPLAIN ANALYZE 中 HashAggregate 可能显示内存峰值或落盘。内存限制的影响：分组数多、聚合状态大时易超限落盘。优化手段：调大 work_mem；减少分组数（更精确过滤）；或改走 GroupAggregate（输入有序时内存占用小）。理解 HashAggregate 的内存限制对评估大分组聚合性能至关重要。

HashAggregate 内存受 work_mem 约束，超限落盘。优化方向是调大 work_mem 或改用有序 GroupAggregate。

• JIT 用 LLVM 将执行代码编译为机器码。
• 编译表达式、聚合、tuple deforming。
• 编译 vs 解释的权衡。

PostgreSQL 的 JIT（Just-In-Time）编译通过 LLVM 框架，在查询执行时把热路径代码（表达式求值、聚合函数、tuple deforming 等）编译为原生机器码，替代原有的解释执行。解释执行每条表达式/行都要经过解释器派发，开销较大；JIT 编译后，代码直接在 CPU 上运行，减少解释开销，尤其适合大量重复执行表达式、大量行转换的复杂查询。编译发生在查询开始阶段（有编译开销），所以只在查询足够复杂（优化器代价超过 jit_above_cost）时才启用。LLVM 提供优化与代码生成，生成高效机器码。JIT 的权衡是编译成本 vs 执行节省，对 OLAP 大查询收益显著，对简单小查询可能得不偿失。

JIT 是运行时编译换解释，LLVM 是编译后端。编译开销存在，故需阈值控制，适合复杂重复计算查询。

• leader 进程负责协调与汇总。
• 多个 worker 并行处理数据分片。
• Gather 节点汇总结果。

并行查询（parallel query）由 PostgreSQL 等实现。原理是：一个 leader 进程（后端会话）负责整体协调，将查询的部分工作（如扫描、聚合、连接）分派给多个 worker 进程并行执行。Gather 节点是并行与串行的分界点：Gather 下方的节点（如并行 Seq Scan、并行聚合）由多个 worker 并行执行，Gather 上方由 leader 串行处理并汇总各 worker 的部分结果。worker 通过共享内存协作，leader 负责收集 worker 输出、处理无法并行的部分（如最后的聚合、排序）。并行度由 max_parallel_workers_per_gather 等控制。并行查询能利用多核加速大表扫描、聚合、连接等，但受 worker 协调、内存、数据分片开销影响，小查询并行反而变慢。

并行查询是 leader 协调加 Gather 收集加 worker 并行。Gather 是分界点，理解并行度与协调开销是关键。

• worker 启动与调度开销。
• 数据分片与结果合并的通信开销。
• 并行带来的内存放大。

并行查询虽能加速，也引入额外代价。worker 协调代价：启动多个 worker 进程有调度开销，worker 之间通过共享内存通信、Gather 汇总结果有同步开销，数据分片（把表分成若干范围）也有计算开销。内存代价：多个 worker 各自执行排序/哈希/聚合，每个 worker 的操作各自占用 work_mem，总内存随并行度放大，可能加剧内存压力。此外，worker 协调还可能引入负载不均（某 worker 处理的数据多于其他）。因此优化器仅在查询足够大、并行收益超过协调与内存代价时才启用并行；过高的并行度在数据量小时反而因协调开销变慢。合理设置 max_parallel_workers_per_gather 与 work_mem 平衡并行收益与代价。

并行代价主要是 worker 调度/通信/分片与内存放大。并行收益与代价的权衡决定了优化器是否启用并行。

• ANALYZE 采样表数据生成统计信息。
• 样本大小影响估算精度。
• 采样频率影响统计新鲜度。

ANALYZE 通过采样表数据构建统计信息（行数、直方图、相关性）。采样大小由 default_statistics_target（PostgreSQL，默认 100，采样行数约为 300×target，即约 3 万行）或 MySQL 的统计采样页数控制；单个列可用 SET STATISTICS 提高样本。样本越大，估算越准，但 ANALYZE 代价越高（读更多行）。采样频率决定了统计新鲜度：autovacuum 自动 analyze 和数据变化后手动 ANALYZE 控制频率；频率过低会导致统计过期、估算失真。因此需要在采样精度与 ANALYZE 代价、频率与新鲜度之间权衡。对高基数、分布波动大的列，提高采样目标；对数据频繁变化的表，提高自动 analyze 频率。

采样大小与频率共同决定统计准确度。样本大则准但代价高，频率高则新鲜但开销大，需权衡。

• EXISTS 通常转为半连接（semi join）。
• IN 可能转为半连接或去重连接。
• 现代优化器两者可能生成相似计划。

EXISTS 与 IN 的执行计划在现代优化器（PostgreSQL、MySQL 8.0）中通常都通过子查询展开转为半连接（semi-join）。半连接只输出外层表匹配的行，不重复，且可共享子查询的索引。IN 在语义上允许子查询重复（需去重），通常也会展开为半连接（若优化器识别去重需求）或加 DISTINCT。两者差异：EXISTS 更明确表达存在性，优化器可靠地转半连接；IN 在子查询含 NULL 或重复时需额外处理。在优化器能正确展开时，EXISTS 与 IN 的执行计划往往相似（都走半连接）。若优化器无法展开，则退化为逐行执行子查询，此时 EXISTS 仍可能优于 IN（半连接 vs 逐行去重）。手工倾向用 EXISTS 表达存在性。

现代优化器把 EXISTS/IN 都转为半连接，计划相似。差异在表意与 NULL/去重处理，展开失败时 EXISTS 更稳。

• Query Cache 缓存完整查询结果。
• 表更新导致缓存失效，写入场景性能反而下降。
• 5.7.20 起废弃，8.0 移除。

MySQL Query Cache 曾是缓存查询结果到内存的机制：完全相同的查询（文本完全一致）命中缓存时直接返回结果，避免重复执行。其缺陷在于：只要涉及的表有任何更新（INSERT/UPDATE/DELETE），该表的所有缓存行全部失效，且失效检查有锁竞争。在写多读少的互联网场景，更新频繁导致缓存几乎无效，反而引入缓存维护与锁开销，降低写入性能。因此 MySQL 5.7.20 将其标记为废弃，8.0 彻底移除。教训是：缓存命中率依赖同样查询重复执行且表不常更新，在写多场景适得其反；现代替代方案是应用层缓存、Redis、或针对只读场景的独立缓存，且数据库层缓存应避免一写全失效的粗粒度失效模型。

Query Cache 的失败在于有更新即整体失效的粗粒度模型与锁竞争，在写多场景弊大于利。它是缓存设计反例，教训是命中率与失效粒度要匹配工作负载。