MySql. Что такое индексы? Как создать или удалить индекс?


В работе проекта очень важным фактором является скорость его работы.
Одним из методов повышение скорости работы проекта является: ускорение времени выполнение запроса БД MySql, за счет сокращения времени отклика при выполнении запроса в базу данных.
Сегодня мы познакомимся с таким инструментом как "Индексы".
Использование индексов, позволит сократить время отклика от базы данных MySql, что дает нам огромные преимущества при выборке данных из крупной БД и окажет большую пользу при использовании в крупных, высоконагруженных проектах.
Давайте разберем, что такое индексы, какие индексы бывают и какие преимущества можно из них извлечь.

MySql Index - это важный и полезный инструмент, который позволяет оптимизировать выборку из базы данных. Использование индексов значительно сокращает время выполнения запроса, что позволяет быстрее получать данные.
Ощутимую разницу при использовании MySql индексов можно увидеть при работе с таблицами крупных размеров.
Индекс представляет из себя структуру, в которой хранятся значения одного или нескольких столбца[ов] таблицы и ссылок на строки, где эти значения расположены. Для хранения индексов чаще всего используются "бинарные деревья".

Рассмотрим пример работы индексов:
Ощутить преимущества работы индексов можно при выполнении SELECT запроса с условием WHERE.
У нас есть таблица, которая хранит информацию о пользователях.
В таблице есть колонки:
  • id - autoincrement
  • firstname - Имя
  • lastname - Фамилия
  • age - Возраст

Первое поле id является авто инкрементирующим и всегда уникальным полем, по этому ему по умолчанию присваиваем индекс.
Теперь по данной таблице сделаем выборку всех пользователей, у которых возраст составляет 20 лет.
Данный запрос будет выглядеть следующим образом:
SELECT * FROM users WHERE age = 20;

Данный запрос с виду не сложный и не должен оказывать нагрузку, однако если ваша БД размером более 1000 строк и количество таких запросов более одного, то выполнение данного запроса уже будет оказывать нагрузку на базу данных и затраченное время на выборку будет уже не тем, которое ожидалось.
А теперь выполним данный запрос на базе данных разметом в 600мб и содержащую 15 миллионов строк.
И так, запрос запущен и время выполнения данного запроса составляет - 10,562 сек.
Повторяю выполнение данного запроса еще 2 раза, для того, чтобы было понятно среднее время выполнения запроса:
Второй запуск - 10,359
Третий запуск - 10,715

Согласитесь, что ждать по 10 секунд результат запроса это довольно долго.
Теперь определим "индекс" колонке age и повторим запрос.
Время выполнения запроса составило - 0,031 сек, что значительно быстрее, чем было ранее.
Таким образом мы видим значительный прирост скорости при использовании индексов.


Типы MySql индексов:
  • PRIMARY KEY - Первичный ключ: основной ключ, который позволяет хранить свои значения как уникальные записи таблицы. Данный тип ключа должен присутствовать в таблице в единственном экземпляре. Обычно данный тип ключа определяют колонке с наименованием id.
  • UNIQUE - Уникальный ключ: Частично похож на первичный ключ, за счет того, что значения колонки таблицы должны быть уникальными (не должны повторяться) и не долны быть равны NULL.
  • Составной индекс: данный тип индекса включает в индексирование несколько полей. Данный тип индексирования обычно используется в запросах, в которых необходимо произвести выборку по нескольким полям (там где в условии WHERE встречается более одного параметра).

Пример выборки с составным индексом:
SELECT * FROM users WHERE age = 20 AND firstname = 'Alex';

Как создать индекс в MySql?
Создать индекс можно несколькими способами.
Ниже приведен пример того, как создать индекс с помощью софта для администрирования MySql баз данных(БД) - PhpMyAdmin и как создать индекс с помощью запроса в базу данных(БД).
  1. Выдача индекса с помощью PhpMyAdmin. - Перед вами структура ранее созданной базы данных с таблицей users, где содержатся поля id - первичный ключ, firstname - имя, lastname - фамилия, age - возраст.

    Можно выбрать несколько колонок таблицы и отметить их как индекс.

    Или же, на интересующей вас колонке в правой стороне навести на поле "Еще" и в выпадающем списке выбрать поле "Индекс"

  2. Назначение индекса с помощью запроса:
    ALTER TABLE `Наименование_таблицы` ADD INDEX [Наименование_индекса] (`Наименование_колонки`,`или_колонок_через_запятую`...)

    Пример. Добавление индекса к таблице users на колонку age. Наименование индекса - NEWINDEX.
    ALTER TABLE `users` ADD INDEX NEWINDEX (`age`);


