22、数据库搜索与存储格式优化全解析-优快云博客

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

数据库搜索与存储格式优化全解析

1. 全文搜索基础

在搜索特定词汇时，全文搜索（Full - text search）有着不同的匹配规则。例如，使用 CONTAINS 时采用精确匹配，若搜索词为 car ，包含 cars 的库存项不会出现在结果中。但使用 FREETEXT 并搭配搜索表达式 children cars 时，会使用两个词的屈折形式进行搜索。

全文搜索是一个强大的工具，能通过简单表达式实现复杂搜索。它不仅适用于非结构化文本，还能用于半结构化数据，如 JSON 搜索查询。当需要过滤包含客户端定义的键值对的文档时，可利用全文搜索索引（FTS indexes）提升性能。

2. 在 JSON 数据中利用全文搜索

当要搜索大量 JSON 文档，且使用任意键值组合时，为每个可能的键添加 B - Tree 索引效率低下，而 CLUSTERED COLUMNSTORE 索引用于分析场景，不适合过滤操作。

若已知值为单个单词，可利用 JSON 以文本形式存储且键值相邻的特点。使用 CONTAINS 过滤出键值相邻的 JSON 文本，示例查询如下：

SELECT si.StockItemID, si.StockItemName, si.Tags
FROM Warehouse.StockItems AS si
WHERE CONTAINS(CustomFields, 'NEAR((Color,Silver),1)
                      AND NEAR((MakeFlag,true),1)
                      AND NEAR((SafetyStockLevel,100),1)')

CONTAINS 中的 NEAR 操作符适用于 JSON 属性的键值相邻的场景，能快速找到键值相邻的文本单元格。不过， FTS 不理解 JSON 结构中文本部分的语义，可能会返回非键值对的结果。为去除误报结果，可结合标准 JSON 谓词进行双重检查：

SELECT ProductID, Name
FROM ProductCatalog
WHERE CONTAINS(Data, 'NEAR((Color,Silver),1)
AND NEAR((MakeFlag,true),1)
AND NEAR((SafetyStockLevel,100),1)')
AND JSON_VALUE(Data,'$.Color') = 'Silver'
AND JSON_VALUE(Data,'$.MakeFlag') = 'true'
AND JSON_VALUE(Data,'$.SafetyStockLevel') = '100'

此查询结合了全文搜索和 JSON 特性的优势：
- CONTAINS 快速过滤掉不满足条件的大部分条目，减少可能包含所需数据的候选行数。
- JSON_VALUE 对全文搜索返回的较小候选集进行精确检查，确保返回正确结果。

3. Azure SQL 的多模型能力

Azure SQL 是现代多模型数据库平台，支持不同数据格式并可组合使用，以设计满足特定领域需求的最佳数据模型。关系可表示为经典外键关系或图节点/边，半结构化数据可存储在 JSON、Spatial 或 XML 列中。

Azure SQL 的核心数据库功能与多模型能力具有互操作性，可轻松将 Columnstore 与图或 JSON 数据结合，实现高性能分析，利用内置语言处理规则定制应用，使用 T - SQL 语言创建各种查询和强大报告，并与多种支持 T - SQL 的工具集成。

在选择数据库时，若有经典 NoSQL 场景，且仅需高级图或文档支持，不涉及其他适合存储在表中的数据，可考虑使用如 Azure Cosmos DB、MongoDB、Neo4j 等成熟的图或文档数据库。但如果应用需要除基本图和文档处理功能外的更多功能，如使用 T - SQL 进行高级查询、借助 Columnstore 技术进行分析、内置机器学习能力等，Azure SQL 是更好的选择。

4. 不同存储格式对比

Azure SQL 中，行存储（Rowstore）格式是表的默认存储格式，适用于大多数通用工作负载。在这种格式下，一行的单元格值物理上紧密存储在固定大小为 8KB 的页面中。当执行查询选择或更新整行或一组行时，行存储格式是不错的选择。

然而，在分析和报告查询中，行存储格式并非最佳。例如以下查询：

SELECT State, AVG(Price)
FROM Sales.Products
GROUP BY State

此类查询只需访问所有行中的两列来计算结果，即便有聚集索引帮助保持相关数据物理上相邻，仍会消耗大量资源加载不需要的列数据，影响查询性能。

列存储（Columnstore）格式常用于分析系统，可提升此类场景的性能。在列存储格式中，单元格按列段物理分组，列段是来自单个列的所有值的紧密集合，表以列段集合而非行集合的形式物理表示。

以下是行存储和列存储格式的对比表格：
| 存储格式 | 适用场景 | 性能特点 |
| ---- | ---- | ---- |
| 行存储 | 选择或更新整行或一组行的查询 | 访问单一行或小行集时效率高，加载整行数据快 |
| 列存储 | 分析和报告查询 | 读取所需列数据效率接近 100%，显著提升分析查询性能 |

列存储格式还有其他优势：
- SIMD 操作 ：现代处理器支持 SIMD 操作，列存储格式可提供连续的单元格数组，实现批量模式执行，比传统逐行处理更高效。
- 数据压缩 ：列存储利用列段中单元格值相似的特点，应用多种压缩算法，如 NULL 值消除、重复消除、字典归一化等，还可压缩整个段以节省空间。

5. Azure SQL 中的列存储实现

Azure SQL 对经典列存储组织进行了改进。理论上可创建大列段并压缩，但更新时需解压整个列段，开销大。为避免此问题，Azure SQL 进行了以下修改：
- 列段不跨全量数据 ：将表行划分为最多 100 万行的组，这些行组采用列存储格式组织，每个列段最多包含 100 万行的值。
- 新行插入缓冲区 ：所有新行插入使用行存储格式组织的缓冲区行组（Deltastore）。当这些行组达到 100 万条记录时，会在后台透明地转换并压缩为列存储格式。

Azure SQL 中列存储结构的数据行分为两个区域：
- 压缩区域 ：包含多个采用列存储格式组织的行组，行组中的列段高度压缩。
- Deltastore 区域 ：包含一个或多个采用行存储格式组织的行组，是等待压缩并移动到压缩区域的临时缓冲区。

若数据库中已有列存储索引，可使用以下系统视图查找行组：

SELECT * FROM sys.columnstore_rowgroups;

压缩行组中的列组织成列段，包含行组中指定列的所有值，该段高度压缩，所有值存储在一个连续的物理位置，且每个列段包含描述段中最小值和最大值的统计信息。

通过 mermaid 流程图展示 Azure SQL 列存储数据处理流程：

graph LR
    A[新数据插入] --> B(Deltastore区域)
    B -->|达到100万记录| C(转换并压缩)
    C --> D(压缩区域)

综上所述，了解全文搜索在不同数据类型中的应用，以及行存储和列存储格式的特点与适用场景，能帮助我们根据具体需求选择合适的数据库功能和存储格式，优化数据库性能。

数据库搜索与存储格式优化全解析

6. 内存优化表与OLTP性能提升

内存优化表是提升在线事务处理（OLTP）工作负载性能的有效方式，尤其适用于具有创建、读取、更新和删除（CRUD）操作的场景。与传统磁盘存储表不同，内存优化表将数据存储在内存中，减少了磁盘 I/O 操作，从而显著提高了数据访问速度。

以下是使用内存优化表的一些优势和操作步骤：
- 优势
- 高速访问 ：数据存储在内存中，避免了磁盘 I/O 延迟，使得数据读取和写入操作更快。
- 并发性能 ：支持更高的并发访问，多个事务可以同时对内存优化表进行操作，而不会产生过多的锁竞争。
- 操作步骤
1. 创建内存优化文件组 ：在数据库中创建一个专门用于存储内存优化表的文件组。
sql ALTER DATABASE YourDatabase ADD FILEGROUP YourMemoryOptimizedFilegroup CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE YourDatabase ADD FILE (NAME = 'YourMemoryOptimizedFile', FILENAME = 'C:\YourPath\YourMemoryOptimizedFile') TO FILEGROUP YourMemoryOptimizedFilegroup;
2. 创建内存优化表 ：使用 MEMORY_OPTIMIZED = ON 选项创建内存优化表。
sql CREATE TABLE YourMemoryOptimizedTable ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1048576), Column1 NVARCHAR(50), Column2 INT ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
3. 插入和查询数据 ：可以像操作普通表一样对内存优化表进行插入和查询操作。
sql INSERT INTO YourMemoryOptimizedTable (Column1, Column2) VALUES ('Value1', 1); SELECT * FROM YourMemoryOptimizedTable;

7. 时态表与历史数据管理

时态表允许保存数据的完整变更历史，并支持进行历史和时间旅行分析。在许多业务场景中，需要跟踪数据的变化过程，时态表提供了一种方便的方式来实现这一需求。

时态表分为系统版本控制时态表和用户定义时态表，这里主要介绍系统版本控制时态表。
- 系统版本控制时态表
- 原理：系统版本控制时态表由两个表组成，一个是当前表，存储当前数据；另一个是历史表，自动记录数据的历史版本。数据库系统会在数据发生变更时自动管理历史表的插入和更新操作。
- 操作步骤
1. 创建系统版本控制时态表 ：使用 SYSTEM_VERSIONING = ON 选项创建时态表。
sql CREATE TABLE YourTemporalTable ( ID INT NOT NULL PRIMARY KEY, Column1 NVARCHAR(50), ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourHistoryTable));
2. 插入和更新数据 ：对时态表进行插入和更新操作时，数据库会自动记录历史版本。
sql INSERT INTO YourTemporalTable (ID, Column1) VALUES (1, 'Initial Value'); UPDATE YourTemporalTable SET Column1 = 'Updated Value' WHERE ID = 1;
3. 进行时间旅行查询 ：可以使用 FOR SYSTEM_TIME 子句查询特定时间点的数据。
sql SELECT * FROM YourTemporalTable FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

