SQL-Ex blog

Типы индексов в MySQL

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

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


2. Хэш-индексы используются только движком хранилища MEMORY (для тех, кто не очень знаком с этим, MySQL предлагает пользователям выбрать один из пары движков хранилища, одним из которых является InnoDB), и они известны тем, что позволяют пользователям выполнять точный поиск (любой поисковый запрос, который применяет операторы = или <=> может использовать такой вид индекса). Такие индексы обычно очень быстрые, благодаря своей структуре, но имеют ограниченные случаи использования в силу наложенных на них MySQL ограничений - пользовательские хэш-индексы поддерживаются только движком хранилища MEMORY.


3. Пространственные индексы используются для индексирования географических данных.


4. Префиксные индексы обычно покрывают префикс (часть) столбца.


5. Составные индексы, также называемые многостолбцовыми, и, как предполагает название, такие индексы обычно функционируют сразу на нескольких столбцах.


6. Покрывающие индексы иногда путают с составным индексами - в то время как составные индексы покрывают несколько столбцов, покрывающие индексы индексируют только столбцы, требуемые для выполнения запроса. Покрывающий индекс является специальным типом индекса - такие индексы используются, когда все столбцы, необходимые для выполнения запроса, включаются в индекс. При использовании покрывающего индекса MySQL может читать индекс, а не диск.


7. Кластеризованные индексы обычно сохраняют таблицы в структуре B-Tree. Все индексы, которые не являются кластеризованными, называют вторичными индексами.

При поверхностном рассмотрении широкое разнообразие индексов может несколько сбивать с толку, поэтому для лучшего понимания, что это они такое и как работают, мы должны погрузиться немного глубже.

Подробнее об индексах MySQL

Индексы обычно используются для улучшения производительности запросов SELECT при замедлении процедур UPDATE, DELETE и INSERT. Замедление вставки данных - это цена, которую приходится платить за увеличение производительности поисковых запросов. При вставке, удалении или обновлении данных в то же время должны обновляться и индексы. Если значительная часть данных находится в проиндексированной инфраструктуре базы данных, это может стать довольно большой проблемой в долгосрочной перспективе. Однако преимущества могут быстро преодолеть недостатки, если оценить рабочую нагрузку в целом. Вот простая таблица, объясняющая, когда использовать тот или иной тип индекса:






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

На какие факторы обратить внимание при индексировании

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





Список вопросов не является исчерпывающим, но он послужит хорошим стартом в направлении вашего выбора. После ответа на эти вопросы вы должны иметь достаточно хорошее понимание, в каком направлении развивается инфраструктура вашей MySQL, и как лучше всего подходить к вашим данным с помощью индексов.

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

Заключение

Индексы в MySQL имеют свои нюансы, и большинство из них связано с конкретными видами индексов. Нет бесполезных типов индексов - все индексы имеют свои варианты использования. Однако для адекватного их применения в этих вариантах использования вы должны знать, по крайней мере, некоторые из этих нюансов.