21、Azure SQL 多模型数据处理能力解析

Azure SQL 多模型数据处理能力解析

1. 地理空间数据处理

在处理地理空间数据时，由于地球是一个球体，而我们通常需要将地理对象转换到二维平面进行处理，这就涉及到空间转换策略。每种转换策略都会存在一定的失真，但为了准确处理地理数据，每个地理对象都关联了一个空间参考标识符（SRID）。SRID 描述了使用的坐标系统（如经纬度、东向和北向坐标）、测量单位以及坐标原点等信息。Azure SQL 会根据 SRID 来比较地理对象的位置。

例如，要将坐标转换为地理线，可以指定使用世界大地测量系统 1984（WGS84，SRID 为 4326）进行转换，代码如下：

DECLARE @g GEOGRAPHY;
SET @g = GEOGRAPHY::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STSrid;

有些国家，如智利，由于南北边界距离较远，可能需要在其领土内使用多个 SRID。在比较地理对象的位置时，需要确保使用相同的 SRID，否则会导致位置或形状的差异，测量结果也会出错。因此，Azure SQL 不允许对不同 SRID 的空间对象进行空间操作。

WGS84 是最常用的标准，我们手机或汽车上的 GPS 系统也使用该标准。如果不确定使用哪个 SRID，WGS84 通常是一个不错的选择。幸运的是，Azure SQL 内置了这些复杂的转换功能，我们只需要利用这些功能并了解基本原理，就能使用空间特性。

2. XML 数据处理

XML 数据类型可以看作是 JSON 的“大哥”，它在 2000 - 2005 年期间随着 XML 成为不同应用之间数据交换的主流格式而被引入到 SQL Server 数据库引擎中。

Azure SQL 对 XML 的支持与之前介绍的 JSON 支持类似。如果需要解析 XML 数据或将查询结果格式化为 XML，可以使用以下功能：
- OPENXML 表值函数 ：用于解析 XML 文档。
- FOR XML 子句 ：将查询结果格式化为 XML 文档。
- XML 类型及其方法 ：用于处理 XML 文档中的值。

与 JSON 支持不同的是，Azure SQL 为 XML 提供了专门的 SQL 类型。JSON 文本通常存储在原生的 NVARCHAR 类型中，而 XML 有自己的专用类型。在处理 JSON 时，我们使用类似字符串的函数进行解析，而 XML 内容则表示为一个对象，可以使用各种方法提取数据。

XML 类型有以下成员方法用于提取和操作 XML 数据：
- value(path, type) ：从 XML 对象中返回一个节点或属性，并自动将其转换为 SQL 类型。需要指定一个标准的 XPath 表达式来定位 XML 文档中的单个值。
- query(path) ：根据指定的 XPath 表达式从 XML 文档中返回一个对象。
- nodes(path) ：类似于 OPENXML/OPENJSON 函数，用于将指定路径上的 XML 元素数组转换为一组行，可在 FROM 子句中使用。
- exists(path) ：检查指定路径上是否存在元素。
- modify(path, type) ：用于在 XML 文档中插入、删除或替换某些节点的值。

下面通过一个示例来说明如何查询 XML 数据：

DECLARE @i INT = 47;
DECLARE @x xml;
SET @x='<Family id="1804">
    <row id="17"><name>Robin</name></row>
    <row id="47"><name>Lana</name></row>
    <row id="81"><name>Merriam</name></row>
</Family>';

-- 第一个查询
SELECT
  family_id = @x.value('(/Family/@id)[1]', 'int'),
  family_81_name = @x.value('(//row[@id=81]/name)[1]', 'varchar(20)'),
  family_name = @x.query('//row[@id=sql:variable("@i")]/name');

-- 第二个查询
SELECT
  family_member = xrow.value('name[1]', 'varchar(20)'),
  family_member_id = xrow.value('@id[1]', 'varchar(20)'),
  family_member_xml = xrow.query('.')
FROM
  @x.nodes('/Family/row') AS Members(xrow)
