Твой индекс в MySQL не медленный, он просто фрагментирован :)

Базы данных вроде MySQL, которые хранят данные в B+-деревьях, страдают от фрагментации индексов, и это серьезно влияет на производительность. Сейчас объясню.

Фрагментация индекса возникает, когда страницы индекса B+-дерева содержат значительное количество свободного пространства вместо плотного размещения данных. Но почему так происходит?

В MySQL движок InnoDB хранит данные в кластеризованных индексах (организованных по первичному ключу). Когда кластеризованный индекс фрагментируется из-за случайных вставок по первичному ключу (например, UUID), производительность чтения данных ухудшается.

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

Фрагментация индекса напрямую влияет на производительность запросов и использование памяти. Когда индексы фрагментированы, базе данных приходится читать больше страниц с диска для выполнения того же запроса, что увеличивает количество I/O-операций и снижает пропускную способность.

Кстати, при последовательных вставках фрагментация минимальна, поскольку InnoDB просто создаёт новые страницы без разделения существующих и размещает их вдоль крайнего правого пути дерева. Это помогает сохранять оптимальную плотность страниц.

Управлять фрагментацией индексов можно, настроив параметр innodb_fill_factor и/или выполнив следующий запрос:

ALTER TABLE tbl_name FORCE;

Надеюсь, было интересно :)

👉 @SQLPortal

Понимание IOPS в Postgres: зачем они вообще нужны, даже когда данные в кеше: читать

👉 @SQLPortal

Если номер телефона (`phone`) равен `NULL`, а `backup_phone` равен нулю,

что вернёт этот запрос?

SELECT
  COALESCE(phone, backup_phone, 'Not provided') AS primary_contact
FROM customers;

A) NULL
B) ноль (0)
C) 'Not provided'
D) ошибка

👉 @SQLPortal

Совет по Data Engineering: различайте трансформации и действия в Apache Spark

При работе с Apache Spark понимание разницы между transformations и actions имеет ключевое значение.

Transformations (трансформации) — это операции над DataFrame или Dataset, которые создают новый распределённый набор данных на основе существующего.
Обычно они ленивые (lazy) — то есть выполняются не сразу, а лишь формируют логический план выполнения.

Примеры трансформаций из кода:

👉spark.read.option()
👉 withColumn()
👉 filter()
👉 groupBy()
👉 count()

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

Actions (действия), в свою очередь, запускают выполнение трансформаций и возвращают результат в driver-программу или выводят его.

Примеры действий:

👉 show(5)
👉 collect()

👉 @SQLPortal

Много NULL-ов или неактивных строк в PostgreSQL? Настройте частичный индекс (partial index), чтобы индексировались только те данные, по которым вы выполняете запросы. Такие индексы меньше по размеру и уменьшают нагрузку на операции записи.

👉 @SQLPortal

Причины, почему SELECT * плохо влияет на производительность SQL: читать

Старый блог (с примерами, специфичными для Oracle), где приводится целый ряд причин. Дело не только в сетевом трафике и выполнении плана SQL-запроса, но также в обработке данных на стороне клиента и использовании памяти.

👉 @SQLPortal

Никогда не предполагайте, что SQL вычисляет условия слева направо

Если вы пишете код так:

SELECT * FROM products 
WHERE price > 100 
 AND (total_sales / price) > 10;

Можно предположить, что запрос будет вычисляться слева направо, и это предотвратит ошибку деления на ноль. Однако это не всегда так. Большинство крупных SQL-баз данных НЕ гарантируют short-circuit-вычисление условий AND в WHERE. И даже если в простых случаях кажется, что short-circuit работает, оптимизатор часто перестраивает или вычисляет выражения вне исходного порядка по соображениям производительности.

Это означает, что база данных может попытаться вычислить:

total_sales / price

раньше, чем:

price > 100.

То есть, если какое-либо значение в price равно нулю, может возникнуть ошибка деления на ноль.

Таким образом, даже несмотря на то, что логически условие price > 100 должно предотвращать деление на ноль, фактический порядок выполнения не гарантирует этого.

Чтобы обезопасить себя, можно использовать оператор NULLIF в WHERE:

SELECT *
FROM products
WHERE price > 100
AND (total_sales / NULLIF(price, 0)) > 10;

Никогда не используйте деление в WHERE, если знаменатель не защищён через NULLIF(), не переписан в умноженную форму (например, numerator > denominator * constant) или не обёрнут в CASE.

👉 @SQLPortal

Представили pg_duckpipe: CDC в реальном времени для вашего Lakehouse

TL;DR:
pg_duckpipe — это новое расширение для PostgreSQL, которое непрерывно синхронизирует обычные heap-таблицы с колонночными таблицами в DuckLake, используя CDC на основе WAL.

Чтобы запустить синхронизацию, достаточно одного SQL-вызова — без внешней инфраструктуры.

https://pgducklake.select/blog/introducing-pg-duckpipe/

