За кулисами производительности: где и как базы данных хранят свои индексы?

Если база данных — это библиотека, то данные — это книги на полках, а индексы — это умный каталог, который позволяет найти нужную книгу за секунды, не прочёсывая каждую полку. Все знают, что индексы ускоряют поиск. Но задумывались ли вы когда-нибудь, где эти индексы физически живут и как они устроены под капотом? Понимание этого — ключ к настоящей оптимизации.

Давайте отбросим абстракции и заглянем внутрь.

Основной принцип: индекс — это отдельная структура данных

С самого начала важно понять: индекс не хранится «вместе» с данными строк таблицы. Это независимая, но тесно связанная структура. Когда вы создаёте индекс, СУБД создаёт и поддерживает специальный объект, чаще всего основанный на сбалансированных деревьях (B-tree, B+tree).

Так где же он на диске? Есть несколько вариантов:

1. В том же табличном пространстве, что и сама таблица. Это наиболее распространённый подход по умолчанию (например, в PostgreSQL, MySQL InnoDB). Данные таблицы и её индексы — это разные наборы страниц (блоков) в одних и тех же файлах. Удобно для управления, но может приводить к конкуренции ввода-вывода между операциями чтения/записи данных и индексов.
2. В отдельном табличном пространстве или на отдельном диске. Это продвинутая техника оптимизации. Например, вы можете разместить индексы на быстром SSD, а реже используемые данные — на более медленном HDD. Или распределить нагрузку ввода-вывода. Это возможно в Oracle, SQL Server, PostgreSQL.

Сердце индекса: структура B+Tree в деталях

Почти все реляционные СУБД используют B+tree. Давайте визуализируем его анатомию:

• Корневой и промежуточные узлы: Содержат навигационные ключи (например, значения индексируемого столбца) и указатели на дочерние узлы. Это «оглавление» каталога.
• Листовые узлы: Это самая важная часть. Они хранят значения индексируемых ключей и указатели (или сами данные строки, в случае кластерного индекса).

На физическом диске все эти узлы дерева хранятся в страницах или блоках — фиксированных фрагментах памяти (обычно 4–16 КБ). Страницы связаны друг с другом, образуя структуру дерева.

Особые случаи и другие структуры

1. Хеш-индексы: Используются для поиска точных совпадений (=). Это хеш-таблица, где ключ — это хеш-значение столбца, а значение — указатель на строку. Хранятся в собственном сегменте памяти/диска. Очень быстрые, но бесполезны для диапазонных запросов.
2. Битмап-индексы: Идеальны для столбцов с низкой кардинальностью (пол, статус). Это массивы битовых карт. Хранятся в сжатом формате, экономя пространство. Каждая битовая карта соответствует одному уникальному значению.
3. Инвертированные индексы: Основа полнотекстового поиска (FTS). Они хранят слова (токены) и список документов/строк, где они встречаются. Это специализированные структуры, часто управляемые отдельными движками (например, Lucene в Elasticsearch).

Почему эти практические знания важны?

1. Производительность запросов: Плохо спроектированный индекс (по столбцу с низкой кардинальностью) будет занимать дисковое пространство, не принося пользы.
2. Планирование размещения хранилища: В высоконагруженных системах вы можете разместить индексы и данные на разных физических дисках, чтобы снизить конкуренцию ввода-вывода.
3. Обслуживание: Индексы фрагментируются при частых обновлениях. Операции вроде REINDEX или REORGANIZE перестраивают их, физически переупорядочивая страницы для более быстрого последовательного чтения.
4. Мониторинг: Вы можете запрашивать системные представления (например, pg_stat_all_indexes в PG или sys.indexes в SQL Server), чтобы увидеть размер индекса на диске и статистику его использования.

Заключение: Индексы — это не магия; это конкретные, физически существующие структуры данных. Их эффективность зависит не только от логики (какие поля индексировать), но и от физического размещения и внутренней механики. Глубокое понимание этих механизмов отличает хорошего разработчика от настоящего инженера баз данных.