罗罗编程学习记录
本页内容 展开查看

05.4.2.4 列存、内存优化与跨库对照

所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.2 T-SQL 与执行计划

覆盖范围

  • 列存索引:行组、段、批处理执行与压缩
  • 内存优化表:持久化/非持久化、原生编译、内存索引
  • 备份恢复、PITR 与诊断扩展事件
  • SQL Server 与 MySQL、PostgreSQL 在架构、日志、并发、高可用上的对照
  • 选型边界、版本特性边界与高频结论清单

高频题速览

编号 题目 难度 频率
05.4.2.4-001 列存索引基本原理 进阶 ★★★★☆
05.4.2.4-002 行组类型与增量合并 高级 ★★★☆☆
05.4.2.4-004 列存适用与不适用场景 进阶 ★★★★☆
05.4.2.4-006 内存优化表 高级 ★★★☆☆
05.4.2.4-011 与 MySQL 架构对照 高级 ★★★★☆
05.4.2.4-013 LDF 与 redo/binlog/WAL 高级 ★★★★☆
05.4.2.4-015 高可用方案对照 高级 ★★★★☆
05.4.2.4-017 什么时候选 SQL Server 进阶 ★★★☆☆

两类专用结构

列存索引(Columnstore)
  表 → 行组 Rowgroup → 段 Segment(按列连续存放)→ 字典/增量编码压缩
  批处理模式:按行组批量计算,适合扫描/聚合

内存优化表(In-Memory OLTP)
  数据常驻内存,结构按内存布局组织
  哈希索引 / 内存非聚集索引;原生编译模块
  持久化(内存 + 日志)或非持久化(仅内存)

05.4.2.4-001 列存索引的基本原理是什么?

频率 ★★★★☆ | 难度 进阶

一句话结论

列存按列组织数据:同一列的值连续存放在段(Segment)中,配合批处理模式与高压缩,大幅提升分析型扫描与聚合效率。

概念解释

  • 行存:一行各列相邻,适合点查与单行读写。
  • 列存:同列连续存放,类型一致、重复多,压缩率高;扫描聚合只读涉及列。
  • 批处理模式(Batch Mode):按行组批量处理,减少逐行解释开销。

原理流程

行存:读一行需取整行所有列
列存:聚合只扫目标列的段 → 字典/编码解压 → 批量聚合

代码 / 命令

-- 聚集列存索引(分析大表)
CREATE CLUSTERED COLUMNSTORE INDEX CIX_FactSales ON dbo.FactSales;

-- 查看行组与压缩状态
SELECT  OBJECT_NAME(object_id) AS table_name, row_group_id, state_desc,
        total_rows, trim_reason_desc
FROM    sys.column_store_row_groups;

常见误区

  • 误区:列存就是把行存按列拆开存磁盘。正解:它有独立的段、编码与批处理执行机制。
  • 误区:列存可以替代所有 B 树索引。正解:点查、高频单行更新仍以行存为主。

追问

  • 问:列存为什么压缩率高?答:同列类型一致、重复值多,字典与增量编码效果好。
  • 问:列存扫描只读涉及列吗?答:是,未涉及列的段不读取。

05.4.2.4-002 行组有哪些类型?增量数据怎么合并?

频率 ★★★☆☆ | 难度 高级

一句话结论

列存数据按行组组织:新先进增量行组(delta rowgroup),达到阈值后压缩为压缩行组;删除标记存墓碑,后台合并优化。

概念解释

  • 增量行组(Delta):新插入/更新暂存,行存结构,可快速写入。
  • 压缩行组(Compressed):批量压缩后的列存行组。
  • 墓碑(Tombstone):被删行的标记,合并时清理。
  • 合并(Tuple Mover/重建):把增量行组转压缩、清理墓碑与碎片。

原理流程

插入 → delta 行组 → 达阈值 → 压缩行组
删除 → 墓碑标记 → 后台合并清理

