Оптимизация SQL-запросов: ускоряем работу с СУБД

Почему важно оптимизировать SQL-запросы

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

Бэкенд-разработчики используют практики оптимизации SQL-запросов — ищут способ выполнения запроса наиболее эффективно, чтобы:

• минимизировать время ответа и сделать респонс быстрым (уложиться в требования SLO — Service Level Objectives, цель уровня обслуживания);
• выполнить договоренности на проекте (например, карточки интернет-магазина должны отдаваться не дольше, чем за 200 мс);
• ускорить работу приложений;
• уменьшить потребление ресурсов.

Анализ производительности SQL-запросов

Чтобы начать оптимизировать работу с БД, необходимо понять ее особенности. Когда разобран SQL-код, оптимизатор («query optimizer») проходится по нему и определяет последовательность операций. 

Если у нас простые запросы, операция может быть только одна. В случае со сложными (например, включающими JOIN, GROUP BY и прочие) нужно несколько. Причина в том, что нельзя выполнить следующую query, пока не получили результат предыдущей.

Операции, которые извлекают данные, условно делятся на два типа:

• «последовательные сканы» (sequential scans), «табличные сканы» (table scans) — проходятся по всей таблице последовательно, чтобы найти нужные значения;
• «индексные сканы» (index scans, index searches) — используют своего рода «закладки», как в книгах.

Также существует Parallel Seq Scan, но зачастую это требует больше ресурсов и расходов на пересылку данных от процесса к процессу.

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

Если появляется проблема «тяжелый запрос» или «тормозит база данных», нужно понять, в чем причина. Как правило, в БД настраивают логирование медленных запросов или метрики.

Основные способы найти тяжелый запрос: 

1. Использование инструментов логирования. В настройках конфигурации сервера БД можно подключить логирование и аудит. Опцию предоставляет большинство СУБД (MySQL, MariaDB и иные).
2. Проверка показателей БД. Архитектура базы данных и ее производительность влияют на скорость выполнения запросов. Стоит проверить скорость выполнения, количество одновременных подключений, использование буферного пула.
3. Профилирование производительности приложений. Найти медленно выполняющиеся запросы и сегменты кода помогают инструменты вроде AppDynamics APM, dynatrace, Sumo Logic или New Relic. Для мониторинга IT-инфраструктуры и приложений доступны российские Application Operations Management и Application Performance Management от cloud.ru. Разработчикам пригодится облачный сервис для построения запросов и анализа данных графовой структуры Graph Engine Service. 
4. Анализ плана выполнения. Это визуализация операций ядра БД, которые необходимы для выполнения SQL-запросов. Анализ позволяет найти «уязвимые места», на которые тратятся ресурсы: сканирование таблиц, лишние сортировки и т.д.
5. Профилировщики и инструменты диагностики. СУБД часто предоставляют решения для измерения ключевых показателей, влияющих на производительность: время отклика, загрузка ЦП, потребление памяти. Среди известных инструментов — pgBadger (для PostgreSQL), MySQL Enterprise Monitor (MySQL).
6. Проведение нагрузочного тестирования. НТ позволяет найти узкие места («бутылочное горлышко»), оценить лимит производительности для планирования пиковых нагрузок (например, в период распродаж или «Черной пятницы»), обнаружить потенциальные проблемы с производительностью. Инструменты нагрузочного тестирования — Apache JMeter, Locust, К6, Gatling и другие. В России доступен сервис Cloud Performance Test Service (CPTS) от команды Cloud.ru. Он предоставляет возможности, аналогичные Visual Studio Team Services (Test) от Microsoft Azure.

Ниже простые примеры анализа производительности SQL-запросов с помощью EXPLAIN и ANALYZE (SQLite). Обратите внимание, что все примеры ниже приводятся на основе SQLite. 

Синтаксис незначительно отличается от PostgreSQL и некоторых других. Для написания кода используют GigaIDE Desktop. Среда разработки предоставляет инструменты для работы с Structured Query Language и базами данных. Нужно скачать и установить GigaIDE Desktop, подключить SQLite и начать делать первые запросы. Помогать писать код будет GigaCode — AI-ассистент разработчика.

Создать файл с расширением .sql. В нем будет код для тестовой таблицы под названием GitVerse. 

CREATE TABLE GitVerse (c1 integer, c2 integer);

INSERT INTO GitVerse (c1, c2) VALUES (1, 1000000);

SELECT * FROM GitVerse

Если нужно посмотреть, как отрабатывает код, используют EXPLAIN:

EXPLAIN SELECT * FROM GitVerse

