23、Azure SQL 存储结构与性能优化全解析

最新推荐文章于 2025-10-11 16:52:21 发布

echo99

最新推荐文章于 2025-10-11 16:52:21 发布

阅读量58

点赞数

CC 4.0 BY-SA版权

分类专栏：探索Azure SQL数据库的现代应用文章标签： Azure SQL Columnstore结构内存优化表

本文链接：https://blog.youkuaiyun.com/echo99/article/details/149549616

探索Azure SQL数据库的现代应用专栏收录该内容

28 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

Azure SQL 存储结构与性能优化全解析

在数据处理和管理领域，Azure SQL 提供了多种强大的存储结构和优化技术，以满足不同场景下的性能需求。本文将深入探讨 Azure SQL 的 Columnstore 结构、内存优化表等特性，以及它们在提升查询性能、数据处理效率方面的应用。

1. Columnstore 结构概述

Columnstore 结构是 Azure SQL 中用于优化报表和分析查询性能的重要技术。通过 Columnstore，数据以列的形式存储，而不是传统的行存储方式，这使得在处理大量数据时能够显著提高查询效率。

我们可以使用以下视图轻松查找 Azure SQL 数据库中现有的列段：

SELECT * FROM sys.columnstore_segments;

Columnstore 结构主要包含压缩区域和 Deltastore 区域。压缩区域应包含超过 95% 的数据，并且压缩数据不应被更改。因为修改压缩区域的数据时，Azure SQL 需要解压缩整个段中的所有值来更新一个值，然后再重新压缩所有内容，这会带来较大的性能开销。

Deltastore 区域以经典的行存储格式组织较小的行组，其主要目的是在新数据和更改的数据被压缩之前进行缓冲。所有新行都插入到行存储的 delta 区域，直到行组达到一百万行。当 delta 存储中的行组填满后，它将关闭以接受新的更改，并且后台进程会将该行组转换为 Columnstore 格式并移动到压缩区域。目标是尽可能将更多的行组从 delta 存储移动到压缩存储，delta 存储应包含少于 5% 的行。

2. Columnstore 结构的数据操作

2.1 数据插入规则

Azure SQL 根据插入数据的数量决定是否使用 Deltastore 进行缓冲：
- 如果插入的行数少于 102,400，所有行将进入某个 Delta 行组。
- 如果使用单个 BULK INSERT 或 SELECT INTO 语句插入超过 102,400 行，Azure SQL 将直接将这些值流式传输到新的压缩行组。

2.2 数据删除操作

为了高效地从 Columnstore 中删除行，Azure SQL 使用 Delete 索引和 Delete 位图来“虚拟”删除行。当需要从压缩行组中删除一行时，Azure SQL 只需将该行的“位置标记”为已删除。在读取压缩列段中的行时，Azure SQL 在运行时会忽略标记为已删除的行。

2.3 数据更新操作

数据更新是最复杂的操作，因为 Azure SQL 不能直接将更新的值注入到现有的高度压缩列段中而不重新解压缩和压缩其他所有内容。因此，每次行更新都表示为删除和插入操作的组合。压缩行组中该行的旧值被标记为已删除，新值插入到 delta 存储中。但如果行位于 Delta 存储中，它将像常规行存储中的任何其他行一样进行更新或删除。

3. 查询 Columnstore 结构

Columnstore 结构非常适合使用较少列子集且需要扫描表中大部分数据的报表查询。例如以下查询：

SELECT State, AVG(Price)
FROM Products
WHERE State IN ('Available', 'In Stock')
GROUP BY State

Columnstore 格式支持两种重要的查询优化：

3.1 列段消除

Azure SQL 在查询处理开始之前，根据以下规则确定哪些列段对于查询处理是不必要的：
- 不属于查询中使用的列的列段将被忽略。
- 不包含查询所需值的列段将被忽略。

通过列段消除，Azure SQL 可以减少所需的 IO 和内存占用。例如，在一个包含六个压缩行组和五列的表中，如果查询只使用了两列，那么另外三列的列段将被忽略，从而将所需的 IO 和内存占用减少到 40%。

3.2 批量查询处理

Azure SQL 利用底层硬件的 SIMD 指令，对大量数据向量应用少量操作。数据向量是包含连续值范围的列段。这种批量模式处理的硬件加速显著提高了查询性能，并且对查询和使用它的应用程序完全透明。

4. 聚集 Columnstore 索引（CCI）

聚集 Columnstore 索引（CCI）是以 Columnstore 格式组织的表。我们可以在创建表时直接创建 Columnstore 索引，也可以在现有表上创建：

-- 创建表时创建 CCI
CREATE TABLE Sales.Orders (
     <column definitions>
     INDEX cci CLUSTERED COLUMNSTORE
)

-- 在现有表上创建 CCI
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.Orders

CCI 适用于存储高度压缩的数据，并且具有主要的读取和大量（批量加载）插入工作负载模式。这种工作负载模式在数据仓库中非常常见，因此 CCI 在现代数据仓库场景中是一种常见的结构。

CCI 还提供了一种特定的优化来提高批量加载性能。当加载 102,400 行时，Azure SQL 会直接压缩所有传入的数据，这可以提高数据加载过程的性能。如果工作负载产生了许多未满足关闭条件的开放行组，可以使用以下语句合并它们：

ALTER INDEX cci ON Sales.Orders REORGANIZE

CCI 重组操作是一种轻量级的非阻塞操作，它将合并和压缩开放的 delta 行组，而不会对工作负载产生重大影响。

5. 非聚集 Columnstore 索引（NCCI）

非聚集 Columnstore 索引（NCCI）是以 Columnstore 格式组织的索引，创建在传统行存储表之上。这使得 Azure SQL 在市场上非常独特，因为它允许在同一表上同时存在 Columnstore 和行存储索引。

可以使用以下定义在表中创建 NCCI：

CREATE TABLE dbo.Sales (
  <column definitions>
  INDEX ncci NONCLUSTERED COLUMNSTORE (Price, Quantity, SalesID)
)

NCCI 的主要目的是在不改变现有行存储表的原始结构的情况下，优化对表中部分列的分析和报表查询。当运行不同类型的查询时，Azure SQL 会根据情况选择最优的格式：
- 如果运行选择单行或少量行的查询，Azure SQL 将使用底层的行存储表，并利用索引查找或范围扫描操作。
- 如果运行扫描整个表并聚合数据的报表，Azure SQL 将从 NCCI 索引中读取信息，利用列段消除和批量模式执行来快速处理整个表。

任何更新、删除或插入操作都会立即影响底层的行存储表，NCCI 将通过 Deltastore 进行更新。这种双重更新可能会减慢事务性工作负载，但如果需要在提升表的报表功能和事务性语句的速度之间进行权衡，这是可以接受的。

6. 内存优化表

Azure SQL 的内存优化表可以极大地提高 OLTP 工作负载的性能和可扩展性。其主要目的是改进诸如选择几行、更新或删除现有行等类似 CRUD 的操作。

6.1 内存优化表的优势

很多人认为将行始终保存在内存中是提高性能的关键因素，但这在很多情况下并非真正的原因。Azure SQL 本身就有优秀的内存缓存机制（称为 Buffer Pool），可以从磁盘获取最重要的数据，并智能地淘汰近期不需要的行，为更可能使用的行腾出空间。如果数据库小于内存，所有行可能已经在 RAM 中，即已经拥有一个“内存数据库”。

内存优化表的真正优势在于可以摆脱磁盘 IO 和 8KB 页面的限制，从而应用高乐观并发。在传统表中，数据以 8KB 页面的形式存储在磁盘上，每次访问行时，相关的数据页面会被加载到内存中。当多个事务同时更新同一页面上的行时，可能会导致冲突和性能问题。而内存优化表的数据始终保存在内存中，没有数据在缓冲池和磁盘存储之间的传输，也没有导致行之间依赖的 8KB 容器，因此可以使用乐观并发，允许大量的并发更新。

6.2 内存优化表的类型

内存优化表有三种类型：
- 内存优化的非持久化仅架构表 ：类似于临时表，表架构在重启或潜在的系统崩溃后将被保留，但内容不会被保留。主要用于缓存场景（如 ASP.NET 会话状态）、复杂数据处理的中间存储和快速加载数据（即所谓的暂存表）。
- 内存优化的持久化表 ：表架构和数据在进程崩溃或故障转移后都将被保留。Azure SQL 会确保将最少的信息发送到日志并持久化，以便数据在系统崩溃后仍然存在。这些表在逻辑上等同于具有内存优化增强功能的经典表，用于提高现有行存储表的性能。
- 内存优化的 Columnstore 表 ：结合了 Columnstore 结构和内存优化表的优点，适用于同一表上的分析和事务性工作负载（OLTP）的组合，即混合事务分析处理（HTAP）。