代码 / 命令

-- 查看行组状态:OPEN(增量)/ CLOSED / COMPRESSED / TOMBSTONE
SELECT  row_group_id, state_desc, total_rows, deleted_rows
FROM    sys.column_store_row_groups
WHERE   object_id = OBJECT_ID('dbo.FactSales');

-- 手动重组列存合并行组
ALTER INDEX CIX_FactSales ON dbo.FactSales REORGANIZE;

常见误区

  • 误区:列存插入立即进入压缩段。正解:先进增量行组,批量后才压缩。
  • 误区:删除立即释放段空间。正解:先标记墓碑,合并后才真正回收。

追问

  • 问:增量行组过多说明什么?答:批量不足或合并滞后,需检查 Tuple Mover 与负载。
  • 问:列存频繁单行更新有什么问题?答:产生大量增量与版本维护,压缩合并成本高。

05.4.2.4-003 列存用了哪些压缩手段?

频率 ★★☆☆☆ | 难度 高级

一句话结论

列存压缩包括段内字典编码、增量编码与列式压缩,归档模式再叠加更高压缩率但解压更慢。

概念解释

  • 字典编码:段内重复值映射为字典索引。
  • 增量编码:存储相邻值差,适合有序数值。
  • 归档压缩(Archive):更高压缩比,用于冷数据,查询解压成本高。

原理流程

段内值 → 字典/增量编码 → 压缩存储
查询 → 按段解压 → 批处理聚合

代码 / 命令

-- 列存归档压缩(冷数据)
ALTER INDEX CIX_FactSales ON dbo.FactSales
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

常见误区

  • 误区:归档压缩适合热数据。正解:压缩率高但解压慢,只适合归档冷数据。
  • 误区:压缩不影响查询速度。正解:解压增加 CPU 开销,需在压缩率与查询性能间权衡。

追问

  • 问:怎么看段压缩大小?答:列存目录视图与 DMV 可查段大小与行数。
  • 问:列存压缩与行存页压缩叠加吗?答:两套机制,列存走自己的编码压缩。

05.4.2.4-004 列存适合什么场景?不适合什么场景?

频率 ★★★★☆ | 难度 进阶

一句话结论

列存适合大表扫描、分组聚合、报表分析;不适合高频点查、单行频繁更新与强事务 OLTP。

概念解释

  • 适合:宽表事实表、批量分析、大范围扫描、低写入频率。
  • 不适合:按键点查、单行高频增删改、需要在行存结构上做频繁索引维护。

原理流程

分析负载:扫大段 + 聚合 → 列存收益大
OLTP 负载:点查 + 频繁单行写 → 行存 B 树更合适

代码 / 命令

-- 行存 + 非聚集列存索引(混合负载示例)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCIX_Fact ON dbo.FactSales;

常见误区

  • 误区:列存索引越多查询越快。正解:列存维护成本高,按分析查询需求建。
  • 误区:小表也值得建列存。正解:小表扫描成本本就低,列存收益不明显。

追问

  • 问:混合负载怎么办?答:行存支撑事务,列存支撑分析,按查询路由选择路径。
  • 问:列存表能建 B 树索引吗?答:可在列存基础上配合行存索引,具体组合按版本核对(待确认)。

05.4.2.4-005 行存与列存混合(OLTP 聚集列存)是什么思路?

频率 ★★☆☆☆ | 难度 高级

一句话结论

同一表可同时有行存结构与列存索引:事务写入走行存,分析查询走列存,优化器按代价选择访问路径。

概念解释

  • 混合负载:既有单行事务又有报表聚合。
  • 共存代价:写入需维护两套结构,写放大上升。
  • 具体索引组合(聚集列存/非聚集列存/行存索引)受版本限制。

原理流程

写入 → 维护行存 + 列存结构
查询 → 优化器按算子代价选行存或列存路径

代码 / 命令

