Составной индекс и селективность его столбцов?

Question

[Mimuss]

Составной индекс b типа сравнивается кортежами, то бишь порядок определения индекса имеет значение - (a,b) и (b,a) не равнозначны. Допустим, имеется два столбца в некоторой абстрактной таблице: x - внешний ключ, y - datetime, соответственно x имеет более низкую селективность, чем y
Знаю и понимаю, что лучше определять b индекс для столбцов с высокой селективностью, то есть порядок будет таков - (y,x).
Я прав?

Answer 1

[Melkij]

Составной индекс b типа сравнивается кортежами, то бишь порядок определения индекса имеет значение

Зависит от алгоритма. Справедливо для btree, может быть полностью лишено смысла для других типов. Например, если СУБД будет строить ключ для hash-индекса от конкатенации полей индекса - (a,b) ничем не будет отличаться от (b,a).

лучше определять b индекс для столбцов с высокой селективностью

Не имеет важного значения.
Значение имеет под какие запросы индексы делаем. btree (a,b) не может нормально обслуживать where b = value and a > value, для этого запроса куда осмысленнее (b,a)
Если у вас запрос where a = const and b = const, то btree индексы (a,b) и (b,a) будут равнозначны.

Если у вас индекс с крайне селективным timestamp на первом месте - то что вы с ним делать-то будете? Строгое сравнение таймштампа случай крайне редкий, а на любой запрос диапазона эффективность последующих полей падает катастрофично и большинство СУБД их вовсе не используют. В основном используют только если получается взять индекс как покрывающий.

имеется два столбца в некоторой абстрактной таблице: x - внешний ключ, y - datetime

Варианты если вы попробуете сделать только btree(y,x) индекс:
- СУБД вовсе откажется делать FK т.к. нет подходящего индекса для проверки FK
- СУБД неявно этот индекс создаст при добавлении FK
- СУБД позволит существовать FK без индекса, но соответственно fullscan всей таблицы на каждую проверку консистентности по этому FK (возможно по отдельному сканированию на каждую строку, которая вызвала проверку целостности ключа)

Comments

[Mimuss]

вообще мой запрос будет вида where sysdate >= y and x = "какой то айдишник"
Только почему такой запрос эффективнее для (x,y), а не наоборот?

[Melkij]

Потому что по (x,y) вы сможете легко спуститься на ветвь нужного x, читать всё до конца этого x или до нахождения нужного y, получая только нужные указатели строк.
А для (y,x) вы будете читать весь индекс от начала до y, отбрасывая указатели на неподходящие строки x. Если СУБД будет вообще использовать от индекса что-то кроме условия по y или же по индексу будет получать все подходящие y и проверять второе условие после вычитки таблицы

Answer 2

[Запорожченко Олег]

В общем случае - да, такой индекс будет быстрее работать и быстрее перестраиваться.

Comments

[Mimuss]

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

[Запорожченко Олег]

Я если честно не понял зачем он добавил первичный ключ в составной индекс и как это относится к физическому хранению данных. Есть паритцирование (в постгресе, не уверен насчет мускуля) которое позволяет данные физически группировать вместе по условию. Но тут явно о чем-то другом речь.