WHERE
  xrow.value('@id[1]', 'int') < 50
AND
  xrow.exist('.[@id > 5]') = 1;

第一个查询使用 @x 变量的 value 成员函数提取家族标识符、ID 为 81 的家族成员姓名以及标识符值等于变量 @i 的成员姓名。第二个查询从 XML 文档中选取所有 /family/row 节点作为行集，条件是每行的 id 属性小于 50 且大于 5。满足条件的节点作为 xrow 列返回，使用 value() 方法提取用于比较的值， exist() 方法直接将谓词推送到 XML 变量。最后，使用 value() 和 query() 方法获取每个返回行的姓名、标识符和 XML 内容。

此外，Azure SQL 还支持在 XPath 表达式中绑定 SQL 变量或列的值，这为查找数据提供了更灵活的方式。同时，还可以直接更新 XML 文档，而无需将其解析、转换为关系格式，再使用 FOR XML 子句重新构建 XML。例如：

SET @x.modify('insert <row id="109"><name>Danica</name></row>
              into (/Family)[1]') ;
SELECT @x;

3. XPath 和 XQuery 语言

Azure SQL 支持 XML 标准，允许对 XML 文档进行复杂的处理和查询。其 XML 支持基于以下两种语言：
- XPath（XML 路径语言） ：用于从 XML 文档中选择节点的查询语言。
- XQuery（XML 查询） ：用于查询和转换 XML 数据集合的查询和函数式编程语言。

XPath 是一种基于表达式的语法，用于在 value() 、 nodes() 和 query() 方法中指定要定位的 XML 文档元素：
- 层次表达式 ：使用 XPath 指定从 XML 文档根节点到所需元素的路径。例如， /Family/row/name 用于引用位于 <row> 元素下、 <Family> 元素（XML 文档的根元素）下的 <name> 元素。可能有多个元素匹配相同的 XPath 表达式，因此需要使用索引运算符 [] 来指定要引用的元素。
- 节点和属性引用 ：XPath 可以引用节点或其属性。任何不以 @ 开头的名称将被视为 XML 节点的名称，而以 @ 开头的名称（如 @id ）将被视为属性。
- 递归表达式 ：在某些情况下，可能无法或不需要引用从根节点开始的完整路径，或者需要查找位于文档不同位置的元素。递归运算符 // 允许指定一个“分离路径”，XML 函数将尝试查找与递归运算符右侧表达式匹配的任何路径。例如， //row/name 将查找位于 XML 文档中任何位置的 <row> 元素内的 <name> 元素。
- 谓词 ：用于指定元素必须满足的条件，以匹配 XPath 表达式。例如， //row[@id=17]/name 指定 XML 方法应查找 <row> 元素内 id 属性值为 17 的 <name> 元素。谓词是过滤不满足条件的节点的简单方法。

除了上述基本功能，XPath 还支持命名空间、七方向轴和内置函数等特性，可用于定义名称范围、引用父节点、兄弟节点和子节点，以及在表达式中转换结果。

XQuery 是一种标准化语言，用于查询和处理 XML 文档中的元素集合。它定义了一种类似 SQL 的语法，称为 FLWOR（发音为“flower”），代表 FOR、LET、WHERE、ORDER BY 和 RETURN 运算符，可用于转换 XML 节点。这些运算符允许我们根据现有数据选择、转换和返回新对象。例如：

SELECT xrow.query(
'let $r := self::node()
  return <person id="{$r/@id}">{$r/name/text()}</person>')
FROM @x.nodes('/Family/row') AS Members(xrow);

上述代码中， XML nodes() 方法从 @x 变量中发出三个 XML 行，然后 query 方法使用 XQuery 表达式处理这些行。在 XQuery 表达式中，当前节点被赋值给变量 $r ， return 语句创建一个新的 XML 节点，将 id 属性和 <name> 节点的内容插入模板中。最终结果如下：

<person id="17">Robin</person>
<person id="47">Lana</person>
<person id="81">Merriam</person>

4. XML 索引

由于 XML 查询的特殊性，标准的 B - 树索引可能无法提供足够的查询性能提升。Azure SQL 提供了几种专门的索引类型，用于高效处理 XPath/XQuery 表达式：
- 主 XML 索引 ：是一种预计算结构，包含 XML 列中分解的值和节点。Azure SQL 使用主索引中的值，而不是调用昂贵的 value() 、 nodes() 或 query() 方法来解析 XML 类型。这与 Azure Cosmos DB 对 JSON 文档的自动索引类似。
- 辅助 XML 索引 ：用于提高使用 exists() 方法搜索或过滤 XML 文档，或使用 value() 方法返回 XML 文档中多个值的查询性能。
- 选择性 XML 索引 ：仅对 XML 列中指定的路径进行索引，类似于对预定义 XML 表达式的多个 B - 树索引。

根据 SQL Server 中多个 XML 场景的经验，选择性 XML 索引是推荐的索引方法。因为自动索引所有可能的字段会导致 XML 索引体积过大，而大部分索引路径并未使用。因此，选择性 XML 索引在可用性、性能和大小之间取得了最佳平衡。

例如，创建一个简单表的选择性 XML 索引的代码如下：

CREATE TABLE XmlDocs (
  id INT IDENTITY PRIMARY KEY,
  doc XML
);
GO
CREATE SELECTIVE XML INDEX sxi_docs
ON XmlDocs(doc)
FOR (
    path_price = '/row/info/price' AS SQL INT,
    path_name = '/row/info/name' AS SQL NVARCHAR(100)
)

在选择性索引中，可以选择要包含在索引中的路径并指定其类型。使用 value() 函数对 doc 列进行与索引规范中定义的 XPath 查询匹配的查询时，可以利用 sxi_doc 索引，即使索引很小，也能获得显著的性能提升。

5. 键值对处理

Azure SQL 没有专门用于存储键值对的结构，因为键值映射可以通过简单的两列表来实现。Azure SQL 允许自定义两列表，并使用各种索引对键列进行索引。对于内存优化表，可以使用 B - 树或哈希索引对键列进行索引。

例如，创建一个内存优化、无锁、原生编译的键值表，并使用哈希索引以实现更快的键访问：

CREATE TABLE [Cache] (
    [key] BIGINT IDENTITY,
    value NVARCHAR(MAX),
    INDEX IX_Hash_Key HASH ([key]) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

这种结构通过哈希索引实现了键的快速检索，非常适合基本的获取/插入操作。内存优化表具有乐观无锁的数据访问特性， SCHEMA_ONLY 耐久性确保了更快的更新，因为数据不会持久化到磁盘。此外，如果值以 JSON 格式存储，我们可以使用原生 JSON 函数直接在数据库中过滤和处理数据。

键值结构在 Azure SQL 中的一个应用场景是集中式缓存。在 SQL Server 2016 中有一个著名的案例，客户将一个分布式缓存机制（在 19 个分布式 SQL Server 节点上每秒可处理 150K 请求）替换为单个服务器上的内存优化表，性能提升到每秒 120 万请求，目标场景是实现 ASP.NET 会话缓存。Azure SQL 使用与 SQL Server 相同的技术，同样可用于 Azure 云环境中的缓存。

在许多实际项目中，使用专门的缓存引擎通常是首选，但这意味着需要掌握另一项技术，并考虑如何将其与解决方案集成。如果已经在使用 Azure SQL，了解数据库中具备的键值对处理能力，可以为简化整体架构提供一个额外的选择。

6. 非结构化文本处理

处理非结构化文本数据是一项具有挑战性但并不罕见的任务。在某些情况下，我们会遇到无法很好地组织成 JSON 或 XML 格式的文本数据，但需要对其进行搜索。例如，存储在数据库中的 HTML 代码，理想情况下，如果 HTML 符合 XHTML 规范，它应该与 XML 相同，但在很多情况下，HTML 可能存在一些变化，破坏了严格的 XML 结构。

Azure SQL 允许使用 LIKE 子句来确定文本列是否匹配某些模式。例如，查找 Warehouse.StockItems 表中 SearchDetails 和 Tags 列包含用户在搜索文本框中输入的文本的所有库存项目：

SELECT si.StockItemID, si.StockItemName, si.Tags
FROM Warehouse.StockItems AS si
WHERE si.SearchDetails LIKE N'%' + @SearchText + N'%'
OR si.Tags LIKE N'%' + @SearchText + N'%'

LIKE 谓词使用百分号 % 匹配零个或多个任意字符，下划线 _ 匹配任意一个字符。这些特殊字符允许我们定义各种模式，如以某些文本序列开头或结尾的文本。 LIKE 运算符是一个常用的工具，适用于小型数据集的文本搜索。Azure SQL 甚至可以优化并使用索引，特别是在使用 LIKE 搜索以某些前缀开头的所有文本时，索引和 LIKE 运算符可以提供很好的性能。

如果需要进行更复杂的搜索，例如在较大的文本集中查找包含特定单词的文本，可能需要考虑使用文本索引解决方案来进一步提高性能。

7. 非结构化文本索引

如果需要搜索大量的非结构化文本数据，需要使用特定的索引。Azure 提供了通用的 Azure 认知搜索索引服务，可对各种数据源进行索引。但使用外部服务会增加解决方案的复杂性。如果不需要 Azure 认知搜索的所有功能，Azure SQL 提供了类似的本地文本搜索索引，即全文搜索（FTS）索引。

FTS 索引是一种对指定表中的非结构化文本字段进行索引的结构。例如，在 Warehouse.StockItems 表的三个文本字段上创建 FTS 索引的代码如下：

CREATE FULLTEXT CATALOG [Main] AS DEFAULT;
GO
CREATE FULLTEXT INDEX
ON Warehouse.StockItems (SearchDetails, CustomFields, Tags)
KEY INDEX PK_Warehouse_StockItems
WITH CHANGE_TRACKING AUTO;
GO

FTS 索引包含一组使用分词器划分的文本片段（标记），这些标记与找到文本的原始行的键相关联。FTS 允许我们提供简单的文本模式描述，并返回匹配条件的行的键。

8. 查询非结构化文本

设置好 FTS 索引后，可以使用以下功能通过文本匹配搜索行：
- CONTAINS 和 FREETEXT ：检查某些列中的值是否匹配文本谓词中定义的条件。
- 表值函数 CONTAINSTABLE 和 FREETEXTTABLE ：返回文本匹配某些条件的行的标识符。

例如，查找 Warehouse.StockItems 表中 SearchDetails 列包含与 @SearchCriterion 变量中定义的搜索条件匹配的文本的所有键：

DECLARE @SearchCondition NVARCHAR(200) = 'blue car';
SELECT StockItemID = ft.[KEY], ft.[RANK]
FROM FREETEXTTABLE(Warehouse.StockItems, SearchDetails, @SearchCondition) 
AS ft

KEY 列是 StockItems 表中用于 FTS 索引的行标识符，代表 FREETEXTTABLE 或 CONTAINSTABLE 函数返回的记录。 RANK 列描述了行与选择条件的匹配程度。可以使用 KEY 列将结果集与原始表连接，以获取更多结果。

例如，查找 FTS 函数返回的键对应的所有库存项目：

DECLARE @SearchCondition NVARCHAR(200) = 'blue AND car';
SELECT
  si.StockItemID,
  si.StockItemName,
  ft.[RANK]
FROM
  Warehouse.StockItems AS si
INNER JOIN
 CONTAINSTABLE(Warehouse.StockItems, SearchDetails, @SearchCondition) AS ft 
ON si.StockItemID = ft.[KEY]
ORDER BY
  ft.[RANK];

CONTAINSTABLE 和 FREETEXTTABLE 函数根据精确或模糊匹配来查找文本。 CONTAINSTABLE 进行更精确的匹配，而 FREETEXTTABLE 使用同义词库、同义词和词形变化形式进行模糊匹配。例如，如果搜索条件为“children”， FREETEXTTABLE 还会匹配包含“child”的行，而 CONTAINSTABLE 则不会。在 CONTAINSTABLE 中，需要明确指定 FORMSOF(INFLECTIONAL,children) 来指示 Azure SQL 包含该词的词形变化形式。

此外， CONTAINSTABLE 允许指定 AND 、 OR 或 NEAR 等运算符来定义搜索方式。而 FREETEXTTABLE 类似于大多数搜索引擎，提供一组单词时会返回包含其中任何一个单词的行。

除了使用 FREETEXTTABLE 和 CONTAINSTABLE 函数，还可以使用等效的谓词 FREETEXT 和 CONTAINS 。这些谓词可以在查询的 WHERE 子句中使用，功能上与显式连接 FREETEXTTABLE 和 CONTAINSTABLE 相同，但不会返回 RANK 列。例如：

DECLARE @SearchCondition NVARCHAR(200) =
          'FORMSOF(INFLECTIONAL,children) OR car';
SELECT
  si.StockItemID,
  si.StockItemName,
  si.SearchDetails
FROM
  Warehouse.StockItems AS si
WHERE
  CONTAINS(SearchDetails, @SearchCondition);

综上所述，Azure SQL 提供了丰富的多模型数据处理能力，涵盖地理空间数据、XML 数据、键值对和非结构化文本等多种类型的数据。通过合理利用这些功能，我们可以更高效地处理和查询不同类型的数据，满足各种复杂的业务需求。

Azure SQL 多模型数据处理能力解析

9. 多模型数据处理总结与对比

为了更清晰地了解 Azure SQL 对不同类型数据的处理能力，下面通过表格进行总结对比：
| 数据类型 | 支持功能 | 主要操作方法 | 索引类型 | 应用场景 |
| ---- | ---- | ---- | ---- | ---- |
| 地理空间数据 | 坐标转换、位置比较 | 使用 SRID 描述空间转换策略，通过 GEOGRAPHY 类型操作 | 无专门提及（依赖 SRID 信息） | 地图应用、物流路径规划等 |
| XML 数据 | 解析、格式化、查询、更新 | OPENXML 、 FOR XML 、XML 类型方法（ value 、 query 等） | 主 XML 索引、辅助 XML 索引、选择性 XML 索引 | 数据交换、配置文件处理等 |
| 键值对 | 存储、检索 | 两列表实现，使用 B - 树或哈希索引 | 哈希索引（内存优化表） | 集中式缓存 |
| 非结构化文本 | 模式匹配、全文搜索 | LIKE 子句、 CONTAINS 、 FREETEXT 等 | 全文搜索（FTS）索引 | 文档搜索、网页内容搜索等 |

从这个表格可以看出，Azure SQL 针对不同类型的数据提供了多样化的处理方式，每种数据类型都有其适用的场景和操作方法。

10. 操作流程梳理

下面通过 mermaid 流程图来梳理处理不同类型数据的主要操作流程。

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px

    A([开始]):::startend --> B{数据类型?}:::decision
    B -->|地理空间数据| C(指定 SRID 进行坐标转换):::process
    C --> D(使用地理类型方法操作):::process
    D --> E(进行位置比较等操作):::process
    B -->|XML 数据| F(选择处理功能):::process
    F -->|解析| G(使用 OPENXML 函数):::process
    F -->|格式化| H(使用 FOR XML 子句):::process
    F -->|查询/更新| I(使用 XML 类型方法):::process
    B -->|键值对| J(创建两列表):::process
    J --> K(选择索引类型):::process
    K -->|哈希索引| L(创建内存优化表):::process
    B -->|非结构化文本| M{搜索需求?}:::decision
    M -->|简单模式匹配| N(使用 LIKE 子句):::process
    M -->|全文搜索| O(创建 FTS 索引):::process
    O --> P(使用 CONTAINS 等功能查询):::process

    E --> Q([结束]):::startend
    I --> Q
    L --> Q
    N --> Q
    P --> Q

这个流程图展示了根据不同的数据类型，选择相应的处理流程。从开始选择数据类型，到具体的操作步骤，最后结束处理，清晰地呈现了整个操作过程。

11. 实际应用中的注意事项

在实际使用 Azure SQL 的多模型数据处理能力时，有以下几点需要注意：
- SRID 一致性 ：在处理地理空间数据时，确保使用相同的 SRID 进行位置比较，否则会导致结果错误。
- XML 索引选择 ：对于 XML 数据，根据实际查询需求选择合适的索引类型。如果查询主要基于特定路径，可以考虑选择性 XML 索引；如果需要频繁进行全文搜索或条件过滤，可以结合主 XML 索引和辅助 XML 索引。
- 键值对性能 ：使用键值对进行缓存时，要根据数据量和访问模式选择合适的索引类型。哈希索引适用于快速的键查找，但需要注意桶数量的设置，以避免哈希冲突影响性能。
- 非结构化文本搜索 ：对于非结构化文本搜索，简单的模式匹配可以使用 LIKE 子句，但对于大规模文本数据，建议使用 FTS 索引以提高搜索性能。同时，注意 CONTAINS 和 FREETEXTTABLE 等函数的使用差异，根据搜索需求选择合适的函数。

12. 综合案例分析

假设我们有一个综合的业务系统，需要处理地理空间数据、XML 配置文件、缓存数据和非结构化文本搜索。下面是一个简化的操作示例：

-- 地理空间数据处理
DECLARE @g GEOGRAPHY;
SET @g = GEOGRAPHY::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STSrid;

-- XML 数据处理
DECLARE @x xml;
SET @x='<Family id="1804">
    <row id="17"><name>Robin</name></row>
    <row id="47"><name>Lana</name></row>
    <row id="81"><name>Merriam</name></row>
</Family>';
SELECT
  family_id = @x.value('(/Family/@id)[1]', 'int'),
  family_81_name = @x.value('(//row[@id=81]/name)[1]', 'varchar(20)'),
  family_name = @x.query('//row[@id=sql:variable("@i")]/name');

-- 键值对处理
CREATE TABLE [Cache] (
    [key] BIGINT IDENTITY,
    value NVARCHAR(MAX),
    INDEX IX_Hash_Key HASH ([key]) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

-- 非结构化文本处理
CREATE FULLTEXT CATALOG [Main] AS DEFAULT;
GO
CREATE FULLTEXT INDEX
ON Warehouse.StockItems (SearchDetails, CustomFields, Tags)
KEY INDEX PK_Warehouse_StockItems
WITH CHANGE_TRACKING AUTO;
GO
DECLARE @SearchCondition NVARCHAR(200) = 'blue car';
SELECT StockItemID = ft.[KEY], ft.[RANK]
FROM FREETEXTTABLE(Warehouse.StockItems, SearchDetails, @SearchCondition) 
AS ft

这个综合案例展示了如何在一个系统中同时使用 Azure SQL 的多模型数据处理能力，通过不同的数据类型和操作方法，满足多样化的业务需求。

13. 未来展望

随着数据类型的不断丰富和业务需求的日益复杂，Azure SQL 的多模型数据处理能力有望进一步发展。可能会在以下方面进行改进：
- 更强大的索引技术 ：针对不同类型的数据，开发更高效、更智能的索引算法，提高查询性能。
- 跨数据类型的融合处理 ：支持更复杂的跨数据类型查询和操作，例如在地理空间数据和 XML 数据之间进行关联查询。
- 简化操作接口 ：提供更简洁、易用的操作接口，降低开发人员的学习成本，提高开发效率。

总之，Azure SQL 的多模型数据处理能力为处理多样化的数据提供了强大的支持，通过不断的发展和优化，将能够更好地满足未来复杂业务场景的需求。