-- 行存表上建非聚集列存索引
CREATE NONCLUSTERED COLUMNSTORE INDEX NCIX_Sales ON dbo.Sales (
    CustomerId, ProductId, Amount, CreateTime);

常见误区

  • 误区:混合结构对写入透明无成本。正解:双结构维护增加写入与日志开销。
  • 误区:优化器总会自动选最优结构。正解:统计与查询模式影响选择,需实测验证。

追问

  • 问:什么版本开始支持混合模式?答:特性随版本演进,具体能力边界按目标版本文档核对(待确认)。
  • 问:怎么判断查询走了列存?答:实际计划中列存扫描/批处理算子标识。

05.4.2.4-006 内存优化表是什么?

频率 ★★★☆☆ | 难度 高级

一句话结论

内存优化表(In-Memory OLTP)数据常驻内存,按内存布局组织,减少锁/闩锁开销;分持久化与非持久化两类。

概念解释

  • 持久化内存表:内存中有副本,变更写日志,崩溃后可从日志恢复。
  • 非持久化(内存优化临时):仅内存,重启丢失,等价高性能临时表。
  • 无传统页结构与锁竞争模型,适合高并发点查点写。

原理流程

数据常驻内存 → 直接内存访问 → 低延迟
持久化:日志保证可恢复;非持久化:不落盘

代码 / 命令

