Data Science. SQL hub

🖥 Почему SELECT * и SELECT column возвращает строки в разном порядке?

Есть запрос №1:

WITH cte1 AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY column1) AS rn
FROM table)
SELECT column0
FROM cte1
WHERE rn = 1022;

есть запрос №2:

WITH cte1 AS
(SELECT column0,
ROW_NUMBER() OVER (ORDER BY column1) AS rn
FROM table)
SELECT column0
FROM cte1
WHERE rn = 1022;
PostgreSQL, в column0 нет NULL-ов, значения уникальны.

При использовании SELECT * и SELECT column0 , получаем разные значения на rn 1022 (и не только на 1022). Сортировка в cte1 одна и та же, ничего не меняется, кроме количества колонок. Почему так происходит ?

Это особенность SQL. Если явно не указана сортировка, то база данных может вернуть строки в любом порядке. Как ей захочется. И это может зависить не только от звездочки в запросе, но и просто от времени когда делается запрос.

Хотите избежать неоднозначности - указывайте сортировку в явном виде.

Проблема возникает из-за того, что сортировка по column1 в случае - неоднозначна т.е. в этом поле есть дубликаты и отсортировать по column1 можно разными способами, что БД и делает при разных запросах.

Вот пример:

column0 | column1
--------+---------
A | 1
B | 1

Проблема возникает из-за того, что сортировка по column1 в вашем случае неоднозначна т.е. в этом поле есть дубликаты и отсортировать по column1 можно разными способами, что БД и делает при разных запросах.

Вот пример:

column0 | column1
--------+---------
A | 1
B | 1

Если выполнить запрос из CTE, до любой порядок строк будет правильным и если мы выбираем, скажем, rn=1 то мы можем получить в результате и A и B.

Чтоб этого избежать нужно задавать сортировку по полям, которые однозначно задают порядок. Обычно достаточно включить последним (т.е. самым низкоприоритетным) полем в сортировке уникальное поле (например, первичный ключ), например:

SELECT column0,
ROW_NUMBER() OVER (ORDER BY column1, id) AS rn
FROM table

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

@sqlhub

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

Вводные данные
Таблица со зданиями и координатами (в оригинальной таблице более 100 тыс. строк):

CREATE TABLE table1(
building_id int,
region varchar(55),
street varchar(55),
housenumber varchar(55),
pos_x float(100),
pos_y float(100)
);

INSERT ALL
INTO table1(building_id,region, street, housenumber, pos_x, pos_y ) VALUES(1, 'Moscow','Lenina', '1a', 45.45, 55.55 )
INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(2, 'Spb','Mira', '20', 45.00, 55.00 )
INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(3, 'Moscow','Lenina', '1a', 45.00, 55.00 )
INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(4, 'Moscow', 'Lenina', '1a', 45.45, 55.55 )

SELECT * FROM dual;


Вывод
Building_ID region street housenumber pos_x pos_y
1 Moscow Lenina 1a 45.45 55.55
2 Spb Mira 20 45 55.55
3 Moscow Lenina 1a 45 55.55
4 Moscow Lenina 1a 45.45 55.55
Ожидаемый результат
Building_ID region street housenumber pos_x pos_y
1 Moscow Lenina 1a 45.45 55.55
4 Moscow Lenina 1a 45.45 55.55

Нужно вывести дубли по региону, улице, дому и координатам одновременно, т.е. должны остаться только ID 1 и 4. Для меня получилось проблемно, т.к. тип координат FLOAT и они никак не хотят фильтроваться.

С помощью HAVIG BY и EXISTS:

SELECT *
FROM table22 A
WHERE EXISTS (
SELECT COUNT(*)
FROM table22 t
WHERE a.region = t.region
AND a.street = t.street
AND a.housenumber = t.housenumber
AND a.pos_x = t.pos_x
AND a.pos_y = t.pos_y
HAVING COUNT(*) > 1
)

С помощью оконной фукнции

SELECT x.building_id, x.region, x.street, x.housenumber, x.pos_x, x.pos_y FROM (
SELECT
t.*
, COUNT(*) OVER(PARTITION BY t.street, t.region, t.housenumber, t.pos_x, t.pos_y ) cnt
FROM table22 t
) x
WHERE cnt > 1;

Пишите свое решение в комментариях👇

@sqlhub

🖥 Сравнение производительности сжатия данных MySQL на Java 2023

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

