6️⃣-й пост из цикла.

Оптимизация подзапросов и агрегаций

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


🔵 Оптимизация подзапросов

Помимо преобразования подзапросов в JOIN или использования EXISTS, рассмотри следующие советы по оптимизации подзапросов:

🔘 Используй коррелированные подзапросы с умом: Коррелированные подзапросы могут быть медленными, поскольку они выполняются один раз для каждой строки внешнего запроса. Если возможно, пересмотри логику, чтобы минимизировать использование коррелированных подзапросов.
🔘 Избегай подзапросов в списке SELECT: Подзапросы в списке SELECT могут привести к снижению производительности. Если нужно получить данные из связанных таблиц, используйте вместо этого JOIN.
🔘 Ограничение результатов подзапроса: Если подзапрос возвращает большой набор результатов, это может повлиять на производительность. При необходимости ограничь набор результатов с помощью выражений TOP (SQL Server) или LIMIT (MySQL, PostgreSQL, SQLite).

🔵 Оптимизация агрегаций

Агрегации, такие как SUM, COUNT, AVG и другие, необходимы, но могут быть ресурсоёмкими. Вот как их оптимизировать:

🔘 Материализованные представления агрегаций: Если ты часто используешь агрегации, подумай о создании материализованных представлений (если это поддерживается твой БД). Материализованные представления хранят предварительно вычисленные агрегации, что уменьшает необходимость их перерасчёта во время запросов.
🔘 Используй INDEX для столбцов агрегации: Если запрос включает агрегации по отдельным столбцам, убедись, что эти столбцы соответствующим образом проиндексированы. Это может ускорить операции агрегации.
🔘 Пакетная обработка: Если в приложении используется пакетная обработка или расписание задач, выполняющих агрегирование, рассмотри возможность запуска этих задач в непиковые часы, чтобы минимизировать влияние на запросы в реальном времени.

👩‍💻 PostgreSQL

▶️Документация
▶️Как не надо делать

▶️Книги
▶️В каком порядке читать книги
▶️Курсы

▶️Чат @pgsql
▶️Вакансии @pgsqljobs

7️⃣-й пост из цикла.

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

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


🟣 Операции на основе множеств

Вместо использования курсоров или циклов для манипулирования данными строка за строкой используй операции на основе множеств, предоставляемые SQL. Например, ты можешь использовать операторы UPDATE или DELETE с предложениями WHERE для изменения или удаления нескольких строк на основе определённых условий.

🟣 Пакетная обработка

Если нужно обработать много строк, рассмотри возможность использования пакетной обработки. Разбей операцию на управляемые фрагменты, используя LIMIT (MySQL, PostgreSQL, SQLite), FETCH FIRST (IBM Db2) или аналогичные выражения. В каждой итерации обрабатывай подмножество строк, снижая нагрузку на ресурсы сервера.

🟣 Оптимизация циклов с помощью операций на основе множеств

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

Заключительный пост цикла.

🙂 Применяя методы, описанные в постах выше, ты сможешь улучшить навыки оптимизации SQL-запросов, особенно когда речь идёт о высоконагруженных приложениях, сложных соединениях и проблемах индексирования.

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

📎 Postman и паттерны проектирования API

Команда Postman Open Technologies собирает информацию о стандартах, отраслевых форматах и спецификациях, открытых API.

Каталог практик и паттернов оформлен как рабочее пространство Postman

На текущий момент там описаны следующие паттерны:
🟡 Форматы данных:
🔵Коды стран (ISO 3166)
🔵Коды валют (ISO 4217)
🔵Дата, время и временные промежутки (ISO 8601)
🔵Числа с десятичными дробями
🔵Кастомные заголовки HTTP
🔵Расширенное описание ошибки (RFC 9457 - кстати, очень хороший формат для передачи смысла ошибки HTTP)

🟡Управление кэшированием:
🔵Параметр Cache-control
🔵Параметр Etag
🔵Параметр Expires

🟡Фильтрация:
🔵Параметры поискового запроса
🔵Точное соответствие
🔵Диапазон значений поля
🔵Выбор полей для ответа

🟡Пагинация:
🔵Заголовки page and per_page (rfc 5988)
🔵Курсор / NextRecordKey
🔵Параметры Limit и Offset

🟡Сортировка:
🔵По одному полю - параметры sort_by, sort_order
🔵По нескольким полям

🟡Версионирование:
🔵На уровне URL API
🔵На уровне отдельного ресурса
🔵Через заголовок Accept-Version
🔵Через заголовок Accept

