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

Откуда:
Сообщений: 33
Добрый день

Есть таблица TestIndx (тестовый стенд)

CREATE TABLE [dbo].[TestIndx](
	[ID] [int] NOT NULL,
	[OrderID] [int] NULL,
	[CustomerID] [int] NULL,
	[OrderValue] [decimal](18, 2) NULL,
 CONSTRAINT [PK_TestIndx] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
) 

также создан композитный некластерный индекс
CREATE NONCLUSTERED INDEX [Order_Customer] ON [dbo].[TestIndx] 
(
	[CustomerID] ASC,
	[OrderID] ASC
)


Таблица заполнена тестовыми данными
Insert into TestIndx
Select number,number%10000,number/10000,ABS(checksum(newID()))/10000 from Numbers


Количество записей в тестовой таблице - 4 194 304

Далее начали возникать вопросы: нужно обоснованно определить порядок следования полей в композитном индексе.
В интернете(может вновь ошибочно) прочитал, что поля должны следовать в порядке убывания селективности.
Тестовые данные распределены равномерно, даже я бы сказал циклично. В поле CustomerID - 420 уникальных значений, в поле OrderID -10000 уникальных значений.
Беру формулу расчета селективности(нашел здесь , в ветках форума), считаю для поля CustomerID 1/420=0.00238 для поля OrderID 1/10000=0.0001 т.е.0.23% для CustomerID и 0.01% для OrderID т.е. OrderID Более селективен и порядок полей в индексе должен быть
CREATE NONCLUSTERED INDEX [Order_Customer] ON [dbo].[TestIndx] 
(
	[OrderID] ASC,
	[CustomerID] ASC
)


Но, беру произвольный запрос
Select CustomerID from TestIndx Where  OrderID>1000 AND CustomerID>300

и при первоначальном порядке следования столбцов получаю:

(1065186 row(s) affected)
Table 'TestIndx'. Scan count 1, logical reads 2650, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