Давайте отложим в сторону вопрос о том, подходит ли MySQL для хранения двоичных данных. Вопрос здесь будет заключаться в том, как хранить двоичные данные, чтобы считывание из БД происходило как можно быстрее?

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

➡️ Читать

@sqlhub

Покупки товара после 10 октября 2022 [Тестовое Альфа-банк]
#junior

Задание
Необходимо вывесть количество людей, которые покупали товар с id = 5 после 10 октября 2022 (включительно).

Столбцы в результате
count - столбец с посчитанным количеством людей
Важно: Названия столбцов должны в точности совпадать.

Пишите свое решение в комментариях👇

@sqlhub

🖥 Напишите SQL-запрос, выводящий max(count(...)) и другие поля таблицы, соответствующие max-параметру

Имеются следующие таблицы:

Person(поля Nom и др.) - информация о людях,
Profit(поля ID, Source, Moneys) - источники дохода,
Have_d(поля Nom, ID и др.) - связь между людьми и их доходами.
Каждый человек может иметь несколько источников дохода. Необходимо вывести всю информацию о самом популярном источнике дохода.

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

Решение:

select top 1 t1.*
from (select profit.source,count(*) as expr1
from profit, have_d
where profit.id = have_d.id
group by profit.source) as t1
order by expr1 desc

Вариант2:

select t1.*
from (select profit.*,count(*) as expr1
from profit, have_d
where profit.id = have_d.id
group by profit.id
order by expr1 desc) as t1
limit 1

👉 Это не оптимальное решение. Пишите свое решение в комментариях👇

@sqlhub

🖥 Влияние кол-ва запросов в транзакции на производительность

Нужно ли отправлять изменения в БД одной транзакцией, или каждый INSERT, UPDATE, DELETE лучше выполнять отдельной транзакцией? Что лучше при выполнении 10 несвязанных запросов (не нужно откатывать предыдущий запрос при ошибке последующего), отправить их одной транзакцией, или каждый по отдельности? Какой вариант лучше для производительности приложения и субд?

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

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

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

Чтобы наглядно продемонстрируем, написал небольшой скрипт. MS SQL Server.

Кратко что он делает:

▪T_TABLE - таблица с данными
▪T_RESULT - таблица с результатами эксперимента
▪P_INSERT_ROWS @Count - вставляет в таблицу T_TABLE @Count строк
▪P_DALATE_ROWS @Count, @Pack - удаляет из таблицы
T_TABLE все строки в транзакциях по @Pack штук, и записывает затраченное время в таблицу T_RESULT.

Финальный скрипт запускает в цикле вставку 1 000 000 строк, и их удаления пачками по 1, 10 .. 1 000 000 штук. И затем выводит содержимое T_RESULT.

Скрипт:

USE tempdb;
GO
IF OBJECT_ID('P_INSERT_ROWS', 'P') IS NOT NULL DROP PROC P_INSERT_ROWS;
IF OBJECT_ID('P_DELETE_ROWS', 'P') IS NOT NULL DROP PROC P_DELETE_ROWS;
IF OBJECT_ID('T_TABLE', 'U') IS NOT NULL DROP TABLE T_TABLE;
IF OBJECT_ID('T_RESULT', 'U') IS NOT NULL DROP TABLE T_RESULT;
GO
CREATE TABLE T_TABLE (
id INT IDENTITY(1,1),
Number INT,
String NVARCHAR(4000)
)
CREATE INDEX IN_T_TABLE_NUMBER ON T_TABLE(Number ASC)
CREATE TABLE T_RESULT (
execute_time DATETIME,
Cnt INT,
Pack INT
)
GO
CREATE PROC P_INSERT_ROWS
@Count INT
AS
;WITH CTE AS(
SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<@Count
)
INSERT T_TABLE
SELECT N, LEFT(REPLICATE(N, 100),4000)
FROM CTE
OPTION(MAXRECURSION 0)
GO
CREATE PROC P_DELETE_ROWS
@Count INT,
@Pack INT
AS
DECLARE @TTT DATETIME = GETDATE();
DECLARE @NumberStart INT = 1
WHILE @NumberStart < @Count
BEGIN
BEGIN TRAN;
DELETE FROM T_TABLE WHERE Number BETWEEN @NumberStart AND @NumberStart + @Pack - 1 OPTION(RECOMPILE);
COMMIT TRAN;
SET @NumberStart += @Pack;
END;
INSERT T_RESULT
SELECT GETDATE()-@TTT execute_time, @Count Cnt, @Pack Pack
GO
SET NOCOUNT ON;
DECLARE
@Count INT = 1000000,
@Pack INT = 1
WHILE @Pack <= @Count
BEGIN
EXEC P_INSERT_ROWS @Count
EXEC P_DELETE_ROWS @Count, @Pack
SET @Pack *= 10;
END
SELECT * FROM T_RESULT
GO
Содержимое T_RESULT:

