Индексы и с чем их едят.

Всем привет!
Похоже, я уткнулся в необходимость плотной работы с базами данных. А это значит, надо вплотную заняться их оптимизацией.
Первое, что приходит в голову - расставить индексы. Но даже это оказалось не так просто! Какое-то погуглив-почитав в рунетах, я понял, что четкости в понимании MySQL-ных индексов у меня совершенно не прибавляется. Даже скорее наоборот.
Потому решил обратиться к опытным товарищам с просьбой расставить над ними точки.

Итак, что я понял (не факт, что правильно):
1. Индексы ускоряют работу БД.
2. Индекс - это отдельный файл, который хранится на жестком диске и занимает место. Соответственно, не стоит их плодить слишком много.
3. Индекс - это что-то типа предметного указателя, т.е. то же самое поле, что и в таблице, только сортированное, скажем, по алфавиту.
4. Индексы могут быть разными (primary key, unique, index, fulltext), при этом какие-то из них (видимо - первые два) уникальные, то есть не могут существовать две записи с одним значением индекса, а какие-то (оставшиеся два) - не уникальные.
5. Для полнотекстовых индексов можно указать какую часть текста индексировать.
6. Индекс может быть натуральным (несущим смысловую нагрузку) и суррогатным (служащим только для идентификации записи)

Ок. Что я не понял.
1. Почему-то все пишут только про уникальные индексы, некоторые даже открыто заявляют, что неуникальные индексы не имеют смысла. Это, на мой взгляд, странно. Скажем, у нас есть таблица с топиками и таблица с комментами к этим топикам. И нам нужно выбрать все комменты к топику 2834. Комментов с topic_id=2834 будет куча (скажем 77, а всего записей, скажем - 100500), и проще их искать по индексу. Не так ли? Да. и правильно ли я понимаю, что для этого поля индекс должен быть типа INDEX?
2. Почему-то все рекомендуют создавать суррогатный PRIMARY_KEY на любых таблицах. Я не понимаю, зачем. Скажем, в той же таблице с комментами к топику. Зачем нужно делать comment_id, если с отдельным комментом никаких операций производиться не должно? Соответственно, он никогда не будет участвовать в WHERE и т.п.
3. Чем PRIMARY_KEY отличается от UNIC? Я так понял, что тем, что PRIMARY_KEY может быть только один в таблице. А в чем смысл?
4. Когда нужно использовать многостолбцовые индексы? Я так понял, что тогда, когда гм.. записей с одинаковым индексом по первому столбцу становится слишком много?
5. Где-то слышал, что лучше иметь один суррогатный PRIMARY_KEY, чем многостолбцовый индекс. Что выглядит логично - проще работать с одной колонкой, чем с кучей. Но я опять же не понимаю: допустим, нам нужно удалить все комменты юзера user_id из топика topic_id. Конечно, можно удалить комменты за номерами 4, 56, 58, 71, и это будет быстро, если comment_id - PRIMARY_KEY. Но. Вначале ведь нужно их как-то найти, эти значения!
6. Опять же, допустим, может возникнуть удалить все комментарии одного юзера из одного топика, а может - все комментарии в топике после 22-х часов. Но ведь не делать же под каждый такой вариант свой многостолбцовый индекс!

Вот, как-то пока так...

Bertolomych Использование индексов в системе хранения информационных полей позволяет осуществить ускорение поиска элементов в SQL-запросе выборочного типа на основании целевых параметров - при этом распространение возможности включения индексных значений может быть осуществлено для списка информационных полей которые будет обеспечить задание уникальной записи при условиях хранения данных в первой нормализованной форме

-----
Сделать можно все что угодно - нужно только старание, терпение и хороший поисковик
Безлимитный web-хостинг от 15 рублей за 40 МБ дискового пространства - http://ihost[dot]oks71[dot]ru/

Омг.. JustUserR, простите за переход на личности, но Вы не устаете меня поражать! Седьмой раз читаю написанные Вами 3 строчки и все равно не могу понять, как это "может быть осуществлено распространение возможности"...
Причем, каждый Ваш ответ (на мои вопросы, по крайней мере) требует столь же долгого осмысления. И почти всегда оно столь же безрезультатно.
Конечно большое Вам спасибо за оперативные ответы, но мой Вам совет - почитайте "Капитанскую дочку".

Bertolomych
1. Индексы полезны для быстродействия. особенно при поиске данных. Уникальные индексы нужно использовать когда записей очень много и нужно использовать какие-либо связки при выборке данных(к примеру дерево категорий и т.п.). Опять же если в одной таблице несколько индексных полей то уникальным обозначается то которое будет участвовать в запросах как основное. К примеру выборка всех топиков из определенного раздела у вас как основное, но вы дополнительно выбираете индексы из второй колонки основной таблицы чтобы выбрать к примеру юзеров по id. Вот в этом случае unique для user id в таблице topics не требуется.
2 и 3 пункты хорошо описаны тут http://ru[dot]wikipedia[dot]org/wiki/Primary_key
5 и 6 пункты из личного опыта могу сказать что в этом случае нужно использовать многостолбцовые индексы когда нужно делать поиск данных в n-столбцах.
Опять же индексы могут не участвовать в некоторых запросах и тогда от них толку не будет(к примеру UNION ALL).

-----
Список фильмов с описанием, блекджеком и... для Joomla? -> https://киноархив[dot]com
Демо нового движка для сайта php.su -> php[dot]su, проект на гитхабе

Viper, спасибо!
Ну, в общем, я примерно так себе все это и представлял. Просто надеялся, что может быть, где-нибудь есть какая-нибудь компактная табличка, с описанием, когда какой ключ лучше использовать. =)

Ну, или у кого-нибудь есть простенький совет типа: когда у тебя в explain количество записей по запросу, который должен вернуть одну будет больше 50 - вводи индекс/еще одно поле в индекс

(Отредактировано автором: 02 Февраля, 2011 - 22:50:37)