Зачем нужна индексация?

Зачем нужна индексация

Индексирование (indexing) — это создание специальной структуры данных, которая ускоряет поиск информации в таблице БД. Это один из наиболее критичных аспектов оптимизации производительности баз данных.

Аналогия из реальной жизни

Представь книгу на 1000 страниц:

Без индекса (полный скан):

• Нужно прочитать все 1000 страниц, чтобы найти информацию о "гравитации"
• Время: часы

С индексом (Index в конце книги):

• Открываешь индекс → находишь "гравитация: стр. 345, 567, 890"
• Идёшь сразу на эти страницы
• Время: секунды

В БД принцип точно такой же.

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

Без индекса:

SELECT * FROM users WHERE email = 'john@example.com'

БД должна:
1. Открыть первую строку таблицы
2. Проверить: email == 'john@example.com'?
3. Если нет → следующая строка
4. Повторить для всех 1 000 000 строк

Время: O(n) — линейный, медленно

С индексом на колонке email:

SELECT * FROM users WHERE email = 'john@example.com'

БД:
1. Открывает индекс (структура как binary search tree)
2. Двоичный поиск: за ~20 шагов находит место
3. Прямой доступ к строке

Время: O(log n) — логарифмический, очень быстро
Ускорение: 50x-1000x

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

1. Primary Key Index (первичный ключ)

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

-- Очень быстрый поиск по ID
SELECT * FROM users WHERE id = 42  -- мгновенно

Характеристики:

• Обязателен для каждой таблицы
• Уникален (нет двух одинаковых значений)
• Не может быть NULL
• Ускоряет все операции на первичном ключе

2. Unique Index

CREATE UNIQUE INDEX idx_email ON users(email)

-- Гарантирует, что email уникален
-- И ускоряет поиск по email
SELECT * FROM users WHERE email = 'john@example.com'  -- быстро

Применение:

• Email (уникален для каждого пользователя)
• Username
• Social security number

3. Regular Index (обычный индекс)

CREATE INDEX idx_status ON orders(status)

-- Ускоряет поиск, но не гарантирует уникальность
SELECT * FROM orders WHERE status = 'completed'  -- быстро

Применение:

• Поля, часто используемые в WHERE
• Поля, часто используемые в сортировке

4. Composite Index (составной индекс)

CREATE INDEX idx_user_status ON orders(user_id, status)

-- Индекс на двух полях сразу
SELECT * FROM orders 
WHERE user_id = 42 AND status = 'completed'  -- очень быстро

Правило: Порядок колонок в индексе важен!

-- Хороший индекс для этого запроса:
CREATE INDEX idx_user_status ON orders(user_id, status)

-- Этот запрос будет быстрым:
SELECT * FROM orders WHERE user_id = 42  -- использует индекс
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed'  -- ещё быстрее

-- Этот запрос НЕ будет использовать индекс:
SELECT * FROM orders WHERE status = 'completed'  -- полный скан

5. Full-Text Index (полнотекстовый индекс)

CREATE FULLTEXT INDEX idx_content ON articles(content)

-- Для поиска по тексту
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('machine learning' IN BOOLEAN MODE)  -- быстрый поиск

Применение:

• Поиск в тексте статей
• Поиск в описаниях товаров

6. Spatial Index (пространственный индекс)

CREATE SPATIAL INDEX idx_location ON places(location)  -- для координат

-- Поиск мест рядом с точкой
SELECT * FROM places 
WHERE ST_Distance(location, POINT(55.75, 37.62)) < 1000  -- быстро

Применение:

• Maps приложения (поиск рядом)
• GIS системы

Практический пример: до и после индексации

Таблица users: 1 000 000 строк

Запрос без индекса:

SELECT * FROM users WHERE email = 'john@example.com'

Время выполнения: 2-3 секунды (полный скан таблицы)

Добавляем индекс:

CREATE INDEX idx_email ON users(email)

Запрос с индексом:

SELECT * FROM users WHERE email = 'john@example.com'

