🖥 SQL: быстрое погружение 2022

Автор: Шилдс Уолтер

Книга идеальна для всех, кто ищет новые перспективы карьерного роста; для разработчиков, которые хотят расширить свои навыки и знания в программировании.

➡️ Книга

@sqlhub

🖥 Как откатить часть транзакции внутри функции или процедуры?
На SQL вы можете сделать так:

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint; --rollback previous command
INSERT INTO table1 VALUES (3);
COMMIT;

Внутри функции или процедуры код выше завершится с ошибкой, например в is_sql.sql. Но вы можете откатить часть SQL команд в транзакции через подтранзакции:

DO $TEST$
BEGIN
-- here you can write DDL commands, for example, adding or deleting a table or its section
-- and/or
-- here you can write DML commands that modify data in tables and, thus, check the operation of triggers

-- rollback all test queries
raise exception using errcode = 'query_canceled';

EXCEPTION WHEN query_canceled THEN
--don't do anything
END
$TEST$;

@sqlhub

🖥 Как модифицировать данные в нескольких таблицах и вернуть id затронутых записей в одном запросе?

WITH
updated AS (
UPDATE table1
SET x = 5, y = 6 WHERE z > 7
RETURNING id
),
inserted AS (
INSERT INTO table2 (x, y, z) VALUES (5, 7, 10)
RETURNING id
)
SELECT 'table1_updated' AS action, id
FROM updated
UNION ALL
SELECT 'table2_inserted' AS action, id
FROM inserted;

@sqlhub

🖥 Расскажите о видах индексов

Простые индексы. Создаются только для одного столбца таблицы.

Синтаксис:

CREATE INDEX index_name
ON table_name(column_name);

Составные индексы. Создаются для двух или большего количества столбцов таблицы.

Синтаксис:

CREATE INDEX index_name
ON table_name (column1, column2)

Уникальные индексы. Используются для поддержания целостности данных таблицы. Они не дают вставлять в таблицу несколько значений.

Синтаксис:

CREATE UNIQUE INDEX index
ON table_name(column_name)

@sqlhub

🖥 Вопросы с собеседования по анализу данных SQL в 2023 году.

Вы готовитесь к собеседованию по SQL для анализ данных? Тогда вы пришли в нужное место!

Это руководство поможет вам усовершенствовать свои навыки работы с SQL, вернуть уверенность в себе и быть готовым к работе!

Здесь вы найдёте подборку реальных вопросов для собеседований, задаваемых в таких компаниях, как Google, Oracle, Amazon, Microsoft и т.д. К каждому вопросу прилагается идеально написанный ответ, что экономит ваше время на подготовку к собеседованию.

➡️ Читать дальше

@sqlhub

🖥 Как обезопасить приложение от тяжёлых миграций, приводящих к блокированию запросов?

#PostgreSQL

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

Вначале каждой миграции, которая выполняется внутри транзакции, нужно изменить настройки конфигурации lock_timeout и statement_timeout и idle_in_transaction_session_timeout командой SET LOCAL. Действие SET LOCAL продолжается только до конца текущей транзакции, независимо от того, фиксируется она или нет. При выполнении такой команды вне блока транзакции выдаётся предупреждение и больше ничего не происходит.

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

BEGIN;
DO $$
DECLARE
exception_message text;
exception_context text;
BEGIN
/*
Задаёт максимальную длительность ожидания любым оператором получения блокировки таблицы, индекса, строки или другого объекта базы данных.
Если ожидание не закончилось за указанное время, оператор прерывается.
Это ограничение действует на каждую попытку получения блокировки по отдельности и применяется как к явным запросам блокировки
(например, LOCK TABLE или SELECT FOR UPDATE без NOWAIT), так и к неявным.
Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах.
*/
SET LOCAL lock_timeout TO '3s';
-- Максимальное время выполнения любого SQL запроса в этой транзакции. Если будет превышено, то транзакция откатится.
SET LOCAL statement_timeout TO '30min';
-- Максимальное время простаивания транзакции, PostgreSQL >= 10. Если будет превышено, то транзакция откатится.
SET LOCAL idle_in_transaction_session_timeout TO '10s';
EXCEPTION WHEN undefined_object THEN
GET STACKED DIAGNOSTICS
exception_message = MESSAGE_TEXT,
exception_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '%', exception_context;
RAISE NOTICE '%', exception_message;
END $$;


