💻ACID

Когда речь идёт о базах данных, может всплыть сочетание «Требования ACID».
Давайте освежим в памяти, что же это значит

Требования ACID — набор требований, которые обеспечивают сохранность ваших данных.

🔅Atomicity — Атомарность
Атомарность гарантирует, что каждая транзакция будет выполнена полностью или не будет выполнена совсем. Не допускаются промежуточные состояния.

🔄Consistency — Согласованность
Это свойство вытекает из предыдущего. Благодаря тому, что транзакция не допускает промежуточных результатов, база остается консистентной. Есть такое определение транзакции: «Упорядоченное множество операций, переводящих базу данных из одного согласованного состояния в другое». То есть до выполнения операции и после база остается консистентной (согласованной)

⬇️Isolation — Изолированность
Во время выполнения транзакции параллельные транзакции не должны оказывать влияния на её результат.

🔓Durability — Надёжность
Если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя. Обесточилась система, произошел сбой в оборудовании? На выполненную транзакцию это не повлияет.

🔗 Полезная статья на Хабре в тему

@sqlhub

💻Про NOT NULL и NULL в SQL

☑️IS NOT NULL + OR
Иногда внутри SQL-запроса возникает необходимость проверить наличие/отсутствие NULL-значения в некотором наборе полей:

a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL

Но то же самое по смыслу условие можно записать гораздо короче с помощью функции coalesce:

coalesce(a, b, c) IS NOT NULL

☑️IS NOT NULL + AND
Немного изменим условие - заменим OR на AND:

a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL

Тут нам поможет ROW-конструктор:

(a, b, c) IS NOT NULL

☑️IS NULL + AND
Теперь заменим IS NOT NULL на IS NULL:

a IS NULL AND b IS NULL AND c IS NULL

Тут достаточно вспомнить из логики, что (A and B) эквивалентно not(not A or not B), а (A or B) - not(not A and not B), поэтому легко применяем not к варианту IS NOT NULL + OR:

coalesce(a, b, c) IS NULL

Или с помощью ROW-конструктора:

(a, b, c) IS NULL

☑️IS NULL + OR
Остался последний вариант:

a IS NULL OR b IS NULL OR c IS NULL

Тут мы можем "обратить" вариант IS NOT NULL + AND:

NOT (a, b, c) IS NOT NULL

Заметьте, что пара NOT тут "не сокращается", иначе получился бы предыдущий вариант.

Подробнее об особенностях работы со сложными выражениями тут — "PostgreSQL Antipatterns: вычисление условий в SQL". И ещё годная статья — "«Ленивый сахар» PostgreSQL"

@sqlhub

💻 Делаем SQL-запросы чище

Периодически в коде запросов и "заточенных" под них индексов наблюдаю примерно подобные куски:

coalesce("Фамилия", '') || ' ' || coalesce("Имя", '') || ' ' || coalesce("Отчество", '')

Понятно, что тут хотели обезопасить себя от заполненности любого из полей NULL-значением, чтобы случайно вся строка не заNULL'илась.

Правда, тут возникают некоторые артефакты в виде "висящих пробелов" типа ' Иван Иванович' или 'Иванов Иван '.

Но ведь есть решение изящнее и проще - функция concat_ws:

concat_ws(' ', "Фамилия", "Имя", "Отчество")

RTFM!

@sqlhub

💻Методы оптимизации SQL-запросов

1️⃣Использование EXPLAIN для анализа запросов

2️⃣Продвинутые стратегии индексирования
├── Пересмотр покрывающих индексов
├── Частичные индексы для отфильтрованных данных
└── Стратегии индексирования для соединений

3️⃣Переписывание и оптимизация запросов
├── Пересмотр подзапросов к JOIN'ам
├── Общие табличные выражения (CTE) и рекурсивные запросы
└── Ограничение наборов результатов с помощью OFFSET-FETCH

4️⃣Денормализация данных для повышения производительности
├── Выбор кандидатов на денормализацию
├── Создание денормализованных таблиц
└── Баланс между согласованностью данных и производительностью

5️⃣Использование временных таблиц для оптимизации запросов
├── Временные таблицы для промежуточных результатов
├── Создание и наполнение временных таблиц
└── Временные таблицы и агрегации

