临时表与 CTE 物化与 DDL 元数据与目录 - 面试题

#

★★★

1. CTE 的多次引用语义，默认情况下 CTE 被优化器视为一次性计算还是可以多次执行？

问题

CTE 的多次引用语义是什么？默认情况下 CTE 被优化器视为一次性计算还是可以多次执行？

考察点

CTE 的定义与引用次数
默认内联 vs 物化的行为
MATERIALIZED 的控制

答案

语义：CTE（WITH cte AS (...)）在语句内"可多次引用"（主查询中引用两次：JOIN cte c1 JOIN cte c2），但"默认是否只计算一次"取决于优化器：PostgreSQL——默认"优化器自由决定"：轻量 CTE 被内联（inline，像宏一样展开到每个引用处——"多次引用可能多次执行"（每次引用处重新计算））、代价高或含不可合并特征的 CTE 被物化（materialize：计算一次存临时结果——"只计算一次"）；PG 12 起默认"内联优先"（11 及以前默认物化），可用 MATERIALIZED（强制物化：计算一次、多次引用共享结果）与 NOT MATERIALIZED（强制内联：每次引用处展开）。MySQL 8.0——CTE 支持（8.0.1+），优化器倾向于"物化一次"（derived table 默认物化；MySQL 的 CTE 默认作为物化临时表？MySQL 8.0 中 CTE 默认物化（非内联），多次引用只计算一次；无 MATERIALIZED 关键字控制）；SQL Server——CTE 总是"内联展开"（无物化选项：每次引用处展开执行（多次引用可能多次执行），无 MATERIALIZED 概念（SQL Server 用表变量/临时表显式物化）；Oracle——CTE 可物化（MATERIALIZE hint）或内联。关键结论：其一，"多次引用是否只算一次"不是语言保证而是"优化器实现选择"（PG 可控、SQL Server 恒内联、MySQL 默认物化）；其二，语义等价——无论内联还是物化，结果一致（物化是执行优化不是语义变化）；其三，影响——内联让谓词下推/索引可用（性能可能更好），但"多次引用昂贵计算"时重复执行浪费（此时 PG 用 MATERIALIZED 强制物化）；物化保证"只算一次"但中间结果无法下推（性能可能更差）；其四，volatile 函数——物化时"求值一次"、内联时"每引用处求值"（结果可能不同（含 random()/now() 的 CTE）——语义差异注意。工程建议：默认信任优化器（PG 12+ 内联优先）；"昂贵且多次引用"的 CTE 显式 MATERIALIZED（PG）；"希望谓词下推/索引"的轻量 CTE 用 NOT MATERIALIZED 或依赖默认；用 EXPLAIN 确认 CTE 是否物化（Materialize 节点）））。

解析

答题先讲 CTE 可多次引用与"默认一次或多次"的实现差异（PG 默认内联（12+）可 MATERIALIZED 控制、MySQL 默认物化、SQL Server 恒内联），再讲语义等价与影响（谓词下推 vs 重复计算、volatile 差异），最后给工程建议与 EXPLAIN 验证。

示例代码

-- 多次引用
WITH base AS (SELECT * FROM orders WHERE status = 'PAID')
SELECT * FROM base b1 JOIN base b2 ON b1.user_id = b2.user_id;
-- PG：强制物化（只算一次） / 强制内联
WITH base AS MATERIALIZED (SELECT * FROM orders WHERE status = 'PAID') ...
WITH base AS NOT MATERIALIZED (SELECT * FROM orders WHERE status = 'PAID') ...

#

★★★

2. PostgreSQL 的 ON COMMIT 子句（DELETE ROWS、PRESERVE ROWS、DROP）的语义差异？

问题

PostgreSQL 临时表的 ON COMMIT 子句（DELETE ROWS、PRESERVE ROWS、DROP）的语义差异是什么？

考察点

三个选项的行为
会话级与事务级临时表
与 MySQL 的对比

答案

ON COMMIT 子句（CREATE TEMP TABLE ... ON COMMIT 选项，仅临时表）：DELETE ROWS——事务提交时"清空临时表数据"（表结构保留、会话内后续事务可继续用）：适合"每事务一批数据"（同一会话多事务各自独立使用表）；PRESERVE ROWS（默认）——事务提交时"保留数据"（表内容跨事务持续到会话结束）：适合"会话级暂存"（多次事务累积）；DROP——事务提交时"删除临时表本身"（结构+数据都消失，后续事务需重建）：适合"单事务内的一次性中间表"（用完即焚，避免残留）。三者的差异本质是"临时表的生命周期粒度"：DELETE ROWS = 数据按事务清、结构按会话留；PRESERVE ROWS = 数据与结构都按会话留（默认）；DROP = 结构按事务留（提交即删）。行为细节：其一，ON COMMIT 在"事务提交"时生效（回滚不触发）；其二，未开启事务（autocommit 单语句）时每条语句即事务——DELETE ROWS 的表现在"每条语句后清空"（需显式事务才实用）；其三，PRESERVE 是默认（不写 ON COMMIT 即保留）；其四，与会话生命周期——无论哪个选项，会话断开临时表都删除（pg_temp 清理）；其五，索引/约束——DELETE ROWS 清数据但索引结构保留（TRUNCATE 式清空）、DROP 连索引一起删；其六，PG 中 ON COMMIT 也支持"事务级临时表"场景（CREATE TEMP TABLE ... ON COMMIT DROP 在事务内创建使用、提交后自动清理——事务性中间表的标准做法）。MySQL 对比：MySQL 临时表（CREATE TEMPORARY TABLE）无 ON COMMIT 选项（会话级唯一生命周期：断开删除；事务回滚只影响表内 DML 不影响表本身）；SQL Server 的 #local 临时表无 ON COMMIT（会话级；事务中创建的表在事务回滚时"表本身回滚删除"（SQL Server 的 DDL 事务性：临时表在事务内创建、回滚则表消失（除非指定 WITH 选项？默认回滚删除）））。工程建议：事务内一次性中间表用 ON COMMIT DROP（自动清理防残留）；每事务一批用 DELETE ROWS；跨事务累积用 PRESERVE ROWS（默认）。

解析

答题先分别讲三个选项（DELETE ROWS 提交清数据留结构、PRESERVE ROWS 默认保留、DROP 提交删表）与生命周期粒度（数据按事务/会话、结构按会话/事务），再讲行为细节（提交才生效、autocommit 影响、索引处理）与各库对比（MySQL 无 ON COMMIT、SQL Server 事务回滚删表），最后给工程建议。

示例代码

CREATE TEMP TABLE batch_tmp (id INT) ON COMMIT DELETE ROWS;  -- 每事务清空
CREATE TEMP TABLE sess_tmp (id INT) ON COMMIT PRESERVE ROWS;  -- 默认，会话保留
CREATE TEMP TABLE once_tmp (id INT) ON COMMIT DROP;           -- 提交即删表

#

★★★

3. WITH RECURSIVE 在 PostgreSQL、SQL Server、MySQL 8.0 中的实现差异？

问题

WITH RECURSIVE 在 PostgreSQL、SQL Server、MySQL 8.0 中的实现差异是什么？

考察点

三库递归 CTE 的语法支持
递归深度限制与终止
性能与功能差异

答案

支持现状：三者都支持递归 CTE（PostgreSQL 8.4+、SQL Server 2005+、MySQL 8.0.1+），标准语法 WITH RECURSIVE cte AS (锚点 UNION [ALL] 递归部分) SELECT ...。差异：其一，RECURSIVE 关键字——PG 与 MySQL 要求显式 RECURSIVE；SQL Server 不需要（其语法恒为 WITH cte AS (...)），靠 CTE 内自引用识别递归，无 RECURSIVE 关键字；其二，递归深度限制——SQL Server 默认 MAXRECURSION 100（超限报错，可 OPTION (MAXRECURSION 0) 无限或设大值）；MySQL 8.0 默认 cte_max_recursion_depth=1000（超限报错，可调）；PostgreSQL 无显式"层数"参数（递归 CTE 无行数/深度硬上限，靠终止条件与内存/栈约束，PG 14 起可用 SEARCH/CYCLE 子句控制遍历与防环）；其三，递归部分引用——三库都要求递归引用"只出现一次"（标准限制：递归项中 cte 只能引用一次（MySQL/PG 报错于多引用））；其四，功能——PG 支持 SEARCH/CYCLE 子句（PG 14+：SEARCH DEPTH/BREADTH FIRST 排序、CYCLE 环检测）、递归 CTE 中可用窗口函数（PG 12+）；SQL Server 无 SEARCH/CYCLE（需手工深度列与访问集合）、MySQL 8.0 无 SEARCH/CYCLE（手工实现）；其五，性能——PG 的递归执行器较成熟（工作队列+索引 JOIN）、MySQL 8.0 的递归 CTE 实现基础（深层递归性能弱、不能利用物化优化？MySQL 8.0 对递归 CTE 的优化有限）、SQL Server 用工作台+假脱机；其六，终止语义——UNION 去重（PG/MySQL 支持、SQL Server 的 UNION 去重同样）、UNION ALL 需深度控制防环（三库一致）；其七，限制差异——PG 递归 CTE 中不能有聚合/窗口（递归项中标准限制，PG 允许部分？递归项中不允许聚合与窗口（标准）；SQL Server 递归项中不能有聚合/窗口/排序（有严格限制：递归部分不能有 GROUP BY、DISTINCT、HAVING、ORDER BY、TOP 等）；MySQL 同标准限制；其八，资源——MySQL 8.0 对"递归 CTE + 外部引用"的物化策略与 PG 不同（MySQL 递归 CTE 不能物化索引）。工程建议：跨库递归查询按"锚点+UNION ALL+深度计数"的可移植写法（显式深度列与终止条件）；生产 SQL Server 记得设置 MAXRECURSION 上限（防失控）；深层递归（万级）评估性能（必要时换物化路径/闭包表））。

解析

答题先确认三库都支持与标准语法，再按"关键字、深度限制（SQL Server MAXRECURSION 100/MySQL 1000/PG 14+ 无层数）、功能（PG 的 SEARCH/CYCLE 独有）、性能（PG 成熟/MySQL 基础）、终止语义"五维对比，最后给可移植写法与深度控制建议。

示例代码

-- 通用可移植递归 CTE（带深度计数）
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 50
) SELECT * FROM tree;
-- SQL Server 深度上限
SELECT * FROM tree OPTION (MAXRECURSION 200);

#

★★★

4. 递归 CTE 的语法与终止条件，UNION ALL 终止、UNION 去重。

问题

递归 CTE 的语法与终止条件是什么？UNION ALL 与 UNION 在递归中的差异与防环作用是什么？

考察点

递归 CTE 的语法结构（锚点+递归项）
终止机制（不动点）
UNION vs UNION ALL 的防环差异

答案

语法：WITH RECURSIVE cte AS (锚点查询 UNION [ALL] 递归查询) SELECT ... FROM cte;——锚点（anchor）生成初始行集；递归查询引用 cte 自身（把"上一轮新增的行"扩展出新行）；执行：迭代式——结果集 = 锚点 ∪ 递归轮次（每轮把上轮新增行 JOIN 源表产生新行），直到"某轮不再产生新行"（不动点）终止；终止条件本质上由"数据不再扩展"决定（递归查询无新行可产生即停），因此"递归 CTE 不一定需要显式终止条件"（数据有限时自然终止），但环/深数据需显式防护。UNION ALL vs UNION 的差异：UNION ALL——不去重：递归过程中"已出现过的行"会再次出现（环：A→B→A 的路径会无限重复（每轮都产生 A 的下一跳），永不收敛 → 无限循环（或撞深度/内存限制报错））；必须用"深度计数 + WHERE depth < N"显式终止，或记录路径判断环；UNION——去重：已出现过的行不再次加入结果（集合不动点语义）：环数据下"重复行被去重"→ 递归自然收敛（A→B→A 第二轮产生的 A 已在结果中，去重后无新增 → 终止）——UNION 天然防环（代价是丢失路径的重复计数，且结果不区分"第一次到达"顺序（BFS 语义））；但 UNION 去重不保证"不产生新行"？（环中"同一行"被去重 → 无新增 → 终止——是的，UNION 下环自动终止（只要行可比较去重））。选择：需要"完整路径/重复展开"（如深度统计、每路径一行）用 UNION ALL + 深度限制/访问集（路径数组记录防环）；需要"可达集合"（去重、防环、自动终止）用 UNION（传递闭包的标准形式——但注意 UNION 递归同样需要"有界数据"（无限数据（无环但有无限链）不可能有）；实践：树遍历通常用 UNION ALL + 深度计数（保留遍历顺序与重复？树的"行"唯一（无环）→ UNION ALL 也不会重复 → 深度计数主要用于"限制异常深度"）；图可达性用 UNION（防环自动终止）；始终加"深度上限"作为安全网（防数据错误导致的失控）。各库注意：SQL Server 递归 CTE 的 UNION 去重语义与 PG/MySQL 一致；SQL Server 的 MAXRECURSION 是"层数硬上限"（无论 UNION/UNION ALL 都适用））。

解析

答题先讲语法结构（锚点 + 递归项 + UNION/UNION ALL）与终止机制（迭代到不动点：无新行即停、数据有限自然终止），再重点对比 UNION（去重→环自动收敛）与 UNION ALL（不去重→环无限、需深度限制/访问集防环）的防环差异与适用场景，最后给实践建议（深度上限安全网、图/树选择）。

示例代码

-- 树遍历：UNION ALL + 深度上限（防异常数据）
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 100
) SELECT * FROM tree;
-- 图可达性：UNION 去重（环自动收敛）
WITH RECURSIVE reach AS (
  SELECT from_id AS node FROM edges WHERE from_id = 1
  UNION
  SELECT e.to_id FROM edges e JOIN reach r ON e.from_id = r.node
) SELECT * FROM reach;

#

★★★

5. 临时表是否创建索引？PostgreSQL 与 MySQL 的差异？

问题

临时表是否创建索引？PostgreSQL 与 MySQL 的差异是什么？

考察点

临时表建索引的语法支持
索引的会话可见性与清理
性能与场景

答案

可以：临时表与普通表一样可建索引（CREATE INDEX ON temp_table (col)、建表时 PRIMARY KEY/UNIQUE/索引列定义），且索引自动随临时表删除（会话结束/ON COMMIT DROP 时）；PostgreSQL——CREATE TEMP TABLE t (id INT PRIMARY KEY, name TEXT); CREATE INDEX ON t (name);（临时表的索引建在 pg_temp schema、会话内可见、VACUUM/ANALYZE 可作用于临时表（PG 会自动 analyze 临时表？PG 对临时表收集统计（第一次访问时 analyze？PG 会为临时表做统计（在会话内自动 analyze（autovacuum 不处理临时表，但查询前会做 analyze？PG 的临时表统计由"手工 ANALYZE 或首次使用时的自动 analyze"（PG 中临时表在首次使用时会自动 ANALYZE（避免无统计的次优计划）））；MySQL——CREATE TEMPORARY TABLE t (id INT PRIMARY KEY, name VARCHAR(50), INDEX idx_name (name));（临时表可建索引（内存临时表（MEMORY/TEMPTABLE 引擎）支持 HASH/BTREE 索引、磁盘临时表（InnoDB 临时表（8.0 默认临时表用 InnoDB）支持索引）；8.0 中临时表默认 InnoDB（内存临时表用 TempTable 引擎），索引行为与普通表一致。差异：其一，语法与能力——两库都支持（MySQL 临时表建表时定义索引或 ALTER/CREATE INDEX（MySQL 8.0 可 CREATE INDEX on temp table））；其二，统计与优化——PG 会对临时表做统计（会话内 ANALYZE），MySQL 临时表统计有限（优化器按估算，临时表上的 JOIN 计划可能次优）；其三，生命周期——索引随表（会话/ON COMMIT）；其四，共享——两库临时表都会话私有（索引同样私有）；其五，用途——临时表 + 索引用于"复杂查询的中间结果加速"（多步计算：先插临时表、建索引、再 JOIN 查询）——比 CTE 物化可控（CTE 物化不能建索引、临时表可）；MySQL 的派生表（子查询）默认物化且有隐式索引（8.0 物化派生表自动建索引？MySQL 8.0 的物化派生表可为"唯一列"建隐式索引（合并到外部条件时），临时表则显式控制。工程建议：临时表数据量大且多次查询时显式建索引（避免中间结果全扫）；用完即删（ON COMMIT DROP/显式 DROP 防残留）；MySQL 注意临时表统计与内存限制（max_heap_table_size 等对内存临时表））））））））。

解析

答题先确认"可以建索引"（两库语法与生命周期随表），再分别讲 PG（pg_temp 中、自动 ANALYZE 统计）与 MySQL（8.0 InnoDB 临时表、统计有限、内存/磁盘临时表差异）的差异，最后给使用场景（中间结果加速）与工程建议。

示例代码

-- PostgreSQL
CREATE TEMP TABLE tmp_stats (user_id INT PRIMARY KEY, total NUMERIC);
CREATE INDEX ON tmp_stats (total);
-- MySQL
CREATE TEMPORARY TABLE tmp_stats (
  user_id INT PRIMARY KEY,
  total DECIMAL(12,2),
  INDEX idx_total (total)
);

#

★★★

6. CTE 与子查询在优化器中是否等同？

问题

CTE 与子查询在优化器中是否等同？两者的执行与优化差异是什么？

考察点

语法等价与优化差异
内联/物化与子查询提升
多次引用与可读性

答案

语义等同：CTE 与"内联子查询"语义等价（WITH cte AS (Q) SELECT ... FROM cte 与 SELECT ... FROM (Q) AS cte 结果一致）——优化器层面：两者都可能被"展开/提升（subquery unnesting/flattening）"到外层统一优化（PG 把 CTE 与派生表都视为可内联对象、MySQL 8.0 的 derived_merge 同样处理、SQL Server 恒内联）——简单情况下执行计划相同（EXPLAIN 一致）。差异（非语义、在优化与工程）：其一，物化控制——PG 的 CTE 支持 MATERIALIZED/NOT MATERIALIZED 显式控制（子查询无此语法；派生表只能靠优化器决策（PG 13+ 可对子查询用 MATERIALIZED？PG 中只有 CTE 有物化控制；派生表默认内联（除非物化边界））；其二，多次引用——CTE 可在语句内引用多次（且可强制"只算一次"（MATERIALIZED）），子查询"同一查询写两遍"才是多次（写一遍只能出现一次——CTE 提供"命名复用"（可读性+物化共享））；其三，作用域与可读性——CTE 前置命名（先定义后使用、可分层 WITH a, b）、子查询内嵌（定义在使用处）；复杂查询 CTE 可读性显著更好；其四，递归——递归只能 CTE（WITH RECURSIVE，子查询无递归）；其五，优化器差异细节——PG 12 前 CTE 默认物化（曾与子查询计划差异大）、12+ 默认内联（趋向等同）；MySQL 8.0 中 CTE 默认物化而派生表默认（derived_merge 开）内联——同一逻辑两种写法可能计划不同；SQL Server 两者都内联（等同）；其六，volatile 与物化——物化 CTE 求值一次（volatile 函数只算一次）、内联（子查询/CTE 内联）每引用处求值（结果可能不同）。工程建议：语义优先（可读性用 CTE）；性能敏感时用 EXPLAIN 对比（同逻辑 CTE 与子查询计划差异：PG 12+ 通常相同、MySQL 可能不同（物化 vs 合并））；"多次引用且昂贵"用 MATERIALIZED（PG）或临时表；不要假设"CTE 一定比子查询快/慢"——以计划为准）。

