Какие знаешь способы ускорения выполнения запроса?

Основные подходы к оптимизации SQL-запросов

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

1. Использование правильных индексов

Индексы — фундамент производительности. Важно понимать их типы и применение:

Создание составных индексов для часто используемых условий WHERE и JOIN:

CREATE INDEX idx_user_status_created ON users(status, created_at);

Такой индекс ускорит запросы с фильтрацией по status и сортировкой по created_at.

Использование покрывающих индексов (covering indexes), когда индекс содержит все необходимые для запроса поля:

-- Индекс покрывает все выбираемые поля
CREATE INDEX idx_covering ON orders(user_id, amount, created_at);
SELECT user_id, amount FROM orders WHERE user_id = 100;

Анализ использования индексов через EXPLAIN:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 100;

Ключевые метрики: type (должен быть ref или range, а не ALL), key (используемый индекс), rows (оценочное количество сканируемых строк).

2. Рефакторинг структуры запросов

Оптимизация JOIN-операций:

• Всегда присоединяйте таблицы по индексируемым полям
• Избегайте JOIN большого количества таблиц в одном запросе
• Используйте EXISTS вместо IN для проверки существования записей:

-- Вместо IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- Лучше использовать EXISTS
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Сокращение объема выбираемых данных:

• Избегайте SELECT * — выбирайте только необходимые поля
• Используйте LIMIT с OFFSET осторожно (при больших значениях OFFSET производительность падает)
• Рассмотрите пагинацию на основе ключа:

-- Вместо OFFSET 10000 LIMIT 20
SELECT * FROM items WHERE id > :last_id ORDER BY id LIMIT 20;

Оптимизация агрегаций и группировок:

• Фильтруйте данные до агрегации с помощью WHERE, а не HAVING
• Используйте приблизительные агрегатные функции (например, APPROX_COUNT_DISTINCT в ClickHouse) там, где допустима неточность

3. Кэширование результатов запросов

В PHP-экосистеме эффективны несколько подходов:

Кэширование на уровне приложения с использованием Redis или Memcached:

// Пример кэширования с использованием Redis
$cacheKey = 'user_orders_' . $userId;
$orders = $redis->get($cacheKey);

if (!$orders) {
    $orders = $db->query("SELECT * FROM orders WHERE user_id = ?", [$userId])->fetchAll();
    $redis->setex($cacheKey, 3600, serialize($orders)); // Кэш на 1 час
}

Использование кэша запросов MySQL (для повторяющихся идентичных запросов):

-- Включаем кэш запросов (в конфигурации MySQL)
query_cache_type = 1
query_cache_size = 64M

4. Оптимизация через нормализацию/денормализацию

Нормализация (разделение данных на логические таблицы) уменьшает избыточность, но может увеличивать количество JOIN. Денормализация (дублирование данных) ускоряет чтение за счет увеличения объема данных и усложнения обновлений.

Пример денормализации — добавление счетчика комментариев в таблицу статей:

ALTER TABLE articles ADD COLUMN comment_count INT DEFAULT 0;
-- Обновляется триггером или приложением при каждом новом комментарии

5. Использование партиционирования

Для очень больших таблиц эффективно партиционирование по диапазонам или хэшу:

-- Партиционирование по дате
CREATE TABLE logs (
    id INT NOT NULL,
    created_at DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

6. Архитектурные оптимизации на уровне PHP

Пакетная обработка запросов (batch processing) вместо N+1 проблемы:

// Плохой подход (N+1 запрос)
foreach ($userIds as $userId) {
    $orders = $db->query("SELECT * FROM orders WHERE user_id = $userId");
}

// Хороший подход (1 запрос)
$placeholders = implode(',', array_fill(0, count($userIds), '?'));
$orders = $db->query(
    "SELECT * FROM orders WHERE user_id IN ($placeholders)", 
    $userIds
)->fetchAll();

Использование подготовленных выражений для повторяющихся запросов:

$stmt = $db->prepare("SELECT * FROM products WHERE category_id = ? AND price > ?");
$stmt->execute([$categoryId, $minPrice]);

7. Мониторинг и анализ

• Регулярный анализ медленных запросов через slow query log
• Использование инструментов мониторинга (Percona Monitoring, VividCortex)
• Профилирование запросов в рамках приложения

Важно понимать, что оптимизация — итеративный процесс: измерение → анализ → изменение → повторное измерение. Начинать следует с самых "тяжелых" запросов (часто выполняемых или затрагивающих большие объемы данных), используя инструменты профилирования как базы данных, так и приложения.