数据库基础体系 · 第 48/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL Server 数据库引擎：存储、事务日志、锁、索引和执行计划

SQL Server 数据库引擎可以从四条相互关联的路径理解：

数据如何存储：表、索引如何组织到数据页和文件中；
修改如何持久化：事务日志如何保证提交结果可恢复；
并发如何协调：锁与行版本如何控制多个事务的交错执行；
查询如何执行：索引、统计信息和优化器如何共同决定执行计划。

这几条路径不是相互独立的。例如，一条 UPDATE 既会修改数据页，也会生成日志；它需要获得锁，可能触发锁升级；提交后还可能因为索引维护产生额外写入。查询计划中的 Key Lookup 又可能导致大量随机页面访问，并延长锁持有时间。

一、数据库引擎中的基本对象和数据流

SQL Server 中常见的层次关系是：

实例
└── 数据库
    ├── 数据文件：.mdf / .ndf
    ├── 日志文件：.ldf
    ├── 文件组
    ├── 表
    └── 索引

1. 数据文件和日志文件

数据库至少包含：

一个主数据文件，通常扩展名为 .mdf；
零个或多个辅助数据文件，通常扩展名为 .ndf；
一个或多个事务日志文件，通常扩展名为 .ldf。

数据文件存储表、索引、系统目录等数据库对象。日志文件存储事务日志记录。日志文件不是普通的数据文件，也不是备份文件，不能通过“直接删除日志文件”来释放空间。

数据库文件属于文件组。文件组主要用于组织数据文件和控制对象的默认放置位置，但它不是事务边界，也不会让不同文件组自动形成独立的恢复单元。

查看数据库文件：

SELECT
    DB_NAME(database_id) AS database_name,
    name AS logical_name,
    type_desc,
    physical_name,
    size * 8 / 1024 AS size_mb,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'YourDatabase');

其中：

size 的单位是 8 KB 页面；
type_desc = ROWS 表示数据文件；
type_desc = LOG 表示日志文件；
growth 的解释依赖 is_percent_growth：可以是页面数，也可以是百分比。

生产环境中，文件自动增长是兜底机制，不应被当作正常容量规划。增长操作可能产生 I/O 延迟，日志增长还可能阻塞需要日志空间的事务。

二、页、区和表的存储方式

1. 8 KB 数据页

SQL Server 的数据和索引通常以 8 KB 页为基本 I/O 和分配单位。

一个页包含：

页头
记录区域
行偏移数组

页头记录页类型、对象标识、页链等信息；行偏移数组帮助引擎定位页内记录。一个 8 KB 页并不意味着用户可用空间正好是 8192 字节，因为页头和其他结构会占用空间。

SQL Server 的行不能正常地跨越多个数据页存放。对于可变长度列和较大值类型，实际存储可能使用行外结构，例如行外 LOB 数据或行溢出数据。因而“查询一行”未必只访问一个页。

2. 区：8 个连续页

一个区（extent）由 8 个连续页组成，共 64 KB：

8 \times 8\text{ KB} = 64\text{ KB}

区用于页面分配。早期 SQL Server 版本中，区分为混合区和统一区；现代版本对小对象分配策略已经有变化，因此不应简单地把“每个对象总是独占一个区”作为不变规则。理解区的主要目的，是理解空间分配和分配竞争，而不是推断每张表的物理连续性。

3. 堆和聚集索引

没有聚集索引的表称为堆（heap）。堆中的数据行没有由索引键定义的逻辑顺序。非聚集索引需要使用 RID（Row Identifier）定位堆中的行。

有聚集索引的表，其数据行存储在聚集索引的叶级结构中。聚集索引不是“额外复制一份数据”，而是决定表数据本身的主要存储组织方式。

例如：

CREATE TABLE dbo.Customer
(
    CustomerID int NOT NULL,
    CustomerName nvarchar(100) NOT NULL,
    Email nvarchar(200) NULL,
    CreatedAt datetime2(3) NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerID)
);

这里：

CustomerID 是聚集索引键；
聚集索引的叶级页包含表行；
CustomerID 越宽，引用该聚集索引的非聚集索引通常也会越宽，因为非聚集索引叶级记录需要保存聚集键作为行定位器。

这也是聚集键设计会影响所有非聚集索引大小的原因之一。

4. 行迁移与堆上的 forwarded record

堆中的可变长度行如果更新后无法放回原页，SQL Server 可能把行移动到其他页，并在原位置留下 forwarded record。以后通过堆上的 RID 查找时，可能需要先访问原位置，再跳转到新位置。