execute_time Cnt Pack
1900-01-01 00:15:05.183 1000000 1
1900-01-01 00:01:56.003 1000000 10
1900-01-01 00:00:32.200 1000000 100
1900-01-01 00:00:22.370 1000000 1000
1900-01-01 00:00:21.940 1000000 10000
1900-01-01 00:00:22.663 1000000 100000
1900-01-01 00:01:10.327 1000000 1000000

Как видим, лучший результат по времени выполнения имеем, когда удаляется в одной транзакции пачками по 10000 штук, т.е. это намного эффективнее, чем удалять по одной записи в транзакции, и эффективнее, чем удалить все строки в одной транзакции. Эксперимент не строгий, запуски влияют друг на друга, но всё равно показательно.

UPD: На практике, транзакции в реальной жизни не часто бывают большими. Кроме запросов вида DELETE FROM T. В этом случае да, чаще всего если есть возможность обернуть несколько действий в одну транзакцию - лучше обернуть.

@sqlhub

🔥 Подборка каналов для разработчиков

⭐️ Нейронные сети
@vistehno - chatgpt ведет блог, решает любые задачи и отвечает на любые ваши вопросы.
@aigen - сети для генерации картинок. видео, музыки и многого другого.
@neural – погружение в нейросети.

🖥 Machine learning
@ai_ml – погружение в нейросети, ai, Chatgpt, midjourney, машинное обучение.
@datasc - дата сайнс обучаем самой востребованной профессии
@machinelearning_ru – машинное обучении на русском от новичка до профессионала.
@machinelearning_interview – подготовка к собеседованию.
@datascienceiot – бесплатные книги Machine learning
@ArtificialIntelligencedl – канал о искусственном интеллекте

@machinee_learning – чат о машинном обучении
@datascienceml_jobs - работа ds, ml
@Machinelearning_Jobs - чат работы мл

🖥 SQL базы данных

@sqlhub - Повышение эффективности кода с грамотным использованием бд.
@chat_sql - чат изучения бд.

🖥 Python

@pythonl - главный канал самого популярного языка программирования.
@pro_python_code – учим python с ментором.
@python_job_interview – подготовка к Python собеседованию.
@python_testit - проверочные тесты на python
@pythonlbooks - современные книги Python
@python_djangojobs - работа для Python программистов
@python_django_work - чат обсуждения вакансий


🖥 Javascript / front

@react_tg - - 40,14% разработчиков сайтов использовали React в 2022 году - это самая популярная библиотека для создания сайтов.
@javascript -канал для JS и FrontEnd разработчиков. Лучшие практики и примеры кода. Туториалы и фишки JS
@Js Tests - каверзные тесты JS
@hashdev - погружение в web разработку.
@javascriptjobjs - отборные вакансии и работа FrontEnd.
@jsspeak - чат поиска FrontEnd работы.

🖥 Java
@javatg - выучить Java с senior разработчиком по профессиональной методике.
@javachats - чат для ответов на вопросы по Java
@java_library - библиотека книг Java
@android_its - Android разработка
@java_quizes - тесты Java
@Java_workit - работа Java
@progersit - шпаргалки ит

👣 Golang
@Golang_google - восхитительный язык от Google, мощный и перспективный.
@golang_interview - вопросы и ответы с собеседований по Go. Для всех уровней разработчиков.
@golangtests - интересные тесты и задачи GO
@golangl - чат изучающих Go
@GolangJobsit - отборные вакансии и работа GO
@golang_jobsgo - чат для ищущих работу.
@golang_books - полезные книги Golang
@golang_speak - обсуждение языка Go

🖥 Linux
@linux -топ фишки, гайды, уроки по работе  с Linux.
@linux chat - чат linux для обучения и помощи.
@linux_read - бесплатные книги linux

👷‍♂️ IT работа

@hr_itwork -кураторский список актуальных ит-ваканнсии

🤡It memes
@memes_prog - ит-мемы

⚙️ Rust
@rust_code - Rust избавлен от болевых точек, которые есть во многих современных яп
@rust_chats - чат rust