6️⃣Оптимизация подзапросов и агрегаций
├── Оптимизация подзапросов
└── Оптимизация агрегаций

7️⃣Избегайте курсоров и циклов для повышения производительности
├── Операции на основе множеств
├── Пакетная обработка
└── Оптимизация циклов с помощью операций на основе множеств

Примеры некоторых из этих приёмов

Пересмотр подзапросов к JOIN'ам.
Оригинальный подзапрос:

SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE region = 'West');

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

SELECT e.* FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.region = 'West';

Общие табличные выражения (CTE) предоставляют удобный способ упростить сложные запросы и улучшить их читаемость:

WITH RecursiveCTE AS (
  SELECT employee_id, first_name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.first_name, e.manager_id, rc.depth + 1
  FROM employees e
  INNER JOIN RecursiveCTE rc ON e.manager_id = rc.employee_id
)
SELECT * FROM RecursiveCTE;

➖ Более подробно можно прочитать тут

@sqlhub

💻Проверка наличия записи в SQL: альтернатива SELECT COUNT(*)

Для того чтобы проверить наличие записи, используйте оператор EXISTS:

IF EXISTS (SELECT 1 FROM ваша_таблица WHERE ваш_столбец = ваше_значение)
  PRINT 'Запись существует';
ELSE
  PRINT 'Запись не найдена';

Замените ваша_таблица, ваш_столбец и ваше_значение релевантными данными. Этот способ является эффективным для поиска записей.

*️⃣Использование SELECT COUNT(*) для проверки наличия записи может показаться удобным, однако это не всегда эффективный подход, особенно при работе с обширными базами данных.
В MS SQL Server целесообразно использовать SELECT TOP 1 для сохранения ресурсов:

IF EXISTS (SELECT TOP 1 1 FROM ваша_таблица WHERE ваш_столбец = ваше_значение)
  PRINT 'Запись существует';

В MySQL и PostgreSQL применяйте LIMIT 1 для ограничения выборки:

SELECT 1 FROM ваша_таблица WHERE ваш_столбец = ваше_значение LIMIT 1;

Такие запросы быстро останавливают выполнение, как только найдено первое соответствие.

*️⃣Миф о SELECT COUNT(*). Несмотря на то что многие СУБД оптимизируют SELECT COUNT(*), в работе не все из них показывают одинаковую эффективность. В случае сомнений используйте EXISTS или LIMIT/TOP.

@sqlhub

💻Немного о недавнем обновлении PostgreSQL 16

PostgreSQL 16 вносит немало улучшений в планировщик запросов и позволяет выполнять многие SQL-запросы быстрее, чем в предыдущих версиях PostgreSQL.

Вот некоторые из изменений:
⏩Разрешена инкрементальная сортировка в большем количестве случаев, включая DISTINCT

⏩Добавлена возможность для агрегатов, имеющих ORDER BY или DISTINCT, использовать предварительно отсортированные данные

⏩Разрешена мемоизация в UNION ALL

⏩Разрешено выполнять anti-join с ненулевым входом в качестве внутреннего отношения

⏩Разрешено распараллеливание FULL и RIGHT OUTER хэш-соединения

⏩Разрешено оконным функциям использовать более быстрый режим ROWS, когда режим RANGE активен, но не нужен

⏩Оптимизация постоянно увеличивающихся оконных функций ntile(), cume_dist() и percent_rank()

⏩Разрешено удаление left join и unique joins в секционированных таблицах

⏩Можно использовать Limit вместо Unique для реализации DISTINCT, когда это возможно

⏩Ослаблены слишком строгие правила в select_outer_pathkeys_for_merge()

📎 Статья с подробным описанием улучшений

@sqlhub

💻BYTE и CHAR в Oracle: разница, хранение, кодировка

Если в двух словах
Выбор между BYTE и CHAR зависит от кодировки и объема данных, которые вы собираетесь представлять в байтах.
⏩BYTE фиксирует объем данных в байтах и идеален для однобайтовых кодировок, таких как ASCII где каждому символу соответствует один байт. Так, VARCHAR2(10 BYTE) вмещает любую комбинацию из десяти байтов.
⏩CHAR же определяет количество символов, не учитывая их объем в байтах; выбрав VARCHAR2(10 CHAR), вы гарантируете пространство на 10 символов, независимо от их размера в байтах. Для иллюстрации вот простой SQL-пример:

-- BYTE: сохраняет байты
CREATE TABLE bytewise (name VARCHAR2(10 BYTE));

-- CHAR: распределяет пространство с учетом количества символов
CREATE TABLE charwise (name VARCHAR2(10 CHAR));

Если BYTE обеспечивает компактность данных, то CHAR гарантирует гибкость, позволяя сохранять символы различной ширины. Ваш выбор зависит от типов символов, которые вы планируете использовать.


Детали при работе с UTF-8
Важно понимать последствия выбора типа данных при работе с многобайтовыми символами кодировки, такой как UTF-8, где один символ может занимать до 4 байтов.
⏩Если вы опираетесь на BYTE, это может привести к проблемам в системах, поддерживающих много языков.
⏩Для предотвращения потери или обрезки данных CHAR будет более надежной опцией, так как он обеспечивает достаточное пространство для любого символа, независимо от его веса.


Критерии для выбора между BYTE и CHAR:
⏩Однородность данных: Если данные подчиняются стандартному размеру в байтах, особенно в случаях, когда важно экономить место, BYTE будет наилучшим выбором.
⏩Многоязычная поддержка: CHAR выигрывает, когда важна поддержка множества языков, это помогает предотвратить проблемы с несоответствием размера хранения.
⏩Производительность: BYTE может быть быстрее по сравнению с CHAR из-за обработки меньшего объема данных, что делает его подходящим для приложений с высокими требованиями к производительности.

📎 Подробнее про BYTE и CHAR можно прочитать тут

@sqlhub

💻Склонение месяцев в SQL

Вроде мелочь, а вполне-таки реальный кейс
В общем, необходимо вносить в договор дату в формате: "30 мая 2099 г."
Эта дата хранится в БД, ее нужно просто подгрузить и нормально отформатировать.
Для этого можно обращаться к месяцу с помощью: LOWER(DATENAME(month, @Tdate)), однако в таком случае получается месяц в именительном падеже - "май".

И тут полезно вспомнить, что форматирование даты с правильными склонениями месяца работает из коробки:

SELECT FORMAT(GETDATE(), 'dd MMMM yyyy', 'ru-RU')

-- 30 мая 2099

@sqlhub

💻SQL в качестве API

📌Интересная статья, которая позволит посмотреть на SQL с другой стороны
Статья обсуждает использование SQL в API для обработки сложных запросов, в результате автор приходит к выводу, что применение SQL в API может быть безопасным и удобным.

📎 Статья

@sqlhub

💻 Зачем нужен SELECT ... FOR UPDATE?

▶️Итак, ответ. Пока у вас транзакции следуют строго одна за другой - у вас всё хорошо и нет проблем. Кроме производительности. А чтобы улучшить производительность — необходимо разрешить транзакции выполнять параллельно. И вот тут начинается богатый и поразительный мир concurrently control. При том, не только в БД, а везде где хоть что-то выполняется параллельно.

Допустим есть пользователь, у него есть 100 денег на счету. Пользователь может их тратить, вы проверяете баланс SELECT balance ..., затем обновляете баланс при покупке UPDATE ... SET balance = ? WHERE .... И вот в счастливый день как-то так вышло, что приходят сразу 2 запроса на покупки для этого пользователя. Одна на 50 денег, вторая на 70. Одна из них должна быть отклонена, т.к. денег недостаточно. Но в результате получается что обе покупки прошли и у вас проблема, вы продали то, что не надо было. И это даже не видно по балансу пользователя. Как же?

Это типичный race condition, обе транзакции сначала данные читают, потом локально что-то делают, потом что-то пишут.
⏩читать им никто не мешает, потому обе транзакции прочитали что у пользователя 100 денег
⏩обе транзакции закономерно решили что денег достаточно
⏩обе транзакции обновили баланс пользователя
При конкурентном доступе к ресурсу подрались только на последнем шаге, транзакция которая начала обновлять данные позже сначала подождала завершение первой транзакции. А затем банально перезаписала баланс на тот который считала правильным сама — lost update аномалия.

