Индексы в MySQL – это важный и полезный инструмент, который позволяет оптимизировать выборку из базы данных, значительно сокращая время на получение нужных данных.
При этом заметить разницу можно на очень больших таблицах, содержащих десятки и сотни тысяч строк. Если такие таблицы не имеют индекса полей, то при запросах на выборку будут перебираться все строки подряд, пока не будет найдено искомое значение.
По сути, индекс это поле по которому оптимизирован (ускорен) поиск. Поскольку индекс занимает место на диске, то индексировать нужно только те поля, по которым происходит выборка.
Допустим нужен поиск по имени firstname
:
SELECT * FROM `MyGuests` WHERE `firstname` = "Вася";
Есть смысл добавить индекс по данному полю.
Для одной небольшой таблицы приемущество не будет очевидно < 1000 записей, но только до тех пор, пока вы не
попытаетесь
объеденит join
несколько (3-4 уже достаточно) таблиц по неиндексированным полям. Убивает сервер на раз!
Типы индексов
Кластеризованный индекс
это древовидная структура данных, при которой значения индекса хранятся вместе с данными, им соответствующими. И индексы, и данные при такой организации упорядочены. При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировкеНекластеризованный индекс
на листьях древовидной структуры хранятся указатели (или ссылки) на соответствующие строки с данными в таблице. Каждая запись во вторичном индексе содержит столбцы первичного ключа для строки, а также столбцы, указанные в некластеризованном индексе
Общие правила при создании индекса
- Каждая таблица всегда имеет только один кластеризованный индекс
- Когда вы определяете
PRIMARY KEY
для таблицы, MySQL используетPRIMARY KEY
в качестве кластеризованного индекса - Если у вас нет
PRIMARY KEY
для таблицы, MySQL будет искать первыйUNIQUE
индекс, в котором находятся все ключевые столбцы, и будет использовать этотUNIQUE
индекс в качестве кластеризованного индекса - В случае, если таблица не имеет
PRIMARY KEY
или подходящегоUNIQUE
индекса, MySQL внутренне генерирует скрытый кластерный индекс, названныйGEN_CLUST_INDEX
на синтетическом столбце, который содержит значения идентификатора строки
Ключи в системе индексов MySQL
MySQL использует значение первичного ключа для поиска строк в некластеризованном индексе.
Следовательно, предпочтительно иметь короткий первичный ключ, иначе вторичные индексы будут использовать больше места. Обычно для столбца первичного ключа используется целочисленный столбец с автоинкрементом.
PRIMARY KEY
Первичный ключ - это столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице. Первичный ключ следует следующим правилам:
- Первичный ключ должен содержать уникальные значения. Если первичный ключ состоит из нескольких столбцов, комбинация значений в этих столбцах должна быть уникальной
- Столбец первичного ключа не может иметь
NULL
значений. Любая попытка вставить или обновитьNULL
столбцы первичного ключа приведет к ошибке
Обратите внимание, что MySQL неявно добавляет NOT NULL
ограничение к столбцам первичного ключа. Таблица может иметь
один и только один первичный ключ!!!
UNIQUE key
Чтобы обеспечить значение уникальности одного или нескольких столбцов, часто используют PRIMARY KEY
.
Однако каждая
таблица может иметь только один первичный ключ.
UNIQUE index
позволяет обеспечить уникальность значений в одном или нескольких столбцах. В отличие от
PRIMARY KEY
, вы
можете создать более одного UNIQUE
индекса для каждой таблицы.
Также, в отличие от PRIMARY key
, MySQL допускает NULL
значения в UNIQUE
индексе.
FULLTEXT key
FULLTEXT
– полнотекстовый специализированный индекс, для больших объемов текстовых данных,
которые хранятся в столбцах строкового типа VARCHAR, TEXT
. Для относительно небольших типов данных
используется
обычный индекс.
Суть работы алгоритма в том, что из каждой ячейки вытаскивается текст, разбивается на слова, и для каждого слова создается отдельная таблица индекса. Появляется связь конкретного слова и ячеек, в которых это слово собственно и встречается.
Если вы задавались вопросом, как же работают поисковые системы, то в них есть подобный механизм.
Полнотекстовый индекс
может так же включать в себя одновременно несколько столбцов таблицы, в этом
случае содержимое склеивается в одно единое, и создается полнотекстовый индекс, по алгоритму, который уже был описан
выше. Кроме всего этого поддерживает морфологию языков, стоп слова, а так же перестановки в словосочетаниях. Так же
есть минимальное значение длины слова, которое равно 4-м символам, т.е. если длина слова меньше 4-х, то слово не
попадает в индекс.
SPATIAL key
Пространственный индекс
- это индекс, установленный в области пространственного типа данных. В MySQL существует 4
типа
пространственных данных, а именно: геометрия, точка, линия и многоугольник.
MySQL использует ключевое слово SPATIAL
для расширения, поэтому столбцы,
которые можно использовать для создания пространственных индексов, должны быть объявлены как NOT NULL
.
Аналогично, до
MySQL 5.7.xx пространственные индексы могут создаваться только в таблицах, механизм хранения которых - MyISAM.
Индекс префиксов
Если столбцы являются строковыми, при создании индекса, он будет занимать много места на диске и потенциально
замедлять
INSERT
операции.
Чтобы решить эту проблему, MySQL позволяет создавать индекс для ведущей части значений столбцов строковых столбцов.
Невидимый индекс (только для MySQL 8.0)
По умолчанию индексы видимые (VISIBLE)
. Невидимые индексы (INVISIBLE)
позволяют помечать
индексы как недоступные для
оптимизатора запросов.
MySQL поддерживает невидимые индексы в актуальном состоянии при изменении данных в столбцах, связанных с индексами.
Чтобы сделать индекс невидимым, с помощью ключевых слов VISIBLE
и INVISIBLE
, вы должны явно
заявить о видимости индекса
во время создания или с помощью ALTER TABLE
команды.
Например, вы можете сделать индекс невидимым, чтобы увидеть, влияет ли он на производительность, и снова пометить индекс как видимый, если это так.
PRIMARY key оr UNIQUE index
нельзя сделать невидимыми.
Составной индекс
Составной индекс - это индекс по нескольким столбцам. MySQL позволяет создавать составной индекс, состоящий до 16 столбцов.
Оптимизатор запросов использует составные индексы для запросов, которые проверяют все столбцы в индексе, или запросов, которые проверяют первые столбцы, первые два столбца и т.д.
Если вы укажете столбцы в правильном порядке в определении индекса, единый составной индекс может ускорить выполнение таких запросов к одной и той же таблице.