На основе дискуссии/ислледования вырисовывается план создания оптимальных индексов для неагрегаторных запросов :)

Постановка задачи: есть фиксированный запрос, параметрами в котором являются накладываемые пользователем фильтры-условия. Например: выдача веб-данных по БД итогов биржевых торгов, где пользователь может указать дату и группу интересующих бумаг.

Нужно посторить оптимальные индексы для минимизации времени выполнения запроса.

Общее положение: Селективность условия - ответ на вопрос "Как сильно сузится облать просмотра значений после выполнения данного условия?" Сильно - селективность хорошая (оценочно: диапазон должен сузиться в десятки/сотни раз), мало - плохая.

Для каждой таблицы, участвующей в запросе:
- анализируется селективность условия для каждого поля, участвующего в связывании/фильтре. То есть селективность поля может быть "хорошей", но само условие дает "плохой" диапазон. Например: для уникального pos условие pos>NNN сузит диапазон всего в 2 раза.
- берутся поля с "хорошей" селективностью и создаются индексы по ним, со всевозможным порядком полей. Например: f1 f2 f3; f1 f3 f2; f2 f1 f3; f2 f3 f1; и т.д. Обновляется статистика на таблице.
- запускается запрос с EXPLAIN PLAN, смотрим какой индекс выбрал оптимизатор
- довешиваем к этому индексу поля с "плохой" селективностью, опять же - всевозможные престановки. Например: f2 f1 f3 f4 f5; f2 f1 f3 f5 f4;
- запускается запрос с EXPLAIN PLAN, смотрим какой индекс выбрал оптимизатор
- убиваем все ненужные индексы
- идем курить табак :)

Использование конструкций EXIS, IN или простого связывания остается для творческого выбора программиста.

Есть возражения? Комментарии?

Журавлев Денис Сугубый- довешиваем к этому индексу поля с "плохой" селективностью, Это бессмысленно, вообще если селективность f0 хорошая, то каким будет индекс просто f0 или f0, f1 или f0,f1,f4,,fn большой роли не играет, выйгрышь будет миллионные доли процента, зато с обновлением таблицы начнутся проблемы.

Если у оптимизатора есть индекс по всем полям - он выберет его. Потому как если есть два индекса f0, f1 и f3, то если оптимизатор выбрал индекс по f0, f1 , то индекс по f3 ему уже не выбрать. Поэтому надо создавать индекс f0, f1, f3.

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

Журавлев Денис СугубыйПоэтому надо создавать индекс f0, f1, f3.А вот тут вывод совсем неверный. Может пользы от такого индекса 0, и достаточно индекса по f0.

А вот это нужно проверять. И боюсь что эстимэйтед кост для случая, когда есть всего лишь индекс по f0 будет гораздо больше, чем для индекса f0, f1, f3.

Да, по одному эстимэйтед костому судить нельзя (есть еще время вставки и дисковое пространство), но в моем случае (одни селекты и достаточно места) - это хороший показатель.

ТанКомментарий

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

Цель простая - максимально уменьшить время отклика сервера. Еще раз повторю - для _конкретных_ запросов, которые зашиты в клиенте и не будут меняться (собственно и не меняются вот уже 4 года)

Оптимальный план на бумажке по _моему_ оптыту малоэффективен. Серверу как раз лучше видно, что там и как. Я пробовал и долго удивлялся получившемуся эстимейтед кост. Может у вас лучше получается - не знаю.

Valentyn Pidburtnyi СугубыйПоэтому надо создавать индекс f0, f1, f3.
При этом ты можешь сказать со 100% гарантией, что у тебя не будет запросов, где в соединении не учавствуют f0 или f1? Т.е., что в каждом запросе (настоящем и будущем) по этой таблице этот индекс будет использоваться? Может лучше перестраховаться и построить f0+f1 и отдельно f3? Или по трем полям все индексы отдельные? Вдруг статистика сильно изменится в будущем и выгоднее будет таблицы соединять по-другому, но твой 5-этажный индекс не дает оптимизатору и шанса на другой путь соединения...
Еще может случиться, что в данный момент времени статистика такая по таблице, что именно данный индекс был оптимален только в эту секунду, но в остальное время работы БД - оптимальнее другие индексы...
Мое мнение: тяжело такое важное дело подчинить строгому алгоритму.:)

