临时表与 CTE 物化与 DDL 元数据与目录

共 42 题

#

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

A CTE 可多次引用，是否"只算一次"取决于优化器：PostgreSQL 12+ 默认内联（可用 MATERIALIZED 强制物化）、MySQL 默认物化、SQL Server 恒内联，物化与内联语义等价但影响下推与重复计算 ✓ 正确答案

B CTE 一定只计算一次

C CTE 不能被引用两次

D SQL Server 支持 MATERIALIZED 关键字

#

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

A ON COMMIT DELETE ROWS 提交时清空数据留结构、PRESERVE ROWS（默认）会话级保留、DROP 提交时删除表本身；MySQL 临时表无 ON COMMIT、SQL Server 事务回滚会删表 ✓ 正确答案

B ON COMMIT DROP 只清空数据

C 三个选项行为相同

D ON COMMIT 在回滚时生效

#

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

A 三库都支持递归 CTE，差异在：SQL Server 用 MAXRECURSION（默认 100）、MySQL 默认深度 1000、PostgreSQL 14+ 无层数限制且有 SEARCH/CYCLE 子句；递归项中不允许聚合/窗口是共性 ✓ 正确答案

B 三库递归语法完全一致

C MySQL 8.0 不支持递归

D SQL Server 支持 SEARCH 子句

#

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

A 递归 CTE 一定会自动终止

B UNION 会保留重复路径

C UNION ALL 天然防环

D 递归 CTE 迭代到"无新行"即终止（数据有限时自然收敛）；UNION 去重使环自动收敛（集合语义），UNION ALL 不去重在环上会无限循环，需深度计数/访问集防环，实践上始终加深度上限 ✓ 正确答案

#

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

A PostgreSQL 与 MySQL 的临时表都可建索引（索引随会话/事务自动清理）；PG 会为临时表做统计，MySQL 临时表统计有限，大中间结果多查询时应显式建索引 ✓ 正确答案

B 临时表不能建索引

C 临时表索引跨会话可见

D 临时表索引不能删除

#

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

A CTE 与内联子查询语义等价，优化器都可能展开统一优化（PG 12+ 默认内联两者计划趋同）；差异在 CTE 可命名复用、可 MATERIALIZED 控制物化、可递归，MySQL 中两者默认行为可能不同 ✓ 正确答案

B CTE 一定比子查询快

C 子查询可以递归

D CTE 不能引用多次

#

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

A MySQL 8.0 的 CTE 默认内联

B MySQL 8.0.1+ 支持 CTE（递归深度默认 1000），CTE 默认物化（多次引用共享但谓词不下推、物化临时表无索引），无 MATERIALIZED 关键字，与派生表默认合并的行为不同 ✓ 正确答案

C MySQL 5.7 支持 CTE

D MySQL 的 CTE 支持 SEARCH 子句

#

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

A PostgreSQL 14+ 的 SEARCH DEPTH/BREADTH FIRST 声明递归结果的深度/广度优先遍历（自动维护排序路径），CYCLE 子句声明环检测键并剪枝标记，替代手工路径数组与深度列 ✓ 正确答案

B SEARCH 子句只在 MySQL 中支持

C SEARCH 子句只能用于非递归 CTE

D CYCLE 子句不检测环

#

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

A 计划缓存不受临时表影响

B 预处理语句不受结构变化影响

C DISCARD ALL 只清临时表

D 会话内临时表结构/存在性变化会使缓存的计划失效（"cached plan must not change result type"），DISCARD ALL 清空会话级状态（计划缓存+临时表）、DISCARD PLANS 只清计划缓存，连接池常配置归还时执行 ✓ 正确答案

#

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

A CTE 链式/嵌套定义无硬性层数上限（PG/MySQL 允许 WITH 内嵌 WITH、SQL Server 要求平铺），实际受解析栈与内存约束；有硬限制的是递归 CTE 的迭代层数 ✓ 正确答案

B CTE 定义嵌套有硬性语法上限

C SQL Server 支持嵌套 WITH

D 嵌套深度不影响性能

#

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

A CTE 可以直接声明 WITH CHECK OPTION

B CTE 是可更新对象

C 所有视图都支持 CHECK OPTION

D WITH CHECK OPTION 属于视图对象，CTE 是语句级只读命名查询不能直接声明；合法联用是"视图体内用 CTE 组织、视图带 CHECK OPTION"（PG 支持，MySQL/SQL Server 视图不支持 CTE 需子查询） ✓ 正确答案