这会增加随机 I/O 和访问成本。聚集索引也可能发生页分裂，但其结构和堆上的 forwarded record 不是同一个机制。

三、缓冲池：磁盘页如何参与查询

SQL Server 不会让每次查询都直接从数据文件读取页面，而是通过内存中的 **Buffer Pool（缓冲池）**缓存数据页和索引页。

一次读取大致经过以下过程：

查询执行算子请求某个页面；
引擎检查页面是否已经在缓冲池中；
如果命中，直接从内存读取；
如果未命中，从数据文件读取到缓冲池；
页面被访问或修改；
修改后的页面成为 dirty page；
后续由检查点、惰性写入器等机制写回数据文件。

数据页写回磁盘不等于事务已经提交。事务提交的关键约束是：

在事务提交成功返回之前，相关日志记录必须已经持久化到日志文件。

因此，数据库可以先把 dirty data page 留在内存里，而不能在日志还没有安全写入时确认提交。

1. 逻辑读与物理读

可以使用以下命令观察语句的 I/O：

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT CustomerID, CustomerName
FROM dbo.Customer
WHERE CustomerID = 100;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

典型输出会包含：

logical reads：从缓冲池读取了多少个页；
physical reads：需要从磁盘读取了多少个页；
CPU time；
elapsed time。

physical reads = 0 不代表查询没有成本，它可能只是数据已经在内存中。反过来，第一次执行产生物理读，也不代表查询计划一定低效。

四、事务日志：修改为什么能够恢复

1. 日志先行写入

SQL Server 遵循 **Write-Ahead Logging（WAL，预写日志）**思想。简化后的修改流程是：

事务执行 UPDATE
    ↓
生成日志记录，写入内存中的日志缓冲区
    ↓
必要时将日志缓冲区刷新到 .ldf
    ↓
事务 COMMIT 返回成功
    ↓
数据页可在之后再写入 .mdf/.ndf

日志记录描述了恢复所需的信息。它不一定是“把完整 SQL 文本保存下来”，而是记录足以支持恢复的物理或逻辑变化。

关键约束是：

\text{提交成功} \Rightarrow \text{相关日志记录已持久化}

而不是：

\text{提交成功} \Rightarrow \text{所有数据页已写入数据文件}

2. 恢复的三个阶段

数据库启动或恢复时，可以抽象为三个阶段：

Analysis（分析）
确定哪些事务已经提交，哪些事务在故障时仍未完成，并定位恢复所需的日志范围。
Redo（重做）
将日志中已经发生的修改重新应用到数据页，确保已提交修改最终体现出来。
Undo（撤销）
撤销故障发生时尚未提交的事务。

例如：

T1 修改 A，写日志
T1 COMMIT
T2 修改 B，写日志
服务器突然断电

恢复后：

T1 的修改必须保留；
T2 的修改必须被撤销；
即使 T1 修改过的数据页在断电前尚未写入数据文件，也可以依靠日志重做。

事务日志中的 LSN（Log Sequence Number）用于标识日志记录的顺序。恢复、日志备份和日志截断都依赖日志链和日志记录状态。

3. 日志截断不是日志文件收缩

日志文件空间有两个不同概念：

已分配文件大小：.ldf 文件当前占用的磁盘空间；
可复用日志空间：旧日志记录已经不再需要，可以被后续事务覆盖。

日志截断通常只是让日志空间可复用，不会自动缩小 .ldf 文件的物理大小。

可以查看日志使用情况：

USE YourDatabase;
GO

SELECT
    total_log_size_in_bytes / 1024 / 1024 AS total_log_mb,
    used_log_space_in_bytes / 1024 / 1024 AS used_log_mb,
    used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

日志无法截断的常见原因包括：

长时间运行的事务仍然打开；
Always On 等高可用副本尚未重做或截取日志；
事务复制、数据库镜像等消费者仍需要日志；
在完整恢复模式下，日志备份链未按计划进行。

恢复模式决定日志管理方式：

简单恢复模式：日志在满足条件后自动截断，但不能使用日志备份把数据库恢复到任意时间点；
完整恢复模式：需要定期日志备份，否则日志可能持续增长；
大容量日志恢复模式：某些大容量操作可以减少日志量，但是否能实现更细粒度恢复取决于具体操作和备份条件。

日志备份不是“清空日志”。日志备份复制日志记录以支持恢复链，并可能使一部分日志变为可复用。

