В каких случаях используют индексы

Индексы в базах данных — это структуры, ускоряющие поиск и извлечение данных. Они применяются для повышения производительности запросов, особенно в больших таблицах, но требуют компромисса между скоростью чтения и затратами на запись. Основные сценарии использования индексов включают частые операции чтения, запросы с условиями, объединения таблиц, обеспечение уникальности, поддержку внешних ключей и полнотекстовый поиск.

Как работают индексы

Индекс — это отдельная структура, которая хранит отсортированные значения выбранных столбцов и ссылки на соответствующие строки таблицы. Благодаря этому база данных может находить нужные строки без полного сканирования таблицы. Например, для запроса SELECT * FROM users WHERE email = 'test@example.com' без индекса СУБД просмотрит все строки, а с индексом по email — сразу перейдёт к нужной записи.

Индексы бывают разных типов: B-tree (стандартный для большинства случаев), hash, полнотекстовые, GiST и другие. Выбор типа зависит от структуры данных и характера запросов.

Когда использовать индексы

• Частые операции чтения. Если таблица активно используется для выборок с фильтрацией или сортировкой, индексы по соответствующим столбцам значительно ускоряют выполнение запросов.
• Запросы с условиями. Операторы WHERE, ORDER BY, GROUP BY выигрывают от индексов, так как они позволяют быстро отфильтровать и отсортировать данные.
• Объединения таблиц (JOIN). Индексы на столбцах, участвующих в соединении, ускоряют поиск соответствующих записей в каждой таблице.
• Уникальность данных. Уникальные индексы гарантируют отсутствие дубликатов в столбце (например, для email или номера паспорта).
• Внешние ключи. Индексы на внешних ключах ускоряют проверку ссылочной целостности и операции, связанные с каскадными изменениями.
• Полнотекстовый поиск. Специализированные полнотекстовые индексы позволяют эффективно искать по словам и фразам в больших текстовых полях.

Пример

Рассмотрим таблицу products с миллионами записей. Запрос SELECT * FROM products WHERE category_id = 5 ORDER BY price будет выполняться долго без индексов. Создадим составной индекс:

CREATE INDEX idx_category_price ON products (category_id, price);

Теперь база данных сможет быстро отфильтровать товары по категории и отсортировать их по цене, используя один индекс.

Подводные камни

• Замедление операций записи. Каждая вставка, обновление или удаление требует обновления всех индексов таблицы. Чем больше индексов, тем медленнее записи.
• Дополнительное место на диске. Индексы хранятся отдельно и занимают значительный объём, особенно для больших таблиц.
• Неоптимальные индексы. Создание индекса на столбце с низкой селективностью (например, на поле gender с двумя значениями) может не дать выигрыша и лишь добавить накладные расходы.
• Пересечение индексов. Иногда несколько индексов могут дублировать друг друга, что приводит к лишним затратам. Важно анализировать планы запросов и удалять неиспользуемые индексы.

Коротко

• Индексы ускоряют чтение, но замедляют запись и требуют места на диске.
• Используйте индексы для столбцов, часто встречающихся в WHERE, JOIN, ORDER BY и GROUP BY.
• Уникальные индексы обеспечивают целостность данных, а полнотекстовые — быстрый поиск по тексту.
• Перед созданием индекса оцените соотношение операций чтения и записи, а также селективность столбца.