Почему мой SQL Server ORDER BY медленный, несмотря на индексирование упорядоченного столбца?

У меня есть 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 second.

Я использовал SQL Server Management Studio, чтобы поместить индекс в столбец startDatetime, а также кластерный индекс в "cityId, industryId, startDatetime, positionTypeId, payPerId, stateId" (то есть все столбцы в "заданиях", которые мы используем в JOINs и в столбце, в котором мы используем ORDER BY). У меня уже есть отдельные индексы для каждого столбца, который мы используем в JOIN. К сожалению, это не ускорило запрос.

Я запустил 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 делает мой запрос намного медленнее, и как я могу ускорить свой запрос?

Ответы

Ответ 1

Если ваш запрос не содержит порядка, он вернет данные в любой найденный файл. Нет гарантии, что данные будут даже возвращены в том же порядке, когда вы снова запустите запрос.

Когда вы включаете предложение order by, dabatase должен построить список строк в правильном порядке и затем вернуть данные в этом порядке. Это может занять много дополнительной обработки, что переводится в дополнительное время.

Вероятно, потребуется больше времени для сортировки большого количества столбцов, которые могут быть возвращены вашим запросом. В какой-то момент у вас закончится буферное пространство, и базе данных придется начинать замену, а производительность будет идти вниз.

Попробуйте вернуть меньше столбцов (укажите нужные столбцы вместо Select *) и посмотрите, выполняется ли запрос быстрее.

Ответ 2

Поскольку ваш запрос проектирует все столбцы (*), для условий соединения требуется 5 столбцов и имеет неселективное предложение WHERE на вероятном объединенном столбце таблицы, это приводит к тому, что он попадает в Индексирующая точка опроса: оптимизатор решает, что для сканирования всей таблицы было бы менее дорогостоящим, отфильтровать его и отсортировать, чтобы он мог варьировать сканирование индекса, а затем искать каждый ключ в таблицу для получения необходимых дополнительных столбцов (5 для объединений, а остальные для *).

Лучшим индексом для частичного покрытия этого запроса может быть:

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

Предложение Джеффри сделать кластеризованный индекс охватит запрос 100% и, безусловно, улучшит производительность, но изменение кластерного индекса имеет много побочных эффектов. Я бы начал с кластерного индекса non-, как указано выше. Если они не понадобятся другим запросам, вы можете удалить все остальные non- кластерные индексы, которые вы создали, они не помогут этому запросу.

Ответ 3

В каком порядке включены поля в кластерном индексе? Сначала вы должны поместить поле startDateTime для соответствия ORDER BY или в этом случае (countryId, startDateTime) спереди в этом порядке, так как вы хотите выбрать один countryId (косвенно, через countryName), а затем порядок startDateTime.

Ответ 4

Вспышка новостей: индексирование столбца не ускоряет сортировку.

Если вы хотите сделать свой запрос A LOT быстрее, измените порядок ваших таблиц. В частности, перечислим таблицу country сначала в ваших присоединенных таблицах. Причина? Предложение where может фильтровать строки из первой таблицы, а не создавать все эти соединения, а затем фильтровать строки.

Ответ 5

Вы также должны попробовать под кодом

Вставьте записи в временную таблицу. Без использования предложения Порядок заказа

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 Clause

Select * from #temp ORDER BY startDatetime