Все о MySQL. Индексы

Для того чтобы повысить скорость поиска и уменьшить время выполнения запросов, MySQL позволяет задавать индексы для определенных полей таблицы. Термин "индекс" здесь означает то же, что и в реальном мире. Аналогичный по назначению и концепции индекс вы найдете в конце данной книги - это не что иное, как предметный указатель. Индекс представляет собой упорядоченный перечень отсортированных значений полей, предназначенных для упрощения задачи поиска определенных полей при запросах.
При отсутствии индекса, MySQL необходимо просмотреть последовательно все записи таблицы для того, чтобы найти записи, соответствующие определенному запросу. На небольших таблицах это не слишком заметно, но по мере увеличения размера таблицы полный просмотр ее приводит к существенным задержкам при обработке запроса. Индекс существенно улучшает ситуацию: при наличии индекса, MySQL может вообще не производить полного просмотра таблицы, при этом достаточно просмотреть индекс и прямо перейти к определенным записям в таблице. При поиске записей, которые соответствуют определенным условиям поиска, чтение индекса обычно происходит быстрее просмотра всей таблицы, поскольку индексы по своим размерам значительно меньше размеров таблицы. Поэтому просмотр индекса производится быстрее (о том, как индексы влияют на производительность, можно подробнее узнать в главе 16, "Оптимизация производительности").
Нужно отметить, что использование индексов приводит и к определенным издержкам: для них требуется дополнительное дисковое пространство, и кроме того они влияют на скорость выполнения запросов INSERT, UPDATE и DELETE, т.к. при модификации записей в таблицах, добавлении в таблицы новых записей или удалении их из таблиц модификация должна производиться и в индексе. Но в большинстве случаев это не должно стать причиной для отказа от использования индексов: дисковое пространство дешевеет с каждым днем, а MySQL имеет множество методов оптимизации для уменьшения времени, необходимого для модификации индексов и просмотра их в целях поиска определенных значений (см. детали во врезке "Ударим хешем по производительности").
Ударим хешем по производительности
При работе с таблицами типа InnoDB, для уменьшения количества операций записи на диск, MySQL использует "разумную" буферизацию. Она заключается в создании протокола изменений в специальном буфере добавлений, с последующим обновлением всего индекса с учетом всех изменений, причем в одной операции записи (вместо многочисленных мгновенных операций записи). MySQL также пытается преобразовать индексы с использованием B-дерева (хранящегося на диске) в адаптивные хеши-рованные индексы (поиск по которым производится значительно быстрее), основанные на шаблонах выполняемых запросов. 
Индексирование рекомендуется прежде всего для полей, которые используются в предложениях WHERE, ORDER BY и GROUP BY запросов SELECT, и в полях, которые ис­пользуются для объединения таблиц.
Индексы создаются путем добавления модификатора INDEX к оператору CREATE TABLE.

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

Такое частичное индексирование вполне возможно (хотя и не всегда) и для полей CHAR и VARCHAR.
На заметку
Если в операторе CREATE INDEX или CREATE TABLE имя индекса отсутствует, MySQL автома­тически присваивает индексу имя индексируемого поля.
Если индекс вам больше не нужен, его можно удалить с помощью команды DROP INDEX:
DROP INDEX имя-индекса ON имя-таблицы;
Далее следует пример удаления индекса username, созданного в предыдущем примере.

Кроме индексов "обычного" типа, MySQL поддерживает еще два важных типа индексов: UNIQUE и FULLTEXT, речь о которых пойдет ниже.