🟡Информация:
🔵Контакты разработчиков
🔵Лицензия
🔵Условия использования
🔵Заголовок Sunset

👩‍💻 SQL CheatSheet

Шпаргалка по SQL

JOIN, IN, LIKE, BETWEEN, ORDER BY и другое, ботай!

⚡️ Выкатили новую версию 🖼️ Airflow 2.8.3


📦 PyPI

📚 Docs

🛠 Release Notes

🐳 Docker Image:
docker pull apache/airflow:2.8.3

#airflow

👩‍💻 Распространённые ошибки составления SQL запросов

Использование SELECT *

❤️‍🩹 Проблема: Выбор всех колонок с помощью SELECT * приводит к передаче ненужных данных, увеличению использования памяти и снижению производительности запросов.
✔️ Решение: Укажи в операторе SELECT только необходимые колонки.

〰️ Пример проблемы

SELECT * FROM employees;

〰️ Улучшенный запрос

SELECT employee_id, first_name, last_name FROM employees;

Отсутствие индексов

❤️‍🩹 Проблема: Отсутствие индексов может привести к полному сканированию таблицы и снижению производительности запросов.
✔️ Решение: Создай и используй индексы для часто используемых в выражениях WHERE колонок.

〰️ Создание индекса

CREATE INDEX idx_last_name ON employees(last_name);

〰️ Использования индекса в запросе

SELECT * FROM employees WHERE last_name = 'Smith';

Чрезмерное использование подзапросов

💔 Проблема: Подзапросы могут работать медленнее, чем JOIN, особенно при работе с большими наборами данных.
✔️ Решение: Используй JOIN, когда это возможно, а подзапросы оставь для ситуаций, в которых они более эффективны.

〰️ Пример проблемы (подзапрос)

SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees);

〰️ Улучшенный запрос (JOIN)

SELECT DISTINCT d.department_name FROM departments d JOIN employees e ON d.department_id = e.department_id;

Неэффективные JOIN

❤️‍🩹 Проблема: Выбор неправильного типа JOIN (например, Cartesian JOIN) или неправильное указание условий соединения может привести к неправильным результатам или замедлению запросов.
✔️ Решение: Разберись в различных типах JOIN (INNER, LEFT, RIGHT, FULL) и используй их по назначению.

〰️ Пример проблемы (Cartesian JOIN)

SELECT * FROM employees, departments;

〰️ Улучшенный запрос (INNER JOIN)

SELECT e.employee_name, d.department_name FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Неиспользование выражений WHERE

❤️‍🩹 Проблема: Отсутствие фильтрации данных с помощью выражений WHERE может привести к запросу ненужных данных.
✔️ Решение: Всегда включайте выражения WHERE, ограничивающие набор результатов.

〰️ Пример проблемы (без выражения WHERE)

SELECT * FROM orders;

〰️ Улучшенный запрос (с выражением WHERE)

SELECT * FROM orders WHERE order_date >= '2023-01-01';

Игнорирование планов выполнения запросов

💔 Проблема: Игнорирование планов выполнения запросов может привести к упущенным возможностям оптимизации.
✔️ Решение: Используй такие инструменты, как EXPLAIN, для анализа планов выполнения и внесения необходимых оптимизаций.

〰️ Просмотр плана выполнения

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

Отсутствие оптимизации больших наборов данных

❤️‍🩹 Проблема: Запросы, хорошо работающие с небольшими наборами данных, могут плохо работать с большими объёмами данных.
✔️ Решение: Реализуй такие стратегии, как пагинация, разбиение данных на разделы и оптимизация индексов для больших наборов данных.

〰️ реализация пагинации

SELECT * FROM products LIMIT 10 OFFSET 20;

Повторяющиеся агрегации

❤️‍🩹 Проблема: Повторение одних и тех же агрегаций в нескольких частях запроса может быть неэффективным.
✔️ Решение: Используй CTE (Общие табличные выражения) для хранения промежуточных результатов и избегай лишних вычислений.

〰️ Пример проблемы (повторяющаяся агрегация)

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;

〰️ Улучшенный запрос (с CTE)

WITH DepartmentSalaries AS (
    SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department
)
SELECT * FROM DepartmentSalaries;

Неадекватная обработка ошибок

💔 Проблема: Неправильная обработка ошибок может привести к сбоям в работе приложения или неправильным результатам.
✔️ Решение: Реализуйте надлежащую обработку ошибок в SQL запросах или в коде приложения.

〰️ Пример обработки ошибок в SQL (MySQL)

BEGIN;
-- SQL выражение
IF some_condition THEN
    ROLLBACK; -- Откат транзакции при ошибке
