Оптимизация запросов в SQL: советы и трюки для программистов

ИT-системы обычно строятся по трехзвенной архитектуре:

1. Интерфейс пользователя отвечает за отображение информации и получение инструкций.
2. Бизнес-логика (бэкенд) берет на себя функции обработки и подготовки данных в интересах пользователя и для взаимодействия с внешними сервисами. Например, для отправки уведомлений платежными API или любыми другими — в зависимости от цели создания ИT-системы.
3. База данных обеспечивает хранение информации.

В целом все три звена несут ответственность за то, чтобы в восприятии пользователя приложение работало быстро. Но с учетом того, что количество информации постоянно растет, скорость работы последнего звена, БД, выходит на первый план. 

1. Понимание основ производительности SQL
2. Индексы: ключ к быстрому доступу
3. Оптимизация SELECT-запросов
4. Работа с объединениями (JOINS)
5. Использование подзапросов и CTE
6. Управление транзакциями и блокировками
7. Обновление и удаление данных
8. Параметризация запросов
9. Индексные и материализованные представления
10. Мониторинг и профилирование производительности
11. Заключение

В статье поговорим об оптимизации запросов SQL. Мы рассмотрим фундаментальные аспекты, которые влияют на производительность БД, и предложим практические рекомендации для их улучшения. Расскажем о важности индексации, правильного использования объединений и подзапросов, а также о лучших практиках работы с агрегированными данными и управлением транзакциями. Также уделим внимание инструментам мониторинга и профилирования, которые помогут выявить узкие места и предложить решения для их устранения.

Эта статья поможет построить карту действий при решении проблем производительности сервиса. 

Давайте разберемся, как работают базы данных, которые используют SQL. 

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

Поэтому, когда запрос поступает в СУБД, он сначала разбирается на синтаксические компоненты и анализируется. Затем создается план выполнения, который включает оптимизированную последовательность шагов для извлечения и обработки информации. 

СУБД выбирает наилучший способ на основе статистики о данных и преднастроенных механизмов оптимизации. Это означает, что, во-первых, для более продуктивной работы нужно правильно обращаться к БД из бэкенда. То есть для постоянно запускающихся запросов нужно использовать возможность заранее их подготовить. Обычно драйвер БД позволяет вызвать инструкцию prepare или аналогичную ей, которая возвращает структуру подготовленного к исполнению запроса. Многократное ее использование положительно скажется на производительности. 

Но большая доля работы по оптимизации базы данных проходит в самой БД. Для этого используют несколько групп инструментов: 

• Языковые. Например, индексы, заранее сгруппированные по параметру строки, инструкции EXPLAIN, EXPLAIN ANALYZE и другие.
• Инструменты самой базы данных. Например, планировщики поддерживающих операций, таких как VACUUM или REINDEX.
• Утилиты базы данных, такие как pg_stat_statements (модуль расширенной статистики по запросам), pgBadger (анализатор логов), pgTune (инструмент подбора оптимальных параметров) и многие другие.
• Инструменты организации работы базы данных. Например, системы построения кэшей, а также пулирования и диспетчеризации входящих соединений.

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

Реальные запросы к системе носят, в общем, случайный характер, поэтому при формулировании критериев достижения требуемой производительности нужно использовать вероятностную терминологию. И вместо «система должна отрабатывать подготовку платежной ссылки менее, чем за 3 секунды» лучше формулировать что-то вроде SLA: «99% запросов на формирование платежной ссылки должны завершаться менее, чем за 3 секунды». 

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

Индексы создают копию определенных столбцов таблицы в виде дерева или другой структуры данных, где значения отсортированы и упорядочены. Когда вы выполняете запрос, СУБД может использовать этот индекс для быстрого поиска нужных строк, вместо того чтобы сканировать всю таблицу. Это отлично работает при применении условий выборки данных, в том числе при использовании JOIN’ов.

Существует несколько видов индексов, у каждого свои особенности и предназначение. 

• Кластерные определяют физический порядок строк. Это означает, что данные будут фактически отсортированы по значению индексированного столбца. Кластерные индексы могут значительно ускорить запросы, которые возвращают диапазоны значений.
• Некластерные, напротив, не изменяют физический порядок строк, но создают отдельную структуру для быстрого поиска.
• Уникальные обеспечивают уникальность значений в столбце, предотвращают дублирование данных. Например, вот такая конструкция неявно создает уникальный индекс и тем самым препятствует появлению повторных email’ов в таблице.

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