#️⃣ c# c++
C# - объединяет лучшие идеи современных языков программирования
@csharp_cplus чат
С++ - Универсальность. Возможно, этот главный плюс C++.

📓 Книги

@programming_books_it - большая библиотека. программиста
@datascienceiot -ds книги
@pythonlbooks - python библиотека.
@golang_books - книги Golang
@frontendbooksit - front книги
@progersit - ит-шпаргалки
@linux_read - Linux books
@java_library - Java books

🖥 Github
@github_code - лучшие проекты с github
@bigdatai - инструменты по работе с данными

🖥 Devops
Devops - специалист общего профиля, которому нужны обширные знания в области разработки.

📢 English for coders

@english_forprogrammers - Английский для программистов

🖥 Как вывести несколько чисел используя только SQL запрос?

Задча написать SQL запрос, который бы возвращал точно такой вывод:

id|
---
1 |
2 |
3 |
5 |

Представьте, что в БД нет ни одной таблицы, и создавать их нельзя.

mysql> SELECT 1 id UNION SELECT 2 id UNION SELECT 3 id UNION SELECT 5 id;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0,00 sec)

Существует ли более элегантное решение?

В MySQL единственный вариант (Алиас нужен только у первого числа):

select 1 ID union select 2 union select 3 union select 5

Самый красивый (для малого количества чисел) вариант можно написать в MS-SQL и Postrgess:

select * from (values (1),(2),(3),(5)) as t(id)

Oracle (С использованием системного типа в качестве коллекции):

select column_value ID from table(sys.odcinumberlist(1,2,3,5))

Если чисел значительно больше и они идут просто подряд, то почти универсальный (Из широко распространенных СУБД НЕ работает только в MySQL):

with Q as (
select 1 ID
union all
select ID+1 from Q where ID<5
) select * from Q

Самый лаконичный (IMHO) вариант для большого количества чисел подряд в Oracle:

select rownum id from DUAL connect by rownum<6

ЕщёВ Oracle можно воспользоваться XQuery последовательностью:

select to_number(column_value) id from xmlTable ('1 to 3, 5')
/
ID
----------
1
2
3
5

👉 Пишите свое решение в комментариях👇

@sqlhub

🖥 Задача

Каким образом можно сократить количество взятых строк из таблицы Jobs, чтобы при выборе значений брались все значения из первой таблицы и только 10 значений из второй?

Решения
Вариант 1:

select *
from hr.job_history jh
left join (
select *
from hr.jobs jb
order by job_id
fetch first 10 rows only) jb on jb.job_id = jh.job_id

Вариант 2:

SELECT *
FROM HR.JOB_HISTORY JH
LEFT JOIN ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY JB.job_id
ORDER BY JB.id -- обязательна уникальная сортировка
) rownum
FROM HR.JOBS JB ) JBS
ON JH.job_id = JBS.job_id
WHERE JBS.rownum < 10;

👉 Пишите свое решение в комментариях👇

@sqlhub

🖥 Pandas + SQL. Запросы SQL для работы с датафреймами pandas.

Данная библиотека может быть полезна:

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

- в случаях, когда задачу выгрузки данных интуитивно легче сформулировать средствами декларативного языка запросов SQL.

$ pip install -U pandasql

▪Читать
▪Примеры с кодом
▪Github

@sqlhub

🔟 продвинутых SQL вопросов с собеседований, на которые вы должны знать ответ.

Продолжаем серию статей по вопросам с SQL собеседований для аналитика данных.

▪ Читать
▪ Зеркало

@sqlhub

🖥 Есть ли преимущество в использования IN вместо множества OR в sql?

Если говорить про абстрактный SQL - никакой разницы нет. Есть много способов попросить достать одни и те же данные. Впрочем, с точки зрения читаемости человеком in явно выигрывает из-за компактности.

А если рассматривать конкретные реализации - то различия могут быть. Например, мой локальный postgresql строит разные планы:

melkij=> explain select * from bigtable where id = 1 or id = 3 or id=4;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on bigtable (cost=13.34..25.34 rows=3 width=12)
Recheck Cond: ((id = 1) OR (id = 3) OR (id = 4))
-> BitmapOr (cost=13.34..13.34 rows=3 width=0)
-> Bitmap Index Scan on bigtable_pkey (cost=0.00..4.45 rows=1 width=0)
Index Cond: (id = 1)
-> Bitmap Index Scan on bigtable_pkey (cost=0.00..4.45 rows=1 width=0)
Index Cond: (id = 3)
-> Bitmap Index Scan on bigtable_pkey (cost=0.00..4.45 rows=1 width=0)
Index Cond: (id = 4)