Система выставляет план с основными данными:

1. Init — инструкция, откуда начинают выполнять query.
2. OpenRead — открывает таблицу или index (если есть) для чтения. Используется идентификатор корня root=2, указывающий на table, которая используется для чтения данных.
3. Rewind — ставит указатель на начало таблицы. Идет подготовка к проходу по строкам.
4. Column — вытаскивает значения столбцов из текущей строки. Значение из столбца c1 table GitVerse записывается в регистр 1 (r[1]), значение столбца c2 записывают в регистр 2 (r[2]).
5. ResultRow — подготовленный ранее результат (регистр 1 и регистр 2) возвращается в качестве выходных данных.
6. Next — переходит к следующей строке. Если строк больше нет, выполняется переход на адрес 7 (Halt).
7. Halt — останавливает исполнение байт-кода. 
8. Transaction — нужен для защиты изменений, которые будут внесены в БД.
9. Goto — переходит к инструкции (адрес 1 для исполнения команды OpenRead заново).

Объяснение кода от AI-ассистента разработчика GigaCode (функциональность GIGACHAT):

Таким образом, можно увидеть, как SQLite выполняет запрос (QUERY PLAN). Код простой, поэтому и проследить его легко. EXPLAIN позволяет узнать и больше информации. Пример (скопировать и вставить):

CREATE TABLE IF NOT EXISTS test_table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
data TEXT
);
WITH RECURSIVE cnt(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM cnt
    WHERE x < 1000
)
INSERT INTO test_table (data)
SELECT 'Data ' || x FROM cnt;
SELECT * FROM test_table;

Результат:

В GigaIDE Desktop есть вкладка «DB Execution Console». Благодаря ей программист видит execution plan — план выполнения. Данных, которую предоставляет среда разработки, на крупных и сложных проектах зачастую недостаточно. Поэтому их отслеживают в терминале самого движка БД.

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

Рассмотрим приемы и способы, как оптимизировать работу со Structured Query Language.

Улучшение индексации таблиц

Index можно сравнить с закладкой в книге: он ускоряет поиск. Но в то же время есть риск замедления вставки (добавления записей), ведь в таком случае необходимо перестраивать index. 

Есть и еще одна опасность — неправильная установка индекса. Например, когда разработчик указывает hash index, а поиск происходит по параметру вроде «дай мне возраст больше 60». В таком случае index даже не подтянется: произойдет full scan (полное сканирование). Разработчик видит, что индекс есть, но не может им воспользоваться. 

Использование подзапросов и объединений

В одном SQL-запросе можно реализовать несколько подзапросов (SUBQUERIES). Это позволяет отдать результат одного подзапроса на исполнение второго. 

JOIN позволяет объединить несколько групп данных в один поток информации.

Реорганизация и репликация данных

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

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

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

Кэширование позволяет оптимизировать чтение и получение записей. БД закэширует query на своем уровне (закинет в буфер). При выполнении запроса БД будет возвращать данные уже из буфера, и это значительно быстрее по сравнению с тем, чтобы «проходиться» по условной таблице. Буфер выставляется в настройках: например, 1024 Мб (1 Гб). 

Если одновременных queries много, возможны ситуации Cache Miss (промах кэша), когда данные не попадают в кэш. При переполнении буфера система удаляет старые данные и заменяет их новыми.

Использование временных таблиц

Временные таблицы уменьшают количество операций чтения и записи за счет переиспользования данных. В Temp Table один раз записывается результат. Затем он считывается — из памяти, а не с диска. Эта операция требует меньше ресурсов, за счет чего и достигается экономия. Есть специальные метрики (latency table), которые позволяют сравнить, сколько «стоит» прочитать данные из памяти, а сколько — с диска.

Обновление статистики базы данных

Каждая таблица имеет статистику: сводка по данным, сколько значений null, какой range и другие. В зависимости от этого планировщик ставит их в очередь на исполнение. Например, можно установить index и посмотреть информацию по нему.

Создать index (код):

CREATE TABLE REPO (
  userId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL
);
INSERT INTO REPO VALUES (0001, 'GitVerseUser', 'Running code');
INSERT INTO REPO VALUES (0002, 'Dave', 'Working hard');
INSERT INTO REPO VALUES (0003, 'AvaGit', 'Optimization');
CREATE INDEX idx_username ON REPO (name);

Чтобы увидеть список индексов, используют команду:

SELECT count( * ) FROM sqlite_master WHERE type='index'

Результат для REPO:

Чтобы посмотреть количество таблиц, созданных в БД, используют:

SELECT count( * ) FROM sqlite_master WHERE type='table'

