为什么这个包含2个表连接的SQL语句需要5分钟才能完成？

Question

更新：以下新增了3个更新

下面的SQL语句需要5分钟才能完成。我真的不理解 :( 第一个表中有6861534行，第二个表稍微少一些...第三个表（包含4个地理字段）与第一个表相同。

那些第三个表中的 GEOGRAPHY 字段... 它们不应该影响这个SQL语句... 是吗？难道是因为表太大（由于 GEOGRAPHY 字段），所以页面大小很大，破坏了COUNT执行的表扫描吗？

SELECT COUNT(*)
FROM [dbo].[Locations] a
    inner join [dbo].[MyUSALocations] b on a.LocationId = b.LocationId
    inner join [dbo].[GeographyBoundaries] c on a.locationid = c.LocationId

更新

根据要求，以下是有关GeographyBoundaries表的更多信息...

/****** Object:  Index [PK_GeographyBoundaries]    Script Date: 11/16/2010 12:42:36 ******/
ALTER TABLE [dbo].[GeographyBoundaries] ADD  CONSTRAINT [PK_GeographyBoundaries] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

第二次更新 - 添加非聚集索引之后

添加非聚集索引之后，查询时间现在缩短到了4秒！这太棒了。但是为什么呢？

这是什么鬼？

第三次更新 - 更有趣更令人困惑的信息！

现在，当我只执行一次连接并强制使用索引时，查询时间又变成了5分钟。我这样做是为了：

• 确保MyUSALocations表没有对连接造成影响。
• 确保主键没有出现异常情况。

.

SELECT COUNT(*)
FROM [dbo].[Locations] a 
        INNER JOIN [dbo].[GeographyBoundaries] c
            WITH (INDEX(PK_GeographyBoundaries)) ON a.locationid = c.LocationId

- Pure.Krome

---

所有的LocationId字段都是int类型吗？varchar(5000)？还是其他类型？ - Tahbaza

地理边界的索引是否与表数据物理上位于同一文件组中？ - Tahbaza

@Tahbaza：是的，所有的ID都是整数。这些都在一个文件组中，或者说是默认标准。我没有对默认的创建新数据库进行任何调整。 - Pure.Krome

Answer 1

这不太对。

我有两种可能性：

1）表的统计信息已过期。重建索引并更新统计信息。

2）正如你所说，地理表记录很大，跨越许多页面（不是一个记录跨越多个页面，因为它不可能，但该记录接近8K）。在这种情况下，有趣的是，在聚集索引上创建另一个非聚集索引可能会有所帮助。

更新

我很高兴它起作用了。现在解释一下。

首先，如果某些东西不是真正正确的，并且执行计划看起来很奇怪，请始终查看统计信息并重建索引。

为聚集索引创建非聚集索引通常不应提供任何好处，但当表具有许多记录并且该记录接近其8K限制时，它是有用的。如您所知，SQL在加载记录时去磁盘时会加载8K页。以类似的方式访问索引时，它将加载8K页。现在，由于索引是4字节整数，这意味着在加载2000个记录的ID时，如果使用聚集索引，它只会加载少量记录（请记住，我们需要的只是JOIN部分的ID）。现在，由于这是二进制搜索，我不指望它会有很大帮助，只有一点点。因此，也许还有其他问题没有解决，但没有看到系统很难猜测。

- Aliostad

---

好的，我已经更新了统计数据，用了32秒。现在我再次运行查询...到目前为止已经1分钟了，仍在进行中...所以我不确定是否有太大帮助。 - Pure.Krome

兄弟！WTF！！！！！！！！！！！！通过添加一个新的非聚集索引，现在它需要4秒钟（这是我预期的）。你做到了！但是...为什么？！ - Pure.Krome

Answer 2

没有看到数据，我不能确定，但是你成本的97%来自于GeographyBonudaries表上的聚集索引扫描。LocationID是你的GeographyBoundaries表的主键吗？

你可以尝试创建一个只有locationID的新表，并与其连接，而不是使用GeographyBoundaries表，以查看地理字段是否是问题所在。只需确保设置所有索引相同，以确保你正在比较相同的内容。我猜这些字段正在拖慢你的速度。

如果这样做可以加快速度，并且经常需要进行计数，则考虑保留此新表并定期刷新它，具体取决于数据更新的频率以及你需要在任何给定时间点准确的计数。

只是出于好奇，你的PK_GeographyBoundaries的定义是什么？

- Ryan Guill

---

我已更新初始帖子，包括有关GeographyBoundaires表的更多信息，如所请求。 - Pure.Krome

啊，好的。提供的 SQL 只有 count(*)！ - James Anderson

Answer 3

我认为大部分工作都是I/O。查询计划似乎表明相对于CPU成本，I/O成本很高。例如，在PK_GeographyBoundries上，预计的CPU成本约为7.5，而预计的I/O成本约为1730，这占了预计操作成本的很大一部分。

统计数据可能已经过时，但它似乎并没有太大帮助。无论如何，它都必须扫描整个表。如果非聚集索引存储在较少的页面上，则可能受益于非聚集索引的扫描，但无论如何都会有很多I/O。而且，最好的索引是LocationID，它在两个表上都是聚集的。你已经拥有了最好的索引。

如果重要的是提高性能，那么你必须寻找加快物理I/O速度的方法。

我不确定地理列是否影响性能。查询计划显示行大小很小（每个11字节）。

- bobs

---

总之，我的硬盘不太好，我应该考虑升级吗？ - Pure.Krome

@Pure Krome：可能不是你的硬盘烂了。要提高I/O性能，你可以做两件事：1）少做I/O或2）更快地完成I/O。看起来你的Update 2展示了#1。非聚集索引做更少的I/O的原因是它占用更少的空间（仅locationID，没有geography）。扫描较少的页面需要更少的时间。 - bobs

Answer 4

你不需要涉及“locations”表，直接在USA_Locations和地理边界之间进行连接即可。优化器已经注意到这一点并排除了locations表，但是如果直接连接它们，它可能会选择更好的连接策略。

- James Anderson

---

我需要加入父级位置表，因为它有一个字段（名称NVARCHAR（100）），我需要这个字段。 - Pure.Krome