Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Порядок поле в индексе и селективность  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Допустим есть таблица с полями OrderType и ClientId.
Поле OrderType может принимать одно из пяти значений, в то время как ClientId одно из 200 тыс.
В таблице порядка 300 тыс записей.
На данный момент на таблице построен следующий некластерный индекс:
CREATE NONCLUSTERED INDEX [IX_OrderType_ClientId] ON Orders 
(
	[OrderType] ASC,
	[ClientId] ASC
)
INCLUDE (
[field1],
[field2],
[field13]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FG_Data_01]

Верно ли утверждение, что если перестроить индекс, поменяв порядок полей на
	[ClientId] ASC,
	[OrderType] ASC

то выборка по запросу
select ClientId, OrderType, field1, field2, field3 from Orders where ClientId=@ClientId and OrderType=@OrderType

будет идти быстрее?
17 сен 12, 14:44    [13176146]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
В принципе фиолетово.
17 сен 12, 15:00    [13176342]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Kudep,

точно могу сказать, что порядок полей в индексе будет влиять на то,
в каком из нижеследующих запросов будет вообще применяться этот индекс:
select ClientId, OrderType, field1, field2, field3 from Orders where ClientId=@ClientId
select ClientId, OrderType, field1, field2, field3 from Orders where OrderType=@OrderType
Ибо если первое поле в индексе не используется в предикатах и т.п., то индекс не используется вовсе.
17 сен 12, 15:03    [13176374]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Возможно люди слышат слово "селективность", но путают причинно-следственную связь (и качают деревья).
17 сен 12, 15:26    [13176630]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
В принципе фиолетово.
Абсолютно согласен, но есть небольшой нюанс. Вместе с индексом создается статистика. 
И статистика по [ClientId], [OrderType] может оказаться весьма интереснее для оптимизатора чем по [OrderType], [ClientId], из-за разной селективности.
Поэтому если решено делать индекс по [OrderType], [ClientId] (ввиду наличия запросов только по [OrderType]), то имеет смысл рассмотреть создание ручной статистики по [ClientId], [OrderType]. Лично сталкивался с ситуациями, где это оказывалось критично.


Еще будет небольшая разница в случае запроса с неравенством:
select ClientId, OrderType, field1, field2, field3 from Orders where ClientId BETWEEN @ClientId_Min and @ClientId_Max and OrderType=@OrderType
Поиск по диапазону будет выполняться быстрее если поле стоит в конце индекса.
18 сен 12, 01:06    [13179701]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Mind
Поиск по диапазону будет выполняться быстрее если поле стоит в конце индекса.
Можно сказать ещё жёстче:
индекс по второму полю будет использоваться,
только если первое поле проверяется на точное равенство (=).
18 сен 12, 09:14    [13180073]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mind
Поэтому если решено делать индекс по [OrderType], [ClientId] (ввиду наличия запросов только по [OrderType]), то имеет смысл рассмотреть создание ручной статистики по [ClientId], [OrderType]. Лично сталкивался с ситуациями, где это оказывалось критично.
По идее, должно быть достаточно статистики просто по одному столбцу ClientId, которая создается автоматически, если авто статистика не отключена, sqlcat что-то такое писал на эту тему. Интересно, в каких ситуациях была критична статистика именно по двум столбцам?
18 сен 12, 09:26    [13180124]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
Mind
Поэтому если решено делать индекс по [OrderType], [ClientId] (ввиду наличия запросов только по [OrderType]), то имеет смысл рассмотреть создание ручной статистики по [ClientId], [OrderType]. Лично сталкивался с ситуациями, где это оказывалось критично.
По идее, должно быть достаточно статистики просто по одному столбцу ClientId, которая создается автоматически, если авто статистика не отключена, sqlcat что-то такое писал на эту тему. Интересно, в каких ситуациях была критична статистика именно по двум столбцам?
Понятно, что распределение хранится только для первой колонки в статистике, по остальным только плотность, но тем не менее.