Результат в терминале:

Объяснение кода от GigaCode:

Цифра «1» мало что дает разработчику. Можно вывести имя индекса. Для этого необходимо ввести:

SELECT name FROM sqlite_master WHERE type='index'

Результат:

Для работы со статистикой в SQLite используют:

• PRAGMA database_size — размер БЗ в байтах;
• PRAGMA page_count — количество страниц в БД;
• PRAGMA freelist_count — количество используемых страниц;
• PRAGMA freepage_count — количество свободных страниц.

Для управления базами данных существуют специальные сервисы. Например, Data Admin Service (DAS) от Cloud.ru поддерживает несколько типов БД, включая MySQL и PostgreSQL. Предусмотрены инструменты визуализации, возможности ведения лога медленных запросов и другие опции.

Использование инструментов для профилирования запросов

СУБД предоставляют набор инструментов для профилирования: 

• MSQL Server Profiler для SQL Server;
• Oracle Enterprise Manager для Oracle;
• Performance Schema для MySQL.

Дополнительно разработчикам доступны инструменты мониторинга — Prometheus, Grafana, New Relic и DataDog. С их помощью можно увидеть время выполнения и отследить медленные, оценить количество затрачиваемых ресурсов и т.д.

Практические советы по оптимизации SQL-запросов

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

Использование операторов EXISTS и NOT EXISTS

Операторы EXISTS и NOT EXISTS позволяют выполнить проверку до того, как выполнять целевое действие. EXISTS в SQL возвращает значение —  true или false.

С помощью EXISTS можно проверить, существуют ли:

• таблицы;
• индексы;
• триггеры; 
• данные. 

С помощью EXISTS и NOT EXISTS в SQL идет проверка, есть ли нужная сущность. В зависимости от этого будет выполняться или не выполняться запрос. 

Например:

CREATE TABLE IF NOT EXISTS GitVerse (
  c1 INT,
  c2 VARCHAR(10)
);
SELECT * FROM GitVerse

В данном случае мы проверяем, существует ли таблица под названием GitVerse. Если table уже есть, то появится ошибка «Error executing»: система указывает, что таблица существует. Если нет, она создается. 

Объяснение кода от AI-ассистента разработчика GigaCode:

Данную возможность поддерживают MySQL, MariaDB, PostgreSQL, SQLite и некоторые другие. Вариантов, как применять EXISTS, много. Например, можно сделать условие: «Дай мне запись с ID, если она существует. Если нет, добавь». Для отбора результатов основного запроса в соответствии с данными, связанными в другой таблице, используют WHERE.

Избегание использования функций в WHERE-клаузулах

SQL WHERE Clause позволяет внедрять определенные условия. Например, получить записи, где ID пользователя = 1 или больше 60, сумма операций меньше 100 000 рублей и так далее. Проблема в том, что в WHERE-клаузулах могут появляться длинные и сложные условия. Из-за них операция выполняется долго. 

Функции в SQL могут возвращать множества (tables), содержать арифметические операции с числом, сортировки (ORDER BY), сложные операции. В результате снижается производительность. Планировщик видит функцию в WHERE-клаузуле и не может точно определить, какие столбцы будут задействованы. В результате планировщик может выбрать неправильный план исполнения, из-за чего обработка будет происходит медленно.

Причины, почему стоит избегать использования функций в WHERE-клаузулах, могут быть связаны с:

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

В SQLite запрещено писать функции в WHERE-клаузулах. Вместо этого разработчикам доступны подзапросы, объединение таблиц, case-выражения, объединение условий с помощью OR, NOT, AND.

Но в MySQL и некоторых других это допускается. Представим, что нужно записать все имена пользователей со строчной буквы (преобразовать формат GitVerse в gitverse). Затем начинается поиск совпадения: необходимы юзеры, у которых в имени есть слово GIT.

CREATE TABLE REPO (
  userId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL
);

INSERT INTO REPO VALUES (0001, 'GitVerseUser', 'Running code');
INSERT INTO REPO VALUES (0002, 'Dave', 'Working hard');
INSERT INTO REPO VALUES (0003, 'AvaGit', 'Optimization');
SELECT * FROM REPO WHERE UPPER(name) LIKE '%GIT%'

Результат:

Объяснение от GigaCode:

Правильное использование JOIN