/*
Здесь SQL команды для наката (или отката) внутри транзакции
*/
COMMIT;

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

Если транзакция откатится, то есть 2 варианта: запустить повторно во время меньших нагрузок или оптимизировать код миграции, чтобы свести к минимуму блокировки.

@sqlhub

🖥 Как упростить себе жизнь при разработке интерфейса взаимодействия приложения и БД?

же давно существуют ORM-фреймворки (англ. Object-Relational Mapping). ORM – это та самая прослойка между приложением и БД, с помощью которой можно, управляя объектами в приложении, синхронизировать их с объектами в БД, а также избавиться от необходимости вручную реализовывать DAL — то есть, не прописывать как должен выглядеть SQL-запрос на CRUD-операцию, не раскладывать переменные объекта по местам в запросе, не задавать приведение к типам/размерность, и т. д.

При этом, следует отметить, что, опираясь на личный опыт, я бы с осторожностью использовал ORM там, где для работы с данными требуются сложные SQL-запросы, так как на них ORM часто работает неоптимально, либо же вообще не работает.

Для большинства языков существует масса ORM-фреймворков, например, SQLAlchemy для Python, Entity Framework для .NET, Hibernate для Java. Опираясь на свои предпочтения, я буду разбирать типовые фишки ORM с помощью .NET Core/ EF Core/MS SQL.

➡️ Читать дальше

@sqlhub

🖥 Гайд по оптимизации SQL запросов

Думаю, каждый хоть раз использовал команду explain или хотя бы слышал про нее. Эта команда демонстрирует план выполнения запроса, но как именно СУБД приходит к нему остается загадкой. Да и как вообще СУБД понимает, что выбранный запрос оптимален? Неужели она проверяет все возможные варианты?

В этой статье я постараюсь дать небольшое представление о том, как работают оптимизаторы запросов с теоретической точки зрения.

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

➡️ Читать дальше

@sqlhub

🖥 Что вы знаете о функции STUFF(Transact-SQL)?

Функция STUFF вставляет одну строку в другую, начиная с указанной позиции. При этом в исходной строке, начиная с указанной позиции, удаляются символы в количестве, определенном в параметре Length.

Другими словами STUFF вставляет одну строку в другую. Она удаляет указанное количество символов первой строки в начальной позиции и вставляет на их место вторую строку.

Синтаксис:

STUFF(String1, Position, Length, String2)

Здесь String1 — это строка, которая будет перезаписана. Position определяет стартовую позицию для перезаписи строки. Length — это длина подстроки, которая будет удалена из исходной строки. String2 — это строка, которая будет вставлена в String1.

Пример:

SELECT STUFF(‘SQL Tutorial’,1,3,’Python’)
В результате работы функции «SQL Tutorial» превратится в «Python Tutorial».

Результат:

Python Tutorial

@sqlhub

🖥 Как быстро заполнить таблицу 1 000 000 ТЕСТОВЫХ записей?

Создадим таблицу samples с точно такой же структурой, что и таблица tests. Заполним ее 10 тестовыми записями.

CREATE TABLE samples (
title varchar(256) NOT NULL
);

INSERT INTO
samples (title)
VALUES
('8iRDgsEIq4GmOs32FnHM3b3cH60n3mm8070'),
('UAmsXyrKgApfHMyV2kUrYqLphN99Q7TJSoe'),
('OX1qiFeTigcOTO5JVvgFk7MRDgjgatkTqwL'),
('CT9Kfbplp4QC87G32UIKlkGd31jdjt4qH4f'),
('WsAhjBL5tAgihYZBtX97FNUmEpXavhb4CRw'),
('LwaitJ5dieXyixEmjJXhhqDY8Zg9Tu5ecoV'),
('KpHsnqrcMCpkRxkGNMjEJV0jFaeucPtbLWe'),
('t2GSIDOvW14eMlroAWrRR6xU5DoeNUXY0lD'),
('rSvrEPxR8rcw7QYjXfeNdyf3LpqYNHu3W7a'),
('6liUHPkjnygSatoUB4juZ5TaJZjaxHpR4BL');