Как создать уникальный индекс в MySql?
Пример создания уникального индекса с помощью PhpMyAdmin и создание уникального индекса с помощью запроса:
  1. Выдача индекса с помощью PhpMyAdmin. - Перед вами структура ранее созданной базы данных с таблицей users.

    Можно выбрать несколько необходимых вам колонок таблицы и отметить их как уникальный индекс.

    Или навести на вкладку "Еще" на против необходимого поля и в выпадающем списке выбрать пункт "Уникальный".

  2. Назначение уникального индекса с помощью запроса:
    ALTER TABLE `Наименование_таблицы` ADD UNIQUE(`Наименование_колонки`);

    Пример. Назначение уникального индекса к таблице users на колонку age.
    ALTER TABLE `users` ADD UNIQUE(`age`);


Как создать составной индекс в MySql?
Пример создания составного индекса с помощью PhpMyAdmin и создание составного индекса с помощью запроса:
  1. Выдача составного индекса с помощью PhpMyAdmin. -
    Примем за условие, что у нас должен быть запрос по 2 колонкам БД.
    Первая колонка это lastname, вторая колонка age.
    Для создания составного индекса необходимо перейти на страницу "Структура" и в таблице "Индексы" в поле "Создать индекс для", вводим необходимое нам количество столбцов (Для текущего примера необходимо создать 2 столбца) и нажимаем кнопку "Вперед".
    Должно открыться модальное окно, в котором необходимо отметить требуемые столбцы и размер.

    Назначаем составной индекс для полей "lastname" и "age". Присваиваем данному индексу название "TEST" и тип индекса "INDEX". Для примера этого достаточно, теперь нажимаем кнопку "Выполнить".

    Составной индекс создан.

  2. Назначение составного индекса с помощью запроса:
    ALTER TABLE `Наименование_таблицы` ADD INDEX `Наименование_индекса` (`Наименование_колонки_n`, `Наименование_колонки_n+1`);

    Пример. Добавление составного индекса к таблице users на колонку lastname и age. Наименование индекса - TEST.
    ALTER TABLE `users` ADD INDEX `TEST` (`lastname`, `age`);


Как удалить MySql индекс?
  1. Удаление индекса с помощью PhpMyAdmin:
    PhpMyAdmin умеет так-же легко и просто удалять индексы. Удалить индексы можно на странице "Структура таблиц".
    Под таблицей со структурой будет располагаться таблица, которая называется индексы.
    Для удаления индекса необходимо найти интересующую вас колонку и нажать на кнопку "Удалить".

  2. Удаление индекса с помощью запроса:
    ALTER TABLE ``Наименование_таблицы`` DROP INDEX ``Наименование_колонки``;

    Пример. Удаление индекса с колонки age, таблицы users.
    ALTER TABLE ``users`` DROP INDEX ``age``;


Ряд недостатков при использовании индексов:
Если же индексы дают такой прирост во время выполнения запросов, так почему бы не определять индексы на все колонки таблицы?
  1. В процессе индексирования колонок MySql таблицы создаются дополнительные таблицы, в которых хранятся отсортированные данные. И такие данные занимают некий объем физической памяти.
  2. При выполнении ряда запросов наличие индексов может играть совершенно противоположную роль и увеличивать время выполнения запроса.
  3. При наличии индекса в таблице, INSERT запрос выполняется дольше чем в таблице, которая не содержит индексов.
  4. Использование индексов может оказывать и негатив при выполнении запроса с использованием выражения LIKE в условии WHERE. Например использование выражения LIKE по индексированному полю не даст ни какого прироста в скорости выполнения.

Мы разобрали только часть основных недостатков при использовании индексов. Реальное количество может отличаться.

Новость отредактировал: Fixlix - 16-12-2018, 14:54
Причина: Дополнено пояснение про создание составного и уникального индекса

Комментариев 2

  1. Офлайн
    cotln 14 декабря 2018 21:56
    Спасибо. В статье отсутствует пояснение как назначить уникальный и составной индекс.
    1. Офлайн
      Fixlix 14 декабря 2018 22:07
      Действительно, спасибо что заметили
Информация
Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.