#

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

A 三库的 INFORMATION_SCHEMA 完全一致

B 信息_SCHEMA 查询总是最快

C SQL Server 没有 INFORMATION_SCHEMA

D PostgreSQL/MySQL/SQL Server 都实现标准核心视图（TABLES/COLUMNS/TABLE_CONSTRAINTS/REFERENTIAL_CONSTRAINTS），但覆盖度与扩展字段不同（PG 最全、MySQL 有 ENGINE 扩展、SQL Server 仅子集且推荐 sys 目录），大库查询慢可用原生目录 ✓ 正确答案

#

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

A MDL 不会阻塞 DDL

B MDL 队列不影响应用

C 所有 DDL 都支持 INSTANT

D 8.0 的 INSTANT（元数据级）/INPLACE（就地、可 LOCK=NONE）/COPY（复制锁表）自动选择；长事务持共享 MDL 会阻塞 DDL（排他 MDL 排队且写优先），需监控 innodb_trx/metadata_locks 并设 lock_wait_timeout ✓ 正确答案

#

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

A performance_schema 存权限

B mysql 库存权限/账户/字典、information_schema 提供标准元数据视图、performance_schema 记录运行时指标（语句/锁/事务）、sys 是诊断封装视图；按场景选用 ✓ 正确答案

C information_schema 记录运行时指标

D mysql 库可直接改字典表

#

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

A pg_class 记录列信息

B pg_class（关系/relkind）、pg_attribute（列/类型 OID）、pg_constraint（约束/contype）、pg_type（类型）、pg_proc（函数/源码）是核心目录表，通过 OID JOIN 与 format_type/regclass 组成查询模式，比 information_schema 更全更快 ✓ 正确答案

C pg_proc 记录表结构

D 信息_SCHEMA 底层不依赖 pg_catalog

#

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

A SQL Server 中 information_schema 比 sys 目录更完整

B sys.indexes 不含唯一标记

C sys.tables 不含 schema 信息

D SQL Server 官方优先使用 sys 目录视图（sys.tables/columns/indexes/objects：覆盖索引与专有细节、性能好），information_schema 仅为标准兼容（无索引、有权限过滤、慢），跨库可移植脚本才用信息_SCHEMA ✓ 正确答案

#

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

A 元数据一致性由元数据锁与快照保证：PG 目录表走 MVCC 且 DDL 需 ACCESS EXCLUSIVE、MySQL 用 MDL（8.0 原子 DDL）、SQL Server 用 Sch-S/Sch-M；长查询阻塞 DDL 是普遍现象 ✓ 正确答案

B DDL 从不与查询冲突

C 查询执行中元数据可以任意变化

D MySQL 无元数据锁

#

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

A 注释存储在应用配置文件

B 信息_SCHEMA 提供标准注释视图

C COMMENT ON 把注释写入 pg_description（objoid/classoid/objsubid/description：列注释用 objsubid 区分），查询用 obj_description/col_description 或 JOIN pg_class，pg_dump 会导出注释 ✓ 正确答案

D 删除表不会清理注释

#

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

A 事件触发器监听 DML 变更

B 事件触发器可按表粒度创建

C 事件触发器监听 DDL 事件（ddl_command_start/end、sql_drop、table_rewrite，按命令标签触发、tg_tag 与配套函数取明细），不监听 DML/VACUUM/临时表 DDL，用于审计、防护与阻止大表重写 ✓ 正确答案

D 事件触发器不需要超级用户权限

#

20. MySQL 中 information_schema.INNODB_TRX 的用途？

A INNODB_TRX 列出全部运行中的 InnoDB 事务（状态/开始时间/线程 id/当前 SQL/锁行数），用于定位长事务与锁等待源头（8.0 锁信息在 performance_schema.data_locks），可 KILL 阻塞连接 ✓ 正确答案

B INNODB_TRX 只显示当前连接的事务

C INNODB_TRX 无查询字段

D 查询 INNODB_TRX 不需要权限

#

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

A InnoDB 为 MVCC/并发不维护精确行数，TABLE_ROWS 是统计估算（随 ANALYZE/自动重算更新、偏差可能很大）；精确行数需 COUNT(*)（大表慢）或应用侧计数缓存表 ✓ 正确答案