Смотри постановку задачи. Есть _конкретный_ запрос, который выполняется 10000 раз в день, который не меняется _годами_. И вот его-то мне и нужно максимально ускорить. Я не беру в рассмотрение всевозможные другие запросы - это все у меня на тестовом серваке.

"Только в эту секунду" - имхо неверно. Оптимизатор строит план выполнения основываясь на статистике по таблице, текущее состояние сервера на него не влияет. Могут влиять параметры при запуске, но и они у нас не меняются годами. А если и меняются, то можно "переоптимизировать" запрос.

Журавлев Денисфлеймим дальше: "Если селективность поля f0 -- плохая, и поля f1 -- плохая, а полей f0+f1 -- хорошая, то это либо ключ (который подразумевает наличие индекса), либо ошибка проектирования (зависимость аттрибутов)".

отчего ж флеймим? Разбираемся :)

Возможно, ключ. То есть мой алгоритм неправильный? И нужно включать в рассмотрение ключи? Подумаю.

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

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

И зачем мне второй раз изобретать велосипед, когда проще научится ездить на этом? Что я и делаю - разбираюсь, как на этом конкретном велосипеде ездить быстро :)

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

Valentyn PidburtnyiИндексы ведь, которые не прошли твой тест "на полезность", ты ведь убьешь.:)
А окажется, что через месяц оптимизатор один из них и использовал бы в первую очередь. А тот индекс, который тогда победил в тестах - теперь окажется неэффективным.

состав данных прогнозируем. Да, увеличивается кол-во бумаг, меняются соотношения селективности полей. но это происходит в течении месяцев. то есть раз в полгода нужно "переоптимизировать" запрос, согласен (в общем-то я этим счас и занимаюсь)

зато эти полгода он будет "летать" :)

Valentyn PidburtnyiМое мнение: тяжело такое важное дело подчинить строгому алгоритму.:)

А как тогда жить? :) Как тогда создавать индексы? Создавать индексы на каждом поле по отдельности и верить что это будет оптимально?

Valentyn PidburtnyiИмхо, лучше без фанатизма подходить. Без составных индексов по 3-4 полям.:)
Чтобы не случилось, что через пол-года запрос не просто замедлился, а перестал двигаться вообще, причем внезапно...
А если при наличии простого индекса по каждому из полей, учавствующих в соединениях и фильтрах - медленно работает (при том, что база маленькая) - правильнее искать ошибки в проектировании оной.

А чего фанатичного в составных индексах?

Запрос не встанет - индекс есть. Причем я не сбрасываю простые индексы по каждому полю - пусть будут.

Спор переходит в разряд вкусов и личных мнений. Конструктивнее пожалуста :)

Valentyn PidburtnyiОчень и очень спорно.
Бывают случаи, когда ты лучше оптимизатора знаешь КАК быстрее связать таблицы...

Пример в студию.

Я таких случаев _не_знаю_ Создавал БД до сотни таблиц, до сотен тысяч записей.

Ну да, ну да. Так с самого начала и сделал - создал индексы на ключи. Время отклика - несколько секунд. Не устраивает. Занялся оптимизацией, создал "фанатичные" составные ключи, пробовал, куртил/вертел с месяц наверно. Добился времени отклика в десятые доли секунды. Хорошо. Решил понять, как _правильно_ создавать индексы, не тупым перебором. Обратился к уважаемой общественности...

Я так понял, что мой НИОКР никому, кроме меня, здесь не нужен. Все сами с усами ;) Что ж, я своих целей добился. Чего и вам желаю.

ЗЫ. Не волнуйтесь, я еще приду :)