SQL Server 2005 -奇数聚集索引大小

Existing table structure

CREATE TABLE [MYTABLE](
    [ROW1] [numeric](18, 0) NOT NULL,
    [ROW2] [numeric](18, 0) NOT NULL,
    [ROW3] [numeric](18, 0) NOT NULL,
    [ROW4] [numeric](18, 0) NULL,
    CONSTRAINT [MYTABLE_PK] PRIMARY KEY CLUSTERED ([ROW1] ASC, [ROW2] ASC, [ROW3] ASC)
)

This table has 2 non-clustered indexes, and the following stats:

此表有2个非聚集索引，以及以下数据:

RowCount:    5260744
Data Space:  229.609 MB
Index Space: 432.125 MB

I wanted to reduce the size of the indexes, and use a surrogate primary key as the clustered index, instead of the natural composite key.

我希望减少索引的大小，并使用代理主键作为聚集索引，而不是使用自然的复合键。

New table structure

CREATE TABLE [dbo].[TEST_RUN_INFO](
    [ROW1] [numeric](18, 0) NOT NULL,
    [ROW2] [numeric](18, 0) NOT NULL,
    [ROW3] [numeric](18, 0) NOT NULL,
    [ROW4] [numeric](18, 0) NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [MYTABLE_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
)

Still with only 2 non-clustered indexes, here's the new stats:

仍然只有两个非聚集索引，这里是新的统计数据:

RowCount:    5260744
Data Space:  249.117 MB
Index Space: 470.867 MB

Question

Can someone account for how a clustered index using 3 NUMERIC(18,0) columns is smaller than a clustered index using a single INT column?

有人能解释为什么使用3个数字(18,0)列的聚集索引比使用单个INT列的聚集索引要小吗?

I rebuilt the indexes before and after the changes, and the fill factor is set to 0 for both structures.

我在修改前后重新构建了索引，对于这两个结构，填充因子都被设置为0。

The two non-clustered indexes are the same, and were not changed to include the new ID column.

这两个非聚集索引是相同的，并且没有被更改为包含新的ID列。

sys.dm_db_index_physical_stats

Stats taken with the ID column

ID列包含的状态

Composite clustered index

复合聚集索引

INDEX   TYPE            DEPTH   LEVEL   PAGECOUNT   RECORDCOUNT RECORDSIZE  
1       CLUSTERED       3       0       31884       5260744     47
1       CLUSTERED       3       1       143         31884       34
1       CLUSTERED       3       2       1           143         34
5       NONCLUSTERED    3       0       27404       5260744     40
5       NONCLUSTERED    3       1       167         27404       46
5       NONCLUSTERED    3       2       1           167         46
6       NONCLUSTERED    3       0       27400       5260744     40
6       NONCLUSTERED    3       1       164         27400       46
6       NONCLUSTERED    3       2       1           164         46

INT clustered index

INT聚集索引

INDEX   TYPE            DEPTH   LEVEL   PAGECOUNT   RECORDCOUNT RECORDSIZE  
1       CLUSTERED       3       0       31887       5260744     47
1       CLUSTERED       3       1       54          31887       11
1       CLUSTERED       3       2       1           54          11
5       NONCLUSTERED    4       0       29893       5260744     44
5       NONCLUSTERED    4       1       198         29893       50
5       NONCLUSTERED    4       2       3           198         50
5       NONCLUSTERED    4       3       1           3           50
6       NONCLUSTERED    4       0       29891       5260744     44
6       NONCLUSTERED    4       1       193         29891       50
6       NONCLUSTERED    4       2       2           193         50
6       NONCLUSTERED    4       3       1           2           50

1 个解决方案

#1

The clustered index leaf pages include all the columns of the table (not just the key columns). By adding a surrogate primary key you have just increased the length of all rows in the leaf pages by 4 bytes. Multiply that out by 5,260,744 rows and that equals an additional 20 MB to store the ID column.

聚集索引页包括表的所有列(不只是键列)。通过添加代理主键，您只需将叶页中的所有行的长度增加4个字节。再乘以5,260,744行，这就等于额外的20mb来存储ID列。

The key is narrower however so you may well have fewer non leaf level pages (use sys.dm_db_index_physical_stats to see this) and as the clustered index key is used as the row locator in the non clustered indexes this can make those smaller (but less covering) too.

但是键比较窄，因此您可能会有更少的非叶级页面(使用sys)。dm_db_index_physical_stats将看到这一点)，由于集群索引键用作非集群索引中的行定位符，这也可以使这些索引更小(但覆盖更少)。

秒客网