4. 事务提交与日志刷新示例

USE YourDatabase;
GO

CREATE TABLE dbo.Account
(
    AccountID int NOT NULL PRIMARY KEY,
    Balance decimal(12, 2) NOT NULL
);
GO

INSERT INTO dbo.Account(AccountID, Balance)
VALUES (1, 1000.00), (2, 500.00);
GO

BEGIN TRANSACTION;

UPDATE dbo.Account
SET Balance = Balance - 100.00
WHERE AccountID = 1;

UPDATE dbo.Account
SET Balance = Balance + 100.00
WHERE AccountID = 2;

COMMIT TRANSACTION;

这段代码表达的是一个转账事务：

\Delta Balance_1=-100,\quad \Delta Balance_2=+100

如果第二条 UPDATE 失败，应用必须回滚第一条修改，否则余额总和会改变。SQL Server 通过日志恢复数据库状态，但“是否把两个业务动作放入同一事务”仍然是应用和数据库设计共同决定的语义。

更完整的错误处理：

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Account
    SET Balance = Balance - 100.00
    WHERE AccountID = 1;

    IF @@ROWCOUNT <> 1
        THROW 50001, '源账户不存在或更新行数不正确', 1;

    UPDATE dbo.Account
    SET Balance = Balance + 100.00
    WHERE AccountID = 2;

    IF @@ROWCOUNT <> 1
        THROW 50002, '目标账户不存在或更新行数不正确', 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

这里：

SET XACT_ABORT ON 使许多运行时错误导致当前事务终止；
XACT_STATE() 为 1 表示事务可提交，为 -1 表示事务不可提交但可以回滚，为 0 表示没有事务；
THROW 保留原始错误上下文并把错误继续交给调用方。

五、锁：并发事务如何互相约束

1. 锁的目的

锁用于保证并发事务满足所选隔离级别的约束。它不是“防止所有并发”，而是限制冲突操作的交错方式。

常见锁模式包括：

锁模式	主要含义
`S`	共享锁，通常用于读取
`X`	排他锁，用于修改
`U`	更新锁，减少读取后再修改时的转换死锁
`IS`	意向共享锁
`IX`	意向排他锁
`SIX`	共享加意向排他锁
`Sch-S`	架构稳定锁
`Sch-M`	架构修改锁

共享锁和排他锁不兼容：

事务 T1 持有 S，事务 T2 请求 X  → T2 等待
事务 T1 持有 X，事务 T2 请求 S  → T2 等待
事务 T1 持有 X，事务 T2 请求 X  → T2 等待

意向锁表示更低层级存在锁。例如，一个事务在某些行上加了排他锁，表级可能出现 IX，用于告诉其他事务：不要直接对整张表申请与这些行冲突的表级锁。

2. 锁资源不只包括“行”

常见资源层级有：

数据库；
表；
页；
键；
行；
索引键范围；
元数据和架构资源。

在聚集索引上看到的“键锁”锁定的是索引键资源，不应简单理解为堆表中的物理行。堆表通常使用 RID 作为行定位资源。

SQL Server 可以使用行锁、页锁或表锁。锁粒度并不是由 SQL 文本直接固定决定的，优化器和锁管理器会综合访问方式、成本和内存压力做决定。

3. 锁升级

当一个事务持有大量较细粒度锁时，SQL Server 可能将其升级为更粗粒度锁，例如表锁，以降低锁管理开销。

锁升级的意义是：

\text{大量行锁的管理成本} > \text{一个表锁带来的并发损失}

但锁升级不是简单的固定“超过某个精确行数就一定发生”。具体行为受版本、分区、内存压力、语句和锁资源等因素影响。不能依靠某个传闻中的固定阈值设计并发控制。

锁提示也不是无条件解决方案。例如：

SELECT *
FROM dbo.Account WITH (ROWLOCK, UPDLOCK)
WHERE AccountID = 1;

UPDLOCK 可以在“先读取、后修改”的模式中提前使用更新锁；ROWLOCK 只是请求行级锁，不是绝对保证。锁提示可能提高锁数量、导致死锁或增加内存压力，必须结合执行计划和并发路径验证。

六、事务隔离级别与行版本

隔离级别决定一个事务可以观察到什么，以及读取是否使用锁或行版本。

1. Read Uncommitted

允许读取其他事务尚未提交的修改，即脏读：

T1: UPDATE A = 100，尚未 COMMIT
T2: SELECT A
T1: ROLLBACK

