День семьсот шестьдесят второй. #ЗаметкиНаПолях #SQL
На третьем году существования канала я понял, что есть один язык, который я незаслуженно обходил вниманием. Восполняю упущение. Начинаю новую серию постов про SQL. Думаю, что основные основы читателям моего канала объяснять не надо (если надо, пишите в комментариях, что интересно), поэтому буду писать о «фишках». Проблема в том, что они различаются как по наличию в разных СУБД, так и по синтаксису. Поэтому буду стараться выбирать более-менее универсальные вещи и описывать концепцию, а не особенности синтаксиса.

Возвращение Данных из DML Запроса
Поначалу это может показаться странным, но возможность возвращать данные из DML - очень полезная функция.
- В запросе INSERT:
Наиболее распространённый вариант использования - получение автоматически сгенерированных значений. Например, ключей для создания записей в дочерних таблицах. Кроме того, возвращённые таким образом данные могут использоваться для аудита или логирования запросов.
- В запросе UPDATE:
Здесь выражение это используется не так часто, но всё же возможно его применение для получения значений по умолчанию, заданных в базе данных, либо для аудита запросов.
- В запросе DELETE:
Совсем редкий случай использования, в основном для целей аудита, либо, возможно, переноса данных в архивную таблицу, хотя так лучше не делать.

СУБД: Oracle, PostgreSQL, MariaDB
Выражение: RETURNING
Синтаксис:

INSERT INTO | UPDATE | DELETE FROM table …
RETURNING expression1 [INTO variable1]
[, expression2 [INTO variable2]] …;

Выражение INTO variable1 позволяет вставить возвращённое значение в переменную в хранимой процедуре. Возвращать значения можно как в переменные примитивных типов, так в целую строку, используя пользовательский тип:

my_row mytable%ROWTYPE;
…
RETURNING * INTO my_row;

Пример:

INSERT INTO users (firstname, lastname) 
VALUES ('Joe', 'Cool') RETURNING id;

СУБД: MsSQL
Выражение: OUTPUT
Синтаксис:

INSERT INTO | UPDATE | DELETE FROM table …
OUTPUT INSERTED.expression1 [INTO variable1]
[, DELETED.expression2 [INTO variable2]] …;

В MsSQL также можно обратиться к псевдотаблицам INSERTED и DELETED. В запросе UPDATE – DELETED представляет заменяемое значение, INSERTED представляет новое значение поля. В запросах INSERT и DELETE используется соответствующая псевдотаблица.

Пример:

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;

Источники:
- https://app.pluralsight.com/library/courses/postgresql-data-manipulation-playbook/
- https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

День семьсот семьдесят девятый. #ЗаметкиНаПолях #SQL
Выражение WITH и Сложные Запросы
В разработке ПО обычной практикой является инкапсуляция инструкций в небольшие и легко понятные единицы - функции или методы. В SQL мы оперируем не инструкциями, а запросами. Чтобы сделать запросы многоразовыми, в SQL-92 были введены представления (view). После создания представление получает имя в схеме базы данных, чтобы другие запросы могли использовать его как таблицу. В SQL-99 добавлено предложение WITH для определения «представлений в области действия оператора». Они не хранятся в схеме базы данных, а живут только в рамках текущего запроса. Это позволяет улучшить структуру запроса, не загрязняя глобальное пространство имен. Предложение WITH также известно как общее табличное выражение (CTE).

Синтаксис

WITH query1 (column1, …) AS
  (SELECT … FROM table …)
SELECT * FROM query1;

WITH не является самостоятельной командой, за ним должен следовать SELECT. Запрос SELECT (и содержащиеся в нем подзапросы) могут ссылаться в своём блоке FROM на определённое в предложении WITH имя подзапроса.

Одно предложение WITH может определять несколько имён подзапросов, разделяя их запятыми. Каждый из этих подзапросов может ссылаться на ранее определённые имена подзапросов:

WITH 
  query1 AS (SELECT …), 
  query2 AS (SELECT … FROM query1 …)
SELECT …

ВАЖНО! Имена подзапросов, определённые в предложении WITH скрывают таблицы или представления с тем же именем.

СУБД
Базовая функциональность предложения WITH доступна во всех современных СУБД.

Производительность
- Большинство баз данных обрабатывают WITH-запросы так же, как и представления: они заменяют ссылку на запрос его определением и оптимизируют общий запрос.
*PostgreSQL до версии 12 оптимизировала каждый подзапрос и главный запрос независимо друг от друга.
- Если WITH-запрос упоминается несколько раз, некоторые базы данных кешируют его результат, чтобы предотвратить двойное выполнение.

Полезные расширения
1. WITH как префикс DML запроса (PostgreSQL, SQL Server, SQLite)
Некоторые СУБД позволяют использовать WITH не только с запросами SELECT, но и с запросами на манипуляцию с данными (INSERT, UPDATE, DELETE).

2. WITH как цель DML (SQL Server)
SQL Server также позволяет использовать WITH-запрос как цель DML запроса, то есть создавать обновляемые представления.

3. Функции в WITH (Oracle)
Oracle, начиная с версии 12cR1 позволяет определять функции и процедуры в предложении WITH.

