主页 / dba / 问题 / 313784
Accepted
fmi21
Asked: 2022-06-27 00:40:46 +0800 CST

3 列的聚集索引是否太大？

  • 772

我的目标是设计一个表，可以通过外部 id( uniqueidentifier)、内部 id( bigint) 进行查询，始终与和/或与 ( , n=0,10) 结合使用companyId(bigint)，这两个条件都起到所有权检查的作用。userId(bigint)dashboardId(bigint)dashboardId IN @0, ..., @n

我想出了以下指数组成：

CREATE CLUSTERED INDEX Mytable_createdBy_cix ON Mytable(companyId, createdBy, dashboardId)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_extId_nix ON Mytable(extId) INCLUDE (valueD, valueN)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_chartId_nix ON Mytable (chartId) INCLUDE (valueD, valueN)

我不知道以下问题的答案：

  • 聚集索引是否因为非唯一而坏？我应该添加隔离键而不使用自动分配的uniqueifier吗？
  • 3 * 8 字节列 + 4 字节唯一符（总共 28 字节）对于聚集索引来说太多了吗？我读到它包含在每个唯一非聚集索引的包含页面中（根据使用的键添加额外的 16 或 8 个字节）。
  • 这种索引设计对下面的查询是否有意义？

我计划运行查询，类似于：

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId

SELECT chartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)

SELECT chartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2)

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND chartId = @chartId

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId= @extId

UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId= @extId

我确实知道，最好在 stackexchange 上提问时测试、评估执行计划并分享它们，但这是设计阶段，因此还没有实际的数据或表格。

我可以调整键/索引/表结构以更好地适应查询。我只是希望在第一次创建它们时至少部分正确，所以这个问题不会被重新讨论。

非常感谢您提前提供的任何帮助。

sql-server index-tuning

2 个回答

  1. Best Answer

    首先，请注意，仅在实际存在重复值的情况下才添加非唯一键上的唯一符。如果同一索引页面上没有重复项，则不会占用任何空间。因此，除非有两行完全相同，否则companyId, createdBy, dashboardId不会发生这种情况。

    宽集群键可能会出现问题，但它们也解决了一些死锁问题，因此这可能是一个因素。无论如何都不清楚您选择的集群键是否正确，但另一方面：UNIQUE考虑到表设计，两个非集群索引如何有意义？如果它们是唯一的，那么为什么这些查询中所有谓词都是必需的？

    从您的评论看来，额外chartId的只是拥有一个较小的索引列。我认为这可能是一个过早的优化：它只是增加了额外的索引成本，因为您现在还需要索引该列。我建议您删除它，并完全依赖它，exrChartId即使它更宽。

    对于给定的查询，您需要处理它们并决定如何最好地使用索引来满足它们。哪个应该是聚簇索引的问题有些正交，因为聚簇索引有效地INCLUDE自动对所有列。

    每个人都可以使用也满足不同索引的索引，只要前导键列相同，而不管键或INCLUDE.

    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId
      这可以满足以下指标
      (companyId, createdBy) INCLUDE (extChartId, valueD, valueN)
    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId
      这可以满足以下指标
      (companyId, createdBy, dashboardId) INCLUDE (extChartId, valueD, valueN)
    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)
      这可以满足以下指标
      (dashboardId) INCLUDE (extChartId, valueD, valueN)
    1. SELECT extChartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2) 这个比较困难，需要一个索引联合（可能需要重写查询才能得到它）。所需的索引将与 #1 和 #3 相同
    1. 我们把这个改成只使用自然键，所以和#6一模一样
    1. UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId = @extId
      因为extChartId是唯一的，其他列都可以进去，INCLUDE
      所以需要一个索引 (extChartId) INCLUDE (companyId, createdBy, valueD)
    1. UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId = @extId
      同样，由于OR. 可能有必要将其拆分为两个单独的更新。但鉴于这extChartId是独一无二的，我们可以再次依赖相同的索引。

    查看这些指标，我们得出以下结论：

    • 谓词都是=相等谓词，或者IN在一个短列表中，因此键列可以是任何顺序。这极大地帮助了我们组合索引。
    • 适合#1 的索引可以有额外的列来适应#2，但不适合#3。同样适用于#3 的一种也适用于#2，但不适用于#1。所以我们需要单独的索引。问题仍然是这些组合中的哪些也可以满足其他查询。
    • #4 可以使用与前三个相同的索引，所以我们不用担心。
    • #6 和 #7 需要extChartId，你说这是独一无二的。因此，所有其余的列都可以进入，而INCLUDE对性能影响很小。

    因此，索引的最佳组合是这样的

    (companyId, createdBy, dashboardId) INCLUDE (extChartId, valueD, valueN)
(dashboardId) INCLUDE (extChartId, valueD, valueN)
(extChartId) INCLUDE (companyId, createdBy, dashboardId, valueD)

    问题仍然是您选择哪一个作为集群键。无论您选择哪一个，INCLUDE所有其他列都会如此。

    第一个或第三个索引对我来说最有意义。鉴于它extChartId本身是独一无二的，正如您正确指出的那样，由于尺寸的原因，使用它可能更有意义。

    但是死锁也可能是一个问题，这取决于您的事务更新等的复杂性。从那个开始，如果您发现这是一个问题，请切换集群密钥。

    • 4

  2. 我确实知道，最好在 stackexchange 上提问时测试、评估执行计划并分享它们，但这是设计阶段，因此还没有实际的数据或表格。

    在设计阶段为每个键添加一个唯一索引，并为每个键不支持的外键添加一个额外的非聚集索引。

    然后随着您的开发，评估查询执行并考虑其他索引。

    • 2

相关问题