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

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

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

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

При использовании индексов существуют ограничения, но по мере развития СУБД они сужаются. Свои особенности накладывают выбранные механизмы хранения, например, если применить индекс 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 в качестве источника.

Принимаем к оплате
Все способы оплаты
Консультант
Я могу Вам помочь?
x
Задать вопрос