JOIN позволяет объединять таблицы. Представьте, что вам необходимо создать table с продуктами (id, наименование, цена). Пускай стоит задача добавить категорию для каждого продукта («Мясо», «Рыба» и т.д.). При этом сами категории лежат в другой таблице.

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    price REAL NOT NULL
);
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name TEXT NOT NULL
);
INSERT INTO products (product_name, price) VALUES ('Product 1', 10.00), ('Product 2', 20.00), ('Product 3', 30.00);
INSERT INTO categories (category_id, category_name) VALUES (1, 'Milk'), (2, 'Meat');
SELECT p.product_name, c.category_name
FROM products AS p
JOIN categories AS c ON p.id = c.category_id;

Результат:

Объяснение от GigaCode:

Но при этом возникают проблемы: 

• сложность запросов, особенно когда необходимо работать с несколькими tables, данными и условиями;
• неоптимальность планов выполнения — планировщику сложно понять, в какой последовательности выполнять запрос;
• ошибки соединения между таблицами — некорректные результаты. 

Наконец, query с JOIN более сложные в отладке. Хорошими практиками считаются:

• разделение сложного JOIN на несколько мелких;
• проверка плана (EXPLAIN QUERY PLAN или EXPLAIN);
• использование временных таблиц.

Использование временных таблиц для уменьшения количества операций чтения/записи

Временная таблица создается так же, как обычная. Отличие только в том, что она содержит зарезервированное слово TEMPORARY:

CREATE TEMPORARY TABLE temp_table_GitVerse (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repo TEXT NOT NULL,
    date INTEGER
);
INSERT INTO temp_table_GitVerse (id, repo, date) 
VALUES (1, 'my_GitVerse_repo', '1999-10-30 15:30:00');
SELECT  *  FROM temp_table_GitVerse;

Итог:

GigaCode подробнее расскажет, что происходит в коде:

Оптимизация запросов с использованием подзапросов

Подзапросы (SUBQUERIES) — альтернатива использованию JOIN. В случае с JOIN БД сама решает, каким способом производить операцию. Применение SUBQUERIES указывает на то, что нужны вложенные циклы. Что лучше выбрать — объединение или подзапросы — зависит от конкретной ситуации. 

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

Пример кода с подзапросом:

CREATE TABLE REPO (
  userId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL
);
INSERT INTO REPO VALUES (0001, 'GitVerseUser', 'Running code');
INSERT INTO REPO VALUES (0002, 'Dave', 'Working hard');
INSERT INTO REPO VALUES (0003, 'AvaGit', 'Optimiztion');
SELECT * FROM REPO WHERE name = 'AvaGit';

Результат в терминале:

Объяснение кода от GigaCode:

Оптимизация запросов с использованием оконных функций

Оконная функция в SQL (Window Function) работает с выделенным набором строк (окном, секцией, рамкой окна, партицией) и выполняет вычисление для этого набора строк в отдельном столбце. Существуют разные виды:

• агрегирующие (Aggregate) — SUM, AVG, COUNT, MIN, MAX;
• ранжирующие (Ranking) — ROW_NUMBER, DENSE_RANK, RANK;
• смещения (Value) — LEAD, LAG, FIRST_VALUE, LAST_VALUE.

SQLite не поддерживает оконные функции напрямую, поэтому необходим подзапрос. Например, создается таблица со значениями id, date и amount. Стоит задача рассчитать среднее скользящее для поля amount за 7 дней.

CREATE TABLE your_table (
    id INTEGER PRIMARY KEY,
    date DATE,
    amount REAL
);

INSERT INTO your_table (id, date, amount) VALUES (1, '1999-10-30 15:30:00', 1111);
INSERT INTO your_table (id, date, amount) VALUES (2, '1999-10-29 15:30:00', 999);
INSERT INTO your_table (id, date, amount) VALUES (3, '1993-10-28 15:30:00', 689);

SELECT 
    t1.*,
    (SELECT AVG(t2.amount) 
     FROM your_table t2 
     WHERE t2.date BETWEEN DATE_SUB(t1.date, INTERVAL 7 DAY) 
     AND t1.date) AS moving_average_7days
FROM 
    your_table t1;

Результат:

Объяснение кода от GigaCode:

Заключение

Общие рекомендации по работе с SQL-запросами и повышению эффективности таковы:

• проверять планы выполнения;
• создавать индексы;
• ограничивать JOIN;
• внедрять временные таблицы;
• упорядочивать результаты с помощью функций ранжирования RANK или DENSE_RANK;
• фильтровать данные через подзапросы.

БД становятся сложнее, количество функций и сущностей увеличивается. Поэтому вопросы, как работать эффективнее и оптимизировать query, выходят на первый план. С GigaCode и GigaIDE Desktop можно писать код, тестировать, генерировать документацию и использовать практики оптимизации.