Какую знаешь команду, чтобы узнать какие индексы задействованы в запросе?

Отличный вопрос! Для анализа того, какие индексы использует или может использовать база данных для выполнения конкретного SQL-запроса, существует несколько мощных команд и методов. Их выбор зависит от используемой СУБД.

1. Ключевая команда: EXPLAIN

Основной и универсальный инструмент — это команда EXPLAIN (или её вариации). Она не выполняет запрос, а показывает план выполнения, составленный оптимизатором запросов СУБД. В этом плане как раз и указано, какие индексы будут (или могут быть) использованы.

-- Стандартная форма для MySQL/MariaDB, PostgreSQL
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Для получения более подробной информации, включая дополнительные данные о производительности (например, затраченное время), используют:

-- В MySQL 8.0+ и MariaDB 10.1+ - показывает реальное время выполнения (выполняет запрос!)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- В PostgreSQL также есть ANALYZE
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

2. Чтение вывода EXPLAIN (на примере MySQL)

Результат EXPLAIN — это таблица. Нас интересуют несколько ключевых столбцов:

• possible_keys: Какие индексы могут быть использованы для этого запроса (т.е. подходящие по условиям WHERE и JOIN).
• key: Какой индекс фактически выбран оптимизатором для использования.
• key_len: Длина используемой части ключа (важно для составных индексов).
• rows: Оценочное количество строк, которые будет проверять СУБД.
• type: Тип соединения (доступа). Самые важные для нас — index (полное сканирование индекса) и ref / eq_ref (поиск по ключу). Худший вариант — ALL (полное сканирование таблицы, обычно означает отсутствие подходящего индекса).
• Extra: Дополнительная информация, например, Using index (используется покрывающий индекс — Covering Index, когда все нужные данные есть в самом индексе).

Пример интерпретации:

EXPLAIN SELECT id, name FROM orders WHERE user_id = 100 AND status = 'shipped';

Если в выводе key = idx_user_status, а possible_keys также содержит этот индекс, значит, запрос будет эффективно использовать составной индекс по полям (user_id, status).

3. Особенности для разных СУБД

• PostgreSQL: Использует EXPLAIN и EXPLAIN ANALYZE. Также часто используют расширенный формат:

  EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) SELECT ...;
  

    Он даёт очень детализированный отчёт, который можно визуализировать с помощью инструментов вроде [PEV](https://tatiyants.com/pev/).

• SQLite: Команда также EXPLAIN QUERY PLAN. Её вывод менее детализирован, но показывает порядок сканирования и используемые индексы.

  EXPLAIN QUERY PLAN SELECT * FROM products WHERE category_id = 5;
  

4. Практическое использование в PHP-разработке

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

// Пример для PDO в PHP
$sql = "EXPLAIN SELECT * FROM articles WHERE published = 1 AND category = :cat";
$stmt = $pdo->prepare($sql);
$stmt->execute(['cat' => 'tech']);
$plan = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Анализ плана
foreach ($plan as $row) {
    echo "Используемый индекс (key): " . $row['key'] . "\n";
    echo "Возможные индексы (possible_keys): " . $row['possible_keys'] . "\n";
    if ($row['type'] === 'ALL') {
        echo "ВНИМАНИЕ: Полное сканирование таблицы! Рассмотрите добавление индекса.\n";
    }
}

5. Другие полезные команды и методы

• SHOW INDEX FROM table_name; (MySQL): Показывает все индексы, созданные для конкретной таблицы, их тип, уникальность и столбцы. Помогает понять структуру существующих индексов перед анализом.
• Включение лога медленных запросов (Slow Query Log): Позволяет автоматически фиксировать запросы, выполняющиеся дольше заданного порога. Каждый такой запрос затем нужно анализировать через EXPLAIN.
• Профилировщики БД: Например, mysqldumpslow для анализа логов, встроенный Profiler в MySQL Workbench, или pg_stat_statements в PostgreSQL. Они помогают выявить самые тяжелые запросы по накопительному времени выполнения.
• Инструменты мониторинга: Percona Monitoring and Management (PMM), VividCortex, инструменты облачных провайдеров (RDS Performance Insights). Они часто визуализируют планы запросов и статистику по индексам.

Ключевые выводы:

1. EXPLAIN и EXPLAIN ANALYZE — это основные команды для анализа использования индексов в конкретном запросе.
2. Важно не просто увидеть название индекса в key, но и понять план целиком: тип доступа (type), количество просматриваемых строк (rows), использование покрывающего индекса (Using index).
3. Если в possible_keys есть подходящий индекс, но в key стоит NULL, это часто означает, что оптимизатор считает полное сканирование таблицы более дешёвым, чем использование индекса (например, из-за маленького размера таблицы или неселективности условия). Возможно, нужна пересборка статистики (ANALYZE TABLE в MySQL).
4. Регулярный анализ планов выполнения для критичных и медленных запросов — обязательная практика для backend-разработчика, отвечающего за производительность приложения.

Таким образом, команда EXPLAIN — это ваш главный «диагностический инструмент» для оптимизации работы с базой данных, позволяющий заглянуть «под капот» и понять, как СУБД на самом деле выполняет ваш запрос.