4. DML в WITH (PostgreSQL)
Начиная с версии 9.1 PostgreSQL поддерживает использование DML запросов внутри предложения WITH. А при использовании выражения RETURNING, предложение WITH возвращает данные в основной запрос. Таким образом, например, можно сделать запрос к только что вставленным в таблицу записям:

WITH added AS (
  INSERT INTO table1 …
  RETURNING *
)
SELECT * FROM added;

Источник: https://modern-sql.com/feature/with

День семьсот девяносто шестой. #ЗаметкиНаПолях #SQL
Ограничение Результатов с Помощью Оконных Функций
Оконные (window) аналитические функции давно присутствуют в SQL, однако многие до сих пор не умеют их применять.

Окно (window) - это набор строк таблицы, которые можно анализировать или применить к ним функцию. Строки должны быть как-то связаны. Иногда они связаны с конкретной строкой, т.е. строки могут быть выше или ниже друг друга или в пределах заданного диапазона. Либо связь может быть основана на отдельных группах данных в наборе.

Все оконные функции следуют определенному синтаксису.

<название функции>(<выражение>) OVER (
  <окно>
  <сортировка>
)

Сначала идёт название функции, а за ним следует предложение OVER. Оно определяет область действия окна, указывая набор строк, к которым будет применяться функция. Предложение OVER является обязательной частью оконной функции. Остальной синтаксис является необязательным, и зависит от желаемой области действия:
- Выражение PARTITION BY можно использовать для разделения набора данных на отдельные группы (аналогично GROUP BY).
- Выражение ORDER BY используется для упорядочивания строк в каждой группе.

В качестве оконных можно использовать простые агрегатные функции, вроде SUM(), COUNT(), AVG() и т.п. Но есть и несколько специальных.

Номера и ранг строк
Простейший вариант применения оконной функции – вывести номера строк результата (см. пример 1 на картинке ниже). Здесь «окном» является весь набор, а функция ROW_NUMBER() выводит номер строки по порядку.
Мы можем разделить набор данных на группы, используя PARTITION BY. В примере 2 на картинке ниже тот же набор разделён по полю name, а внутри каждой такой группы упорядочен по полю course.

Кроме этого две функции RANK() и DENSE_RANK() выводят ранг строки. Они отличаются от ROW_NUMBER() тем, что при равенстве результатов задают строкам одинаковые значения. При этом DENSE_RANK() продолжает нумерацию, например, 1,1,2,2,3. А RANK() использует следующий номер строки по порядку, оставляя разрывы в нумерации: 1,1,3,3,5. В примере 3 на картинке ниже приводится сравнение этих функций на том же наборе данных. Здесь использована сортировка всего набора по полю name, без разбиения на группы.

Заметьте, что выражение ORDER BY внутри оконной функции никак не связано с выражением ORDER BY всего запроса. Оно влияет только на результаты оконной функции. В предыдущем примере мы могли бы добавить ORDER BY ко всему запросу и изменить порядок строк в запросе, но результаты функций ROW_NUMBER(), RANK() и DENSE_RANK() в строках не изменились бы.

Первое и последнее значение
Эти функции позволяют вывести первое или последнее соответственно значение столбца в группе. В отличие от функций, описанных выше, для каждой группы будет выведена только одна строка. Например, если использовать LAST_VALUE(course) на наборе данных из примера 2(последний по алфавиту курс для каждого студента), то будет выведено 3 строки:
Jason Economics
Lucy Health Science
Martha Biology.

Предыдущие и последующие строки
Часто бывает полезно сравнивать строки с предыдущими или последующими, особенно если данные упорядочены (например, хронологически). Для этого используются функции LAG(), которая извлекает значения из предыдущих строк, и LEAD(), которая извлекает значения из последующих строк. В примере 4 на картинке ниже мы получаем значение суммы из предыдущего месяца с помощью функции LAG(sales,1). В функцию, помимо названия столбца, передаётся целое число, означающее количество строк, которые нужно отсчитать от текущей (в нашем случае 1). Для первой строки, очевидно, нет предыдущего значения, поэтому функция возвращает NULL.

Источник: https://app.pluralsight.com/library/courses/combining-filtering-data-postgresql

День 2749. #BestPractices #SQL
Как Оптимизировать SQL-запросы. Часть 1
Медленный SQL-запрос — один из самых простых способов испортить быстрое приложение. У вас может быть чистая архитектура, отличный кэш и мощный сервер — и всё равно страница может зависать из-за того, что один запрос сканирует миллион строк без индекса. Большинство успехов достигается за счёт одного и того же небольшого набора методов, применяемых снова и снова. Некоторые из них очевидны. Некоторые противоречат советам, которые вы, вероятно, уже слышали. Советы можно условно разделить на 6 групп.

Замечание: здесь мы рассматриваем PostgreSQL. Те же принципы применимы и к другим БД, хотя точный синтаксис может отличаться.

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

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

Создавайте индексы по столбцам, по которым вы чаще всего выполняете фильтрацию, соединение, сортировку и группировку — столбцам в WHERE, JOIN, ORDER BY и GROUP BY. Когда используется несколько столбцов одновременно, один составной индекс, охватывающий их, намного лучше, чем отдельные индексы по одному столбцу:

-- Составной индекс для частой фильтрации по статусу и дате
CREATE INDEX idx_orders_status_order_date
  ON orders (status, order_date);

Этот индекс ускоряет запросы, фильтрующие по статусу, а также по статусу и дате заказа. Порядок столбцов имеет значение: индекс по (status, order_date) помогает запросам, которые сначала фильтруют по статусу, но не запросам, которые фильтруют только по дате заказа.

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

-- Добавляем часто читаемые данные
CREATE INDEX idx_orders_status_order_date_covering
  ON orders (status, order_date)
  INCLUDE (customer_id, total_amount);

SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid'
 AND order_date >= DATE '2026-01-01';

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

2. Избегайте функций в WHERE
Обёртывание столбца в функцию — один из наиболее распространённых способов случайно отключить индекс. Когда вы вызываете функцию для столбца, БД должна вычислить значение функции для каждой строки, прежде чем сможет сравнить его, поэтому она не может использовать индекс для исходного столбца:

-- Плохо: функция по order_date отключает сканирование по индексу
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025;

Перепишите условие так, чтобы оно сравнивало исходный столбец с диапазоном:

-- Хорошо: диапазон по чистому значению столбца
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date <  '2026-01-01';

Оба запроса возвращают одни и те же строки, но только второй может использовать индекс по order_date.
То же правило применяется к LOWER(email), CAST(…) и арифметическим операциям по столбцу. Если вам часто нужно фильтровать по вычисляемому значению, создайте вместо этого функциональный индекс для этого конкретного выражения.

3. Избегайте символов подстановки в начале запроса LIKE
Шаблон LIKE, начинающийся с символа подстановки, не может использовать обычный индекс. БД считывает индекс слева направо, поэтому ей необходимо знать начало значения. Шаблон типа '%son' скрывает начало и заставляет выполнять полное сканирование:

-- Плохо: полное сканирование таблицы
SELECT * FROM customers
WHERE last_name LIKE '%son';

-- Хорошо: сканирование индекса по известному префиксу
SELECT * FROM customers
WHERE last_name LIKE 'Anders%';

Если вам действительно нужно выполнить contains-поиск в тексте, используйте полнотекстовый поиск или триграммный индекс (расширение pg_trgm в PostgreSQL), созданный специально для этой задачи.

4. Точное соответствие типов данных
Сравнение двух разных типов данных заставляет БД преобразовывать один из них, и это преобразование может незаметно отключить индекс.
Если столбец является целым числом, но вы сравниваете его со строкой, или соединяете int-ключ с bigint-ключом, БД добавляет неявное приведение типов — и индекс по исходному столбцу может быть пропущен. Сохраняйте одинаковые типы с обеих сторон каждого соединения и фильтрации:

CREATE TABLE logs (
  log_id     int PRIMARY KEY,
  event_date timestamptz NOT NULL,
  user_id    int NOT NULL
);

Определите logs.user_id так, чтобы он соответствовал типу users.id, и тогда соединение будет использовать индекс с обеих сторон.

Продолжение следует…

https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

День 2750. #BestPractices #SQL
Как оптимизировать SQL-запросы. Часть 2
Часть 1

Часть II. Извлекайте только необходимые данные
Быстрее всего обрабатываются данные, которые вы не читаете. Каждый столбец и каждая строка, которые вы извлекаете, требуют операций ввода-вывода на диске, памяти и сетевого времени. Следующие методы позволяют максимально сократить результирующий набор данных на ранней стадии.

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

-- Плохо: извлечение всех столбцов
SELECT * FROM customers;

-- Хорошо: только используемые столбцы
SELECT customer_id, first_name, last_name
FROM customers;

Явное указание столбцов также безопаснее. Ваш запрос не изменит свою форму и не сломается незаметно, когда кто-то добавит или изменит порядок столбцов.

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

SELECT o.order_id, o.total
FROM orders o
WHERE o.completed = true
  AND o.order_date >= '2026-01-01'
  AND o.order_date <  '2026-02-01';

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

3. Keyset-пагинация вместо OFFSET, когда возможно
OFFSET кажется простым способом реализации пагинации, но чем глубже вы углубляетесь, тем медленнее она становится. Чтобы вернуть OFFSET 100000, БД прочитает и отбросит 100 000 строк перед ней. Keyset-пагинация (также называемая поисковой пагинацией) запоминает последнее увиденное значение и переходит непосредственно за него:

-- Keyset-пагинация по индексированному столбцу
SELECT * FROM orders
WHERE order_id > 1000
ORDER BY order_id
LIMIT 10;

Стоимость каждой страницы одинакова, будь то страница 2 или страница 2000, поскольку индекс переходит непосредственно к order_id > 1000.

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

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

-- Читаем только записи, обновлённые с последнего запуска
SELECT * FROM records
WHERE modified_date > '2026-08-10 00:00';

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

Продолжение следует…

Источник: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

День 2751. #BestPractices #SQL
Как оптимизировать SQL-запросы. Часть 3
Часть 1
Часть 2

