Индексы в SQL: что это, какие бывают и как создать

Индексы в SQL хранят значения столбцов в отдельной упорядоченной структуре рядом с таблицей, и по ней база данных находит нужные строки без перебора всей таблицы. В нашем замере на SQLite поиск по индексу в таблице на 100 000 строк шёл примерно в 200 раз быстрее, а вставка тех же данных с тремя индексами стала медленнее примерно в четыре раза.

Примеры прогнаны в SQLite 3.50 из Python 3.14. Синтаксис PostgreSQL, MySQL и Microsoft SQL Server там, где он отличается, приведён по документации и помечен названием СУБД.

Что такое индекс в базе данных

Как предметный указатель в конце книги, индекс в базе данных содержит значения столбца в отсортированном виде, а рядом с каждым значением указатель на строку таблицы. Поэтому индекс требует дополнительного места: по сути он хранит вторую копию данных столбца. В SQLite каждый индекс лежит в отдельном B-дереве, и ключ индекса состоит из значений столбцов плюс rowid строки. В SQL Server обычные (rowstore) индексы устроены как B+ дерево. Дерево сбалансированное, от корневого узла до любого листа одинаковое число шагов, поэтому время поиска растёт как логарифм от числа строк.

Индекс не меняет результат запроса, только способ, которым база данных к нему приходит. Способ выбирает оптимизатор запросов: если индекс ему невыгоден, запрос выполняется без индекса.

Как создать индекс: CREATE INDEX

Команда создания индекса почти одинакова во всех системах управления базами данных:

-- общий вид, не для запуска
CREATE INDEX имя_индекса ON таблица (столбец);

Проверим на таблице заказов. Сохраните раннер как run.py, блоки SQL ниже подряд в файл orders.sql и запустите python run.py orders.sql. Блоки с пометкой другой СУБД в первой строке (-- PostgreSQL ..., -- SQL Server ...) в файл не копируйте: SQLite их не выполнит. Раннер выполняет запросы по очереди и печатает результат, а из плана запроса только описание шага; нужен Python 3.12+ (autocommit).

import sqlite3
import sys

conn = sqlite3.connect(":memory:", autocommit=True)
buf = ""
for line in open(sys.argv[1], encoding="utf-8"):
    buf += line
    if sqlite3.complete_statement(buf):
        try:
            for row in conn.execute(buf):
                if buf.lstrip().startswith("EXPLAIN"):
                    row = row[-1:]  # из плана печатаем только описание шага
                print("|".join(str(v) for v in row))
        except sqlite3.Error as e:
            print(f"{type(e).__name__}: {e}")
        buf = ""

Таблицу на 100 000 записей заполняет рекурсивный запрос: 5000 пользователей, у каждого по 20 заказов.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  total INTEGER NOT NULL,
  created_at TEXT NOT NULL
);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 100000)
INSERT INTO orders (user_id, status, total, created_at)
SELECT i % 5000 + 1,
       CASE WHEN i % 50 = 0 THEN 'new' ELSE 'done' END,
       i % 997 * 10,
       date('2026-01-01', '+' || (i % 270) || ' days')
