sql - 加入表索引性能改进关注点

Question

首先，我有三个表，每个表都有一个主键和一些其他数据。这些表连接在一个约有 7000 万行的连接表中:表_1、表_2、表_3。

此连接表中有一个主键跨越 Table_3_Id、Table_1_Id、Table_2_Id(按此顺序)。 Table_1_Id、Table_2_Id、Table_3_Id 上还有一个非聚集索引(按此顺序)，填充索引为 95。

数据按 Table_1_Id(我预设了大约 100 个这些 ID)和(通过连接)使用 Table_3 中的属性进行过滤(因此它使用 Table_3_Id)。然后，返回 Table_1_Id 和 Table_2_Id 值。这一切都在 Entity Framework 中的一个查询中完成。

这是查询:

  var items = dataContext.TablesJoin.AsNoTracking()
             .Join(dataContext.Table_3.AsNoTracking(), x => x.Table_3_Id, x => x.Id, (combi, scan) => new { combi, scan })
             .Where(x => possibleIds.Contains(x.combi.Table_1_Id) && otherIds.Contains(x.scan.Other_Id))
             .Select(x => new { FirstId = x.combi.Table_1_Id, SecondId = x.combi.DeviceInformationDevices_Id })
              ToList();

因为这是在 SQL Server Express 上运行的配置，所以我遇到了一些空间问题(最大 10GB)。数据大概2GB，但是主键和索引一共5GB。因为数据库中也有更多数据，所以我有兴趣在保持性能的同时减小索引的大小。

看完所有内容后，我对究竟使用了什么感到有些担忧。由于加入，我不完全确定在非聚集索引中包含 Table_3_Id 有多大用处。从索引中删除此列可节省大约 1GB 的空间。

最初，我将此表作为聚集索引(到安全空间)，但由于该表具有相当多的插入量(1000/小时)，所以它非常慢，因为所有磁盘访问都必须不断交换10GB左右的数据。如果将填充因子设置得较低(比如 70)来解决这个问题会有帮助吗？当然，这也意味着更多的空间浪费，但如果这可以在索引上节省很多，那可能是值得的？

这个表被大量使用，为了性能需要索引。在没有索引的情况下运行它需要几分钟才能执行，而在 2 秒内完成索引。

执行计划 xml:http://pastebin.com/raw.php?i=tfUxgYrK

Answer 1

您不需要主键来保证唯一性。您的 NCI 已经提供了这种独特性。您可以删除其中一个索引。这应该可以节省一些空间。

您可以通过使其他索引成簇来节省其他索引使用的空间。您注意到性能问题显然是由于插入位置随机造成的。这是有道理的。考虑更改索引的列顺序，以便插入仅发生在一个或几个地方。这样所有受影响的页面都将被缓存。 DML 所需的工作集会很低。

DML 性能问题可能不是由于页面拆分造成的。这些主要导致 CPU 负载和碎片。性能问题可能是因为必须从磁盘读取随机页面。

每小时 1000 次插入并不多。考虑将写入累积到一个小型且完全缓存的增量表中。在后台进程中将行移至主表。这样 DML 延迟就脱离了关键路径。选择要么需要容忍陈旧性，要么需要 UNION ALL 增量表。