Зачем нужны индексы в реляционных базах данных?

Индекс в базе данных — это отдельная структура данных (чаще всего B-дерево или его вариации, хеш-таблица), которая ускоряет операции поиска (WHERE), сортировки (ORDER BY) и соединения (JOIN) за счет уменьшения количества страниц данных, которые СУБД должна прочитать с диска. Без индекса СУБД выполняет полное сканирование таблицы (full table scan), что для больших таблиц крайне неэффективно.

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

Как это работает (на примере B-дерева):

1. Индекс хранит значения ключевого столбца (или нескольких) в отсортированном виде вместе с указателями на соответствующие строки в основной таблице.
2. При выполнении запроса с условием по индексированному столбцу СУБД сначала ищет значение в индексе (быстро, т.к. дерево сбалансировано).
3. Найдя значение в индексе, СУБД использует сохраненный указатель для прямой выборки строки из таблицы.

Пример на SQL:

-- Создание таблицы
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    country_code CHAR(2),
    registration_date DATE
);

-- Создание индекса на столбце 'email' (ускорит поиск по email)
CREATE INDEX idx_users_email ON users(email);

-- Создание составного индекса (ускорит запросы с фильтрацией по country и сортировкой по дате)
CREATE INDEX idx_users_country_date ON users(country_code, registration_date);

-- Запрос, который будет эффективно использовать индекс idx_users_email
SELECT * FROM users WHERE email = 'alice@example.com';

-- Запрос, который может использовать составной индекс idx_users_country_date
SELECT id, email FROM users 
WHERE country_code = 'US' 
ORDER BY registration_date DESC;

Типы индексов и их применение:

• B-Tree (B+Tree): Наиболее распространенный. Подходит для операторов =, >, <, BETWEEN, LIKE 'prefix%'. Поддерживает сортировку.
• Hash: Только для точного совпадения (=). Не поддерживает диапазонные запросы или сортировку. Обычно быстрее B-Tree для точечного поиска.
• GiST, SP-GiST, GIN, BRIN: Специализированные индексы в PostgreSQL для полнотекстового поиска, геоданных, массивов и блоковых диапазонов.

Компромиссы (цена индексов):

• Замедление операций записи (INSERT, UPDATE, DELETE): При каждой модификации данных СУБД должна обновлять все affected индексы.
• Дополнительное дисковое пространство: Индексы занимают место, иногда сравнимое с размером самой таблицы.
• Выборка индекса (Index-Only Scan): Если индекс содержит все столбцы, запрашиваемые в SELECT (является "покрывающим" — covering index), СУБД может выполнить запрос, читая только индекс, что значительно ускоряет работу.

Правило: Создавайте индексы осознанно, основываясь на частых шаблонах запросов (WHERE, JOIN, ORDER BY). Профилирование запросов с помощью EXPLAIN — обязательный этап для определения необходимости и эффективности индекса.