使用索引进行优化

8 分钟

索引是通过创建表行的优化查找路径来加速数据检索的数据结构。如果没有索引，数据库引擎必须扫描表中的每一行才能查找匹配的记录，当表增长时，完整表扫描会变得令人禁忌地缓慢。

索引的工作方式类似于书籍的索引：而不是读取每个页面来查找文章，而是咨询索引以直接跳转到相关页面。数据库同样使用索引，将可能的数百万行比较转换为少量高效查找。

但是，索引占用存储空间并减慢 INSERT、UPDATE 和 DELETE 操作的速度，因为数据库必须与数据一起维护索引结构。这种权衡使索引选择成为直接影响查询性能和写入吞吐量的关键设计决策。

不同的索引类型用于不同的目的。

使用行存储索引

设计高效的索引是实现良好的数据库和应用程序性能的关键。缺少索引、过度索引或设计不佳的索引是数据库性能问题的主要来源。

行存储索引以行格式组织数据，将行的所有列存储在同一页上，这使得它们最适合检索完整记录或执行频繁更新的事务工作负荷。

聚集索引 根据其键值对表中的数据行进行排序和存储。这些键值是索引定义中包含的列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

非聚集索引 具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。可以对表或索引视图创建多个非聚集索引。

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

当你需要高效的范围查询、稳定键和窄键或自然排序顺序（例如标识列或日期字段）时，聚集索引是最好的，因为它们定义了物理行顺序并优化对有序数据的扫描。

当需要快速查找与聚集键不一致的特定谓词、联接或排序模式时，或者想要通过包括额外的列来覆盖查询以避免键查找时，非聚集索引是理想的。

选择的依据取决于您访问数据的方式：使用聚集索引作为主要访问路径，而非聚集索引用来支持备用、高选择性或经常查询的模式，同时平衡它们在写入操作中引入的成本。

了解列存储索引

传统的行存储索引逐行存储数据，非常适合检索单个记录的事务系统。但是，扫描数百万行以计算聚合（SUM、AVG、COUNT）的分析查询，会浪费时间读取不需要的列。列存储索引旨在通过存储数据列逐列来解决此问题，只读取查询所需的列。

了解列存储体系结构

列存储索引将数据组织成行组，每个行最多包含 1,048,576 行。在每个行组中，引擎将每列单独存储为列段，并独立压缩它。此体系结构使查询优化器能够仅读取查询所需的列，完全跳过不相关的数据。

插入数据时，小批量数据首先转到deltastore，这是一种使用 B+树索引的临时行存储结构。当增量行组累积足够多的行（至少 102,400 行）时，一个名为元组移动器的后台进程会将其压缩到列存储中。通过批量加载 102,400 行或更多行的数据会绕过增量存储，直接压缩到列存储中。

下表提供了关于列存储索引的建议：

Scenario	建议	原因
数据仓库事实数据表	使用列存储	用于分析的上百万行数据表能够从列式存储和压缩中受益。
报告数据库	使用列存储	包含聚合查询的读取密集型工作负荷通过面向列的访问方式运行更快
历史数据	使用列存储	很少更新但经常分析的存档数据可实现高压缩率
小型表（<100 万行）	避免使用列存储	开销成本超过优势；行组需要具备足够的行数才能实现有效压缩。
高频率更新/删除	避免使用列存储	在修改时，将数据行标记为已删除，而不是就地更新，从而导致碎片化。
单行查找	避免列存储	用于检索单个记录的行存储索引速度更快

使用聚集列存储索引（CCI）

聚集列存储索引（CCI）是列存储索引的类型，它将成为整个表的主存储结构，替换任何现有的聚集行存储索引。非聚集列存储索引 (NCCI) 会在行存储表旁创建辅助列存储副本，而聚集列存储索引 (CCI) 则仅以列存储格式存储所有表数据。

这意味着表没有传统的基于行的存储，引擎会单独压缩和存储每列。 CCI 和 NCCI 都使用相同的列压缩和批处理优化，但当分析是主要工作负载且不需要行级事务访问模式时，优先使用 CCI。相比之下，NCCI 允许维护事务查询的行存储索引，同时为同一表上的分析查询提供列式结构。

可以使用语句创建聚集列存储索引 CREATE CLUSTERED COLUMNSTORE INDEX 。下面是一个示例：

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

使用非聚集列存储索引（NCCI）

非聚集列存储索引（NCCI）与现有行存储表一起创建一个单独的列列副本，使同一表能够高效地为事务和分析工作负荷提供服务。该表保留其原始的聚集行存储索引以实现快速单行查找和更新，而 NCCI 为分析查询提供优化的面向列的访问。查询优化器根据查询模式自动选择行存储和列存储结构。

可以使用语句创建非聚集列存储索引 CREATE NONCLUSTERED COLUMNSTORE INDEX 。下面是一个示例：

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

监视列存储索引

可以通过查询 sys.dm_db_column_store_row_group_physical_stats 动态管理视图来监视列存储索引的运行状况和性能。

以下查询显示行组统计信息，包括状态、行计数、已删除行和存储大小。开放行组仍在增量存储中接受插入，关闭行组正在等待元组移动器压缩，压缩行组以列存储格式存储数据。较高的删除行计数或众多小行组表明存在碎片，您可以使用ALTER INDEX REORGANIZE进行解决。

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

索引选择直接影响查询性能和写入吞吐量。在初始开发期间仔细设计索引，以避免在生产环境中产生成本高昂的重新生成和性能问题。

反馈

此页面是否有帮助？