超级会员免费看
Azure SQL 高级表特性深度解析
1. 非聚集哈希索引
非聚集哈希索引是一种内存中的哈希表，它使用列值作为键（例如
EmpID
），并包含指向表中实际行的指针列表。每个哈希表由固定数量的槽（哈希桶）组成，这些槽包含指向实际行的指针数组。具有相同哈希值的行指针会链接到代表其自身哈希桶的列表中。
CREATE TABLE [dbo].[Employees](
[EmpID] [int] NOT NULL
CONSTRAINT PK_Employees_EmpID PRIMARY KEY
NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000),
[EmpName] [varchar](50) NOT NULL,
[EmpAddress] [varchar](50) NOT NULL,
[EmpDEPID] [int] NOT NULL,
[EmpBirthDay] [datetime] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
在创建索引时必须指定桶的数量，这会影响每个桶中指针列表的长度。不同键值的数量与哈希桶数量的比率代表了预期的平均桶链表长度。较长的列表意味着根据键查找行时需要更多的操作。
桶的数量应该在索引键中不同值数量的 1 到 2 倍之间。在实践中，很难估计列中不同值的数量；但是，如果
BUCKET_COUNT
值在索引列中实际值数量的 10 倍以内，你将获得良好的性能。通常，高估比低估更好。如果一段时间后发现比率不佳，你可以始终使用新的桶数量重建索引。
2. 访问内存优化表
内存优化表的另一个优点是，它们可以像任何其他传统表一样被访问。你可以使用相同的 Transact - SQL 查询来连接内存优化表、列存储表和传统表。不同功能（如列存储、内存优化表、JSON 和图形）之间的互操作性是 Azure SQL 的核心价值主张之一。任何针对特定场景进行优化的功能都将具有相同或相似的访问方法。
内存优化表使用乐观并发控制，每当某个事务更新行时，它会快速创建该行的新版本。这样，更新数据的事务可以获得自己的行私有版本，而不会被读取者阻塞，也不会阻塞其他写入者。
这种行版本控制对 Azure SQL 中使用的某些事务隔离级别有影响。基于磁盘的表允许你读取行的临时未提交值，前提是你确定这是你想要的，并且绕过锁。但在内存优化表中这是不可能的，因为同一行可能有多个未提交的副本，Azure SQL 不知道你想要使用哪个版本。
读取已提交的行也不像常规表那样容易。如果你想读取已提交的行，你的事务始终需要检查当前行是否真的是最新的，或者是否有某个事务提交了新的公共版本。这可能会带来巨大的性能开销，你肯定希望避免这种情况。
解决方案是在事务开始时使用行的已提交版本，这看起来就像表数据的快照。在事务开始时使用数据快照的事务隔离级别称为快照隔离级别。
如果你需要使用
READ COMMITTED
或
READ UNCOMMITTED
事务隔离级别，你应该在内存优化表上显式添加快照隔离提示，以仅针对此表覆盖该隔离级别：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION; -- Explicit transaction.
-- Employees is a memory-optimized table.
SELECT * FROM
dbo.Employees as o WITH (SNAPSHOT) -- Table hint.
COMMIT TRANSACTION;
WITH(SNAPSHOT)
提示指示 Azure SQL 将表中的值解释为执行时数据状态的快照。此隔离级别会忽略在此期间可能发生的更改。此提示将防止在其他隔离模式下出现代码为 41368 的错误。
作为查询提示的替代方案，你可以使用数据库选项，该选项将在需要时自动将内存优化表的隔离级别提升为快照：
ALTER DATABASE current
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
如果正确设置了事务隔离级别，在使用 DML 语句查询内存优化表时没有太大限制。
3. 原生编译代码
Azure SQL 解释你用于处理数据的 T - SQL 代码批。这意味着查询批（一个或多个 T - SQL 语句）会被解析，然后批中的每个查询会被编译成执行计划，该计划会分解为操作符（例如扫描、连接、排序），并执行这些操作符以获取或更新行。这种查询处理策略在处理内存优化数据时可能是一个很大的瓶颈。尽管数据可以在内存中使用，并且锁最少，但操作符链仍然会使用传统机制逐行获取和处理数据。
Azure SQL 允许你使用原生编译模块来提高 T - SQL 代码的性能。你可以将 Azure SQL 数据库中的原生编译模块想象成定制的 C 程序，它们可以处理你的数据。Transact - SQL 代码将被预编译成本地 .dll（动态链接库），然后由 Azure SQL 引擎执行以操作数据。
Azure SQL 允许你编写将被编译成本地可执行文件的 SQL 存储过程、函数和触发器。原生编译代码可能比传统代码快几个数量级。唯一的限制是它们只能访问内存优化表，并使用 T - SQL 语言的一个子集。
原生编译存储过程是访问和修改内存优化表数据的常用方法。以下是一个代表原生编译存储过程的模板示例：
CREATE PROCEDURE myProcedure(@p1 int NOT NULL, @p2 nvarchar(5))
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english')
/* Procedure code goes here */
END
原生编译过程有两个主要组件：
-
WITH
子句：定义过程属性，如将过程标记为原生编译并指定架构绑定。架构绑定意味着只要过程存在，存储过程引用的表就不能被更改。这些是唯一必需的选项，但你也可以指定其他选项，如
EXECUTE AS
等。
-
ATOMIC
块：表示一个工作单元，它将要么被处理，要么被取消。在原子块中，你需要指定诸如隔离级别或语言等选项，因为这些设置将被编译到动态链接库中。
Azure SQL 还允许编写原生编译函数。例如，以下函数获取格式为 JSON 文本的字符串，解析它，并将 JSON 对象的属性作为表列返回：
CREATE FUNCTION PeopleData(@json nvarchar(max))
RETURNS TABLE
WITH NATIVE_COMPILATION, SCHEMABINDING
AS RETURN (
SELECT Title, HireDate, PrimarySalesTerritory,
CommissionRate, OtherLanguages
FROM OPENJSON(@json)
WITH(Title nvarchar(50),
HireDate datetime2,
PrimarySalesTerritory nvarchar(50),
CommissionRate float,
OtherLanguages nvarchar(max) AS JSON)
)
原生编译函数的调用方式与其他函数相同。我们可以使用标准的
CROSS APPLY
操作符为函数提供参数值，并将结果与主行连接：
select p.FullName, p.EmailAddress, j.Title, j.CommissionRate
from Application.People p
cross apply PeopleData(p.CustomFields) j
使用原生编译代码访问内存优化表是推荐的方法，因为它提供了最佳的性能和可扩展性。
4. 时态表
常规数据库表包含数据行的最新版本。每当某个查询更新值时，旧值会被覆盖。这通常是预期和期望的行为，但在某些情况下，你可能还希望能够访问旧值，例如：
- 错误纠正：有人意外更改或删除了表中的一行，你需要纠正或撤销此更改。
- 历史分析：你需要分析表中随时间发生的更改。
- 审计：你需要找出谁更改了值、何时更改以及更改了什么。
时态表为你的数据添加了时间维度，以便它们可以保留所有更改数据的信息以及更改发生的时间。这完全是自动且透明地完成的，这要归功于系统管理的历史记录表。历史记录表是一个影子表，其列结构与主表相同。每当主表中的某些行（如图 9 - 7 所示）被更改（删除或更新）时，旧版本会被写入历史记录表，并附带跟踪这些行生命周期的额外信息。
graph LR
A[主表] -->|更新/删除| B[历史记录表]
只进行插入或选择数据的查询将仅对存储当前数据版本的主时态表起作用，它们不会影响历史记录表。时态表会修改更新或删除数据的查询的行为，并在主表中的行被更新之前，将当前行值静默发送到历史记录表。这完全是透明的，你可以继续使用标准的 T - SQL 查询来访问数据。
将旧行在更改之前移动到历史记录表是触发器最常见的场景之一。时态表通过自动移动行来为这种行为提供原生支持。在大多数情况下，时态表使用的方法应该比等效的触发器快得多。
5. 查询时态数据
旧行从时态表自动移动到历史记录表很重要，但这不是时态表带来的唯一好处。即使你在历史记录表中拥有所有所需的行版本及其有效期范围，查询历史数据仍然不容易。如果你想查看表在历史上某个时间点的样子，你需要从历史记录表中获取一些旧行值，但也需要从主表中获取一些行，因为它们在当前时间可能仍然有效。
Azure SQL 允许你使用特殊语法在某个时间点选择数据，并为你处理时态查询的复杂性。除了从主表读取当前行的标准
SELECT
查询外，Azure SQL 还允许你使用时态操作符（如图 9 - 8 所示）来获取过去某个时间点、特定时间段内的行版本，或者某行的所有版本等。
Azure SQL 数据库提供了几个可以应用于时态表的操作符，最常见的有：
-
FOR SYSTEM_TIME AS OF <datetime>
：查询将读取表在指定过去日期时间的内容。
-
FOR SYSTEM_TIME ALL
：查询将读取过去任何时间点存在的所有行。
例如，以下查询将获取指定时间点具有指定主键值的员工信息：
SELECT *
FROM Employee FOR SYSTEM_TIME AS OF @asOf AS History
WHERE EmployeeID = @EmployeeID
假设有人意外更新了员工行，我们可以使用以下查询进行错误纠正：
UPDATE
E
SET
Position = History.Position,
Department = History.Department,
Address = History.Address,
AnnualSalary = History.AnnualSalary
FROM
Employee AS E
JOIN
Employee FOR SYSTEM_TIME AS OF @asOf AS History
ON E.EmployeeID = History.EmployeeID
WHERE
E.EmployeeID = @EmployeeID
6. 配置时态表
时态表和历史记录表是独立的表。你可以通过添加不同的索引分别对它们进行配置和优化。为了提高时态表性能，最佳配置如下：
- 包含当前数据的表应尽可能实现为内存优化的架构和数据表。
- 历史记录表应实现为聚集列存储索引。
如果你使用的是业务关键服务层，内存优化表将在插入、更新和删除数据的工作负载中为你提供最佳性能，这是包含当前行的表的主要场景。如果你认为所有行无法全部放入内存，则不应使用内存优化表，因为将所有行放在内存中是内存优化表的要求。
列存储格式是历史记录表的最佳解决方案，因为你可能有许多具有相同值的列单元格。在许多情况下，你只会更新少数几列，而其他单元格保持不变。时态表会将整个前一行物理复制到历史记录表中，这意味着未更改的单元格在历史记录的不同行版本中会具有相同的值。如果列存储格式可以用一个标记来替换一组具有相同值的单元格，该标记描述了该值出现的行范围，那么它将具有最有效的压缩。在这种情况下，在历史记录表上创建的聚集列存储索引可以提供极高的压缩率，并提高时态表的历史分析性能。
假设你有一个用于历史数据的
DepartmentHistory
表，以下索引可能会提高时态历史查询的性能：
CREATE CLUSTERED COLUMNSTORE INDEX cci_DepartmentHistory
ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);
聚集列存储索引在查询扫描大量历史数据时将提供高压缩率并提高分析能力。范围列和主键上的非聚集索引对于提高在历史上某个指定时间点访问某些行值的查询（例如使用
FOR SYSTEM_TIME AS OF
子句）的性能非常有帮助。根据你运行的历史查询类型，你可能需要添加第一个、第二个或两个索引。
另一个重要的优化是数据库历史记录保留。你可以定义历史数据应在历史记录表中存储多长时间。时态表允许你使用
HISTORY_RETENTION_PERIOD
设置为历史记录定义生存时间：
ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));
此设置意味着历史记录表中所有超过 9 个月前输入的记录将自动从历史记录表中删除，以避免无限增长。你还可以使用以下数据库属性全局启用或禁用这些规则：
ALTER DATABASE current
SET TEMPORAL_HISTORY_RETENTION ON
此数据库级选项允许你为数据库中的所有表打开或关闭保留策略。
Azure SQL 高级表特性深度解析
7. 总结与建议
为了更清晰地展示不同表特性的优势和适用场景，我们可以通过以下表格进行对比：
| 表特性 | 优势 | 适用场景 |
| — | — | — |
| 非聚集哈希索引 | 快速查找，基于列值作为键 | 对查询速度要求高，数据分布相对均匀的场景 |
| 内存优化表 | 高并发处理，乐观并发控制 | 读写频繁，对性能要求极高的场景 |
| 原生编译代码 | 高性能，预编译成本地 .dll | 复杂业务逻辑，需要频繁操作内存优化表的场景 |
| 时态表 | 保留历史数据，自动管理历史记录 | 需要进行历史分析、错误纠正和审计的场景 |
根据以上对比，我们可以给出以下使用建议：
- 对于数据查询速度要求高且数据分布均匀的场景，可以优先考虑使用非聚集哈希索引。在创建索引时，合理设置
BUCKET_COUNT
，如果难以估计列中不同值的数量，可将其设置在索引列实际值数量的 10 倍以内。
- 当面临读写频繁、对性能要求极高的场景时，内存优化表是一个不错的选择。同时，要注意事务隔离级别的设置，可根据需要使用快照隔离级别或添加相应提示。
- 对于复杂业务逻辑且需要频繁操作内存优化表的场景，使用原生编译代码能显著提高性能。编写原生编译存储过程、函数和触发器时，要遵循相应的语法规则。
- 如果需要进行历史分析、错误纠正和审计等操作，时态表是必备的。合理配置时态表和历史记录表的索引，并设置合适的历史记录保留时间。
8. 常见问题解答
在使用 Azure SQL 的这些高级表特性时，可能会遇到一些常见问题，以下是一些解答：
-
非聚集哈希索引
BUCKET_COUNT
设置错误怎么办？
- 如果一段时间后发现
BUCKET_COUNT
比率不佳，可以使用新的桶数量重建索引。例如，若发现当前索引性能不理想，可先删除原索引，再重新创建并设置合适的
BUCKET_COUNT
。
-
内存优化表使用
READ COMMITTED
或
READ UNCOMMITTED
隔离级别报错怎么办？
- 可以在内存优化表上显式添加快照隔离提示，如
WITH (SNAPSHOT)
，或者使用数据库选项
ALTER DATABASE current SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
来自动提升隔离级别。
-
时态表历史记录占用空间过大怎么办？
- 可以通过设置
HISTORY_RETENTION_PERIOD
来定义历史记录的生存时间，如
ALTER TABLE dbo.WebsiteUserInfo SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS))
，同时可使用数据库属性
ALTER DATABASE current SET TEMPORAL_HISTORY_RETENTION ON
全局管理保留策略。
9. 操作流程总结
为了方便大家在实际操作中更好地运用这些特性，以下是一些关键操作的流程总结：
9.1 创建非聚集哈希索引
graph LR
A[定义表结构] --> B[创建非聚集哈希索引]
B --> C[设置 BUCKET_COUNT]
C --> D[完成索引创建]
具体代码示例：
CREATE TABLE [dbo].[Employees](
[EmpID] [int] NOT NULL
CONSTRAINT PK_Employees_EmpID PRIMARY KEY
NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000),
[EmpName] [varchar](50) NOT NULL,
[EmpAddress] [varchar](50) NOT NULL,
[EmpDEPID] [int] NOT NULL,
[EmpBirthDay] [datetime] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
9.2 配置内存优化表事务隔离级别
graph LR
A[选择隔离级别] --> B[添加快照隔离提示或设置数据库选项]
B --> C[执行查询操作]
代码示例：
-- 添加快照隔离提示
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
SELECT * FROM dbo.Employees as o WITH (SNAPSHOT);
COMMIT TRANSACTION;
-- 设置数据库选项
ALTER DATABASE current
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
9.3 创建原生编译存储过程
graph LR
A[定义存储过程参数] --> B[设置编译和绑定属性]
B --> C[设置原子块选项]
C --> D[编写过程代码]
D --> E[完成存储过程创建]
代码示例：
CREATE PROCEDURE myProcedure(@p1 int NOT NULL, @p2 nvarchar(5))
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english')
/* Procedure code goes here */
END
9.4 创建时态表并配置索引
graph LR
A[创建主表] --> B[创建历史记录表]
B --> C[配置主表和历史记录表索引]
C --> D[设置历史记录保留时间]
D --> E[完成时态表配置]
代码示例：
-- 创建主表
CREATE TABLE dbo.WebsiteUserInfo (
UserID int PRIMARY KEY,
UserName nvarchar(50),
-- 其他列
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
-- 配置历史记录表索引
CREATE CLUSTERED COLUMNSTORE INDEX cci_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory;
CREATE NONCLUSTERED INDEX IX_WebsiteUserInfoHistory_ID_PERIOD_COLUMNS
ON dbo.WebsiteUserInfoHistory (SysEndTime, SysStartTime, UserID);
-- 设置历史记录保留时间
ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));
通过以上对 Azure SQL 高级表特性的详细解析，我们可以看到这些特性为数据库的性能优化和功能扩展提供了强大的支持。在实际应用中，根据具体的业务需求和场景，合理选择和运用这些特性，能够显著提升数据库的性能和管理效率。
扫一扫
837
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
