Оптимизация работы РЗ

Одной из ключевых особенностей СМЭВ4 является скорость выполнения запросов. На этот показатель могут влиять как состояние и возможности инфраструктуры – ресурсы серверов, конфигурации СУБД (систем управления базами данных), настройки сетей, – так и состав и структура SQL-запроса внутри регламентированного запроса (РЗ).

В данной статье рассматриваются основные рекомендации по оптимизации SQL-запросов. Эти рекомендации помогут улучшить производительность запросов и сократить время их выполнения.

Рекомендации по разработке запросов

Для начала рассмотрим, как правильно составить запрос. При создании SQL-запросов следует избегать конструкций, заведомо замедляющих их выполнение. Ниже приведены рекомендации по общим принципам составления: примеры основных нежелательных вариантов использования конкретных операторов и способы оптимизации запросов.

Общие рекомендации:

  1.   SELECT *

Извлекайте только необходимые данные. Не следует запрашивать ненужные столбцы в SELECT, особенно во вложенных запросах в конструкциях IN и EXISTS.

  2.   Запрос неограниченного количества записей

Если запрос предполагает выдачу нескольких записей, ограничьте результаты оператором LIMIT.

  3.   LIKE ‘%text’

При использовании оператора LIKE в запросе индекс не используется, если шаблон начинается с % или знака _ (нижнее подчеркивание). Также этот тип запроса оставляет потенциальную возможность для получения слишком большого количества записей, которые не обязательно удовлетворяют цели запроса.

  4.   ORDER BY

Если нет строго требования к сортировке результирующего набора данных, то не следует использовать ORDER BY.

Использование операторов и конструкций SQL:

  1.   SELECT DISTINCT

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

  2.   UNION

Не рекомендуется использовать в одном запросе две инструкции SELECT с UNION с целью просмотреть одну и ту же таблицу несколько раз. Попробуйте переработать запрос таким образом, чтобы использовать все условия в одном SELECT, или используйте OUTER JOIN вместо UNION.

  3.   EXISTS полей

Если в запросе проверяется только существование записей коррелированным подзапросом с EXISTS, то в операторе SELECT этого подзапроса следует использовать константу вместо выбора значения фактического столбца. Например, EXISTS (SELECT 1 FROM table).

  4.   NOT

Если запрос содержит оператор NOT, то вполне вероятно, что индекс не используется, как и в случае с оператором OR. Рассмотрите возможность замены NOT операторами сравнения, такими как «>», «<» или «<>».

  5.   Неэффективный OR

При использовании оператора OR, скорее всего, не будет использоваться индекс. Альтернативным решением может быть замена на условие с IN.

  6.   Неэффективный AND

Оператор AND также может замедлить запрос, если используется неэффективным образом, например: WHERE year >= 1960 AND year <= 1980. Лучше переписать этот запрос, используя оператор BETWEEN: WHERE year BETWEEN 1960 AND 1980.

Индексы

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

• количество записей или строк;
• количество столбцов в ключе;
• размер значений столбца: например, символьное значение «abcdefghi» занимает больше места, чем «xyz»;
• количество похожих значений ключа.

  Примечание

 К применению индексов стоит подходить осмотрительно, так как в некоторых случаях это может ухудшить ситуацию. Следует учитывать, что при использовании индексов для их хранения требуется дополнительное дисковое пространство. Если проиндексированы текстовые поля, то это пространство может быть сравнимо с размером самой таблицы. Также при обилии индексов (особенно по нескольким полям или пересекающихся) оптимизатор может запутаться и «оптимизировать» выполнение запроса не в лучшую сторону.

Вот несколько рекомендаций, которые помогут наиболее эффективно применить индексы:

• При создании таблицы с UNIQUE и PRIMARY KEY индексы для этих полей создаются автоматически.
• При принятии решения об использовании индексов оптимизатору очень помогает команда VACUUM ANALYZE.
• В первую очередь необходимо создавать индекс на столбцах, которые часто используются в запросах в качестве условий поиска в WHERE и соединения в JOIN.
• Индекс для полей, используемых в ORDER BY и GROUP BY, MAX() и MIN(), не менее важен, чем индекс для полей в условиях WHERE. 

  Примечание

В PostgreSQL для ORDER BY, MIN(), MAX():
     –     вместо индекса часто используется Seq Scan;
     –     каждый случай нужно рассматривать, используя EXPLAIN;
     –     иногда использование LIMIT помогает выбрать в пользу index вместо Seq Scan.

• Лучше не индексировать поля, имеющие строковый тип, особенно поля типа text.

• Не следует индексировать поля boolean или числовые поля, имеющие небольшой разброс значений (флаговые поля), в этих случаях индекс больше навредит, чем поможет.
• Индекс для LIKE полезен только в случаях, когда маска не стоит в начале, т.е. при указании '%text' индекс не будет использован, а при 'text% — будет.
• Если в запросе используются функции LOWER/UPPER или ILIKE, то индекс будет использован, только если он был построен с учетом регистра, например: "CREATE INDEX news_ilike ON news (lower(title));".
• На небольших таблицах прямой перебор может работать быстрее, чем использование индекса, которое требует лишних дисковых операций. Таким образом, на небольших таблицах оптимизатор может отказаться от использования индексов автоматически.
• Индексы UNIQUE быстрее, чем индексы не по уникальным полям.
• Для полей, отражающих дату/время, и числовых больше подходит индекс btree, для текстовых – hash. Для индексов по двум и более полям возможно использовать только btree. Индексы btree лучше подходят для операций '<', '>', сортировки, а hash – для '=' и '<>'. При этом важно учитывать и само содержание текстовых полей. Например, если в поле содержится классификатор, то лучше использовать btree, потому что он так же хорошо построится в дерево. Если нет уверенности, какой тип индекса использовать, то следует выбрать btree.

