Поставить индексы на каждое поле?

Question

[Vadim Stepanenko]

Привет! Есть сайт в стадии разработки, бд пока без индексов. Стоит задача - оптимизировать бд, проставив индесы. Но тк сайт еще в разработке, пока не понятно, какие индексы нужны, а какие нет. Но и выкатывать сайт вообще без индексов не очень идея. Если я на каждый столбец, который используется в WHERE добавлю одиночные индексы, не ухудшит ли это производительность?

Смогу ли я потом изменить индексы (когда записей уже будет много)? Затратно ли это по ресурсам - удаление индексов. создание новых, изменение?

Часто запрос имеет такой вид:

SELECT ... FROM table WHERE field1 = :field1 AND field2 = field2 ....

то есть многие запросы начинаются так, но потом имеют разное продолжение в WHERE Я правильно понимаю, что тогда лучше сделать один общий индекс для field1 и field2?

Answer 1

[Akina]

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

Абсолютно бессмысленное мероприятие - одна копия таблицы может использовать только один индекс. Так что надо делать композитные индексы. Немного - иначе просядут модифицирующие запросы, а композит может использоваться и по префиксу, и как покрывающий.

пока не понятно, какие индексы нужны, а какие нет.

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

Comments

[Vadim Stepanenko]

ну вот например есть таблица posts: id, title, categoryId, userId, isActive, type, rating (условно)

И есть разные запросы на выборку. WHERE у всех их содержит общую часть: WHERE isActive = :val AND rating > :val2

Дальше запросы могут быть по title, categoryId, userId, isActive:
WHERE ... AND title LIKE :val3
WHERE ... AND userId LIKE :val4
WHERE ... AND title LIKE :val3 AND userId LIKE :val4
WHERE ... AND categoryId = :val5
WHERE ... AND type = :val6 AND id > :val7

в таком случае лучше сделать 1 композитный индекс на title, categoryId, userId, isActive, isMarked, rating или один композитный на isActive и rating и одиночные на остальные столбцы?

[Алексей Горбунов]

Vadim Stepanenko, LIKE на индекс не станет.

[Akina]

Лентюй,

допустим, вы пишите код, вообще в принципе не расставляя индексы) опустим зачем и почему себе рыть могилу, но допустим)

Когда пишется код, уже можно предсказать, какие запросы будут частыми, а какие критичны. То есть уже изначально есть некоторый материал для анализа и принятия решения. И формирования начального "минимального пака".

а как вы потом из десятков тысяч разнотипных запросов будете этот самый мифический "минимальный пак" формировать?

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

если вы даже стату не сможете собрать, ибо сервак повиснет сразу и наглухо?

А если сервер наглухо повиснет, то я пойду и начищу репу тому придурку, который вместо покупки нормального сервера украл в музее старый пентиум-100.

Чтобы повесить сервер БД не расчётной задачей, а каким-то сайтом под тестовой нагрузкой - это надо слишком сильно стараться.

[Akina]

Vadim Stepanenko,

в таком случае лучше сделать 1 композитный индекс на title, categoryId, userId, isActive, isMarked, rating или один композитный на isActive и rating и одиночные на остальные столбцы?

Скорее всего ни то ни другое.

Чтобы индекс работал эффективно, у него должна быть высокая избирательность - по его выражению должно отбираться не более 5-7% записей от их общего числа в таблице, и чем меньше процент, тем лучше. Композит по isActive и rating к таковым явно не относится - разве что в качестве параметров будет передано isActive = FALSE при очень малом количестве неактивных записей и запредельное пороговое значение рейтинга. А поскольку скорее всего большинство запросов будут запрашивать активные записи (т.е. по этому полю считай избирательность будет все 99%) и более-менее вменяемый рейтинг (т.е. статистически 10-50%), то оптимизатор просто проигнорирует индекс - скан таблицы будет дешевле.

Композитный же на всё подряд тоже не имеет смысла - индекс может (хотя не факт что будет) использоваться только если первые поля в его выражении используются для отбора по равенству. Т.е. если в отборе нет условия по первому полю title, то индекс не может использоваться в принципе, даже если есть отборы по всем остальным полям. Если в отборах есть все поля, кроме, например, userId, то из индекса может использоваться только префикс (title, categoryId). И т.п.