主页 / dba / 问题 / 48458
Accepted
BlueChippy
Asked: 2013-08-21 22:01:52 +0800 CST

PK 索引中列的顺序是否重要？

  • 772

我有几张非常大的桌子，基本结构相同。每个都有一个RowNumber (bigint)和DataDate (date)列。每晚使用 SQLBulkImport 加载数据，并且从未加载任何“新”数据 - 它是历史记录（SQL 标准，而不是企业，因此没有分区）。

因为每一位数据都需要绑定回其他系统，而且每个RowNumber/DataDate组合都是唯一的，这就是我的主键。

我注意到由于我在 SSMS 表设计器中定义 PK 的方式，RowNumber它被列为第一和DataDate第二。

我还注意到我的碎片总是非常高~99%。

现在，因为每个DataDate只出现一次，我希望索引器每天只添加到页面中，但我想知道它是否实际上是基于RowNumber第一个索引，因此必须改变其他所有内容？

Rownumber不是身份列，它是由外部系统生成的 int （可悲）。它在每个DataDate.

示例数据

RowNumber | DataDate | a | b | c..... 
   1      |2013-08-01| x | y | z 
   2      |2013-08-01| x | y | z 
...
   1      |2013-08-02| x | y | z 
   2      |2013-08-02| x | y | z 
...

数据正在按RowNumber顺序加载，DataDate每次加载一个。

导入过程是 bcp - 我尝试加载到临时表，然后从那里按顺序选择 ( ORDER BY RowNumber, DataDate) 但仍然出现高碎片。

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    PK 索引中列的顺序是否重要？

    是的，它确实。

    默认情况下，主键约束在 SQL Server 中由唯一聚集索引强制执行。聚集索引定义表中行的逻辑顺序。可能会添加许多额外的索引页来表示 b 树索引的上层，但聚集索引的最低（叶）层只是数据本身的逻辑顺序。

    为了清楚起见，页面上的行不一定以聚集索引键顺序物理存储。页中有一个单独的间接结构，用于存储指向每一行的指针。此结构按聚集索引键排序。此外，每个页面都有一个指向聚集索引键顺序中同一级别的上一页和下一页的指针。

    使用 聚集的主键(RowNumber, DataDate)，行首先按逻辑排序RowNumber，然后按DataDate- 所以所有行在RowNumber = 1逻辑上分组在一起，然后是行，RowNumber = 2依此类推。

    当您添加新数据（RowNumbers从 1 到 n）时，新行在逻辑上属于现有页面，因此 SQL Server 可能需要做大量工作来拆分页面以腾出空间。所有这些活动都会产生大量额外的工作（包括记录更改）而无济于事。

    拆分页面也开始时大约 50% 是空的，因此过度拆分也会导致页面密度低（每页的行数少于最佳值）。这不仅是从磁盘读取的坏消息（低密度 = 更多要读取的页面），低密度页面在缓存时也会占用更多内存空间。

    将聚集索引更改为(DataDate, RowNumber) 意味着新数据（可能高于DataDates当前存储的数据）将附加到新页面上聚集索引的逻辑末端。这将消除拆分页面的不必要开销并导致更快的加载时间。更少的碎片数据还意味着预读活动（在进行中的查询需要它们之前从磁盘读取页面）可以更有效。

    如果不出意外，您的查询DataDate比RowNumber. 上的聚集索引支持（然后）(DataDate, RowNumber上的索引查找。现有的安排仅支持搜索（并且可能仅支持搜索）。更改主键后，您很可能可以删除现有的非聚集索引。聚集索引将比它替换的非聚集索引更宽，因此您应该进行测试以确保性能仍然可以接受。DataDateRowNumberRowNumberDataDateDataDate

    使用 导入新数据时bcp，如果导入文件中的数据按聚集索引键排序（理想情况下(DataDate, RowNumber），您可能会获得更高的性能）并指定bcp选项：

    -h "ORDER(DataDate,RowNumber), TABLOCK"

    为了获得最佳数据加载性能，您可能会尝试实现最少记录的插入。有关更多信息，请参阅：

    • 54

  2. 是的，顺序很关键。我非常怀疑您是否曾经按 RowNumber 查询（例如WHERE RowNumber=1）。绝大多数时间序列是按日期 ( WHERE DataDate BEWEEN @start AND @end) 查询的，这样的查询将需要一个集群组织DataDate。

    一般来说，碎片化是一个红鲱鱼。减少碎片不应该是您的目标，但应该为您的查询提供适当的组织。此外，减少碎片是一个很好的想法，但它本身并不是一个目标。如果您有一个与您的工作负载相匹配的正确组织的数据模型（您的查询被正确覆盖）并且您的测量结果显示碎片会影响性能，那么我们可以讨论它。

    • 15

相关问题