解析

答题先确认语义等同（内联展开后结果一致）与优化器的统一处理（子查询提升/内联：PG/MySQL derived_merge/SQL Server），再列差异（物化控制、多次引用复用、可读性、递归、MySQL 默认物化 vs 派生表内联的细节），最后给工程建议（EXPLAIN 对比、按语义选择）。

示例代码

-- 语义等同的两种写法
WITH cte AS (SELECT id, amount FROM orders WHERE status='PAID')
SELECT * FROM cte WHERE amount > 100;
SELECT * FROM (SELECT id, amount FROM orders WHERE status='PAID') AS cte
WHERE amount > 100;
-- PG 12+ 两者计划通常相同；MySQL 8.0：CTE 默认物化、派生表默认合并

#

★★★

7. MySQL 8.0 中 CTE 的实现差异？

问题

MySQL 8.0 中 CTE 的实现差异是什么？与标准/其他数据库的差异有哪些？

考察点

MySQL 8.0 的 CTE 支持范围
物化与优化行为
与其他库的差异

答案

支持范围：MySQL 8.0.1+ 支持 CTE（非递归 WITH ... AS 与递归 WITH RECURSIVE），语法与标准一致：WITH cte AS (SELECT ...) SELECT ...;、WITH RECURSIVE 递归（8.0 中递归 CTE 可用）；限制与差异：其一，递归深度——cte_max_recursion_depth 默认 1000（超限报错，可调）；其二，物化行为——MySQL 8.0 中 CTE"默认物化"（计算一次存内部临时表（TempTable 引擎/磁盘临时表），多次引用共享——与 PG 12+ 默认内联不同；无 MATERIALIZED/NOT MATERIALIZED 关键字（无法显式控制内联）；物化的好处（多次引用只算一次）、代价（谓词无法下推到 CTE 内部、无索引（物化临时表无用户索引——MySQL 物化 CTE 无索引，JOIN 时可能全扫临时表））；其三，递归 CTE 的限制——递归部分不能含聚合/窗口/DISTINCT/GROUP BY（标准限制）、递归查询中"外部引用"处理有限、SEARCH/CYCLE 子句不支持（PG 14+ 有）——需手工深度列与路径防环；其四，与派生表对比——8.0 派生表默认"合并（derived_merge）"（可内联），CTE 默认物化——同一逻辑 CTE 与子查询计划可能不同；可影响 derived_merge 的优化器开关（optimizer_switch）调整；其五，作用域——CTE 只在本语句内可见、可多次引用、可嵌套（WITH a AS (...), b AS (SELECT ... FROM a)）；其六，性能——物化 CTE 数据量大时用磁盘临时表（tmp_table_size/max_heap_table_size 影响内存临时表阈值）、无索引导致连接慢（大 CTE JOIN 时考虑改写为临时表+显式索引）；其七，版本——5.7 无 CTE（需子查询/派生表/临时表替代）；8.0.1 引入（8.0.1 早期版本有部分 bug、8.0.19+ 稳定）。工程建议：MySQL 8.0 中"多次引用/昂贵计算"的 CTE 天然物化（直接用）；"轻量且需下推索引"的场景注意物化代价（EXPLAIN 看 Materialize/临时表节点，必要时改写为派生表/临时表+索引）；递归查询评估深度与性能（深层用物化路径替代））。

解析

答题先讲支持范围（8.0.1+ 非递归+递归、语法标准）与深度限制（默认 1000），再重点讲默认物化行为（与 PG 内联不同的影响：多次引用共享但谓词不下推、物化无索引）与无 MATERIALIZED 关键字，然后列递归限制（无 SEARCH/CYCLE、递归项限制）与派生表 merge 的对比，最后给工程建议（EXPLAIN、临时表替代）。

示例代码

-- MySQL 8.0 CTE
WITH base AS (SELECT id, amount FROM orders WHERE status='PAID')
SELECT * FROM base WHERE amount > 100;
-- 递归（深度默认 1000）
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 100
) SELECT * FROM tree;
SET SESSION cte_max_recursion_depth = 5000;

#

★★★

8. PostgreSQL 中 SEARCH 子句（深度优先、广度优先）？

问题

PostgreSQL 14+ 的 SEARCH 子句（深度优先、广度优先）是什么？如何使用？

考察点

SEARCH DEPTH/BREADTH FIRST 的语义
与手工深度列对比
CYCLE 子句

答案

SEARCH 子句（PG 14+）：在递归 CTE 中声明"结果排序方式"：SEARCH DEPTH FIRST BY 列 SET 排序列——深度优先（DFS：先深入子节点再回溯：输出顺序为"根→第一子树整棵→第二子树..."）；SEARCH BREADTH FIRST BY 列 SET 排序列——广度优先（BFS：按层输出：根→所有深度 1→所有深度 2...）。实现：PG 自动为递归结果附加"排序路径"（深度优先用"路径数组/序数"，广度优先用"层级+顺序"），并填充 SET 指定的"排序列"（order_col）——查询只需 ORDER BY 排序列即得到对应遍历顺序；等价于手工实现（深度优先 = 累积路径字符串/数组排序、广度优先 = depth 列 + 兄弟序），SEARCH 子句让声明式表达（少写路径拼接逻辑）。语法：WITH RECURSIVE cte AS (...) SEARCH DEPTH FIRST BY id SET ord SELECT * FROM cte ORDER BY ord;。CYCLE 子句（PG 14+，可单独或与 SEARCH 同用）：CYCLE 列 SET 环标记列 USING 路径列——声明"哪些列构成环检测键"：PG 自动维护"已访问路径"（path 列）并在遇到重复键时"截断该分支"（不无限递归）并标记（is_cycle 列 true）——替代手工"路径数组防环"（UNION ALL 防环的手动方案）；语法：WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path SELECT ...。与手工对比：SEARCH/CYCLE 的价值——声明式（少写路径数组/深度计数/访问集合）、避免常见错误（路径拼接顺序、环检测遗漏）；语义一致（SEARCH DEPTH 等价于手工"路径数组排序"、CYCLE 等价于"访问集合剪枝"）；限制——SEARCH/CYCLE 要求"递归 CTE"（非递归不可用）、SEARCH 的 BY 列须是递归输出列、SET 列名不与其他列冲突；PG 14 前的替代——深度优先：路径数组（ARRAY 累积）ORDER BY、广度优先：depth 列 ORDER BY depth, 兄弟序；环：路径数组 contain 判断（UNION ALL 内 WHERE NOT path @> ARRAY[id]）。工程建议：PG 14+ 优先用 SEARCH/CYCLE（可读性+正确性）；树遍历按业务需要选 DEPTH（层级展示/序列化）或 BREADTH（逐层处理）；图遍历必须 CYCLE（防环）或 UNION 去重。

解析

答题先定义 SEARCH 子句（DEPTH/BREADTH FIRST BY ... SET ...）与 PG 14+ 的引入，讲实现原理（自动附加排序路径/层级并填充排序列）与两种遍历的输出特征，再讲 CYCLE 子句（环检测键、路径列、剪枝标记）与手工方案的对比（路径数组/depth 列），最后给限制（仅递归 CTE）与工程建议。

示例代码

-- 深度优先
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name FROM category c JOIN tree t ON c.parent_id = t.id
) SEARCH DEPTH FIRST BY id SET ord
SELECT * FROM tree ORDER BY ord;
-- 广度优先
... SEARCH BREADTH FIRST BY id SET ord ...
-- 环检测
WITH RECURSIVE g AS (
  SELECT from_id, to_id FROM edges WHERE from_id = 1
  UNION ALL
  SELECT e.from_id, e.to_id FROM edges e JOIN g ON e.from_id = g.to_id
) CYCLE to_id SET is_cycle USING path
SELECT * FROM g;

#

★★★

9. PostgreSQL 中如何避免临时表预编译（plan caching）问题？DISCARD ALL？

问题

PostgreSQL 中如何避免临时表预编译（plan caching）问题？DISCARD ALL 的作用是什么？

考察点

计划缓存与临时表结构变化
计划失效与重新规划
DISCARD ALL 的语义

答案

问题背景：PostgreSQL 的"通用计划缓存"（generic plan caching）——PL/pgSQL 函数/预处理语句首次按实参生成计划后缓存复用（参数嗅探）；若会话中"临时表结构变化"（建了临时表、改了临时表列），缓存的计划可能"引用不存在的对象"或"与当前结构不符"（如函数首次执行时无临时表（计划假设无）、后续会话建了同名临时表（解析到临时表））→ 缓存计划失效/错误（"cached plan must not change result type" 或 plan 引用了旧对象）；常见场景：同一会话/连接池中"预编译语句 + 临时表重建"的组合。解决方式：其一，DISCARD ALL——清空会话级状态：包括"已缓存的计划（prepared statements）、PL/pgSQL 函数缓存、临时表（DROP）、会话级 GUC 重置、监听、参数等"——即"把会话恢复到初始状态"（相当于轻量重连）；在"临时表使用完毕/连接归还池前"执行可清除计划缓存与残留临时表；其二，DISCARD PLANS——只清除"已缓存的执行计划"（保留临时表与设置）：针对"计划缓存失效"的精准清理；其三，DEALLOCATE ALL——清除预处理语句（PREPARE 的语句）；其四，规避——函数/预处理语句中"不依赖会话临时表"（用永久表/CTE）；临时表操作后手动 PREPARE 重新生成；连接池归还时清理（DISCARD ALL 是连接池的常见配置（pgbouncer 的 server_reset_query 默认 DISCARD ALL））。机制细节：PG 的计划缓存——plpgsql 函数内的 SQL 语句"第一次执行生成 custom plan、之后按 generic plan 复用（若 generic 可安全用于所有参数）"；对象依赖——缓存计划依赖"引用的对象（含临时表）"：对象结构变化（DROP/ALTER）触发计划失效（下次重新规划）；但"临时表的存在性"变化（首次无、后来有）可能不触发失效（解析时快照差异）——因此显式清理（DISCARD PLANS）最可靠；PREPARE 语句的缓存同理。工程实践：连接池（pgbouncer）配置 server_reset_query = DISCARD ALL（归还即清理）；应用在"动态建临时表"的会话中，用完临时表后 DISCARD PLANS（或 ALL）；避免在"预处理语句热点路径"上依赖临时表（改 CTE/永久表）；监控"cached plan must not change result type"错误（排查计划缓存与结构变更冲突）。

解析

答题先讲问题（计划缓存 + 临时表结构/存在性变化导致缓存失效/错误：plpgsql 与 PREPARE 的 generic plan 机制），再给三种清理（DISCARD ALL 会话级全面重置、DISCARD PLANS 精准清计划、DEALLOCATE ALL）与规避（连接池 server_reset_query、避免依赖临时表），最后讲机制细节（对象依赖触发失效的边界）与监控。

示例代码

DISCARD ALL;        -- 清空会话状态（计划缓存+临时表+GUC+监听等）
DISCARD PLANS;      -- 只清执行计划缓存
DEALLOCATE ALL;     -- 清预处理语句
-- pgbouncer 配置
server_reset_query = DISCARD ALL

#

★★★

10. WITH ... AS ... SELECT 的嵌套深度是否有限制？

问题

WITH ... AS ... SELECT 的嵌套深度是否有限制？影响是什么？

考察点

CTE 嵌套/链式定义的深度
实际限制（解析器、内存、性能）
各库差异

答案

语法层面：CTE 支持"链式/嵌套"（WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b——同一 WITH 中多个 CTE 相互引用（按声明顺序，只能引用"前面定义的"）；以及"CTE 内再嵌套 CTE"（WITH a AS (WITH x AS (...) SELECT ... FROM x) SELECT ...）——理论上"无硬性深度上限"（递归式嵌套 CTE 定义），PG/MySQL/SQL Server 都没有"显式最大嵌套层数"的语法限制（与"递归 CTE 深度"（层数限制不同：这里指"CTE 定义的嵌套/链式深度"而非"递归迭代层数"）。实际限制：其一，解析器与内存——深度过深（上百层）时解析/展开的复杂度（语法树深度、重写（rewrite）阶段对象引用解析）消耗内存与时间（PG 的查询重写对多层嵌套有"递归深度限制"（PG 的 recursion limit（如 max_stack_depth？PG 解析器递归深度受"栈"限制：深层嵌套表达式/子查询可能触发 stack depth limit（max_stack_depth 默认 2MB 栈，深层嵌套报错 "stack depth limit exceeded"））；其二，优化器与执行——深嵌套导致"重写展开后的查询树"巨大（CTE 内联时），优化时间与内存上升；其三，SQL Server——CTE 嵌套（CTE 内再写 WITH）在 SQL Server 中"不允许"（CTE 不能嵌套定义：WITH 内不能再含 WITH——SQL Server 的 CTE 必须平铺（用多个 CTE 逗号分隔，不能嵌套 WITH）；MySQL 8.0 与 PG 允许嵌套（PG 支持、MySQL 8.0 支持嵌套 CTE？MySQL 8.0 的 CTE 可嵌套（WITH 内可再 WITH）——实际 MySQL 支持）；其四，实际工程——"逻辑深度"由可读性与性能决定而非语法上限：深嵌套应重构为"平铺多 CTE/视图/临时表"；递归 CTE 的"迭代层数"才有硬限制（SQL Server MAXRECURSION、MySQL 1000、PG 无层数（内存/终止条件））。结论：CTE 定义的嵌套/链式无硬性语法上限（除 SQL Server 不允许 WITH 内嵌 WITH），但受"解析栈/内存/可读性"约束；递归 CTE 的迭代深度各库有限制；工程上"超过几层的嵌套"应平铺或拆对象）））））。

解析

答题先区分两个"深度"概念（CTE 定义嵌套深度 vs 递归迭代层数），再讲链式/嵌套 CTE 的语法支持（PG/MySQL 可嵌套、SQL Server 平铺限制）与"无硬性上限"的结论，然后讲实际限制（解析栈（stack depth limit）、重写内存、SQL Server 差异），最后给工程建议（平铺重构、递归层数硬限制回顾）。

示例代码

-- 链式（平铺）：允许
WITH a AS (...), b AS (SELECT ... FROM a)
SELECT * FROM b;
-- 嵌套：PG/MySQL 允许、SQL Server 不允许
WITH a AS (WITH x AS (SELECT 1 AS v) SELECT * FROM x)
SELECT * FROM a;
-- 递归层数才有硬限制（SQL Server MAXRECURSION、MySQL cte_max_recursion_depth）

#

★★★

11. WITH CHECK OPTION 与 CTE 联用可行吗？

问题

WITH CHECK OPTION 与 CTE 联用可行吗？两者的关系与限制是什么？

考察点

WITH CHECK OPTION 的对象（视图）
CTE 的可更新性
联用场景与限制

答案

直接联用"不可行"：WITH CHECK OPTION 是"可更新视图"的写入校验子句（CREATE VIEW ... WITH CHECK OPTION——保证 INSERT/UPDATE 后的行满足视图定义条件），作用于"持久化视图对象"；CTE（WITH ... AS）是"语句级临时命名查询"，不是可更新对象——CTE 上不能声明 WITH CHECK OPTION（语法上无此用法：CTE 是 SELECT 表达式的命名，不是可写目标）；"可更新 CTE"概念也不存在（CTE 只读引用）。关系澄清：其一，若想"基于 CTE 逻辑的写入校验"，正确做法是"把 CTE 查询固化为视图并加 WITH CHECK OPTION"（CREATE VIEW v AS <CTE 查询体> WITH CHECK OPTION）——"CTE 与视图"是同一查询逻辑的两种形态，视图才有 CHECK OPTION 语义；其二，"视图定义中能否用 CTE + CHECK OPTION"——可以：CREATE VIEW v AS WITH cte AS (...) SELECT ... FROM cte WITH CHECK OPTION（视图体内用 CTE 组织、视图整体带 CHECK OPTION）——这是"联用"的合法形式（CHECK OPTION 属于视图、CTE 只是视图体内的查询组织手段）；其三，限制——CHECK OPTION 的校验对象是"视图可见性条件"（视图 WHERE），CTE 在视图体内只是中间结构（不影响 CHECK OPTION 语义）；若视图不可更新（含聚合/JOIN/去重），WITH CHECK OPTION 不生效（需 INSTEAD OF 触发器实现）；其四，MySQL 中视图可用 CTE？MySQL 8.0 的视图定义中"不能使用 CTE"（MySQL 视图体不支持 WITH 子句——MySQL 的 CREATE VIEW 不支持 CTE（8.0 限制：视图定义不允许 WITH）？实际 MySQL 8.0 的视图定义不支持 WITH（报错）——需要子查询替代；PG 的视图定义支持 CTE；SQL Server 视图定义可用 CTE？SQL Server 的视图不支持 WITH（CTE）在 CREATE VIEW 中（视图定义中不能有 ORDER BY/CTE？SQL Server 的 CREATE VIEW 不支持 CTE 与 ORDER BY（除非 TOP）——需要子查询/内联视图替代）；工程结论：WITH CHECK OPTION 只用于"可更新视图"；想给"CTE 逻辑"加写入校验 → 固化视图 + CHECK OPTION（PG 支持视图内 CTE）；MySQL/SQL Server 的视图不支持 CTE（改写为子查询 + CHECK OPTION）；不可更新视图的写入校验用 INSTEAD OF 触发器）。

解析

答题先直接回答"CTE 上不可声明 CHECK OPTION"（CTE 是语句级只读命名查询、CHECK OPTION 属视图对象），再讲合法联用（视图体内用 CTE + 视图带 CHECK OPTION（PG 支持；MySQL/SQL Server 视图不支持 CTE 需子查询）），最后列限制（视图可更新性前提、不可更新用 INSTEAD OF）与工程结论。

示例代码

-- 合法联用（PostgreSQL）：视图体内 CTE + 视图 CHECK OPTION
CREATE VIEW v_active AS
WITH filtered AS (SELECT id, name, status FROM users WHERE status = 'ACTIVE')
SELECT * FROM filtered
WITH CHECK OPTION;
INSERT INTO v_active (id, name, status) VALUES (1, 'x', 'INACTIVE');  -- 报错
-- MySQL/SQL Server：视图定义不支持 CTE，改写为子查询
CREATE VIEW v_active AS
SELECT * FROM (SELECT id, name, status FROM users WHERE status = 'ACTIVE') t
WITH CHECK OPTION;

