为什么我的SQL Server ORDER BY语句很慢，尽管排序列已被索引？

Question

我有一个SQL查询（由LINQ to Entities生成），大致如下：

SELECT * FROM [mydb].[dbo].[employees]
JOIN [mydb].[dbo].[industry]
  ON jobs.industryId = industry.id
JOIN [mydb].[dbo].[state]
  ON jobs.stateId = state.id
JOIN [mydb].[dbo].[positionType]
  ON jobs.positionTypeId = positionType.id
JOIN [mydb].[dbo].[payPer]
  ON jobs.salaryPerId = payPer.id
JOIN [mydb].[dbo].[country]
  ON jobs.countryId = country.id
WHERE countryName = 'US'
ORDER BY startDatetime

这个查询返回大约1200行，我认为这不算很多。不幸的是，它需要大约16秒才能完成。如果去掉ORDER BY，查询时间少于1秒。

我使用SQL Server Management Studio在startDatetime列上创建了一个索引，还创建了一个聚集索引在“cityId、industryId、startDatetime、positionTypeId、payPerId、stateId”上（即我们在JOINs和ORDER BY中使用的jobs所有列）。每个用于JOINs的列都已经有单独的索引了。不幸的是，这并没有使查询变得更快。

我运行了showplan并得到了：

   |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[cityId]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[stateId]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[industryId]))
       |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[positionTypeId]))
       |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[salaryPerId]))
       |    |    |    |    |--Sort(ORDER BY:([mydb].[dbo].[jobs].[issueDatetime] ASC))
       |    |    |    |    |    |--Hash Match(Inner Join, HASH:([mydb].[dbo].[currency].[id])=([mydb].[dbo].[jobs].[salaryCurrencyId]))
       |    |    |    |    |         |--Index Scan(OBJECT:([mydb].[dbo].[currency].[IX_currency]))
       |    |    |    |    |         |--Nested Loops(Inner Join, WHERE:([mydb].[dbo].[jobs].[countryId]=[mydb].[dbo].[country].[id]))
       |    |    |    |    |              |--Index Seek(OBJECT:([mydb].[dbo].[country].[IX_country]), SEEK:([mydb].[dbo].[country].[countryName]='US') ORDERED FORWARD)
       |    |    |    |    |              |--Clustered Index Scan(OBJECT:([mydb].[dbo].[jobs].[PK_jobs]))
       |    |    |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[payPer].[PK_payPer]), SEEK:([mydb].[dbo].[payPer].[id]=[mydb].[dbo].[jobs].[salaryPerId]) ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[positionType].[PK_positionType]), SEEK:([mydb].[dbo].[positionType].[id]=[mydb].[dbo].[jobs].[positionTypeId]) ORDERED FORWARD)
       |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[industry].[PK_industry]), SEEK:([mydb].[dbo].[industry].[id]=[mydb].[dbo].[jobs].[industryId]) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[state].[PK_state]), SEEK:([mydb].[dbo].[state].[id]=[mydb].[dbo].[jobs].[stateId]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([mydb].[dbo].[city].[PK_city]), SEEK:([mydb].[dbo].[city].[id]=[mydb].[dbo].[jobs].[cityId]) ORDERED FORWARD)

关键的一行似乎是"| - Sort（ORDER BY：（[mydb]。[dbo]。[jobs]。[issueDatetime] ASC））"——没有提到该列上的任何索引。

为什么我的ORDER BY会使查询变得如此缓慢，我该如何加快查询速度？

- George

---

你所连接的所有外键列是否都被索引了？ - marc_s

@marc_s：是的，所有单独表上的ID列也都被索引了。我有99.9%的把握不是JOIN导致的效率低下问题，因为去掉ORDER BY（只保留JOIN）后执行时间从大约16秒降至少于1秒。 - George

Answer 1

如果您的查询不包含order by子句，那么它将按照数据被找到的顺序返回。没有保证数据在再次运行查询时会以相同的顺序返回。

当您包含order by子句时，数据库必须按照正确的顺序构建行的列表，然后按照该顺序返回数据。这可能需要额外的处理时间，导致额外的时间消耗。

对于可能返回的大量列，排序可能需要更长的时间。在某个点上，您的缓冲空间将会耗尽，数据库将不得不开始交换，性能将会下降。

尝试返回较少的列（指定您需要的列而不是使用select *），看看查询是否运行得更快。

- Scott Bruns

---

所以我认为这个是“正确”的答案 - 我的描述列非常大（它存储了一块HTML），这将每行的大小推高，这意味着排序必须要到磁盘上进行。 - George

由于列数较多，您应该首先返回排序列的Id，然后可以为每个Id选择整个元组。 - Saeed Neamati

内存不足，MSSQL正在使用SWAP进行排序，这并不快... - Thibault D.

@SaeedNeamati 你是怎么做到的？ - undefined