T2 读到了最终不存在的值。WITH (NOLOCK) 常被用于请求这种语义，但它不仅可能读到脏数据，还可能遇到重复行、漏行或读取结构不稳定的数据。它不是“无锁高性能读取”的可靠替代品；即使使用 NOLOCK，查询仍可能受到架构锁影响。

2. Read Committed

SQL Server 的传统锁定式 READ COMMITTED 通常在读取时使用共享锁，并在读取完成后释放；修改锁通常持续到事务结束。

它避免脏读，但不保证同一个事务内重复读取结果相同：

T1: SELECT A  -- 读到 10，语句结束
T2: UPDATE A = 20，COMMIT
T1: SELECT A  -- 可能读到 20

数据库也可以启用 READ_COMMITTED_SNAPSHOT，使 READ COMMITTED 使用行版本而不是读取共享锁。此时语义仍是语句级读提交，但并发行为和 tempdb、版本存储压力会变化。

3. Repeatable Read

事务读取过的行，在事务结束前不能被其他事务修改，因此同一事务重复读取这些行可以得到相同结果。但它不一定阻止其他事务插入满足条件的新行。

4. Serializable

Serializable 需要防止：

脏读；
不可重复读；
幻读。

它不仅锁住已存在的键，还可能锁住索引键范围。例如：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT *
FROM dbo.Account
WHERE AccountID BETWEEN 1 AND 10;

-- 其他事务通常不能在这个范围内插入满足条件的新键

COMMIT TRANSACTION;

如果存在合适的索引，范围锁可以更准确地作用于索引键范围；缺少索引时，SQL Server 可能需要扫描更多数据并持有更宽的锁，代价显著增加。

5. Snapshot

Snapshot 隔离为事务建立一致性视图，读取历史行版本，通常不因读取而阻塞写入。写入冲突在提交时检测。

行版本需要额外空间。传统版本存储主要使用 tempdb；启用加速数据库恢复（ADR）等能力后，版本存储的具体位置和恢复机制可能出现差异，不能把所有版本数据都简单归因于单一位置。

版本隔离减少读写阻塞，但并不等于没有并发冲突：

版本生成会增加写入成本；
长事务会延长旧版本保留时间；
两个事务修改同一行仍可能发生更新冲突；
tempdb 或持久版本存储空间需要监控。

七、死锁：等待图中的环

阻塞是等待链，死锁则是等待图中出现环：

T1 持有 A，等待 B
T2 持有 B，等待 A

例如：

-- 会话 1
BEGIN TRANSACTION;
UPDATE dbo.Account SET Balance = Balance - 10 WHERE AccountID = 1;
WAITFOR DELAY '00:00:05';
UPDATE dbo.Account SET Balance = Balance + 10 WHERE AccountID = 2;
COMMIT;

-- 会话 2
BEGIN TRANSACTION;
UPDATE dbo.Account SET Balance = Balance - 20 WHERE AccountID = 2;
WAITFOR DELAY '00:00:05';
UPDATE dbo.Account SET Balance = Balance + 20 WHERE AccountID = 1;
COMMIT;

如果两个会话在同一时间执行，可能形成：

会话 1 持有 AccountID=1，等待 AccountID=2
会话 2 持有 AccountID=2，等待 AccountID=1

SQL Server 的死锁检测器会选择一个事务作为牺牲者回滚，另一个事务继续执行。应用必须捕获死锁错误并在适当情况下重试；重试必须有次数上限、退避策略，并确保事务已经结束。

降低死锁概率的核心不是盲目增加锁提示，而是：

让相关事务以一致顺序访问资源；
缩短事务范围；
避免在事务中执行不必要的外部调用；
为定位条件提供合适索引，减少锁住的行数；
捕获并分析死锁图，而不是只看应用端的超时异常。

可以使用扩展事件收集 xml_deadlock_report。死锁图中应重点看：

每个进程已持有的锁；
每个进程正在等待的锁；
访问对象和索引；
事务语句和执行计划；
最终被选为 victim 的事务。

八、索引：从访问路径到 B+Tree

1. B+Tree 的基本结构

SQL Server 的行存储索引通常采用 B+Tree 类结构：

根节点
  ↓
中间节点
  ↓
叶节点

非叶节点保存键值和子节点指针；叶节点保存：

聚集索引：完整表行；
非聚集索引：索引键、包含列和行定位器；
堆上的非聚集索引：RID；
有聚集索引的表上的非聚集索引：聚集键。