#

★★★

12. INFORMATION_SCHEMA 的标准化视图（TABLE、COLUMNS、CONSTRAINTS、TABLES、REFERENTIAL_CONSTRAINTS）在三大数据库中的支持度差异？

问题

INFORMATION_SCHEMA 的标准化视图（TABLES、COLUMNS、TABLE_CONSTRAINTS、REFERENTIAL_CONSTRAINTS 等）在 PostgreSQL、MySQL、SQL Server 中的支持度差异是什么？

考察点

标准视图在各库的支持
字段覆盖差异
原生目录 vs 标准视图

答案

标准视图支持：三者都实现 INFORMATION_SCHEMA 核心视图：TABLES（表/视图清单：TABLE_NAME、TABLE_TYPE、TABLE_SCHEMA）、COLUMNS（列清单：COLUMN_NAME、DATA_TYPE、IS_NULLABLE、CHARACTER_MAXIMUM_LENGTH 等）、TABLE_CONSTRAINTS（约束：CONSTRAINT_NAME/TYPE（PRIMARY KEY/UNIQUE/FOREIGN KEY/CHECK））、KEY_COLUMN_USAGE（约束-列关联）、REFERENTIAL_CONSTRAINTS（外键：UPDATE_RULE/DELETE_RULE、REFERENCED_TABLE_NAME）——SQL 标准视图，跨库"名称与主要字段一致"。差异：其一，覆盖度与字段——PostgreSQL 实现最全（几乎全部标准视图与字段，CHECK_CONSTRAINTS（PG 9.2+）、DOMAINS、VIEWS、ROUTINES 等），但部分字段为 NULL（如 COLUMNS.COLUMN_DEFAULT 在 PG 中按表达式返回）；MySQL 实现较全（8.0 增加 CHECK_CONSTRAINTS（8.0.16+）、ST_* 空间视图），但部分标准字段缺失/命名差异（如无 COLUMNS.COLLATION_NAME？有）；SQL Server 实现"标准子集"（信息_SCHEMA 只覆盖部分对象（表/列/约束/视图/例程），且 SQL Server 官方推荐用"sys 目录视图"（sys.tables 等）——信息_SCHEMA 视图在 SQL Server 中"只返回用户有权查看的对象"且部分字段空洞（如无 TABLES.TABLE_TYPE 之外扩展）；其二，性能——信息_SCHEMA 视图在 PG/MySQL/SQL Server 都是"视图（底层查询系统目录）"，大库查询慢（多次子查询）；原生目录（pg_catalog/pg_class、MySQL 的 SHOW/information_schema 表、SQL Server sys.）更快更全；其三，对象范围——信息_SCHEMA 只覆盖"标准定义的对象"：物化视图不在 information_schema.tables 中（PG 需查 pg_class 的 relkind='m' 或 pg_matviews 视图）、索引/序列也不在信息_SCHEMA（无标准视图）；MySQL 的引擎信息（ENGINE、TABLE_ROWS）在 information_schema.tables（非标准字段扩展）；SQL Server 的索引（sys.indexes）不在信息_SCHEMA；其四，权限——信息_SCHEMA 视图"只显示当前用户有权限的对象"（三库一致语义，但实现细节（PG 的可见性规则、SQL Server 的权限过滤）不同）；其五，方言字段——MySQL 的 information_schema.tables 有 ENGINE/TABLE_ROWS/AUTO_INCREMENT 等扩展字段、PG 无（用 pg_class/pg_relation_size）、SQL Server 用 sys 补充。工程建议：跨库元数据脚本用信息_SCHEMA 公共子集（TABLES/COLUMNS/TABLE_CONSTRAINTS/KEY_COLUMN_USAGE/REFERENTIAL_CONSTRAINTS——三库字段基本兼容）；性能敏感/方言细节（大小、引擎、索引）用各库原生目录（PG 的 pg_catalog、MySQL 的 SHOW/information_schema 扩展、SQL Server 的 sys.）；注意"只显示有权限对象"的语义差异）。

解析

答题先列三库都支持的核心标准视图（TABLES/COLUMNS/TABLE_CONSTRAINTS/KEY_COLUMN_USAGE/REFERENTIAL_CONSTRAINTS）与公共字段，再讲三库差异（PG 实现最全、MySQL 有扩展字段（ENGINE/TABLE_ROWS）、SQL Server 仅标准子集且推荐 sys 目录），然后讲性能（视图层层查询慢、原生目录快）与权限可见性、物化视图/索引不在标准覆盖，最后给工程建议（跨库公共子集 + 方言原生目录）。

示例代码

-- 跨库公共查询（三库可用）
SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_SCHEMA = 'app';
SELECT c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'orders';
-- 方言原生目录
-- PG: pg_class/pg_indexes；MySQL: SHOW TABLE STATUS；SQL Server: sys.tables/sys.indexes

#

★★★

13. MySQL 8.0 的 INSTANT/INPLACE DDL 与元数据锁（MDL）对在线变更的影响

问题

MySQL 8.0 的 INSTANT/INPLACE DDL 与元数据锁（MDL）对在线变更的影响是什么？

考察点

三种 DDL 算法（INSTANT/INPLACE/COPY）
MDL 锁的类型与队列
在线变更的最佳实践

答案

DDL 算法（8.0）：INSTANT——只改元数据（O(1)，如加列（8.0.12+ 部分操作支持：加列（可空/有默认）、改默认值、改列名等）：不重建表、瞬间完成、不占空间；INPLACE——就地构建（支持的操作：加二级索引、加列（非 INSTANT 场景）、改列类型（部分）等）：不复制全表（8.0 对大部分操作）、允许并发 DML（LOCK=NONE 支持的操作）；COPY——复制表（旧操作/不支持的场景）：锁表、空间翻倍、慢。8.0 自动选择"最优算法"（ALGORITHM 可显式指定：INSTANT 优先（支持则用）、否则 INPLACE、否则 COPY（报错或回退（ALGORITHM=INSTANT 不支持时直接报错，INPLACE 不支持时回退 COPY（默认允许回退，可 ALGORITHM=INPLACE 强制报错）））。元数据锁（MDL，Metadata Lock）：DDL/DML 都先获取表级 MDL（共享（SHARED，DML 之间兼容）、排他（EXCLUSIVE，DDL 独占）等类型）；MDL 特性——"队列机制"：新请求排队（FIFO），且"写优先"（排他请求排在共享请求前，避免写饥饿）；影响：其一，长事务阻塞 DDL——DML 事务持有共享 MDL 直到提交，期间 DDL（要排他 MDL）等待（wait，可 lock_wait_timeout 超时）——"在线变更被长事务卡住"是 MySQL 常见事故（会话 A 大事务未提交 → ALTER 等待 → 后续所有 DML 都排队（写优先把读也堵住）→ 应用雪崩）；其二，DDL 期间 DML——INPLACE+LOCK=NONE 的 DDL 持有"排他 MDL 的时间很短"（构建期间降级为共享（允许 DML），仅在提交瞬间升级排他）——大部分时间不阻塞 DML；但 MDL 升级瞬间仍会短暂阻塞；其三，INSTANT——MDL 持有极短（仅元数据改），影响最小。实践：其一，监控长事务（information_schema.innodb_trx、performance_schema 的 metadata_locks 视图查 MDL 等待）；其二，DDL 前检查是否有长事务/大查询（kill 或等其结束）；其三，设置 lock_wait_timeout（DDL 等待超时（8.0 默认 31536000 秒（1 年）——需显式调小避免无限等））；其四，低峰执行 + ALGORITHM/LOCK 显式声明（INSTANT/INPLACE+NONE 优先，失败报错而非静默降级）；其五，大表变更用 pt-osc/gh-ost（MDL 感知的在线工具）；其六，8.0 的 INSTANT 加列有次数限制（8.0.29 前每表最多 64 次 INSTANT 加列（后续需重建）——8.0.29+ 无限制（可复用行尾空间））。MDL 与 replication——DDL 在从库执行时同样持 MDL（从库长查询阻塞复制（从库 DDL 等待）→ 主从延迟）））。

解析

答题先讲三种算法（INSTANT 元数据级/INPLACE 就地/COPY 复制）与 8.0 自动选择及显式控制，再讲 MDL 机制（共享/排他、队列写优先、长事务阻塞 DDL、DDL 提交瞬间升级）与影响（长事务卡 ALTER→雪崩、从库复制延迟），最后给最佳实践（监控 innodb_trx/metadata_locks、lock_wait_timeout、显式算法、pt-osc/gh-ost、INSTANT 次数限制）。

示例代码

ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;      -- 元数据级
ALTER TABLE t ADD INDEX idx (col), ALGORITHM=INPLACE, LOCK=NONE;
-- 查 MDL 等待
SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA='app';
-- 查长事务
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;
SET SESSION lock_wait_timeout = 60;   -- DDL 等待超时

#

★★

14. MySQL 的元数据表（mysql、information_schema、performance_schema、sys schema）的职责划分？

问题

MySQL 的元数据表（mysql、information_schema、performance_schema、sys schema）的职责划分是什么？

考察点

四个系统库的定位
各库的内容与用途
使用建议

答案

四个系统库职责划分：mysql 库——"服务器内部状态与权限字典"：用户账号（user、db、tables_priv 等授权表）、全局/库级权限、时区表、通用日志/慢日志表、帮助表（8.0 中 mysql 库含数据字典（8.0 起数据字典表在 mysql 库（mysql.* 下，如 tables、columns 的底层字典（不可直接改））与授权、复制元数据（slave_master_info 等））；运维不直接修改（官方工具/GRANT 语句操作授权表），只读查询（如 SELECT * FROM mysql.user）。information_schema——"标准元数据视图"：跨库的"库/表/列/约束/引擎/分区/权限"信息（TABLES、COLUMNS、SCHEMATA、STATISTICS（索引）、TABLE_CONSTRAINTS 等），可移植（SQL 标准视图），也含 InnoDB 专有（INNODB_TRX、INNODB_LOCKS（8.0 为 performance_schema.data_locks）、INNODB_TABLES 等）；大库查询慢（视图）。performance_schema——"运行时性能与等待信息"：语句执行（events_statements_）、等待事件（events_waits_）、锁（data_locks/data_lock_waits）、事务（events_transactions_）、内存（memory_summary_）、复制状态（replication_）——性能诊断（慢语句、锁等待、IO）的核心来源；需要启用（performance_schema=ON，默认开启）。sys schema——"performance_schema 的友好封装视图"：把 performance_schema 与 information_schema 包装成易读视图/存储过程（sys.schema_table_lock_waits、sys.innodb_lock_waits、sys.statements_with_full_table_scans、sys.diagnostics() 等），便于快速诊断（官方运维推荐入口）。职责对比：mysql = 权限/账户/字典（静态状态）、information_schema = 元数据标准视图（对象定义）、performance_schema = 运行时指标（动态）、sys = 诊断加速器（封装）。使用建议：查对象定义用 information_schema；查权限用 mysql.（只读）或 SHOW GRANTS；查锁/事务/慢语句用 performance_schema（或 sys 包装）；8.0 中"数据字典"（frm 文件消失）存于 mysql 库（不可直接改，用 DDL 操作））。

解析

答题先分别定义四个库（mysql 权限字典、information_schema 标准元数据视图、performance_schema 运行时指标、sys 封装诊断视图），再给职责对比（静态 vs 动态、标准 vs 专有）与典型查询场景（对象定义/权限/锁事务/慢语句），最后提 8.0 数据字典变化与使用建议。

示例代码

-- 权限：mysql 库（只读）
SELECT user, host, plugin FROM mysql.user;
-- 元数据：information_schema
SELECT TABLE_NAME, ENGINE, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='app';
-- 锁等待：performance_schema
SELECT * FROM performance_schema.data_lock_waits;
-- sys 封装（更易读）
SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM sys.statements_with_full_table_scans;

#

★★

15. PostgreSQL 的系统目录（pg_catalog）核心表（pg_class、pg_attribute、pg_constraint、pg_type、pg_proc）的结构与查询模式？

问题

PostgreSQL 系统目录（pg_catalog）的核心表（pg_class、pg_attribute、pg_constraint、pg_type、pg_proc）结构与查询模式是什么？

考察点

五张核心目录表的字段
关联查询模式（JOIN 链）
与信息_SCHEMA 的对比

答案

核心目录表（pg_catalog schema）：pg_class——"关系（表/索引/视图/序列/物化视图）"目录：relname（名）、relnamespace（所属 schema 的 OID）、relkind（'r' 表/'i' 索引/'v' 视图/'S' 序列/'m' 物化视图/'p' 分区表/'t' toast）、relowner、reltuples（行数估算）、relpages（页数估算）、reloptions（存储参数）；pg_attribute——"列"目录：attrelid（所属关系 OID）、attname、atttypid（类型 OID）、attnum（列号）、attnotnull、attlen/atttypmod（长度）、attisdropped（已删列）；pg_constraint——"约束"目录：conname、contype（'p' 主键/'u' 唯一/'f' 外键/'c' CHECK/'x' 排他）、conrelid（所属表）、confrelid（外键引用表）、conkey/confkey（列号数组）、pg_get_constraintdef()（定义文本）；pg_type——"数据类型"目录：typname、typtype（'b' 基础/'c' 复合/'e' 枚举/'r' 范围/'d' 域）、typrelid（复合类型的表 OID）、typarray；pg_proc——"函数/过程"目录：proname、pronamespace、prorettype（返回类型）、proargtypes（参数类型数组）、prolang（语言）、provolatile（I/V/S）、prosecdef（SECURITY DEFINER）、prosrc（函数体源码）。查询模式：关联用 OID——JOIN pg_namespace（schema 名：nspname）、JOIN pg_type（类型名：format_type(atttypid, atttypmod)）、pg_get_userbyid(relowner)（owner 名）；典型查询：表清单（pg_class JOIN pg_namespace WHERE relkind='r'）、列清单（pg_attribute JOIN pg_type：SELECT attname, format_type(...) FROM pg_attribute WHERE attrelid='t'::regclass AND attnum>0 AND NOT attisdropped）、约束（pg_constraint JOIN pg_class：contype+conkey+pg_get_constraintdef）、函数（pg_proc JOIN pg_namespace：proname+prosrc）；''::regclass/::regtype 快速 OID 转换（名→OID）。与信息_SCHEMA 对比：pg_catalog 信息全（含 OID、存储参数、源码、位标记）、查询快（底层表）、是"权威来源"（信息_SCHEMA 视图底层也查它）；信息_SCHEMA 可移植但慢且字段裁剪。工程建议：运维/诊断脚本用 pg_catalog（JOIN pg_namespace/pg_type 的固定模板）；跨库脚本用信息_SCHEMA；常用辅助视图（pg_indexes、pg_views、pg_stat_*）封装了常见查询。

解析

答题先逐个讲五张核心表的关键字段（pg_class 的 relkind/reltuples、pg_attribute 的 atttypid/attnotnull、pg_constraint 的 contype/confrelid、pg_type 的 typtype、pg_proc 的 provolatile/prosrc），再给典型查询模式（OID JOIN 链、format_type、regclass 转换、表/列/约束/函数四类查询模板），最后对比信息_SCHEMA（权威 vs 可移植）并给使用建议。

示例代码

-- 表清单
SELECT n.nspname, c.relname, c.relkind, pg_get_userbyid(c.relowner) AS owner
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p') AND n.nspname = 'app';
-- 列清单
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull
FROM pg_attribute a WHERE a.attrelid = 'app.users'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped;
-- 约束
SELECT conname, contype, pg_get_constraintdef(oid) FROM pg_constraint
WHERE conrelid = 'app.users'::regclass;
-- 函数（含源码）
SELECT p.proname, p.provolatile, p.prosrc FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'app';

#

★★

16. SQL Server 的系统视图（sys.tables、sys.columns、sys.indexes、sys.objects）与 INFORMATION_SCHEMA 的优先级？

问题

SQL Server 的系统视图（sys.tables、sys.columns、sys.indexes、sys.objects）与 INFORMATION_SCHEMA 的优先级是什么？

考察点

sys 目录视图的内容
与 information_schema 的对比
使用建议

答案

sys 目录视图（sys schema）：sys.objects——"所有 schema 级对象"（object_id、name、type（'U' 用户表/'V' 视图/'P' 存储过程/'FN' 函数/'PK' 约束等）、schema_id、create_date）；sys.tables——"用户表"（object_id、name、schema_id、is_replicated 等表属性（含 create_date、lock_escalation））；sys.columns——"列"（object_id、column_id、name、system_type_id、user_type_id、max_length、is_nullable、is_identity）；sys.indexes——"索引"（object_id、index_id、name、type（0 堆/1 聚集/2 非聚集）、is_unique、is_primary_key、is_unique_constraint），配合 sys.index_columns 取索引列。与信息_SCHEMA 的优先级：SQL Server 官方明确"优先使用 sys 目录视图"——信息_SCHEMA 视图在 SQL Server 中"只为标准兼容保留"：其一，覆盖不全——信息_SCHEMA 不含索引（无标准索引视图）、不含对象类型细节、不含存储/文件组等 SQL Server 专有信息；其二，性能——信息_SCHEMA 是视图（底层多次查询 sys 目录），大库比直接查 sys 慢；其三，权限语义——信息_SCHEMA 只返回"当前用户有权限的对象"（权限过滤），sys 目录直接反映"元数据全集"（配合权限查询（sys.fn_my_permissions 等）自行控制）；其四，完整性——sys 视图与对象生命周期/依赖（sys.sql_dependencies/sys.dm_sql_referencing_entities）等更配套；结论：SQL Server 中"权威元数据源 = sys 目录"（官方文档也以 sys.* 为主），信息_SCHEMA 仅用于"跨库可移植脚本"（MySQL/PG/SQL Server 三库共用的元数据查询）。典型查询：sys.tables JOIN sys.schemas（schema 名）、sys.columns JOIN sys.types（类型名）、sys.indexes JOIN sys.index_columns（索引列与顺序）、sys.objects 按 type 过滤（表/视图/过程）。工程建议：SQL Server 专属脚本用 sys.*（性能与完整性）；跨库脚本用信息_SCHEMA 公共子集；迁移工具（SSDT/DAC）用 sys 目录做 schema 比较。

解析

答题先逐个介绍 sys 视图（objects/tables/columns/indexes 与关键字段与 type 编码），再重点讲优先级（SQL Server 官方推荐 sys 目录：信息_SCHEMA 覆盖不全（无索引）、性能慢、权限过滤差异、与依赖配套），最后给工程建议（专属脚本 sys.*、跨库脚本信息_SCHEMA）与典型查询模板。

示例代码