FROM n;
SELECT COUNT(*), COUNT(DISTINCT user_id) FROM orders;
PRAGMA page_count;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;
CREATE INDEX ix_user ON orders (user_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;
PRAGMA page_count;

100000|5000
735
SCAN orders
SEARCH orders USING INDEX ix_user (user_id=?)
996

До создания индекса план показывает SCAN: база данных читает все 100 000 строк таблицы и проверяет условие у каждой. После CREATE INDEX тот же запрос идёт через SEARCH и читает только подходящие строки. Цену индекса видно по PRAGMA page_count: объём базы данных вырос с 735 до 996 страниц по 4 КБ, индекс по одному целочисленному столбцу занял около 1 МБ.

Насколько индекс ускоряет поиск, мы замерили отдельным скриптом на Python: та же таблица, 1000 запросов SELECT total FROM orders WHERE user_id = ? подряд, значение передаётся параметром (склейка строк дала бы SQL-инъекцию). Условия: база данных в памяти, Windows 10, Intel Core i9-9900KF. Без индекса 1000 запросов заняли 2,84 с, с индексом 0,013 с. Вставка 100 000 строк в таблицу без вторичных индексов заняла 0,05 с, с тремя индексами 0,22 с. Три запуска подряд дали почти те же цифры. На другой СУБД, на диске и на других данных числа будут другими.

Как проверить, что индекс работает: EXPLAIN

Использует ли запрос индекс, показывает план выполнения запроса. Команда у каждой СУБД своя:

EXPLAIN ANALYZE в PostgreSQL на самом деле выполняет запрос и показывает реальное время. Для UPDATE и DELETE его запускают внутри BEGIN … ROLLBACK, иначе изменения данных останутся в таблице.

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

Уникальный индекс

CREATE UNIQUE INDEX запрещает повторяющиеся значения в столбце: уникальный индекс гарантирует, что двух одинаковых email в таблице не будет. Уникальный индекс нельзя создать на столбце, где повторы уже есть, а ограничение UNIQUE в описании таблицы создаёт такой индекс автоматически:

CREATE UNIQUE INDEX ux_user ON orders (user_id);
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE,
  name TEXT
);
INSERT INTO users (email, name) VALUES ('Anna@Example.com', 'Анна');
INSERT INTO users (email, name) VALUES ('Anna@Example.com', 'Аня');
INSERT INTO users (email, name) VALUES (NULL, 'Олег'), (NULL, 'Иван');
SELECT COUNT(*) FROM users;
PRAGMA index_list(users);

IntegrityError: UNIQUE constraint failed: orders.user_id
IntegrityError: UNIQUE constraint failed: users.email
3
0|sqlite_autoindex_users_1|1|u|0

Первая команда упала: у одного пользователя 20 заказов. Вторая вставка Анны упала на уникальном индексе, а две строки с NULL прошли. PRAGMA index_list показывает индекс sqlite_autoindex_users_1, который база данных создала сама: 1 значит уникальный, u значит «создан ограничением UNIQUE». В SQL Server дубликат даёт ошибку 2601 при уникальном индексе и 2627 при ограничении PRIMARY KEY или UNIQUE, их ловят в TRY ... CATCH (разбор в статье про обработку ошибок в SQL Server).

С NULL СУБД ведут себя по-разному, и на этом ловятся при переносе схемы:

В SQL Server несколько NULL в уникальном столбце получают фильтрованным уникальным индексом с условием WHERE email IS NOT NULL: уникальность тогда проверяется только у отобранных строк.

Составной индекс и порядок столбцов

Составной индекс строится по нескольким столбцам таблицы, и порядок столбцов в нём решает, каким запросам он поможет. Строки индекса отсортированы сначала по первому столбцу, внутри него по второму, как в телефонном справочнике по фамилии, а потом по имени:

CREATE INDEX ix_status_date ON orders (status, created_at);
EXPLAIN QUERY PLAN SELECT id FROM orders
WHERE status = 'new' AND created_at >= '2026-09-01';
EXPLAIN QUERY PLAN SELECT id FROM orders
WHERE created_at >= '2026-09-01';

SEARCH orders USING COVERING INDEX ix_status_date (status=? AND created_at>?)
SCAN orders USING COVERING INDEX ix_status_date

Первый запрос нашёл строки поиском по обоим столбцам. Во втором условия на первый столбец нет, и в плане SCAN: перебирается весь индекс целиком. MySQL без первого столбца составной индекс для поиска обычно не использует (правило левого префикса); исключение с 8.0.13: Skip Scan, если запрос читает только столбцы индекса. PostgreSQL сокращает просматриваемую часть B-tree условиями на ведущие столбцы, а с версии 18 умеет skip scan и иногда использует индекс без условия на первый столбец, обычно если различных значений в нём мало. Отсюда порядок: сначала столбцы, которые сравниваются через =, последним столбец с диапазоном (>, <, BETWEEN). Столбцы индекса правее диапазона для поиска уже не работают.