План выполнения:
SELECT [CustomerID] FROM [TestIndx] WHERE [OrderID]>@1 AND [CustomerID]>@2
  |--Index Seek(OBJECT:([Indx].[dbo].[TestIndx].[Order_Customer]), SEEK:([Indx].[dbo].[TestIndx].[CustomerID] > CONVERT_IMPLICIT(int,[@2],0)),  WHERE:([Indx].[dbo].[TestIndx].[OrderID]>CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)


При перемене порядка следования столбцов исходя из расчета описанного выше:

(1065186 row(s) affected)
Table 'TestIndx'. Scan count 5, logical reads 8508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


План выполнения:
SELECT [CustomerID] FROM [TestIndx] WHERE [OrderID]>@1 AND [CustomerID]>@2
  |--Parallelism(Gather Streams)
       |--Index Seek(OBJECT:([Indx].[dbo].[TestIndx].[Order_Customer]), SEEK:([Indx].[dbo].[TestIndx].[OrderID] > CONVERT_IMPLICIT(int,[@1],0)),  WHERE:([Indx].[dbo].[TestIndx].[CustomerID]>CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)


Теперь вопрос: почему если ставить поля в порядке уменьшения селективности, то запрос выполняется хуже? Неправильный расчет селективности композитного индекса?
Помогите разобраться как обоснованно определить порядок полей в композитном индексе.
11 май 16, 12:48    [19157731]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
iljy
Member

Откуда:
Сообщений: 8711
sqldbmail,

поля нужно ставить в первую очередь в порядке использования. Если у вас индекс (f1, f2), диапазон значений f1 (0..100000), условие f1 > 3 and f2 = 5, то индекс по f1 может быть суперселективным, только это нифига не даст, потому что будет почти полное его сканирование, а условие на f2 в поиске не будет использовано совсем. Потому что условие на неравенство может быть использовано только для ОДНОГО поля в индексе, причем все поля перед ним должны в условии так же присутствовать и проверяться на равенство.

В вашем случае всегда поиск использует условие только на первое поле, при этом условие OrderID>1000 дает вам 90% записей в таблице, а CustomerID>300 - 25%. Отсюда и разница.
11 май 16, 13:06    [19157881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy
sqldbmail,
поля нужно ставить в первую очередь в порядке использования.

Что понимается под порядком использования?
Это порядок следования полей в условии where?
11 май 16, 13:10    [19157915]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy,
т.е. расчет получается верный и порядок следования должен быть OrderID,CustomerID?
11 май 16, 13:13    [19157944]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
o-o
Guest
sqldbmail
iljy
sqldbmail,
поля нужно ставить в первую очередь в порядке использования.

Что понимается под порядком использования?
Это порядок следования полей в условии where?

в where можно как угодно расставлять, речь о порядке следования полей ключа индекса
при объявлении этого индекса.
если у вас в запросе куча условий на равенство, то поля из этих условий в ключ индекса можно в любом порядке включать.
а если в where неравенства, как у вас, то только одно может быть использовано для поиска,
как вам выше iljy написал.
разумеется, нужно из всех условий на неравенство выцепить самое селективное
и это поле в ключе поставить первым
(ну или первым после тех полей, что были в условиях типа равенство)
11 май 16, 13:20    [19158008]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
iljy
Member

Откуда:
Сообщений: 8711
sqldbmail
iljy
sqldbmail,
поля нужно ставить в первую очередь в порядке использования.

Что понимается под порядком использования?
Это порядок следования полей в условии where?

AND - коммутативный оператор, так что порядок в условии значения не имеет. "Порядок использования" имеется в виду использование полей в условиях f1= 1 and f2 >5, f1 = 1 and f2 = 5 and f3<10 и т.п. Если у вас, например, f1 и f2 всегда проверяются в паре на равенство, то первым надо ставить поле с большей селективностью, если условия идут вида f1 > 5 или f1 = 4 and f2 > 7, то ставить f2 на первое место бессмысленно. Почитайте про устройство B-tree.
11 май 16, 13:21    [19158021]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy
sqldbmail,

а условие на f2 в поиске не будет использовано совсем.



Совсем запутался. Если второе условие не используется совсем, то как отбираются записи?
11 май 16, 13:25    [19158064]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
[quot o-o]
sqldbmail
пропущено...

разумеется, нужно из всех условий на неравенство выцепить самое селективное

ну я вроде так и сделал, но получил несколько не те результаты.... что я сделал не правильно?
11 май 16, 13:28    [19158091]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
я запутался:
1 вариант:

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

и это поле в ключе поставить первым
(ну или первым после тех полей, что были в условиях типа равенство)

2 вариант
Если у вас, например, f1 и f2 всегда проверяются в паре на равенство, то первым надо ставить поле с большей селективностью,
11 май 16, 13:37    [19158175]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
iljy
Member

Откуда:
Сообщений: 8711
sqldbmail
iljy
sqldbmail,

а условие на f2 в поиске не будет использовано совсем.



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


Вы целиком фразы читайте. Второе условие не будет использовано ПРИ ПОИСКЕ, это не означает, что оно не будет проверено при окончательной фильтрации записей. Просто оно никак не поможет уменьшить количество записей, читаемых из таблицы.

Почитайте книги и статьи по организации индексов, например
https://habrahabr.ru/post/247373/
https://habrahabr.ru/post/102785/
https://www.sql.ru/articles/mssql/03013101indexes.shtml
11 май 16, 13:40    [19158199]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy,
за ссылки спасибо, почитаю
11 май 16, 13:44    [19158241]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
iljy
Member

Откуда:
Сообщений: 8711
sqldbmail
разумеется, нужно из всех условий на неравенство выцепить самое селективное[/b]
и это поле в ключе поставить первым
(ну или первым после тех полей, что были в условиях типа равенство)


Понятие "селективность неравенства" довольно странное. Для равенства можно построить некое среднее значение количества записей на уникальное значения поля. Для неравенства все гораздо сложнее, хотя в принципе тоже можно построить кривую распределения. Для единичного же запроса это понятие довольно бессмысленно. В вашем случае (и я вам это уже написал, если бы читали все, то не путались бы) OrderID>1000 дает вам 90% записей в таблице, а CustomerID>300 - 25%. Если взять условие OrderID > 9500, то это условие даст вам 5%, и индекс с первым полем OrderID окажется эффективнее.
11 май 16, 13:45    [19158251]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy,


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


попробовал запрос
Select CustomerID from TestIndx Where CustomerID=300 AND OrderID=9998 


Здесь при любом раскладе 1 скан и 3 логических чтения.

Попробовал следующий запрос при полях в индексе OrderID CustomerID OrderID более селективен(если считать)
Select CustomerID from TestIndx Where CustomerID=300 AND OrderID>9998 

И получил картину аналогичную описанной в начале топика. Плюс сообщение о пропущенном индексе...
Записей каждым условием вернется по одной....... Почему так?
11 май 16, 14:02    [19158429]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
iljy
Member

Откуда:
Сообщений: 8711
sqldbmail,

я чет не пойму, вы вообще что ли думать не хотите, ждете, пока вам прожуют все? У вас в одном случае проверка на равенство, в другом - на неравенство, смоделируйте поиск по дереву и все станет понятно.
11 май 16, 14:16    [19158523]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy,

Новая для меня тема, не въезжаю.
А как смоделировать поиск по дереву?
11 май 16, 14:24    [19158563]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
iljy,

т.е. получается что при поиске учитывается порядок полей в индексе, и если у меня поле OrderID первое и на него в where неравенство, то оно будет использовано, а остальные условия отброшены?
Я правильно понимаю?
А во втором случае поиск на равенство а затем на неравенство......
11 май 16, 14:26    [19158578]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
o-o
Guest
никто никуда условия не отбрасывает.
вопрос только в том, поможет вам индекс в поиске по такому-то условию или нет.
чтобы искать что-то по условию на равенство,
нужен индекс, где все упорядочено по каждому из полей, входящих в условие равенства.
неважно, в каком они порядке.

вы ищите в телефонном справочнике человека с данным фио.
вам поможет справочник, если он составлен в алфавитном порядке.
хоть сперва по фалимиям упорядочено, потом по именам,
хоть сперва по именам, потом по фамилиям.
потому что подряд пойдут все Васи Петровы: среди всех Вась Петровы пойдут рядышком
или среди всех Петровых Васи все равно группой пойдут.
встаешь на первого Васю Петрова или Петрова Васю и идешь, пока не напорешьcья на первого Васю Петрушкина
или Петрова Васяню.
а с неравенствами так не выйдет, потому что берем снова имя больше равно Васи, фамилия больше Петрова,
пусть индекс по имени, фамилии.
нашли первого Васю, ура, читаем все до конца.
ВСЕ ДО КОНЦА, НАЧИНАЯ С ВАСИ.
а попадут уже там кроме Васи Петрова еще Гены Анискины, т.е. Гена больше Васи, но Анискин меньше Петрова,
теперь все фамилии разбросаны.
придется проверять ВСЕ строки, отобранные по первому условию.

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

дальше объяснять некуда.
возьмите реальный справочник и поищите в нем по двум условиям с неравенствами
11 май 16, 15:05    [19158850]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
o-o,

примеры, мягко говоря, не из той оперы и даже балета. Вы тут объясняете нам отличие между равенством и неравенством?
и ЧЕМ БОЛЬШИМИ буквами писать ТЕМ это ДОКАЗАТЕЛЬСТВОМ не становится :)
11 май 16, 15:39    [19159085]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
o-o,
Спасибо, очень доходчиво написано.....
Собрал "итог" применительно к моему вопросу: получается что если в условиях стоит проверка на равенство, то поля могут быть установлены в любой последовательности, но если идут неравенства, то нужно ставить наиболее селективное поле, но рассчитать селективность поля для неравенства нужно несколько иначе чем сделал я, и поэтому получились такие результаты....

Я правильно все понял?
11 май 16, 15:48    [19159128]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
o-o
Guest
TaPaK
o-o,
примеры, мягко говоря, не из той оперы и даже балета. Вы тут объясняете нам отличие между равенством и неравенством?
и ЧЕМ БОЛЬШИМИ буквами писать ТЕМ это ДОКАЗАТЕЛЬСТВОМ не становится :)

дорогой критикант,
растолковали бы сами.
доходчиво и с подходящими операми/балетами/драмами и мелодрамами.
не говоря о том, что к вам я и вовсе не обращаюсь, все адресовано исключительно ТСу
11 май 16, 15:55    [19159175]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
o-o
Guest
sqldbmail,
да.
а если сомневаетесь, возьмите и создайте оба индекса, посмотрите, какой выберет сервер.
потом ненужный дропнете.
на ваших объемах это можно себе позволить
11 май 16, 15:57    [19159197]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
o-o,

Еще вопрос: правильно ли я понял (это правда ответ iliy) что если у меня есть индекс(f2,f1) и я использую выражением where f1=1 and f2>7 , то поскольку порядок использования индексов f2,f1 будет использован только f2 и f1 использован не будет.
11 май 16, 16:00    [19159212]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
sqldbmail
Member

Откуда:
Сообщений: 33
sqldbmail,

Тогда получается, в свете всего здесь мне объясненного рекомендация на WEB ресурсах:"При проектировании композитного индекса первым должно идти поле, с наибольшей селективностью" является вырванной из контекста?
11 май 16, 16:08    [19159258]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
o-o
Guest
sqldbmail
Тогда получается, в свете всего здесь мне объясненного рекомендация на WEB ресурсах:"При проектировании композитного индекса первым должно идти поле, с наибольшей селективностью" является вырванной из контекста?

получается так.
потому что это касается только предикатов с неравенствами.
11 май 16, 16:14    [19159316]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать с селективностью композитного индекса  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
sqldbmail,

лучше подходить к вопросу исходя из того какие запросу идут к серверу, а не гипотетические больше меньше и исходя из потребностей создавать индексы. И из практики, довольно часто применяем как раз таки оба варианта сразу(если на вашем примере)
11 май 16, 16:41    [19159515]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить