Объясните оптимальный запрос для большой таблицы с кластеризованным индексом в SQL Server 2008.

Я работаю над очень большой таблицей (примерно 2,7 миллиона строк добавляется в день), которая имеет следующую структуру:

CREATE TABLE [dbo].[Result](
    [ResultDate] [date] NOT NULL,
    [Thing1Id] [int] NOT NULL,
    [Num] [int] NOT NULL,
    [Thing2Id] [int] NOT NULL,
CONSTRAINT [PK_Result] PRIMARY KEY CLUSTERED 
(
    [ResultDate] ASC,
    [Thing1Id] ASC,
    [Num] ASC
))

Поскольку кластеризованный первичный ключ находится в ResultDate, Thing1Id и Num, я ожидаю, что следующий запрос будет оптимальным:

SELECT Thing2.* 
FROM dbo.Result
INNER JOIN Thing2 ON Thing2.Id = result.Thing2Id
WHERE 
    ResultDate >= '2012-01-01'
    AND
    ResultDate <= '2012-01-30'
    AND Thing1Id = 23

Как видите, запрос находит результаты за 12 января для конкретной вещи 1.

Однако план выполнения показывает, что можно добиться значительного увеличения производительности, добавив следующий индекс:

CREATE NONCLUSTERED INDEX [IX_Missing]
ON [dbo].[Result] ([Thing1Id],[ResultDate])
INCLUDE ([Num],[Thing2Id])

И, конечно же, добавление этого индекса значительно повышает производительность.

Может кто-нибудь объяснить, почему? Насколько я понимаю, результаты должны быть достаточно сужены с использованием кластеризованного первичного ключа, и добавление этого сделает размер индекса намного больше и добавит ненужные накладные расходы.

Могу ли я индексировать таблицу по-другому, чтобы повысить производительность?

(Обратите внимание, что на самом деле таблица представляет собой две объединенные таблицы, данные передаются из одной в другую ежедневно, а данные секционируются ежемесячно).


sql-server performance clustered-index sql-execution-plan
person smartypantsfe    schedule 05.12.2012    source источник
comment
Бьюсь об заклад, даже простое добавление некластеризованного индекса к dbo.Result(Thing2Id) немного ускорит ваш запрос, основываясь на том факте, что Thing2Id является внешним ключом и используется в вашем выражении INNER JOIN Thing2 ON Thing2.Id = result.Thing2Id...   -  person marc_s    schedule 05.12.2012
comment
Да, ты прав. Я уже добавил индекс для Thing2Id, который ускоряет работу, но я не включил его в сообщение, потому что меня больше интересует кластеризованный индекс. Спасибо.   -  person smartypantsfe    schedule 06.12.2012
comment
Я не понимаю, почему индекс Thing2Id может помочь в этом запросе. Простое добавление индекса, потому что у него есть FK, может больше навредить, чем помочь в таких больших таблицах, как эта.   -  person Wim    schedule 07.12.2012
comment
Вероятно, Thing1id является более избирательным, или SQL Server выполняет какое-то пересечение индексов. Опубликуйте планы выполнения, и мы будем знать наверняка.   -  person usr    schedule 28.04.2013
comment
Можете ли вы опубликовать планы выполнения запросов для обоих?   -  person Justin    schedule 04.06.2013

Ответы (3)


arrow_upward
0
arrow_downward

Индексы в основном упорядочивают вашу таблицу по «ключу». В вашем случае «thing1ID», «ResultDate». Когда таблица отсортирована, доступ к строкам происходит намного быстрее, чем цикл по всей таблице (2,7 мил), потому что вы не знаете, где может быть строка.

то есть 2,7,3,8,1, вам нужно выполнить поиск по всей таблице, чтобы добраться до номера 1. Но если у вас будет 1, 2, 3, 7, 8. вы проверяете только первый номер.

НО! для таблицы, в которой есть много обновлений/вставок с использованием «ключа», будет замедление, потому что вам нужно сортировать таблицу после каждой записи. Так что выясните, что лучше для вашей БД.

person Gustav Klimt    schedule 05.12.2012
comment
Именно поэтому кластеризованный индекс находится на ResultDate THEN Thing1Id. Боюсь, ваш комментарий не поможет объяснить, зачем нужен дополнительный индекс. - person smartypantsfe; 06.12.2012

arrow_upward
0
arrow_downward

PK не является оптимальным для вашего запроса, поскольку вы выполняете поиск по диапазону на ResultDate. С помощью вашего запроса вы сужаете поиск Thing1Id 23 до прибл. 81 миллион строк, что все еще много.

В вашем запросе поиск по Thing1Id зафиксирован на 23, поэтому дополнительный индекс по Thing1Id и ResultDate будет оптимальным для вашего запроса.

person Wim    schedule 05.12.2012
comment
Спасибо, Вим. Так это лучший способ проиндексировать таблицу, или вы можете предложить лучший способ? - person smartypantsfe; 06.12.2012
comment
Невозможно сказать, лучший ли это способ индексации таблицы, не зная точно содержимого базы данных и всех запросов. Но это хороший индекс для этого запроса. Я бы просто создал индекс, провел несколько тестов производительности и снова проанализировал результаты. - person Wim; 07.12.2012

arrow_upward
0
arrow_downward

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

Во-первых, часть INCLUDE ([Num],[Thing2Id]) вашего индекса просто означает, что значение этих двух столбцов дублируется как в индексе, так и в самой таблице. Это полезно, потому что может помешать SQL Server искать эти данные в самой таблице после выполнения поиска в этом индексе (в этом случае индекс представляет собой покрывающий индекс), однако, как правило, этот поиск выполняется довольно быстро и поэтому вряд ли напрямую отвечает за "значительно" улучшенную производительность. . Я предполагаю, что следующий индекс на 99,9% быстрее.

CREATE NONCLUSTERED INDEX [IX_Missing]
ON [dbo].[Result]
(
    [Thing1Id],
    [ResultDate]
)

Прежде чем мы продолжим, важно понять, что SQL Server может выполнить этот запрос двумя способами (значительно упрощенными для целей объяснения):

  1. Найдите все строки, которые имеют ResultDate между двумя указанными датами, а затем просмотрите эти строки на наличие тех, которые имеют Thing1Id из 23.
  2. Найдите все строки, в которых Thing1Id равно 23, а затем просмотрите эти строки в поисках строк, в которых ResultDate находится между двумя указанными датами.

В зависимости от того, какие данные присутствуют в таблице, один из этих подходов может быть значительно быстрее, чем другой, например, если большинство строк в таблице имеют Thing1Id из 23 и очень немногие имеют совпадающее ResultDate. тогда, вероятно, будет быстрее использовать первый подход, поскольку он быстрее устраняет больше строк.

Другая важная часть головоломки, которую нам нужно понять, заключается в том, что из-за того, как работают индексы, SQL не может использовать ваш кластеризованный индекс во втором случае, потому что столбец Thing1Id идет после столбца ResultDate ( это все равно, что попросить кого-то использовать индекс в книге, чтобы найти все записи, вторая буква которых "Q", а затем затем попросить их просмотреть и выбрать только те слова, которые начинаются с "С")

Поэтому я предполагаю, почему этот индекс улучшает производительность, просто потому, что для SQL Server более эффективно использовать подход 2 (сначала фильтровать по Thing1Id), чем подход 1.

Вы должны быть в состоянии использовать планы выполнения запросов, чтобы подтвердить это.

person Justin    schedule 04.06.2013