Как оптимизировать SQL-запросы. Часть 6
Части 1, 2, 3, 4-5
Часть VI. Пусть БД поможет вам: измерение, поддержка и доверие оптимизатору
Планировщик запросов умнее, чем принято считать. Ваша задача — предоставить ему достоверную информацию, а затем проверить результат.
1. Прочитайте план выполнения
План выполнения — это то, как база будет выполнять ваш запрос. Прежде чем что-либо оптимизировать, посмотрите на план. В PostgreSQL команда
EXPLAIN ANALYZE выполняет запрос и сообщает план и что произошло на самом деле:EXPLAIN ANALYZE
SELECT * FROM orders WHERE total > 100;
Ознакомьтесь с ним, чтобы понять суть дорогостоящих операций: последовательное сканирование большой таблицы обычно означает отсутствие или неиспользуемый индекс, а большой разрыв между расчётным и фактическим количеством строк указывает на устаревшую статистику. План превращает оптимизацию из догадок в подтверждение. Вы исправляете то, что, по данным плана, работает медленно, без гадания.
2. Поддерживайте актуальность статистики
Планировщик выбирает между сканированием и поиском по индексу на основе статистики ваших данных — количества строк, количества уникальных значений и их распределения. Когда эта статистика устарела, планировщик делает неверные предположения и выбирает неверные планы, даже при идеальных индексах.
ANALYZE обновляет статистику:ANALYZE orders;
PostgreSQL использует автоочистку (autovacuum) для поддержания актуальности статистики, но после большой загрузки данных или массового обновления стоит самостоятельно запустить
ANALYZE. Движок может оптимизировать запрос настолько хорошо, насколько это позволяют его статистические данные. Хорошая статистика гораздо важнее точной формулировки запроса.3. Используйте подсказки запросов экономно
Подсказка запроса заставляет базу выполнять запрос по вашему алгоритму, а не по алгоритму планировщика. PostgreSQL намеренно поставляется без синтаксиса подсказок. Его философия в том, что вы должны исправлять первопричину — индексы, статистику, форму запроса — а не быть умнее планировщика. Вы можете подкрутить его с помощью настроек сессии, но рассматривайте это только как диагностику в среде разработки:
-- Только для диагностики: посмотреть, как выглядит план без последовательного сканирования
SET enable_seqscan = off;
Если вам действительно нужны подсказки, расширение
pg_hint_plan добавит их — но используйте это как последнюю меру. Подсказка фиксирует решение, которое сегодня кажется правильным, но может оказаться неверным после увеличения объёма данных, так что завтра оно незаметно превратится в медленный запрос.4. Непрерывный мониторинг и настройка
Оптимизация — не разовая задача. Объём данных растёт, шаблоны доступа меняются, и вчерашний быстрый запрос становится сегодняшним узким местом. Отслеживайте, какие запросы на самом деле обходятся дороже всего. Расширение
pg_stat_statements агрегирует статистику выполнения по всей вашей рабочей нагрузке:-- Самые медленные запросы по среднему времени
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Современный планировщик запросов уже выполняет умные переписывания — перенос предикатов вниз, изменение порядка соединений, преобразование
IN в JOIN. Вы мало выиграете, вручную настраивая текст предложения WHERE. Большая выгода достигается за счёт предоставления планировщику того, что ему нужно: селективных предикатов, актуальной статистики и правильных индексов — а затем анализа плана для подтверждения.Источник: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices
