Что такое индексы и как они работают
Индекс в базе данных — это объект, который ускоряет выполнение запросов, позволяя быстро находить строки таблицы по значениям одного или нескольких столбцов. Без индекса база данных вынуждена сканировать всю таблицу последовательно, что при большом количестве строк занимает значительное время. Индекс хранит отсортированные значения выбранных столбцов и ссылки на соответствующие строки, что позволяет использовать эффективные структуры данных, такие как сбалансированные деревья (B-деревья), для поиска за логарифмическое время.
Как работают индексы
Таблицы в базе данных хранят строки в произвольном порядке. При выполнении запроса с условием, например WHERE last_name = 'Smith', без индекса СУБД просматривает каждую строку таблицы, сравнивая значение столбца с искомым. Это называется полным сканированием таблицы (table scan) и становится крайне неэффективным на больших таблицах.
Индекс создаётся на одном или нескольких столбцах. Он содержит отсортированные значения этих столбцов и указатели на соответствующие строки таблицы. Благодаря отсортированности и сбалансированной структуре (например, B-дерево) поиск нужного значения выполняется быстро — за O(log n) операций вместо O(n). После нахождения значения в индексе СУБД получает указатель на строку и извлекает её данные.
Типы индексов: кластеризованные и некластеризованные
В большинстве СУБД, включая SQL Server, индексы делятся на два основных типа:
- Кластеризованный индекс определяет физический порядок хранения строк в таблице. Таблица может иметь только один кластеризованный индекс, так как строки нельзя упорядочить двумя способами одновременно. Обычно это первичный ключ.
- Некластеризованный индекс хранит логический порядок значений, но не меняет физическое расположение строк. Таблица может иметь множество некластеризованных индексов.
Если кластеризованный индекс отсутствует, таблица хранится в структуре, называемой кучей (heap). В куче строки не упорядочены, и новые данные добавляются в конец. Это ускоряет операции вставки, но замедляет чтение, особенно если нет других индексов.
Подводные камни и компромиссы
Индексы не бесплатны. Каждый дополнительный индекс требует:
- Дополнительное дисковое пространство для хранения структуры индекса.
- Обновление индекса при каждой операции
INSERT,UPDATEилиDELETEнад таблицей, что замедляет эти операции.
Поэтому создавать индексы нужно только на столбцах, которые часто используются в условиях WHERE, JOIN, ORDER BY или GROUP BY. Избыточное количество индексов может снизить общую производительность системы из-за накладных расходов на их поддержку.
Пример создания индекса
В SQL создание индекса выглядит так:
-- Создание некластеризованного индекса на столбце last_name
CREATE INDEX idx_last_name ON employees (last_name);
-- Создание кластеризованного индекса (обычно на первичном ключе)
CREATE CLUSTERED INDEX idx_employee_id ON employees (employee_id);После создания индекса запросы, фильтрующие по last_name, будут выполняться значительно быстрее.
Коротко
- Индекс — это структура данных, ускоряющая поиск строк по значениям столбцов.
- Без индекса выполняется полное сканирование таблицы, что медленно на больших объёмах.
- Кластеризованный индекс определяет физический порядок строк, некластеризованный — только логический.
- Индексы замедляют вставку, обновление и удаление, поэтому их нужно создавать обдуманно.
- Таблица без кластеризованного индекса называется кучей и оптимизирована для быстрой вставки, но не для чтения.