@jtlz2，我已经很久没有使用SQL Server了，我不记得了。抱歉。 - undefined

Answer 2

由于您的查询涉及所有列（*），需要5个列用于连接条件，并且在可能是连接表列的非选择性WHERE子句上，它导致它撞到了索引翻转点：优化器决定扫描整个表格、过滤和排序比范围扫描索引更为节省成本，然后查找表中的每个键以检索所需的额外列（连接的5个和其余的*）。

一个更好的部分覆盖此查询的索引可能是：

CREATE INDEX ... ON .. (countryId, startDatetime);

Jeffrey建议创建聚集索引，可以完全覆盖查询并显著提高性能，但更改聚集索引会有许多副作用。我建议从上面的非聚集索引开始。除非它们被其他查询所需，否则您可以删除所有其他非聚集索引，它们对此查询没有帮助。

- Remus Rusanu

Answer 3

您也可以尝试以下代码：

将记录插入临时表，而不使用Order by子句

SELECT * into #temp FROM [mydb].[dbo].[employees]
JOIN [mydb].[dbo].[industry]
  ON jobs.industryId = industry.id
JOIN [mydb].[dbo].[state]
  ON jobs.stateId = state.id
JOIN [mydb].[dbo].[positionType]
  ON jobs.positionTypeId = positionType.id
JOIN [mydb].[dbo].[payPer]
  ON jobs.salaryPerId = payPer.id
JOIN [mydb].[dbo].[country]
  ON jobs.countryId = country.id
WHERE countryName = 'US'

现在使用Order By子句运行该语句。

Select * from #temp ORDER BY startDatetime

- Pankaj

---

运行速度显著提高了 - 整个查询现在只需要<1s。有没有办法要求LINQ to SQL生成该查询，还是我必须手写一些SQL？我已经做了一些谷歌搜索 - 材料化/索引视图是否有帮助？ - George

我不确定现在这是否有帮助 - 最初看起来好像这使得事情变得更快了，但是： SELECT * into #temp FROM [atr].[dbo].[jobs] 不起作用 - “列名必须唯一”（多个“id”列）所以我尝试了： SELECT title, cityName, stateName INTO #temp...这很快（<1s），但如果我加入“description”（varchar(MAX)）列，就会变慢： SELECT title, cityName, stateName, description INTO #temp...因此，如果查询中存在（1）ORDER BY AND（2）VARCHAR(MAX)字段，则查询速度会变慢。 - George

我的假设是该列太大了，导致每行的大小增加，因此总数据大小增加，因此 ORDER BY 需要对大量数据进行排序 - 太多无法放入内存，因此我们会遇到磁盘交换/分页/抖动/等问题。 - George

Answer 4

对某一列进行索引并不能提高排序速度。

如果你想让查询速度更快，那么可以反转表的顺序。具体来说，在连接表时，将表country放在第一个位置。

这么做的原因是where子句可以从第一个表中过滤行，而不需要进行所有的连接，然后再过滤行。

- Bohemian

---

索引不是按排序顺序存储的吗？当我添加它时，我可以选择“排序：升序”和“排序：降序”。我在SQLite中使用索引使查询中的ORDER BY更快。我交换了连接的顺序，并将查询时间从16秒降至7秒-但ORDER BY仍然占用了所有7秒的时间。难道真的没有办法让ORDER BY更快吗？ - George

@George：索引是按排序顺序存储的，但通常每个表只能选择一个索引，在这种情况下，它选择聚集索引PK_jobs，因为它是一个覆盖索引。如果存在更紧迫的问题，如连接，则索引的存在对于ORDER BY没有帮助。 - Jeffrey Hantin

Answer 5

聚集索引中包含哪些字段的顺序？为了使ORDER BY与之匹配，您需要首先将startDateTime字段放在前面，或者在这种情况下，按照(countryId, startDateTime)的顺序排列，因为您想选择一个单一的countryId（间接地通过countryName），然后按startDateTime排序。

- Jeffrey Hantin

---

我已经重新排列了我的第一个聚集索引中的列，使countryId排在第一位，startDateTime排在第二位，并且还添加了一个仅包含countryId和startDateTime的单独索引。查看我的查询计划，查询似乎正在命中连接表的PK索引和我的“employees”表上的PK，但没有命中其他任何内容（也没有命中我的聚集索引）。速度并没有改善。 - George

你应该知道，聚集索引中的列也会被添加到该表上每个非聚集索引的每个条目中 - 如果你的聚集索引变得臃肿，例如由多个大列组成，则整个表的索引结构都会变得臃肿，从而严重影响性能。如果可能的话，我会尽量避免使用复合聚集键，而且绝对不会在任何情况下使用长度大于等于10个字符的VARCHAR列。 - marc_s

@marc_s：是的，就是这个问题——我的描述字段太大了（索引只是一个误导）。非常感谢你的帮助。 - George