-- 表清单（sys + schemas）
SELECT s.name AS schema_name, t.name AS table_name, t.create_date
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'dbo';
-- 列清单
SELECT c.name, ty.name AS type_name, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.orders');
-- 索引清单
SELECT i.name, i.type_desc, i.is_unique, i.is_primary_key
FROM sys.indexes i WHERE i.object_id = OBJECT_ID('dbo.orders') AND i.type > 0;

#

★★

17. DDL 与 DML 对元数据一致性的影响，MVCC 下元数据快照如何？

问题

DDL 与 DML 对元数据一致性有何影响？MVCC 下元数据快照如何工作？

考察点

元数据与 MVCC 的关系
DDL 与并发查询的元数据一致性
各库的实现差异

答案

问题：查询执行需要"元数据（表结构/列/约束）"；DDL 修改元数据；并发场景（DDL 与长查询同时）需保证"查询看到的元数据一致"（查询开始时的结构 vs DDL 后的结构）。实现机制：PostgreSQL——"元数据同样走 MVCC"：pg_class/pg_attribute 等目录表本身是普通表（有版本链与快照），每个事务的元数据读取按"自己的快照"：DDL（ALTER TABLE 加列）是"元数据行的新版本"——已开始的长查询（持有旧快照）看到的仍是"旧元数据"（列数不变），新查询看到新结构；但物理层面——长查询扫描表时"元组布局可能已变"（PG 处理：加列等 DDL 会"锁表（ACCESS EXCLUSIVE）"等旧查询结束？PG 的 ALTER TABLE ADD COLUMN（8.0 起可非阻塞（加列不需要重写（8.0 起 instant 加列？PG 的 ADD COLUMN 默认"只改元数据不重写表"（PG 11+ 加默认值快的版本）——但需要 ACCESS EXCLUSIVE 锁（等待长查询结束）；PG 的 MVCC 保证"查询开始时的元数据快照"一致性：DDL 与查询通过"锁 + 元数据版本"协同——长查询阻塞 DDL（ACCESS EXCLUSIVE 等待）、DDL 提交后新查询用新元数据；已开始查询用旧元数据继续（PG 的查询计划在开始时就固定（元数据快照在计划生成时））。MySQL——"8.0 数据字典 + MDL"：查询执行时按"MDL 共享锁"保护元数据读取（读元数据需要共享 MDL），DDL 需要排他 MDL（等待长查询结束）——因此"元数据一致性"靠 MDL 串行化（查询开始时获取共享 MDL 直到语句结束：DDL 不能越过正在执行的语句——同一语句内元数据不变）；MVCC（InnoDB 行版本）管"数据"、MDL 管"元数据"（两层分离）；8.0 的原子 DDL（数据字典事务化）保证 DDL 失败不留半状态。SQL Server——"schema stability（Sch-S）与 schema modification（Sch-M）锁"：Sch-S（查询持有，允许并发查询）、Sch-M（DDL 独占，等待 Sch-S 释放）——机制与 MySQL 的 MDL 类似（元数据锁串行化）；快照隔离下查询的元数据按语句开始（计划编译时）。共性结论：元数据一致性由"元数据锁（MySQL/SQL Server 的 MDL/Sch-M、PG 的 ACCESS EXCLUSIVE）+ 版本/快照（PG 的目录 MVCC）"保证：长事务/长查询"阻塞 DDL"是普遍现象（PG 的 ALTER 等 DDL、MySQL 的 DDL、SQL Server 的 Sch-M）——在线变更需等"无活动查询"窗口（MDL 问题）；"查询看到一致的元数据"是数据库保证的（计划/语句级快照）。实践：变更前查长事务/长查询（pg_stat_activity、innodb_trx、sys.dm_exec_requests）、低峰执行、8.0/PG 的在线 DDL 减少阻塞窗口））））。

解析

答题先讲问题（查询需元数据、DDL 改元数据、并发一致性），再分别讲三库机制（PG 目录 MVCC+ACCESS EXCLUSIVE 锁、MySQL MDL+8.0 原子 DDL、SQL Server Sch-S/Sch-M），最后给共性结论（长查询阻塞 DDL 普遍、语句级元数据快照保证一致）与实践建议。

#

★★

18. PostgreSQL 中 pg_description 与 COMMENT ON 的关系？

问题

PostgreSQL 中 pg_description 与 COMMENT ON 的关系是什么？注释如何存储与查询？

考察点

COMMENT ON 的存储位置
pg_description 的结构
注释的查询与导出

答案

关系：COMMENT ON TABLE/COLUMN/... IS '...' 语句把注释"写入 pg_description 系统表"（或 pg_shdescription（共享对象：数据库/角色/表空间））——pg_description 是注释的物理存储：字段：objoid（被注释对象的 OID）、classoid（对象所属目录的 OID（pg_class/pg_attribute/pg_proc 等））、objsubid（子对象号：0 表示对象本身、>0 表示列号（列注释 objsubid=列号））、description（注释文本）；COMMENT ON 是"写操作"（INSERT/UPDATE pg_description）、查询注释是"读 pg_description"；删除注释用 COMMENT ON ... IS NULL（删除行）。查询方式：obj_description('t'::regclass, 'pg_class')——按对象名+目录取注释（表注释）；col_description('t'::regclass, 1)——取第 1 列注释；psql 的 \d+ t 显示表/列注释；批量导出：SELECT c.relname, d.description FROM pg_description d JOIN pg_class c ON c.oid = d.objoid WHERE d.classoid = 'pg_class'::regclass AND d.objsubid = 0（表注释清单）；列注释 JOIN pg_attribute。与信息_SCHEMA 的关系：信息_SCHEMA 无标准注释视图（PG 的信息_SCHEMA 没有 COMMENT 字段——注释查询用 pg_description/obj_description）。细节：其一，注释不参与约束/执行（纯文档）；其二，pg_dump 导出注释（COMMENT ON 语句随对象导出——迁移/备份含注释）；其三，对象删除自动清理注释（级联）；其四，类目——不同对象类型用 classoid 区分（pg_class 的表、pg_attribute 的列、pg_proc 的函数、pg_type 的类型），同一 objoid 可有多行（表+列（objsubid 不同））；其五，性能——注释查询走 OID 索引；大库注释量大时 pg_description 有体积（一般可忽略）。工程建议：注释进代码评审与迁移脚本（Flyway 的 COMMENT 语句）；字典/数据目录工具从 pg_description 生成文档；统一注释格式（口径、单位、枚举含义）便于自动化解析。

解析

答题先讲存储关系（COMMENT ON 写入 pg_description/pg_shdescription：objoid/classoid/objsubid/description 四字段），再给查询方式（obj_description/col_description/\d+、批量 JOIN 模板）与导出（pg_dump 含注释、信息_SCHEMA 无注释），最后讲细节（类目区分、对象删除清理）与工程建议。

示例代码

COMMENT ON TABLE app.users IS '用户表';
COMMENT ON COLUMN app.users.email IS '登录邮箱（小写）';
SELECT obj_description('app.users'::regclass);          -- 表注释
SELECT col_description('app.users'::regclass, 3);       -- 第 3 列注释
-- 批量导出表注释
SELECT n.nspname, c.relname, d.description
FROM pg_description d
JOIN pg_class c ON c.oid = d.objoid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE d.classoid = 'pg_class'::regclass AND d.objsubid = 0;
-- 删除注释
COMMENT ON TABLE app.users IS NULL;

#

★★

19. DDL 触发器（Event Trigger）监听哪些元数据变更？

问题

DDL 触发器（Event Trigger）监听哪些元数据变更？事件范围与限制是什么？

考察点

Event Trigger 的事件类型
可监听的 DDL 范围
限制与用途

答案

Event Trigger（PG 的事件触发器）监听"DDL 事件"（元数据变更）：事件类型（事件名）：ddl_command_start——任何 DDL 语句开始前（可阻止）；ddl_command_end——DDL 成功完成后（记录）；sql_drop——DROP 语句中对象被删除时（获取被删对象清单）；table_rewrite（PG 10+）——表被重写（ALTER TYPE/ADD COLUMN 触发重写）时（可阻止大表重写：如禁止对超大表做触发重写的 ALTER）；事件范围：所有"DDL 命令标签"（CREATE/ALTER/DROP 的 TABLE/INDEX/VIEW/FUNCTION/SCHEMA/TYPE/SEQUENCE 等——事件触发器的触发由"命令标签（command tag）"决定（如 'CREATE TABLE'、'ALTER TABLE'、'DROP FUNCTION'）），可在事件触发器函数中用 tg_tag 读取当前命令标签，并用 pg_event_trigger_ddl_commands()（ddl_command_end 中取命令详情：对象类型/身份/命令字符串）、pg_event_trigger_dropped_objects()（sql_drop 中被删对象列表）获取明细。可监听 vs 不可监听：事件触发器"不监听"——DML（INSERT/UPDATE/DELETE/TRUNCATE 是行/语句触发器职责）、非 DDL 的内部操作（VACUUM/ANALYZE 不属于事件触发（无事件名）、临时表 DDL（pg_temp 中 DDL 不触发？PG 中事件触发器对临时表的 DDL 不触发（文档：temp 对象不触发））、被事件触发器自身触发的 DDL（防递归）；限制：其一，事件触发器"仅数据库级"（CREATE EVENT TRIGGER ... ON ddl_command_start——无 schema/表粒度（函数内按 tg_tag 与对象过滤（如仅拦截特定表的 ALTER/DROP））；其二，权限——需超级用户创建；其三，不能对"已经开始的语句"撤销（ddl_command_start 抛错阻止、ddl_command_end 只能记录不能回滚（抛错可回滚语句（ddl_command_end 中 RAISE 会回滚 DDL？ddl_command_end 抛错导致整个命令失败回滚——可用））；其四，递归——事件触发器自身的 DDL 不再触发；其五，与行触发器/约束触发器区分（DDL vs DML）。用途：DDL 审计（记录变更）、DDL 防护（阻止危险 DDL：DROP TABLE/大规模 ALTER（结合 table_rewrite 阻止重写））、变更追踪（迁移同步：把 DDL 记录到审计表）、sql_drop 归档（删表前备份结构）。其他库：SQL Server 的 DDL 触发器（DATABASE 级：AFTER CREATE_TABLE/ALTER_TABLE/DROP_TABLE 等事件组，DDL 事件清单（事件组 EVENTDATA() 返回 XML）——功能对等）；MySQL 无事件触发器（用审计插件/general_log））））。

解析

答题先列事件类型（ddl_command_start/end、sql_drop、table_rewrite）与监听范围（命令标签决定、tg_tag、配套函数取命令/对象明细），再讲不监听范围（DML、VACUUM/ANALYZE、临时表 DDL、防递归）与限制（数据库级、超级用户、阻止时机），最后给用途（审计/防护/重写阻止/归档）与 SQL Server 对照。

示例代码

CREATE FUNCTION audit_ddl() RETURNS event_trigger AS $$
BEGIN
  INSERT INTO ddl_log(tag, user, at)
  VALUES (tg_tag, current_user, now());
  IF tg_tag = 'DROP TABLE' AND current_user <> 'admin' THEN
    RAISE EXCEPTION 'DROP TABLE blocked';
  END IF;
END $$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER evt ON ddl_command_end EXECUTE FUNCTION audit_ddl();
-- 阻止大表重写
CREATE FUNCTION no_rewrite() RETURNS event_trigger AS $$
BEGIN
  IF pg_event_trigger_table_rewrite_oid() = 'big_table'::regclass THEN
    RAISE EXCEPTION 'rewrite of big_table not allowed';
  END IF;
END $$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER evt_rw ON table_rewrite EXECUTE FUNCTION no_rewrite();

#

★★

20. MySQL 中 information_schema.INNODB_TRX 的用途？

问题

MySQL 中 information_schema.INNODB_TRX 的用途是什么？如何用它诊断事务问题？

考察点

INNODB_TRX 的字段
诊断长事务/锁问题
与其他视图配合

答案

INNODB_TRX（information_schema 中的 InnoDB 专有视图）——列出"当前 InnoDB 所有正在运行的事务"：关键字段：trx_id（事务 ID）、trx_state（RUNNING/LOCK WAIT/ROLLING BACK/COMMITTING）、trx_started（开始时间）、trx_mysql_thread_id（连接线程 id，可 KILL）、trx_query（当前执行的 SQL）、trx_rows_locked/trx_rows_modified（锁行数/修改行数）、trx_isolation_level、trx_lock_memory_bytes 等。用途（事务诊断）：其一，查长事务——SELECT * FROM information_schema.innodb_trx ORDER BY trx_started：运行很久未提交的事务（trx_started 很早）——长事务的后果：持锁阻塞他人（锁等待链）、undo log 膨胀（回滚段不清）、MVCC 版本堆积（purge 停滞）——定位后 KILL（KILL trx_mysql_thread_id）或通知应用提交/回滚；其二，查锁等待——配合 performance_schema.data_locks/data_lock_waits（8.0；5.7 用 INNODB_LOCKS/INNODB_LOCK_WAITS）：找"等待者与持有者"（谁锁了谁、阻塞源头（trx_mysql_thread_id 对应连接））；其三，事务状态与进度——ROLLING BACK（回滚中）、LOCK WAIT（等待锁：可看 trx_query 判断当前语句）；其四，事务规模——trx_rows_locked/rows_modified 大 → 大事务（批处理未提交）风险（锁范围大、回滚时间长）；其五，隔离级别确认（trx_isolation_level）。诊断流程示例：发现锁等待（performance_schema 或 SHOW ENGINE INNODB STATUS）→ INNODB_TRX 看等待事务与持有事务（trx_started 最早的通常是源头）→ KILL 阻塞连接或让其提交 → 复查。注意：其一，8.0 中锁信息迁移到 performance_schema（data_locks/data_lock_waits），INNODB_TRX 仍保留（事务信息）；5.7 的 INNODB_LOCKS/INNODB_LOCK_WAITS 在 8.0 移除；其二，权限——查询需 PROCESS 权限；其三，性能——该视图是"动态生成"（每查询扫事务链表），大并发下查询本身有开销（诊断时用）；其四，trx_query 对"非当前连接"的事务可能为 NULL（无权限/已结束语句）；其五，配合 SHOW PROCESSLIST（连接状态）、sys.schema_table_lock_waits（MDL 等待）、performance_schema.events_statements_current（语句详情）形成完整诊断链。

解析

答题先介绍 INNODB_TRX 的定位（当前全部 InnoDB 事务）与关键字段（trx_state/started/mysql_thread_id/query/rows_locked），再给三类诊断用途（长事务定位与 KILL、锁等待配合 data_lock_waits 找源头、事务规模与状态），最后列注意（8.0 锁视图迁移、PROCESS 权限、动态查询开销、配合 SHOW PROCESSLIST 诊断链）。

示例代码

-- 长事务（最老在前）
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;
-- 锁等待（8.0）
SELECT * FROM performance_schema.data_lock_waits;
-- 终止阻塞事务（谨慎）
KILL <trx_mysql_thread_id>;
-- 或等其提交；复查
SELECT COUNT(*) FROM information_schema.innodb_trx;

#

★★

21. MySQL 中 information_schema.TABLES 的 TABLE_ROWS 字段为何是估算值？

问题

MySQL 中 information_schema.TABLES 的 TABLE_ROWS 字段为何是估算值？如何获取准确行数？

考察点

TABLE_ROWS 的估算机制
与真实行数的差异
准确统计方法

答案

TABLE_ROWS 是"估算值"的原因：InnoDB 不维护"精确行数"（与 MyISAM 不同——MyISAM 存储精确行数（COUNT() 直接返回）、InnoDB 为支持 MVCC 与并发（每行有版本链、删除/插入频繁）不维护全局计数器）；information_schema.tables.TABLE_ROWS 取自"统计信息（InnoDB 的 statistics：基于采样估算（字典中的 n_rows 估算：按"页数 × 每页平均行数"估算，随 ANALYZE 更新））——因此：其一，与真实行数偏差（可能 30%-100%+ 差异）；其二，更新时机——ANALYZE TABLE（或 InnoDB 自动统计（参数 innodb_stats_on_metadata？8.0 中查询 information_schema 可能触发统计刷新（stats_on_metadata 默认 OFF？8.0 中 information_schema 查询不触发重算（除非 innodb_stats_on_metadata=ON（默认 OFF））；ANALYZE/自动 analyze（innodb_stats_auto_recalc 默认 ON：表变化超 10% 自动重算）更新）；其三，用途——优化器用"估算行数"做计划（TABLE_ROWS 反映的是"优化器视角的行数"）；运维误用 TABLE_ROWS 做"精确计数"会导致误判（容量规划、对账）。获取准确行数的方法：其一，COUNT()（全表扫描（InnoDB 无计数缓存）：准确但大表慢（秒级-分钟级）——可用 COUNT() 加条件（主键范围分片并行）或"计数缓存表"（应用维护计数器：INSERT/UPDATE/DELETE 时更新计数表（事务内）——实时准确、需应用配合）；其二，采样估算（近似）：TABLE_ROWS/EXPLAIN 的 rows（估算，快）；其三，show table status（同 TABLE_ROWS 估算）；其四，近似计数技巧（information_schema 估算 + 样本比例）或 EXPLAIN 的 rows 做"量级判断"；其五，8.0 的"直方图"不影响 TABLE_ROWS（直方图是列分布）；其六，外键/权限视图——information_schema.tables 受权限过滤（只显示有权限的表）。工程建议：需要"精确行数"用 COUNT()（低频/小表）或计数缓存（高频/大表）；"量级/容量"用 TABLE_ROWS/EXPLAIN rows（快）；监控告警用 TABLE_ROWS 需设容差；对账场景必须 COUNT(*)）））。

解析

答题先讲原因（InnoDB 为 MVCC/并发不维护精确计数、TABLE_ROWS 是统计信息估算（页×每页行数、ANALYZE/自动重算更新）），再讲差异幅度与更新时机（innodb_stats_auto_recalc、ANALYZE），然后给准确方法（COUNT(*) 慢但准、计数缓存表、分片并行）与近似方法（EXPLAIN rows），最后给工程建议（按场景选择）。

示例代码

SELECT TABLE_ROWS, TABLE_NAME FROM information_schema.tables
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'orders';   -- 估算
SELECT COUNT(*) FROM app.orders;                         -- 准确（大表慢）
ANALYZE TABLE app.orders;                                -- 刷新估算
-- 计数缓存表（高精度低成本）
CREATE TABLE tbl_count (t VARCHAR(50) PRIMARY KEY, n BIGINT);
-- 事务内维护：INSERT 后 UPDATE tbl_count SET n = n+1 ...

#

★★

22. MySQL 的 SHOW 命令与 INFORMATION_SCHEMA 查询的取舍？

问题

MySQL 的 SHOW 命令与 INFORMATION_SCHEMA 查询的取舍是什么？各自适用场景是什么？

考察点

