Как работают индексы в базах данных?

Индексы в Базах Данных

Индекс — это структура данных в БД, которая ускоряет поиск, сортировку и фильтрацию данных. Индекс работает как оглавление в книге: вместо чтения каждой страницы, смотришь в оглавление и прыгаешь на нужную страницу.

Как работает индекс

// БЕЗ индекса (полный scan таблицы)
SELECT * FROM users WHERE email = 'john@example.com';
// БД проверяет ВСЕ 1000000 строк: O(n)

// С индексом (быстрый поиск)
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
// БД ищет в индексе: O(log n)

Основные типы индексов

B-Tree индекс (самый частый)

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_created_at ON users(created_at);

-- Используется для: =, <, >, <=, >=, BETWEEN, LIKE
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE created_at > '2024-01-01';

Составной индекс (несколько колонок)

CREATE INDEX idx_user_role ON users(user_id, role);

-- Помогает для запросов
SELECT * FROM users WHERE user_id = 5 AND role = 'admin';

Уникальный индекс (PRIMARY KEY, UNIQUE)

CREATE UNIQUE INDEX idx_email ON users(email);
-- или
ALTER TABLE users ADD UNIQUE(email);

-- Гарантирует отсутствие дубликатов
INSERT INTO users (email) VALUES ('john@example.com'); -- OK
INSERT INTO users (email) VALUES ('john@example.com'); -- Ошибка!

Полнотекстовый индекс (FULLTEXT)

CREATE FULLTEXT INDEX idx_content ON posts(title, content);

-- Поиск по словам
SELECT * FROM posts WHERE MATCH(title, content) AGAINST('php backend');

Производительность

Без индекса (Full Table Scan)

$start = microtime(true);
$results = $pdo->query("SELECT * FROM users WHERE email = 'john@example.com'")->fetchAll();
$time = microtime(true) - $start;
echo $time; // 2.5 секунды для 10 млн строк

С индексом (Index Seek)

CREATE INDEX idx_email ON users(email);

$start = microtime(true);
$results = $pdo->query("SELECT * FROM users WHERE email = 'john@example.com'")->fetchAll();
$time = microtime(true) - $start;
echo $time; // 0.001 секунды! 2500x быстрее

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

Индексируй колонки:

• В WHERE условиях (SELECT * FROM users WHERE email = ...)
• В JOIN условиях (ON users.id = orders.user_id)
• В ORDER BY (ORDER BY created_at DESC)
• В GROUP BY (GROUP BY category)
• Первичные ключи (PRIMARY KEY)
• Внешние ключи (FOREIGN KEY)

-- Хорошие кандидаты на индексы
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_user ON orders(user_id);
CREATE INDEX idx_post_created ON posts(created_at);
CREATE INDEX idx_product_category ON products(category);

НЕ индексируй:

• Колонки с низкой селективностью (много одинаковых значений)
• Маленькие таблицы (< 1000 строк)
• Колонки, которые редко используются в запросах
• Очень большие TEXT/BLOB колонки

Составные индексы (важно!)

-- Порядок колонок имеет значение
CREATE INDEX idx_user_role_active ON users(user_id, role, is_active);

-- Работает хорошо
SELECT * FROM users WHERE user_id = 5;
SELECT * FROM users WHERE user_id = 5 AND role = 'admin';
SELECT * FROM users WHERE user_id = 5 AND role = 'admin' AND is_active = 1;

-- Не использует индекс!
SELECT * FROM users WHERE role = 'admin'; -- Первая колонка не в условии
SELECT * FROM users WHERE is_active = 1; -- Пропустили role

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

1. Замедление INSERT/UPDATE/DELETE

// Без индексов
INSERT INTO users (name, email) VALUES ('John', 'john@example.com'); // быстро

// С индексами
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name ON users(name);
INSERT INTO users (name, email) VALUES ('John', 'john@example.com'); // медленнее
// БД должна обновить оба индекса!

2. Избыточные индексы

-- Избыточно
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_email_name ON users(email, name); -- Первый индекс не нужен

3. Фрагментация индекса

-- Много DELETE/UPDATE может привести к фрагментации
ALTER TABLE users ENGINE=InnoDB; -- Перестроить таблицу
-- или
OPTIMIZE TABLE users; -- Оптимизировать

Анализ индексов

-- Посмотреть использует ли запрос индекс
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Результат:
-- type: const/eq_ref (хорошо, использует индекс)
-- type: range (норм)
-- type: ALL (плохо, full table scan)

-- Показать индексы таблицы
SHOW INDEXES FROM users;

-- Показать статистику индексов
SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME = 'users';

Лучшие практики

• Создавай индексы на колонках в WHERE — это главное
• Порядок в составных индексах — сначала колонки без range
• Не переиндексируй — каждый индекс замедляет INSERT/UPDATE
• Удаляй неиспользуемые индексы — они занимают место
• Проверяй EXPLAIN — смотри как работают твои запросы
• Мониторь performance — следи за slow query логом