Часть III. Упростите операции соединения и подзапросы
Это место, где запросы становятся дорогостоящими и где скрываются самые большие возможности улучшения. Цель в том, чтобы заставить БД выполнять меньше работы и представить эту работу в форме, с которой она лучше всего справляется.

1. Уменьшайте сложность операций соединения
Каждая операция соединения — это дополнительная работа. Чем меньше таблиц базе нужно coединить, тем быстрее запрос. Распространённая ошибка — соединение таблицы, из которой вы фактически не читаете данные:

-- Плохо: соединение с suppliers, которая не используется 
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN suppliers  s ON p.supplier_id = s.supplier_id;

Удалите ненужное соединение:

SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id;

Прочитайте столбцы в SELECT и WHERE и удалите все соединения с таблицами, на которые нет ссылок.

2. Выбирайте правильный тип соединения
Тип соединения влияет как на результат, так и на стоимость. Используйте INNER JOIN, когда нужны совпадающие строки с обеих сторон, LEFT JOIN только тогда, когда действительно нужны и несовпадающие строки, и EXISTS, когда просто нужно узнать, существует ли совпадение:

-- Проверка существования: EXISTS останавливается на первом совпадении
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
 SELECT 1 FROM orders o
 WHERE o.customer_id = c.customer_id
   AND o.total > 100
);

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

3. Заменяйте избыточные подзапросы соединениями (JOIN) или CTE
Подзапрос выполняется для каждой строки, что может быть крайне неэффективно при работе с большим набором результатов. В данном случае подзапрос выполняется для каждого заказа, просто чтобы найти имя клиента:

-- Плохо: подзапрос на каждую строку 
SELECT o.order_id,
  (SELECT c.name FROM customers c
   WHERE c.customer_id = o.customer_id) AS customer_name
FROM orders o;

Простое соединение выполнит ту же работу один раз:

-- Хорошо: одно соединение
SELECT o.order_id, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Когда один и тот же подзапрос требуется несколько раз в одном запросе, преобразуйте его в общее табличное выражение (CTE) с помощью оператора WITH, чтобы он был написан один раз и его было легче читать. Вот пример медленного запроса:

-- Плохо: подзапрос повторяется 
SELECT
  c.customer_id,
  c.name,
  (
    SELECT SUM(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= DATE '2026-01-01'
  ) AS total_spent,
  (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= DATE '2026-01-01'
  ) AS order_count
FROM customers c;

А вот более быстрый запрос, использующий CTE:

-- Хорошо: считаем результат один раз и переиспользуем 
WITH customer_order_totals AS (
  SELECT
    customer_id,
    SUM(total_amount) AS total_spent,
    COUNT(*) AS order_count
  FROM orders
  WHERE order_date >= DATE '2026-01-01'
  GROUP BY customer_id
)
SELECT
  c.customer_id, c.name,
  COALESCE(t.total_spent, 0) AS total_spent,
  COALESCE(t.order_count, 0) AS order_count
FROM customers c
LEFT JOIN customer_order_totals t ON t.customer_id = c.customer_id;

4. EXISTS лучше, чем IN
EXISTS может привести к прерыванию обработки: он останавливается на первой совпадающей строке, в то время как IN может сначала сформировать полный список значений:

SELECT p.product_id, p.product_name
FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_details od
  WHERE od.product_id = p.product_id
);

Примечание: не следует воспринимать «EXISTS всегда лучше IN» как жёсткое правило. В современных версиях PostgreSQL запросы IN, EXISTS и даже некоторые соединения часто переписываются в один и тот же план выполнения, поэтому они могут работать идентично. Однако проблема всё ещё возникает с NOT IN в подзапросе, который может возвращать NULL — это приводит к неожиданным результатам и худшему плану выполнения, поэтому в этом случае предпочтительнее использовать NOT EXISTS. Как всегда, проверяйте план выполнения, а не гадайте.

Продолжение следует…

Источник: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

День 2752. #BestPractices #SQL
Как оптимизировать SQL-запросы. Части 4-5
Части 1, 2, 3

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

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

-- Сохраняем агрегированные данные в сводной таблице
CREATE TABLE sales_summary AS
SELECT product_id, SUM(quantity) AS total_sold
FROM order_details
GROUP BY product_id;

Теперь чтение представляет собой простой поиск, а не агрегацию в реальном времени по всей таблице order_details. Компромисс заключается в необходимости синхронизации сводной таблицы — её обновления по расписанию или при изменении исходных данных. Денормализацию следует проводить целенаправленно, для конкретных часто используемых запросов, а не повсеместно.

2. Использование материализованных представлений
Материализованное представление физически хранит результат запроса, поэтому чтение обращается к предварительно вычисленным строкам, а не пересчитывает их. Это вариант сводной таблицы, описанной выше:

-- Храним агрегированные данные
CREATE MATERIALIZED VIEW mv_total_sales AS
SELECT product_id, SUM(quantity) AS total_qty
FROM order_details
GROUP BY product_id;

-- Уникальный индекс позволяет представлению обновляться без блокирования чтения 
CREATE UNIQUE INDEX idx_mv_total_sales_product
    ON mv_total_sales (product_id);

-- Обновление по расписанию; читатели будут получать старые данные до завершения обновления 
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_total_sales;

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