SHOW 与信息_SCHEMA 的对应
性能与可移植性差异
使用建议

答案

对应关系：多数 SHOW 命令有信息_SCHEMA 等价物——SHOW TABLES ↔ information_schema.tables、SHOW COLUMNS FROM t ↔ information_schema.columns、SHOW INDEX FROM t ↔ information_schema.statistics、SHOW CREATE TABLE ↔（无直接视图，用 SHOW）、SHOW PROCESSLIST ↔ performance_schema.threads/events_statements_current、SHOW VARIABLES ↔ performance_schema.variables_info/global_variables、SHOW STATUS ↔ performance_schema.global_status；实现上"SHOW 命令底层也是查询信息_SCHEMA/performance_schema"（SHOW 是语法糖/客户端展示）。取舍维度：其一，性能——SHOW 命令通常更快（内部按优化路径查询（SHOW TABLES LIKE 比 SELECT ... WHERE 快）、8.0 的 SHOW 与信息_SCHEMA 查询性能接近（都走底层表）；信息_SCHEMA 视图查询在大库上可能较慢（视图展开）；其二，可编程性——信息_SCHEMA 可 SELECT：过滤/排序/JOIN/聚合（跨库统计：按引擎分组、JOIN 列与约束）、可作为子查询（动态生成 SQL：把"所有含某列的表"生成 ALTER 语句）；SHOW 只能展示/简单 LIKE 过滤（无法 JOIN、聚合）；其三，可移植性——信息_SCHEMA 是标准（跨库脚本（PG/MySQL/SQL Server）可用），SHOW 是 MySQL 方言（SHOW CREATE TABLE 无跨库等价）；其四，功能覆盖——部分信息只在 SHOW（SHOW CREATE TABLE/VIEW/PROCEDURE——完整 DDL 文本（信息_SCHEMA 无等价视图）；SHOW ENGINE INNODB STATUS（InnoDB 状态文本））；部分只在信息_SCHEMA/性能库（INNODB_TRX、data_locks 等）；其五，权限——两者都按权限过滤（SHOW 同信息_SCHEMA 语义）；其六，输出形态——SHOW 是"结果集"（客户端友好、\G 竖排），信息_SCHEMA 可自由列选择。使用建议：交互式快速查看用 SHOW（SHOW TABLES/COLUMNS/CREATE TABLE/INDEX）；脚本与程序化查询用信息_SCHEMA（过滤/JOIN/聚合、跨库兼容）；需要"完整 DDL"用 SHOW CREATE TABLE（或 mysqldump --no-data）；性能诊断用 performance_schema/sys（SHOW PROCESSLIST 快速看连接）。注意：8.0 中 SHOW 与信息_SCHEMA 的"性能差距缩小"（都直接查底层表）；信息_SCHEMA 查询"触发统计刷新"的行为（8.0 默认不刷新（stats_on_metadata OFF）——查询快））。

解析

答题先给 SHOW ↔ 信息_SCHEMA 的对应表（tables/columns/index/processlist/variables），再讲取舍四维度（性能（SHOW 略快/底层相同）、可编程（信息_SCHEMA 可 JOIN 聚合子查询）、可移植（信息_SCHEMA 标准、SHOW 方言）、功能覆盖（SHOW CREATE 独有、INNODB_TRX 独有）），最后给使用建议（交互 SHOW、脚本信息_SCHEMA、DDL 用 SHOW CREATE）。

示例代码

-- 交互快速
SHOW TABLES FROM app LIKE 'order%';
SHOW COLUMNS FROM app.orders;
SHOW CREATE TABLE app.orders;
-- 程序化（信息_SCHEMA：可过滤/聚合/生成 DDL）
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_SCHEMA='app' AND TABLE_NAME LIKE 'order%';
-- 跨库统计
SELECT ENGINE, COUNT(*) FROM information_schema.tables GROUP BY ENGINE;
-- 动态生成（把有 email 列的表批量加索引）
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ADD INDEX idx_email (email);')
FROM information_schema.columns WHERE COLUMN_NAME='email' AND TABLE_SCHEMA='app';

#

★★

23. PostgreSQL 中如何查询所有自定义函数？

问题

PostgreSQL 中如何查询所有自定义函数？各查询方式的差异是什么？

考察点

pg_proc 的过滤条件
与系统函数的区分
函数源码与参数的查询

答案

查询方式：其一，psql——\df（当前 schema 的函数列表：名字/参数/返回类型/语言/稳定性）、\df+（含源码（prosrc）与描述）；\df app.*（按 schema）；其二，pg_proc 查询——SELECT p.proname, pg_get_function_arguments(p.oid) AS args, pg_get_function_result(p.oid) AS ret, p.prolang, p.provolatile, p.prosrc FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname NOT IN ('pg_catalog','information_schema') AND n.nspname NOT LIKE 'pg_toast%';——过滤"非系统 schema"即自定义函数（业务函数在业务 schema 中）；排除系统函数（pg_catalog 中）与扩展函数（扩展 schema（如 pg_catalog 或扩展自身 schema——扩展函数在扩展 schema（如 public 中安装的扩展（pg_trgm 的函数在 public？扩展函数安装到指定 schema（默认 public）——若扩展装在 public，按 schema 过滤会混入扩展函数：需按"prokind（f 函数/p 过程/a 聚合/w 窗口）"与"扩展归属（pg_depend deptype='e'）"进一步区分）；其三，information_schema.routines——SELECT routine_name, routine_type, external_language FROM information_schema.routines WHERE routine_schema = 'app' AND routine_type = 'FUNCTION'（标准视图：函数/过程清单（无源码、性能较慢））；其四，pg_views？函数无此视图；查询细节：参数与返回用 pg_get_function_arguments/pg_get_function_result（比手工解析 proargtypes 友好）、函数体源码 prosrc（plpgsql/SQL 语言函数体文本；C 函数是符号名）、语言名称（JOIN pg_language：lanname（sql/plpgsql/c/plpython））、稳定性（provolatile：i/s/v）、prokind（f/p/a/w：函数/过程/聚合/窗口——PG 11+ 区分过程（prokind='p'））。区分"自定义 vs 系统/扩展"：排除 pg_catalog/information_schema schema + 排除扩展函数（JOIN pg_depend deptype='e'（扩展成员））——纯"用户自定义"（业务代码写的）常用"业务 schema 过滤 + 排除扩展"；函数重载——同名多签名函数各一行（按 proargtypes 区分）。应用场景：审计（谁写了什么函数）、迁移（导出函数 DDL（pg_dump -s 或 prosrc 重建（注意语言头））、清理（无引用函数）、文档生成））））。

解析

答题先给四种查询（\df、pg_proc JOIN 模板（过滤非系统 schema + 排除扩展）、information_schema.routines、细节函数），再讲关键字段（prosrc 源码、pg_get_function_arguments/result、provolatile、prokind）与"自定义 vs 系统/扩展"的区分方法，最后给应用场景（审计、导出、清理）。

示例代码

\df app.*
-- pg_proc 自定义函数（业务 schema）
SELECT p.proname, pg_get_function_arguments(p.oid) AS args,
       pg_get_function_result(p.oid) AS ret,
       l.lanname, p.provolatile, p.prosrc
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname = 'app' AND p.prokind = 'f';
-- 排除扩展函数
... AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = p.oid AND d.deptype = 'e');
-- 标准视图（无源码）
SELECT routine_name, external_language FROM information_schema.routines
WHERE routine_schema = 'app' AND routine_type = 'FUNCTION';

#

★★

24. PostgreSQL 中如何查询索引使用统计？pg_stat_user_indexes？

问题

PostgreSQL 中如何查询索引使用统计？pg_stat_user_indexes 的用途与字段是什么？

考察点

索引使用统计视图
字段含义（idx_scan 等）
未使用索引的识别

答案

统计视图：pg_stat_user_indexes（用户索引的使用统计）与 pg_stat_all_indexes（含系统索引）、pg_statio_user_indexes（IO 统计）：关键字段：relid/indexrelid（表/索引 OID）、schemaname/relname/indexrelname（名称）、idx_scan（索引被"扫描次数"——使用频率核心指标）、idx_tup_read（扫描返回的元组数）、idx_tup_fetch（回表取的行数）、（pg_statio_* 有 idx_blks_read/idx_blks_hit（读页/命中页——IO 视角））。用途：其一，识别"未使用索引"——idx_scan = 0（或长期极低）的索引：候选删除对象（索引有维护成本（写入放大）与存储占用，无查询使用应删除）：SELECT ... FROM pg_stat_user_indexes WHERE idx_scan = 0（注意：新建索引统计从创建时开始累计（刚建的 idx_scan 低正常）；统计自"最近一次统计重置（pg_stat_reset）"起累计）；其二，评估索引使用分布——高频扫描的索引（idx_scan 高）是核心索引（删除影响大）；其三，覆盖/回表分析——idx_tup_fetch 高而 idx_tup_read 高（回表多）→ 考虑覆盖索引（INCLUDE）减少回表；idx_tup_read = idx_scan 比例（每次扫描读多少元组）；其四，JOIN pg_class 看索引大小（pg_relation_size(indexrelid)）——"未使用且巨大"的索引优先删除（存储回收）。注意：其一，统计是"累计值"（从服务器启动/重置起）——对比用"两次快照差值"或先 RESET 再观察；其二，pg_stat_user_indexes 不含"索引未被使用但被约束需要"的情况（唯一约束伴随索引 idx_scan 可能低但"约束功能"必需（唯一性检查不记入 idx_scan？唯一约束的"检查"（写路径）不增加 idx_scan（idx_scan 只计"查询扫描"）——删除前需确认约束依赖）；其三，外键索引——子表外键索引"查询扫描少但 DELETE/UPDATE 父表时被使用"（级联/检查扫描计入 idx_scan？外键检查的索引查找计入 idx_scan（会显示使用）——实际会）；其四，部分索引/表达式索引在统计中同名显示（按 indexrelname）；其五，重置：SELECT pg_stat_reset();（清全部统计）或按表（pg_stat_reset_single_table_counters）。实践：定期（周/月）巡检"idx_scan 极低 + 体积大"的索引（DROP INDEX 前确认无约束/外键依赖（pg_index.indisunique/indisprimary、pg_depend）并用"灰度删除"（先注释禁用？PG 无禁用索引——用 DROP+观察（或改名观察））；监控核心表索引使用分布（容量与写放大优化）））。

解析

答题先介绍视图（pg_stat_user_indexes/pg_statio_*）与关键字段（idx_scan/tup_read/tup_fetch/IO 字段），再给三类用途（未使用索引识别（idx_scan=0+大小）、核心索引评估、回表与覆盖分析），最后列注意（累计值需快照对比、约束伴随索引、外键索引、重置方法与删除前确认）。

示例代码

-- 未使用索引（含大小）
SELECT s.indexrelname, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan, s.idx_tup_read
FROM pg_stat_user_indexes s
WHERE s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC;
-- 使用最多的索引
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes
ORDER BY idx_scan DESC LIMIT 10;
-- 回表分析（tup_fetch 高 → 考虑覆盖索引）
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes WHERE idx_tup_fetch > 0 ORDER BY idx_tup_fetch DESC;

#

★★

25. PostgreSQL 中查询表的物理大小（pg_relation_size）？

问题

PostgreSQL 中如何查询表的物理大小？pg_relation_size 与相关函数的用法是什么？

考察点

pg_relation_size 等大小函数
表大小构成（堆+TOAST+索引）
大小查询实践

答案

大小函数族：pg_relation_size(oid)——"指定关系（表/索引）的主存储大小"（不含 TOAST 与索引，单位字节）；pg_total_relation_size(oid)——表总大小 = 堆 + TOAST 表 + 所有索引（"全包含"）；pg_indexes_size(oid)——索引总大小；pg_table_size(oid)——堆 + TOAST（不含索引）；pg_size_pretty(bytes)——格式化为可读（KB/MB/GB）；pg_size_bytes(text)——反向解析。查询示例：SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total, pg_size_pretty(pg_relation_size('orders')) AS heap, pg_size_pretty(pg_indexes_size('orders')) AS indexes, pg_size_pretty(pg_table_size('orders')) AS table_total;——查看表总体积与构成（堆/索引/TOAST 拆分：TOAST = pg_table_size - pg_relation_size）。批量：按 schema 排名（JOIN pg_class：SELECT n.nspname, c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) FROM pg_class c JOIN pg_namespace n ... ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20——找大表）；所有表总大小（SUM(pg_total_relation_size)）；数据库大小——pg_database_size('dbname')（库级：所有表+WAL 之外）；psql 快捷——\l+（库大小）、\dt+（表大小含描述）、\di+（索引大小）。注意：其一，大小是"逻辑页大小×页数"（含膨胀（死元组未回收的空间计入）——VACUUM FULL/REINDEX 后变小）；其二，pg_relation_size 对"表"不含索引（常被误用为"表大小"——完整视图用 pg_total_relation_size）；其三，分区表——父表 pg_total_relation_size 不含子分区（需聚合各分区（pg_total_relation_size 每个分区））；其四，临时表大小也可查（会话内）；其五，大库批量查询有开销（按 OID 逐个算页数——用 pg_class.relpages（估算页数）× 页大小快速近似（SELECT relpages * 8192 FROM pg_class））。工程实践：容量监控（定期收集 pg_total_relation_size 快照入库）、膨胀检测（relpages 估算 vs pgstattuple 精确）、清理候选（大而 idx_scan 低的索引）。

解析

答题先讲函数族（pg_relation_size 主存储、pg_total_relation_size 全含、pg_indexes_size、pg_table_size、pg_size_pretty）与示例（构成拆分：堆/TOAST/索引），再给批量/库级查询（排名、SUM、pg_database_size、\dt+）与近似（relpages×页大小），最后列注意（含膨胀、分区聚合、临时表）与工程实践。

示例代码

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total,
       pg_size_pretty(pg_relation_size('orders')) AS heap,
       pg_size_pretty(pg_indexes_size('orders')) AS idx,
       pg_size_pretty(pg_table_size('orders')) AS heap_toast;
-- 大表排名
SELECT n.nspname, c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'app'
ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;
-- 库大小
SELECT pg_size_pretty(pg_database_size('appdb'));

#

★★

26. PostgreSQL 的 pg_settings 视图的用途？

问题

PostgreSQL 的 pg_settings 视图的用途是什么？关键字段与查询模式是什么？

考察点

pg_settings 的定位（GUC 参数视图）
关键字段（name/context/setting）
参数排查与设置

答案

pg_settings——"服务器运行时参数（GUC）的视图"：每个参数一行，展示当前值、默认值、可设置范围、生效范围与来源；关键字段：name（参数名）、setting（当前值）、unit（单位）、category（分类）、context（生效范围：internal/postuser/suser/user——"何时可设置"：internal（启动时固定）、postuser（配置文件/超级用户）、suser（超级用户/会话）、user（任何用户/会话））、vartype（类型：bool/int/real/string/enum）、min_val/max_val（范围）、boot_val（编译默认）、reset_val（重置值）、source（来源：default/configuration_file/environment/session/command line）、pending_restart（是否需重启生效）、short_desc/extra_desc（描述）。用途：其一，参数排查——SHOW work_mem 单参数 vs SELECT * FROM pg_settings WHERE name='work_mem'（含来源/范围/重启标志）；按分类查看（category='Resource Usage / Memory'）了解内存参数族；其二，确认修改是否生效——ALTER SYSTEM 修改后查 pg_settings（pending_restart=true 表示需重启）；session 级 SET 后 source='session'（当前会话覆盖）；其三，变更审计/对比——导出当前配置（\dconfig？SELECT name, setting FROM pg_settings）、对比主从配置差异（pg_settings 两端对比）；其四，找参数——按描述搜索（short_desc ILIKE '%memory%'）找出相关参数；其五，动态参数 vs 静态——context 字段（user 可 SET、internal 需重启）。相关：SHOW ALL（psql 列出全部参数）；pg_file_settings（配置文件里的设置（含注释/是否生效（被覆盖的显示 applied=false）））、pg_db_role_setting（库/角色级设置）、ALTER SYSTEM（写入 postgresql.auto.conf）。注意：其一，pg_settings 是"当前会话视角"（session 级 SET 会反映在 setting 与 source）；其二，参数有单位（unit：kB/ms 等，setting 是数值、单位列分开）；其三，pending_restart 是运维关键（确认是否需要重启）；其四，权限——pg_settings 可读（部分敏感参数（密码相关）不可见）。工程实践：变更流程"查 pg_settings（当前值/来源）→ ALTER SYSTEM/SET → 复查（pending_restart）→ 配置管理（模板同步）"；监控关键参数（shared_buffers/work_mem/max_connections）与默认值对比。

解析

答题先定义 pg_settings（GUC 参数视图）与关键字段（name/setting/context/source/pending_restart/unit/vartype/min_max），再给三类用途（参数排查（SHOW vs pg_settings 带来源）、生效确认（pending_restart）、变更对比与分类浏览），最后讲相关（pg_file_settings、ALTER SYSTEM、pg_db_role_setting）与注意（会话视角、单位、权限）。

示例代码

SHOW work_mem;
SELECT name, setting, unit, context, source, pending_restart, boot_val
FROM pg_settings WHERE name IN ('work_mem','shared_buffers','max_connections');
-- 需重启的参数
SELECT name, setting FROM pg_settings WHERE pending_restart;
-- 按分类
SELECT name, setting FROM pg_settings WHERE category ILIKE '%memory%';
-- 配置文件视角
SELECT * FROM pg_file_settings;

#

★★

27. 如何查询指定表的所有列信息？INFORMATION_SCHEMA.COLUMNS 的关键字段？

问题

如何查询指定表的所有列信息？INFORMATION_SCHEMA.COLUMNS 的关键字段是什么？

考察点

COLUMNS 视图的关键字段
按表过滤的查询
与 pg_catalog 的对比

答案

