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

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

Избыточное количество индексов может замедлить операции INSERT, UPDATE и DELETE, потому что каждый из них требует дополнительной поддержки и увеличивает нагрузку при выполнении этих операций. Для анализа производительности запросов и оценки эффективности индексов удобно использовать команду EXPLAIN. Для обнаружения неиспользуемых индексов вы можете выполнить следующий запрос:

SELECT * FROM sys.schema_unused_indexes;

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

Индексы: что, когда и зачем использовать?

Индексы способны значительно ускорить извлечение данных, однако они не всегда являются универсальным решением для увеличения производительности. Важно понимать, когда и какие колонки следует индексировать для достижения баланса между ускорением SELECT и сохранением эффективности операций изменения данных (DML).

Критерии для добавления индексов

Стратегия построения индексов должна учитывать селективность данных, типы запросов и особенности их использования. Колонки с большим количеством уникальных значений и участвующие в WHERE, JOIN, ORDER BY или GROUP BY в большинстве случаев подходят для индексации. Однако излишнее количество индексов часто бывает бесполезным: учитывайте, что MySQL использует только один индекс для каждого конкретного SELECT!

Подробнее об этом расскажет наш спикер на видео

Селективность и порядок колонок в индексе

Эффективность индекса также зависит от порядка колонок в нем. Общее правило гласит: более селективные колонки следует помещать в начале индекса. Это позволяет MySQL более эффективно использовать индекс, даже если в запросе не упоминаются все колонки индекса.

Использование функций с индексированными колонками: недруг индексов

Применение функций к индексированным столбцам может свести на нет все усилия, направленные на их оптимизацию. Будьте осмотрительны с этим аспектом!

Цена индексации

Индексы не только помогают ускорять операции чтения, но и оказывают влияние на производительность операций DML. Каждый новый индекс замедляет INSERT, UPDATE и DELETE, при этом требует дополнительного места для хранения. Вот некоторые стратегии для минимизации этих недостатков:

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

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

Ограничения на пространство индексирования

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

Эффективное использование индексов: лучшие практики

Для индексации необходим постоянный мониторинг и периодическая коррекция. Вот некоторые из лучших практик:

Мониторинг использования индексов

Регулярное использование инструментов анализа поможет избежать лишних и ненужных индексов. Это подобно проведению генеральной уборки.

# Приведите в порядок вашу базу данных! 
pt-duplicate-key-checker

Улучшение дизайна схемы базы данных

Никакая индексация не спасет плохо сконструированную базу данных. Продуманная структура базы данных является основным условием для достижения хорошей производительности.

Балансировка

Избегайте крайностей – ни "индексировать всё", ни "избегать индексации".

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

Представьте базу данных как шкаф для рубашек:

👔👔👔👔👔👔 = Строки таблицы (данные)
🏷️🏷️🏷️ = Индексы (отметки для быстрого поиска)

Если каждую рубашку пометить всеми возможными характеристиками:

Метки: ["красный", "в полоску", "хлопок", "размер S", "повседневный", ...]
Таблица:  [`id`, `name`, `date`, `content`, ...]

Это будет избыточно, как и надеть бабочку на пикник:

🏷️🏷️🏷️🏷️🏖️🏷️🏷️🏷️🏷️🏷️🏷️🏷️⛔️🏷️🏷️ 
// Слишком много индексов, будто в смокинге на повседневное мероприятие

Оптимально организованное пространство — это лучший выбор:

👔👔🏷️👔👔🏷️👔👔 = Выбранные метки позволят быстро найти нужные данные (SELECT) без перегрузки.

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

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

1. MySQL :: MySQL 8.0 Справочное руководство :: 8.3.1 Как MySQL использует индексы — Официальное руководство по индексам от MySQL.
2. Изменение номера телефона в эмуляторе – Разработка для Android — Тематическое обсуждение для разработки под Android на Stack Overflow.
3. sql server – Как я могу запретить пользователю изменять файлы и опции базы данных, но все еще разрешать модификации схемы и данных? — Дискуссия о управлении правами пользователей на сервере SQL.
4. Технические презентации – Percona — Подробные исследования о производительности баз данных от экспертов Percona.