Введение
Без индекса поиск конкретной строки требует просмотра всей таблицы целиком (full table scan) — индекс даёт СУБД упорядоченную структуру данных, позволяющую находить нужные строки значительно быстрее, аналогично тому, как алфавитный указатель в конце книги позволяет найти упоминание термина без прочтения всей книги страница за страницей. В статье разберём наиболее распространённый тип индекса — B-tree, и условия, при которых индекс реально помогает, а не просто существует без пользы.
Концепция
B-tree (балансированное дерево) — структура данных, хранящая значения индексируемого столбца в отсортированном порядке, организованная так, что поиск, вставка и удаление выполняются за логарифмическое время относительно размера таблицы (значительно быстрее линейного полного просмотра для больших таблиц). Индекс ускоряет операции поиска по равенству (WHERE column = value), диапазону (WHERE column > value, BETWEEN), и сортировку (ORDER BY column), если порядок индекса совпадает с требуемым порядком сортировки — но индекс не бесплатен: каждая модификация данных (INSERT/UPDATE/DELETE) требует также обновления всех индексов, затронутых изменением, что замедляет операции записи пропорционально количеству существующих индексов на таблице.
Пример кода: Индексы
-- Без индекса — поиск требует полного просмотра таблицы (для большой таблицы это медленно)
SELECT * FROM customers WHERE email = 'ivan@example.com';
-- Создание индекса на столбце, часто используемом в условиях поиска
CREATE INDEX idx_customers_email ON customers(email);
-- Тот же запрос теперь использует индекс — СУБД находит строку через B-tree,
-- не просматривая всю таблицу целиком (проверяется через EXPLAIN, статья 416)
SELECT * FROM customers WHERE email = 'ivan@example.com';
-- Индекс помогает и для диапазонных условий, и для сортировки
CREATE INDEX idx_orders_date ON orders(order_date);
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'; -- диапазон
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10; -- сортировка + ограничение
-- Создание индекса при наличии большого количества существующих данных может занять время —
-- CONCURRENTLY (PostgreSQL) позволяет не блокировать таблицу на запись во время создания
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);
Пояснения к коду
Сравнение поведения одного и того же запроса до и после создания индекса idx_customers_email иллюстрирует основную ценность индексов — сам SQL-запрос не меняется ни на символ, но способ его физического выполнения СУБД может радикально измениться (с полного просмотра на эффективный поиск по B-tree), что демонстрирует декларативную природу SQL: разработчик описывает желаемый результат, а СУБД (с помощью индексов, предоставленных разработчиком) самостоятельно выбирает наиболее эффективный способ его получения. CREATE INDEX CONCURRENTLY — практическая деталь для production-систем: обычное создание индекса блокирует таблицу на запись на всё время построения индекса (что для большой, активно используемой таблицы может означать заметный простой), тогда как конкурентное создание (доступное в PostgreSQL) строит индекс без такой блокировки, за счёт более длительного и немного более ресурсоёмкого процесса построения.
Подводные камни
- Создание индекса на каждом столбце «на всякий случай» без анализа реальных паттернов запросов — каждый дополнительный индекс замедляет каждую операцию записи (
INSERT/UPDATE/DELETE) в эту таблицу, поскольку СУБД должна обновить не только сами данные, но и все существующие индексы, затронутые изменением; индексы должны создаваться осознанно, на основе реальных, часто выполняемых запросов, а не превентивно на всех столбцах подряд. - Предположение, что наличие индекса автоматически гарантирует его использование оптимизатором запросов — СУБД может решить не использовать существующий индекс, если, по её оценке (статья 417), полный просмотр таблицы окажется эффективнее для конкретного запроса и конкретного распределения данных (что особенно вероятно для небольших таблиц, или когда условие отбирает большую часть строк таблицы — в этом случае построчный доступ через индекс может быть медленнее, чем последовательное чтение всей таблицы); реальное использование индекса конкретным запросом стоит проверять через
EXPLAIN(статья 416), а не предполагать его автоматическое применение просто на основании существования индекса. - Создание индекса на столбце с очень низкой кардинальностью (малым количеством уникальных значений относительно общего числа строк, например булевый столбец
is_activeс всего двумя возможными значениями) — для таких столбцов индекс часто малополезен, поскольку B-tree эффективен именно для быстрого сужения большого набора строк до малого, тогда как индекс по столбцу с малым количеством уникальных значений всё равно отбирает значительную долю всех строк таблицы для каждого конкретного значения, не давая существенного выигрыша по сравнению с полным просмотром. - Игнорирование того, что построение индекса на уже большой, существующей таблице без
CONCURRENTLY/аналогичного механизма создаёт заметную блокировку production-таблицы — для систем, где простой даже на несколько минут (время построения индекса на большой таблице) недопустим, обычноеCREATE INDEXбез специального, неблокирующего варианта может стать серьёзной операционной проблемой, и эта деталь должна быть продумана и протестирована заранее, прежде чем выполнять подобную операцию на реальной production-БД.