查询：SELECT COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_COMMENT? FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='app' AND TABLE_NAME='orders' ORDER BY ORDINAL_POSITION;——信息_SCHEMA.COLUMNS 每列一行：关键字段：TABLE_SCHEMA/TABLE_NAME（归属）、COLUMN_NAME（列名）、ORDINAL_POSITION（列序号——建表顺序）、COLUMN_DEFAULT（默认值表达式）、IS_NULLABLE（YES/NO）、DATA_TYPE（类型名（标准名：varchar/integer/numeric/date/timestamp 等））、CHARACTER_MAXIMUM_LENGTH（字符类型最大长度（字符数））、CHARACTER_OCTET_LENGTH（字节数）、NUMERIC_PRECISION/NUMERIC_SCALE（数值精度/标度）、DATETIME_PRECISION（时间精度）、CHARACTER_SET_NAME/COLLATION_NAME（字符集/排序规则）、COLUMN_TYPE（MySQL 扩展：完整类型文本（varchar(100)））、COLUMN_KEY（MySQL：PRI/UNI/MUL）、EXTRA（MySQL：auto_increment/生成列表达式）、COLUMN_COMMENT（MySQL 注释）；PG 中 COLUMN_DEFAULT 返回默认值文本（含类型转换（'x'::text））、PG 无 COLUMN_KEY/EXTRA（用 pg_catalog 查）。用途：数据字典生成（列清单文档）、迁移比对（类型/可空/默认值差异（源库 vs 目标库））、自动生成 DDL/代码（ORM 实体）、权限与审计（哪些列敏感）。对比 pg_catalog：信息_SCHEMA.COLUMNS 可移植（跨库标准）、含语义字段（IS_NULLABLE 为 YES/NO）但"部分字段按标准裁剪"（PG 的 atttypmod 细节、MySQL 的 EXTRA 专有）；pg_attribute 更底层（含 attnotnull、attisdropped、attidentity、attgenerated（生成列）、列号（attnum）与存储参数（attstorage））；实践——跨库脚本用 COLUMNS、PG 专有细节（身份列/生成列/已删列）用 pg_attribute。注意：信息_SCHEMA 只显示"当前用户有权限"的列；大表/大库查询慢。

解析

答题先给按表过滤的查询模板与关键字段分组（标识（schema/table/name/position）、默认与可空、类型（DATA_TYPE/长度/精度/字符集）、MySQL 扩展（COLUMN_TYPE/KEY/EXTRA/COMMENT）），再讲用途（字典、迁移比对、代码生成）与 pg_catalog 对比（可移植 vs 底层细节：identity/generated/dropped），最后列注意（权限过滤、性能）。

示例代码

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE,
       IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'app' AND TABLE_NAME = 'orders'
ORDER BY ORDINAL_POSITION;
-- MySQL 扩展
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY, EXTRA, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='orders';
-- PG 底层（身份/生成列）
SELECT attname, attidentity, attgenerated FROM pg_attribute
WHERE attrelid = 'app.orders'::regclass AND attnum > 0 AND NOT attisdropped;

#

★★

28. 临时表与 unlogged table 的取舍？

问题

PostgreSQL 中临时表（TEMP TABLE）与 UNLOGGED TABLE 的取舍是什么？各自适用场景是什么？

考察点

两类表的机制差异（会话 vs 持久）
可见性与生命周期
场景选型

答案

机制差异：临时表（CREATE TEMP TABLE）——"会话级私有表"：存于 pg_temp schema、只对本会话可见（其他会话不可见也不可引用）、会话结束（或 ON COMMIT DROP）自动删除、数据不参与备份/复制；UNLOGGED TABLE——"持久表（结构与数据跨会话存在、所有会话可见）但不写 WAL"：崩溃后数据清空（结构保留）、不参与流复制、可 SET LOGGED 转普通表。差异维度：其一，可见性——临时表会话私有（每个会话各建各的、同名互不影响）、UNLOGGED 全局共享（一个会话建的 UNLOGGED 表其他会话可见可读写）；其二，生命周期——临时表随会话/事务（自动清理）、UNLOGGED 持久（直到 DROP；崩溃清数据）；其三，WAL/复制——两者都不写 WAL（临时表本身不写 WAL（临时表数据变更不写 WAL）、UNLOGGED 同理）——写入都快；但"不参与复制"语义不同（临时表本来就不该复制、UNLOGGED 是不复制（设计取舍））；其四，备份——pg_dump 不含临时表（默认）；UNLOGGED 表结构导出、数据不导出（恢复后空表）；其五，统计与优化——临时表有会话级统计（自动 ANALYZE）、UNLOGGED 有全局统计；其六，崩溃——临时表随会话消失（无恢复问题）、UNLOGGED 崩溃清空（需重建入口）。取舍场景：临时表——"单会话内的中间数据"（复杂查询分步、批处理会话内暂存）：隔离性好（并发会话互不干扰（各自临时表））、自动清理（无残留风险（ON COMMIT 控制））；UNLOGGED——"跨会话共享的可重建数据"（ETL 暂存表（多 worker 共享写）、缓存表（所有连接可读）、分析中间表（会话间传递））：需要"多会话可见 + 崩溃可重建"。选型规则：数据只需本会话 → 临时表；需要多会话共享且可重建 → UNLOGGED；需要多会话共享且不可丢 → 普通表（LOGGED）；写入性能诉求相同（都不写 WAL），决策关键是"可见性与生命周期"；注意：UNLOGGED 被多个会话并发写时无会话隔离（需应用控制）；临时表的 ON COMMIT 选项细化生命周期；两者都可建索引。

解析

答题先对比机制（临时表会话私有+自动清理 vs UNLOGGED 全局共享+持久但崩溃清空、都不写 WAL 快），再按可见性/生命周期/WAL 复制/备份/统计五维展开差异，最后给选型规则（本会话用临时表、跨会话可重建用 UNLOGGED、不可丢用普通表）与注意（并发、索引）。

#

★★

29. 临时表与派生表（Derived Table）的语义差异？

问题

临时表与派生表（Derived Table）的语义差异是什么？各自适用场景是什么？

考察点

派生表的语句级作用域
临时表的会话级作用域
可写性与性能差异

答案

语义差异：派生表（Derived Table）——"FROM 子句中的子查询"（SELECT ... FROM (SELECT ...) AS t）：语句级作用域（只存在于当前语句内、不可复用、不可被其他语句引用）、只读（不可 INSERT/UPDATE/DELETE——它是查询表达式不是对象）、不建索引（物化时无索引（除非优化器内部）、内联时无物化）；临时表（CREATE TEMP TABLE）——"会话级持久对象"（存于 pg_temp、可被本会话多语句引用与复用、可写（INSERT/UPDATE/DELETE）、可建索引、可控制生命周期（ON COMMIT））。差异维度：其一，作用域与复用——派生表单语句一次性、临时表跨语句（同一会话的复杂流程（多条 SQL 组装）需临时表（每条语句结果给下一条用：INSERT INTO tmp SELECT ...; SELECT * FROM tmp JOIN ...））；其二，可写性——派生表只读、临时表可写（中间结果的增量处理、分批写入）；其三，索引与统计——临时表可建索引/ANALYZE（中间结果多次查询加速）、派生表无（物化派生表无用户索引（MySQL 8.0 的物化派生表自动加隐式索引？8.0 可对物化派生表建隐式索引（合并场景）；PG 的派生表内联/物化均无用户索引）；其四，优化——派生表通常被优化器内联/合并（谓词下推、与外部统一优化：PG/MySQL derived_merge/SQL Server 内联）——"语义上等价于把子查询直接展开"（性能可能优于临时表（无物化 IO））；物化边界（聚合/DISTINCT/LIMIT）时物化（临时结构）；临时表是"显式物化"（写入 IO + 可复用）；其五，生命周期与清理——派生表语句结束即消失（无残留）、临时表需管理（会话结束/ON COMMIT/显式 DROP，残留风险）；其六，可见性——派生表本语句私有（天然隔离）、临时表会话私有（也是隔离但跨语句）；其七，CTE 的定位——CTE 是"命名派生表"（语句级、可复用可递归可物化控制（PG MATERIALIZED））——介于派生表与临时表之间（无索引、不可写）。选型：单语句内组织 → 派生表/CTE（优化器统一优化）；跨语句流程 + 需要索引/可写/复用 → 临时表；注意"派生表多次引用"用 CTE 而非重复子查询（可读性+PG 可物化）。工程建议：优先派生表/CTE（声明式、可优化）；中间结果"被多条语句消费/需索引/需写"时显式临时表；避免"大派生表重复计算"（用 CTE MATERIALIZED 或临时表））。

解析

答题先定义两者（派生表 = FROM 子查询语句级只读、临时表 = 会话级可写可索引对象），再按作用域复用、可写性、索引统计、优化（内联 vs 显式物化）、生命周期清理五维对比，最后给选型（单语句用派生表/CTE、跨语句+索引+写用临时表）与工程建议。

示例代码

-- 派生表：语句级、只读
SELECT * FROM (SELECT id, amount FROM orders WHERE status='PAID') AS d
WHERE amount > 100;
-- 临时表：跨语句、可写、可索引
CREATE TEMP TABLE tmp AS SELECT id, amount FROM orders WHERE status='PAID';
CREATE INDEX ON tmp (amount);
SELECT * FROM tmp WHERE amount > 100;
-- 下一条语句继续用
DELETE FROM tmp WHERE amount < 10;

#

★★

30. 临时表是否占用 shared_buffers？

问题

PostgreSQL 中临时表是否占用 shared_buffers？临时表的内存/缓存机制是什么？

考察点

临时表的内存位置（本地缓存 vs shared_buffers）
临时文件与临时表的关系
内存参数（temp_buffers）

答案

不占用 shared_buffers：PostgreSQL 的临时表（及临时索引）数据存放在"会话私有的本地缓存（temp_buffers）"中，而不是共享缓冲池（shared_buffers）——每个会话的 temp_buffers（默认 8MB，会话级参数）是"本地缓冲"（不属于全局 shared_buffers、不与其他会话共享、无需 buffer 锁）；读写临时表的 IO 走"本地缓冲 + 临时文件（temp file）"：数据超过 temp_buffers 容量时溢出到"临时文件"（pg_temp 目录/基目录的 pgsql_tmp），文件在会话/事务结束自动删除。机制要点：其一，隔离性——临时表缓冲不参与 shared_buffers 的竞争（大临时表不会挤占热数据缓存），但临时表读写也不受益于 shared_buffers 命中（独占本地）；其二，容量——temp_buffers 是会话参数（SET temp_buffers = 256MB）：大临时表操作（排序/哈希的临时文件另说）前调大可减少溢出；临时文件大小受 temp_file_limit 限制（默认 -1 不限（可设上限防失控））；其三，与排序/哈希的关系——查询的排序/哈希/物化也用"work_mem 内存 + 临时文件"（非 temp_buffers；work_mem 是操作内存、temp_buffers 是临时表缓存——两个不同池）；其四，性能——临时表写满 temp_buffers 后走磁盘（临时文件随机 IO）：大数据量临时表操作（join/排序多）时调大 temp_buffers 与 work_mem；其五，共享——临时表数据不进入 shared_buffers 也"不参与 WAL/复制"；其六，清理——会话结束临时表文件删除（崩溃残留由启动清理（pg_clean 临时文件（服务器启动清理 pgsql_tmp）））。对比：MySQL——临时表（内存临时表用 MEMORY/TempTable 引擎（内存池 tmp_table_size/max_heap_table_size）、超限转磁盘临时表（InnoDB on-disk temporary table（8.0）或 MyISAM 临时表（8.0 前））——MySQL 的临时表"内存优先、超限落盘"，与 PG 的 temp_buffers 机制不同但思路类似（内存+临时文件）。工程实践：大临时表/复杂排序会话调大 temp_buffers（会话级，不影响全局）；监控临时文件（pg_stat_database 的 temp_files/temp_bytes 字段：临时文件 IO 量）定位"排序/临时表溢出"；临时文件目录空间监控（临时文件占用磁盘（pg_stat_database.temp_bytes 与磁盘告警））；避免超大临时表（改用永久表/合理设计））。

解析

答题先直接回答"不占 shared_buffers"（临时表用会话级 temp_buffers 本地缓冲+临时文件），再讲机制（本地缓存隔离、超限落临时文件（temp_file_limit 限制）、与 work_mem 排序池的区别、崩溃清理），然后对比 MySQL（内存临时表超限转磁盘），最后给工程实践（调大 temp_buffers、监控 temp_files/temp_bytes、磁盘空间）。

示例代码

SHOW temp_buffers;          -- 8MB（会话级）
SET temp_buffers = '256MB';
SHOW temp_file_limit;       -- -1（不限）
-- 监控临时文件使用
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database WHERE datname = current_database();

#

★★

31. 为什么 OLTP 高并发场景不推荐使用临时表？

问题

为什么 OLTP 高并发场景不推荐使用临时表？代价与替代方案是什么？

考察点

临时表的创建/写入开销
会话私有与连接池的交互
替代方案（CTE、子查询）

答案

不推荐的原因：其一，创建开销——每个会话使用临时表都要"建表（DDL：创建 pg_temp 对象、写目录）+ 数据写入（IO）"：高并发下"频繁建/删临时表"产生元数据操作与临时文件 IO（每请求一次建删），吞吐下降；其二，会话/连接池问题——连接池（pgbouncer 等）复用时"会话状态残留"：临时表不清理则下一个请求看到旧数据（需 ON COMMIT DROP/DISCARD ALL 清理），清理本身是额外开销与出错点；其三，优化劣势——临时表"无统计或统计滞后"（PG 会话内 ANALYZE 可改善但自动性弱）、MySQL 临时表统计更弱：优化器对临时表的连接/过滤计划可能次优（全扫临时表）；且临时表是"显式物化"（无谓词下推——先算全量再过滤，而子查询/CTE 可下推（索引利用））；其四，资源——temp_buffers/临时文件占用（每个会话独立缓冲池、临时文件吃磁盘 IO 与空间（temp_bytes 大时磁盘与 IO 压力））；并发会话各自大临时表 → 磁盘临时文件风暴；其五，锁与并发——临时表操作仍有目录锁/缓冲管理开销（虽然无全局锁竞争（会话私有），但 DDL（CREATE TEMP TABLE）有元数据路径）；其六，可观测性——临时表的数据不进入常规监控（复制/备份不含），问题难排查。适用场景（何时可用）——低频批处理/报表会话（建一次用多次：复杂中间结果）、ETL 流程（会话内多步骤）、非常规查询（管理员诊断）；"OLTP 请求路径（高频小查询）"绝对避免。替代方案：其一，CTE/子查询——单语句内的中间结果（可内联、谓词下推、无需建表）：WITH cte AS (...) SELECT ...（PG 可 MATERIALIZED 控制物化）；其二，视图/物化视图——跨语句复用的"查询封装/预计算"；其三，应用侧组合——应用内存中组织中间结果（多查询结果应用层 JOIN）；其四，永久表+分区——真正需要"跨会话大中间结果"时用持久表（可索引、统计全）；其五，IN 列表/数组参数——"小集合过滤"用参数列表（= ANY($1)）替代临时表。工程结论：OLTP 高频路径"零临时表"（用 CTE/子查询/应用层）；临时表只留给"低频重活"（批量/分析会话），并配 ON COMMIT DROP/连接池清理。

解析

答题先列五类原因（建删 DDL 与 IO 开销、连接池残留、统计弱与无下推（显式物化）、temp 资源与临时文件风暴、监控盲区），再明确适用场景（低频批处理）与替代方案（CTE/子查询（下推）、视图/物化、应用层、永久表、数组参数），最后给结论（高频零临时表、低频重活用）。

#

★★

32. COMMENT ON TABLE/COLUMN 的元数据存储位置？

问题

COMMENT ON TABLE/COLUMN 的元数据存储位置是什么？如何查询与导出？

考察点

注释的存储（pg_description/pg_shdescription）
表/列注释的区分（objsubid）
查询与导出方式

答案

存储位置：PostgreSQL——COMMENT ON 语句把注释写入"pg_description"系统表（普通对象（表/列/索引/约束/函数/类型等））与"pg_shdescription"（共享对象（数据库、角色、表空间——跨库共享的全局对象））；pg_description 关键字段：objoid（对象 OID）、classoid（对象类型目录 OID（pg_class=表/视图、pg_attribute=列、pg_proc=函数、pg_type=类型、pg_constraint=约束等））、objsubid（子对象号：0=对象本身；>0=列号（列注释的 objsubid 是该列在表中的序号（attnum）））、description（注释文本）；表注释 = classoid=pg_class + objsubid=0；列注释 = classoid=pg_attribute + objsubid=attnum。查询方式：obj_description(regclass)——表注释（内部按 classoid=pg_class & objsubid=0 查）；col_description(regclass, attnum)——列注释（classoid=pg_attribute & objsubid=attnum）；psql 的 \d+ t 显示表/列注释；批量导出：JOIN pg_description + pg_class + pg_namespace（表注释清单）、JOIN pg_attribute（列注释清单（objsubid=attnum））；生成"COMMENT ON 语句"重建脚本（迁移/文档）：SELECT format('COMMENT ON TABLE %I.%I IS %L;', nspname, relname, description) FROM ...。MySQL——表/列注释存于"数据字典/表定义"（information_schema.tables.TABLE_COMMENT、information_schema.columns.COLUMN_COMMENT；8.0 数据字典存储（SHOW CREATE TABLE 输出含 COMMENT 'xxx'）；无独立"注释系统表"（跟对象定义走）；SQL Server——注释用"扩展属性"（sys.extended_properties：class/class_desc（OBJECT/COLUMN 等）、major_id（对象 id）、minor_id（列 id）、name（MS_Description）、value），sp_addextendedproperty/sp_updateextendedproperty 管理。工程：注释随 pg_dump 导出（COMMENT ON 语句）；迁移工具（Flyway）脚本中的 COMMENT ON 语句执行后写入上述表；数据目录/文档工具从系统表生成（统一口径）；注意——对象删除自动清理注释（级联）；注释不参与执行与约束（纯元数据文档））。

解析

答题先讲 PG 的存储（pg_description/pg_shdescription：objoid/classoid/objsubid/description，表=classoid pg_class+subid 0、列=pg_attribute+subid 列号），再给查询方式（obj_description/col_description/\d+ 与批量 JOIN 模板与 COMMENT 语句生成），然后讲 MySQL（TABLE_COMMENT/COLUMN_COMMENT 在信息_SCHEMA/字典）与 SQL Server（扩展属性）的对应存储，最后给工程（pg_dump 导出、文档生成、删除清理）。

示例代码

COMMENT ON TABLE app.users IS '用户表';
COMMENT ON COLUMN app.users.email IS '邮箱';
SELECT obj_description('app.users'::regclass);
SELECT col_description('app.users'::regclass, 3);
-- 批量导出表/列注释（生成 COMMENT 语句）
SELECT format('COMMENT ON TABLE %I.%I IS %L;', n.nspname, c.relname, d.description)
FROM pg_description d
JOIN pg_class c ON c.oid = d.objoid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE d.classoid = 'pg_class'::regclass AND d.objsubid = 0;
-- MySQL / SQL Server 对应
SELECT TABLE_COMMENT FROM information_schema.tables WHERE TABLE_NAME='users';
SELECT value FROM sys.extended_properties WHERE name = 'MS_Description';

#

★★

33. Oracle 的 DBA_TABLES 与 ALL_TABLES 差异？

问题

Oracle 的 DBA_TABLES 与 ALL_TABLES（及 USER_TABLES）的差异是什么？

考察点

三视图的可见范围
字段与用途差异
使用建议

答案