В MySQL в индексе до 16 столбцов, в PostgreSQL и SQL Server до 32.

Покрывающий индекс

В плане выше стоит COVERING INDEX. Покрывающим называют индекс, в котором уже есть все столбцы запроса, и база данных не обращается к самой таблице. id здесь входит в ключ индекса, поэтому SELECT id покрыт. Если запросить столбец, которого в индексе нет, появится второй поиск, теперь в таблице:

EXPLAIN QUERY PLAN SELECT total FROM orders
WHERE status = 'new' AND created_at >= '2026-09-01';

SEARCH orders USING INDEX ix_status_date (status=? AND created_at>?)

В PostgreSQL и SQL Server лишние столбцы кладут в индекс без участия в ключе, через INCLUDE:

-- PostgreSQL и SQL Server
CREATE INDEX ix_status_date ON orders (status, created_at) INCLUDE (total);

Частичный индекс

Частичный индекс (в SQL Server он называется фильтрованным) хранит только строки, подходящие под условие WHERE. Новых заказов у нас 2000 из 100 000, и индексировать остальные 98 000 записей ради поиска новых незачем:

DROP INDEX ix_status_date;
CREATE INDEX ix_new ON orders (created_at) WHERE status = 'new';
EXPLAIN QUERY PLAN SELECT id FROM orders
WHERE status = 'new' AND created_at >= '2026-09-01';
EXPLAIN QUERY PLAN SELECT id FROM orders
WHERE status = 'done' AND created_at >= '2026-09-01';

SEARCH orders USING COVERING INDEX ix_new (created_at>?)
SCAN orders

Запрос с status = 'done' индексом не воспользовался: этих строк в индексе нет. Частичные индексы есть в SQLite, PostgreSQL и SQL Server; в синтаксисе CREATE INDEX у MySQL предложения WHERE нет.

Индекс по выражению

Если в условии над столбцом стоит функция, обычный индекс по этому столбцу не помогает. Например, поиск почты без учёта регистра:

EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'anna@example.com';
CREATE INDEX ix_email_lower ON users (lower(email));
EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'anna@example.com';

SCAN users USING COVERING INDEX sqlite_autoindex_users_1
SEARCH users USING COVERING INDEX ix_email_lower (<expr>=?)

До индекса по lower(email) запрос перебирал весь уникальный индекс и вычислял функцию для каждого значения. В PostgreSQL выражение берут в двойные скобки: CREATE INDEX ON users ((lower(email)));. В MySQL функциональные индексы есть с версии 8.0.13, тоже в двойных скобках.

Хэш, полнотекстовые и другие типы индексов

Тип по умолчанию почти везде B-tree: он подходит и для равенства, и для диапазонов. Остальные типы индексов зависят от СУБД:

• PostgreSQL: Hash (только сравнение на равенство), GIN (массивы, полнотекстовый поиск), GiST, SP-GiST, BRIN;
• MySQL: FULLTEXT для столбцов CHAR, VARCHAR и TEXT в InnoDB и MyISAM; обычный индекс в InnoDB бывает только BTREE;
• SQL Server: кроме кластеризованных и некластеризованных, колоночные индексы columnstore для хранилищ данных и аналитических запросов по большим таблицам.

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

Это деление есть в SQL Server и в MySQL с InnoDB. Кластеризованный индекс хранит в листьях сами строки таблицы, отсортированные по ключу, поэтому на таблице он может быть только один. Таблица без кластеризованного индекса в SQL Server называется кучей (heap). Некластеризованный индекс хранится отдельно от строк: в нём ключ плюс указатель на строку. У кучи это указатель на саму строку, у таблицы с кластеризованным индексом им служит ключ кластеризованного индекса.

-- SQL Server (T-SQL)
CREATE TABLE dbo.orders (
  id INT IDENTITY(1, 1) NOT NULL,
  user_id INT NOT NULL,
  total INT NOT NULL,
  CONSTRAINT PK_orders PRIMARY KEY CLUSTERED (id)
);
CREATE NONCLUSTERED INDEX IX_orders_user ON dbo.orders (user_id)
  INCLUDE (total);

Кластеризованный индекс по id здесь создаёт первичный ключ (он стал бы кластеризованным и без слова CLUSTERED), поэтому второй кластеризованный на этой таблице уже не создать. Чтобы кластеризовать таблицу по другому столбцу, ключ объявляют как PRIMARY KEY NONCLUSTERED. Ограничение UNIQUE и CREATE INDEX без слова CLUSTERED дают некластеризованный индекс.

В MySQL InnoDB кластеризованным всегда становится первичный ключ, а без него первый уникальный индекс с NOT NULL столбцами. Каждый вторичный индекс хранит копию первичного ключа, поэтому короткий ключ вроде целочисленного id выгоднее длинного строкового: длинный раздувает все индексы таблицы.

Как посмотреть и удалить индекс

PRAGMA index_list(orders);

0|ix_new|0|c|1
1|ix_user|0|c|0

Последний столбец 1 у ix_new означает частичный индекс, c значит «создан командой CREATE INDEX». Индексы первичного ключа и UNIQUE в SQL Server через DROP INDEX не удаляются: для них нужен ALTER TABLE ... DROP CONSTRAINT.

Когда индексы вредят

База данных обновляет индексы вместе с таблицей, поэтому INSERT, UPDATE и DELETE с ними работают дольше, а каждый индекс занимает место на диске. Отсюда правила:

1. Создавайте индекс под конкретный медленный запрос и сравнивайте план выполнения до и после. Индекс «на всякий случай» на каждый столбец замедлит запись данных, а нужен ли он хоть одному запросу, неизвестно.
2. Удаляйте индексы, которые запросы не используют: их всё равно приходится обновлять при каждой записи.
3. На небольших таблицах и в запросах, которые возвращают большую часть строк, индекс мало помогает: последовательное чтение всей таблицы бывает дешевле.
4. Не оборачивайте индексированный столбец в функцию в WHERE. Условие substr(created_at, 1, 7) = '2026-09' перепишите диапазоном created_at >= '2026-09-01' AND created_at < '2026-10-01'.
5. LIKE '%текст' с % в начале в SQLite и PostgreSQL обычным индексом не ускоряется. В SQLite даже LIKE 'abc%' идёт через индекс, только если столбец объявлен с COLLATE NOCASE или включён PRAGMA case_sensitive_like.

В PostgreSQL обычный CREATE INDEX блокирует запись в таблицу до конца построения индекса. На рабочей базе данных используют CREATE INDEX CONCURRENTLY: он строится дольше и не работает внутри транзакции, зато не останавливает вставки.

Частые вопросы

Сколько индексов можно создать на одну таблицу?

В SQL Server один кластеризованный и до 999 некластеризованных. В MySQL на таблице InnoDB до 64 вторичных индексов плюс кластеризованный первичный ключ.

Создаётся ли индекс для PRIMARY KEY автоматически?

Да. PostgreSQL создаёт уникальный B-tree индекс для первичного ключа и для ограничения UNIQUE, SQL Server делает первичный ключ кластеризованным индексом, если другого кластеризованного нет, в InnoDB первичный ключ и есть кластеризованный индекс. Второй такой же индекс вручную только продублирует первый.

Нужен ли индекс на внешний ключ?

Обычно нужен. Ни PostgreSQL, ни SQLite сами его не создают, а без индекса удаление строки в родительской таблице или изменение её ключа требует просмотра дочерней таблицы в поисках строк со старым значением. Пример такого индекса с планом до и после есть в разборе нормализации.

Почему оптимизатор не использует мой индекс?

Частые причины: в условии запроса нет первого столбца составного индекса, над столбцом стоит функция, LIKE начинается с %, запрос выбирает большую часть таблицы. В PostgreSQL бывают и устаревшие статистики: запустите ANALYZE и посмотрите план заново.