Какие данные можно индексировать в базах данных

Индексирование в базах данных: стратегии и практики

Индексирование — это фундаментальная техника оптимизации производительности баз данных, представляющая собой отдельную структуру данных (обычно B-дерево, реже хеш-таблицу или битовую карту), которая ускоряет операции поиска, сортировки (ORDER BY) и соединения (JOIN), но замедляет операции вставки, обновления и удаления данных (DML-операции), так как индекс требуется поддерживать в актуальном состоянии.

Какие данные (столбцы) обычно индексируют

Выбор столбцов для индексации — это компромисс между скоростью чтения и скоростью записи, основанный на анализе запросов (workload analysis). Ключевыми кандидатами являются:

1. Первичные ключи (Primary Keys) — индексируются автоматически практически во всех СУБД (создается кластеризованный или некластеризованный индекс, в зависимости от СУБД). Это обеспечивает уникальность и быстрый доступ по id.

   -- В PostgreSQL и MySQL (InnoDB) по умолчанию создается кластеризованный индекс
   CREATE TABLE users (
       id SERIAL PRIMARY KEY, -- Будет автоматически проиндексирован
       email VARCHAR(255) NOT NULL
   );
   

2. Внешние ключи (Foreign Keys) — часто индексируются вручную для ускорения операций JOIN и проверок ссылочной целостности при удалении или обновлении родительской записи.

   CREATE TABLE orders (
       order_id SERIAL PRIMARY KEY,
       user_id INT NOT NULL,
       FOREIGN KEY (user_id) REFERENCES users(id)
   );
   -- Рекомендуется создать индекс вручную, если СУБД не делает этого автоматически (как PostgreSQL)
   CREATE INDEX idx_orders_user_id ON orders(user_id);
   

3. Столбцы, часто используемые в условиях WHERE — особенно с операторами равенства (=), диапазона (>, <, BETWEEN) и членства (IN).

   -- Если частый запрос: SELECT * FROM logs WHERE created_at > '2023-01-01';
   CREATE INDEX idx_logs_created_at ON logs(created_at);
   

4. Столбцы, используемые в ORDER BY и GROUP BY — индекс позволяет избежать дорогостоящей операции сортировки во временной области (filesort).

   -- Для запроса: SELECT department, COUNT(*) FROM employees GROUP BY department;
   -- Полезен индекс:
   CREATE INDEX idx_employees_department ON employees(department);
   

5. Столбцы для соединения таблиц (JOIN) — помимо внешних ключей, это могут быть любые столбцы, по которым часто происходит связывание.

6. Столбцы с условиями уникальности (UNIQUE constraint) — как и первичный ключ, автоматически получают уникальный индекс.

Типы индексов и их применение

Выбор типа индекса зависит от типа данных, распределения значений и паттернов запросов.

• B-дерево (B-Tree) — универсальный, наиболее распространенный индекс. Подходит для точного поиска, диапазонов, сортировки по порядку. Работает с числами, датами, строками.

  CREATE INDEX idx_btree ON table_name (column1, column2);
  

• Хеш-индекс (Hash) — идеален только для операций точного равенства (=). Не поддерживает диапазоны, сортировку. Часто используется в оперативной памяти.

  -- В PostgreSQL
  CREATE INDEX idx_hash ON table_name USING HASH (column1);
  

• Составной (композитный) индекс — индекс по нескольким столбцам. Важен порядок столбцов. Он эффективен для запросов, которые используют префикс этого списка (принцип leftmost prefix).

  CREATE INDEX idx_composite ON orders (user_id, status, created_at);
  -- Этот индекс может быть использован для:
  -- WHERE user_id = 10
  -- WHERE user_id = 10 AND status = 'shipped'
  -- WHERE user_id = 10 AND status = 'shipped' AND created_at > '...'
  -- Но НЕ будет эффективно использован для: WHERE status = 'shipped'
  

• Частичный индекс (Partial / Filtered) — индекс, построенный только для подмножества строк, удовлетворяющих условию. Экономит место и повышает эффективность для частых фильтров.

  -- Индексируем только активные заказы, если запросы чаще всего работают с ними
  CREATE INDEX idx_active_orders ON orders(status) WHERE status = 'active';
  

• Индекс по выражению (Functional / Expression-based) — индекс, построенный не на значении столбца, а на результате выражения или функции.

  -- Ускорение поиска по email без учета регистра
  CREATE INDEX idx_lower_email ON users(LOWER(email));
  -- Теперь запрос сможет использовать индекс:
  -- SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
  

• Полнотекстовый индекс (Full-Text) — специализированный индекс для семантического поиска по текстовым данным (статьи, описания). Основан на инвертированных списках или специальных структурах (как tsvector в PostgreSQL).

  -- PostgreSQL
  CREATE INDEX idx_fts ON documents USING GIN (to_tsvector('english', body));
  

• Пространственный индекс (Spatial, например, GiST, SP-GiST) — для данных геолокации (точки, полигоны), ускоряет поиск "ближайших объектов" или объектов в границах.

• Индекс с включенными столбцами (Covering Index / INCLUDE) — позволяет включить в листовые узлы индекса дополнительные столбцы, не участвующие в поиске, чтобы полностью удовлетворить запрос без обращения к основной таблице (достичь index-only scan).

  -- PostgreSQL и SQL Server
  CREATE INDEX idx_covering ON orders (user_id) INCLUDE (total_amount, created_at);
  -- Запрос SELECT total_amount, created_at FROM orders WHERE user_id = 123
  -- может быть выполнен, читая только индекс.
  

Данные, которые обычно НЕ индексируют

• Столбцы с очень низкой селективностью (например, пол gender с значениями 'M'/'F'). Индекс по такому столбцу будет малоэффективен, так как возвращает большую часть таблицы. Оптимизатор СУБД, скорее всего, его проигнорирует.
• Часто изменяемые столбцы (высокая churn-rate) — если столбец постоянно обновляется, затраты на поддержку индекса могут перевесить выгоду.
• Небольшие таблицы (до нескольких тысяч строк) — последовательное сканирование (sequential scan) часто быстрее, чем поиск по индексу с дополнительным случайным доступом.
• Столбцы типа BLOB, TEXT (если не используется префикс или полнотекстовый поиск) — их размер делает стандартное индексирование нецелесообразным.

Заключение: Процесс создания индексов — итеративный и основанный на мониторинге. Необходимо постоянно:

1. Анализировать медленные запросы (через pg_stat_statements, slow query log).
2. Использовать EXPLAIN ANALYZE для изучения планов выполнения.
3. Отслеживать соотношение польза от чтения (read performance) к стоимости записи (write overhead) и занимаемому месту.
4. Удалять неиспользуемые индексы, которые лишь замедляют операции модификации данных.

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