Оптимизация запросов MySQL: два одноколоночных индекса или один двухколоночный?

Быстрый ответ

В зависимости от того, используются ли поля по отдельности или вместе, стоит рассмотреть использование составного индекса или одноколоночных индексов. Вот пример:

При наличии запросов, вовлекающих оба поля:

CREATE INDEX idx_compound ON your_table (column1, column2);

При наличии запросов к отдельным полям:

CREATE INDEX idx_col1 ON your_table (column1); 
CREATE INDEX idx_col2 ON your_table (column2);

Определите потребность в составном индексе или независимых индексах для каждой колонки, проанализировав тестовые запросы и планы выполнения.

Составной индекс – необходимый помощник для совместных запросов

Составной индекс увеличивает производительность запросов, в которых оба поля участвуют в WHERE, JOIN или ORDER BY. Таким образом, оптимизатор базы данных повышает скорость и эффективность сортировки и фильтрации данных.

Индекс должен соответствовать шаблону запроса

Если вы часто используете поля 'giver_id' и 'recipient_id' вместе, возможно, подойдёт составной индекс:

SELECT * FROM donations WHERE giver_id = 1 AND recipient_id = 2;

Однако, если запросы часто адресованы лишь к одному из полей, предпочтительнее могут быть одноколоночные индексы.

Влияние индексации на операции CRUD

Индексы повышают скорость чтения, но могут замедлять запись данных из-за необходимости обновления структуры индекса при каждом изменении.

Проверьте занимаемое пространство перед индексацией

Убедитесь, что пространство, занимаемое двухколоночным индексом, соизмеримо с его пользой для производительности.

Кардинальность – преимущество для индексации

Лучшими кандидатами на индексацию будут являться колонки с высокой кардинальностью, так как они предоставляют большее количество информации для оптимизации запросов.

Столкновение с особенностями старых версий MySQL

В старых версиях MySQL может быть полезен покрывающий индекс — он увеличивает производительность запросов, подобно тому как виниловая пластинка с классическим хитом.

Индексация: искусство баланса

Несмотря на увеличение затрат на обслуживание, индексация значительно улучшает производительность запросов.

Визуализация

Можно представлять индексацию как ящик с инструментами, где каждый индекс — это отдельное отделение.

🧰 – Ящик с инструментами (Ваша Таблица)
|🗄️ Отделение 1 (Индекс по Колонке A)|
|🗄️ Отделение 2 (Индекс по Колонке B)|

Применительно к двум одноколоночным индексам:

🔍 Отдельные Поиски:
|🗄️ Индекс Колонки A|   |🗄️ Индекс Колонки B|

- Это словно иметь два ключа для двух разных замков.

И к составному индексу:

🔎 Совмещенный Поиск:
|🗄️ Составной Индекс Колонок AB|

- Это подобно мастер-ключу, открывающему специальное отделение.

Одиночество второй колонки в составном индексе

Если запросы обычно содержат только вторую колонку, составной индекс может быть неэффективен.

Обеспечение уникальности и целостности данных при помощи индекса

Составной индекс помогает контролировать уникальность комбинации двух полей и обеспечивает целостность данных.

Подбор индекса в зависимости от объема данных и частоты запросов

Объем данных и частота запросов влияют на выбор между составными и одноколоночными индексами.

Руководство по выбору индекса на основе производительности

Основывайтесь на показателях производительности и используйте аналитические инструменты, как например EXPLAIN в MySQL, для выбора стратегии индексации.

Автоматическая индексация и её ограничения

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

Не все колонки нуждаются в индексации

Индексировать колонки с множеством NULL, с одинаковыми значениями или редко используемые в запросах не стоит.

Мультиколоночный индекс как псевдо-первичный ключ

Мультиколоночный индекс может выступать в роли псевдо-первичного ключа и обеспечивать уникальность в сводных таблицах.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 8.3.1 Как MySQL использует индексы
2. Является ли составной индекс также хорош для запросов по первому полю? – DBA Stack Exchange
3. [4. Оптимизация производительности запросов – High Performance MySQL, 2-е издание [Книга]](https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html)
4. Мультиколоночный индекс против множества индексов в MySQL 5.6
5. Использование EXPLAIN для написания лучших запросов в MySQL — SitePoint
6. MySQL: Добро пожаловать