Сортировка индексов

Пересказ статьи Erik Darling. Index Sortables.

Крики и вопли

Если вы читали мой пост о параметризованных TOP, вы тогда же могли возмутиться одним индексом, который я создавал.

И справедливо — это был ужасный индекс по причинам, которые мы тут и обсудим.

Если этот индекс сводит вас с ума, поздравляю, вы весьма сообразительны.

CREATE INDEX ix_whatever ON dbo.Votes(CreationDate DESC, VoteTypeId)
GO

Да, друзья мои, этот индекс плох.

Он плох не потому, что мы поставили вторым столбец, по которому выполняется фильтрация, а потому, что для него нет причин быть вторым.

CREATE OR ALTER PROCEDURE dbo.top_sniffer (@top INT, @vtid INT)
AS
BEGIN
 
    SELECT   TOP (@top) 
             v.Id, 
             v.PostId, 
             v.UserId, 
             v.BountyAmount, 
             v.VoteTypeId, 
             v.CreationDate
    FROM     dbo.Votes AS v
    WHERE    v.VoteTypeId = @vtid
    ORDER BY v.CreationDate DESC;
 
END;

Мы индексируем

Иногда причина, по которой столбцы ставят первыми в индексе, стоит в том, чтобы избежать необходимости физической сортировки данных.

Если я выполню процедуру при отсутствии каких-либо некластеризованных индексов, это приведет к такому плану запроса:

EXEC dbo.top_sniffer @top = 1, @vtid = 1;


Сортировка, слив, …. Это не обрадовало

С оригинальным индексом наши данные организованы в том же порядке, который мы просим в предложении ORDER BY.

Это вызывало всевозможные проблемы при поиске VoteTypeIds, которые были распределены по всему индексу, в результате чего мы не могли быстро удовлетворить TOP.

Никакой сортировки не наблюдается в плане, когда мы добавляем «плохой» индекс.


Отстой

Равенство в B-Tree

Мы можем также избежать сортировки, разместив столбец вторым в индексе, поскольку фильтрация выполняется по равенству.

CREATE INDEX ix_whatever ON dbo.Votes(VoteTypeId, CreationDate DESC)
GO

Размещение столбца, по которому выполняется сортировка, первым также помогает нам избежать проблемы «долгоиграющего» запроса, когда поиск выполняется по VoteTypeId = 4.

EXEC dbo.top_sniffer @top = 5000, @vtid = 4;


Это мне уже больше нравится

Table 'Votes'. Scan count 1, logical reads 2262

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 148 ms.

Решения для сортировки

Если вы какое-то время следили за моим блогом, вы, наверняка, помните, что я раньше говорил о некоторых проблемах сортировки.

  • Они накладывают локальные блокировки, т.к. каждая строка должна быть сначала получена, прежде чем выведена.
  • Они требуют дополнительной памяти для заданной упорядоченности данных.
  • Они могут приводить к сбросу данных на диск, если памяти не хватает.
  • Если оценка некорректна, может быть затребована дополнительная память.
  • Они могут оказаться в плане запроса, даже если вы их явно не запрашиваете.

Зачастую это не вызывает проблем, но полезно знать о них и о том, какие проблемы могут при этом возникнуть.

Добавить комментарий