Зачем нужен индекс в SQL?

Индексы в SQL

Индекс — это структура данных, которая ускоряет поиск в таблице БД. Без индексов БД делает full table scan, что очень медленно для больших данных.

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

Без индекса:

Таблица users (1M записей):
id | name | email
1  | Alice | alice@example.com
2  | Bob | bob@example.com
3  | Charlie | charlie@example.com
...
1000000 | Zoe | zoe@example.com

Запрос: SELECT * FROM users WHERE email = 'zoe@example.com'

БД проверяет ВСЕ 1M записей по одной, пока не найдет. O(n)
Время: ~5 секунд

С индексом:

Индекс на email — это отсортированная структура (B-Tree):
email -> id
alice@example.com -> 1
bob@example.com -> 2
charlie@example.com -> 3
...
zoe@example.com -> 1000000

Запрос: SELECT * FROM users WHERE email = 'zoe@example.com'

БД ищет в индексе (binary search). O(log n)
Время: ~0.001 секунды

Ускорение в 5000 раз!

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

1. Primary Key Index

CREATE TABLE users (
    id INT PRIMARY KEY,  -- Автоматически индекс
    name VARCHAR(100)
);

-- Запрос очень быстрый
SELECT * FROM users WHERE id = 123;  -- O(log n)

2. Single Column Index

Индекс на одну колонку:

CREATE INDEX idx_users_email ON users(email);

-- Теперь этот запрос быстрый
SELECT * FROM users WHERE email = 'alice@example.com';  -- O(log n)

3. Composite Index (Multi-column)

Индекс на несколько колонок:

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Оба этих запроса быстрые
SELECT * FROM orders WHERE user_id = 5;
SELECT * FROM orders WHERE user_id = 5 AND created_at > '2024-01-01';

-- Этот медленный (обратный порядок)
SELECT * FROM orders WHERE created_at > '2024-01-01';  -- Не использует индекс!

4. UNIQUE Index

Гарантирует уникальность + ускоряет поиск:

CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Быстрый поиск + гарантия уникальности
SELECT * FROM users WHERE username = 'alice_123';

5. Full Text Index

Для поиска текста:

CREATE FULLTEXT INDEX idx_articles_content ON articles(content);

-- Быстрый полнотекстовый поиск
SELECT * FROM articles WHERE MATCH(content) AGAINST('python django');

Практический пример

Сценарий: Поиск пользователя по email в таблице 100M записей

-- БЕЗ индекса
SELECT * FROM users WHERE email = 'user@example.com';
-- Execution time: 45 секунд (full table scan)
-- Reads: 100M страниц

-- С индексом
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- Execution time: 0.005 секунд
-- Reads: ~20 страниц (логарифмический поиск)

-- Ускорение: 9000x раз!

Когда индексы помогают

1. WHERE условие

-- ✅ Быстро (есть индекс)
SELECT * FROM users WHERE user_id = 123;

-- ❌ Медленно (нет индекса)
SELECT * FROM users WHERE created_at > '2024-01-01';

2. JOIN условие

-- ✅ Быстро (индекс на user_id)
SELECT * FROM orders 
JOIN users ON orders.user_id = users.id
WHERE users.id = 5;

-- ❌ Медленно (нет индекса)
SELECT * FROM orders
JOIN custom_data ON orders.data = custom_data.data;

3. ORDER BY

-- ✅ Быстро (индекс)
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- ❌ Медленно (нет индекса, нужно sort)
SELECT * FROM users ORDER BY last_name ASC;

Когда НЕ нужны индексы

1. На маленьких таблицах

-- Таблица 10 записей
CREATE TABLE status_types (id INT, name VARCHAR(50));
-- Индекс не поможет, overhead больше пользы

2. На колонках с низкой selectivity

-- Колонка is_active с двумя значениями (true/false)
CREATE INDEX idx_users_active ON users(is_active);

-- Индекс почти бесполезен, БД всё равно прочитает много строк
SELECT * FROM users WHERE is_active = true;  -- 50% таблицы

3. На колонках, которые часто меняются

-- Если колонка часто обновляется, индекс замедляет UPDATE
CREATE INDEX idx_users_status ON users(status);

UPDATE users SET status = 'active' WHERE id = 1;
-- БД обновляет индекс, это дополнительная работа

Cost of Indexes

Индексы не бесплатны:

1. Место на диске

-- Индекс занимает память
CREATE INDEX idx_users_email ON users(email);
-- Может занять 500MB+ для большой таблицы

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

-- Без индекса
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');  -- 0.1ms

-- С 5 индексами
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');  -- 0.5ms
-- БД должна обновить 5 индексов!

3. Риск неправильного выбора

БД может не использовать индекс, если запрос написан неправильно:

-- ❌ Индекс НЕ используется (функция на колонке)
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ Индекс используется
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Как я выбираю, какие индексы создавать

1. Анализирую slow queries

# MySQL slow query log
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

2. Смотрю EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: ALL (full table scan — плохо)
-- rows: 1000000 (проверит 1M строк)

CREATE INDEX idx_users_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: ref (индекс используется — хорошо!)
-- rows: 1 (проверит только 1 строку)

3. Смотрю частоту операций

# Python код, который анализирует нагрузку
read_queries = 100000  # В день
write_queries = 10     # В день

ratio = read_queries / write_queries  # 10000:1
# Читаем в 10000 раз больше, чем пишем
# Индекс стоит того!

Пример: Оптимизация с индексами

До:

SELECT COUNT(*) FROM orders 
WHERE user_id = 5 AND status = 'completed' AND created_at > '2024-01-01';
-- Execution time: 12 seconds

SELECT * FROM orders WHERE user_id = 5 ORDER BY created_at DESC LIMIT 10;
-- Execution time: 8 seconds

После:

CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

SELECT COUNT(*) FROM orders 
WHERE user_id = 5 AND status = 'completed' AND created_at > '2024-01-01';
-- Execution time: 0.05 seconds (240x быстрее!)

SELECT * FROM orders WHERE user_id = 5 ORDER BY created_at DESC LIMIT 10;
-- Execution time: 0.02 seconds (400x быстрее!)

Best Practices

-- ✅ Правильно
CREATE INDEX idx_users_email ON users(email);  -- Часто ищут по email
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);  -- Composite

-- ❌ Неправильно
CREATE INDEX idx_users_all ON users(id, name, email, age);  -- Слишком широкий
CREATE INDEX idx_users_bool ON users(is_active);  -- Low selectivity

Резюме

Индекс нужен для:

• Ускорения SELECT в 100x-1000x раз
• Быстрого поиска (WHERE, JOIN, ORDER BY)
• Обеспечения уникальности (UNIQUE index)

Но имеет cost:

• Место на диске
• Замедление INSERT/UPDATE
• Сложность выбора

Правило: Создавай индексы на колонки, которые часто используются в WHERE и JOIN, с высокой selectivity.