Индексы: как ускоряют и когда не работают

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

Индекс — отдельная отсортированная структура (обычно B-дерево), позволяющая СУБД находить строки по значению, не сканируя всю таблицу.

Аналогия с книгой

Без индекса поиск строки по условию — это полное сканирование (full scan): СУБД читает все строки подряд. Индекс — как алфавитный указатель в конце книги: вместо чтения всех страниц вы по указателю сразу прыгаете на нужную. Поэтому индекс по колонке из WHERE может ускорить запрос в сотни раз на большой таблице.

Создание индекса

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

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT,
    city  TEXT
);
INSERT INTO users (email, city) VALUES
    ('[email protected]','Москва'), ('[email protected]','Казань'), ('[email protected]','Москва');

CREATE INDEX idx_users_city ON users(city);

-- Теперь поиск по city может использовать индекс вместо полного скана
SELECT email FROM users WHERE city = 'Москва' ORDER BY email;

Вывод:

[email protected]
[email protected]

Запрос вернёт двоих москвичей. На таблице из трёх строк разницы в скорости нет, но на миллионах строк индекс по city превращает полный скан в быстрый поиск по дереву.

Чем платим за индексы

Индекс — не бесплатный. За ускорение чтения платят:

• местом на диске — индекс хранится отдельно и занимает память;
• скоростью записи — при каждом INSERT/UPDATE/DELETE индекс надо обновить;
• поэтому «навесить индексы на все колонки» — антипаттерн: лишние индексы замедляют запись и не используются.

Когда индекс НЕ работает

Очень частый вопрос. Индекс по колонке не используется, если:

• вы применяете к колонке функцию: WHERE LOWER(email) = '...' — индекс по email бесполезен (нужен индекс по выражению);
• условие с LIKE '%текст' (подстановка в начале) — дерево отсортировано слева направо, искать «с конца» нельзя;
• колонка имеет мало разных значений (например, пол: всего 2 значения) — проще прочитать всю таблицу;
• СУБД оценила, что строк подходит слишком много, и полный скан дешевле.

Вывод для собеседования: индекс ускоряет выборочный доступ по значению, но требует, чтобы условие было «дружелюбным» к индексу (без функций над колонкой, без ведущего %).

Итог

• Индекс — отсортированная структура для быстрого поиска по значению вместо полного скана.
• Плата — место на диске и замедление записи; не индексируйте всё подряд.
• Индекс не работает при функции над колонкой, LIKE '%...' и низкой селективности.