Для того, чтобы превратить 10 записей из таблицы samples в 1 000 000 записей произведем самообъединение таблицы samples шесть раз

SELECT
fst.title
FROM
samples AS fst,
samples AS snd,
samples AS thd,
samples AS fth,
samples AS fif,
samples AS sth;

Оператор JOIN, который в SQL может быть заменен обычной запятой, осуществляет декартово соединение таблиц, когда каждой записи одной таблицы сопоставляется каждая запись другой таблицы. Таким образом, если в одной таблице 10 записей и в другой таблице 10 записей, результирующая таблица, полученная их соединением через JOIN будет содержать 100 записей. Как нетрудно увидеть

10 x 10 x 10 x 10 x 10 x 10 = 1 000 000 записей
Если в таблице samples будет 100 записей, для получения 1 000 000 записей будет достаточно соединений трех таких таблиц, если в samples будет 1000 записей, будет достаточно объединить таблицу саму с собой один раз.

Вставить результат в целевую таблицу tests можно при помощи оператора INSERT ... SELECT ...

INSERT INTO
tests
SELECT
fst.title
FROM
samples AS fst,
samples AS snd,
samples AS thd,
samples AS fth,
samples AS fif,
samples AS sth;

@sqlhub

🖥 Что такое уровни изолированности транзакций. Какие они бывают

Изоляция в смысле ACID означает, что конкурентно выполняемые транзакции изолированы друг от друга — они не могут помешать друг другу. Классические учебники по базам данных понимают под изоляцией сериализуемость (serializability). То есть каждая транзакция выполняется так, будто она единственная во всей базе. БД гарантирует, что результат фиксации транзакций такой же, как если бы они выполнялись последовательно (serially, одна за другой), хотя в реальности они могут выполняться конкурентно.

Чтение зафиксированных данных (read comitted):

Самый базовый уровень изоляции транзакций — чтение зафиксированных данных. Он обеспечивает две гарантии.

▪При чтении из БД клиент видит только зафиксированные данные (никаких «грязных» операций чтения).
▪При записи в БД можно перезаписывать только зафиксированные данные (никаких «грязных» операций записи).

«Грязные» операции чтения. Клиент читает записанные другим клиентом данные до их фиксации. Уровень изоляции чтения зафиксированных данных и более сильные предотвращают «грязные» операции чтения.

«Грязные» операции записи. Клиент перезаписывает данные, которые другой клиент записал, но еще не зафиксировал. Практически все реализации транзакций предотвращают «грязные» операции записи.

Изоляция снимков состояния и воспроизводимое чтение:

Ее идея состоит в том, что каждая из транзакций читает данные из согласованного снимка состояния БД, то есть видит данные, которые были зафиксированы в базе на момент ее (транзакции) начала. Даже если данные затем были изменены другой транзакцией, каждая транзакция видит только старые данные, по состоянию на конкретный момент времени. Позволяет предотвратить асимметрии чтения.

Асимметрия чтения (невоспроизводимое чтение). Клиент видит различные части базы данных по состоянию на разные моменты времени. Чаще всего такую проблему предотвращают с помощью изоляции снимков состояния, при которой транзакция читает данные из согласованного снимка состояния, соответствующего определенному моменту времени. Обычно это реализуется благодаря многоверсионному управлению конкурентным доступом (MVCC).

Сериализуемость (serializability):

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

Большинство современных БД, обеспечивающих сериализуемость, применяют один из трех методов:

По-настоящему последовательное выполнение транзакций. Если вы можете сделать отдельные транзакции очень быстрыми, причем количество транзакций, обрабатываемых за единицу времени на одном ядре CPU, достаточно невелико, то для обработки этот вариант окажется простым и эффективным.
Двухфазная блокировка. На протяжении десятилетий она была стандартным способом обеспечения сериализуемости, но многие приложения стараются ее не использовать из-за плохих показателей производительности.
Сериализуемая изоляция снимков состояния (SSI). Довольно свежий алгоритм, лишенный практически всех недостатков предыдущих подходов. В нем используется оптимистический подход, благодаря чему транзакции выполняются без блокировок. Перед фиксацией транзакции выполняется проверка, и если выполнение было несериализуемым, то транзакция прерывается без фиксации.

@sqlhub