主页 / dba / 问题 / 317863
Accepted
Ross Bush
Asked: 2022-10-06 05:46:46 +0800 CST

基于键字段的聚集索引能否比大表的非聚集覆盖索引更高效

  • 772

我正在研究一个会变得非常大的 DW 报告表。为简单起见，我将显示该表如下：

BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL

每个查询都将使用公司名称在数据分区（而不是物理分区）内进行查询。

由于该表可能包含超过十亿行，并且每个公司的数据分布非常均匀，因此按公司查询应该尽可能快。我正处于设置一些测试的阶段，但在这样做之前，我想我会问一下，看看这是否会浪费时间。

我的想法是确定如果每个公司的数据分区通过聚集索引在磁盘上彼此相邻放置，那么数据检索是否会比仅使用非聚集索引覆盖 CompanyName 更快。

示例 1：这是 IDENTITY 列是 PK 但不是 CLUSTERED 的变体。CompanayName 和 TableID 结合起来形成聚集索引，因此数据将按公司在磁盘上排序。

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
    [CompanyName] ASC,
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

这是创建具有覆盖索引的表的传统方式。

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
    [CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

有谁知道在使用第一个示例而不是第二个示例时是否会有任何性能改进？

编辑：我倾向于在公司中使用聚集索引。如果行需要唯一引用，TableID 只是用作 PK 的自动增量字段。我觉得聚集索引搜索/扫描比索引扫描/搜索更快。

我希望您可以轻松地根据 companyid 之类的东西进行分区或分片。

基本查询的形式为

SELECT
    SUM(FieldA) OVER (PARTITION BY ...) a,
    COUNT(1) OVER (PARTITION BY...) b
    ...       
FROM
    BigTable 
WHERE
    CompanyName = 'NABISCO'
GROUP BY
   ....
ORDER BY
   ....
sql-server index-tuning

1 个回答

  1. Best Answer

    根据评论，这是一个尝试回答。

    总之，您说您总是在 CompanyName 上过滤您的查询。

    在 CompanyName 上有一个聚集索引，TableID 确实是有益的，因为 SQL Server 可以在“数据”中导航到正确的公司，然后只读取该特定公司的行。

    考虑使用数据压缩。并确保您评估行和页面压缩。人们往往会忘记行压缩，但考虑到它几乎不存在开销，在某些情况下它可能是一种非常有吸引力的压缩类型。

    拥有列存储索引可能更有益。部分原因是与无、行或页面压缩相比，压缩率更高。但也因为您更有可能在执行计划中看到操作员的批处理模式。2019 年可以获取不带列存储索引的批处理模式，但需要 2019 年数据库兼容级别和企业版。

    您希望使用列存储索引覆盖查询。要么是具有查询所需的所有列的非集群。或者在您的情况下可能更有吸引力的是聚集列存储索引 - 您现在还意识到列存储索引的存储节省。

    一方面是行在行组中的布局方式（行组大约有 100 万行，具体取决于您加载新数据的方式等）。您想根据公司“聚集”它。搜索公司 A，如果公司 A 的行仅限于一小组行组，您现在可以在运行时很好地消除行组（也称为段消除）。SQL Server 具有每个列和每个行组的最低和最高值的元数据。创建索引时，您将确保 SQL Server“发生”以所需顺序读取行 - 通过在该列上创建行聚集索引并使用 CREATE INDEX ... WITH DROP EXISTING (基本上将行聚集索引转换为列聚集索引）。

    行组消除和数据类型存在限制。我相信您还没有消除字符串数据类型。即，仔细考虑这将是 CompanyName 还是 CompanyID 列！下一个版本计划扩展对行组消除的类型支持。

    然后是添加数据的方面。添加一堆行，这些行可能适用于您的许多客户，并且它们将位于同一个行组中 - 现在必须为您即将进行的查询读取此行组。即，如果您在行组消除时添加数据，索引将随着时间的推移而“降级”，让您决定是否重建索引（到目前为止，这仍然有点棘手，因为我们缺少一些 ORDER 子句）以便重新-根据公司对行进行聚类。

    • 3

相关问题