Однако использование их не бесплатно. Чем больше вы их создаете, тем больше у СУБД возникает накладных расходов на их поддержание. При каждом изменении информации (вставке, обновлении, удалении) приходится перебирать все связанные с данными индексы и уточнять их. А значит, операции выборки ускоряются за счет всех остальных. Поэтому, чтобы не гадать, где и какой индекс создать, важно проанализировать частоту выборки данных по тем или иным столбцам. И самые частые запросы поддержать созданием индексов по столбцам. 

Важно также понимать, что не везде уместно создание индекса. Например, если у нас есть таблица со статусами транзакций, которых очень немного, а выборка часто происходит только по успешным транзакциям, то смысл создать индекс есть. Тогда как индексировать их по сумме — идея, скорее, не очень хорошая. 

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

SELECT * FROM table

лучше избегать, а вместо этого явно перечислять столбцы:

SELECT colunm1, column2 FROM table

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

По мере возможности в операторе WHERE используйте в первую очередь индексируемые столбцы. Аналогично HAVING применяют для фильтрации данных, но на уровне групп, созданных с помощью GROUP BY. Например, если нужно найти группы с определенными характеристиками, сначала группируем данные, а затем применяем HAVING:

SELECT column1, COUNT*) FROM table GROUP BY column 1 HAVING
COUNT(*) > 1

Сортировка результатов с помощью ORDER BY и ограничение их количества посредством LIMIT также влияют на скорость исполнения запросов. ORDER BY позволяет отсортировать результаты по одному или нескольким столбцам:

SELECT column1, column2 FROM table ORDER BY columnl ASC;

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

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

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

SELECT column, column FROM table ORDER BY columnI ASC LIMI
10;

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

В целом же, при доработке запроса с целью его ускорения стоит следовать принципу «чем меньше, тем лучше»: меньше доставляемых столбцов, строк, полей объединения. Ровно столько, сколько нужно для решения задачи, не больше. Иногда в системах используют один и тот же запрос для построения разных визуализаций. При этом одни кейсы требуют больше данных, другие меньше. И для более частых кейсов можно пойти по пути создания специального запроса под конкретную ситуацию, который будет отрабатывать хорошо только в определенных условиях. 

Инструкции JOIN призваны выдать в качестве результата данные из нескольких источников. 

Оптимизация производительности объединений — это искусство балансировки между необходимостью получения всех нужных данных и минимизацией времени выполнения запроса. Для JOIN’ов применяют все те же принципы, что и для обычных: 

• индексирование;
• избегание функций в условиях WHERE;
• сокращение набора доставляемых столбцов;
• ограничение числа строк.

При выборе типа JOIN’а старайтесь применять наиболее ограничивающий (INNER), где это возможно, и избегать FULL JOIN. 

Подзапросы и Common Table Expressions (CTE) — это сильные инструменты в арсенале SQL-разработчика. Они позволяют создавать более гибкие и понятные конструкции. Это запросы внутри другого запроса. Их могут использовать в различных частях SQL-запроса: в операторе SELECT, WHERE или FROM. Например, если нужно найти сотрудников, которые работают в департаментах с численностью более 10 человек, можно использовать:

SELECT name
FROM employees
WHERE department_ id IN (
  SELECT id
  FROM departments
  WHERE employee _count > 10
)

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

Common Table Expressions (CTE), или общие табличные выражения, предоставляют альтернативный метод организации сложных запросов. CTE объявляют с помощью ключевого слова WITH и могут использовать для создания временных результирующих наборов. Их можно многократно применять в основном запросе. Например, если нужно найти департаменты с численностью более 10 человек и затем выбрать сотрудников из этих департаментов, можно использовать CTE:

WITH large departments AS (
  SELECT id
  FROM departments
  WHERE employee_count > 10
)
SELECT employees.name
FROM employees
INNER JOIN large
_departments ON employees department_ id =
large _departments.id;

CTE улучшают читаемость запросов, разбивают их на логические блоки, что делает код более понятным и поддерживаемым. Они также могут быть рекурсивными, что позволяет решать задачи, которые требуют итеративного подхода, такие как обработка иерархических данных.

Оптимизация CTE заключается в их правильном использовании и создании индексов на соответствующих столбцах, чтобы ускорить выполнение запросов. CTE обрабатывают так же, как подзапросы, но благодаря их декларативному стилю оптимизатор может лучше планировать их выполнение.

В итоге, использование подзапросов и CTE зависит от конкретных задач и контекста. Подзапросы удобны для простых вложенных запросов, в то время как CTE предпочтительны для сложных и многократно используемых временных наборов данных. 

Транзакции позволяют группировать несколько операций в одну атомарную единицу. Это гарантирует выполнение всех процессов целиком — либо их не выполняют совсем. Начинается транзакция с команды BEGIN, включает нужные операции и завершается COMMIT или ROLLBACK, чтобы зафиксировать или отменить изменения. 