Часть V. Повышение эффективности операций записи и транзакций
Медленная запись и длительные транзакции вызывают конфликты блокировок, заставляя остальные запросы ждать.

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

WITH batch AS (
 DELETE FROM source_table
 WHERE ctid IN (
   SELECT ctid
   FROM source_table
   WHERE processed = false
   LIMIT 1000
 )
 RETURNING col1, col2
)
INSERT INTO archive_table (col1, col2)
SELECT col1, col2 FROM batch;

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

2. Сокращайте транзакции
Транзакция удерживает блокировки до момента фиксации, и все другие запросы, которым нужны эти строки, должны ждать. Чем дольше транзакция остаётся открытой, тем больше конкуренции она создаёт. Держите транзакцию открытой только для операций записи, а медленные операции — вызовы API, файловый ввод-вывод, ресурсоёмкие вычисления — выполняйте вне её:

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

INSERT INTO transactions (account_id, amount)
VALUES (1, -100);

COMMIT;

Эта транзакция открывается, выполняет две связанные операции записи и немедленно фиксируется. Никогда не оставляйте транзакцию открытой, ожидая ввода пользователя или ответа по сети.

Окончание следует…

Источник: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

День 2753. #BestPractices #SQL
Как оптимизировать SQL-запросы. Часть 6
Части 1, 2, 3, 4-5

Часть VI. Пусть БД поможет вам: измерение, поддержка и доверие оптимизатору
Планировщик запросов умнее, чем принято считать. Ваша задача — предоставить ему достоверную информацию, а затем проверить результат.

1. Прочитайте план выполнения
План выполнения — это то, как база будет выполнять ваш запрос. Прежде чем что-либо оптимизировать, посмотрите на план. В PostgreSQL команда EXPLAIN ANALYZE выполняет запрос и сообщает план и что произошло на самом деле:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE total > 100;

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

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

ANALYZE orders;

PostgreSQL использует автоочистку (autovacuum) для поддержания актуальности статистики, но после большой загрузки данных или массового обновления стоит самостоятельно запустить ANALYZE. Движок может оптимизировать запрос настолько хорошо, насколько это позволяют его статистические данные. Хорошая статистика гораздо важнее точной формулировки запроса.

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

-- Только для диагностики: посмотреть, как выглядит план без последовательного сканирования
SET enable_seqscan = off;

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

4. Непрерывный мониторинг и настройка
Оптимизация — не разовая задача. Объём данных растёт, шаблоны доступа меняются, и вчерашний быстрый запрос становится сегодняшним узким местом. Отслеживайте, какие запросы на самом деле обходятся дороже всего. Расширение pg_stat_statements агрегирует статистику выполнения по всей вашей рабочей нагрузке:

-- Самые медленные запросы по среднему времени 
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

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

Источник: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices

День 2791. #ЗаметкиНаПолях #SQL
10 Редких Возможностей SQL, Которые Стоит Знать Каждому. Часть 1
Большинство разработчиков используют лишь 20% возможностей SQL. SELECT, JOIN, GROUP BY — и на этом останавливаются. Однако у SQL есть и «второй уровень» — функции, позволяющие превратить страницу кода приложения или три отдельных запроса в одну лаконичную и понятную инструкцию. При этом они не являются новыми или экзотическими: они уже доступны в используемой вами БД.

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

1. Обобщённые табличные выражения (CTE)
Сложный запрос, оформленный как единая инструкция, труден для чтения, а вносить в него изменения ещё сложнее. CTE позволяет разбить его на последовательные именованные этапы с помощью ключевого слова WITH. Каждый этап представляет собой временный именованный набор данных, который можно использовать в дальнейшем ходе запроса:

WITH recent_shipments AS (
  SELECT id, number, carrier, status, created_at
  FROM shipments
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
shipment_details AS (
  SELECT rs.number, rs.carrier, rs.status,
   COUNT(si.id) AS total_items,
   SUM(si.quantity) AS total_quantity
  FROM recent_shipments rs
  LEFT JOIN shipment_items si ON rs.id = si.shipment_id
  GROUP BY rs.number, rs.carrier, rs.status
)
SELECT number, carrier, status, total_items, total_quantity
FROM shipment_details ORDER BY total_quantity DESC;

Здесь 2 именованные части:
- recent_shipments выбирает данные об поставках за последние 30 дней;
- shipment_details использует этот результат, присоединяя к нему данные о товарных позициях и выполняя агрегацию (подсчёт количества и объёмов). В итоговом операторе SELECT обращение ко второму CTE происходит так же, как к обычной таблице.
В результате получается запрос, который читается сверху вниз, в отличие от подхода с использованием вложенных подзапросов, которые приходится разбирать изнутри наружу.
CTE также поддерживают рекурсию (с помощью конструкции WITH RECURSIVE); это позволяет работать с иерархическими данными, такими как организационные структуры или деревья категорий.
CTE можно использовать в операторах SELECT, INSERT, UPDATE или DELETE.

2. Оконные функции
Иногда требуется выполнить вычисления по связанным строкам, сохранив при этом в результирующем наборе каждую отдельную строку. Оператор GROUP BY сворачивает строки, оставляя по одной записи на группу. Оконная функция же выполняет вычисления по набору строк (так называемому «окну»), не объединяя при этом сами строки:

SELECT number, carrier, created_at,
 ROW_NUMBER() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_sequence,
 RANK() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_rank
FROM shipments;

SELECT number, status, created_at,
 LAG(status) OVER (ORDER BY created_at) AS previous_status,
 LEAD(carrier) OVER (ORDER BY created_at) AS next_carrier
FROM shipments;

Первый запрос ранжирует отправления каждого перевозчика по дате. ROW_NUMBER() присваивает уникальный порядковый номер в рамках группы (заданной через PARTITION BY carrier), а RANK() делает то же самое, но при совпадении значений присваивает им одинаковый ранг.
Во втором запросе используются LAG и LEAD для обращения к предыдущей и следующей строкам (в данном случае — для получения предыдущего статуса и следующего перевозчика) без выполнения самосоединения (self-join).
Оконные функции позволяют вычислять нарастающие итоги и скользящие средние, определять ранги, а также сравнивать данные в разных строках.
Они являются стандартом SQL и поддерживаются в PostgreSQL, SQL Server, Oracle и MySQL 8+.

3. LATERAL-Соединения
Обычное соединение соединяет две таблицы на основе определённого условия. LATERAL-соединение позволяет подзапросу, расположенному справа, обращаться к столбцам таблицы, расположенной слева, и выполняется для каждой строки отдельно, что идеально подходит для задач типа «выбрать N лучших записей в каждой группе»:

-- Для каждого перевозчика выбираем одну последнюю отправку
SELECT c.carrier, s.number, s.status, s.created_at
FROM (SELECT DISTINCT carrier FROM shipments) c
CROSS JOIN LATERAL (
  SELECT number, status, created_at
  FROM shipments WHERE carrier = c.carrier
  ORDER BY created_at DESC LIMIT 1
) s;

Для каждого конкретного перевозчика коррелирующий подзапрос выбирает одну — самую свежую — поставку (с помощью ORDER BY created_at DESC LIMIT 1).
Ключевой момент — условие WHERE carrier = c.carrier: внутренний запрос «видит» значение carrier из строки внешнего запроса, чего обычный подзапрос сделать не может.
Это наиболее элегантный способ получить «самую свежую запись в группе» или «топ-3 записи в категории» без использования оконных функций.
Примечание: в SQL Server аналогичная задача решается с помощью CROSS APPLY (или OUTER APPLY для аналога LEFT JOIN), а в PostgreSQL используются CROSS JOIN LATERAL и LEFT JOIN LATERAL.

Продолжение следует…

Источник: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know

День 2792. #ЗаметкиНаПолях #SQL
10 Редких Возможностей SQL, Которые Стоит Знать Каждому. Часть 2
1-3

4. GROUPING SETS, ROLLUP и CUBE
Для отчёта часто требуется получить сразу несколько уровней агрегации: итоговые значения по перевозчику и статусу, промежуточные итоги по перевозчику и общий итог. Наивный подход предполагает объединение нескольких запросов с помощью оператора UNION ALL. Конструкции GROUPING SETS, ROLLUP и CUBE позволяют получить все эти уровни в рамках одного запроса:

SELECT carrier, status,
 COUNT(*) AS shipment_count,
 SUM(si.quantity) AS total_quantity
FROM shipments s
LEFT JOIN shipment_items si ON s.id = si.shipment_id
GROUP BY GROUPING SETS (
 (carrier, status), -- по поставщику и статусу 
 (carrier),         -- подытог по поставщику
 (status),          -- подытог по статусу
 ()                 -- общий итог
);

SELECT carrier, status,
 DATE_TRUNC('month', created_at) AS month,
 COUNT(*) AS shipment_count
FROM shipments
GROUP BY ROLLUP (carrier, status,
  DATE_TRUNC('month', created_at)
);

Первый запрос формирует группировки, которые вам нужны: по перевозчику и статусу, только по перевозчику, только по статусу, а также пустую группу () для получения общего итога.
Оператор ROLLUP во втором запросе — это сокращённая запись для иерархических промежуточных итогов: сначала по перевозчику, затем по перевозчику и статусу, далее по перевозчику, статусу и месяцу — и наконец общий итог.
Оператор CUBE создает все возможные комбинации столбцов.
Один запрос заменяет 4, а БД вычисляет уровни за один проход, вместо того чтобы многократно сканировать таблицу.
Эти средства являются частью стандарта SQL и поддерживаются в PostgreSQL, SQL Server и Oracle.

5. Предложение FILTER в агрегатных функциях
Часто возникает необходимость подсчитать количество или сумму только для тех строк, которые удовлетворяют определённому условию, и вывести эти результаты рядом друг с другом. Предложение FILTER применяет условие к конкретной агрегатной функции, благодаря чему каждая из них обрабатывает своё подмножество данных — в рамках одной строки и за один проход по данным:

SELECT carrier,
 COUNT(*) AS total_shipments,
 COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_count,
 COUNT(*) FILTER (WHERE status = 'in_transit') AS in_transit_count,
 COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
 SUM(si.quantity) FILTER (WHERE status = 'delivered') AS delivered_quantity,
 SUM(si.quantity) FILTER (WHERE status = 'pending') AS pending_quantity
FROM shipments s
LEFT JOIN shipment_items si ON s.id = si.shipment_id
GROUP BY carrier;

Каждое выражение COUNT(*) FILTER (WHERE …) подсчитывает только соответствующие условию строки, благодаря чему вы получаете количество отправлений со статусами «доставлено», «в пути» и «в ожидании» в виде отдельных столбцов для каждого перевозчика.
Запись COUNT(*) FILTER (WHERE status = 'delivered') читается легче, чем старый приём с использованием CASE: SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END).
Назначение конструкции FILTER гораздо более очевидно.
Примечание: FILTER поддерживается в PostgreSQL. В SQL Server и MySQL такой возможности нет — там приходится использовать CASE внутри агрегатной функции, например: COUNT(CASE WHEN status = 'delivered' THEN 1 END).