B TABLE_ROWS 总是精确的

C MyISAM 的 TABLE_ROWS 也是估算

D TABLE_ROWS 用于精确对账

#

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

A SHOW 命令交互快捷（且 SHOW CREATE TABLE 提供完整 DDL），信息_SCHEMA 可 SELECT 过滤/JOIN/聚合且跨库标准（SHOW 是 MySQL 方言），脚本化查询优先信息_SCHEMA ✓ 正确答案

B 信息_SCHEMA 只能展示不能过滤

C SHOW 可以跨库移植

D SHOW 与信息_SCHEMA 完全等价

#

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

A \df 显示函数源码

B information_schema.routines 含源码

C 所有函数都在 pg_catalog

D 可用 \df、pg_proc（prosrc 源码、pg_get_function_arguments/result、provolatile、prokind，排除 pg_catalog 与扩展函数）或 information_schema.routines（标准但无源码）查询自定义函数 ✓ 正确答案

#

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

A idx_scan 记录唯一约束的检查次数

B 统计从表创建时开始

C pg_stat_user_indexes 的 idx_scan 是索引扫描次数（累计值），用于识别未使用索引（idx_scan=0 且体积大者优先删除）与回表分析（tup_fetch 高考虑覆盖索引），删除前需确认约束与外键依赖 ✓ 正确答案

D 未使用索引没有维护成本

#

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

A pg_relation_size 包含索引

B 分区父表的大小包含所有分区

C pg_table_size 不含 TOAST

D pg_relation_size 返回表主存储、pg_total_relation_size 返回表总大小（堆+TOAST+索引）、pg_indexes_size 返回索引大小，配合 pg_size_pretty 与批量 JOIN pg_class 可做容量分析与大表排名 ✓ 正确答案

#

26. PostgreSQL 的 pg_settings 视图的用途？

A pg_settings 只显示默认值

B 参数修改总是立即生效

C pg_settings 是运行时参数（GUC）视图：含当前值/默认值/context（何时可设）/source（来源）/pending_restart（是否需重启），用于参数排查、生效确认与配置对比 ✓ 正确答案

D pg_settings 不含单位信息

#

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

A COLUMNS 视图没有类型字段

B 列序号 ORDINAL_POSITION 不存在

C COLUMNS 只能查库不能查表

D INFORMATION_SCHEMA.COLUMNS 每列一行：名称/序号/默认值/可空/类型（长度与精度）/字符集，MySQL 有 COLUMN_TYPE/EXTRA 扩展；PG 底层细节（身份列/生成列）用 pg_attribute 查 ✓ 正确答案

#

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

A 临时表跨会话可见

B UNLOGGED 表会话私有

C 临时表会话私有且自动清理（中间数据隔离），UNLOGGED 表全局共享、持久但崩溃清空且不复制（可重建数据用）；两者都不写 WAL 写入快，选型看可见性与生命周期 ✓ 正确答案

D 两者都参与流复制

#

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

A 派生表可以跨语句复用

B 临时表无法建索引

C 派生表可以写入

D 派生表是语句级只读子查询（通常被优化器内联、无索引），临时表是会话级可写对象（可建索引、跨语句复用、需管理生命周期）；单语句用派生表/CTE、跨语句流程用临时表 ✓ 正确答案

#

30. 临时表是否占用 shared_buffers？

A 临时表使用 shared_buffers

B temp_buffers 是全局共享参数

C 临时表数据参与 WAL

D PostgreSQL 临时表数据存于会话级 temp_buffers（本地缓冲，不占 shared_buffers、不参与复制），超限落临时文件（受 temp_file_limit 限制），排序/哈希另用 work_mem ✓ 正确答案

#

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

A 临时表适合高频请求路径

B 临时表统计总是最优

C 临时表每请求建删有 DDL/IO 开销、连接池残留风险、统计弱且显式物化无法下推，OLTP 高频路径应用 CTE/子查询（可下推）或应用层替代，临时表只留给低频批处理 ✓ 正确答案

D 临时表可以避免所有子查询

#

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

A PostgreSQL 的 COMMENT ON 写入 pg_description（objoid/classoid/objsubid：表注释 subid=0、列注释 subid=列号），查询用 obj_description/col_description，MySQL 用 TABLE_COMMENT/COLUMN_COMMENT、SQL Server 用扩展属性 ✓ 正确答案