B+Tree 的查询成本通常与树高相关：

\text{查找成本} \approx h + \text{叶级范围扫描成本}

其中 $h$ 是从根到叶的层数。对于等值查询，理想路径是根节点到一个叶级位置；对于范围查询，还需要沿叶级链访问连续范围。

SQL Server 的 B+Tree 页面在逻辑上按键排序，但物理页面不一定在磁盘上连续。聚集索引提供的是逻辑访问顺序，不是“磁盘文件中永远连续排列”。

2. 非聚集索引与 Key Lookup

创建索引：

CREATE INDEX IX_Customer_Email
ON dbo.Customer(Email);

执行：

SELECT CustomerName, CreatedAt
FROM dbo.Customer
WHERE Email = N'a@example.com';

索引只包含：

Email；
行定位器。

但查询还需要 CustomerName 和 CreatedAt。典型计划可能是：

Index Seek
    ↓
Key Lookup
    ↓
Nested Loops

Key Lookup 需要根据非聚集索引找到的行定位器，再回到聚集索引读取剩余列。返回行数少时，这通常可以接受；返回行数很多时，大量 Lookup 会变成重复的随机访问。

3. 覆盖索引

可以把查询所需的非谓词列作为包含列：

CREATE INDEX IX_Customer_Email_Covering
ON dbo.Customer(Email)
INCLUDE (CustomerName, CreatedAt);

此时索引叶级已经包含查询需要的列，计划可能只需要 Index Seek，不再回表。

但覆盖索引不是免费优化：

索引占用额外空间；
INSERT 需要维护它；
修改 Email、CustomerName 或 CreatedAt 可能需要更新它；
写入产生更多日志；
过宽的索引会降低缓存命中率和写入吞吐。

因此覆盖索引的本质是：

\text{减少读取路径成本} \quad \text{交换为} \quad \text{增加存储、写入和维护成本}

4. 联合索引和左侧匹配

建立：

CREATE INDEX IX_Order_Customer_Date
ON dbo.Orders(CustomerID, OrderDate);

它天然适合：

WHERE CustomerID = 10

也适合：

WHERE CustomerID = 10
  AND OrderDate >= '2025-01-01';

因为索引键的排序顺序是：

(CustomerID, OrderDate)

首先按 CustomerID 分组，在同一个客户组内再按 OrderDate 排序。

但它通常不适合仅按第二列查找：

WHERE OrderDate >= '2025-01-01';

此时没有前导列 CustomerID 的边界，优化器可能选择扫描整个索引，而不是高效 Seek。

联合索引列顺序不能只按“选择性从高到低”机械排列。应先看查询谓词和排序需求：

等值条件通常可以放在前面；
范围条件会缩小后续键的 Seek 能力；
ORDER BY、GROUP BY 可能改变合理顺序；
索引还要考虑写入和其他查询复用。

5. SARGable 谓词

SARGable 谓词是可以有效利用索引搜索边界的谓词。例如：

-- 通常更容易使用 OrderDate 的索引
WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2025-02-01'

下面这种写法可能对列应用函数，使索引难以建立直接搜索边界：

WHERE CONVERT(date, OrderDate) = '2025-01-01'

可以改写为半开区间：

WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2025-01-02'

但“出现函数就一定不能 Seek”也不严谨。优化器可能对某些表达式进行转换，或者使用计算列索引。最终应检查实际执行计划，而不能只按 SQL 外观判断。

6. 隐式转换

列类型和参数类型不一致时，可能发生隐式转换：

-- 假设 CustomerID 是 int
DECLARE @id nvarchar(20) = N'100';

SELECT *
FROM dbo.Customer
WHERE CustomerID = @id;

转换方向取决于类型优先级和表达式，可能导致列一侧发生转换，降低索引使用效率，也可能造成语义问题。应用参数应尽量使用与列一致的数据类型。

7. 堆、聚集索引和索引选择的边界

索引不是越多越好。

如果查询返回表中很大比例的数据，顺序扫描可能比大量随机 Lookup 更便宜。一个索引查找只有在“跳过的页面足够多、返回结果足够少”时才有优势。

此外：

聚集键过宽会扩大所有非聚集索引；
随机插入的聚集键可能增加页分裂；
单调递增键减少中间页分裂，但可能让最后一页成为热点；
删除和更新也需要维护所有相关索引；
在线重建、离线重建和空间需求取决于版本、索引类型、选项和部署环境。

8. 列存储索引