А вот что было я уже точно не вспомню. Вроде запрос вида:
select column1, column2 from table1
except
select column1, column2 from table2
Давал очень плохие эстимейты если не была созданна статистика по 2м полям сразу, в обеих таблицах.
18 сен 12, 10:11    [13180326]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mind,

Плотность, по идее, должна быть одинаковая для a,b и b,a, т.е. если есть индекс a,b оптимизатор может воспользоваться тем значением...Ну ладно, в любом случае, благодарю, присмотрюсь на досуге к запросам такого вида.
18 сен 12, 10:36    [13180487]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SomewhereSomehow
Mind,

Плотность, по идее, должна быть одинаковая для a,b и b,a, т.е. если есть индекс a,b оптимизатор может воспользоваться тем значением...Ну ладно, в любом случае, благодарю, присмотрюсь на досуге к запросам такого вида.
Хм, действительно, ну тогда не знаю в чем был прикол. Если еще увижу такое, то отпишусь.
18 сен 12, 10:51    [13180599]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
iap
Mind
Поиск по диапазону будет выполняться быстрее если поле стоит в конце индекса.
Можно сказать ещё жёстче:
индекс по второму полю будет использоваться,
только если первое поле проверяется на точное равенство (=).

у меня немножко мозг взорвался, пока пытался это осмыслить.
Возьмем индекс из моего примера в начале темы. Индекс построен по полям [OrderType], [ClientId].
Если я в запросе укажу следующий предикат:
where OrderType between 2 and 5 and ClientId=9000

В этом случае мой индекс будет использоваться. Это видно из плана выполнения.
А вы т.е. хотите сказать, что сиквел где-то у себя в потрохах на самом деле использует индекс только для поиска по первому условию, а для удовлетворения второго делает index scan, так что ли?
18 сен 12, 20:59    [13185078]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Kudep,

Индекс-то будет использоваться, но речь про второе поле.
Будет примерно так: находим первое значение диапазона (2), выполняем частичный просмотр всего индекса пока не встретим последнее (точнее следующее после него) значение(5), во время частичного просмотра, проверяем каждое из значений второго поля на соответствие предикату(=9000). Т.е. по сути для второго поля residual предикат, сканирование и фильтрация. Т.е. просматриваем больше строк чем надо. Чтобы этого избежать нужно что-то вроде skip scan (который реализован, пока что, вроде как только для особого случая просмотра по секциям, а в общем случае нет).
18 сен 12, 21:17    [13185142]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow, да но дотошный увидит в плане 4ре прямых SEEK-а:
OrderType = 2, ClientId=9000
OrderType = 3, ClientId=9000
OrderType = 4, ClientId=9000
OrderType = 5, ClientId=9000
Не помню что там надо, только статистика или ключ. Может ещё Enterprize.

А вот если поставить диапазон побольше, то тогда будет уже RANGE SEEK.
Только вот всё зависит от статистики, запроса и немного невезения.

SomewhereSomehow
skip scan
Это что за зверь?
Не верю - ибо тогда можно уже вешать M$ за яйца.
18 сен 12, 23:35    [13185678]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
SomewhereSomehow, да но дотошный увидит в плане 4ре прямых SEEK-а:
OrderType = 2, ClientId=9000
OrderType = 3, ClientId=9000
OrderType = 4, ClientId=9000
OrderType = 5, ClientId=9000
Не помню что там надо, только статистика или ключ. Может ещё Enterprize.

А вот если поставить диапазон побольше, то тогда будет уже RANGE SEEK.
Только вот всё зависит от статистики, запроса и немного невезения.

Ну вот в том и дело, что я не увидел, чтоб between преобразовался в подобие in (2,3,4,5) и было выполнено 4 seek. Мне кажется, в общем случае такого не будет. Возможно есть какие-то особенные случаи, если у вас есть пример, приводите, это будет интересно!