Время выполнения: 5-10 миллисекунд (ускорение в 200-600 раз!)

Где использовать индексы

Индексируй колонку, если она используется в:

1. WHERE clause (условия фильтрации)

SELECT * FROM users WHERE status = 'active'  -- индекс на status
SELECT * FROM products WHERE category_id = 5  -- индекс на category_id

2. JOIN conditions (объединения)

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id  -- индекс на users.id и orders.user_id
WHERE u.status = 'active'

3. ORDER BY (сортировка)

SELECT * FROM products 
ORDER BY created_at DESC  -- индекс на created_at ускорит сортировку

4. GROUP BY (группировка)

SELECT category, COUNT(*) FROM products
GROUP BY category  -- индекс на category ускорит группировку

5. DISTINCT

SELECT DISTINCT country FROM users  -- индекс на country поможет

Антипаттерны: когда НЕ использовать индексы

1. Не индексируй часто изменяемые колонки

-- Плохо: статус меняется часто
CREATE INDEX idx_order_status ON orders(status)
-- При каждом обновлении статуса нужно обновлять индекс
-- Замедляет WRITE операции

2. Не индексируй колонки с низкой кардинальностью (мало уникальных значений)

-- Плохо: только 3 значения (M/F/Other)
CREATE INDEX idx_gender ON users(gender)
-- Индекс не даст большого ускорения (всё равно нужно прочитать много строк)

3. Не создавай слишком много индексов

-- Плохо: 20 индексов на одной таблице
CREATE INDEX idx_col1 ON table(col1)
CREATE INDEX idx_col2 ON table(col2)
CREATE INDEX idx_col3 ON table(col3)
-- ...
-- Замедляет INSERT, UPDATE, DELETE
-- Занимает много памяти
-- Оптимум: 3-5 индексов на таблицу

4. Не индексируй очень длинные текстовые колонки без нужды

-- Плохо: индекс на большом тексте
CREATE INDEX idx_description ON products(description)  -- тяжело
-- Используй вместо этого полнотекстовый поиск
CREATE FULLTEXT INDEX idx_description ON products(description)  -- оптимально

Стоимость индексирования

Преимущества:

• ✅ Ускорение SELECT (READ) в 10x-1000x раз
• ✅ Ускорение фильтрации и сортировки
• ✅ Ускорение объединения таблиц

Недостатки:

• ❌ Замедление INSERT (нужно обновлять индекс)
• ❌ Замедление UPDATE (нужно обновлять индекс)
• ❌ Замедление DELETE (нужно обновлять индекс)
• ❌ Занимает дополнительное место на диске
• ❌ Занимает памят в буфере БД

Вывод: Индексы хорошо подходят для таблиц, которые часто читаются и редко изменяются.

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

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

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com'

Результат:
Seq Scan on users  (cost=0.00..35.50 rows=1)  -- полный скан (плохо)
  Filter: (email = 'john@example.com')

После создания индекса:
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com'

Результат:
Index Scan using idx_email on users  (cost=0.29..8.31 rows=1)  -- индекс (хорошо)
  Index Cond: (email = 'john@example.com')

Рекомендации для аналитика

При проектировании системы:

1. Определи, какие запросы будут самыми частыми
2. На каких колонках они ищут данные
3. Создай индексы на этих колонках
4. Проверь с помощью EXPLAIN, использует ли индексы
5. Измерь улучшение производительности

Стратегия индексирования:

• PRIMARY KEY — всегда
• FOREIGN KEY — всегда (для JOIN'ов)
• Колонки в WHERE — обязательно
• Колонки в ORDER BY — если часто
• Составные индексы для частых комбинаций

Резюме

Индексирование — это фундаментальная техника оптимизации БД, дающая 10x-1000x ускорение READ операций ценой незначительного замедления WRITE операций. Ключ успеха — правильный выбор колонок для индексирования на основе анализа реальных запросов. Переиндексирование вредно, недоиндексирование создаёт узкие места. Баланс достигается через мониторинг и анализ EXPLAIN планов.