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 区别"怎么答?答:从页大小、表组织、日志、并发模型、高可用五维逐项对比。
- 问:生产排查第一反查看什么?答:等待类型 + 实际计划 + 统计新鲜度,再深入具体机制。
参考来源
- Microsoft Learn:列存索引:https://learn.microsoft.com/sql/relational-databases/indexes/columnstore-indexes-overview
- Microsoft Learn:内存优化表:https://learn.microsoft.com/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization
- Microsoft Learn:备份与还原:https://learn.microsoft.com/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases
- Microsoft Learn:扩展事件:https://learn.microsoft.com/sql/relational-databases/extended-events/extended-events