Индексы в MySQL на примерах — добавление, удаление

Сейчас мы поговорим и разберем на примерах индексы в MySQL. Индексы в базе данных — это специальные структуры данных, которые позволяют серверу базы данных быстро находить строки в таблице без необходимости полного сканирования всех записей. Для оптимизации запросов, чтоб не перегружать сервер и увеличить скорость загрузки страницы используют именно индексы в базе данных. Рассмотрим как их создавать, удалять и применять — это ключевой навык в оптимизации SQL запросов. Очень хорошо mysql индексы могут помочь для всех типов связей mysql или когда используются join запросы в mysql.
Данная статья подробно расскажет:

Какие виды индексов в MySQL существуют
Как создать индекс MySQL
Как использовать MySQL индексы
Как посмотреть индексы таблицы MySQL
Как удалить индекс MySQL

Что такое индекс в MySQL

MySQL индекс — это структура данных (чаще всего B-tree), которая хранит отсортированные значения определённого поля или набора полей. Проще говоря, это метка определенных записей в БД, по которой проще найти ту или иную информацию. Благодаря этому MySQL может:

Ускорять поиск записей.
Уменьшать нагрузку на сервер.
Ускорять работу с JOIN запросами.
Эффективно выполнять сортировки и фильтрации данных.

Виды индексов в MySQL

Типов индексов существует множество, использовать их нужно в зависимости от конкретной задачи. Например, индекс UNIQUE INDEX подразумевает уникальное значение, то есть, повтор данных в этой колонке невозможен. Ставится такой индекс в основном на email или телефон.

Тип индекса	Описание	Пример
PRIMARY KEY	Главный уникальный индекс	PRIMARY KEY (id)
UNIQUE INDEX	Значения должны быть уникальными	CREATE UNIQUE INDEX idx_u ON t(email);
INDEX (KEY)	Обычный индекс	CREATE INDEX idx ON t(name);
FULLTEXT	Поиск текста	CREATE FULLTEXT INDEX ft_idx ON articles(text);
SPATIAL	Для геоданных	CREATE SPATIAL INDEX sp_idx ON geo(coord);
COMPOSITE	Индекс по нескольким полям	INDEX (col1, col2)

Как создать индекс в MySQL

Создание индексов в mysql может происходить разными способами, например, при создании самой таблицы базы данных или добавлении индекса к существующей таблице. Можно создать как обычный индекс, так и уникальный и составной.

Создать индекс при создании таблицы

Этот запрос сразу создаёт таблицу и добавляет обычный индекс на поле email.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    created_at DATETIME,
    INDEX (email)
);

Добавить индекс к существующей таблице

Обычный индекс ускоряет поиск по столбцу но допускает дублирование. Рассмотрим добавление обычного индекса к существующей таблице:

ALTER TABLE users
ADD INDEX idx_email (email);

Добавить уникальный индекс MySQL

MySQL уникальный индекс создается/добавляется для быстрого поиска по столбцу и для недопущения дублирования содержимого.

ALTER TABLE users
ADD UNIQUE INDEX uniq_email (email);

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

Составные индексы применяют, если фильтрация или сортировка идет по нескольким полям. Важно понимать что порядок полей имеет значение.

ALTER TABLE orders
ADD INDEX idx_user_date (user_id, order_date);

Как использовать индекс в MySQL запросах

Использование индексов подразумевает запрос на получение данных, предварительно оптимизировав таблицу созданием/добавлением индексов. Рассмотрим обычный пример запроса на получение данных пользователя фильтруя по email адресу.

SELECT *
FROM users
WHERE email = 'test@learn-top.ru';

Если на поле email используется индекс то mysql пример его во внимание. Для того чтоб посмотреть используется ли индекс на нужном поле, нужно сделать данный запрос:

EXPLAIN SELECT * FROM users WHERE email = 'test@mail.com';

Как посмотреть индексы таблицы MySQL

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

SHOW INDEXES FROM users;

Результат запроса покажет:

Название индекса
Тип индекса
Колонку индекса
Есть ли уникальность индекса

Как удалить индекс MySQL

Удаление индексов происходит также легко и просто как и их создание или добавление. Удалим обычный индекс:

ALTER TABLE users
DROP INDEX idx_email;

Удалить уникальный индекс:

ALTER TABLE users
DROP INDEX uniq_email;

Удалить первичный ключ:

ALTER TABLE users
DROP PRIMARY KEY;