对于大量数据分析，列存储索引采用按列组织和压缩的存储方式，适合扫描少数列、聚合大量行的场景。它与传统行存储 B+Tree 的目标不同：

行存储适合点查、短范围查询和事务型更新；
列存储适合大范围扫描、聚合和压缩；
列存储不是所有 OLTP 查询的替代品；
更新、删除和增量装载会涉及增量存储结构及压缩过程。

因此“索引”不只是一种 B+Tree；选择行存储、列存储、聚集或非聚集结构，必须结合访问模式。

九、统计信息：优化器如何估算结果集

执行计划选择依赖基数估算（cardinality estimation）。优化器需要估计谓词会返回多少行：

\hat{N} = N \times \hat{S}

其中：

$N$ 是输入表或索引的行数；
$\hat{S}$ 是优化器估计的选择率；
$\hat{N}$ 是估计返回行数。

统计信息通常包含：

总行数；
不同值数量等密度信息；
直方图，用于描述键值分布。

例如，若优化器估计：

返回 1 行

它可能倾向于：

Index Seek + Key Lookup

若估计：

返回 100 万行

它可能选择：

Index/Table Scan

估算错误会连锁影响：

Join 算法；
Join 顺序；
并行度；
内存授予；
是否使用 Lookup；
是否发生排序或哈希溢出。

数据分布变化、统计信息过旧、相关列之间存在相关性、参数值具有明显倾斜，都可能造成估算偏差。

十、执行计划：SQL 文本如何变成物理执行

1. 从逻辑操作到物理算子

SQL 描述的是关系操作，例如：

SELECT c.CustomerName, COUNT(*)
FROM dbo.Orders AS o
JOIN dbo.Customer AS c
  ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2025-01-01'
GROUP BY c.CustomerName;

优化器需要将它转换为物理执行计划，例如：

索引扫描或查找
    ↓
过滤
    ↓
连接
    ↓
聚合
    ↓
排序或输出

常见物理算子包括：

Index Seek：在索引中定位范围；
Index Scan：扫描索引叶级；
Table Scan：扫描堆；
Clustered Index Scan：扫描聚集索引；
Nested Loops：外侧每得到一行，就驱动内侧查找；
Hash Match：建立哈希表后进行连接或聚合；
Merge Join：利用两侧有序输入进行连接；
Sort：排序；
Stream Aggregate：利用有序输入聚合；
Hash Aggregate：通过哈希结构聚合。

2. 三种连接算法

设外侧输入有 $M$ 行，内侧输入有 $N$ 行。

Nested Loops

如果外侧行数很少，且内侧有高效索引：

\text{成本} \approx M \times \text{内侧查找成本}

适合点查或小结果集。如果优化器误以为 $M$ 很小，但实际返回很多行，就可能出现大量 Key Lookup 或重复扫描。

Hash Join

Hash Join 通常先读取较小输入建立哈希表，再扫描另一侧探测：

\text{成本} \approx M + N

它适合较大的无序输入，但需要内存。内存不足时可能产生 spill，将临时数据写入 tempdb。

Merge Join

Merge Join 需要两侧按连接键有序：

\text{成本} \approx \text{排序成本} + M + N

如果两侧已有合适索引顺序，能够避免排序；否则排序本身可能成为主要成本。

3. 估计计划与实际计划

查看估计计划：

SET SHOWPLAN_TEXT ON;
GO

SELECT CustomerName
FROM dbo.Customer
WHERE Email = N'a@example.com';
GO

SET SHOWPLAN_TEXT OFF;
GO

SHOWPLAN 只生成计划，不执行语句。执行它需要相应权限，并且 SET SHOWPLAN_TEXT ON 后后续语句会被解释为计划请求，必须按示例关闭。

更常用的是在 SSMS 中启用“包括实际执行计划”，或者使用：

SET STATISTICS XML ON;

SELECT CustomerName
FROM dbo.Customer
WHERE Email = N'a@example.com';

SET STATISTICS XML OFF;

实际计划可比较：

Estimated Number of Rows；
Actual Number of Rows；
Estimated Number of Executions；
实际 I/O 和 CPU；
是否出现黄色警告；
是否有 Sort/Hash spill；
是否存在大量 Lookup；
并行算子是否带来等待和协调开销。

看到 Index Scan 不能直接判定为错误，看到 Index Seek 也不能直接判定为高效。真正需要比较的是：

估算行数与实际行数
页面读取量
CPU 与耗时
内存授予和溢出
锁等待与并发影响

