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

Индексы — это дополнительные структуры данных, которые ускоряют операции поиска (SELECT, WHERE, JOIN, ORDER BY) за счет увеличения скорости доступа к строкам таблицы, ценой дополнительного расхода памяти и замедления операций записи (INSERT, UPDATE, DELETE).

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

Структура (на примере B-дерева):

• B-дерево — сбалансированное дерево, где каждый узел содержит ключи и ссылки на дочерние узлы или на данные.
• Поиск по индексированному столбцу происходит за O(log n) операций вместо полного сканирования таблицы (O(n)).

Типы индексов в PostgreSQL/MySQL:

1. B-tree: Стандартный индекс для равенства и диапазонов (=, >, <, BETWEEN, LIKE 'prefix%').
2. Hash: Только для операций равенства (=), обычно быстрее B-tree, но не поддерживает сортировку и диапазоны.
3. GiST/GIN: Для сложных типов данных (геоданные, полнотекстовый поиск, массивы).
4. Составной индекс (Composite): Индекс по нескольким столбцам. Порядок столбцов критически важен.

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

-- Создание индексов
CREATE INDEX idx_users_email ON users(email); -- B-tree по умолчанию
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC); -- Составной

-- Запрос, который использует составной индекс
SELECT * FROM orders
WHERE user_id = 123          -- Первый столбец индекса -> эффективный поиск
ORDER BY created_at DESC;    -- Индекс уже отсортирован -> не нужна сортировка

-- Запрос, который НЕ использует индекс эффективно (или вообще)
SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- Функция над столбцом
SELECT * FROM orders WHERE EXTRACT(MONTH FROM created_at) = 5; -- То же самое

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

• Столбцы в условиях WHERE, JOIN (ON), ORDER BY.
• Столбцы с высокой селективностью (много уникальных значений). Индекс по столбцу gender (2 значения) бесполезен.
• Часто используемые в запросах столбцы.

Цена индексов (trade-offs):

• Замедление DML: При каждой вставке/обновлении/удалении нужно обновлять все индексы этой таблицы.
• Расход дискового пространства.
• Выбор неправильного индекса оптимизатором. Нужно регулярно анализировать использование индексов (EXPLAIN ANALYZE, pg_stat_user_indexes в PostgreSQL).

Правило: Сначала измеряем медленные запросы, потом добавляем индекс и снова измеряем.