B 注释存储在应用代码

C pg_description 不含列注释

D 注释参与约束执行

#

33. Oracle 的 DBA_TABLES 与 ALL_TABLES 差异？

A USER_TABLES 只含当前用户拥有的表、ALL_TABLES 含当前用户可访问的表、DBA_TABLES 含全库所有表（需 DBA 权限）；字段结构一致（NUM_ROWS 是统计快照），同族还有 TAB_COLUMNS/INDEXES ✓ 正确答案

B 三个视图的行集完全相同

C DBA_TABLES 无需权限

D NUM_ROWS 是实时精确行数

#

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

A 信息_SCHEMA 查询总是最快

B pg_attribute 查询比信息_SCHEMA 慢

C 信息_SCHEMA 的权限检查是免费的

D information_schema 是多层视图：逐对象权限检查与格式化函数使大库查询慢；替代用 pg_catalog 直查（pg_class/pg_attribute JOIN + format_type，快 1-2 个数量级），SQL Server 同理用 sys 目录 ✓ 正确答案

#

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

A sys.dm_db_partition_stats 按表/索引/分区返回近似行数与页数统计（row_count、used/reserved 页数），用于容量分析、分区分布与索引管理，比 sp_spaceused 粒度更细、比 COUNT(*) 快但行数近似 ✓ 正确答案

B row_count 是精确事务计数

C 该视图只返回行数不返回页数

D 可以跨数据库查询

#

36. pg_class 与 pg_namespace 的关系？

A pg_class 中直接存 schema 名

B 两个表没有关联

C pg_class 通过 relnamespace（OID）关联 pg_namespace（对象→schema 多对一），查询 schema 名必须 JOIN，用 'schema.table'::regclass 可精确转换，相关目录同样以 OID 外键关联 ✓ 正确答案

D pg_namespace 包含对象名

#

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

A 两者都是数据库清单

B sys.databases 是实例层数据库清单（恢复模式/状态/兼容级别），information_schema.schemata 是当前库内 schema 清单（名称/属主），层级不同；MySQL 的 schemata 相当于库清单、PG/SQL Server 的 schemata 是库内 schema ✓ 正确答案

C schemata 显示实例所有库的 schema

D sys.databases 不含恢复模式

#

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

A 临时表是全局共享的

B CREATE TEMP TABLE t (id INT) 创建会话私有临时表（自动清理、pg_temp、无 WAL/复制）；MySQL 用 TEMPORARY、SQL Server 用 # 前缀、Oracle 用全局临时表（结构持久数据临时） ✓ 正确答案

C 临时表结构与普通表完全相同包括持久性

D 临时表需要所有会话同步创建

#

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

A ON COMMIT DELETE ROWS 提交清数据留结构、PRESERVE ROWS（默认）提交保留数据、DROP 提交删除表本身；按"每事务一批/会话累积/一次性"场景选择，MySQL 无 ON COMMIT ✓ 正确答案

B PRESERVE ROWS 提交时清空数据

C 三个选项都删除表

D ON COMMIT 在回滚时生效

#

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

A DDL 不阻塞任何语句

B DDL 持排他元数据锁（PG ACCESS EXCLUSIVE、MySQL MDL 写优先、SQL Server Sch-M）：长事务会卡住 DDL 且排队放大阻塞、计划失效、并发 DDL 串行化；规避靠低峰、排长事务、在线算法与锁超时 ✓ 正确答案

C MDL 不影响 SELECT

D 从库的 DDL 不影响复制

#

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

A 元数据从不参与 MVCC

B MySQL 8.0 字典不支持事务

C 目录 MVCC 使 DDL 无需锁

D PostgreSQL 系统目录是 MVCC 表（事务性 DDL、快照一致的元数据读取），MySQL 8.0 数据字典基于 InnoDB、SQL Server 靠 Sch-S 锁；元数据一致性 = 快照（MVCC）+ 锁（DDL 互斥）两层 ✓ 正确答案

#

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

A 临时表在断连后仍然保留

B 连接池中的临时表自动清理

C ON COMMIT 只影响数据不影响表

D 临时表两级绑定：会话级（断连自动清理，正常/异常/崩溃都会清理）与事务级（PG 的 ON COMMIT 控制提交时数据/表的去留）；连接池复用时残留需 DISCARD ALL 或 ON COMMIT DROP 清理 ✓ 正确答案