Блог

Я пытаюсь выяснить, какой вариант выбрать лучше всего, мое основное требование — уменьшить ввод-вывод.

• У меня есть таблица с 500 миллионами записей, где приведенный ниже запрос выбирает кластеризованное сканирование индекса по умолчанию в таблице.
• Я попытался создать охватывающий некластеризованный индекс, но он по-прежнему выбирает сканирование кластеризованного индекса по умолчанию. итак, я заставил его использовать покрывающий индекс, и, по моим наблюдениям, логические чтения сократились с 3 м до 1 М, но процессор и продолжительность увеличились.
• Я пытаюсь понять поведение и что здесь лучше.

Запрос:

 set statistics time, io on;
select 
    min(CampaignID), 
    max(CampaignID) 
from Campaign
where datecreated < dateadd(day, -90, getutcdate())
go
CREATE NONCLUSTERED INDEX [NCIX] 
ON [dbo].[Campaign](DateCreated)
INCLUDE (Campaignid)
go
select 
    min(CampaignID), 
    max(CampaignID) 
from Campaign with (index = NCIX)
where datecreated < dateadd(day, -90, getutcdate())
set statistics time, io off;
 

Сообщения:

 (1 row affected)
Table 'Campaign'. Scan count 2, logical reads 3548070, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(8 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 14546 ms,  elapsed time = 14723 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 3 ms.

(1 row affected)
Table 'Campaign'. Scan count 1, logical reads 1191017, physical reads 0, page server reads 0, read-ahead reads 19, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(6 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 163953 ms,  elapsed time = 164163 ms.
 

Планы выполнения:

Полный план выполнения

Прежде всего, нет оператора «best». Иногда чтение большего количества данных более эффективно, чем чтение некоторых данных и их массирование для получения наших результатов. «Лучший», поскольку почти все относительно.

Давайте попробуем понять, что произошло в комментариях…

Запрос

 select 
    min(CampaignID), 
    max(CampaignID) 
from Campaign
where datecreated < dateadd(day, -90, getutcdate())
 

В котором говорится:

Мне нужен первый и последний идентификатор (min / max) любой записи, где дата меньше постоянной даты.

Кластеризованный

Первый запрос без подсказки index / index сделал то, что SQL Server считал дешевле, чем чтение любого индекса, даже если для этого требуется больше ввода-вывода (использование диска). Это связано с тем, что поиск минимального и максимального значений при проверке записей в таблице обходится дешевле, чем выбор половины таблицы, а затем переупорядочивание / агрегирование их для поиска точно такой же информации.

Кластеризованный индекс хранит все данные на диске и логически упорядочен по ключевым столбцам, в данном случае CampaignID (я полагаю). Это означает, что найти минимальный и максимальный идентификатор легко: минимум — это первый идентификатор, который соответствует критериям -> давайте проверим каждый идентификатор с первого и остановимся, как только найдем запись, в которой указана дата (скорее всего, это будет первая). Максимум — это первая запись, соответствующая условию с конца индекса.

Индекс с датой в качестве ключа

 CREATE NONCLUSTERED INDEX [NCIX] 
ON [dbo].[Campaign](DateCreated)
INCLUDE (Campaignid)
 

С первым индексом (дата в качестве ключевого столбца) SQL Server может использовать дату для фильтрации данных, правда, но это не помогло в сортировке. Он все равно должен проверять каждую запись в этом индексе и определять минимум и максимум из, возможно, неупорядоченного набора значений.

Индекс с идентификатором в качестве ключа

 CREATE NONCLUSTERED INDEX [NCIX] 
ON [dbo].[Campaign](Campaignid)
INCLUDE (DateCreated)
 

Со вторым индексом, где идентификатор был ключевым столбцом, SQL Server может использовать тот же трюк, что и с кластеризованным ключом. Единственное отличие состоит в том, что нет других данных для чтения, кроме идентификатора и даты, которые намного меньше, чем целая запись, поэтому она может поместиться на меньшем количестве страниц и требует меньшего ввода-вывода.

SQL Server, скорее всего, выберет второй индекс, даже если нет подсказки индекса.

Как работает второй индекс (приближение по запросу)

Вы можете получить минимальный Campaignid с помощью

 SELECT TOP(1)
  Campaignid
FROM
  [dbo].[Campaign]
WHERE
  datecreated < dateadd(day, -90, getutcdate())
ORDER BY
  Campaignid ASC
 

и максимум с очень похожим запросом

 SELECT TOP(1)
  Campaignid
FROM
  [dbo].[Campaign]
WHERE
  datecreated < dateadd(day, -90, getutcdate())
ORDER BY
  Campaignid DESC
 

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

Примечания

Здесь я бы добавил примечание: оптимизация только для одного запроса не всегда является лучшей тактикой. Вы не можете оптимизировать все, если этот запрос выполняется один раз в день / неделю / квартал, то 14-15 секунд времени выполнения с кластеризованным ключом, скорее всего, не повредит. Если индекс не помогает другим запросам, я бы не создавал его, если только это не критически важный запрос.