Полный цикл в digital

Индексы в MySQL – это важный и полезный инструмент, который позволяет оптимизировать выборку из базы данных, значительно сокращая время на получение нужных данных.

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

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

Допустим нужен поиск по имени firstname:

SELECT * FROM `MyGuests` WHERE `firstname` = "Вася";

Есть смысл добавить индекс по данному полю.

Для одной небольшой таблицы приемущество не будет очевидно < 1000 записей, но только до тех пор, пока вы не попытаетесь объеденит join несколько (3-4 уже достаточно) таблиц по неиндексированным полям. Убивает сервер на раз!

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

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

Общие правила при создании индекса

  • Каждая таблица всегда имеет только один кластеризованный индекс
  • Когда вы определяете PRIMARY KEY для таблицы, MySQL использует PRIMARY KEY в качестве кластеризованного индекса
  • Если у вас нет PRIMARY KEY для таблицы, MySQL будет искать первый UNIQUE индекс, в котором находятся все ключевые столбцы, и будет использовать этот UNIQUE индекс в качестве кластеризованного индекса
  • В случае, если таблица не имеет PRIMARY KEY или подходящего UNIQUE индекса, MySQL внутренне генерирует скрытый кластерный индекс, названный GEN_CLUST_INDEX на синтетическом столбце, который содержит значения идентификатора строки

Ключи в системе индексов MySQL

MySQL использует значение первичного ключа для поиска строк в некластеризованном индексе.

Следовательно, предпочтительно иметь короткий первичный ключ, иначе вторичные индексы будут использовать больше места. Обычно для столбца первичного ключа используется целочисленный столбец с автоинкрементом.

PRIMARY KEY

Первичный ключ - это столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице. Первичный ключ следует следующим правилам:

  1. Первичный ключ должен содержать уникальные значения. Если первичный ключ состоит из нескольких столбцов, комбинация значений в этих столбцах должна быть уникальной
  2. Столбец первичного ключа не может иметь 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 столбцов.

Оптимизатор запросов использует составные индексы для запросов, которые проверяют все столбцы в индексе, или запросов, которые проверяют первые столбцы, первые два столбца и т.д.

Если вы укажете столбцы в правильном порядке в определении индекса, единый составной индекс может ускорить выполнение таких запросов к одной и той же таблице.

Заполните форму уже сегодня!
Для начала сотрудничества необходимо заполнить заявку или заказать обратный звонок. В ответ получите коммерческое предложение, которое будет содержать индивидуальную стратегию с учетом требований и поставленных задач
Работаем по будням с 9:00 до 18:00. Заявки, отправленные в выходные, обрабатываем в первый рабочий день до 12:00.
Спасибо, ваш запрос принят и будет обработан!
Эйч Маркетинг