MySQL индексы: типы, создание и оптимизация запросов

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

Индекс — это отдельная структура данных, которая ускоряет поиск по таблице. Без индекса MySQL делает полный перебор всех строк (Full Table Scan). С правильным индексом поиск за логарифмическое время — O(log n) вместо O(n).

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

Создание индексов

-- При создании таблицы
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status_created (status, created_at)
);

-- Добавить к существующей таблице
ALTER TABLE orders ADD INDEX idx_status (status);

-- Уникальный индекс
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Составной индекс
CREATE INDEX idx_user_status ON orders(user_id, status);

Правило порядка в составных индексах

Составной индекс (a, b, c) работает для запросов: WHERE a, WHERE a AND b, WHERE a AND b AND c. Но НЕ работает для: WHERE b, WHERE c, WHERE b AND c — первый столбец должен быть в условии.

-- Этот индекс (user_id, status) поможет:
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';
SELECT * FROM orders WHERE user_id = 5;

-- Но НЕ поможет:
SELECT * FROM orders WHERE status = 'pending';

Анализ запроса через EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'\G

-- Плохо: type=ALL (полный перебор)
-- Хорошо: type=ref или type=eq_ref

Просмотр индексов таблицы

SHOW INDEX FROM orders;

-- Или через information_schema:
SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

Удаление индексов

DROP INDEX idx_status ON orders;
ALTER TABLE orders DROP INDEX idx_status;