4. 编译、计划缓存和参数敏感

SQL Server 通常会编译查询并缓存执行计划。编译时会使用：

SQL 文本和参数；
统计信息；
可用索引；
参数值；
优化器规则和成本模型；
资源配置。

参数化查询可能出现参数敏感问题（常称 parameter sniffing）：

CREATE OR ALTER PROCEDURE dbo.GetOrders
    @CustomerID int
AS
BEGIN
    SELECT OrderID, OrderDate, Amount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID;
END;

如果某个客户只有少量订单，另一个客户有大量订单，那么：

少量结果可能适合 Index Seek + Lookup；
大量结果可能适合扫描或不同连接策略。

若缓存计划主要基于第一个参数编译，后续参数可能复用并不合适的计划。诊断时应先确认：

实际行数和估计行数是否严重偏离；
不同参数值是否需要不同访问路径；
统计信息是否合理；
计划是否因结构、参数或资源变化而重新编译。

不能一看到性能问题就使用 OPTION (RECOMPILE)。它可能解决单次参数选择问题，但会增加编译成本，并减少计划复用。强制计划、优化提示和重编译都应建立在实际诊断之上。

十一、一个可运行的索引与计划实验

下面的示例在测试数据库中创建订单表，观察联合索引、覆盖列和执行计划。

USE YourDatabase;
GO

DROP TABLE IF EXISTS dbo.OrderDemo;
GO

CREATE TABLE dbo.OrderDemo
(
    OrderID int NOT NULL,
    CustomerID int NOT NULL,
    OrderDate date NOT NULL,
    Amount decimal(12, 2) NOT NULL,
    StatusCode tinyint NOT NULL,
    CONSTRAINT PK_OrderDemo PRIMARY KEY CLUSTERED (OrderID)
);
GO

