Проблема расстановки индексов в таблице с большим числом полей

[zerkms]

Проблема расстановки индексов в таблице с большим числом полей

Здравствуйте товарищи

Исходные данные:
Таблица, профиль пользователя. Схематично поля:
id (PK), a, b, c, d, e, f, g

Задача:
Обеспечить поиск по таблице, причём в качестве входных данных могут быть любые комбинации свойств профиля пользователя
Т.е. например:
выборка 1: a = 1, b = 2
выборка 2: b = 3, c = 4
выборка 3: e = 5, f = 6
(объединение всегда по AND)

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

Придумал вот какое решение (на оригинальность не претендую, даже считаю - что он является своеобразной "лучшей практикой", но так или иначе - ни разу не видел):
Создаём N (по числу свойств) составных индексов вида: `a` + `id`, `b` + `id`, `c` + `id`

Соответственно выборка 1 (см. выше) будет производиться за 2 запроса:
Первым мы выбираем id профилей, у которых a = 1 (используется индекс `a` + `id`)
Вторым - выбираем id профилей, у которых b = 2 и id IN (см. п.1)

Альтернативные варианты? Мнения?

 

[fixxxer]

ну тут еще надо смотреть если в in будет небольшое количество то возможно начиная со 2го запроса будет выгоднее просто по (id) запихав во where все что осталось.
ну и in .. limit N не забыть
а так то нормальная идея.

 

[Gas]

zerkms
вроде выглядит логично.
интересно, а как при таких индексах будет отличаться скорость запросов вида:
select t1.* from t as t1 join t as t2 on t1.id=t2.id where t1.a=1 and t2.b=2;
от варианта с 2-мя запросами.

 

[zerkms]

Gas
ну если составного индекса a+b не будет, то, думаю, разница будет соотносится с разницей запросов без джоина

 

[Gas]

ну такие составные индексы (a+b) не выход, ты и сам говорил, а вот вторую часть фразы не совсем понял .
Имхо, и при запросе с джойном и с 2-мя запросами, логика работы с такими индексами будет примерно одинаковая, но в случае джойна - оптимизатор сам выберет правильный порядок таблиц (вернее условие под которое попадает меньше записей), в случае с 2-мя запросами можно не угадать и первым выполнить запрос, строк по которому больше чем по другому условию - лишний обмен данными и сам код формирования запросов чуть больше.
В своих выводах не уверен, за идею о таких индексах в любом случае спасибо.
Тестовой таблички нет с парами десятками-сотнями тыс. записей чтоб запустить запросы и выложить результаты?

 

[zerkms]

В своих выводах не уверен, за идею о таких индексах в любом случае спасибо.

я ожидал услышать, что это старая "фича"... неужели это не стандартная практика? не верю.

а про джоин, который ты предложил... да, сейчас подумал - может так даже будет и лучше, надо будет поэкспериментировать

 

[Mols]

А мне чё то кажется, что конструкция из первого поста не очень то. Если я всё правильно понял, то это

составных индексов вида: `a` + `id`, `b` + `id`, `c` + `id`

Сделано для того, чтобы ид взять из индексного дерева.
Но в следующем запросе по всем полученным ид - всё равно лезть на диск. Так что смысл теряется.
В общем лучше и правильнее сделать просто индексы по полям и предоставить планировщику выбрать наиболее подходящий. Используя для выборки один запрос.

 

[Gas]

Но в следующем запросе по всем полученным ид - всё равно лезть на диск. Так что смысл теряется.

почему будет лезть на диск? как раз составной индекс и стоит чтоб не лезло (если есть в памяти), range будет - это да.

 

[Mols]

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

 

[zerkms]

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

а если 10 полей и комбинации могут быть любыми? какие индексы создавать заранее?

 

[Gas]

Mols
У меня primary не выбирается, используется составной, правда данных - с гулькин нос.

я ожидал услышать, что это старая "фича"... неужели это не стандартная практика? не верю.

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

 

[Mols]

zerkms
Да никаких комбинаций. Просто индексы по отдельным полям.
Gas
Тут и очевидных "неприятностей" достаточно. Увеличение занимаемого места на диске, перестройки массы индексов при добавлении записи.
З.Ы.
Я бы вообще наверное собрал статистику сначала. Надо смотреть какая селективность по значениям полей. А сделать N индексов (да ещё и составных) врядли бы решился. Может только в каких-то очень специфических условиях.

 

[zerkms]

Я бы вообще наверное собрал статистику сначала. Надо смотреть какая селективность по значениям полей. А сделать N индексов (да ещё и составных) врядли бы решился. Может только в каких-то очень специфических условиях.

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

Увеличение занимаемого места на диске

ммм... мелочи

перестройки массы индексов при добавлении записи.

вот это да, но, как ты справедливо заметил, надо исходить из статистики - вполне вероятно что стоимость выборок будет выше, чем стоимость вставок

Да никаких комбинаций. Просто индексы по отдельным полям.

угу, естественно тоже вариант

 

[Gas]

немного подумав

zerkms
вариант с джойном наверное будет хуже чем 2 запроса, особенно в случае если под оба условия попадает много записей, по идее для него сложность O(cond1_rows*cond2_rows) - так как для каждой записи по первому условию достаются все записи по второму и ищется совпадение между их id, а для 2-х запросов O(cond1_rows+cond2_rows)+потери на обмен данными, которые можно сократить засунув всё в SP.

Mols
эти минусы очевидны, но иногда данные добавляются не часто, а размер роли не играет и главное скорость поиска.
Вообще согласен, обычно есть какие-то обязательные условия поиска и такие пляски не нужны. Но для описанного изначально случая может и иметь смысл.

В любом случае, топик заставил немного пробудить серого вещества

 

[zerkms]

а теперь, господа, усложняем задачу
пусть у нас условия объединены логическим "или"
a = 1 OR b = 2 OR c = 3

ваши варианты?

если не задумываться и задачу решать в лоб, то 3 запроса, объединённые через UNION DISTINCT

ещё?

 

[fixxxer]

кстати, mysql 4-й или 5-й?
5-й умеет объединение индексов, хотя по сути это вариация на тему union...

 

[zerkms]

fixxxer
будет - пятый. как раз вот мигрирую на него, в процессе
по поводу оптимизаций в 5 ещё не читал, а судя по всему - различия есть

-~{}~ 09.03.08 15:21:

хм... а Index Merge Optimization работает лишь для 2 полей, чтоли?