6. UPSERT (INSERT … ON CONFLICT)
Вставка строки, если она новая, и её обновление, если она уже существует — распространённая задача, для решения которой обычно требуются SELECT, условие и две ветви выполнения кода. Операция UPSERT позволяет выполнить это одной атомарной командой, исключая риск возникновения состояния гонки между проверкой и записью:

INSERT INTO shipments (id, number, order_id, address_street, address_city, address_zip, carrier, receiver_email, status, created_at, updated_at)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'SH-2024-001', 'ORD-2024-001', '123 Main St', 'New York', '10001', 'FedEx', 'customer@example.com', 'pending', NOW(), NOW())
ON CONFLICT (number) DO UPDATE
SET
 carrier = EXCLUDED.carrier,
 status = EXCLUDED.status,
 updated_at = GREATEST(shipments.updated_at, EXCLUDED.updated_at);

Команда INSERT … ON CONFLICT (number) DO UPDATE пытается выполнить вставку; если строка с таким номером уже существует, вместо этого выполняется обновление.
Псевдотаблица EXCLUDED содержит значения, которые вы пытались вставить, поэтому запись carrier = EXCLUDED.carrier означает «использовать нового перевозчика».
Выражение GREATEST(shipments.updated_at, EXCLUDED.updated_at) позволяет сохранить более позднюю из двух временных меток.
Одна команда, никаких дублирующихся строк и никаких проблем с состоянием гонки при одновременном выполнении запросов разными клиентами.
Примечание: это синтаксис PostgreSQL. В стандарте SQL (и в таких СУБД, как SQL Server или Oracle) используется оператор MERGE, а в MySQL — INSERT … ON DUPLICATE KEY UPDATE.
Замечание: поле, по которому будет отслеживаться конфликт (number) должно иметь ограничение уникальности.

7. Поддержка JSON
Иногда требуется хранить гибкие, полуструктурированные данные — например, событие, тело веб-хука или блок настроек. PostgreSQL поддерживает JSON на уровне ядра (тип JSONB) и позволяет выполнять запросы к содержимому таких полей; благодаря этому вам не нужна отдельная документоориентированная БД для редких случаев использования JSON. Также отпадает необходимость хранить JSON в виде обычных строк и обрабатывать их на стороне бэкенда, теряя при этом все преимущества индексации:

CREATE TABLE ship_events (
 id SERIAL PRIMARY KEY,
 payload JSONB NOT NULL
);

-- Пример данных
INSERT INTO ship_events (payload) VALUES
('{"type":"click","coordinates":[{"x":10,"y":20},{"x":15,"y":25}]}'),
('{"type":"hover","coordinates":[{"x":5,"y":30}]}'),
('{"type":"scroll","coordinates":[{"x":0,"y":100},{"x":0,"y":200},{"x":0,"y":300}]}');

-- Выбираем поля JSON
SELECT
 payload ->> 'type' AS event_type,
 payload -> 'coordinates' -> 0 ->> 'x' AS first_x,
 payload -> 'coordinates' -> 0 ->> 'y' AS first_y
FROM ship_events;

В таблице событий данные хранятся в формате JSONB. Запрос обращается к ним следующим образом: оператор ->> извлекает значение как текст, а оператор -> — вложенный JSON-объект или элемент массива; так, выражение payload -> 'coordinates' -> 0 ->> 'x' позволяет получить координату x первого элемента.
Данные JSONB хранятся в разобранном бинарном виде и поддерживают индексацию, что позволяет выполнять фильтрацию и извлечение информации без сканирования документов целиком.
Примечание: в SQL Server для работы с JSON используются функции JSON_VALUE и OPENJSON, а стандарт SQL предусматривает функцию JSON_TABLE (доступную в Oracle, MySQL и PostgreSQL 17+), которая преобразует JSON-массив непосредственно в строки реляционной таблицы.

Окончание следует…

Источник: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know

День 2793. #ЗаметкиНаПолях #SQL
10 Редких Возможностей SQL, Которые Стоит Знать Каждому. Часть 3
1-3
4-7

8. Вычисляемые (генерируемые) столбцы
Если значение столбца всегда формируется на основе данных из других столбцов, его вычисление в коде приложения чревато ошибками, поскольку формулу приходится учитывать в каждом месте, где он используется. Использование генерируемого столбца позволяет перенести эту формулу в определение таблицы, благодаря чему БД вычисляет и сохраняет значение автоматически.