Oracle 数据字典视图按"可见范围"分三级（表/视图族通用模式）：USER_TABLES——"当前用户拥有的表"（USER_* 前缀：只含"所有者=当前用户"的对象，无需权限即可查（自己的对象））；ALL_TABLES——"当前用户可访问的表"（自己拥有的 + 被授权访问（SELECT 权限）的其他用户的表）；DBA_TABLES——"数据库中所有表"（需 DBA 权限（SELECT ANY DICTIONARY 或 DBA 角色））；管理员视角：全库表（含系统 schema（SYS/SYSTEM 的表））。字段差异：三者"结构（列）基本一致"（OWNER、TABLE_NAME、TABLESPACE_NAME、NUM_ROWS（统计行数）、BLOCKS、AVG_ROW_LEN、LAST_ANALYZED、STATUS 等），差异只在"返回的行集范围"（OWNER 列区分归属：USER_TABLES 无 OWNER 列（都是自己）、ALL/DBA 有 OWNER）。其他族：USER/ALL/DBA_TAB_COLUMNS（列）、USER/ALL/DBA_INDEXES、USER/ALL/DBA_CONSTRAINTS、USER/ALL/DBA_SEQUENCES 等（同一模式）。用途：USER_——开发自查（自己的表统计）；ALL_——应用/普通用户查"可访问对象"（权限内元数据）；DBA_——管理员全库审计（所有表清单、统计、容量（NUM_ROWS/BLOCKS）、表空间分布）；注意：其一，权限——DBA_ 需 DBA 权限（普通用户查询报 ORA-00942（表或视图不存在）——"不存在"而非"无权限"（视图按权限隐藏））；其二，统计字段——NUM_ROWS 来自"最近 ANALYZE/自动统计"（估算/快照，非实时精确行数（与 MySQL TABLE_ROWS 类似概念））；其三，性能——DBA_* 全库查询大（对象多时慢，配合过滤（OWNER/TABLE_NAME））；其四，与信息_SCHEMA 对比——Oracle 无信息_SCHEMA（用 ALL_/DBA_ 体系；Oracle 也提供 ALL_TABLES 等作为"字典视图"（非标准）；跨库脚本（PG/MySQL/SQL Server）用信息_SCHEMA、Oracle 用 ALL_* 适配）。工程实践：管理员脚本用 DBA_TABLES（过滤 OWNER）；应用/工具用 ALL_TABLES（权限内）；"找表"用 ALL_TABLES WHERE OWNER=... AND TABLE_NAME LIKE ...；容量统计（NUM_ROWS×AVG_ROW_LEN）。

解析

答题先讲三级视图的可见范围（USER 自有/ALL 可访问/DBA 全库需权限）与字段一致性（OWNER/NUM_ROWS/BLOCKS 等，USER 无 OWNER 列），再讲同族视图（TAB_COLUMNS/INDEXES/CONSTRAINTS）与用途（开发自查/应用权限内/管理员全库审计），最后列注意（DBA 权限报"不存在"、NUM_ROWS 是统计快照、性能与过滤、与信息_SCHEMA 的替代关系）。

示例代码

-- 全库表（管理员）
SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM DBA_TABLES WHERE OWNER = 'APP';
-- 可访问的表（应用/工具）
SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'APP';
-- 自己的表
SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES;
-- 表统计（行数估算来自 ANALYZE）
SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'ORDERS';

#

★★

34. 为什么 information_schema 查询在大库中很慢，如何用 pg_catalog/sys 目录替代

问题

为什么 information_schema 查询在大库中很慢？如何用 pg_catalog/sys 目录替代？

考察点

information_schema 慢的原因
pg_catalog 替代写法
实践建议

答案

慢的原因：information_schema 是"视图"（PG 中实现为"基于系统目录的多层嵌套视图"）：每个视图底层展开为"多次子查询 + JOIN 系统表 + 权限过滤（has_table_privilege 等函数逐对象判断）+ 类型/默认值的格式化"——大库（表/列/约束数以万计）时：其一，权限过滤——每个对象逐行调用权限检查函数（has__privilege），开销大；其二，多层视图展开——查询计划复杂（多次扫描 pg_class/pg_attribute/pg_namespace/pg_type 等目录并 JOIN），且目录表本身行数大；其三，格式化——COLUMN_DEFAULT/DATA_TYPE 的表达式重建（pg_get_expr/format_type 等函数逐行调用）；其四，无索引利用——视图查询模式固定（可能全扫目录表）；实际观察：大库 SELECT COUNT() FROM information_schema.columns 可能秒级-分钟级（vs 直接查 pg_attribute 毫秒级）。替代（pg_catalog 直接查询）：表清单——pg_class JOIN pg_namespace（relkind 过滤）；列清单——pg_attribute JOIN pg_type（format_type(atttypid, atttypmod)、attnum>0、NOT attisdropped）；约束——pg_constraint（contype/conkey + pg_get_constraintdef）；索引——pg_indexes/pg_index（indexdef）；函数——pg_proc；权限过滤自行控制（需要时自己加权限判断）；查询模式：直接用 OID JOIN（pg_class.oid = pg_attribute.attrelid）、::regclass 转名、format_type 格式化类型；性能：底层表直查（无权限逐行函数、无多层展开）快 1-2 个数量级。SQL Server 同理——信息_SCHEMA 慢、用 sys 目录（sys.tables/columns/indexes——官方推荐）；MySQL——信息_SCHEMA 部分查询慢（8.0 有优化（字典表缓存），但大库 TABLES/COLUMNS 仍可能慢——SHOW 命令与直接字典查询替代（performance_schema 的表更底层））。实践建议：其一，工具/监控脚本用 pg_catalog/sys 直查（固定模板：JOIN pg_namespace/pg_type、过滤 relkind/attnum、format_type）；其二，交互/可移植场景保留信息_SCHEMA（跨库脚本、一次性查询）；其三，大库信息_SCHEMA 查询加过滤条件（TABLE_SCHEMA/TABLE_NAME 精确过滤——视图的过滤条件可以下推（减少处理对象））；其四，避免"SELECT * FROM information_schema.xxx 全表"（大库不可接受）；其五，缓存元数据查询结果（监控工具缓存、定期刷新）。

解析

答题先讲慢的四点原因（权限逐行检查、多层视图展开、格式化函数逐行调用、无索引利用），再给 pg_catalog 替代的写法（pg_class/pg_attribute/pg_constraint/pg_indexes JOIN 模板、format_type/regclass、OID JOIN）与性能对比（快 1-2 数量级），然后提 SQL Server sys/MySQL 的同类问题，最后给实践建议（脚本用目录直查、交互可移植用信息_SCHEMA、加过滤、缓存）。

示例代码

-- 慢：大库全表（信息_SCHEMA 视图展开+权限检查）
SELECT COUNT(*) FROM information_schema.columns;   -- 大库秒级
-- 快：pg_catalog 直查
SELECT COUNT(*) FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum > 0 AND NOT a.attisdropped AND n.nspname NOT IN ('pg_catalog','information_schema');
-- 列表：format_type 格式化
SELECT c.relname, a.attname, format_type(a.atttypid, a.atttypmod)
FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid
WHERE c.relname = 'orders' AND a.attnum > 0 AND NOT a.attisdropped;

#

★

35. SQL Server 中 sys.dm_db_partition_stats 的用途？

问题

SQL Server 中 sys.dm_db_partition_stats 的用途是什么？关键字段与使用场景是什么？

考察点

DMV 的定位（分区/表统计）
关键字段（行数/页数）
与 sp_spaceused 的对比

答案

sys.dm_db_partition_stats——"数据库分区级统计的 DMV（动态管理视图）"：为"每个表/索引的每个分区"返回一行统计（当前数据库）：关键字段：object_id（对象）、index_id（索引（0=堆、1=聚集索引、>1 非聚集））、partition_number（分区号）、row_count（该分区的行数——"已提交行的近似计数（含未清理的 ghost 行？row_count 是"表/索引分区中的行数（近似）"——不精确（删除未回收行计入？row_count 统计"逻辑行数"（含未清理版本？实际接近当前行数但非事务精确））；in_row_data_page_count（行内数据页数）、in_row_used_page_count（行内已用页）、in_row_reserved_page_count（行内保留页）、lob_used_page_count/reserved（大对象（text/image/varchar(max)）页）、row_overflow_used_page_count/reserved（行溢出页）、used_page_count/reserved_page_count（合计）。用途：其一，容量分析——按分区/索引查看行数与页数（数据大小估算（页数×8KB））：定位大表/大索引/分区分布（哪个分区的数据量最大（时间分区表的月度增长））；其二，行数统计——近似行数（比 COUNT() 快（不扫描））：分区级行数（如按分区查最近月份行数）、表级汇总（SUM(row_count) GROUP BY object_id）；其三，碎片与空间管理——页数对比（reserved vs used：预留但未用的空间）、配合 sys.dm_db_index_physical_stats（碎片率）规划 REBUILD/REORGANIZE；其四，分区维护——按 partition_number 评估归档/切换（SWITCH PARTITION 前查看分区行数）；其五，与 sp_spaceused 对比——sp_spaceused 't' 给表总大小（汇总同一数据）；dm_db_partition_stats 更细粒度（按索引/分区拆分、可编程（JOIN sys.tables/sys.indexes 获取名称））。注意：其一，row_count 是"近似值"（分区统计维护在元数据（非精确事务计数（删除的行在 ghost cleanup 前仍计入？row_count 不随每次删除立即精确——它是"统计信息"（近似，明确时用 COUNT()））；其二，范围——当前数据库（不能跨库查（用 sp_MSforeachdb 或各库查询））；其三，权限——查看元数据权限（VIEW DEFINITION 或更宽）；其四，与 sys.dm_db_index_physical_stats 的区别——physical_stats 是"物理碎片/页密度"（扫描实际结构、慢）、partition_stats 是"统计页数/行数"（元数据、快）；两者配合（先 stats 看规模、再 physical 看碎片）。工程实践：容量监控脚本（定期收集 row_count/used_page_count 入库）、分区表维护（按分区行数判断归档）、索引管理（大索引识别（非聚集索引页数））））））。

解析

答题先定义 DMV（分区级统计：每表每索引每分区一行）与关键字段（row_count、in_row/lob/row_overflow 的 used/reserved 页数、object_id/index_id/partition_number），再给四类用途（容量与分区分布、近似行数、空间碎片配合 physical_stats、分区切换评估），然后对比 sp_spaceused（粒度差异）与注意（row_count 近似、当前库范围、权限），最后给工程实践。

示例代码

-- 大表/大索引（含名称）
SELECT t.name AS tbl, i.name AS idx, ps.partition_number,
       ps.row_count, ps.used_page_count * 8 / 1024 AS used_mb
