Что такое индексы?

Что такое индексы?

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

Основная аналогия

Без индекса:

Поиск в таблице из 1 млн строк
↓
Проверяем строку 1: нет
Проверяем строку 2: нет
...
Проверяем строку 500 000: да!

Время: 500 000 проверок (в худшем случае 1 млн)

С индексом:

В индексе: B-Tree структура
       "user_id"
        /    \
    1-500k  500k-1M
     /   \      /  \
   ...

Время: log(1 млн) ≈ 20 операций

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

1. B-Tree индекс (Самый часто используемый)

-- Создание индекса
CREATE INDEX idx_users_email ON users(email);

-- Структура B-Tree
--           (Root Node)
--          /     |     \
--       (n1)    (n2)   (n3)
--      /  \    /  \   /   \
--    (l)(l)(l)(l)(l)(l) (Leaf Nodes)

-- Характеристики:
-- - Сбалансирован
-- - Отлично для диапазонных запросов (BETWEEN, <, >)
-- - O(log n) для поиска

Пример использования:

-- Запрос с индексом (быстро)
SELECT * FROM users WHERE email = 'john@example.com';  -- O(log n)

-- Запрос с индексом (быстро, диапазон)
SELECT * FROM users WHERE age BETWEEN 25 AND 35;  -- O(log n + k)

-- Запрос без индекса (медленно)
SELECT * FROM users WHERE age / 5 = 5;  -- O(n) - full table scan

2. Hash индекс

-- Создание Hash индекса
CREATE INDEX idx_user_id_hash ON orders USING HASH (user_id);

-- Структура Hash Table
-- Hash функция: hash(key) → bucket
--
--  hash(123) → bucket 5 → [row1, row2]
--  hash(456) → bucket 12 → [row3]
--
-- Характеристики:
-- - O(1) для точного поиска
-- - Плохо для диапазонных запросов
-- - Потребляет больше памяти

Пример:

-- Отлично для точного поиска
SELECT * FROM orders WHERE user_id = 123;  -- O(1)

-- Плохо для диапазонных запросов
SELECT * FROM orders WHERE user_id BETWEEN 100 AND 200;  -- Не может использовать hash индекс

3. Bitmap индекс

-- Для колонок с низкой кардинальностью
CREATE BITMAP INDEX idx_status ON orders(status);

-- Структура:
-- status = 'pending':  [0, 1, 0, 1, 0, 1, 0, 0, 1, ...] (биты)
-- status = 'shipped':  [1, 0, 0, 0, 1, 0, 1, 1, 0, ...]
-- status = 'done':     [0, 0, 1, 0, 0, 0, 0, 0, 0, ...]

-- Характеристики:
-- - Отлично для колонок с ~100 разных значений
-- - Компактный (биты вместо целых чисел)
-- - Быстрые AND/OR операции для фильтрации

4. Full-Text индекс

-- Для полнотекстового поиска
CREATE FULLTEXT INDEX idx_article_text ON articles(title, content);

-- Поиск слов
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('python data' IN BOOLEAN MODE);

-- Характеристики:
-- - Для поиска слов в текстах
-- - Поддерживает морфологию и стоп-слова

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

-- Индекс по нескольким колонкам
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Структура: сначала по user_id, потом по order_date
-- Используется для запросов:
-- ✅ WHERE user_id = 123
-- ✅ WHERE user_id = 123 AND order_date > '2024-01-01'
-- ❌ WHERE order_date > '2024-01-01' (только user_id не в WHERE)

-- Правило: порядок колонок в индексе критичен!

Пример составного индекса:

-- Хороший индекс для этого запроса:
CREATE INDEX idx_good ON users(company_id, department_id, email);

SELECT * FROM users 
WHERE company_id = 1 AND department_id = 5 AND email LIKE 'john%';

-- Плохой индекс для этого запроса:
CREATE INDEX idx_bad ON users(email, company_id, department_id);
-- Потому что условие по email первое, а в WHERE company_id первое

6. BRIN индекс (PostgreSQL)

-- Для очень больших таблиц с сортированными данными
CREATE INDEX idx_events_time ON events USING BRIN (event_time);

-- Хранит minmax значения для блоков страниц
-- Занимает очень мало места
-- Отлично для временных рядов

Преимущества и недостатки индексов

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

# ❌ Без индекса: полное сканирование
SELECT * FROM users WHERE email = 'test@example.com';
# Time: 2500 ms (проверяет все 5 млн строк)

