Использование индексов в СУБД MySQL

Узнать стоимость VPS Вы можете у нас.

В СУБД MySQL индексы являются основным средством ускорения доступа к содержимому таблиц; особенное это касается запросов, включающих объединение нескольких таблиц.

СУБД MySQL использует индексы в нескольких аспектах:

  • индексы используются для поиска строк, соответствующих условиям предложения WHERE, или строк, имеющих соответствия в других таблицах при выполнении объединения.
  • для ускорения поиска максимального или минимального значения индексируемого столбца при работе с функциями MIN() или MAX().
  • для ускорения сортировки с помощью конструкции ORDER BY и GROUP BY.
  • иногда СУБД может избежать чтения из файла данных вообще, при выборке только индексированного столбца.

Существуют достаточно гибкие способы создания индексов:

  • таблицу можно индексировать по одному или нескольким столбцам
  • индексу может быть задан режим содержания повторяющихся или уникальных значений
  • для оптимизации различных запросов одна таблица может иметь более одного индекса, опирающегося на различные столбцы (комбинации столбцов)
  • любой строковый тип (кроме ENUM и SET) можно индексировать по первым n символам слева (нельзя создавать индексы по столбцам типа BLOB и TEXT, пока не задана длина префикса).
  • для таблиц типа InnoDB индекс может строиться на внешних ключах, то есть значения в индексе должны соответствовать значениям, представленным в другой таблице.

При использовании индексов существуют ограничения, но по мере развития СУБД они сужаются. Свои особенности накладывают выбранные механизмы хранения, например, если применить индекс FULLTEXT, необходимо использовать только таблицы типа MyISAM, а если требуется установить внешние ключи, то необходимо работать с таблицами типа InnoDB.

Для создания индекса index_name по таблице table_name необходимо выполнить запрос:

CREATE [ UNIQUE | FULLTEXT | SPATIAL ]
INDEX index_name [ USING = index_type ] ON table_name (index_columns)

Ключевые слова UNIQUE, FULLTEXT и SPATIAL могут добавляться для отображения специфический свойств индекса. Если ни одно из них не задано, создается не уникальный индекс. Оператор CREATE INDEX не может быть использован для создания индекса PRIMARY KEY, для этого необходимо использовать оператор ALTER TABLE.

ALTER TABLE table_name ADD PRIMARY KEY (index_columns)
ALTER TABLE table_name ADD INDEX [index_name] (index_columns)
ALTER TABLE table_name ADD FULLTEXT [KEY | INDEX] [index_name] (index_columns)
ALTER TABLE table_name ADD UNIQUE (index_name) (index_columns)
ALTER TABLE table_name ADD SPATIAL [KEY | INDEX] [index_name] (index_columns)

Если указано несколько столбцов, то из имена следует разделять запятыми. Если имя индекса index_name не определено, оно создается автоматически на основе первого индексируемого столбца. Кроме того, оператор ALTER TABLE позволяет удалять индексы:

ALTER TABLE table_name DROP [KEY | INDEX] index_name
ALTER TABLE table_name DROP PRIMARY KEY

Индексы можно удалять с помощью оператора DROP INDEX:

DROP INDEX index_name ON table_name
DROP INDEX `PRIMARY` ON table_name

Для определения алгоритма индексирования можно использовать оператор USING. TYPE является синонимом USING, для таблиц типа MyISAM и InnoDB это может быть BTREE. Для таблиц типа MEMORY это может быть HASH или BTREE.

У составных индексов существует особенность использования, которая определяется тем, что при наличии такого индекса, например, для столбцов (col1, col2, col3), любой крайний левый префикс может быть использован для поиска. То есть нет необходимости дополнительно создавать индексы (col1) и (col1, col2).

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

Список использованных источников:

  1. Поль Дюбуа, MySQL, 3-е издание.
  2. Официальный сайт MySQL

При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на handyhost.ru в качестве источника.

Принимаем
Все способы