Шпаргалка по SQL, которая выручает меня на собесах05.07.2023 02:32

Привет, Хабр!

Я решил посвятить свою первую статью SQL. Вопросы, рассмотренные ниже мне задавали на собеседованиях на позицию python-разработчика. Естественно отвечать правильно получалось не всегда, а если точнее то чаще не правильно, однако проведя N часов в рефлексии я составил перечень ответов, которыми пользуюсь до сих пор.

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

Мои эмоции на собеседованиях

Я собрал чертову дюжину вопросов, большая часть кода в которых относится к СУБД  PostgreSQL, так как это моя любимая СУБД

Вот спойлер всех вопросов:

1. Что такое «Нормализация»?

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

В такой ситуации разумно вынести номер телефона в отдельную таблицу и организовать связь «Один-ко-многим» (у одного пользователя может быть несколько телефонных номеров)

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

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

Нормальных форм существует аж 8: с 1NF и до 6NF, а также Бойса-Кодда и Доменно-ключевая формы.

2. Есть ли преимущество у NoSQL над SQL?

Иногда можно добиться большего быстродействия у первого языка. Если кратко — преимущество есть в скорости выполнения запросов. Это связано с отсутствием связей и конкретной схемы в NoSQL.

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

3. Что такое транзакция?

Транзакция — это элементарная операция в базе данных.

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

Транзакция начинается с команды BEGIN и заканчивается командой COMMIT либо отменяется командой ROLLBACK

4. Требования, предъявляемые к транзакции. ACID

Согласно теории баз данных транзакции должны обладать следующими свойствами:

1. Атомарность (atomicity). Это свойство означает, что либо транзакция будет зафиксирована в базе данных полностью, т. е. будут зафиксированы результаты выполнения всех ее операций, либо не будет зафиксирована ни одна операция транзакции.

2. Согласованность (consistency). Это свойство предписывает, чтобы в результате успешного выполнения транзакции база данных была переведена из одного согласованного состояния в другое согласованное состояние.

3. Изолированность (isolation). Во время выполнения транзакции другие транзакции должны оказывать по возможности минимальное влияние на нее.

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

Для обозначения всех этих четырех свойств используется аббревиатура ACID.

5. Выполнение транзакций

Можно выполнять транзакции последовательно или параллельно

И если в первом случае все понятно, то со вторым могут возникать «феномены», например:

1. Потерянное обновление (lost update)

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

2. «Грязное» чтение (dirty read)

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

3. Неповторяющееся чтение (non-repeatable read)

При повторном чтении тех же самых данных в рамках одной транзакции оказывается, что другая транзакция успела изменить и зафиксировать эти данные. В результате тот же самый запрос выдает другой результат.

4. Фантомное чтение (phantom read).

Транзакция повторно выбирает множество строк в соответствии с одним и тем же критерием. В интервале времени между выполнением этих выборок другая транзакция добавляет новые строки и успешно фиксирует изменения. В результате при выполнении повторной выборки в первой транзакции может быть получено другое множество строк.

5. Аномалия сериализации (serialization anomaly)

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

6. Что такое аномалия сериализации?

Для двух транзакций, скажем, A и B, возможны только два варианта упорядочения при их последовательном выполнении:

1️⃣ сначала A, затем B

2️⃣ сначала B, затем A.

Причем результаты реализации двух вариантов могут в общем случае не совпадать. 

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

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

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

Сериализация двух транзакций при их параллельном выполнении означает, что полученный результат будет соответствовать одному из двух возможных вариантов упорядочения транзакций при их последовательном выполнении. То есть мы знаем, что результат может быть либо 2100 у.е. либо 2200 у.е. — не иначе. Соответственно, аномалия сериализации — несоответствие результата ни одному из вариантов.

При этом нельзя сказать точно, какой из вариантов будет реализован. 

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

Конечно, чем больше транзакций, тем больше вариантов их упорядочения. Концепция сериализации не предписывает выбора какого-то определенного варианта. Речь идёт лишь об одном из них.

7. Уровни изоляции в SQL

1. Read Uncommitted

Это самый низкий уровень изоляции. Согласно стандарту SQL на этом уровне допускается чтение «грязных» (незафиксированных) данных.

Однако в PostgreSQL требования, предъявляемые к этому уровню, более строгие, чем в стандарте: чтение «грязных» данных на этом уровне не допускается.

2. Read Committed

Не допускается чтение «грязных» (незафиксированных) данных. Транзакция может видеть только те незафиксированные изменения данных, которые произведены в ходе выполнения ее самой.

3. Repeatable Read

Не допускается чтение «грязных» (незафиксированных) данных и неповторяющееся чтение. В PostgreSQL на этом уровне не допускается также фантомное чтение.

4. Serializable

Не допускается ни один из феноменов, перечисленных выше, в том числе и аномалии сериализации.

Конкретный уровень изоляции обеспечивает сама СУБД с помощью своих внутренних механизмов. Его достаточно указать в команде при старте транзакции.

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

8. Блокировки

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

Команда SELECT имеет предложение FOR UPDATE, которое позволяет заблокировать отдельные строки таблицы с целью их последующего обновления.

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

Таким образом если выполнять данную команду:

SELECT * FROM table_tame WHERE column_name ~ 'some text' FOR UPDATE;

на двух терминалах — сначала на одном —, а затем на втором (с учетом начала транзакции BEGIN)

То можно заметить, что выполнение на втором терминале приостановится до тех пор пока не завершится транзакция первого терминала

При этом если на первом терминале выполнить какую-нибудь другую команду:

UPDATE table_name SET column_name = 'kek' WHERE column_value = 404;

То, перейдя на второй терминал станет видно, что там была, наконец, выполнена выборка, которая покажет уже измененные данные с учетом данной UPDATE-команды

Ловушка джокера

9. Инедксы

Индекс — специальная структура данных, которая связана с таблицей и создаётся на основе её данных. Индексы создаются для повышения производительности функционирования базы данных.

Какие бывают индексы?

• В-дерево

• хеш

• GiST

• SP-GiST

• GIN

• BRIN

По умолчанию команда CREATE INDEX создаёт индексы типа В-дерево (эффективны в большинстве случаев)

Как можно создать индексы?

© Habrahabr.ru