melkij=> explain select * from bigtable where id in (1,3,4);
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using bigtable_pkey on bigtable (cost=0.44..17.37 rows=3 width=12)
Index Cond: (id = ANY ('{1,3,4}'::integer[]))

Как можно заметить, in был переписан в другую форму и оценён дешевле, чем несколько эквивалентных or

Какие-то другие СУБД может быть переписывают оба запроса в идентичное представление, или же переписывают in в группу or. Вопросы производительности необходимо рассматривать только в рамках какой-то конкретной реализации.

MS SQL Server на оба запроса генерирует абсолютно одинаковые планы не зависимо от наличия индекса.

В MySQL, согласно документации in использует бинарный поиск, т.е. теоретически должен быть быстрее, чем or. На практике оказывается, что при наличии индекса разницы почти нет, а при отсутствии in работает примерно на 30% быстрее.

@sqlhub

🖥 Советы по оптимизации SQL-запросов

Основные причины для оптимизации SQL-запросов :

Повышение производительности. Основной причиной оптимизации SQL-запросов является сокращение времени отклика и повышение производительности запроса. Разница во времени между запросом и ответом должна быть
минимизирована для лучшего взаимодействия с пользователем.

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

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

📌 Читать

@sqlhub

🖥 Советы как сделать SQL запросы читабельнее и понятнее

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

📌 Статья

@sqlhub

🖥 Как получить записи, которые удовлетворяют условиям из JSON массива (postgresql)?

SELECT * FROM (
VALUES ('[{"id" : 1, "created_at" : "2003-07-01", "name": "Sony"},
{"id" : 2, "created_at" : "2008-10-27", "name": "Samsung"}]'::jsonb),
('[{"id" : 3, "created_at" : "2010-03-30", "name": "LG"},
{"id" : 4, "created_at" : "2018-12-09", "name": "Apple"}]'::jsonb)
) AS t
WHERE EXISTS(
SELECT *
FROM jsonb_to_recordset(t.column1) AS x(id int, created_at timestamp, name text)
WHERE x.id IN (1, 3) AND x.created_at > '2000-01-01' AND name NOT LIKE 'P%'
)

@sqlhub

🖥 Такие одинаковые, но такие разные: особенности реализации функции CONCAT в разных языках и СУБД

Часто при работе с табличными данными из различных источников для их объединения требуется дополнительная подготовка ключевых столбцов.

Например, если в одной таблице реквизиты договора или иного документа находятся в разных столбцах, а в другой – в одном. В подобной ситуации, как и во многих других (а может и во всех) есть два пути решения задачи приведения к одному виду: разделение одного столбца на два и слияние (конкатенация) двух столбцов в один.

В настоящей публикации остановлюсь на подробном рассмотрении второго варианта. Работает функция конкатенации строк в разных СУБД (Microsoft SQL Server, Oracle) и других программных средствах (Excel, Python).

📌 Читать

@sqlhub

🖥 В чём разница между using и on в join-запросах (mysql)?

USING (column_name(s)), по сути, является синтаксическим сахаром над ON. Согласно документации— служит для указания списка столбцов, которые должны существовать в обеих таблицах.

Такое выражение USING, как:

A LEFT JOIN B USING (C1, C2, C3, ...)
семантически идентично выражению ON:

A.C1 = B.C1 AND A.C2 = B.C2 AND A.C3 = B.C3,...
В то время как ON можно "склеить" столбцы с различными именами.

Но с ON можно проделать операций чуть больше, например можно присоединить не только колонку, но и набор колонок или даже целое условие, пример:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

Дополнительно

USING — при перечислении полей в запросе не обязательно указывать префикс:

SELECT film.title, film_id // # film_id указан без префикса
FROM film
JOIN film_actor USING (film_id)
WHERE ...
Тоже самое с ON:

SELECT film.title, film.film_id // # film.film_id обязателен префикс
FROM film
JOIN film_actor ON (film.film_id = film_actor.film_id)
WHERE ...
Если не перечислять поля явно, а использовать select * для соединения столбцов, то в результирующем наборе при ON колонка "всплывёт" дважды, в то время как с USING — только раз:

mysql> create table t(i int);
insert t select 1;
create table t2 select*from t;

Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t join t2 on t.i = t2.i;
+------+------+
| i | i |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t join t2 using(i);
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql>

@sqlhub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@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