В транзакциях есть так называемые уровни изоляции. Они варьируются от READ UNCOMMITTED, где транзакции видят незавершенные изменения друг друга, до SERIALIZABLE, где они полностью изолированы друг от друга. Это предотвращает любые возможные конфликты, но может замедлить систему. Наиболее распространенный и сбалансированный уровень READ COMMITTED, где транзакция видит только зафиксированные изменения других транзакций.

Блокировки возникают, когда транзакция обращается к данным, и их влияние на производительность может быть значительным. Когда одна транзакция захватывает ресурс, она устанавливает блокировку, которая предотвращает другие транзакции от изменения тех же данных до завершения первой. Это может привести к задержкам, связанным с тем, что начатая позже транзакция будет отложена до момента разблокировки данных, к которым она требует получения доступа. А случаются и ошибки программистов, которые могут приводить к взаимоблокировкам — ситуации, когда несколько транзакций ждут друг друга. 

Чтобы минимизировать негативное влияние блокировок, нужно стараться выполнять транзакции как можно быстрее. Чем меньше времени они удерживают блокировки, тем меньше вероятность конфликта. Например, все необходимые операции следует выполнять внутри транзакции быстро, а затем немедленно завершать ее с помощью COMMIT (подтверждение) или ROLLBACK (откат). Это не снижает нагрузки на систему, но позволяет ей работать быстрее, избегать простоев, связанных с блокировкой ресурсов. 

Также есть смысл выбирать подходящий уровень изоляции. В большинстве случаев уровень READ COMMITTED оптимальный, так как он позволяет транзакциям видеть только зафиксированные изменения других транзакций. Это снижает вероятность блокировок и улучшает производительность.

Запросы UPDATE и DELETE могут сильно нагрузить систему, особенно при работе с большими объемами информации. И дело не только в том, что нужно данные изменить. Потребуется еще и перестроение индексов. Их могут использовать для выборки сведений к изменению. 

Батчевые операции позволяют добиться большей производительности при работе с большим количеством данных. Вместо того чтобы обновлять или удалять все строки за один раз, операцию можно разбить на более мелкие части. Это помогает избежать длительных блокировок и уменьшает нагрузку на базу данных. Например, можно обновлять или удалять сведения порциями по 1000 строк. Для этого можно использовать специальные инструкции LIMIT и OFFSET. 

DO $$
DECLARE
  batch _size INTEGER := 1000;
  rows processed INTEGER;
BEGIN
  LOOP
    BEGIN
    -- Начинаем новую транзакцию для каждой порции данных
      WITH batch AS (
        SELECT id
        FROM large
        _table
        WHERE processed = FALSE
        ORDER BY id
        LIMIT batch_size
      )
      UPDATE large table
      SET processed - TRUE
      WHERE id IN (SELECT id FROM batch);

      GET DIAGNOSTICS rows processed = ROW_COUNT;
      -- Завершаем транзакцию

      COMMIT;
      - Если обработано менее batch_size строк, значит, больше нет
      данных для обработки
      EXIT WHEN rows processed < batch size;
    EXCEPTION
      WHEN OTHERS THEN
      -- Откат транзакции в случае ошибки
      ROLLBACK;
      RAISE NOTICE 'Error processing batch, skipping and moving to next batch.';
    END;
  END LOOP;
END $$;

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

Заворачивание запросов UPDATE и DELETE в транзакции позволяет обеспечить целостность данных. В случае ошибок информация восстановится, что защищает от ситуации частичных обновлений. 

Работа с UPDATE или DELETE без условия — это скорее исключение. Поэтому относитесь к таким транзакциям с осторожностью, а в общем случае применяйте условие WHERE с опорой на индексированные столбцы. 

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

Наконец, учитывайте настройки логирования и установленные триггеры. Обновление и удаление данных могут вызывать триггеры и генерировать большие объемы логов. Убедитесь, что это учитывается при планировании и выполнении крупных операций. При необходимости временно отключайте ненужные триггеры или снижайте уровень детализации логов, чтобы избежать перегрузки системы.

Как мы уже говорили, обращения к базе данных из бэкенда должны быть подготовленными. Давайте рассмотрим пример на языке Go для библиотеки pgx версии 5:

_, err = pool.Prepare(context.BackgroundO, "select _employees", "SELECT
id, name, salary FROM employees WHERE department_id=$1 AND salary >
$2")

В этом примере создали подготовленный именованный объект запроса, который дальше можем использовать, например, вот так: 

javascript
for _,q:= range queries { 
rows, err := pool. Query(context.BackgroundO, "select _employees", q.deptID, q.minSalary)
if err != nil {
log. Fatalf(" Query execution failed: %vin", err)
defer rows. Close
// Обработка результатов запроса
for rows.NextO {
var id int
var name string var salary float64
if err := rows.Scan(&id, &name, &salary); err != nil {
log. Fatalf("Row scan failed: %v\n".
', err)
｝
fmt.Printf("ID: %d, Name: %s, Salary: %.2f", id, name, salary)
// Проверка на ошибки при итерации
if err := rows.ErrO; err != nil {
log. Fatalf("Row iteration error: %vin", err)
}
}

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

fmt.Printf (SELECT salary FROM salaries WHERE %s, condition). 

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

Индексные представления, или просто представления (views), — это виртуальные таблицы на основе результатов выполнения SQL-запросов. Когда вы создаете представление, вы сохраняете SQL-запрос в базе данных, и при каждом обращении к нему он выполняется заново, возвращает актуальные данные. Это особенно полезно, когда вам нужно часто выполнять сложные запросы с объединениями и агрегацией данных. Представления упрощают повторное использование и делают код более читаемым и поддерживаемым. Однако, поскольку представления каждый раз выполняют запрос заново, они не всегда обеспечивают значительное улучшение производительности.

Материализованные (materialized views) отличаются тем, что сохраняют результаты выполнения на диск. Это означает, что такое представление содержит реальные данные, которые обновляют только по расписанию или вручную. В отличие от обычных материализованные не требуют повторного выполнения при каждом обращении. Это может значительно ускорить доступ к данным. Они особенно полезны для предвычисления сложных агрегатов и отчетов, которые требуют много ресурсов для вычисления.

Материализованные представления могут значительно улучшить производительность, особенно при работе с большими объемами данных и сложными вычислениями. Например, если у вас есть сложный запрос, который агрегирует данные за год, выполнение его каждый раз может быть очень ресурсоемким. Создание материализованного представления позволяет хранить результаты и обновлять их по мере необходимости, что значительно сокращает время выполнения и уменьшает нагрузку на БД. Обновление материализованных представлений следует планировать в зависимости от требований к актуальности данных. Можно обновлять их раз в день, раз в час или по мере внесения изменений в базу.

Чтобы оптимизировать запросы, нам нужна статистика о том, какие из них загружают систему больше всего. Для этого мы можем воспользоваться инструментом, который называется SQL Profiler. Он позволяет отслеживать активность базы данных: записывает информацию о выполнении запросов, времени их выполнения и потребляемых ресурсах. Это инструмент для глубокого анализа поведения БД в режиме, близком к реальному времени. 

Собрав профиль нагрузки, мы определяем запросы, которые будем оптимизировать. Здесь нам на помощь приходят встроенные инструкции СУБД EXPLAIN и EXPLAIN ANALYZE. Они призваны рассказать программисту о том, как именно СУБД собирается исполнять такой запрос. Какие индексы будет использовать, какие операции проведет и в каком порядке. Команда EXPLAIN выводит план без выполнения, тогда как EXPLAIN ANALYZE выполняет и выводит фактические данные о времени выполнения и количестве обработанных строк. Это позволяет понять, насколько эффективно выполняются запросы и где именно возникают задержки. Например, если EXPLAIN ANALYZE показывает, что полный скан таблицы (Seq Scan) занимает значительное время, это может указывать на отсутствие подходящего индекса. В этом случае создание его на соответствующем столбце может значительно улучшить производительность. Если же Index Scan занимает много времени, возможно, индекс неэффективен из-за плохой селективности или фрагментации информации.

Оптимизация на основе данных мониторинга может включать создание или изменение индексов, переписывание запросов или даже изменение структуры БД. Например, если профилирование показывает, что часто выполняются сложные объединения, которые занимают много времени, можно рассмотреть возможность денормализации данных или создания материализованных представлений для предвычисления сложных агрегатов.

Мониторинг и профилирование производительности — это непрерывный процесс. Системы и данные постоянно меняются, и запросы, которые были оптимальными вчера, могут стать проблемными завтра. Регулярный анализ и своевременная оптимизация позволяют поддерживать высокую производительность БД и обеспечивать пользователям быстрый и надежный доступ к информации. 

В то же время оптимизация скорости работы — это не просто улучшение всего, до чего можно дотянуться, и выжимание максимума из системы. Это должно быть осмысленным процессом, где есть четкие критерии качества, которые постоянно измеряются и оцениваются. При этом измеряться должны, в том числе, и бизнес-показатели. 

Работу по доводке производительности нужно делать не только силами инженера баз данных, но совместно с бэкенд- и фронтенд-специалистами, а также проектировщиком пользовательских интерфейсов. Целесообразность доводки производительности в итоге утверждает владелец сервиса.