Где хранится индекс и как к нему обращаются

Что проверяют

Понимаете ли вы, что индекс это отдельная структура на диске со своими страницами, и как СУБД по ней доходит до строки. Для Go-разработчика следом обычно спрашивают, как проверить, что запрос из сервиса действительно пошёл по индексу.

Как отвечать

Отвечайте на примере конкретной СУБД, лучше двух:

• PostgreSQL: индекс это отдельный объект со своими файлами в каталоге данных, рядом с таблицей, но не внутри неё. Файлы разбиты на страницы по 8 КБ, горячие страницы живут в shared_buffers и кэше ОС. В листьях B-дерева лежит ключ и ctid, адрес строки в таблице: номер страницы и позиция в ней;
• путь запроса: корень, внутренние страницы, лист, затем чтение строки из таблицы по ctid. Если все нужные колонки есть в индексе, а страница таблицы отмечена в visibility map как полностью видимая, будет Index Only Scan без похода в таблицу;
• MySQL InnoDB: таблица сама является кластерным индексом по первичному ключу, строки лежат в его листьях. Вторичный индекс хранит значение первичного ключа, поэтому поиск по нему это два прохода по деревьям.

Решает, брать ли индекс, планировщик: по статистике и оценке стоимости. Код на Go индексом не управляет, database/sql или pgx просто отправляют запрос. Проверка: EXPLAIN (ANALYZE, BUFFERS) на тот же запрос с теми же параметрами, а в проде pg_stat_statements и pg_stat_user_indexes.

Деталь, которая выдаёт опыт: подготовленный запрос в PostgreSQL после нескольких выполнений может перейти на общий план, который не знает конкретного значения параметра и выбирает другой способ доступа.

Где спотыкаются

Говорят, что индекс хранится «в памяти или на диске, как настроено». В PostgreSQL и InnoDB он лежит на диске, в память поднимаются только страницы, к которым обращаются.

Уверены, что планировщик обязан взять индекс. При низкой селективности или маленькой таблице последовательное чтение дешевле, и это нормально.

Проверяют план в psql с литералом, хотя сервис шлёт параметр другого типа. Приведение типа или функция над колонкой в WHERE может отключить индекс, и в проде план окажется другим.