# ✅ С индексом: быстрый поиск
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'test@example.com';
# Time: 2 ms (log(5M) ≈ 22 операции)

Недостатки

1. Дополнительное место на диске

-- Таблица занимает 10 GB
-- 5 индексов занимают ещё 15 GB
-- Всего: 25 GB вместо 10 GB

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

-- При вставке нужно обновить все индексы
INSERT INTO users VALUES (...);
-- Таблица: O(log n) вставка
-- Индекс 1: O(log n) обновление
-- Индекс 2: O(log n) обновление
-- Индекс 3: O(log n) обновление
-- Всего: медленнее

1. Усложнение планирования запросов

Оптимизатор должен выбрать, какой индекс использовать.

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

Метод 1: Анализ медленных запросов

-- PostgreSQL: включить логирование медленных запросов
SET log_min_duration_statement = 1000;  -- Логировать запросы > 1s

-- MySQL: использовать EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- Результат:
-- | id | select_type | table  | key  | rows   | extra        |
-- | 1  | SIMPLE      | orders | NULL | 500000 | Using where  | ← Нет индекса!

-- Добавляем индекс
CREATE INDEX idx_user_id ON orders(user_id);

-- Повторная проверка:
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- | id | select_type | table  | key         | rows | extra |
-- | 1  | SIMPLE      | orders | idx_user_id | 10   | ← Используется индекс!

Метод 2: Правило ESR (Equality, Sort, Range)

-- Порядок колонок в индексе:
-- 1. Equality условия (=)
-- 2. Sort условия (ORDER BY)
-- 3. Range условия (>, <, BETWEEN)

-- Запрос:
SELECT * FROM orders 
WHERE status = 'pending'      -- Equality
  AND customer_id BETWEEN 10 AND 20  -- Range
ORDER BY order_date;           -- Sort

-- Хороший индекс:
CREATE INDEX idx_orders ON orders(status, order_date, customer_id);
-- (status - equality, order_date - sort, customer_id - range)

Метод 3: Анализ кардинальности

-- Проверь уникальность значений
SELECT COUNT(DISTINCT column) as distinct_count,
       COUNT(*) as total_count,
       ROUND(100.0 * COUNT(DISTINCT column) / COUNT(*), 2) as selectivity
FROM table_name;

-- Результат:
-- column         | distinct | total | selectivity
-- status         | 5        | 1M    | 0.0005%  ← Низко (не эффективно)
-- email          | 999K     | 1M    | 99.9%    ← Высоко (отлично)
-- user_id        | 100K     | 1M    | 10%      ← Среднее

-- Индексируй колонки с высокой кардинальностью

Примеры практических индексов

-- Таблица пользователей
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR UNIQUE,  -- PRIMARY KEY и UNIQUE создают индексы автоматически
    created_at TIMESTAMP,
    active BOOLEAN
);

-- Индекс для поиска по email
CREATE INDEX idx_email ON users(email);

-- Индекс для фильтрации и сортировки
CREATE INDEX idx_created_date ON users(created_at DESC);

-- Индекс для WHERE + ORDER BY
CREATE INDEX idx_active_created ON users(active, created_at);

-- Таблица заказов
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL,
    status VARCHAR,
    created_at TIMESTAMP
);

-- Индекс для JOIN (связь с пользователем)
CREATE INDEX idx_orders_user ON orders(user_id);

-- Составной индекс для типичного запроса
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Bitmap индекс для фильтрации по статусу
CREATE INDEX idx_orders_status ON orders(status);

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

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

• Булевы колонки (очень низкая кардинальность)
• Текстовые BLOB поля без полнотекстового поиска
• Маленькие таблицы (< 10000 строк)
• Колонки с частыми NULL значениями
• Колонки, которые часто обновляются

Мониторинг индексов

-- PostgreSQL: найти неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL: размер индексов
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) as size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size';

-- Удалить неиспользуемые индексы
DROP INDEX idx_unused ON table_name;

Итог

Индексы — это критическая оптимизация базы данных:

1. Используй B-Tree индексы по умолчанию (универсальны)
2. Анализируй медленные запросы (EXPLAIN)
3. Создавай составные индексы для частых фильтров
4. Мониторь неиспользуемые индексы и удаляй их
5. Помни про недостатки: замедление INSERT/UPDATE и использование места

Правильные индексы могут ускорить запросы в 100-1000 раз!