-- 持久化内存优化表(需在内存优化文件组中,示例结构)
CREATE TABLE dbo.HotOrder (
    Id BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    Amount MONEY NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

常见误区

  • 误区:内存表就是"放在内存里的普通表"。正解:其数据结构、索引与并发模型都按内存设计。
  • 误区:内存表不需要日志。正解:持久化内存表仍写日志保证恢复。

追问

  • 问:内存表适合大表吗?答:容量受内存限制,适合热数据/高并发小中型表。
  • 问:内存表支持传统 B 树索引吗?答:支持内存优化专用索引(哈希/内存非聚集),结构与磁盘索引不同。

05.4.2.4-007 什么是原生编译模块?

频率 ★★☆☆☆ | 难度 高级

一句话结论

原生编译存储过程/触发器针对内存优化表编译为机器码,减少解释执行开销,适合高频小逻辑。

概念解释

  • 原生编译:模块在创建/部署时编译为 DLL 机器码。
  • 受限子集:并非全部 T-SQL 语法可用于原生编译,只支持受支持结构。
  • 与解释执行的常规存储过程区别明显。

原理流程

创建原生编译过程 → 编译机器码 → 高频调用直接执行

代码 / 命令

-- 原生编译存储过程(语法子集受限,示例)
CREATE PROCEDURE dbo.UpsertHotOrder @Id BIGINT, @Amount MONEY
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.HotOrder SET Amount = @Amount WHERE Id = @Id;
END;

常见误区

  • 误区:把普通过程 WITH NATIVE_COMPILATION 就能提速。正解:语句需落在受支持子集内,否则编译失败。
  • 误区:原生编译适合所有过程。正解:复杂逻辑、大量临时表/函数受限,按场景选择。

追问

  • 问:原生编译支持哪些 T-SQL 结构?答:以官方受支持特性清单为准(待确认目标版本)。
  • 问:原子块 BEGIN ATOMIC 作用?答:定义事务与语言设置,原生模块必需。

05.4.2.4-008 内存优化表的索引有哪几类?

频率 ★★☆☆☆ | 难度 高级

一句话结论

内存优化表支持哈希索引与内存优化非聚集索引;哈希索引适合等值查找,内存非聚集索引适合范围与排序。

概念解释

  • 哈希索引:桶 + 哈希,等值 O(1) 类,不支持范围扫描。
  • 内存非聚集索引:内存结构有序,支持范围与排序。
  • BUCKET_COUNT:哈希桶数,按数据量估算。

原理流程

等值查询 → 哈希索引定位
范围/排序 → 内存非聚集索引

代码 / 命令

CREATE TABLE dbo.HotOrder (
    Id BIGINT NOT NULL,
    Status INT NOT NULL,
    Amount MONEY NOT NULL,
    INDEX IX_Status NONCLUSTERED (Status),
    PRIMARY KEY NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON);

常见误区

  • 误区:哈希索引能做范围查询。正解:哈希无序,范围扫描需内存非聚集索引。
  • 误区:桶数越大越好。正解:桶过多浪费内存,过少哈希冲突上升,按数据量与增长估算。

追问

  • 问:哈希冲突怎么影响性能?答:同桶链变长,等值查找遍历成本上升。
  • 问:内存索引需要重建维护吗?答:结构随表维护,统计与磁盘索引机制不同。

05.4.2.4-009 内存优化表的事务和日志与磁盘表有什么不同?

频率 ★★☆☆☆ | 难度 高级

一句话结论

持久化内存表仍写事务日志保证恢复,但并发模型减少传统锁;非持久化表不保留数据,日志行为受限。

概念解释

  • 日志:持久化表的变更记录日志,崩溃可恢复。
  • 并发:内存优化采用无锁/低锁设计,减少阻塞。
  • Durability 选项:SCHEMA_AND_DATA 持久化;SCHEMA_ONLY 只留结构。

原理流程

持久化:内存数据 + 日志 → 重启按日志恢复
仅结构:重启后表存在、数据清空

代码 / 命令

-- 仅结构持久化,数据重启丢失
CREATE TABLE dbo.SessionCache (
    Id BIGINT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    Payload NVARCHAR(MAX)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

常见误区

  • 误区:内存表不产生日志。正解:持久化表必须写日志。
  • 误区:内存表事务可以不隔离。正解:仍有隔离级别与一致性要求,只是并发机制不同。

追问

  • 问:内存表崩溃会丢数据吗?答:持久化表从日志恢复;仅结构表数据丢失。
  • 问:内存表受 tempdb 版本存储影响吗?答:其版本机制与磁盘表行版本不同,需按版本文档核对。

05.4.2.4-010 列存/内存表的版本演进与适用边界要注意什么?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

两类特性随 SQL Server 版本持续增强,具体支持的索引组合、语法子集与兼容性必须按目标版本文档核对,不凭记忆断言版本行为。

概念解释

  • 演进:列存从分析场景逐步扩展到混合负载;内存优化表语法与支持范围逐版完善。
  • 边界:版本间功能差异、兼容性级别、工具链支持不同。
  • 不确定的版本行为标注"待确认",在目标环境实测验证。

原理流程

选型 → 核对目标版本特性清单 → 小范围验证 → 推广

代码 / 命令

-- 确认实例版本与兼容级别
SELECT @@VERSION;
SELECT name, compatibility_level FROM sys.databases WHERE name = N'DemoDB';

常见误区

  • 误区:文档中的新特性在老版本也能用。正解:特性有起始版本,需逐项核对。
  • 误区:测试库版本等于生产版本就放心使用。正解:还需核对兼容级别与补丁级别。

追问

  • 问:怎么查某特性从哪个版本支持?答:Microsoft Learn 对应页面标注适用版本,生产前实测。
  • 问:跨版本迁移要注意什么?答:核对被废弃/变更语法与特性行为。

05.4.2.4-011 SQL Server 与 MySQL 的整体架构怎么对照?

频率 ★★★★☆ | 难度 高级

一句话结论

SQL Server 为单进程多线程、整体式引擎;MySQL 为插件式存储引擎,Server 层与引擎层分离;二者日志、并发与扩展机制各成体系。

概念解释

维度 SQL Server MySQL InnoDB
进程模型 单实例多线程 多线程,Server 层 + 引擎插件
存储结构 8KB 页 + 64KB 区 16KB 页 + 1MB 区
表组织 堆或聚集索引表 聚簇索引组织表
日志 LDF redo + undo + binlog
并发 锁 + 行版本(RCSI/快照) MVCC + 锁
扩展 AG、列存、内存表 复制、分库分表、InnoDB Cluster

原理流程

  • 对照主线:连接 → 解析优化 → 引擎存取 → 日志恢复,分层方式不同。

代码 / 命令

-- 查看 SQL Server 版本与配置
SELECT @@VERSION;

常见误区

  • 误区:InnoDB 聚簇索引等同于 SQL Server 聚集索引就完全等价。正解:概念相近,页大小、二级索引携带信息与日志模型不同。
  • 误区:MySQL binlog 对应 SQL Server LDF。正解:binlog 是 Server 层逻辑/行日志;LDF 更接近引擎 redo。

追问

  • 问:MySQL 插件式引擎优势是什么?答:可按场景换引擎,InnoDB 为默认。
  • 问:SQL Server 有 binlog 等价物吗?答:变更捕获用 CDC/变更跟踪,不是独立 binlog。

05.4.2.4-012 SQL Server 与 PostgreSQL 在存储、并发、扩展上怎么对照?

频率 ★★★☆☆ | 难度 高级

一句话结论

PG 是多进程 + 堆表 MVCC;SQL Server 多线程 + 堆/聚集表 + 可选行版本;扩展机制上 PG 依赖丰富索引类型与外部高可用组件。

概念解释

维度 SQL Server PostgreSQL
进程模型 单进程多线程 每连接一 backend 进程
表组织 堆/聚集索引 堆表 + MVCC 版本
版本回收 版本存储清理 VACUUM/autovacuum
索引 B 树、列存、XML/空间/全文 B 树、GIN/GiST/BRIN/Hash/表达式/部分
并发 锁 + RCSI/快照 MVCC 快照 + SSI 可序列化

原理流程

  • 对照主线:旧版本放哪、谁回收、读一致性怎么保证。

代码 / 命令

-- SQL Server 版本;PG 对应 SELECT version();
SELECT @@VERSION;

常见误区

  • 误区:PG MVCC 旧版本也存临时区。正解:版本留在堆中,VACUUM 回收。
  • 误区:SQL Server 行版本与 PG MVCC 实现相同。正解:版本存 tempdb 版本存储,回收机制不同。

追问

  • 问:PG 进程模型对连接数有什么影响?答:连接开销大,需连接池,详见 05.3.1-001。
  • 问:哪些 PG 索引类型 SQL Server 没有直接对应?答:GIN/GiST/BRIN、表达式/部分索引(用计算列/筛选索引近似)。

05.4.2.4-013 LDF、redo log、binlog、WAL 分别是什么关系?

频率 ★★★★☆ | 难度 高级

一句话结论

LDF/SQL Server、redo/InnoDB、WAL/PG 都是引擎崩溃恢复日志(WAL 思路);MySQL binlog 是 Server 层额外逻辑/行日志,用于复制与归档。

概念解释

日志 归属 作用
LDF SQL Server 引擎 事务记录、redo/undo、恢复、AG 同步
redo log InnoDB 引擎 崩溃恢复前滚
undo log InnoDB 引擎 回滚与 MVCC 版本
binlog MySQL Server 复制、时间点恢复
WAL PG 引擎 崩溃恢复与流复制

原理流程

修改 → 先写引擎日志(WAL)→ 提交确认 → 脏页异步落盘
崩溃恢复:redo 前滚已提交 → undo/版本回滚未提交

代码 / 命令

-- SQL Server 日志空间与等待
DBCC SQLPERF(LOGSPACE);

常见误区

  • 误区:binlog 是 InnoDB 引擎日志。正解:它在 Server 层,所有引擎可见。
  • 误区:LDF 只记录提交事务。正解:未提交事务的修改也有记录,用于回滚。

追问

  • 问:两阶段提交在 MySQL 解决什么?答:redo 与 binlog 一致提交,详见 05.2.1-010。
  • 问:PG WAL 与流复制关系?答:备库接收 WAL 重做,详见 05.3.4。

05.4.2.4-014 三者的锁/并发模型怎么对照?

频率 ★★★☆☆ | 难度 高级

一句话结论

SQL Server 以显式锁为主、可选行版本;InnoDB 与 PG 以 MVCC 为读一致性基础,再配合行锁与间隙/谓词锁。

概念解释

维度 SQL Server InnoDB PostgreSQL
读一致性 锁读或 RCSI/快照版本 MVCC 读版本 MVCC 读版本
写锁 X 锁 行锁 + 间隙锁 行锁 + 谓词锁
旧版本位置 tempdb 版本存储 undo log 堆版本
幻读防护 Serializable 范围锁 间隙锁 SSI 检测

原理流程

  • 对照主线:读是否阻塞写由锁还是版本决定;版本方案都需回收旧版本。

代码 / 命令

-- SQL Server 当前锁
SELECT session_id, resource_type, request_mode, request_status
FROM sys.dm_tran_locks;

常见误区

  • 误区:开了 RCSI 就不需要锁。正解:写仍加 X 锁,只是读不走 S 锁阻塞写。
  • 误区:MVCC 数据库没有锁。正解:写冲突仍靠行锁等待与中止处理。

追问

  • 问:SQL Server 哪个级别最接近 InnoDB RR 读体验?答:快照隔离(事务级一致快照),写冲突处理不同。
  • 问:PG SSI 解决什么?答:检测并中止写偏序,实现可序列化而不加全表锁。

05.4.2.4-015 三者的高可用方案怎么对照?

频率 ★★★★☆ | 难度 高级

一句话结论

SQL Server AG 内置副本、仲裁与监听器;MySQL 靠主从复制 + 外部切换组件;PG 靠流复制 + Patroni 等外部编排。

概念解释

维度 SQL Server MySQL PostgreSQL
复制载体 日志块 binlog WAL
同步模式 同步/异步提交 异步/半同步 同步/异步流复制
自动切换 WSFC + AG MHA/Orchestrator/InnoDB Cluster Patroni + 协调存储
读扩展 辅助副本 + 只读路由 从库读 备库读
对外地址 监听器 代理/VIP 连接池/代理

原理流程

日志复制 → 副本追平 → 仲裁判定 → 切换角色 → 接入层指向新主

代码 / 命令

-- SQL Server 副本角色
SELECT replica_server_name, role_desc, synchronization_state_desc
FROM sys.dm_hadr_availability_replica_states;

常见误区

  • 误区:三库自动切换都内置在数据库内。正解:只有 SQL Server AG 内置集群协调;MySQL/PG 多需外部组件。
  • 误区:异步复制零丢失。正解:未复制日志在故障时可能丢失。

追问

  • 问:半同步与 SQL Server 同步提交思路相通吗?答:相通,都等待副本确认;超时与降级策略不同。
  • 问:RTO/RPO 分别由什么决定?答:检测切换速度与复制模式/队列延迟。

05.4.2.4-016 方言与生态迁移要注意什么?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

跨库迁移重点核对函数、分页、日期处理、类型、存储过程方言、隐式转换与工具链;生态上 SQL Server 配套 SSMS、SSIS/SSRS/SSAS。

概念解释

  • 方言差异:字符串/日期/数学函数、分页语法、过程与异常块。
  • 类型映射:金额、时间、大对象、布尔类型对应关系。
  • 工具链:备份还原、作业调度、报表与监控各自独立。

原理流程

 schema 迁移 → 数据类型映射 → SQL/过程改写 → 兼容测试 → 性能复测

代码 / 命令

-- 分页方言对照:
-- SQL Server: OFFSET ... FETCH NEXT
-- MySQL:      LIMIT offset, n
-- PG:         LIMIT n OFFSET offset

常见误区

  • 误区:标准 SQL 语句迁移后行为一致。正解:函数与隐式转换最易产生差异。
  • 误区:存储过程可以机械翻译。正解:过程控制、异常、临时表语义需逐段重写。

追问

  • 问:迁移先迁结构还是数据?答:先 schema 与类型映射,再数据迁移,最后过程/查询适配。
  • 问:怎么评估迁移工作量?答:按过程数量、复杂查询、专用函数与外部依赖盘点。

05.4.2.4-017 什么场景应该选 SQL Server?

频率 ★★★☆☆ | 难度 进阶

一句话结论

Windows 生态、强企业级集成、需要成熟高可用/列存分析与商业支持的场景适合 SQL Server;开源优先、轻量或特定扩展生态则考虑 MySQL/PG。

概念解释

  • 适合:.NET/Windows 栈、企业级 OLTP + 报表混合、需要 AG 高可用与商业支持。
  • 权衡:授权成本、平台绑定、开源生态与扩展丰富度。
  • 选型应结合团队经验、现有运维体系与成本约束。

原理流程

数据形态 + 一致性要求 + 团队技能 + 成本/授权 + 生态集成 → 选型

代码 / 命令

-- 记录当前实例版本与版本号作为选型基线
SELECT @@VERSION AS version, SERVERPROPERTY('ProductVersion') AS product_version;

常见误区

  • 误区:关系型数据库性能排序有绝对高低。正解:各库适配场景不同,性能需按 workload 实测。
  • 误区:选库只看功能清单。正解:运维经验、生态与长期成本同样关键。

追问

  • 问:强事务复杂报表混合怎么选?答:结合列存/内存表能力与现有技术栈评估。
  • 问:开源诉求强烈时优先谁?答:MySQL/PG 生态更开放,按查询模式与团队熟悉度选择。

05.4.2.4-018 版本特性边界有哪些需要标注"待确认"的地方?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

列存混合模式、内存表语法子集、异步统计、CE 版本、联机重建限制等行为随版本变化,面试与文档中不确定处标注"待确认"并给出验证点。

概念解释

  • 需核对项:起始版本、兼容级别、受限语法、工具支持。
  • 验证点:@@VERSION、兼容级别、官方特性清单、目标环境实测。

原理流程

不确定行为 → 标注待确认 → 列验证命令/文档位置 → 目标环境核对

代码 / 命令

SELECT @@VERSION AS version,
       SERVERPROPERTY('ProductLevel') AS patch_level,
       SERVERPROPERTY('Edition') AS edition;

常见误区

  • 误区:凭记忆断言某特性从某版本可用。正解:以官方文档与目标实例核对为准。
  • 误区:开发库行为等于生产行为。正解:补丁级别与兼容级别也会影响行为。

追问

  • 问:待确认内容在题库中怎么写?答:明确写出需要验证的点,不编造版本号与输出。
  • 问:怎么持续更新版本差异?答:升级后回归核对关键特性与计划行为。

05.4.2.4-019 备份恢复与 PITR 的基本流程是什么?

频率 ★★★☆☆ | 难度 进阶

一句话结论

完整备份为基线,差异备份减少重还原量,日志备份链支撑时点恢复;还原按完整 → 差异 → 日志顺序执行。

概念解释

  • 完整备份:库基线。
  • 差异备份:自上次完整后的变更。
  • 日志备份:完整模式下周期备份日志,支持 PITR。
  • 还原顺序:完整(NORECOVERY)→ 差异(NORECOVERY)→ 日志到目标时点。

原理流程

备份:完整 → 周期差异 → 频繁日志
恢复:完整 NORECOVERY → 差异 NORECOVERY → 日志链 STANDBY/RECOVERY

代码 / 命令

-- 备份
BACKUP DATABASE DemoDB TO DISK = N'/backup/full.bak';
BACKUP LOG DemoDB TO DISK = N'/backup/log.trn';

-- 还原(示例顺序)
RESTORE DATABASE DemoDB FROM DISK = N'/backup/full.bak' WITH NORECOVERY;
RESTORE LOG DemoDB FROM DISK = N'/backup/log.trn'
WITH RECOVERY, STOPAT = N'2026-11-18 10:00:00';

常见误区

  • 误区:简单恢复模式也能做 PITR。正解:简单模式无日志备份链,不能时点恢复。
  • 误区:还原完立即可读再还原后续日志。正解:后续日志需 NORECOVERY 状态下依次还原。

追问

  • 问:尾部日志(Tail Log)是什么?答:故障后备份活动日志末尾,保证不丢已提交事务。
  • 问:AG 副本备份与主库备份冲突吗?答:可在副本备份,纳入同一日志链管理。

05.4.2.4-020 监控与诊断有哪些常用手段?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

核心手段包括扩展事件(XE)、等待类型 DMV、性能计数器、Query Store 与 DBCC 校验;Profiler 为旧工具,现代以 XE 为主。

概念解释

  • 扩展事件:轻量采集等待、慢查询、死锁等事件。
  • 等待 DMV:sys.dm_os_wait_stats 分类定位瓶颈。
  • Query Store:计划与运行回归。
  • DBCC:CHECKDB 完整性、页面与空间诊断。

原理流程

现象 → 等待类型 → XE/QS 采集语句 → 计划/IO 分析 → 定位资源或计划问题

代码 / 命令

-- 等待排行
SELECT  wait_type, waiting_tasks_count, wait_time_ms, average_wait_time_ms
FROM    sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

-- 数据库完整性校验
DBCC CHECKDB (N'DemoDB');

常见误区

  • 误区:Profiler 仍是首选采集工具。正解:Profiler 开销大且已被 XE 取代方向。
  • 误区:单次等待快照能下结论。正解:需周期采样对比趋势。

追问

  • 问:LCK_M_* 与 PAGEIOLATCH_* 区别?答:前者锁等待,后者内存/IO 闩锁等待。
  • 问:DBCC CHECKDB 平时要常跑吗?答:按维护周期执行,大表耗时需低峰安排。

05.4.2.4-021 SQL Server 面试高频结论清单有哪些?

频率 ★★★☆☆ | 难度 基础

一句话结论

核心结论:8KB 页/64KB 区;一表一个聚集索引;键查找回表、INCLUDE 覆盖;锁 + RCSI/快照行版本;tempdb 共享承载版本与 spill;AG 日志复制 + 仲裁切换;计划靠统计信息与 QS 维稳。

概念解释

  • 存储:文件 → 区 → 页 → 行;堆与聚集索引表。
  • 索引:聚集叶子=数据;非聚集叶子=键/RID;包含列消回表。
  • 并发:锁模式/兼容性 + 行版本;长事务放大阻塞。
  • 高可用:副本同步模式、仲裁、监听器、RTO/RPO。
  • 优化:执行计划、统计估算、参数嗅探、Query Store。

原理流程

存储模型 → 索引访问 → 并发控制 → 高可用复制 → 计划优化

代码 / 命令

-- 快速核对基线
SELECT @@VERSION;
SELECT name, recovery_model_desc FROM sys.databases WHERE database_id > 4;

常见误区

  • 误区:背结论即可应对面试。正解:结论需配合命令、计划片段与场景判断复述。
  • 误区:SQL Server 知识点孤立记忆。正解:与 MySQL/PG 对照着记差异更牢。

追问

  • 问:被追问"和 MySQL 区别"怎么答?答:从页大小、表组织、日志、并发模型、高可用五维逐项对比。
  • 问:生产排查第一反查看什么?答:等待类型 + 实际计划 + 统计新鲜度,再深入具体机制。

参考来源