Каковы основные принципы создания эффективных составных индексов в базах данных?

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

1. Порядок столбцов — это главное. Индекс (col_A, col_B, col_C) может быть использован для запросов, фильтрующих по:

   • col_A
   • col_A и col_B
   • col_A, col_B и col_C

   Он не будет эффективно использоваться для запросов, фильтрующих только по col_B или col_C, так как нарушен принцип "левого префикса" (left-most prefix).

2. Селективность (Кардинальность) столбцов. На первое место в индексе ставьте столбцы с самой высокой селективностью (наибольшим количеством уникальных значений). Например, status (со значениями 'active', 'inactive') имеет низкую селективность, а user_id — высокую. Высокоселективный столбец в начале индекса позволяет базе данных на первом же шаге отбросить максимальное количество ненужных строк.

   • Хорошо: (user_id, status)
   • Плохо: (status, user_id)

3. Покрывающие индексы (Covering Indexes). Если все столбцы, запрашиваемые в SELECT и используемые в WHERE, содержатся в самом индексе, базе данных не нужно обращаться к таблице для получения данных. Это называется "покрывающий индекс" и значительно ускоряет запросы.

   -- Для этого запроса...
   SELECT last_name, age FROM users WHERE last_name = 'Smith';
   
   -- ...покрывающим будет индекс:
   CREATE INDEX idx_users_lastname_age ON users (last_name, age);

   СУБД прочитает только индекс, проигнорировав основную таблицу.

4. Баланс между скоростью чтения и записи. Индексы ускоряют операции чтения (SELECT), но замедляют операции записи (INSERT, UPDATE, DELETE), так как при каждом изменении данных в таблице СУБД должна обновить и все связанные индексы. Не создавайте лишних индексов и не включайте в них столбцы без необходимости.

5. Учитывайте операторы в запросах. Индекс будет эффективно использоваться для операторов равенства (=), диапазонных запросов (>, <, BETWEEN) и LIKE 'prefix%'. Однако, как только в условии встречается диапазонный оператор, все последующие столбцы в индексе могут использоваться менее эффективно или не использоваться вовсе.

   -- Для индекса (last_name, first_name, age)
   -- Этот запрос использует все три части индекса:
   WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;
   
   -- Этот запрос эффективно использует только (last_name, first_name):
   WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND age = 30;

Пример для PostgreSQL:

-- Создаем индекс для быстрого поиска пользователей по фамилии и городу
CREATE INDEX idx_users_lastname_city ON users (last_name, city);

-- Этот запрос будет очень быстрым:
SELECT id, email FROM users WHERE last_name = 'Ivanov' AND city = 'Moscow';