6.3 创建内存优化表

可以通过在创建表时添加 WITH (MEMORY_OPTIMIZED=ON) 子句来标记表为内存优化表。例如：

CREATE TABLE dbo.Cache
(
    [key] INT IDENTITY PRIMARY KEY NONCLUSTERED,
    data NVARCHAR(MAX)
)
WITH (MEMORY_OPTIMIZED=ON)

还可以使用 DURABILITY 选项定义数据库崩溃时数据是否持久化：
- DURABILITY=SCHEMA_ONLY 定义非持久化表，仅在 Azure SQL 数据库重启时重新创建表结构。
- DURABILITY=SCHEMA_AND_DATA 定义持久化表，表结构和数据在故障转移或崩溃后都将保留。

创建内存优化的 Columnstore 表时，需要在表定义中指定聚集 Columnstore 索引，例如：

CREATE TABLE Accounts (
     AccountKey int NOT NULL PRIMARY KEY NONCLUSTERED,
     Description nvarchar (50),
     Type nvarchar(50),
     UnitSold int,
     INDEX cci CLUSTERED COLUMNSTORE
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

内存优化的 Columnstore 表结合了快速分析和数据压缩以及高并发和快速数据摄取与更新的优点，但需要确保数据能够全部放入内存。因为 Azure SQL 为内存优化数据保留了约 60% 的可用内存，如果不确定数据是否能放入内存，建议使用基于磁盘的表。

在创建内存优化表时，还需要至少创建一个索引来组织内存中的行。除了常见的 NONCLUSTERED B - Tree 索引和 CLUSTERED COLUMNSTORE 索引外，还有一种特定于内存优化表的 NONCLUSTERED HASH 索引。

通过合理使用 Azure SQL 的 Columnstore 结构和内存优化表等特性，可以根据不同的工作负载需求选择合适的存储方式和优化策略，从而显著提高数据处理的性能和效率。

Azure SQL 存储结构与性能优化全解析

7. 内存优化表的并发控制与性能影响

7.1 传统表的并发控制问题

在传统的 Azure SQL 表中，数据以 8KB 的页面形式存储在磁盘上。当多个事务同时更新同一页面上的行时，会面临并发控制的挑战。Azure SQL 有两种处理并发的方法：
- 悲观并发控制 ：当一个线程修改了某行或页面时，会对其加锁，阻止其他线程访问，直到修改线程决定保存或放弃更改。这种方法虽然能避免冲突和额外的 IO 操作，但会降低并发性能和可扩展性。
- 乐观并发控制 ：允许其他线程访问被修改行的原始版本，修改线程会创建一个私有版本的行。但当多个事务同时修改同一页面上的行时，可能会导致冲突。例如，当一个事务提交更改后，其他事务可能因为修改的版本不再有效而需要回滚并重新尝试操作，还可能导致其他未冲突的事务也无法提交更改，同时创建临时页面会增加磁盘 IO 和性能开销。

以下是一个简单的 mermaid 流程图，展示了乐观并发控制下的事务处理流程：

graph TD;
    A[事务开始] --> B[读取原始行];
    B --> C[创建私有行版本];
    C --> D{是否有其他事务提交更改};
    D -- 否 --> E[提交更改];
    D -- 是 --> F[检查版本有效性];
    F -- 无效 --> G[回滚并重试];
    F -- 有效 --> E[提交更改];

7.2 内存优化表的并发优势

内存优化表摆脱了磁盘 IO 和 8KB 页面的限制，能够应用高乐观并发。由于数据始终保存在内存中，没有数据在缓冲池和磁盘存储之间的传输，也不存在导致行之间依赖的 8KB 容器，因此可以轻松地使用乐观并发，允许大量的并发更新，避免了传统表中因并发控制带来的性能问题。

8. 内存优化表的索引选择与应用

8.1 索引的必要性

在内存优化表中，索引是必不可少的。因为内存中的行是分散存储的，需要索引来将它们组织起来。在创建内存优化表时，至少需要创建一个索引。

8.2 不同类型的索引

NONCLUSTERED B - Tree 索引 ：适用于范围查询和排序操作，能够快速定位满足条件的行。例如，在一个包含日期列的表中，可以使用 NONCLUSTERED B - Tree 索引来快速查找特定日期范围内的记录。
CLUSTERED COLUMNSTORE 索引 ：结合了 Columnstore 结构的优点，适用于分析查询和数据压缩。在处理大量数据的报表查询时，能够显著提高查询性能。
NONCLUSTERED HASH 索引 ：这是一种专门为内存优化表设计的索引。它通过哈希算法将键值映射到内存中的位置，能够快速定位特定的行。适用于精确查找操作，例如根据主键查找单条记录。

以下是创建 NONCLUSTERED HASH 索引的示例代码：

CREATE TABLE dbo.ExampleTable (
    ID int NOT NULL,
    Name nvarchar(50),
    INDEX hash_index NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

在创建 NONCLUSTERED HASH 索引时，需要指定 BUCKET_COUNT 参数，它表示哈希表中的桶数量。合理设置桶数量可以提高索引的性能。

9. 不同存储结构和索引的应用场景总结

存储结构/索引类型	适用场景	优点	缺点
聚集 Columnstore 索引（CCI）	数据仓库，主要读取和大量批量插入工作负载，如 IoT 数据存储和分析	高度压缩数据，提高报表和分析查询性能，支持批量加载优化	更新和删除操作相对复杂，可能有性能开销
非聚集 Columnstore 索引（NCCI）	现有行存储表上的分析和报表查询优化	不改变原表结构，可根据查询类型选择最优格式	双重更新可能减慢事务性工作负载
内存优化的非持久化仅架构表	缓存场景，如 ASP.NET 会话状态，复杂数据处理的中间存储	快速读写，表架构保留	数据不持久化，系统重启后内容丢失
内存优化的持久化表	提高现有行存储表的性能，需要数据持久化的 OLTP 场景	支持并发更新，数据持久化	受内存限制，数据量不能过大
内存优化的 Columnstore 表	混合事务分析处理（HTAP）场景，需要同时进行分析和事务操作	结合 Columnstore 结构和内存优化表优点	数据必须全部放入内存，对内存要求高

10. 选择合适存储结构和优化策略的步骤

为了根据不同的工作负载需求选择合适的存储结构和优化策略，可以按照以下步骤进行：

分析工作负载类型
- 确定是主要的读取和批量插入工作负载（如数据仓库场景），还是频繁的事务性操作（如 OLTP 场景），或者是两者的混合（HTAP 场景）。
评估数据量和内存情况
- 估算数据量大小，判断是否能够全部放入内存。如果数据量较小且对并发性能要求高，可以考虑内存优化表；如果数据量较大，可能需要选择基于磁盘的存储结构。
考虑查询类型
- 如果查询主要是使用较少列子集且需要扫描大量数据的报表查询，Columnstore 结构（CCI 或 NCCI）可能是合适的选择；如果是选择单行或少量行的查询，传统的行存储表可能更适合。
权衡性能和成本
- 评估不同存储结构和优化策略的性能提升和成本开销。例如，内存优化表虽然能提高性能，但需要更多的内存资源；NCCI 可以优化查询性能，但会增加事务性工作负载的开销。

11. 总结与建议

Azure SQL 提供了丰富的存储结构和优化技术，包括 Columnstore 结构、内存优化表等，能够满足不同场景下的性能需求。在实际应用中，需要根据具体的工作负载、数据量、查询类型等因素，综合考虑选择合适的存储结构和优化策略。

对于数据仓库和分析场景，聚集 Columnstore 索引（CCI）是一个很好的选择，它能够通过压缩数据和批量查询处理提高报表和分析查询的性能。
如果需要在现有行存储表上进行分析和报表查询优化，非聚集 Columnstore 索引（NCCI）可以在不改变原表结构的情况下提供性能提升。
对于 OLTP 场景，内存优化表能够显著提高并发性能和可扩展性，特别是在对读写性能要求较高的情况下。但需要注意内存的使用和数据的持久化问题。
在混合事务分析处理（HTAP）场景中，如果数据量较小且能够全部放入内存，内存优化的 Columnstore 表可以结合分析和事务操作的优点；否则，可以考虑使用传统行存储表和 NCCI 索引的组合。

通过合理选择和配置 Azure SQL 的存储结构和优化策略，可以充分发挥其性能优势，提高数据处理的效率和质量。