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

Почему производительность базы данных важна

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

Узкое место базы данных может влиять на 80% производительности приложения. База данных обычно является самым большим узким местом, а не CPU или память.

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

Стратегии индексации

B-Tree индекс: самый распространённый тип индекса. Эффективен для запросов на равенство (=) и диапазон (<, >, BETWEEN). Первичные и внешние ключи индексируются автоматически.

Составной индекс: индекс, содержащий несколько столбцов. Определите порядок столбцов на основе порядка запроса. Первый столбец в условии WHERE должен быть наиболее селективным.

Covering индекс: индекс, содержащий все столбцы, используемые в запросе. Доступ к таблице не нужен, читается только индекс.

Частичный индекс: индекс для строк, удовлетворяющих определённому условию. Как WHERE is_active = true. Уменьшает размер индекса.

Обслуживание индексов: удаляйте неиспользуемые индексы. Каждый индекс замедляет операции INSERT/UPDATE/DELETE. Проверяйте использование индекса с помощью EXPLAIN.

Оптимизация запросов

EXPLAIN / EXPLAIN ANALYZE: анализируйте план выполнения запроса. Цельтесь на Index Scan вместо Seq Scan (полное сканирование таблицы).

Проблема N+1: получайте связанные данные в одном запросе (JOIN) вместо отдельного запроса для каждой записи. Используйте eager loading в ORM.

Не используйте SELECT *: выбирайте только необходимые столбцы. Это уменьшает сетевой трафик и потребление памяти.

Оптимизация JOIN: избегайте ненужных JOIN. JOINите меньшие таблицы сначала. Используйте INNER JOIN вместо LEFT JOIN (если возможно).

Subquery vs JOIN: коррелированные подзапросы медленные. Используйте JOIN или CTE (Common Table Expression), когда возможно.

Кэширование и Connection Pooling

Query cache: кэшируйте результаты часто используемых запросов. Выполняйте кэширование на уровне приложения с помощью Redis или Memcached.

Connection pooling: повторно используйте соединения с базой данных. Открытие нового соединения для каждого запроса дорого. Используйте pgBouncer (PostgreSQL) или HikariCP (Java).

Read replicas: направляйте операции чтения на серверы-реплики. Держите операции записи на основном сервере. Это распределяет нагрузку чтения.

Materialized views: создавайте materialized view для сложных запросов агрегации. Обновляйте периодически.

Проектирование базы данных

Нормализация: уменьшайте повторение данных (1NF, 2NF, 3NF). Однако чрезмерная нормализация увеличивает количество JOIN и снижает производительность.

Денормализация: денормализация улучшает производительность в системах с интенсивным чтением (analytics, reporting). Компромисс: согласованность данных vs производительность.

Partitioning: разбивайте большие таблицы (миллионы строк). Range partitioning (по дате) — наиболее распространённый метод.

Типы данных: используйте правильные типы данных. INT вместо VARCHAR, VARCHAR (подходящий размер) вместо TEXT. Меньшие типы данных занимают меньше места и обрабатываются быстрее.

Мониторинг и профилирование

Slow query log: логируйте медленные запросы. PostgreSQL: log_min_duration_statement = 100. MySQL: slow_query_log = 1.

Performance Schema (MySQL) / pg_stat_statements (PostgreSQL): собирайте статистику запросов. Определяйте запросы, занимающие больше всего времени.

APM-инструменты: мониторьте производительность базы данных с помощью Datadog, New Relic или Dynatrace. Отслеживайте время запросов и частоту ошибок.

Использование индексов: проверяйте использование индексов с помощью pg_stat_user_indexes (PostgreSQL). Удаляйте неиспользуемые индексы.