Создание соответствующего индекса для часто используемого запроса в SQL Server
В моем приложении у меня есть два запроса, которые будут вполне часто использоваться. Операторы Where этих запросов следующие:
WHERE FieldA = @P1 AND (FieldB = @P2 OR FieldC = @P2)
и
WHERE FieldA = @P1 AND FieldB = @P2
P1 и P2 параметры, вводимые в UI или прибывающий из внешних источников данных.
- FieldA
intи очень групповой, средства: только два, три, четыре различных значения в таблице с говорят 20 000 строк - FieldB является a
varchar(20)и "почти" уникально, будет только очень немного строк, где FieldB мог бы иметь то же значение - FieldC является a
varchar(15)и также очень отличный, но не так как FieldB - FieldA и FieldB вместе уникальны (но не формируйте мой первичный ключ, который является простым столбцом идентификационных данных автопостепенного увеличения с кластерным индексом),
Я задаюсь вопросом теперь, что является лучшим способом определить индекс для ускорения конкретно этих двух запросов. Я определю один индекс с...
FieldB (or better FieldC here?)
FieldC (or better FieldB here?)
FieldA
... или лучшие два индекса:
FieldB
FieldA
и
FieldC
FieldA
Или есть ли даже другие и более оптимальные варианты? Каков лучший способ и почему?
Спасибо за предложения заранее!
Править:
Так же, как информация другим читателям: Здесь был другой ответ, который был удален теперь. На самом деле ответ казался очень полезным для меня. Рекомендация состояла в том, чтобы создать два индекса (согласно моей второй опции выше) и повторно сформулировать первый запрос при помощи a UNION из двух избранных операторов (один с WHERE FieldA = @P1 AND FieldB = @P2 и один с WHERE FieldA = @P1 AND FieldC = @P2) вместо OR извлечь выгоду из обоих индексов (который не имел бы место с операцией ИЛИ).
Edit2:
Оператор, которые с ИЛИ индексы не используются и что ОБЪЕДИНЕНИЕ предпочтительно, кажется, является неправильным - по крайней мере, согласно моим собственным тестам (см. мой собственный ответ ниже).
2 ответа
Расширение ответа Remus' (правка: теперь удалена)...
- если @p2 является varchar(15), то вы не можете надежно сравнивать с FieldB, это varchar(20)
- , если @p2 varchar(20), то FieldC будет преобразован в varchar(20) и не будет использовать индекс (или в лучшем случае сканировать его)
- , если @p1 имеет только 2, 3, 4 значения тогда почему бы не уменьшить и не уменьшить размер таблицы/индекса?
Я бы не стал беспокоиться об индексах, пока вы не решите эту проблему приоритета типов данных: это в верхней части проблемы предложения OR.
Наконец, столбец уникален или неуникален: между ними нет. Статистика помогает здесь с избирательностью, но она не имеет значения.
Я бы изменил индексы из ответа Ремуса на FieldB, FieldA (и уникальные) и FieldC, FieldA из-за селективности FieldA
Edit, после комментариев: вы не можете сравнивать использование @p2 с использованием константных строк.
Я добавляю свой ответ после некоторых тестов с большей базой данных (в SQL Server 2008):
Во-первых, я выбрал второй вариант, то есть я создал два индекса:
CREATE UNIQUE NONCLUSTERED INDEX [IX_B] ON [dbo].[MyTable]
(
[FieldB] ASC,
[FieldA] ASC
)
CREATE NONCLUSTERED INDEX [IX_C] ON [dbo].[MyTable]
(
[FieldC] ASC,
[FieldA] ASC
)
Я протестировал два запроса:
declare @p1 int = 1;
declare @p2 varchar(20) = '12345678';
select * from MyTable
where FieldA=@p1 and (FieldB=@p2 or FieldC=@p2);
Выполняя этот запрос, я получаю следующий план запроса ( ID - это первичный ключ таблицы, PK_MyTable кластерный индекс на первичный ключ):
|--Nested Loops(Inner Join, OUTER REFERENCES:([MyDb].[dbo].[MyTable].[ID]))
|--Stream Aggregate(GROUP BY:([MyDb].[dbo].[MyTable].[ID]) DEFINE:([MyDb].[dbo].[MyTable].[FieldA]=ANY([MyDb].[dbo].[MyTable].[FieldA])))
| |--Merge Join(Concatenation)
| |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_B]), SEEK:([MyDb].[dbo].[MyTable].[FieldB]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_C]), SEEK:([MyDb].[dbo].[MyTable].[FieldC]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[PK_MyTable]), SEEK:([MyDb].[dbo].[MyTable].[ID]=[MyDb].[dbo].[MyTable].[ID]) LOOKUP ORDERED FORWARD)
Похоже, используются оба индекса («Поиск индекса»).
Истекшее время для запроса: 00: 00: 00.2220127
Второй запрос, который я тестировал, использовал JOIN, чтобы избежать оператора OR (см. «Изменить» в моем вопросе):
declare @p1 int = 1;
declare @p2 varchar(20) = '12345678';
select * from MyTable where FieldA=@p1 and FieldB=@p2
union
select * from MyTable where FieldA=@p1 and FieldC=@p2;
Этот запрос имеет следующее план запроса:
|--Merge Join(Union)
|--Nested Loops(Inner Join, OUTER REFERENCES:([MyDb].[dbo].[MyTable].[ID]))
| |--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_B]), SEEK:([MyDb].[dbo].[MyTable].[FieldB]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[PK_MyTable]), SEEK:([MyDb].[dbo].[MyTable].[ID]=[MyDb].[dbo].[MyTable].[ID]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([MyDb].[dbo].[MyTable].[ID]))
|--Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[IX_C]), SEEK:([MyDb].[dbo].[MyTable].[FieldC]=[@p2] AND [MyDb].[dbo].[MyTable].[FieldA]=[@p1]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDb].[dbo].[MyTable].[PK_MyTable]), SEEK:([MyDb].[dbo].[MyTable].[ID]=[MyDb].[dbo].[MyTable].[ID]) LOOKUP ORDERED FORWARD)
Снова используются оба индекса («поиск по индексу»).
Истекшее время для запроса: 00: 00: 00.3710212
Примечание. Для обоих запросов не имеет значения, какую длину я объявляю @ p2 с помощью: Использование varchar (8), varchar (20) или varchar (30) дает те же результаты и планы запроса.
Следуя этим результатам, я остановлюсь на использовании оператора OR вместо UNION, поскольку оба запроса используют индексы, но первый выполняется быстрее.