Привет, Хабр!
Я решил посвятить свою первую статью SQL. Вопросы, рассмотренные ниже мне задавали на собеседованиях на позицию python-разработчика. Естественно отвечать правильно получалось не всегда, а если точнее то чаще не правильно, однако проведя N часов в рефлексии я составил перечень ответов, которыми пользуюсь до сих пор.
Данная информация предполагает знание основ языка запросов и я надеюсь, она окажется полезной для разработчиков, которые сейчас активно ищут работу а также, что ты прочитаешь этот текст до конца и добавишь свой вопрос к перечню (ну или поправишь неточности в существующих)
Я собрал чертову дюжину вопросов, большая часть кода в которых относится к СУБД PostgreSQL, так как это моя любимая СУБД
Вот спойлер всех вопросов:
Нормализация
NoSQL
Транзакции
ACID
Выполнение транзакций
Аномалия сериализации
Уровни изоляции
Блокировки
Индексы
Планировщик
Оптимизация запросов
Масштабируемость
Что будет если сделать
EXPLAIN ANALYZE DROP TABLE POSTGRES
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-команды
Ловушка джокера
тут могут спросить про такую штуку как DEAD LOCK - напишите в комментах пример дед лока)
9. Индексы
Индекс – специальная структура данных, которая связана с таблицей и создаётся на основе её данных. Индексы создаются для повышения производительности функционирования базы данных.
Какие бывают индексы?
В-дерево
хеш
GiST
SP-GiST
GIN
BRIN
По умолчанию команда CREATE INDEX создаёт индексы типа В-дерево (эффективны в большинстве случаев)
Как можно создать индексы?
???? Индекс по столбцу (это чистая классика)
???? Индекс по нескольким столбцам
???? Уникальный индекс
???? Индекс на основе выражения
???? Частичный индекс
Для создания индекса используется примерно такой синтаксис:
CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ... ) [STATEMENT] ;При этом:
для создания уникального индекса может использоваться слово UNIQUE
для создания выражения его записывают в скобках, например для создания выражения проверки индекса на нижний регистр можно написать так:
. . . ( lower( <column_name> ) ) ;для создания частичного индекса после скобок запись продолжается, например для проверки на величину можно написать так:
. . . ( . . . ) WHERE <column_name> > 1000 ;10. Планировщик
Это такой встроенный механизм в СУБД, перед выполнением запроса он формирует "план" выполнения запроса. Просмотреть план выполнения любого запроса можно с помощью команды EXPLAIN. Для детального понимания планов выполнения сложных запросов требуется опыт.
Планировщиком можно управлять с целью просмотра логики его работы и дальнейшей оптимизации запросов
11. Оптимизация запросов
Повлиять на скорость выполнения запроса можно различными способами. Я запомнил их так: изменить сам sql-запрос, обновить статистику планировщика, денормализация и 4 вариации изменения параметров планировщика:
изменение исходного кода запроса
обновление статистики, на основе которой планировщик строит планы
денормализация: создание временных таблиц или создание индексов
изменение параметров планировщика, управляющих выбором порядка соединения наборов строк
изменение параметров планировщика, управляющих выбором метода доступа к данным (
enable_seqscan,enable_indexscan,enable_indexonlyscan,enable_bitmapscan)изменение параметров планировщика, управляющих способом соединения наборов строк (
enable_nestloop,enable_hashjoin,enable_mergejoin);изменение параметров планировщика, управляющих использованием ряда операций: агрегирование на основе хеширования, материализация временных наборов строк, выполнение явной сортировки при наличии других возможностей.
12. Какие есть концепции масштабируемости БД
Я всегда выделял 2 основных. Это Шардирование и Репликация. В обоих случаях подразумевается наличие нескольких инстансов БД.
Для шардирования мы определяем срезы строк, которые будут храниться в конкретном инстансе, например для первого, это с 1 по 10 000, а для второго с 10 001 по 20 000 и так далее.
Репликация подразумевает создание одного инстанса-контроллера и нескольких инстансов-нод. То есть по сути это полное копирование СУБД на другой сервер. Таким образом контроллер сам будет решать в какую ноду и что ему записывать.
По статистике и, наверное, логике выборки данных SELECT в интернетах выполняются во много раз чаще, чем INSERT'ы, на этом фоне репликация выглядит привлекательнее.
Все выше сказанное относится к теме "Повышение отказоустойчивости". Вот пара статей про неё: раз, два
13. Что будет если сделать EXPLAIN ANALYZE DROP DATABASE POSTGRES
Ну и напоследок такой вопрос с подвохом: всё плохо будет, потому что из-за ANALYZE субд мало того что с радостью удалит главную бд, так она ещё и посчитает веса а также время выполнения данного действа.
То есть простой EXPLAIN показывает план а EXPLAIN ANALYZE его ещё и выполняет
Заключение
Большую часть материала я брал из интернетов книги Е.П. Моргунова "PostgreSQL. Основы языка SQL"
Я рекомендую её к прочтению разработчикам любого уровня так как в ней в избытке практических заданий.
Многие выдержки и полезные советы можно почитать и обсудить в моём телеграм-канале.
Комментарии (163)
LeshaRB
05.07.2023 05:32+9EXPLAIN ANALYZE DROP TABLE POSTGRES
Данные стараются не удалятьНа моей практике Explain применялся к select
Для drop вижу первый разТогда странно что нет ответа на классику
Delete drop truncate в чем разница
mkrshn
05.07.2023 05:32delete - построковое удаление
truncate - внутренний механизм удаления массива строк данных с чисткой garbage
drop - удаление всей таблицыLeshaRB
05.07.2023 05:32Не отвечано про триггеры и транзакции, про условия удаления итд
Собеседование завалено...
Ivan22
05.07.2023 05:32+2фраза "массив строк" смущает, применительно к субд, да и что такое garbage ???
edogs
05.07.2023 05:32+2Уж очень просто для шпаргалки. Понятно, что собеседование на питониста, а не на БДшника. Но, имхо, если в резюме хотя бы косвенно включено sql, то такие вещи должны быть на уровне таблицы умножения.
Delfnsk
05.07.2023 05:32+33Хз, работаю аналитиком данных больше 2ух лет. Знаю хорошо SQL для аналитика (могу собрать любой стат тест используя SQL только). Но ни одной вещи из выше не использовал в работе.
Эти знания избыточны для некоторых профессий, где нужен SQL. Это как разработчика просить помнить работу специфичных аналитических функций sql, которые разработчик использует в жизни считанное число раз.
Leetc0deMonkey
05.07.2023 05:32+78Понимаете, современное собеседование оно не про работу. Это что-то вроде викторины или спортивного состязания среди пары сотен кандидатов, с главным призом в виде трудоустройства.
K_Chicago
05.07.2023 05:32+3Я согласен с вами что эта статья - для викторины.
понимаете, то что вы назвали "современное собеседование" - это вариант для трудоустройства тех, кто хорошо запоминает шпаргалки. Они будут сидеть и получать присвоеный оклад содержания, а их наниматель - высылать наверх бодрые отчеты.
Это всё не имеет отношения к специалистам-разработчикам и к фирме, выполняющей нужную и важную работу.
Поэтому если читатель - специалист разработчик и он хочет и любит делать свою работу - это чтиво не для него. К счастью, есть и настоящие собеседования, и реальные коллективы разработчиков с реальными задачами и требованиями.
А г-н @honsour72 написал пособие для "кое-какеров" - по-моему, это неэтично.
honsour72 Автор
05.07.2023 05:32+3Я был на гос службе и все примерно так как ты и говоришь)
Я подразумеваю, что данная информация станет точкой для дальнейшего более углубленного изучения
А не просто стишком для заучивания
С другой стороны тогда мне не нужно было называть статью шпаргалкой)
В любом случае спасибо за комментарий)
Leetc0deMonkey
05.07.2023 05:32+5К счастью, есть и настоящие собеседования, и реальные коллективы разработчиков с реальными задачами и требованиями.
Как бы вот их только научиться сходу распознавать, ещё не вляпавшись в викторину.
honsour72 Автор
05.07.2023 05:32Это многолетняя практика и опыт который как мне кажется приобрести ну очень сложно.
Dremkin
05.07.2023 05:32+4Если вы скажете что-то типа "я не запоминаю синтаксис, а решаю задачи" и после этого вас выгонят, то собеседование для вас прошло успешно )
honsour72 Автор
05.07.2023 05:32Тоже соглашусь, эти вопросы исключительно из собесов на практике только оптимизация особо нужна
smokevadim
05.07.2023 05:32А нормализация? Основа архитектуры БД.
ioncorpse
05.07.2023 05:32Последний раз 3НФ базу видел в ВУЗе, лет 20 назад. Слово нормализация довелось слышать за это время раз 10 максимум. На вопрос бы не ответил. Программирую и БД модели делаю постоянно. Что со мной не так?
mayorovp
05.07.2023 05:32Вот это и не так, что делая БД модели постоянно вы ни разу не увидели 3НФ.
Ndochp
05.07.2023 05:32Да запросто — в реальной, а не учебной задаче вся база скорее всего не будет удовлетворять 3НФ. Что-нибудь да нарушат. Например ключами будут контрагент и договор одновременно. Просто потому, что остатки чаще нужны по контрагенту.
nin-jin
05.07.2023 05:32-1Не так - использование СУБД, где ненормальные формы использовать проще и быстрее, чем нормальные.
PuerteMuerte
05.07.2023 05:32+1Программирую и БД модели делаю постоянно. Что со мной не так?
У меня батя делал мебель, и называл шканты "чопиками". Это ему не мешало пользоваться шкантами, просто он не называл их правильно.
FlashHaos
05.07.2023 05:32+5Хотите сказать, что аналитик данных не должен знать про транзакции и нормализацию?
Ivan22
05.07.2023 05:32+4про транзакции точно нет - т.к. аналитик не делает ETL. Это задача дата инженеров, аналитик в лучшем случае строит графики в PowerBi (где никаких транзакций нету), в худшем - в Экселе.
edogs
05.07.2023 05:32+8Знаю хорошо SQL для аналитика (могу собрать любой стат тест используя SQL только). Но ни одной вещи из выше не использовал в работе.
Не использовали ни одной или не знаете ни одной?
Вы не знаете зачем нормализация, хотя работаете с базами в которых есть сложные выборки? Вы собираете статистику и не в курсе что могут прийти некорректные данные из-за записи в бд во время сбора данных? Вы агрегируете статистику и не в курсе про индексы? Собирая статистику Вы не в курсе про оптимизацию запросов? Ну право слово, Вы серьезно?Эти знания избыточны для некоторых профессий, где нужен SQL. Это как разработчика просить помнить работу специфичных аналитических функций sql, которые разработчик использует в жизни считанное число раз.
Так в том и дело, что в шпаргалке нет ни одной специфической функции. Это как включить в резюме знание математики и не знать таблицу умножения. Типа - я математику знаю, но в специфичных вещах не разбирался, ага.
korvint
05.07.2023 05:32+5Ну не знаю, я работаю с SQL since 1999-го постоянно. И я уже забыл, что нормализация зовется нормализацией :-)
Для меня нормализованная БД как здоровое дыхание.
edogs
05.07.2023 05:32+4Для меня нормализованная БД как здоровое дыхание.
"Мой дядя администратор ДБ и тут не все так однозначно"© после нормализации - денормализация вполне логичный следующий шаг в некоторых ситуациях :)
DimaSimfer
05.07.2023 05:32Нубский вопрос про нормализацию и индексы: а аналитику разрешено изменять структуру БД? Добавлять свои индексы, если ему потребуется?
K_Chicago
05.07.2023 05:32+3очевидный ответ: аналитик может порекомендовать изменения струтуры БД, которые после одобрения архитектом могут быть внесены ДБАем
Areso
05.07.2023 05:32+6Обычно аналитик пишет очень-ужасный-запрос, который либо рушит БД целиком, либо сильно просаживаёт производительность. Во втором акте ДБА находит злодея. В третьем ДБА, аналитик и (если есть) разработчик БД соображают, чего бы им сделать, чтобы и бизнесу аналитика была, и простоев (или деградаций) не было.
UserAd
05.07.2023 05:32+4Аналитиков просто надо сразу отсаживать на свою реплику
GeorgeNordic
05.07.2023 05:32Это ОЧЕНЬ дорого. Датамарты им нарезать, и пусть балуются.
UserAd
05.07.2023 05:32+3Это становится дорого когда это превышает 50-60Tb данных, до тех времен это гораздо дешевле чем работа DBA + DevOPS + Monitoring engineer которые следят за тем что бы их запросы не рушили продакшн.
На больших базах там да, уже проблемы. Но большинство проектов которым уже требуются аналитики с их выборками много меньше данных имеют.
Ivan22
05.07.2023 05:32ну нормальные аналитические субд позволяют разным ролям разные ресурсные приоритеты задавать, так что любой запрос от аналитика не отъест больше X% ресурсов субд.
UserAd
05.07.2023 05:32+2Это да, но разговор то шел не об аналитических субд, а о субд для продакшна. В аналитических субд если аналитик1 затормозит запрос аналитика2 они могут выяснить свои отношения на ножах. А если в продакшн системе аналитик залочит базу в момент когда пришло много пользователей с рекламы, то бюджет уже будет потрачен без прибыли.
mayorovp
05.07.2023 05:32Только надо помнить что одна из стратегий репликации — "пока транзакция не реплицировалась на все реплики, она не может быть закомичена", и её нельзя использовать для аналитической реплики.
vk_26
05.07.2023 05:32ничего удивительного. Для аналитиков и бэкендеров в работе нужны разные компетенции СУБД. Для бэкендера выше перечисленные пункты из статьи - знать действительно важно. Транзакции, аномалии при конкурентых запросах, локи, дедлоки, как их избегать, какие индексы и для чего - все это важно понимать для бэкендера. Не понимая таких вещей можно написать код, который будет в принципе работать в дев-среде, но на проде при конкуретных запросах будут ловиться критические баги периодически, например ловить lost update. Или например, пришел на проект а там на БД уровень изоляции выставлен Repeatable Read - нужно понимать, что это, от этого зависит, как ты будешь писать код в некоторых кейсах. Т.е. все это не теоретические вопросы, а вполне себе практические важные вещи.
В статье еще не сказано про MVCC и vacuum - тоже важные штуки, которые нужно понимать.
FrozenMaster
05.07.2023 05:32которые разработчик использует в жизни считанное число раз
И чаще всего это 1-2 раза, при первом знакомстве с функцией :)
PuerteMuerte
05.07.2023 05:32+21Транзакция- это элементарная операция в базе данных.Наоборот, это группа операций, которые выполняются как одно целое. В частном случае - да, в группе может быть одна операция.
Akina
05.07.2023 05:32+1К тому же на таком определении нет места вложенным транзакциям - а они есть...
honsour72 Автор
05.07.2023 05:32Да, мне проще было запомнить с минимального примера
Да и потом так было в книге)
derwin
05.07.2023 05:32+3своим студентам объяснял как "механизм изменения одного консистентного состояние - на другое консистентное".
alexero
05.07.2023 05:32Если не ошибаюсь, это называлось «целостность» БД и, соответственно, «целостность данных».
PuerteMuerte
05.07.2023 05:32+2"Консистентность данных" и "целостность данных" в БД - действительно одно и то же, но к транзакциям это отношения не имеет. Целостность данных, это про их логическую непротиворечивость, а транзакция подразумевает переход в устойчивое "физическое" состояние, когда внесённые изменения как-то зафиксированы, видны для других транзакций и т.д. Нарушить транзакцией целостность данных - вообще не проблема, например, удалить внешний ключ по столбцу, ну пусть там userId, и ввести туда id не существующего в таблице users пользователя. Такая транзакция успешно отработает, но целостность данных будет нарушена.
Ndochp
05.07.2023 05:32+1Ой нет кажется. Транзакция от одиночного апдейта тем и отличается, что должна перевести из одного логически непротиворечивого состояния в другое. Классическая сказка в статьях о транзакциях о переводе 100$ с одного счета на другой.
Ясно, что любая заказанная дичь формально тоже будет оформлена в виде транзакции БД, но это не повод говорить, что транзакции не связаны с логической целостностью.
Скорее "транзакции это механизм БД, позволяющий обеспечить логическую целостность". Но и для разрушений вы тоже можете их использовать.Ivan22
05.07.2023 05:32предыдущий оратор прав, логическая целостность и физическая - перепендикулярные вещи. От транзакции требуется чтобы при апдейте таблицы она не сломалась на половине или не забыла индекс обновить или при откате все откатила как было. Требовать от нее проверять согласованы ли данные между собой бесполезно
0xd34df00d
05.07.2023 05:32+1Наоборот, это группа операций, которые выполняются как одно целое.
Как там дела у, например, read skew? Я последний раз писал хоть какой-то запрос к БД лет пять назад, если не больше, но до сих пор помню, что есть разные уровни изоляции, которые различаются тем, насколько именно это самое «одно целое» разваливается на части с точки зрения других «целых».
В частном случае — да, в группе может быть одна операция.
В группе по определению одна операция, ЕВПОЧЯ
PuerteMuerte
05.07.2023 05:32+1но до сих пор помню, что есть разные уровни изоляции
Есть
«одно целое» разваливается на части
Нет. Но можно выключить механизм изоляции, т.е. нарушить этот самый I в ACID, чтобы получить доступ к сырым неконсистентным данным, если это нужно (например, когда их целостность для какой-то задачи менее важна, чем производительность чтения). Сама же транзакция останется неделимой, и либо выполняется целиком, либо откатывается целиком.
Ну, ладно, сейчас формально уже не всегда так, некоторые СУБД умеют в промежуточные точки сохранения, и внутри текущей транзакции теперь тоже можно прыгать между сейвпойнтами.
В группе по определению одна операция, ЕВПОЧЯ
Какие-то у вас странные группы. Вот я сделал две операции в транзакции, это что, уже не группа?
0xd34df00d
05.07.2023 05:32Сама же транзакция останется неделимой, и либо выполняется целиком, либо откатывается целиком.
Фиксируется целиком тогда уж.
Какие-то у вас странные группы. Вот я сделал две операции в транзакции, это что, уже не группа?
Это была шутка для душнил вроде меня. Поздравляю с прохождением теста: вы не-душнила!
PuerteMuerte
05.07.2023 05:32Поздравляю с прохождением теста: вы не-душнила
Эх, льстите мне вы, сударь...
Cerberuser
05.07.2023 05:32В группе по определению одна операция
...коммутативная, ассоциативная и с единицей?
cupraer
05.07.2023 05:32+2В группе по определению одна операция
Всегда есть огромное поле для экспериментов же.
sadPacman
05.07.2023 05:32+1Раз уж затронули NoSQL и транзакции, есть смысл добавить вопрос про CAP-теорему
nin-jin
05.07.2023 05:32-2Раз уж затронули NoSQL, то неплохо было бы знать какие вообще бывают СУБД помимо реляционных.
honsour72 Автор
05.07.2023 05:32+1Данная информация предполагает знание основ языка запросов
Да в начале статьи я имел ввиду знание какие бывают СУБД
nin-jin
05.07.2023 05:32-1Типы СУБД и языки запросов никак не связаны.
honsour72 Автор
05.07.2023 05:32+1Понимаю, мне следовало указать это. И всё же я подразумеваю, что если человек идет на собеседование то ответ на вопрос "какие вообще бывают СУБД" ему известен
nin-jin
05.07.2023 05:32Практика показывает, что с вероятностью 99% кроме "SQL и NoSQL" человек ничего не знает.
PuerteMuerte
05.07.2023 05:32Но с другой стороны, знать СУБД иных типов, кроме реляционок и NoSQL (ну или гибридов тех и тех, вроде постгреса) - сейчас это уже скорее вопрос викторины на знание истории развития ИТ, и он тоже практического применения не имеет.
nin-jin
05.07.2023 05:32Нет никакого NoSQL. Есть реляционные СУБД и "все остальные, про которые я не в курсе".
PuerteMuerte
05.07.2023 05:32"Все остальные" сейчас тоже примерно одинаковые.
mayorovp
05.07.2023 05:32Посмотрел бы я как будет выглядеть SQL-запрос к графовой СУБД
p07a1330
05.07.2023 05:32Любую графовую СУБД можно представить в виде реляционной - пусть и крайне неоптимальным путем
Условно - каждое звено рафа можно считать отдельной таблицей, в которой перечислен список входящих вершин, значение и список исходящих вершин
Это очень сильно упрощение, но концептуально - оно будет работатьmayorovp
05.07.2023 05:32Я знаю что графовую СУБД можно привести к реляционной. Но графовая СУБД ценна графовыми запросами, а делать к ней реляционные запросы — это закат солнца вручную.
micronull
05.07.2023 05:32+5Есть ли преимущество у NoSQL над SQL?
Самое главное не добавили - шардирование. Это основное преимущество NoSQL перед реляционными DB.
nice17
05.07.2023 05:32+2Greenplum это же реляционная БД? Дистрибьюция (шардирование) там есть. Аналогично - Teradata, Vertica
Didimus
05.07.2023 05:32+2Что мешает шардировать реляционные бд?
Если уж партиционирование есть, например
PuerteMuerte
05.07.2023 05:32+2Концепция мешает. Шардирование предполагает, что ваши данные можно сгруппировать в непересекающиеся между собой множества, которые затем можно разложить по разным узлам, если надо. Для NoSQL это естественно - там связей между таблицами нет, каждую таблицу условно можно раздерибанить горизонтально по любому набору атрибутов, который вам подходит, и разбросать независимо по нужному количесту узлов. У реляционок же таблицы связаны между собой, и если вы какую-то основную таблицу данных и можете разделить, то все её "шарды" всё равно будут ссылаться на одни и те же справочники, например. Поэтому по независимым нодам такую структуру раскидать не получится... ну а раз так, называть мы это будем не шардированием, а партиционированием.
Didimus
05.07.2023 05:32+1Можно держать несколько копий одной и той же БД, например, справочники реплицировать между шардами, а самую тяжелую таблицу, распилить по, например, клиентам. Если у вас записи биллинга сотового телефона, то почему бы его не попилить
PuerteMuerte
05.07.2023 05:32+1Можно, но это уже не шардирование само по себе, это реплицирование, со всеми его сложностями, синхронизациями/пересинхронизациями и т.д. Шардирование же подразумевает, что вы просто взяли и раскидали базу горизонтально между равнозначными узлами, не заботясь о том, как общий справочник тарифов, филиалов и гендеров абонентов отсинкать на каждый узел при любом его изменении.
Areso
05.07.2023 05:32+11) это сложно (в реализации)
2) это имеет 1000 и 1 ограничений (см. предыдущий пункт)
3) это (может привести) к денормализации (см предыдущие два пункта)
У нас недавно был эксперимент длиной аж 6 месяцев, отказались. В результате материала набралось куча, но он будет весь похоронен в одной из трех систем документирований знаний в компании. Жаль.
Akina
05.07.2023 05:32+1110. Планировщик
Это такой встроенный механизм в СУБД, перед выполнением запроса он формирует "план" выполнения запроса.
Да ладно! Это не "планировщик", а "построитель плана запроса", иногда прозываемый также оптимизатором, ибо оптимизация плана выполнения является неотъемлемой частью его работы.
А планировщик - это именно планировщик, инструмент, который выполняет по заданному расписанию заданные процедуры. И который, кстати, есть далеко не в каждой СУБД. Вот, например, в MySQL - есть. Event Scheduler прозывается. Но в большинстве СУБД - отсутствует, и для периодического выполнения SQL-кода приходится использовать внешний планировщик.
honsour72 Автор
05.07.2023 05:32Спасибо, буду знать эту неточность!
cadmi
05.07.2023 05:32+17Не надо никаких спасибо. Это любители mysql принесли 8 главу своей документации, где эта штука у них называется optimizer и состоит из нескольких частей (query planner, собственно query optimizer и т.д.)
В postgresql (и других базах) это называют planner и в русскоязычном IT в аспекте БД (не только postgres) принято слово именно что "планировщик". Ну а то, что некоторые любители mysql словом "планировщик" с давних пор называют почему-то event/job scheduler, вас в общем случае волновать не должно, это у них там своя атмосфера.
Именно на собеседованиях щеголять столь вольными трактовками общепринятной терминологии может быть чревато, у собеседующего приподнимется бровь и посыплются уточняющие вопросы.
BASic_37
05.07.2023 05:32Ну так и вы туда же не скатывайтесь. В MS SQL тоже вполне себе "оптимизатор запросов" и джобы...
Stawros
05.07.2023 05:32И в оракле тоже оптимизатор, а планировщиком Oracle Scheduler называют, который джобиками оркестрирует.
BASic_37
05.07.2023 05:32+1Так это что же получается... Во всех "нормальных" СУБД оптимизатор - это оптимизатор, а эти любители postgresql эту штуку назвали планировщиком и теперь всему свету пытаются навязать свое название и на собесах везде щеголяют столь вольным трактовками общепринятой терминологии... )))
Akina
05.07.2023 05:32Вопрос не о любви к MySQL, а о точности и однозначности терминологии.
Если на собеседовании попросят рассказать о планировщике - вот так, без какого-либо уточнения,- о чём Вы станете рассказывать? Поди угадай.. А если вопрос будет о построителе плана выполнения запроса - то гадать не требуется, вопрос совершенно однозначный.
В postgresql (и других базах) это называют planner
Ну Вы же сами ссылку даёте! что ж половину отрезаете-то? или если написать, что "В postgresql (и других базах) это называют Planner/Optimizer", то не прозвучит?
Newbilius
05.07.2023 05:32-1Зачем "угадывать", если можно "спросить"?) Это ведь не редкая ситуация, когда короткий термин означает разное в разных контекстах.
Akina
05.07.2023 05:32Дело в том, что у почти любого термина в конкретном контексте есть "дефолтное" значение. Статья - про собеседование для разработчика-"питониста". Согласитесь, что в таком собеседовании в принципе не бывает "Мы не спрашиваем за питон, все вопросы исключительно про SQL". А когда так, то вопрос о планировщике - это вопрос о планировщике либо внешнем (операционной системы или третьей фирмы), либо встроенном в некое приложение/библиотеку/среду/прочее. Но уж никак не о построителе/оптимизаторе запросов.
А ещё - про термин, использованный в тексте статьи и не разъяснённый ранее явно в разделе терминов и определений, трудно "спросить".
ErshoffPeter
05.07.2023 05:32+2Как создавать индексы и какие они бывают написали, а как из использовать и на что они влияют (замедляют, например, insert/update) ни слова.
Про статистики, без которых оптимизатор работать не может тоже ни слова.
А ещё есть в природе кластерные индексы (не уверен насчёт PostreSQL)...
honsour72 Автор
05.07.2023 05:32Спасибо! Да я тут по вершкам прошёлся совсем без подробностей практических, что не слишком хорошо)
shurutov
05.07.2023 05:3213. Что будет если сделать EXPLAIN ANALYZE DROP TABLE POSTGRES
Table not found будет. Если вы ранее не создавали эту таблицу специально. Что же до БД - это с какого перепугу она вся из себя такая главная? Обыкновенная БД, не более того.
mayorovp
05.07.2023 05:32Ага, только используется по умолчанию при подключении большинством клиентов, а так обыкновенная БД которую можно удалить...
mixsture
05.07.2023 05:32+2Я всегда выделял 2 основных. Это Шардирование и Репликация.
Партиционирование. Как шардирование, только не между инстансами, а между файлами (соответственно, дает возможность раскидать нагруженные части на одни накопители, ненагруженные — на другие).
Но к слову, существенная часть описанного в статье скорее относится к специализациям, вроде DBA.
acordell
05.07.2023 05:32+1Ну и как собеседования с такой шпаргалкой?
По нормализации, это, конечно сильно. Логическая избыточность тут вообще не к месту. Слабо ее определение дать? Или хотя бы в инете глянуть, что избыточность информации и логическая избыточность даже и не сестры. Смело для шпаргалки. Тут же за язык же поймают. И какой вывод сделают? Что набрался умных слов, толком в них не разобравшись?
Да и пример с телефонами оправдан только, если в системе человек может иметь несколько телефонов (а такое совсем не в каждой нужно, внутренний телефон в компании, как пример тому), и эти телефоны ему запихивают в одно поле через разделитель. Стоит, все же, глянуть чем первые три нормальных формы друг от друга отличаются и с примерами станет просто.
Ну и самый распространенный вопрос по типам JOIN-нов в SELECT странно даже не помянуть.
Но в целом статья норм, кмк. Особенно для первого раза.
honsour72 Автор
05.07.2023 05:32+2Спасибо, для такой обратной связи как раз и старался. Вы всё верно написали по избыточности - пойду более углубленно изучать эту тему!
Tangeman
05.07.2023 05:32-3Если человеку нужна шпаргалка для ответов на эти вопросы, то к работе с БД его допускать не стоит.
Я на собеседованиях показываю SQL и прошу объяснить что будет в базе после его выполнения а также прошу объяснить как человек решит конкретную задачу, разумеется с подвохом (которые требуют знаний о ACID/MVCC, индексах и разных уровней изоляции) - сразу становится ясно кто зубрил а кто знает.
nin-jin
05.07.2023 05:32+10Выкладывайте задачу, поломаем голову все вместе.
inkelyad
05.07.2023 05:32А можно мне?
Обработка запросов с подтверждением "еще работаю"
Т.е.
1) Если в процессе обработки запроса была ошибка - все откатывается и следов в базе быть не должно.
2) Если запрос пришел повторно (такой-же по бизнесу, но с другим серийником по времени) до окончания обработки - сразу посылается ответ "уже обрабатывается, смотри запрос ID..", а не происходит засыпание на какой-нибудь блокировке.
3) Параллельной обработки одного и того же (потому что повторный запрос совсем не видит, что там первый успел сделать из за того, что тот еще ничего не закоммитил) быть не должно.
4) Read uncommited режим недоступен.
BASic_37
05.07.2023 05:32+2Делаем табличку-журнал с полями "id", "вид бизнес-задачи", "время начала", "время окончания", "id сеанса" + какой-нибудь "desc" по вкусу
При запуске работает следующая логика (можно инкапсулировать в хранимку):
1 открываем транзакцию
2 идем в наш журнал и ищем незаконченную(время окончания is null) запись с нашим типом бизнес-задачи
3
Если не нашли: вставляем новую нашу запись о старте
Если нашли, то берем id-сеанса и проверяем его существование. Если его уже нет завершаем(ставим дату окончания) с отметкой в desc что сеанс умер и вставляем нашу новую запись о старте. Если сеанс еще работает, то возвращаем соответствующее сообщение (в этом варианте - тут конец. закрыть транзакцию конечно надо).
4 Закрываем транзакцию
5 Выполняем нашу бизнес-задачу в новой транзакции, в случае ошибки - откатываем
6 Ставим отметку в журнале об окончании, можно в desc что-то написать
(id сеанса скорее всего потребует еще дату начала сеанса для однозначной идентификации, в общем это особенности субд, в тексте упростил просто до id-сеанса)Примерно так, если правильно понял задачу. Вроде должно работать
inkelyad
05.07.2023 05:32Если нашли, то берем id-сеанса и проверяем его существование.
Я так понял сеанс в данном случае - это сеанс обработки.
Тогда - проверяем в чем и как? Потому что прошлое сообщение на какой-то на другой узел кластера или вообще в другой дата-центр ушло. Поднимать еще что-нибудь рядом для хранения сеансов, когда у нас и так база вроде как есть?
А как это 'что-нибудь' заполнять и инвалидировать? Когда сеанс уже умер, а запись об его существовании осталась?
5 Выполняем нашу бизнес-задачу в новой транзакции, в случае ошибки - откатываем
6. Ставим отметку в журнале об окончании, можно в desc что-то написать
Между 5 и 6 все умирает. После восстановления cледующее повторные сообщения будут пытаться сделать работу заново, хотя она уже сделана.
BASic_37
05.07.2023 05:32+2Потому что прошлое сообщение на какой-то на другой узел кластера или вообще в другой дата-центр ушло
Про распределенную систему не было ни слова, поэтому решение предложено в пределах 1 базы
При распределенных вычислениях, думаю без координатора не обойтись, нам нужен сервис который возьмет на себя эту роль. Таким образом мы выносим обеспечение требований AСID с уровня БД, на уровень системы.Между 5 и 6 все умирает. После восстановления cледующее повторные сообщения будут пытаться сделать работу заново, хотя она уже сделана.
Думаю придется выбирать компромисс:
-Закрываем транзакцию после 6 пункта, но тогда все ждут пока идет работа...
-Либо, что скорее всего предпочтительнее: быть готовым к повторной обработке (предусмотреть такую вероятность).inkelyad
05.07.2023 05:32+1Про распределенную систему не было ни слова, поэтому решение предложено в пределах 1 базы
Виноват.
База и имелась в виду одна. Просто обработчиков сообщений много, которые все к одной этой базе прицеплены.
BASic_37
05.07.2023 05:32Тогда ответ остается тот же с пояснением по "Между 5 и 6 все умирает". Сеансы имелись ввиду субд-шные, вытаскиваются запросом к системным таблицам/представлениям. если бд одна, то логично воспользоваться ее механизмами.
inkelyad
05.07.2023 05:32Продолжаем придираться. Первый повтор пришел слишком быстро - до того как пункт 4 от первого сообщения пришел. Как механизм блокирования устраивать будем?
BASic_37
05.07.2023 05:32Он будет висеть в ожидании пока не случится пункт 4. Т.е. работу не начнет. Таблица-журнал заблокирована же.
BASic_37
05.07.2023 05:32А у вас есть готовое решение этой задачи? Если есть, хотелось бы узнать его
inkelyad
05.07.2023 05:32Нет. Уже несколько раз разным разработчикам в специализации по базам данных задавал - как-то то ли ответа уже не помню, то ли понравившегося мне не было.
nikolayv81
05.07.2023 05:32-1К сожалению в общем случае эта задача неразрешима
Основная проблема в отсутствии координатора, если мы предположим что мы используем oracle (возможно enterprise) то мы можем использовать встроенные механизмы (очереди сообщений, обработчики, scheduler) но это будет платформенно зависимое решение, в принципе можно конечно попытаться завязаться на триггеры, и реализовать обработчик очереди внутри почти любой СУБД(с использованием некоторого механизма запуска процедур по событию) но это все равно будет костыль, и останутся вопросы с отработкой, к примеру, случаев разрыва связи и потенциальные простои сервиса. Т.к. есть в вашей постановке неопределенность касающаяся пункта "уже выполняется смотри запрос id" т.к. ничего не мешает запросу с id заселится после этого ответа.
nomhoi
05.07.2023 05:32+1Способ с номерами версий.
Создаем журнал для фиксации запросов. В журнал добавляем числовое уникальное ненулевое поле "номер версии" для блокировки создания записи с одинаковым номером версии, поле для статуса операции [started, success, failed] и другие поля, например, время старта, время окончания операции.
При добавлении записи в журнал сначала проверяем, не ли уже запущенной такой-же операции, если нет, то находим последнюю запись и номер версии, увеличиваем номер версии на единицу, добавляем запись в новым номером версии в журнал. Если паралельно пришло несколько запросов с таким-же номером, то, естественно, сможет добавиться только один, на остальных поднимется исключение.
Если запись добавилась, то в эту запись пишем статус "started", время начала операции и т.д., запускаем нужный процесс, отдаем пользователю ответ, что операция стартовала при необходимости.
Если запись не добавилась, поднялось исключение, то выполняем запрос для получения информации из записи с таким-же номером версии, отдаем пользователю, что операция уже выполняется.
По-окончании операции изменяем статус операции, устанавливаем время окончания операции и т.д.
Более подробно об этом способе здесь:
https://github.com/cosmicpython/book/blob/master/chapter_07_aggregate.asciidoc
nin-jin
05.07.2023 05:32Я бы делал так:
Клиент создаёт задачу в своей локальной базе.
База синхронизируется с обработчиками.
Обработчик видит новую задачу и начинает обработку, чекинясь в задаче.
База синхронизируется между обработчиками.
Если обработчик видит, что другой обработчик зачекинился раньше, то отменяет задачу.
Перед обработкой задачи вставляется небольшая задержка на синхронизацию базы.
Падение обработчика приводит к его перезапуску и продолжению обработки задачи.
По завершению, обработчик пишет в задачу резолюцию
База синхронизируется с клиентом и он видит её статус.
nomhoi
05.07.2023 05:32Способ с шиной сообщений и редиса. Событийно-ориентированный.
Посылаем команду на запуск процесса по шине. Обработчик принимает команду, проверяет в редисе в соответствующем set или hash контейнере эту команду, если ее в наборе нет, то добавляет ее в набор и стартует процесс, сообщает пользователю о старте процесса. Процесс заканчивается, убирает в редисе из набора команду, сообщает пользователю.
Если в наборе уже есть команда, то значит процесс уже выполняется, тогда обработчик получает информацию из этого hash-контейнера и отправляет отлуп пользователю.
BASic_37
05.07.2023 05:32+3(ну или поправишь неточности в существующих)
ну поехали....
В первом пункте сначала говорите про избыточность, потом приводите довольно неудачный пример, который может быть совсем не про избыточность (если номера не уникальны)...
Ну и потом еще фразы: " мы разбиваем таблицу на части или делаем декомпозицию, приводя таким образом таблицу к нормальной форме" и "декомпозиция - это одна из вариаций нормализации"... Декомпозиция это не вариация нормализации, а всего лишь инструмент, а читая ваш текст создается впечатление что разбить данные на таблицы = нормализация...Так MongoDB может выигрывать у PostrgeSQL в запросах, которые подразумевают много связей и за которыми постгрес полезет в другие таблицы
Тут вы отождествляете РСУБД и реляционную модель данных, что немного не верно... А если в реляционной СУБД хранить данные в не нормализованном виде, скажем в 1 таблице id и json с объектом, тогда уже не надо лазить по таблицам и все становится не так однозначно...
Транзакция - это элементарная операция в базе данных.
Однако транзакция может состоять и из нескольких операцийЧто? Транзакция - не элементарная операция, а атомарная тогда уж...
Повлиять на скорость выполнения запроса можно различными способами. Я запомнил их так: изменить зам sql-запрос, обновить статистику планировщика, денормализация и 4 вариации изменения параметров планировщика:
и дальше не 4, а 7 пунктов частично повторяющих только что сказанное...
Обновление статистики - это какая-то разовая оптимизация, это вопрос регламентного обслуживания или вы предлагаете перед каждым запросом обновлять статистику?денормализация: создание временных таблиц или создание индексов
Вы уверены что это про денормализацию?
изменение параметров планировщика...
Слышал про такое... ) На практике ни разу не приходилось вмешиваться в работу планировщика (правда это были не postgresql), обычно планировщик лучше знает как ему выбирать данные, а все проблемы решаются исправлением своих рук и мозгов
Какие есть концепции масштабируемости БД Я всегда выделял 2 основных.
Ожидал сейчас прочитать: вертикальное и горизонтальное, а тут...
SELECT в интернетах выполняются во много раз чаще, чем INSERT'ы, на этом фоне репликация выглядит привлекательнее
не увидел прямой связи...
Все выше сказанное относится к теме "Повышение отказоустойчивости"
да ну? а я знаю примеры решающие задачи оптимизации и даже репликации для ограничения доступа в другой сетевой сегмент.
Я рекомендую её к прочтению разработчикам любого уровня
Прям не знаю... книга такая плохая или вы недопоняли материал...
honsour72 Автор
05.07.2023 05:32Спасибо за столь объёмный комментарий. Некоторые ответы составлены с "выжимками" из книги. А некоторые полностью взяты оттуда, поэтому Вам точно рекомендую её прочесть: уверен, что Вы поймёте её более правильно!
xukapy
05.07.2023 05:32+1SELECT в интернетах выполняются во много раз чаще, чем INSERT'ы, на этом фоне репликация выглядит привлекательнее
не увидел прямой связи...
Видимо, имелось ввиду, что реплики обычно READ ONLY и писать можно только на мастера.
Ndochp
05.07.2023 05:32+2Кажется исходя из того, что это копи запись считается завершенной, когда попала во все. И это более тормозное требование, чем пропускная способность на запись одного мастера.
Но в таком случае не понимаю фразы из статьиТо есть по сути это полное копирование СУБД на другой сервер.
Таким образом контроллер сам будет решать в какую ноду и что ему записывать.Что-то одно должно быть, или полная копия, или решать куда что писать. Для полной копии ничего решать не надо — пиши во все ноды.
grigorym
05.07.2023 05:32+5Автор молодец, что постарался. В попытках что-то объяснять другим и рождается собственное углублённое понимание предмета, это я вам как преподаватель ответственно заявляю. Однако сейчас у статьи недостатков больше, чем достоинств, настолько, что даже руки не поднимаются - исправлять косяки придётся буквально в каждом вопросе.
Я вам хочу посоветовать другое. Не гонитесь за составлением шпаргалок по тому, о чём могут спрашивать на собеседованиях. Это всё равно что строить своё образование, изучая те вопросы, которые вам встретились при просмотре телепередачи "Своя игра" с Онотоле. Вопросы на собеседованиях очень часто бессистемные, они могут быть просто о том, что вопрошающему конкретно сейчас пришло в голову, и он вообще не факт, что сам умеет спрашивать о главном, а не второстепенном. Приведу только один пример у вас - про индексы. Вас, видимо, спрашивали о том, какие они бывают и каким синтаксисом создаются. Но поверьте, это совсем не самое главное из того, что стоит знать про индексы. Гораздо важнее понимать их суть, что они из себя представляют внутри - чтобы понимать, почему какой-то запрос индексом ускоряется, а какой-то нет - а у вас про это вообще ничего не сказано. Если собеседующий будет от вас требовать в первую очередь синтаксис, а не суть - бегите оттуда, пока не поздно.
is40009
05.07.2023 05:32Встречал я как-то задачку на собесе, решить не смог, хотя по словам собеседующего, это базовый уровень. Кто хочет, может попытаться с ней справится.
Есть таблица 3 полями, в ней хранится id, время посещения сайта и ip:
id | ip | time(timestamp)
Нужно вывести все уникальные ip адреса, и время третьего посещения. (Вложенные запросы делать нельзя)PuerteMuerte
05.07.2023 05:32+1Ну это какой-то совсем лютый базовый уровень, как по мне, если без вложенных запросов.
WITH a AS ( SELECT ROW_NUMBER() OVER (PARTITION BY ip ORDER BY time) AS RowNumber, ip, time FROM mytable ) SELECT ip, time FROM a WHERE RowNumber = 3Stawros
05.07.2023 05:32+2А CTE разве не будет считаться подзапросом? А ещё у вас выпали IP адреса, у которых было меньше 3-х посещений.
PuerteMuerte
05.07.2023 05:32А CTE разве не будет считаться подзапросом?
Формально есть подзапросы, есть CTE, ну т.е. вроде как условию задачи соответсвует, хотя с точки зрения плана выполнения один фиг. Ну и лично я затруднился бы сказать, что хотел бы видеть вопрошающий, запретив использовать и обычные подзапросы, и СТЕ.
А ещё у вас выпали IP адреса, у которых было меньше 3-х посещений.
Ну да. На собеседовании я бы уточнил, надо ли их выводить :) Вполне вероятно, что и не надо, т.к. дополнительных кунштюков SQL это не требует, а писанины больше.
Stawros
05.07.2023 05:32+1На оракле можно было бы решить через model, например -
Запрос
with journal as ( select 1 as id, '127.0.0.1' as ip, to_timestamp('01.01.2010 10:10','dd.mm.yyyy HH24:mi') as time from dual union all select 2 as id, '8.8.8.8' as ip, to_timestamp('02.01.2010 11:15','dd.mm.yyyy HH24:mi') as time from dual union all select 3 as id, '127.0.0.1' as ip, to_timestamp('03.01.2010 12:20','dd.mm.yyyy HH24:mi') as time from dual union all select 4 as id, '127.0.0.1' as ip, to_timestamp('04.01.2010 13:25','dd.mm.yyyy HH24:mi') as time from dual union all select 5 as id, '8.8.8.8' as ip, to_timestamp('05.01.2010 14:30','dd.mm.yyyy HH24:mi') as time from dual union all select 6 as id, '127.0.0.1' as ip, to_timestamp('06.01.2010 15:35','dd.mm.yyyy HH24:mi') as time from dual union all select 7 as id, '8.8.8.8' as ip, to_timestamp('07.01.2010 16:40','dd.mm.yyyy HH24:mi') as time from dual union all select 8 as id, '127.0.0.1' as ip, to_timestamp('08.01.2010 17:45','dd.mm.yyyy HH24:mi') as time from dual union all select 9 as id, '127.0.0.1' as ip, to_timestamp('09.01.2010 18:50','dd.mm.yyyy HH24:mi') as time from dual union all select 10 as id, '1.1.1.1' as ip, to_timestamp('10.01.2010 19:55','dd.mm.yyyy HH24:mi') as time from dual union all select 11 as id, '1.1.1.1' as ip, to_timestamp('11.01.2010 20:55','dd.mm.yyyy HH24:mi') as time from dual ) select ip, third_visit from journal j model return updated rows -- будем брать только те строки, что попали под правила partition by (j.ip) dimension by ( -- нам понадобится номер посещения по IP row_number() over (partition by j.ip order by j.time, j.id) as num_visit, -- и всего посещений count(*) over (partition by j.ip) as total_visits ) measures (cast(null as timestamp) as third_visit, j.time) rules ( -- возьмём дату третьего посещения, если всего посещений >= 3 third_visit[num_visit = 3, total_visits >= 3] = time[cv(), cv()], -- возьмем пустую дату для первой строки по IP, если всего посешений меньше 3 third_visit[num_visit = 1, total_visits < 3] = null ) /Вывод:
IP THIRD_VISIT --------- ------------------------------ 1.1.1.1 127.0.0.1 04-JAN-10 01.25.00.000000 PM 8.8.8.8 07-JAN-10 04.40.00.000000 PMЯ считал, что нужно найти третье посещение, а не третье посещение за день. Но это что-то нифига не базовый уровень :D Базовый уровень - это подзапрос с аналитикой.
playermet
05.07.2023 05:32+1Попробовал в SQLite без CTE, извиняйте если не запустится в других СУБД.
SELECT DISTINCT ip, nth_value(time, 3) OVER win AS time FROM visits WINDOW win AS (PARTITION BY ip ORDER BY time RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
ArtyL
05.07.2023 05:32+1Ну во первых EXPLAIN ANALYZE DROP ... выдаст ошибку, потому что для DROP explain сделать нельзя, а вот услышать на собеседовании вопрос как сделать ничего не сломав EXPLAIN ANALYSE delete from ... это вполне возможно, и иногда даже нужно. Правда ответ в документации по EXPLAIN есть, но кто же ее читает :-) (TLDR: начать транзакцию, а после сделать rollback)
Vika_Fedoseenko
05.07.2023 05:32Интересно, при собеседовании в какие компании у python-разработчика спрашивают так много про SQL? Больше похоже на собеседование администратора баз данных (кстати, в профиле автора указано, что он как раз админ БД).
ArtyL
05.07.2023 05:32Python разработчик почти в 100 процентах случаев сталкивается с SQL, и это не много а самые необходимые основы. Для ДБА это тоже необходимые знания, но там список вопросов побольше будет.
honsour72 Автор
05.07.2023 05:32Эти вопросы собраны со всех моих python-собесов. Около дюжины их было. И - нет - у меня в профиле не совсем так написано - смотрите внимательнее - это ловушка для админов БД)
Sephirothus
05.07.2023 05:32+1В секции индексов я бы дополнил патерн создания индекса при помощи CONCURRENTLY. Из разряда когда нужно создать индекс на нагруженной записью таблице не блокируя ее. Но CONCURRENTLY нельзя выполнять в транзакциях.
honsour72 Автор
05.07.2023 05:32Спасибо за этот комментарий, это очень ценная информация. Всякий раз, когда захожу в комменты этой статьи - узнаю что-то новое.
Leetc0deMonkey
Понимаете, современное собеседование оно не про работу. Это что-то вроде викторины или спортивного состязания среди пары сотен кандидатов, с главным призом в виде трудоустройства.