Подводя итоги, можно резюмировать: оптимизация SQL-запросов — это комплексный процесс, который включает анализ текущей производительности, грамотное использование индексов и следование рекомендациям по написанию эффективных запросов. Регулярный аудит и доработка запросов позволяют не только ускорить выполнение операций, но и снизить нагрузку на базу данных, что особенно важно для высоконагруженных систем. Внедрение этих практик в процесс разработки и поддержки баз данных обеспечивает стабильную и эффективную работу приложения в долгосрочной перспективе.

Если предыдущие рекомендации вам не помогли, то стоит проанализировать план выполнения запроса и определить «узкое место».

Анализ запросов в PostgreSQL: использование EXPLAIN ANALYZE

Для выявления узких мест и определения изменений в запросе, которые будут наиболее эффективны, исключительно полезным инструментом является команда EXPLAIN с параметром ANALYZE. Она позволяет детально изучить план выполнения запроса, предоставляя информацию о том, как PostgreSQL обрабатывает данные на каждом этапе.

Команда EXPLAIN ANALYZE показывает предполагаемый план выполнения запроса и фактически выполняет его, собирая статистику по времени выполнения и количеству обработанных строк.

В результате выполнения этой команды вы получаете:

• фактическое время выполнения для каждого узла плана (в миллисекундах);
• количество строк, обработанных на каждом этапе;
• оценки планировщика, которые можно сравнить с реальными значениями для понимания точности прогнозов.

Пример использования команды EXPLAIN ANALYZE

FROM course_test_avro.trip

Вид обогащенного запроса:

SELECT id, number, duration, length 

FROM course_test_avro.trip_actual 

WHERE sys_from <= 3 AND COALESCE(sys_to, 9223372036854775807) >= 3;

Вывод команды EXPLAIN ANALYZE:

----------------------------------------------------------------------------

 Bitmap Heap Scan on trip_actual (cost=5.74..18.89 rows=70 width=80) 
(actual time=0.041..0.042 rows=1 loops=1)

 Recheck Cond: (sys_from <= 3)

 Filter: (COALESCE(sys_to, '9223372036854775807'::bigint) >= 3)

 Heap Blocks: exact=1

 -> Bitmap Index Scan on trip_actual_sys_from_idx (cost=0.00..5.73 rows=210 width=0) 
(actual time=0.015..0.016 rows=1 loops=1)

        Index Cond: (sys_from <= 3)

Planning Time: 0.732 ms

Execution Time: 0.090 ms

Ключевые показатели в выводе команды EXPLAIN ANALYZE:

• actual time – фактическое время, в миллисекундах: показывает, сколько времени заняло выполнение каждого узла плана.
• rows – количество строк: сравнение оценки планировщика (rows) с фактическим количеством строк (actual rows) помогает понять, насколько точны были прогнозы оптимизатора.
• loops – циклы: если узел выполняется несколько раз (например, в циклах), значение loops показывает количество итераций, а actual time и rows — средние значения за все итерации.
• Planning Time – время планирования: время, затраченное на построение плана выполнения запроса.
• Execution Time – время выполнения: Общее время выполнения запроса.

Как использовать результаты анализа:

• Сравнение оценок и фактических значений – если оценка количества строк значительно отличается от фактического значения, это может указывать на необходимость обновления статистики с помощью команды ANALYZE или пересмотра структуры запроса.

• Выявление узких мест – узлы плана с наибольшим значением actual time являются потенциальными узкими местами. Например, если последовательное сканирование (Seq Scan) занимает много времени, возможно, стоит добавить индекс.
• Оптимизация индексов – если индекс не используется или используется неэффективно, это может быть связано с низкой селективностью или неподходящим типом индекса.
• Оптимизация фильтров и условий – узлы с Filter могут указывать на дополнительные вычисления, которые замедляют выполнение. Проверьте, можно ли упростить условия или перенести их на более ранние этапы выполнения.

  Важно!

 Команда EXPLAIN ANALYZE выполняет запрос, поэтому ее не стоит использовать на продуктивных системах для тяжелых запросов. Результаты анализа могут варьироваться в зависимости от объема данных и текущей нагрузки на систему. Для больших таблиц планировщик может выбирать не такие планы, как для маленьких, поэтому тестируйте запросы на данных, близких к реальным.

Использование команды EXPLAIN ANALYZE в PostgreSQL — это мощный инструмент для анализа и оптимизации запросов. Он позволяет выявить узкие места, оценить точность прогнозов планировщика и определить, какие изменения в запросе или структуре данных будут наиболее эффективными. Регулярное применение этого инструмента помогает значительно улучшить производительность запросов и общую эффективность работы с базой данных.