sql - какие поля индексировать?

[stopkran]

Столкнулся с проблемой в админке: некоторые операции растягивались на 20 с, из-за того (как выяснилось) что забыл одному полю добавить индекс. Вот и возник вопрос: а как не забывать такие вещи?

1) индексировать все поля таблицы подряд?

2) путём набивания шишек: проверять все запросы, и если тормозит, добавлять индексы?

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

 

[WMix]

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

 

[AnrDaemon]

Проще сказать, какие поля индексировать не надо.
1. Поля, в которых дохрена неуникальных данных. Если у тебя таблица из 10к записей, из которых 5к нулей и 5к единиц, индекс тебе ничем не поможет.
2. Поля, по которым не производится поиск.
3. Блобы.

 

[stopkran]

WMix, спасибо, я так примерно и делаю. AnrDaemon, за "много неуникальных" - отдельное спасибо, не знал (хотя вроде достаточно очевидно). То есть серебряной пули, как всегда нет...

 

[AnrDaemon]

Погугли "database index cardinality".

 

[AmdY]

http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

 

[fixxxer]

@stopkran, во-первых, как уже подсказали, смотреть slow query log и explain-ы.
Во-вторых, понимать, как работает база данных, как именно устроены индексы и как база их использует, чтобы не думать, что это какая-то там магия. На эту тему есть масса литературы, но можно представить это себе и просто на пальцах.

 

[stopkran]

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

fixxxer, ну, вот, я представил примерно себе на пальцах, как работает база данных. Потом ещё и проверил в реальной работе. Убедился: магии там нет, сплошная наука. А раз наука, то нет ли возможности предсказать, как действовать в следующий раз? Вот после описания очередной сущности мой код php генерирует примерно такую таблицу:

CREATE TABLE `units` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`topic` varchar(255) NOT NULL,
`url` varchar(100) DEFAULT NULL,
`prop1` varchar(99) NOT NULL,
`prop2` varchar(99) NOT NULL,
`prop3` varchar(255) NOT NULL,
`prop4` int(11) NOT NULL,
`order` int(4) NOT NULL DEFAULT '200',
`html1` text NOT NULL,
`html2` text NOT NULL,
`varlist_prop1` text NOT NULL,
`varlist_prop2` text NOT NULL,
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ctime` datetime NOT NULL,
`status` tinyint(3) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `topic` (`topic`),
KEY `url` (`url`),
KEY `prop4` (`prop4`),
KEY `order` (`order`)
) ENGINE=MyISAM DEFAULT CHARSET=...;

И почему там есть KEY `prop4`, но нет KEY `prop3`? Понятно, что потом, при тестировании работы, я могу обратить внимание на скорость и добавить, в частности, какие-то индексы. Но есть ведь какие-то общие принципы, чтобы можно было создавать таблицы сразу максимально "правильно".

В теме отчасти эти принципы подсказали: внешние ключи (здесь: topic), поисковые поля (видимо, у меня сюда попало prop4), ещё, как я предполагаю, поле сортировки (`order`).

Но раз я заранее не знаю, будет ли "поисковым" prop3, то может, как сказал AnrDaemon, проще действительно индексировать всё подряд (за исключением blob-text)? Ну, чтоб два раза не бегать.

 

[AnrDaemon]

Индекс сильно замедляет вставку. Тем сильнее, чем он более бессмысленен.

 

[Yoskaldyr]

@stopkran последуй совету @fixxxer-а. Потрать немного времени на чтение документации и тогда не будет подобных вопросов.
Даже если брать только innodb от mysql, то больше половины пхп разработчиков не видят разницы между 2 составными BTREE индексом по column1 и column2 и индексом по тем же колонкам но в обратном порядке (column2 и column1). Для многих магия как используются индексы при сортировке особенно если есть составные индексы. Многие не знают что в innodb первичный индекс есть всегда (даже если он не определен явно) и не знают что первичный индекс неявно копируется во все остальные индексы. И большая часть вообще не знает что такое кластерный индекс.

Поэтому чтение документации реально помогает.

 

[stopkran]

Индекс сильно замедляет вставку. Тем сильнее, чем он более бессмысленен.

Получается, что всё-таки индексировать все поля подряд (кроме blob и boolean) неправильно.

больше половины пхп разработчиков не видят разницы между 2 составными BTREE индексом по column1 и column2 и индексом по тем же колонкам но в обратном порядке (column2 и column1)

Чтобы захотеть увидеть эту разницу, пхп-разработчик (?) должен столкнуться с потребностью в составном индексе. На каком этапе? Конечно, когда я в процессе работы увижу конкретный sql-запрос с полями col1 и col2, я уже могу видеть их порядок, и думать об оптимизации, и писать волшебное слово explain, и идти читать документацию. Мой вопрос был о том, можно ли определить какие-то общие принципы индексирования на этапе проектирования БД, до начала отладки. Ответ вроде как теперь понятен.

Про составные индексы вопрос отдельный: здесь, получается, при создании таблицы надо предвидеть, как данные будут использоваться, - будет ли сортировка по трём полям, выборка по пяти критериям, сколько будет связей с другими таблицами... И без документации ясно, что слишком хитрая и навороченная хренотень не будет работать с миллионами записей, а на тысячах так и индексы не особо нужны. Я вот первый раз за 10 лет увидел, что иногда бывает, если забудешь добавить индекс, - время запроса увеличилось раз в 50.

 

[Breeze]

можно ли определить какие-то общие принципы индексирования на этапе проектирования БД

Можно, например, в случае с юзерами поле Адрес на моей практике никогда не требовало индекса в базе, а вот e-mail/он-же-логин 90% требует. Но один раз делал индексацию поля почтового индекса, т.к. по нему нужна была некоторая статистика
Все эти вещи определяются требованиями к системе на этапе проектирования и составления ТЗ, на основе общения с заказчиком.

а на тысячах так и индексы не особо нужны

документация когда-то говорила, что нужны, начиная с десятков
возможно и сейчас говорит.

 

[michelle]

На уровне кроманьонца - индексируй поля которые стоят после where на таблицах с over 100500 данными (сейчас гуру заклюют).

И перед вставкой большого числа данных - отключи индексацию, а после успешной вставки - включи.

 

[AnrDaemon]

И перед вставкой большого числа данных - отключи индексацию, а после успешной вставки - включи.

Как определить "большность" количества? Как бороться с невозможностью включить индексы из-за нарушения условий уникальности?

 

[michelle]

Как определить "большность" количества?

Как определить количество слез в стакане? Как определить количество пылинок в космосе?
Познание дзена возможно только через опыт - и да пусть топикстартер вберет в себя весь поток букв в этом топике и взвесит все и встанет на пусть истинного проектировщика баз

P.S. Отключить индексы - неотносится к primary и unique.

 

[stopkran]

...и встанет на пусть истинного проектировщика баз

Не, наверное, уже не стану. Лень. @Фанат как-то сказал, что 0.05с - нормальное время генерации страницы. Пока я его добиваюсь fas et nefas, перепроектировать свои сайтики-магазинчики нет необходимости.

@AnrDaemon, необходимость отключать индексы наступает, когда надоедает ждать. В моей практике такое было один раз, лет десять назад: я носился как кошка с салом с Большим Энциклопедическим словарём (70 тысяч строк примерно) с компьютера на компьютер. Там был fulltext индекс. И вот его приходилось отключать при создании таблицы из sql-дампа, а потом создавать индекс заново - так получалось быстрее, чем сразу создавать с индексом. А в магазинчиках... ну 1000, ну 10000 строк прайс загружается, визуально не тормозит, про индексы и не вспоминаешь.

 

[michelle]

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

Это хорошо что не тормозит. Но про индексы тоже не забывай - они вещь полезная.
Случай из практики - zabbix (2.0.X) (мониторилка серверов такая) + postgresql - тормозил ужасно на простейших страницах - добавил необходимые индексы (которые не проставили разработчики) и начало работать значительно быстрее. А там в табличке (в которую добавил индексы) было не так уж и много строк (не больше 30-ти)

 

[fixxxer]

Заббикс - это вообще недоразумение, использовать РСУБД в качестве tsdb и при этом даже не партиционировать - это, нууу, я как бы даже и не знаю

 

[michelle]

Для заббикса есть решения по партицированию в постгрес - применяюю его. Работает отлично.