CREATE TABLE shipments.shipping_costs (
  id SERIAL PRIMARY KEY,
  shipment_id UUID NOT NULL,
  base_rate DECIMAL(10,2) NOT NULL,
  weight_kg DECIMAL(8,2) NOT NULL,
  distance_km DECIMAL(10,2) NOT NULL,
  fuel_surcharge_rate DECIMAL(5,4) NOT NULL DEFAULT 0.15,

  -- Вычисляемые столбцы
  weight_cost DECIMAL(10,2) GENERATED ALWAYS AS (weight_kg * 2.50) STORED,
  distance_cost DECIMAL(10,2) GENERATED ALWAYS AS (distance_km * 0.85) STORED,
  fuel_surcharge DECIMAL(10,2) GENERATED ALWAYS AS (base_rate * fuel_surcharge_rate) STORED,
  total_cost DECIMAL(10,2) GENERATED ALWAYS AS (
    base_rate
    + (weight_kg * 2.50)
    + (distance_km * 0.85)
    + (base_rate * fuel_surcharge_rate)
  ) STORED,

  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

 FOREIGN KEY (shipment_id) REFERENCES shipments(id)
);

Значение каждого столбца, определённого как GENERATED ALWAYS AS (…) STORED, вычисляется на основе других столбцов при каждой вставке или обновлении строки. Столбец total_cost суммирует базовый тариф, стоимость с учётом веса, стоимость с учётом расстояния и топливный сбор; при этом невозможно забыть пересчитать его значение, так как в этот столбец нельзя записать данные напрямую.
Ключевое слово STORED означает, что значение сохраняется физически (и может быть проиндексировано), а не вычисляется заново при каждом чтении.
Примечание: в SQL Server такие столбцы называются вычисляемыми (computed) и описываются как total_cost AS (…), а для сохранения значения используется ключевое слово PERSISTED. В MySQL для этого применяется тот же синтаксис GENERATED ALWAYS AS, что и в PostgreSQL.

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

-- Простой пример выборки
SELECT carrier, COUNT(*)
FROM shipments TABLESAMPLE SYSTEM (5)
GROUP BY carrier;

-- Случайная выборка с заданным посевом для повторяемости результатов
SELECT * FROM shipments
TABLESAMPLE BERNOULLI (10)
REPEATABLE (12345);

-- Пример с WHERE
SELECT * FROM shipments
TABLESAMPLE BERNOULLI (20)
WHERE status = 'pending';

-- Пример с соединением
SELECT s.number, s.carrier, sc.total_cost
FROM shipments s
TABLESAMPLE SYSTEM (10)
JOIN shipping_costs sc ON s.id = sc.shipment_id;

Метод TABLESAMPLE SYSTEM (5) выбирает примерно 5% данных таблицы путём считывания случайных страниц: это работает быстро, но выборка осуществляется на уровне блоков. Метод BERNOULLI (10) отбирает около 10% строк по отдельности; такой подход обеспечивает более равномерную с точки зрения статистики выборку, но выполняется медленнее.
Параметр REPEATABLE (12345) фиксирует начальное значение генератора случайных чисел, благодаря чему при каждом запуске получается одна и та же выборка, что полезно для воспроизводимых тестов.
Этот механизм предназначен для быстрой проверки, профилирования и тестирования запросов к большим таблицам без затрат ресурсов на полное сканирование.
Примечание: TABLESAMPLE входит в стандарт SQL; PostgreSQL поддерживает методы SYSTEM и BERNOULLI, а SQL Server также поддерживает TABLESAMPLE SYSTEM.

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

-- Частичный индекс для отправок «в пути»/«в ожидании»
CREATE INDEX idx_shipments_pending_carrier
ON shipments (carrier, created_at)
WHERE status IN ('pending', 'in_transit');

-- Частичный индекс для поставщика
CREATE INDEX idx_shipments_fedex_status
ON shipments (status, updated_at)
WHERE carrier = 'FedEx';

-- Использует idx_shipments_pending_carrier
SELECT number, carrier, created_at
FROM shipments
WHERE status = 'pending'
  AND carrier = 'FedEx'
ORDER BY created_at DESC;

-- Использует idx_shipments_fedex_status
SELECT number, status, updated_at
FROM shipments
WHERE carrier = 'FedEx'
  AND status IN ('delivered', 'pending')
ORDER BY updated_at DESC;

Запросы, соответствующие условиям, используют нужный индекс; поскольку каждый индекс содержит лишь часть данных таблицы, операции поиска и обслуживания выполняются быстрее.
Частичные индексы особенно эффективны для работы с «горячими» подмножествами данных — например, с активными записями, данными с фильтром is_deleted = false (мягкое удаление) или записями с определённым статусом, к которым часто обращаются. В таких случаях большинство запросов затрагивает лишь небольшую, предсказуемую часть таблицы.

Примечание: в SQL Server такие индексы называются «фильтруемыми» (filtered indexes); для их создания используется тот же синтаксис CREATE INDEX … WHERE.

Источник: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know