FROM sys.dm_db_partition_stats ps
JOIN sys.tables t ON t.object_id = ps.object_id
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
ORDER BY used_page_count DESC;
-- 分区行数（时间分区表）
SELECT partition_number, row_count FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.events') AND index_id IN (0,1);
-- 表近似行数（不扫描）
SELECT SUM(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.orders') AND index_id IN (0,1);

#

★

36. pg_class 与 pg_namespace 的关系？

问题

pg_class 与 pg_namespace 的关系是什么？如何关联查询？

考察点

两表的字段与关系（OID 外键）
关联查询模板
相关目录（pg_type 等）

答案

关系：pg_namespace——"schema（命名空间）目录"：oid（schema 的 OID）、nspname（schema 名）、nspowner（属主）、nspacl（权限）；pg_class——"关系目录"：每个表/索引/视图/序列/物化视图一行：relname（对象名）、relnamespace（所属 schema 的 OID——外键指向 pg_namespace.oid）、relkind（类型）、relowner 等——即"pg_class 通过 relnamespace → pg_namespace.oid"建立"对象 → 所属 schema"的关联（多对一：一个 schema 含多个对象）；查询 schema 名时必须 JOIN（pg_class 存 OID 不存名）。关联查询模板：SELECT n.nspname AS schema, c.relname AS obj, c.relkind FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'app';——按 schema 列出对象（表/索引/视图/序列）；或按对象查 schema：SELECT n.nspname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname='users';（注意同名对象跨 schema（多个 schema 各有 users）——按名查需带 schema 过滤或用 'app.users'::regclass 精确转 OID）。相关目录关系（OID 外键模式）：pg_class.relnamespace→pg_namespace.oid、pg_class.reltype→pg_type.oid（表对应的复合类型）、pg_class.relowner→pg_authid.oid（属主（用 pg_get_userbyid 取名字））、pg_attribute.attrelid→pg_class.oid（列→表）、pg_attribute.atttypid→pg_type.oid（列→类型）、pg_constraint.conrelid→pg_class.oid、pg_type.typnamespace→pg_namespace.oid 等——"系统目录普遍用 OID 关联、查询时 JOIN 或 ::regclass/::regtype 转换（名称↔OID 的快捷方式）"。实践：脚本固定模板"pg_class JOIN pg_namespace"（表/视图/序列按 schema 过滤（relkind 区分））；schema 相关操作（CREATE SCHEMA 后对象归入（relnamespace 指向新 schema OID））、对象迁移（ALTER TABLE SET SCHEMA——更新 relnamespace）；查询"每个 schema 的对象数/大小"（GROUP BY nspname + pg_relation_size）；注意：pg_class 包含系统 schema 对象（pg_catalog 的表（pg_class 本身也是 pg_class 中的一行（自引用））——过滤 nspname NOT IN ('pg_catalog','information_schema','pg_toast')（各模板）；物化视图/分区表也在 pg_class（relkind 'm'/'p'））。

解析

答题先讲两表结构（pg_namespace 的 oid/nspname、pg_class 的 relnamespace 外键）与"对象→schema"的多对一关系，再给两类关联查询模板（按 schema 列对象、按对象查 schema）与同名对象的处理（regclass 精确转换），然后扩展相关目录的 OID 外键模式（reltype/relowner/attrelid/atttypid），最后给实践（固定模板、系统 schema 过滤、SET SCHEMA 语义）。

示例代码

-- 按 schema 列对象
SELECT n.nspname, c.relname, c.relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app';
-- 按对象查所属 schema
SELECT n.nspname FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'users';
-- regclass 精确（防同名歧义）
SELECT 'app.users'::regclass;
-- 每个 schema 的表数量
SELECT n.nspname, COUNT(*)
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' GROUP BY n.nspname;

#

★

37. sys.databases 与 information_schema.schemata 的差异？

问题

SQL Server 的 sys.databases 与 information_schema.schemata 的差异是什么？

考察点

两个视图的对象层级（数据库 vs schema）
字段与用途差异
跨库脚本的对应

答案

对象层级不同：sys.databases——"实例中的数据库清单"（每个数据库一行）：关键字段：database_id、name（库名）、create_date、state（0=ONLINE 等）、recovery_model_desc（恢复模式：FULL/SIMPLE/BULK_LOGGED）、compatibility_level（兼容级别）、collation_name（排序规则）、user_access_desc、is_read_only、log_reuse_wait_desc 等——数据库级管理信息（容量/恢复/状态）；information_schema.schemata——"当前数据库中的 schema 清单"（每个 schema 一行）：标准视图：SCHEMA_NAME（schema 名）、SCHEMA_OWNER（属主）、DEFAULT_CHARACTER_SET_CATALOG/SCHEMA_NAME（默认字符集信息（SQL Server 中多为 NULL））——schema 级（库内的命名空间）。差异本质：sys.databases 是"实例层（库）"、schemata 是"库内层（schema）"——对应关系：SQL Server 三级命名空间"实例→数据库（sys.databases）→ schema（schemata）→ 对象（sys.objects）"；两者无直接 JOIN 需求（层级不同：database 是 schemata 的上级容器（schemata 只显示"当前数据库"的 schema——SQL Server 中 information_schema.schemata 仅当前库；要列全部库的 schema 需各库查询或 sys.databases 循环）。字段与用途：sys.databases——DBA 用（恢复模式、状态、兼容级别、只读、数据库大小（配合 sys.master_files））；schemata——跨库可移植的"库内 schema 清单"（用户/应用查"有哪些 schema"、权限归属（SCHEMA_OWNER））；MySQL 对应——MySQL 中"数据库"即命名空间：SHOW DATABASES/information_schema.schemata 列出"库"（MySQL 的 schemata 对应"数据库清单"（每行一个库）——与 SQL Server 的 sys.databases 类似（但 MySQL 的库=SQL Server 的 schema 层级）；PostgreSQL——information_schema.schemata 列"库内 schema"（与 SQL Server 相同语义）、库清单用 pg_database。注意：其一，schemata 的 DEFAULT_CHARACTER_SET_* 字段在 SQL Server 中基本为 NULL（SQL Server 的字符集在库级（collation_name 在 sys.databases））；其二，权限——sys.databases 需 VIEW ANY DATABASE（或属主）才能看全部库（普通用户只能看到自己有权限的库）；schemata 显示"当前用户可访问的 schema"（权限过滤）；其三，跨库工具——"列出所有数据库"用 sys.databases、"列出某库的 schema"用 information_schema.schemata（或 sys.schemas（SQL Server 原生目录：sys.schemas 的 schema_id/name/principal_id——比信息_SCHEMA 快））。工程实践：DBA 巡检（恢复模式/状态）用 sys.databases；应用/脚本列 schema 用 information_schema.schemata 或 sys.schemas；迁移工具按"实例→库→schema→对象"层级组织））。

解析

答题先讲对象层级差异（sys.databases 实例层数据库清单 vs schemata 库内 schema 清单）与关键字段（库的恢复模式/状态 vs schema 名/属主），再讲"无直接 JOIN"（层级不同、schemata 只当前库）与 MySQL/PG 的对应（MySQL 的 schemata=库清单、PG 的 schemata=库内 schema），最后列注意（权限、字符集字段 NULL、sys.schemas 替代）与工程实践。

示例代码

-- 数据库清单（实例层）
SELECT name, state_desc, recovery_model_desc, compatibility_level
FROM sys.databases;
-- schema 清单（当前库内）
SELECT SCHEMA_NAME, SCHEMA_OWNER FROM information_schema.schemata;
-- SQL Server 原生目录（更快）
SELECT s.name, p.name AS owner FROM sys.schemas s
JOIN sys.database_principals p ON p.principal_id = s.principal_id;

#

★

38. CREATE TEMP TABLE t (id int) 的基本语法？

问题

CREATE TEMP TABLE t (id int) 的基本语法是什么？与普通建表的差异是什么？

考察点

TEMP 关键字与语法
临时表的默认行为
各库差异

答案

基本语法：CREATE TEMP TABLE t (id INT);（或 TEMPORARY 全称：CREATE TEMPORARY TABLE t (id INT);）——创建"当前会话私有的临时表"：列定义与普通表一致（类型/约束/默认值，可复合：CREATE TEMP TABLE t (id INT PRIMARY KEY, name TEXT DEFAULT 'x');），可加 ON COMMIT 选项、可 LIKE 复制结构（CREATE TEMP TABLE t2 (LIKE t1)）、可 CTAS（CREATE TEMP TABLE t AS SELECT ...）。默认行为：其一，作用域——本会话可见（其他会话不可见、不可引用（同名永久表被遮蔽））；其二，生命周期——会话结束自动删除（DROP TABLE 可选（显式删除））；不写 ON COMMIT 时数据跨事务保留（PRESERVE ROWS 默认）；其三，存储——pg_temp schema（会话级，目录名 pg_temp_N）；其四，索引/约束——可建；其五，WAL/复制/备份——不写 WAL、不参与复制与备份（临时数据无持久需求）；其六，锁——表级操作会话内（无跨会话竞争）。与普通建表差异总结：普通 CREATE TABLE t（永久表：public/业务 schema、全库可见、持久、参与复制备份、需权限）；TEMP 版——私有、临时、无 WAL、自动清理。各库差异：MySQL——CREATE TEMPORARY TABLE t (id INT)（TEMPORARY 关键字；会话级、连接断开自动删除、存储于内存/磁盘临时引擎（默认 InnoDB 8.0）；同名永久表被临时表遮蔽；无 ON COMMIT 选项；查看 SHOW CREATE TABLE 显示 TEMPORARY？MySQL 的临时表可 SHOW CREATE）；SQL Server——CREATE TABLE #t (id INT)（# 前缀本地临时表（会话级、存 tempdb）、## 全局临时表（跨会话）；无 TEMP 关键字——用 # 命名约定；临时表在 tempdb 中（DROP 或会话结束清理；事务回滚时事务内创建的临时表被删除）；Oracle——CREATE GLOBAL TEMPORARY TABLE t (id NUMBER) ON COMMIT PRESERVE/DELETE ROWS（全局临时表：结构全局定义、数据会话/事务级（多会话共享结构、数据私有）；与 PG 的"会话级建表"不同（Oracle 的结构是持久的、数据临时）；PostgreSQL 的 TEMP 表"结构+数据都会话私有"。注意：CREATE TEMP TABLE 与"普通表 + 会话过滤"（用 user_id 列过滤）是不同方案（临时表隔离更强但每次建）；pg_temp 的 DDL 不触发事件触发器；权限——建临时表需 TEMP 权限（PG 默认允许所有用户（CREATE TEMP 权限默认 GRANT 给 PUBLIC？PG 中 CREATE TEMP 权限默认对 PUBLIC 授权（连接用户可建临时表）——8.0 仍默认））））。

解析

答题先给基本语法（TEMP/TEMPORARY + 列定义 + ON COMMIT/LIKE/CTAS 变体）与默认行为（会话私有、自动清理、pg_temp、无 WAL），再对比普通表差异（可见性/生命周期/持久性），然后列各库语法差异（MySQL TEMPORARY、SQL Server #t/##t、Oracle 全局临时表 ON COMMIT），最后给注意（遮蔽、TEMP 权限、事件触发器不触发）。

示例代码

-- PostgreSQL / MySQL
CREATE TEMP TABLE t (id INT);
CREATE TEMPORARY TABLE t2 (id INT PRIMARY KEY, name TEXT);
CREATE TEMP TABLE t3 AS SELECT * FROM orders WHERE status='PAID';
-- SQL Server
CREATE TABLE #t (id INT);
-- Oracle（全局临时表：结构持久、数据临时）
CREATE GLOBAL TEMPORARY TABLE t (id NUMBER) ON COMMIT DELETE ROWS;

#

★

39. ON COMMIT 子句的三个选项？

问题

临时表的 ON COMMIT 子句有三个选项？各自的语义与适用场景是什么？

考察点

三个选项的语义（DELETE ROWS/PRESERVE ROWS/DROP）
数据与结构的生命周期
场景选择

答案

三个选项（CREATE TEMP TABLE ... ON COMMIT ...，仅 PostgreSQL）：DELETE ROWS——事务提交时"清空数据"（结构保留、表继续存在）：后续事务可继续使用（空表）；场景：每事务一批的中间表（同一会话多事务各自独立填数据）；PRESERVE ROWS（默认）——提交时"保留数据"：数据跨事务累积（会话内持续）；场景：会话级暂存（多次事务收集/查询同一批数据）；DROP——提交时"删除表"（结构+数据都消失）：后续事务需重建；场景：单事务内的一次性中间表（用完即自动清理，防残留）。语义差异本质——"数据/结构的生命周期"与事务对齐：DELETE ROWS = 数据按事务清、结构按会话留；PRESERVE ROWS = 数据+结构按会话留（默认）；DROP = 结构按事务留（提交即删）。行为细节：其一，触发时机——"事务提交时"生效（回滚不触发：回滚时表维持事务开始时的状态）；其二，autocommit——单语句自动提交时"每条语句后"即触发（DELETE ROWS 的表每条语句后清空——需显式事务才实用；DROP 的表单语句后即删）；其三，与索引——DELETE ROWS 清数据保留索引结构（TRUNCATE 式）、DROP 连索引一起删；其四，会话结束——无论选项，断连都删除（pg_temp 清理）；其五，嵌套事务/保存点——ON COMMIT 只在外层事务提交时生效（子事务提交不触发）；其六，与事务内 DML——提交前数据正常可见可查（本会话）。场景选择建议：事务内一次性计算（复杂查询分步：先插中间结果、再查询）用 ON COMMIT DROP（自动清理、无残留——批处理流程的安全选项）；"每事务一批"（如多批导入分别处理）用 DELETE ROWS；"会话级工作集"（多条语句共享的临时汇总）用 PRESERVE ROWS（默认）。注意：MySQL 无 ON COMMIT（临时表会话级，无事务选项——数据在事务内可回滚（InnoDB 临时表）但表生命周期与会话绑定）；SQL Server 的 #local 临时表无 ON COMMIT（事务内创建的表在"事务回滚时被删除"（DDL 事务性）——与 ON COMMIT DROP 的"提交删除"方向相反（SQL Server 是"回滚删除"、PG 的 DROP 是"提交删除"））；Oracle 的全局临时表用 ON COMMIT PRESERVE/DELETE ROWS（两选项：对应 PG 的 PRESERVE/DELETE（无 DROP——结构是持久的））。

解析

答题先逐个讲三个选项的语义（提交清数据留结构/提交保留/提交删表）与场景（每事务一批/会话累积/一次性），再讲行为细节（提交触发、autocommit 影响、索引处理、断连清理、子事务），最后对比各库（MySQL 无 ON COMMIT、SQL Server 回滚删表、Oracle 两选项）与场景选择建议。

示例代码

CREATE TEMP TABLE t1 (id INT) ON COMMIT DELETE ROWS;    -- 提交清数据
CREATE TEMP TABLE t2 (id INT) ON COMMIT PRESERVE ROWS;  -- 默认，提交保留
CREATE TEMP TABLE t3 (id INT) ON COMMIT DROP;           -- 提交删表
BEGIN;
INSERT INTO t3 VALUES (1);
COMMIT;   -- t3 已删除，后续事务需重建

#

★

40. 元数据并发修改（DDL 锁）的影响？

问题

元数据并发修改（DDL 锁）的影响是什么？如何管理与规避？

考察点

DDL 锁的机制（MDL/Sch-M/AccessExclusive）
对并发 DML 与查询的影响
管理与规避实践

答案

DDL 锁机制：数据库对"元数据修改"加排他锁（PostgreSQL——ALTER/DROP 等 DDL 持 ACCESS EXCLUSIVE 表锁（阻塞读写）；MySQL——MDL 排他锁（DDL 需 EXCLUSIVE MDL：阻塞所有其他语句（含 SELECT））；SQL Server——Sch-M（schema modification）锁（DDL 排他、阻塞读写））；"元数据并发修改"的影响：其一，阻塞——DDL 等待"已有查询/事务结束"（PG 的 ACCESS EXCLUSIVE 等待已开始查询、MySQL 的 MDL 等待共享 MDL 释放（长事务卡 DDL）、SQL Server 的 Sch-M 等待 Sch-S 释放）；DDL 一旦获得锁，后续所有 DML/查询排队（MySQL 的 MDL 写优先放大：DDL 排队期间新查询也排队（写优先把读堵住）——"一个 DDL + 一个长事务 = 应用雪崩"）；其二，计划失效——DDL 后已缓存计划失效（依赖对象变化：PG 重新规划、SQL Server 计划重新编译、MySQL 8.0 的 prepared statement 重新解析）——瞬间性能波动（新计划编译）；其三，并发 DDL——同一对象的并发 DDL 串行化（第二个 DDL 等待第一个完成）或报错（MySQL 8.0 对同一表的并发 DDL 有保护（一个执行、其他等待/报错））；PG 中两个 ALTER 同一表串行（锁等待）；其四，元数据不一致风险——DDL 与"正在执行的语句"的元数据快照冲突（MVCC/MDL 保证"语句级一致"，但长语句与 DDL 竞争是性能/可用性问题）；其五，复制影响——DDL 在从库执行持锁：从库长查询阻塞复制（主从延迟放大）；管理与规避：其一，变更窗口——低峰执行 DDL；其二，先查后改——执行前检查长事务/长查询（pg_stat_activity（state 与 duration）、information_schema.innodb_trx、sys.dm_exec_requests），必要时终止（pg_terminate_backend/KILL）；其三，锁超时——设置锁等待超时（MySQL lock_wait_timeout、PG lock_timeout、SQL Server LOCK_TIMEOUT）防无限等待；其四，在线 DDL——优先非阻塞算法（MySQL INPLACE/INSTANT、PG 的 CONCURRENTLY/ADD COLUMN（快速默认值）、SQL Server ONLINE 选项）减少锁持有；其五，拆分变更——大表 ALTER 拆小（加列分批（PG 多次 ADD COLUMN）、用 pt-osc/gh-ost）；其六，监控——锁等待视图（pg_locks/pg_stat_activity、performance_schema.metadata_locks、sys.dm_tran_locks）告警；其七，DDL 幂等与审计（迁移工具统一管理）。总结：DDL 锁影响 = "等待 + 阻塞 + 计划失效"三方面；核心规避 = 低峰 + 排长事务 + 在线算法 + 锁超时 + 监控。

解析

答题先讲 DDL 锁机制（PG ACCESS EXCLUSIVE/MySQL MDL/SQL Server Sch-M 的排他性与阻塞面），再列四类影响（长事务卡 DDL→雪崩（写优先）、计划失效与编译波动、并发 DDL 串行化、从库复制延迟），最后给管理与规避清单（低峰、排长事务、锁超时、在线 DDL、拆分、监控）。

示例代码

-- 排长事务/长查询（PG）
SELECT pid, state, now() - xact_start AS age FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY age DESC;
SELECT pg_terminate_backend(<pid>);
-- MySQL：查 MDL 等待与长事务
SELECT * FROM performance_schema.metadata_locks;
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;
-- 锁超时
SET lock_timeout = '10s';            -- PG
SET SESSION lock_wait_timeout = 60;  -- MySQL

#

★

41. 元数据查询是否走 MVCC？

问题

元数据查询是否走 MVCC？数据库的元数据读取与并发机制是什么？

考察点

系统目录的 MVCC 特性
元数据快照与一致性
各库实现差异

答案

是（PostgreSQL）：系统目录（pg_class/pg_attribute/pg_namespace 等）本身是"普通表"（用堆与 MVCC 存储）——元数据查询（SELECT 目录表、信息_SCHEMA 视图、psql \d）走标准的快照隔离：每个事务/语句看到"自己快照下的元数据版本"（DDL 提交前，并发查询看不到新结构；DDL 与查询由锁（ACCESS EXCLUSIVE）串行化冲突面）；因此"元数据查询走 MVCC"在 PG 中是字面成立的（目录行有版本链、可回滚、事务性 DDL（DDL 在事务内可回滚（目录变更回滚）））。MySQL：8.0 之前——元数据用"MDL 锁 + 非 MVCC 字典（frm 文件/内存字典）"：元数据读取受 MDL 保护（共享锁，语句级一致）、字典本身非 MVCC（8.0 前）；8.0——"数据字典（Data Dictionary）"表（mysql 库中）基于 InnoDB（支持 MVCC 与事务（原子 DDL：DDL 在事务内完成/回滚，字典变更与表变更原子））——8.0 的字典查询（信息_SCHEMA 底层）走 InnoDB（MVCC 化）；但"查询执行中的元数据一致性"仍由 MDL 保证（语句持有共享 MDL 期间结构不变——MVCC 管字典行版本、MDL 管"语句级锁定"）。SQL Server：元数据在系统目录（系统基表）中，查询受 Sch-S 锁保护（语句级稳定）；目录本身"无 MVCC"（SQL Server 的元数据一致性靠锁与快照（语句级）——快照隔离（RCSI）下语句级一致性由版本化保证数据页、元数据用 Sch-S）。共性理解：其一，"元数据一致性"的两个层次——"事务/语句级快照"（PG 目录 MVCC、MySQL 8.0 字典 MVCC、SQL Server 语句级锁）保证"查询看到的元数据一致（开始时的结构）"；"锁"（ACCESS EXCLUSIVE/MDL/Sch-M）保证"DDL 与活动语句的互斥"（长查询阻塞 DDL 的根源）；其二，PG 的特殊性——目录 MVCC 使"DDL 事务性"（回滚 DDL 恢复旧结构）、"长查询与 DDL 竞争"通过锁（非 MVCC 直接并发：DDL 仍需等长查询结束（ACCESS EXCLUSIVE）——MVCC 不解决 DDL 与长查询的互斥（表锁仍要等）；MVCC 解决的是"回滚一致性"与"备份/逻辑复制中元数据版本一致性"（pg_dump 一致性快照含目录）；其三，信息_SCHEMA 视图查询同样走上述机制（PG 中视图查询在快照内读目录表）。实践：诊断"元数据查询慢"考虑快照/锁竞争（长事务持有目录锁？目录锁较少（DDL 持表锁不阻塞目录读（PG 中读目录不需 ACCESS EXCLUSIVE——DDL 锁在表上、目录读走 MVCC 快照——因此 PG 中"查询信息_SCHEMA 不被 DDL 阻塞"（快照隔离），MySQL 中信息_SCHEMA 查询需共享 MDL？MySQL 8.0 中信息_SCHEMA 查询不获取表的 MDL（直接读字典——快照）；理解差异有助于定位"元数据查询卡住"的问题（SQL Server 的 Sch-S 与 DDL 竞争）））））。

解析

答题先回答"是（PG 目录表本身是 MVCC 表）"，再分库讲机制（PG 目录 MVCC+事务性 DDL、MySQL 8.0 字典 InnoDB+MDL、SQL Server Sch-S），然后讲两个层次（快照一致性 vs 锁互斥：MVCC 管回滚/快照、锁管 DDL 与语句互斥），最后给实践（元数据查询与 DDL 的竞争差异、诊断）。

示例代码

-- PG：目录是 MVCC 表（可事务性回滚 DDL）
BEGIN;
ALTER TABLE t ADD COLUMN c INT;
ROLLBACK;   -- 目录变更回滚，t 结构恢复
-- 元数据快照一致性：长事务中的元数据查询看到"事务开始时"的结构
SELECT * FROM information_schema.columns WHERE table_name='t';

#

★

42. 临时表与会话/事务的生命周期绑定（ON COMMIT 与断连清理）

问题

临时表与会话/事务的生命周期绑定是什么？ON COMMIT 与断连清理的机制是什么？

考察点

临时表的会话级生命周期
ON COMMIT 的事务级控制
断连清理机制

答案

生命周期绑定分两层：会话级——临时表"绑定会话"：创建于 pg_temp（PG）/会话内存（MySQL）/#tempdb（SQL Server），会话断开（连接结束）时"自动删除"（无论 ON COMMIT 设置）：断连清理机制——PG：会话结束（正常/异常（崩溃（后端进程退出）））时临时 schema（pg_temp_N）及其对象被删除（后端启动清理（临时文件与目录在崩溃后由服务器启动时清理））；MySQL：连接断开（含异常断开）自动 DROP TEMPORARY TABLE（服务器清理会话临时对象）；SQL Server：会话结束自动删除 #local 临时表（tempdb 中的对象随会话清理（进程退出清理））；事务级——ON COMMIT（PG）控制"数据/表在事务提交时的去留"（DELETE ROWS/PRESERVE ROWS/DROP）：把生命周期从"会话"细化为"事务"（提交清数据/提交保留/提交删表）；MySQL 无 ON COMMIT（临时表生命周期纯会话级；事务内 DML 可回滚（InnoDB 临时表数据按事务版本）但表本身不随事务）；SQL Server：#local 临时表"会话级"，但"事务内创建的临时表在事务回滚时被删除"（SQL Server 的 DDL 事务性：CREATE TABLE #t 在事务内、回滚则表删除——注意方向（回滚删除 vs PG 的 ON COMMIT DROP（提交删除）））；Oracle：全局临时表（结构持久、数据按 ON COMMIT PRESERVE/DELETE ROWS（事务/会话级数据）——断连清理数据（会话数据删除））。机制总结：其一，两级绑定——"会话"是默认/最终边界（断连必清理）、"事务"是可选细化（ON COMMIT/回滚行为）；其二，清理时机——断连（正常/异常）、事务提交（ON COMMIT DELETE/DROP）、事务回滚（SQL Server 的建表回滚）、崩溃（服务器启动清理残留）；其三，连接池场景——会话复用时"临时表残留"是实际问题（连接池把断连推迟：池中连接不真正断开 → 临时表不自动清理 → 下一个请求看到旧数据）：需应用主动清理（ON COMMIT DROP/DISCARD ALL/显式 DROP）；其四，资源回收——断连清理释放 pg_temp 对象与临时文件（temp 文件随事务/语句结束释放（排序/哈希临时文件）、临时表数据文件随会话）；其五，监控——pg_stat_activity 的临时表使用、tempdb（SQL Server）空间监控。工程实践：连接池配置"归还清理"（pgbouncer 的 server_reset_query=DISCARD ALL）；临时表生命周期显式声明（ON COMMIT 按场景）；用完即 DROP（长会话中临时表显式清理防累积）；监控 tempdb/pg_temp 空间。

解析

答题先讲两级绑定（会话级默认边界：断连自动清理（PG/MySQL/SQL Server 的机制）与事务级细化（ON COMMIT 三选项）），再讲清理时机（断连（正常/异常）、提交（DELETE/DROP）、回滚（SQL Server 建表回滚删表）、崩溃（启动清理）），然后重点讲连接池残留问题与对策（DISCARD ALL/ON COMMIT DROP），最后给资源回收与监控建议。

示例代码

-- 会话级 + 事务级绑定
CREATE TEMP TABLE t (id INT) ON COMMIT DROP;   -- 提交删表（防残留）
-- 连接池归还清理（pgbouncer）
server_reset_query = DISCARD ALL
-- 显式清理（长会话）
DROP TABLE IF EXISTS pg_temp.t;
-- 监控临时文件
SELECT datname, temp_files, temp_bytes FROM pg_stat_database;