▶️Вот как раз для того чтобы предупредить СУБД о том, что мы планируем с данными что-то делать, а потому нам надо сериализовать транзакции иначе, и существует FOR SHARE, FOR UPDATE дополнения. Потому они кстати и задокументированы в разделе Explicit Locking

📎 Подробнее прочитать можно тут

@sqlhub

💻 Cheatsheet SQL

Шпаргалок по SQL много не бывает, так что держите, полезно освежить перед собеседованием

Здесь освещаются самые основные операторы, такие как JOIN, IN, LIKE, BETWEEN, ORDER BY, а также много всего ещё
Пользуйтесь)

@sqlhub

💻AlPort опубликовал наиболее полный рейтинг генеративных технологий, который учитывает локальные особенности продуктов

В рейтинг попали самые перспективные разработки в сфере генеративного искусственного интеллекта по ключевым категориям. Туда попали сразу две нейросетки от Яндекса — текстовая YandexGPT и мультимодальная YandexART. Также Яндекс вместе с Stability AI, Open AI, Google, Microsoft, Tencent и Baidu стал одной из 11 компаний со всего мира, которые разрабатывают более одного типа GenAI-моделей.

📎 Статья

@sqlhub

💻 Vitess 19 для кластеризации БД

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

Изменения и дополнения в Vitess 19:
⏩прекращение поддержки MySQL 5.7. Разработчики советуют пользователям выполнить обновление до MySQL 8.0, используя Vitess 18, прежде чем переходить на Vitess 19. Однако Vitess 19 по-прежнему будет поддерживать импорт из MySQL 5.7;
⏩добавлены новые метрики для консолидации потоков и версия сборки в /debug/vars, чтобы обеспечить более глубокое понимание и отслеживаемость;
⏩улучшена совместимость запросов, реализована поддержка операций удаления из нескольких таблиц, новый запрос SHOW VSCHEMA KEYSPACES и несколько других улучшений синтаксиса SQL, которые расширяют совместимость Vitess с MySQL;
⏩поддержка отсрочки попыток переключения в случае блокировки. Поддержка принудительного отключения;
⏩улучшение процесса инкрементного резервного копирования: поддержка имён резервных копий и пустых резервных копий.

«Следуя тенденции последних трёх лет, новая версия Vitess быстрее предыдущей во всех тестах, которые мы отслеживаем в Arewefastyet. Мы исправили несколько проблем с производительностью, доработали интерфейс и код», — пояснили разработчики, порекомендовав изучить документацию проекта и список исправлений.

🖥 Исходный код проекта опубликован на GitHub под лицензией Apache License 2.0.

@sqlhub

Как правильно подставлять значения из переменных / списков / словарей в SQL запрос в Python?

⏩Лучше всего использовать SQL запросы с использованием параметров:

cursor.execute("SELECT * FROM my_table WHERE id = ?", [123])
# parameter placeholder ------------------------> ^
# список/кортеж со значениями параметров -----------> ^^^^^

⏩Какие преимущества приносит использование параметров?
— Защита от SQL-инъекций

— Правильное квотирование литералов в зависимости от их типа (пример со строками, пример с датами).

— Оптимизация — сокращение времени работы SQL запроса. Благодаря использованию параметров следующие шаги не выполняются при повторном запуске (зависит от БД):
— проверка синтаксиса SQL запроса
— проверка прав доступа к объектам БД
— построение плана выполнения SQL запроса

— Защита от переполнения/вытеснения кеша SQL запросов. Например "безобидный" запрос qry = f"SELECT first_name, last_name FROM users WHERE id = {user_id}", который часто выполняется в нагруженной системе с различными значениями user_id может вытеснить из кеша запросов полезные запросы.

⏩ Пример использования параметров в SQL запросе:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table lang (name, first_appeared)")

cur.execute("insert into lang values (?, ?)", ("C", 1972))

lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)

cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
print(cur.fetchall())

con.close()

При таком подходе можно использовать cursor.executemany() - это значительно быстрее и эффективнее по сравнению с вставкой в цикле по одной строке.

📎 Читать подробнее

@sqlhub