👉 @SQLPortal

Вам нужно выполнить запрос по иерархии? Рекурсивные CTE могут быть очень полезны. Они начинают с якорной точки (anchor point) и проходят через CTE до конца.

Рекурсивный CTE состоит из двух частей:

1/ Якорный запрос (Anchor Query) — это верхний уровень / начальная точка.

2/ Рекурсивный запрос (Recursive Query) — он ссылается на CTE и продолжает выполняться, пока не будет выполнено условие остановки.

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

WITH RECURSIVE EmployeeHierarchy AS (
-- Якорь / начальная точка
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 1

UNION ALL

-- Рекурсивный случай: найти элементы, которые относятся к предыдущему уровню
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)

SELECT * FROM EmployeeHierarchy;

👉 @SQLPortal

Как мы даём каждому пользователю доступ к SQL в общем кластере ClickHouse

Как позволить пользователям писать произвольный SQL для общей мультиарендной (multi-tenant) аналитической базы данных — не раскрывая данные других арендаторов и не давая «плохому» запросу уронить кластер?

Именно эту задачу нам нужно было решить для Query & Dashboards. Решение — TRQL (Trigger Query Language), язык в стиле SQL, который компилируется в безопасные, изолированные по арендаторам запросы для ClickHouse. Пользователи пишут привычный SQL, а TRQL берёт на себя безопасность, абстракцию и трансляцию.

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

👉 @SQLPortal

Знаете ли вы, что EXPLAIN может выводить результат в формате JSON? Это удобно для использования специализированных инструментов для просмотра планов выполнения или для их самостоятельного анализа.

EXPLAIN (ANALYZE, FORMAT JSON, BUFFERS) SELECT * FROM employees;

👉 @SQLPortal

Я поддерживаю это предложение
🤣🤣🤣🤣🤣🤣🤣

👉 @SQLPortal

В PostgreSQL 18 появились виртуальные вычисляемые столбцы (virtual generated columns). Generated columns позволяют создавать новый столбец на основе других данных. Такие столбцы не хранятся на диске и вычисляются на лету.

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

CREATE TABLE products (
  id serial PRIMARY KEY,
  price numeric,
  tax_rate numeric DEFAULT 0.099995,
  total_price numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);

👉 @SQLPortal

Решение медленных запросов часто сводится к добавлению ещё одного индекса. Но переиндексация — вполне реальная проблема, и есть менее известная фича в Postgres получше: CREATE STATISTICS 💡

Я часто ловил себя на том, что пытаюсь починить медленный запрос или плохой execution plan, добавляя ещё один, более специфичный индекс. В большинстве случаев это работало, но… чёрт, это было дорого. Не только по дисковому пространству, но и по стоимости обновления индекса на пути записи (write path).

Малоизвестная возможность в Postgres — это расширенная статистика (extended statistics). Это то, о чём должен знать каждый, кто работает с PG. И я сам слишком долго был по другую сторону — жаль, что не узнал об этом раньше 🫣

PostgreSQL уже из коробки имеет немало информации о ваших таблицах. Однако эти данные в основном по отдельным колонкам (есть исключения) и не отражают взаимосвязи между двумя и более колонками.

CREATE STATISTICS (или extended statistics) позволяет PG собирать дополнительную многоколоночную статистику, чтобы учитывать зависимости между колонками, понимать вероятные, маловероятные и невозможные комбинации значений, а также улучшать оценку количества строк (row estimates). Причём улучшения могут быть больше чем на порядок

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

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

И использовать это очень просто:

CREATE STATISTICS my_stats (mcv, dependencies)
ON region, plan_tier, billing_status
FROM tenants;

Требование по хранению — около 2 KiB. Аналогичный индекс занял бы ~30 MiB для того же набора данных. И execution plans при этом, как правило, получаются лучше.

Полный разбор — в этом блоге, там же есть бенчмарк (с сгенерированными execution plan’ами и отчётами) на GitHub. 👇

- Benchmark code + reports
- Blog post

👉 @SQLPortal

Концепции SQL, которые действительно нужно знать:

• CRUD → SELECT, INSERT, UPDATE, DELETE
• Ключи → PRIMARY KEY, FOREIGN KEY
• Ограничения → NOT NULL, UNIQUE, CHECK, DEFAULT
• Джоины → INNER JOIN, LEFT JOIN, RIGHT JOIN
• Агрегация → COUNT, SUM, AVG, MIN, MAX
• Группировка → GROUP BY, HAVING
• Фильтрация → WHERE, BETWEEN, IN, LIKE
• Сортировка → ORDER BY
• Подзапросы → SELECT (SELECT …)
• Индексы → CREATE INDEX
• Представления → CREATE VIEW
• Транзакции → BEGIN, COMMIT, ROLLBACK
• Пагинация → LIMIT, OFFSET
• Оптимизация → EXPLAIN

👉 @SQLPortal