8. 不同存储格式的综合应用案例

为了更好地理解不同存储格式的应用，我们来看一个综合案例。假设我们有一个电商数据库，需要处理订单数据和销售分析。
- 订单数据处理 ：对于订单数据，由于需要频繁进行插入、更新和查询操作，使用内存优化表可以提高 OLTP 性能。例如，创建一个内存优化的订单表：

CREATE TABLE OrdersMemoryOptimized (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1048576),
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10, 2)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

销售分析 ：对于销售分析，需要处理大量数据并进行聚合操作，列存储格式更适合。例如，创建一个列存储索引的销售表：

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales ON Sales.Products;

历史订单跟踪 ：为了跟踪订单的历史变更，使用时态表。例如，创建一个系统版本控制的订单表：

CREATE TABLE OrdersTemporal (
    OrderID INT NOT NULL PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10, 2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

通过综合使用不同的存储格式和数据库功能，我们可以根据不同的业务需求优化数据库性能，提高数据处理效率。

9. 总结与建议

在数据库设计和优化过程中，我们需要根据具体的业务场景和需求选择合适的数据库功能和存储格式。以下是一些总结和建议：
| 业务场景 | 推荐选择 |
| ---- | ---- |
| 通用查询，选择或更新整行数据 | 行存储格式 |
| 分析和报告查询，需要处理大量数据 | 列存储格式 |
| 高并发 OLTP 工作负载，CRUD 操作频繁 | 内存优化表 |
| 需要跟踪数据历史变更，进行时间旅行查询 | 时态表 |

同时，在使用全文搜索时，要根据搜索需求选择合适的搜索方式，如 CONTAINS 用于精确匹配， FREETEXT 用于更灵活的搜索。在处理 JSON 数据时，结合全文搜索和 JSON 谓词可以提高查询效率和结果准确性。

通过合理运用这些数据库功能和存储格式，我们可以优化数据库性能，满足不同业务场景的需求，为企业的数据分析和业务决策提供有力支持。

通过 mermaid 流程图展示根据业务场景选择存储格式的流程：

graph LR
    A[业务场景] --> B{场景类型}
    B -->|通用查询| C(行存储格式)
    B -->|分析报告| D(列存储格式)
    B -->|高并发OLTP| E(内存优化表)
    B -->|历史数据跟踪| F(时态表)

总之，深入了解数据库的各种功能和存储格式，根据实际需求进行合理选择和应用，是优化数据库性能的关键。希望本文能为你在数据库设计和优化方面提供有价值的参考。