Mnior
Это что за зверь?
Не верю - ибо тогда можно уже вешать M$ за яйца.

Я вот про это: Query Processing Enhancements on Partitioned Tables and Indexes
Кто-то уже даже сделал фидбэк на коннекте: Implement Index Skip Scan
еще немного Index Skip Scan
19 сен 12, 10:01    [13186561]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow, возможно я слишком диагонально прочитал, но вроде как мы говорим об одном и тоже.
Секции как заранее определённый набор (для SEEK). Но это не тот Skip Scan.

В данном случае IMXO пока это не проблема и всегда добавляется табличка для 1-й колонки в запрос (принцип VIEW прямо напрашивает этот подход). Т.е. в принципе это всё сводится к синтаксическому сахару не более, притом если M$ не сделает - я пойму.

Совсем другое дело, когда надо к примеру искать края (Max/Min) по колонке. Там Skip Scan просто неистово необходим. Но тут SEEK совсем не причём.

Т.е.
1. Надо не путать реальный Skip, когда низко-уровневый цикла скана продолжает бежать (хитрым способом) по индексным страницам, от того что сейчас делается (повторно от корня ищется следующий ключ).
2. Условие поиска по вложенной колонке не причём, главное сам механизм Partiotion индекса по колонкам.

Связанная тема: Агрегаты Min, Max при группировке на "хорошем" индексе.
19 сен 12, 17:14    [13191013]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
SomewhereSomehow
Ну вот в том и дело, что я не увидел, чтоб between преобразовался в подобие in (2,3,4,5) и было выполнено 4 seek.
Эх, если думал что это ценно, тогда может и запомнил где я это видел, просто такие конструкции в жизни не приживаются. Можете считать что я наврал.
19 сен 12, 17:29    [13191102]     Ответить | Цитировать Сообщить модератору
 Re: Порядок поле в индексе и селективность  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
SomewhereSomehow, возможно я слишком диагонально прочитал, но вроде как мы говорим об одном и тоже.
Секции как заранее определённый набор (для SEEK). Но это не тот Skip Scan.

В данном случае IMXO пока это не проблема и всегда добавляется табличка для 1-й колонки в запрос (принцип VIEW прямо напрашивает этот подход). Т.е. в принципе это всё сводится к синтаксическому сахару не более, притом если M$ не сделает - я пойму.

Совсем другое дело, когда надо к примеру искать края (Max/Min) по колонке. Там Skip Scan просто неистово необходим. Но тут SEEK совсем не причём.

Даже если вы прочитали по диагонали вы поняли все верно. Именно что требуется не синтаксический сахар, а как раз особый метод доступа. Не нужно сканить весь индекс, если уже заведомо найден первый сет результатов, нужно переходить к следующему дистинктному значению, в поисках второго, игнорируя скан строк в первом, которые заведомо не подходят и далее по индукции.
На нашем примере, это выглядело бы так: нашли первое поле 2, нашли значение 9000, зачем дальше сканить 9001,9002...и т.д. Переходи сразу ко второму значению 3 и т.д.
Насчет дефиниции касательно секций - это к MS, они называют это skip scan, в той ссылке что я привел. И алгоритм похож. Но к обычным индексам которых over 9000 это не имееет отношение, видимо по этому появился item на connect (кстати голосуем, если актуально). Связанную тему пока не читал. М.б. завтра.

Mnior
Эх, если думал что это ценно, тогда может и запомнил где я это видел, просто такие конструкции в жизни не приживаются. Можете считать что я наврал.

Ну, я не буду так считать, ибо неисповедимы пути (припомним тему где вы тоже выдвинули предположение, которое сначала не подтвердилось, однако, позже я нашел пример). Я пока просто считаю что в общем, это не так, оставляя место для возможных частных случаев (хотя правил и функций напоминающих по названию такое преобразование не нашел)... Но кто его знает.
19 сен 12, 19:18    [13191624]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить