ОПТИМИЗАЦИЯ JOIN-ЗАПРОСА POSTGRESQL

ОПТИМИЗАЦИЯ JOIN-ЗАПРОСА POSTGRESQL

Авторы публикации

Рубрика

Информационные технологии

Просмотры

85

Журнал

Журнал «Научный лидер» выпуск # 27 (228), Июль ‘25

Поделиться

В статье представлен детальный обзор различных типов JOIN-запросов, поддерживаемых PostgreSQL, включая INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN и CROSS JOIN. Описаны особенности каждого типа JOIN, их влияние на результирующий набор данных. Особое внимание уделено оптимизации JOIN-запросов, с акцентом на важность правильного индексирования столбцов, используемых в условиях соединения и фильтрации данных. Рассмотрены инструменты анализа планов выполнения запросов, позволяющие выявлять неэффективности и оптимизировать производительность JOIN-запросов для обеспечения быстрого и эффективного доступа к данным в реляционной базе данных.

В современном мире, где объемы данных растут с невероятной скоростью, базы данных стали неотъемлемой частью почти любого приложения. Однако, вместе с ростом данных, растет и сложность запросов, особенно тех, которые используют JOIN для объединения информации из разных таблиц. Соединение (join) — это операция, которая объединяет поля двух или более таблиц реляционной базы данных [3, с. 67].

Для того, чтобы наглядно рассмотреть проблему реализована база данных, в которой есть две сущности: users (пользователи) и requests (запросы). Сущность users представлена в таблице 1, requests в таблице 2.

Таблица 1.

Users

user_id

user_firstname

1

Dmitriy

2

Alexsey

3

Ivan

4

Sergey

5

Oleg

 

Таблица 2.

Requests

Request_id

User_id

Request_date

1

1

2025-01-19

2

2

2025-02-20

3

1

2025-02-22

4

3

2025-03-01

5

6

2025-03-02

 

Рассмотрим стандартный запрос JOIN.

SELECT u.user_id, u.user_firstname, r.request_id, r.request_date

FROM users u

JOIN requests r ON u.user_id = r.user_id;

СУБД перебирает все записи в таблице requests для каждой записи из таблицы users. Результат представлен в таблице 3.

Таблица 3.

Результат JOIN

User_id

User_firstnemt

Request_id

Request_date

1

Dmitriy

1

2025-01-19

1

Dmitriy

3

2025-02-22

2

Alexsey

2

2025-02-20

3

Ivan

4

2025-03-01

 

Помимо этого, есть и другие типы JOIN. Рассмотрим подробнее.

1. LEFT JOIN (LEFT OUTER JOIN)

Левое внешнее объединение (LEFT JOIN) включает все записи из основной (левой) таблицы, дополняя их соответствующими данными из связанной (правой) таблицы. Если совпадения не обнаружены, поля правой таблицы заполняются значениями NULL.

SELECT u.user_id, u.user_firstname, r.request_id, r.request_date, r.user_id

FROM users u

LEFT JOIN requests r ON u.user_id = r.user_id;

Результат представлен в таблице 4.

Таблица 4.

Результат LEFT JOIN

User_id

User_firstnemt

Request_id

Request_date

User_id

1

Dmitriy

1

2025-01-19

1

1

Dmitriy

3

2025-02-22

1

2

Alexsey

2

2025-02-20

2

3

Ivan

4

2025-03-01

3

4

Sergey

NULL

NULL

NULL

5

Oleg

NULL

NULL

NULL

 

2. RIGHT JOIN (RIGHT OUTER JOIN)

Правое внешнее соединение (RIGHT JOIN) включает все записи из правой таблицы, дополняя их связанными данными из левой таблицы. При отсутствии совпадений поля левой таблицы заполняются значениями NULL.

SELECT u.user_id, u.user_firstname, r.request_id, r.request_date

FROM users u

RIGHT JOIN requests r ON u.user_id = r.user_id;

Результат представлен в таблице 5.

Таблица 5.

Результат RIGHT JOIN

User_id

User_firstnemt

Request_id

Request_date

1

Dmitriy

1

2025-01-19

2

Alexsey

3

2025-02-22

1

Dmitriy

2

2025-02-20

3

Ivan

4

2025-03-01

NULL

NULL

5

2025-03-02

 

3. FULL JOIN (FULL OUTER JOIN)

Полное внешнее соединение (FULL OUTER JOIN) включает все записи из обеих таблиц, объединяя их по совпадающим значениям ключей. При отсутствии соответствий недостающие данные заменяются на NULL. Например:

SELECT u.user_id, u.user_firstname, r.request_id, r.request_date 

FROM users u

FULL JOIN requests r ON u.user_id = r.user_id;

Результат представлен в таблице 6.

Таблица 6.

Результат RIGHT JOIN

User_id

User_firstnemt

Request_id

Request_date

1

Dmitriy

1

2025-01-19

1

Dmitriy

3

2025-02-22

2

Alexsey

2

2025-02-20

3

Ivan

4

2025-03-01

4

Sergey

NULL

NULL

5

Oleg

NULL

NULL

NULL

NULL

5

2025-03-02

 

4. CROSS JOIN

Перекрестное объединение (CROSS JOIN) формирует комбинации всех записей из первой таблицы со всеми записями из второй таблицы. Этот метод соединения данных отличается максимальной ресурсоемкостью, поскольку результирующий набор содержит количество строк, равное произведению количества строк в объединяемых таблицах. Например:

SELECT u.user_firstname, r.request_date

FROM users u

CROSS JOIN requests r;

Результат представлен в таблице 7.

Таблица 7.

Результат CROSS JOIN

User_firstname

Request_date

Dmitriy
2025-01-19
Dmitriy
2025-02-20
Dmitriy
2025-02-22
Dmitriy
2025-03-01
Dmitriy
2025-03-02
Alexsey
2025-01-19
Alexsey
2025-02-20
Alexsey
2025-02-22
Alexsey
2025-03-01
Alexsey
2025-03-02
Ivan
2025-01-19
Ivan
2025-02-20
Ivan
2025-02-22
Ivan
2025-03-01
Ivan
2025-03-02
Sergey
2025-01-19
Sergey
2025-02-20
Sergey
2025-02-22
Sergey
2025-03-01
Sergey
2025-03-02
Oleg
2025-01-19
Oleg
2025-02-20
Oleg
2025-02-22
Oleg
2025-03-01
Oleg
2025-03-02

 

Оптимизация запросов с соединениями требует обязательного создания индексов на столбцах, используемых в условиях JOIN. Индексы существенно ускоряют поиск соответствий между таблицами, избавляя СУБД от необходимости полного перебора всех записей. Индекс — вспомогательная структура, которая создаётся для ускорения поиска данных [1, c. 153]. Индексы функционируют как предметные указатели в книге, позволяя быстро находить информацию без прочтения всего текста [4].

По умолчанию PostgreSQL автоматически создает индекс для первичного ключа, однако, для внешних ключей автоматическая индексация не происходит. Создание индекса на Requests.User_id критически важно для ускорения операций JOIN между Users и Requests. Без этого индекса СУБД придется сканировать всю таблицу Requests для каждого пользователя, чтобы найти соответствующие запросы. Скрипт для создания индекса для внешнего ключа:

CREATE INDEX idx_requests_user_id ON requests(user_id);

Во всех запросах (JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN) используется условие ON Users.User_id = Requests.User_id. Индексы на Users.User_id и Requests.User_id позволят СУБД очень быстро находить соответствующие строки в обеих таблицах, значительно ускоряя выполнение JOIN.

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

Для проверки результата можно использовать EXPLAIN ANALYZE, он прописывается перед запросом. В плане выполнения отображено, что СУБД использует “Index Scan” или “Index Seek” для доступа к таблицам Users и Requests, что означает, что индексы используются эффективно. Если отображается “Seq Scan” (последовательное сканирование), это означает, что индекс не используется, и вам может потребоваться пересмотреть структуру индексов или оптимизировать запрос.

Индексирование баз данных — критически важная технология, повышающая эффективность запросов к базам данных [2]. Анализ планов выполнения запросов позволяет убедиться в эффективности использования индексов и выявить потенциальные “узкие места”, требующие оптимизации. Грамотное применение индексирования является ключевым фактором для обеспечения быстрого и эффективного доступа к данным в реляционных базах данных.

Список литературы

  1. Комаров, В.И. Путеводитель по базам данных. Изд-во ДМК-пресс, 2024. - 153 с.
  2. Хабр [Электронный ресурс]: [сайт]. – Режим доступа: https://habr.com/ru/companies/ruvds/articles/724066/
  3. Шилдс, У. SQL: быстрое погружение. Изд-во Питер, 2019. - 67 с.
  4. SQL Academy [Электронный ресурс]: [сайт]. – Режим доступа: https://sql-academy.org/ru/guide/indexes
Справка о публикации и препринт статьи
предоставляется сразу после оплаты
Прием материалов
c по
Остался последний день
Размещение электронной версии
Загрузка материалов в elibrary
Публикация за 24 часа
Узнать подробнее
Акция
Cкидка 20% на размещение статьи, начиная со второй
Бонусная программа
Узнать подробнее