主页 / dba / 问题 / 230756
Accepted
Erik A
Asked: 2019-02-27 03:31:45 +0800 CST

具有复合主键的联结表上的第二个索引：包括两列或一列

  • 772

我正在处理一个读取密集型数据库，并使用经常双向读取的联结表。

联结表包含两列，IDA 和 IDB

联结表的主键将是两列的复合键。

我想创建一个索引，以便数据库可以快速为特定的 IDB 查找相应的 IDA。

该索引是否应同时包含 IDB 和 IDA？还是应该只包含 IDB？

换句话说，我的表应该是这样的：

CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    CONSTRAINT AK_ExJunction_IDB_IDA UNIQUE (IDB ASC, IDA ASC)
)

或者像这样：

CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    INDEX IX_ExJunction_IDB NONCLUSTERED (IDB ASC)
)

或者，我应该完全做些别的事情吗？

我很难将其概念化，因为我一直认为非聚集索引包含索引键和主键。但是索引键已经包含在主键中，那么，它们是相同的（都包含 IDB 和 IDA）还是不同的？

sql-server clustered-index

2 个回答

  1. 你应该去第一个。

    如果选择第二个选项，您最终会得到第一个选项，但这是隐含的，因此对所有潜在消费者而言不一定显而易见。

    （SQL Server 始终将聚簇索引键添加到所有非聚簇索引 - 如果它们未声明为唯一，它们将成为键的一部分）

    • 1
  2. Best Answer

    两种选择几乎没有区别。每个都将包括IDA和IDB，因为这两列都是主键的一部分。而且 SQL Server 足够聪明，可以避免多次包含一个列。要查看这一点，请使用这两个选项创建一个表，用数据填充它，然后比较结果索引的大小：

    CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    CONSTRAINT AK_ExJunction_IDB_IDA UNIQUE (IDB ASC, IDA ASC),
    INDEX IX_ExJunction_IDB NONCLUSTERED (IDB ASC)
)
GO

DECLARE @i AS INT = 0
WHILE @i < 250000
BEGIN
  SET @i = @i + 1
  INSERT INTO ExJunction (IDA, IDB)
  VALUES (@i % 1000, @i % 1001)
END


SELECT index_id, index_type_desc, index_depth, index_level, page_count, record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('ExJunction'), NULL, NULL, 'DETAILED')

    如果比较最终查询中的两个非聚集索引，您会发现每个索引都是三层深度，每个索引都使用完全相同的页数，并且每个页的已满百分比相同。另请查看这篇文章，尤其是针对同一场景的一些评论和回复。

    由于生成的索引是相同的，我能看到的唯一可能的区别是维护开销（我在这里推测了一下；可能根本没有区别）。使用唯一性约束，由于需要确保唯一性，更新的成本可能会稍微高一些。但是，读取的效率可能稍微高一些，因为优化器提前知道行是唯一的。但也有可能数据库引擎足够聪明，可以识别出主键已经需要唯一性，并且实际上不会有任何区别。

    所以唯一的其他考虑因素是可读性和个人喜好。您是否更愿意明确指出IDA包含在非聚集索引中并且它是唯一的这一事实？还是您更愿意将定义保持尽可能小，让 SQL Server 在后端执行它的操作？

    • 1

相关问题