Индексация — мощный инструмент для оптимизации SQL-запросов. Не все понимают её суть и применение. Этот текст разъясняет основы индексации.
Аналогия с книжным индексом
Представьте работу с огромной базой данных, например, таблицу product содержащую 12 миллионов товаров. Запрос SELECT COUNT(*) FROM product WHERE category = ‘electronics’ (подсчёт товаров категории «электроника») потребует сканирования всех 12 миллионов строк, что может занять 4 секунды.
Индексация работает аналогично книжному указателю: вместо перелистывания всех страниц, вы обращаетесь к содержанию и находите нужную главу. Индекс позволяет базе данных быстро находить данные по запросу.
Создание и использование индекса
Создадим индекс для столбца category таблицы product:
CREATE INDEX product_category_index ON product (category);Создание индекса занимает время (например, 4 минуты для 12 миллионов строк). Однако, после создания, запрос SELECT COUNT(*) FROM product WHERE category = ‘electronics’ выполнится значительно быстрее — примерно за 400 миллисекунд. Даже более сложные запросы, например:
SELECT COUNT(*) FROM product WHERE category = 'electronics' AND product_subcategory = 'headphones';выполнятся эффективнее (около 600 миллисекунд). Порядок условий в запросе не влияет на использование индекса: база данных сначала обратится к индексированному столбцу category.
Внутренняя работа и оптимизатор запросов
База данных анализирует все возможные пути выполнения запроса, выбирая оптимальный. Каждый путь называется «планом выполнения запроса», а компонент, выбирающий оптимальный план, — «оптимизатором запросов».
Индексы по нескольким столбцам
Индекс можно создавать по нескольким столбцам:
CREATE INDEX product_category_subcategory_index ON product (category, product_subcategory);Здесь важен порядок столбцов: сначала сортировка по category, затем по product_subcategory. Такой индекс ещё больше ускорит выполнение запросов.
Типы индексов и их применение
Существуют два основных типа индексов: кластеризованный и не-кластеризованный.
Кластеризованный индекс: Создаётся автоматически по первичному ключу. Структурирует данные в таблице по порядку первичного ключа. Например, для таблицы product с первичным ключом product_id создаётся кластеризованный индекс по product_id. Запросы по product_id выполняются очень быстро благодаря двоичному поиску. Двоичный поиск — эффективный алгоритм, делящий данные пополам на каждом шаге, сокращая количество необходимых поисков. Для миллиона записей требуется максимум 20 поисков, для 12 миллионов — 24.
Не-кластеризованный индекс: Создаётся явно для не-ключевых столбцов. Хранит значения индексируемого столбца и адреса соответствующих строк в основной таблице. Запросы выполняются медленнее, чем с кластеризованным индексом, из-за дополнительного шага поиска адреса строки.
Пример создания не-кластеризованного индекса:
CREATE INDEX product_category_index ON product (category);Запрос с использованием этого индекса:
SELECT name, category, price FROM product WHERE category = 'electronics';База данных:
- Находит запись в индексе product_category_index.
- Находит адрес соответствующей строки в основной таблице.
- Извлекает name, price.
Удаление индекса
Индекс можно удалить командой:
DROP INDEX product_category_index;Когда индексы не нужны
Небольшие таблицы, таблицы с частыми операциями UPDATE и INSERT, столбцы с большим количеством значений NULL, столбцы, которые часто обновляются.
Индексы — мощный инструмент оптимизации SQL-запросов, но их применение требует понимания принципов работы и особенностей использования. Правильное использование индексов значительно ускорит работу вашей базы данных.