;WITH N AS
(
    SELECT TOP (100000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT dbo.OrderDemo
(
    OrderID, CustomerID, OrderDate, Amount, StatusCode
)
SELECT
    n,
    ((n - 1) % 1000) + 1,
    DATEADD(day, -(n % 730), CONVERT(date, SYSDATETIME())),
    CONVERT(decimal(12, 2), ((n % 50000) / 100.0) + 1),
    CONVERT(tinyint, n % 4)
FROM N;
GO

CREATE INDEX IX_OrderDemo_Customer_Date
ON dbo.OrderDemo(CustomerID, OrderDate)
INCLUDE (Amount, StatusCode);
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderDate, Amount, StatusCode
FROM dbo.OrderDemo
WHERE CustomerID = 42
  AND OrderDate >= DATEADD(day, -30, CONVERT(date, SYSDATETIME()))
  AND OrderDate <  DATEADD(day, 1, CONVERT(date, SYSDATETIME()));

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

这个实验中，索引键是：

(CustomerID, OrderDate)

查询首先用 CustomerID = 42 确定索引中的客户范围，再在该范围内使用日期条件。Amount 和 StatusCode 是 INCLUDE 列，因此查询不需要返回聚集索引读取这些列。

可以进一步测试只使用第二列的条件：

SET STATISTICS IO ON;

SELECT OrderDate, Amount
FROM dbo.OrderDemo
WHERE OrderDate >= DATEADD(day, -30, CONVERT(date, SYSDATETIME()));

SET STATISTICS IO OFF;

这个查询没有限制 CustomerID，联合索引的前导边界不完整。优化器可能扫描较大范围，甚至认为扫描聚集索引更便宜。两种结果都可能是合理计划，最终要结合实际计划和 logical reads 判断。

测试结束后清理对象：

DROP TABLE dbo.OrderDemo;

不要在生产库直接执行清理语句。索引创建也可能读取大量数据、占用空间并产生日志；在线或离线操作的可用性和资源代价取决于 SQL Server 版本、版本 edition、索引类型、选项和部署环境。

十二、把一次 UPDATE 串起来看

考虑如下语句：

UPDATE dbo.OrderDemo
SET Amount = Amount + 10
WHERE CustomerID = 42
  AND OrderDate >= '2025-01-01';

引擎可能经历如下过程：

第一步：编译和优化

优化器读取统计信息，估计满足条件的行数，然后选择：

扫描聚集索引；
扫描或查找非聚集索引；
使用何种连接或更新方式；
是否并行。

第二步：获取锁

执行器对要修改的资源申请更新锁和排他锁。具体锁粒度取决于访问路径、隔离级别、提示和资源状态。

第三步：读取页面

所需的数据页进入缓冲池。找到目标行后，修改内存中的页面，页面成为 dirty page。

第四步：生成日志

更新数据行和维护相关索引都会产生事务日志记录。即使用户只更新一列，受影响的非聚集索引也可能需要更新。

第五步：提交

COMMIT 前，相关日志必须刷新到日志文件。数据页可以稍后写回数据文件。

第六步：并发与恢复

如果另一个事务读取或修改同一资源：

锁定式隔离级别下可能等待；
行版本隔离下可能读取旧版本；
锁顺序不一致时可能死锁；
断电时由日志恢复已提交和未提交状态。

这说明查询计划、索引、锁和日志是同一条执行链上的不同观察角度。

十三、常见误解与诊断边界

1. “提交成功说明数据页已经写盘”

不准确。提交成功首先说明必要日志已经持久化，数据页可能仍在缓冲池中。正因为有 WAL，SQL Server 才能把数据页写回延后。

2. “事务日志就是操作历史”

不准确。事务日志的首要职责是支持事务一致性、回滚和崩溃恢复。它不是面向用户查询的审计日志，也不等同于完整的业务操作记录。

需要业务审计时，应设计独立审计机制，并明确审计数据的事务一致性、保留时间和访问控制。

3. “NOLOCK 可以解决阻塞”

不准确。它允许较弱的读取语义，可能产生脏读、漏读和重复读；还可能受到架构锁影响。应根据业务是否允许旧版本读取，考虑 RCSI 或 Snapshot，而不是把 NOLOCK 作为默认优化手段。

4. “有索引就一定使用 Seek”

不准确。优化器会比较访问成本。返回大量行时，扫描可能更便宜；谓词不可搜索、类型转换、统计信息错误或索引列顺序不合适，也可能使索引无法提供有效的 Seek。

5. “执行计划显示 Seek 就一定快”

不准确。一个 Seek 后面可能有数百万次 Key Lookup。应该查看实际行数、执行次数和逻辑读，而不是只看算子名称。

6. “日志文件增长就收缩”

通常不应这样处理。正确的问题是：为什么日志无法截断，还是一次业务操作确实需要更多日志空间？

诊断应先查看：

当前恢复模式；
日志使用率；
log_reuse_wait_desc；
是否有长事务；
日志备份是否正常；
高可用副本是否滞后；
是否存在大批量操作。

示例：

SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();

收缩只能改变已分配文件的物理大小，不能修复导致日志持续增长的根因。频繁收缩再增长通常会制造更多运维和 I/O 问题。

7. “阻塞和死锁是一回事”

阻塞是等待，死锁是循环等待。阻塞可能最终解除；死锁检测器必须终止其中一个事务。诊断时应区分：

谁持有锁；
谁在等待；
等待了多久；
是否形成环；
执行计划是否导致访问范围过大；
事务是否过长。

十四、生产问题的最小诊断路径

当一条 SQL 变慢或出现并发问题时，可以按以下因果顺序检查：

确认语义
查询是否应该读取当前值、已提交值还是事务一致性快照？
确认实际执行计划
看访问路径、估计行数与实际行数、Lookup、排序、哈希溢出和并行。
确认 I/O 和 CPU
使用 STATISTICS IO/TIME 或 Query Store 等工具观察逻辑读、CPU 和耗时。
确认统计信息
数据分布是否发生变化，统计信息是否能反映当前基数。
确认锁和等待
是锁等待、I/O 等待、内存授予等待，还是 CPU 饱和？不要仅凭“查询慢”归因于索引。
确认日志和事务边界
是否有长事务、批量更新、日志增长或版本存储积压？
验证修改后的副作用
新索引是否减少读取，却增加了写入、日志、空间和维护成本？改变隔离级别是否把阻塞转化成版本存储压力？

SQL Server 的存储、日志、锁、索引和执行计划共同构成数据库引擎的工作基础：

数据页和索引决定数据如何被定位；
缓冲池决定页面如何在内存和磁盘之间流动；
事务日志决定提交结果如何在故障后重建；
锁和行版本决定并发事务可以如何交错；
统计信息和优化器决定查询采用哪条访问路径；
执行计划的真实质量必须由实际行数、I/O、CPU、等待和恢复代价共同验证。

掌握这些机制后，索引设计不再只是“给查询列加索引”，事务也不再只是“用 BEGIN TRANSACTION 包起来”，而可以从数据流、并发状态和故障路径解释每一个性能与一致性结果。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。