ELSE
    COMMIT; -- Коммит транзакции при успешном выполнении всех выражений
END IF;

👩‍💻 Временная таблица в базе данных SQL

〰️〰️〰️〰️〰️〰️〰️〰️

🔜 Временная таблица SQL (temp table) — это такая таблица, которую ты создаёшь и используешь в контексте определённого сеанса или транзакции в СУБД. Она предназначена для хранения временных данных, которые понадобятся на короткое время и не требуют постоянного хранения.

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

🔜 Чтобы создать временную таблицу, можешь использовать инструкцию CREATE TABLE с ключевым словом TEMPORARY или TEMP перед именем таблицы:

CREATE TEMPORARY TABLE my_temp_table (
    id INT,
    name VARCHAR(50),
    age INT
);

Детали:

🔜 Инструкция CREATE TEMPORARY TABLE используется для создания временной таблицы.

🔜 my_temp_table — это имя, которое присваивается временной таблице. Имя можно выбрать любое.

🔜 Внутри круглых скобок мы определяем столбцы временной таблицы.

🔜 В примере временная таблица my_temp_table имеет три столбца: id типа INT, name типа VARCHAR(50) и age типа INT.

🔜 При необходимости ты можешь добавить дополнительные столбцы, указав их имена и типы данных.

🔜 Временная таблица автоматически удаляется в конце сеанса или при завершении сеанса.

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

🔜 Создаёшь временную таблицу с подмножеством данных

CREATE TEMPORARY TABLE subset_data AS
SELECT column1, column2, column3
FROM original_table
WHERE condition;

🔜 Анализируешь подмножества данных

SELECT column1, AVG(column2) AS average_value
FROM subset_data
GROUP BY column1;

🔜 Удаляешь временную таблицу

DROP TABLE subset_data;

👩‍💻 Повышение производительности запросов с использованием временных таблиц

〰️〰️〰️〰️〰️〰️〰️〰️

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

Такое повышение происходит за счёт уменьшения объема данных, обрабатываемых на каждом этапе, или за счёт предварительного вычисления промежуточных результатов.

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

➡️ Создаёшь временную таблицу для хранения промежуточных результатов

CREATE TEMPORARY TABLE temp_results AS
SELECT column1, COUNT(*) AS count_value
FROM large_table
WHERE condition1
GROUP BY column1;

➡️ Используешь временную таблицу для оптимизации итогового запроса

SELECT column1, column2
FROM temp_results
WHERE count_value > 10
ORDER BY column1;

➡️ Удаляешь временную таблицу

DROP TABLE temp_results;

👩‍💻 Подготовка и преобразование данных во временных таблицах

〰️〰️〰️〰️〰️〰️〰️〰️

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

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

Временные таблицы обеспечивают гибкий и эффективный способ обработки и манипуляцией данными без изменения исходного набора. Пример:

➡️ Создаёшь временную таблицу для подготовки данных

CREATE TEMPORARY TABLE staging_table (
    id INT,
    name VARCHAR(50),
    quantity INT
);

➡️ Импортируешь и преобразовываешь данные в стейджинг таблицу

INSERT INTO staging_table (id, name, quantity)
SELECT id, UPPER(name), quantity * 2
FROM external_source;

➡️ Валидируешь и проводишь манипуляции с данными в стейджинг таблице

UPDATE staging_table
SET quantity = 0
WHERE quantity < 0;

➡️ Вставляешь преобразованные данные в таргет таблицу

INSERT INTO final_table (id, name, quantity)
SELECT id, name, quantity
FROM staging_table;

➡️ Удаляешь временную таблицу

DROP TABLE staging_table;

👩‍💻 Кортежи


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

📌 Если хочешь явно узнать, является ли значение кортежа, да и любого другого объекта, фиксированным, можешь использовать встроенную функцию hash и сделать на её основе функцию is_fixed, например такую:

def is_fixed(obj) -> bool:
    try:
        hash(obj)
    except TypeError:
        return False
    return True

#python #trix

👩‍💻 Кортежи


К посту выше. Важно не забывать, что неизменность кортежа относится только к хранящимся в нём ссылкам - их нельзя ни удалить, ни изменить. Но если какая-то ссылка указывает на изменяемый объект и этот объект будет изменён, то значение кортежа изменится.

Пример, изначально два кортежа равны:

a = (1, 'data engi', [8, 9])
b = (1, 'data engi', [8, 9])

print(a == b)

Получим результат:

True

Но при внесении изменений в последний элемент кортежа b:

b[-1].append(10)

print(a == b)

Получим результат:

False