Comments 118
Например, в вашем первом примере, где вы запрашиваете данные заказа, order_id будет разным, но сам запрос всегда один и тот-же и именно он по-идее, должен доставаться из кеша, подставляться параметр и вперед.
Допустим, у нас каталог товаров. У товаров множество полей, будь то наименование, производитель, цена, цвет, вес, и ещё с десяток. Откройте любой каталог — вот вам и пример.
Разные пользователи настраивают фильтры по-разному.
Это приводит к появлению разных вариаций условия в блоке WHERE
.
Каждая вариация пораждает отдельный запрос
Число вариаций запросов — факториал от числа категорий.
Тогда для 3 категорий у нас 6 вариантов запросов, для 4 уже 24, для 5 уже 120.
Для каталога 5 категорий для фильтрации — это ничто, их там будет гораздо больше.
Умножаем на варианты сортировки — и получаем радостное N*(N!)
Очевидно, при таком числе вариантов стратегия кэширования целого запроса приведёт к OOM.
Именно о таких ситуациях, где число вариантов запроса огромно, я и называю комбинаторным взрывом
Хотя есть еще подозрение, что количество наиболее часто используемых комбинаций все-таки будет не слишком большим, так что запросы для них будут всегда оставаться внутри кэша и работать быстро, а какие-то более редкие комбинации будут вытесняться из кэша со временем и чаще перегенерироваться, но из-за их редкости может и не будут играть большой роли.
Ну и до ООМ дело не должно дойти — все-таки одна из задач кэша не доводить до ООМ и выкидывать из хранилища что-то, что давно не использовалось, когда надо положить в кэш что-то новое, а места больше нет.
2) Сколько времени уходит на материализацию запроса, а сколько съедается на транспорте к БД? Мы оптимизируем 5% от общего времени запроса?
3) Секунда на результат в UI — это нормально. Если запрос в БД через ORM обрабатывается в пределах 500мс, то оптимизация — это пустая трата времени. Но мой опыт с EF.Core говорит, что 20-30мс достижимо.
Короче, гораздо проще выкинуть питон и взять нормальный стэк, где и ORM есть и производительность нормальная.
Люди не роботы и ищут примерно одинаково. Никто не ищет автомобили по весу, а ноутбуки по материалу корпуса (ищут, но можно пренебречь). Равно как и запросы с десятком параметров
Нотация О-большое для самых маленьких
2) Сколько времени уходит на материализацию запроса, а сколько съедается на транспорте к БД? Мы оптимизируем 5% от общего времени запроса?
3) Секунда на результат в UI — это нормально. Если запрос в БД через ORM обрабатывается в пределах 500мс, то оптимизация — это пустая трата времени. Но мой опыт с EF.Core говорит, что 20-30мс достижимо.
О, ещё один любитель мерять производительность секундомером. Я рассказывал, как можно не вычислять вообще. Вам приходило в голову, что сэкономленное время CPU может быть израсходовано на другой запрос?
Я тут пол статьи рассказывал, что самый страшный затык по производительности — это затык в БД, как важно выжимать из SQL-запроса всё, что можно, рассказывал про SQL-хинты (бывает, БД некорректно оценивает cardinality, что приводит к трэшовому плану запроса). Говорил о том, что приложение можно масштабировать, а БД нельзя.
И что? Мне предлагают другую ORM. На другом языке. Уровень, который можно отмасштабировать.
Блин, если бы хоть слово было сказано про возможности генерации SQL в EF. Так нет же, только про скорость исполнения и секундомер.
EF Core хвалят те у кого база просто маленькая. На реальных данных они незаметно переходят на хранимки. Скорость генерации сложного запроса в EF Core просто отстойная. Им приходится перелопачивать код чтобы упростить свои визиторы чтобы итерации не гоняли ту же песню миллионы раз.
Об оконных функциях тоже можно забыть. Parameters Sniffing возможен и никак не обходится. DML операции спрятаны через ChangeTracker — еще тот прикол подтянуть 1000 записей чтобы у них поменять одно поле.
Если уж сравнивать SQLAlchemy с .NET ORM, то только с linq2db, он не прячет от вас SQL и быстр как молния. Также он сам проводит оптимизацию запросов вплоть до того что может поотбрасывать незначимые JOIN.
future_select кэширует куски SQL-запросов, из которых итоговый запрос собирается очень быстро
Этот вариант я обязательно задействую, когда дело будет пахнуть комбинаторным взрывом.
Очевидно, при таком числе вариантов стратегия кэширования целого запроса приведёт к OOM.
Эмм, вы подразумеваете кеширование именно строки запроса, а не его результатов? А зачем ее вообще кешировать?
Да, я подразумеваю кэширование строки запроса. Сборка запроса достаточно дорогая. При этом строка запроса переиспользуется в хвост и в гриву.
Кэширование же результата запроса абсурдно. Я много раз в статье говорил об инвалидации кэша, и кэширование результатов запроса лишь усугубляет эту проблему.
Я не говорю о том, что кэши не нужны. Я говорю о том, что кэши нужно применять, задействуя верхний мозг.
Кэширование же результата запроса абсурдно. Я много раз в статье говорил об инвалидации кэша, и кэширование результатов запроса лишь усугубляет эту проблему
Зря вы так. Есть очень много данных где отставание на несколько минут совсем не критично и при этом может снизить РПС в базу в разы, а то на порядки.
Кеширование результата запроса это отличная практика для ускорения ответа и снижения нагрузки.
Как сказанное вами и мной противоречит друг другу?
Я говорил, что кэши не нужны вообще? Нет, я сказал, что их надо применять, подумав головой.
Кэширование же результата запроса абсурдно. Я много раз в статье говорил об инвалидации кэша, и кэширование результатов запроса лишь усугубляет эту проблему.
Это ваша цитата?
Вырвать из контекста кусок фразы и потом им оперировать. Норм способ ведения дискуссии
Хотя мне самому стоило пояснить. Кэши необходимы при пагинации — пользователь будет не сильно доволен, когда по мере подгрузки новой страницы у него будет странный трэш с дубликатами записей
Кэши для редко меняющихся данных — это прекрасно
Кэши для часто запрашиваемых данных — это прекрасно
Кэши важно не забывать инвалидировать
Но впердоливать кэши во все дыры без раздумий — это звездец
Вырвать из контекста кусок фразы и потом им оперировать. Норм способ ведения дискуссии
Этот кусок фразы вы выделили отступами. Логично предположить что он законченный и имет смысл сам по себе.
Кэши для редко меняющихся данных — это прекрасно
Кэши для часто запрашиваемых данных — это прекрасно
Кеши хороши практически везде где возможен запрос одних и тех же данных несколько раз за небольшой промежуток времени. Это большая часть всех запросов к бд.
Исключения есть, но они достаточно редки.
Кэши важно не забывать инвалидировать
Инвалидировать кеш по времени и по объему опять таки достаточно для большей части всех кешей.
Иногда даже можно делать супер локальный кеш прямо в своем приложении. И пусть он расходится на разных инстансах.
Добавляем прилипание пользователя к бекенду и какой-нибудь закешированный локально на бекенде список бестселлеров не будет изменяться после каждого ф5. А то что он у другого пользователя другой, так то ладно.
Но впердоливать кэши во все дыры без раздумий — это звездец
Подумать перед тем как что-то сделать всегда полезно.
Я вроде и не предлагал "впердоливать кэши во все дыры без раздумий", и никто другой не предлагал, откуда вы это взяли?
Кроме собственно строки обычно кэшируется подготовленный запрос (prepared statement). Сборка строки может быть сравнительно недорогой, но вот prepare — может быть очень дорого, для этого и нужен кэш запросов.
Число вариаций запросов — факториал от числа категорий.
Тогда для 3 категорий у нас 6 вариантов запросов, для 4 уже 24, для 5 уже 120.
Почему факториал? С точки зрения выполнения SQL порядок вычисления условий во WHERE неопределен и результат от него не зависит, т.е. для варианты с перестановками можно исключить простой сортировкой выбранных категорий перед построением запроса, и в итоге у нас остаётся 3^(число категорий) — потому что каждая категория может искаться на наличие (=), отсутствие (<>) или вообще не иметь значения — т.е. при числе категорий до 7 там далеко до комбинаторного взрыва (2187 вариаций всего — это ни о чём с точки зрения памяти), и уж по любому там очень далеко до факториала.
Варианты сортировки могут включаться независимо от условий поиска так что нет необходимости их кэшировать совместно с вариантами поиска (а если не могут — то это косяк ORM).
Если учесть что в реальности люди всё же ищут по ограниченным вариантам, то вряд-ли кто-то кроме пьяного тестировщика устроит комбинаторный взрыв.
Но если у пользователя есть полная свобода и он может самостоятельно указывать AND и OR для категорий — тогда ситуация меняется, но я вот сходу не могу вспомнить где такое вообще отдают пользователю — кроме, разумеется, очень специальных случаев.
Проверил для числа 4. Не факториал. И не a^N
:
[0 0 0 0]
[1 0 0 0]
[0 1 0 0]
[0 0 1 0]
[0 0 0 1]
[1 1 0 0]
[1 0 1 0]
[1 0 0 1]
[1 1 1 0]
[1 1 0 1]
[1 1 1 1]
S = N + (N-1) + (N-2) + ... + 1 = N^2 - N - 1
Для тройки там куб будет. То есть N^a
Видимо мы как-то иначе вариации считаем. В частности, почему в вашем примере нет 0011, 0101, 0110 и 0111? Должно ж 16 вариантов получится — если каждый "бит" это наличие условия для параметра или его отсутствие, параметров 4 и условие только одно.
3^n у меня получилось для случаев (parm = col), (parm <> col) и отсутствия параметра — т.е. три возможных варианта для каждого (вместо двух если бы было только "=").
Я не настоящий эскуэльщик, поэтому хочу спросить того кто утверждает, что он настоящий. Как по-вашему вообще возможно написать такой запрос который имеет сразу все условия фильтрации, сортировки и группировки которые разрешены в гуе, и соответственно из гуя передавать только то что юзер выбрал что фильтровать, как сортировать группировать. Соответственно наш запрос получает параметры и по условию если что то нулл (юзер этот фильтр, сортировку, ... не применил), то соответствующая часть запроса просто вырезается ну или в результате ничего не делает. Тогда весь комбинаторный взрыв уже и не таким уж и взрывом будет, а как раз линейно зависеть от n параметров. Да логика выключения части запроса переедет в СУБД, но что то мне подсказывает, что эта экстра логика стоит почти 0 посравнению с непосредственно работой с данными.
Если это в принципе возможно, то почему вы выбираете динамическое создание запроса, какие минусы?
Как по-вашему вообще возможно написать такой запрос который имеет сразу все условия фильтрации
def where_clause_iter(self, params: frozenset):
has_price_min = 'price_min' in params
has_price_max = 'price_max' in params
has_price_between = has_price_min and has_price_max
if has_price_between:
yield Product.price.between(
bindparam('price_min', required=True),
bindparam('price_max', required=True),
)
elif has_price_min:
yield Product.price >= bindparam('price_min', required=True)
elif has_price_max:
yield Product.price >= bindparam('price_max', required=True)
if 'color' in params:
yield Product.color == bindparam('color', required=True)
if 'vendors' in params:
yield Product.vendor.in_(bindparam('vendors', required=True))
...
def where_clause(self, params: frozenset):
where_clause_gen = self.where_clause_iter(params)
where_clause = next(where_clause_gen)
for next_clause in where_clause_gen:
where_clause &= next_clause
return where_clause
Вроде, изячно. То же самое с сортировками, группировками и прочим
Извиняюсь за плохо поставленный вопрос. Я не сомневаюсь в том что это можно написать на орм или даже вручную на любом процедурном языке. Вопрос относился к возможности написать логику отключения фильтраций и т.д. на самом эскуэле, декларативном языке. Я знаю, что в скуле есть when/case. Чего я не знаю так это достаточно ли этого + возможно других средств, что бы выразить ваш код выше напрямую в эскуэле.
Грубо говоря на пальцах, создать временную таблицу whete_clause где будут строки в которых хранятся соответствующие куски фильтрации, а потом по списку параметров, выгребаем соответствующие строки и конкатенируем все до кучи. Ну т.е. буквально все тоже самое что у вас на питоне генератор только непосредственно в базе.
Ну это так идея на пальцах, в идеале конечно что бы все строилось без доп таблиц, а сама структура запроса позволяла отключать его части в зависимости от переданных параметров. И ещё раз все на чистом эскуэле без использования процедурных языков.
Почему такой вопрос возник, а из опыта что любая СУБД работает со строками (парсит, конвертирует, конкатенируют ...) существенно быстрее чем высокоуровневые языки за счёт более эффективного управления памятью. И я не удивлюсь, что тот же самый(по функциональности) билдер запросов который вы написали на алхимии будет сильно быстрее по скорости на эскуэле, и тогда проблемы кэширования в большинстве случаев не будет, а там где что то нужно закэшировать лучше сразу субдшный байт код сохранить прямо в самой СУБД чем голый текст, а потом СУБД будет опять его парсить каждый раз.
(я был сильно неправ)
В PostgreSQL мы так делаем. Приходится отключать формирование генерализованных планов, иначе получается фигня. При построении плана (негенерализованного) учитываются биндинг-параметры и всё, что может быть вычисленно, вычисляется до собственно исполнения запроса, соответственно, большая часть условий вообще вырезается. Но там доставляет боли один нюанс: в текущих версиях есть недоработка в планере (в master её уже исправили), из-за которой не происходит предвычисления функции nullif. Приходится изгаляться case-ами что очень сильно усложняет код. Да и вообще, такой подход делает запросы несколько более сложными. Если и без этого запросы сложные (например, из-за сложной аналитики), то запрос не сильно усложняется и такой подход может быть оправдан. Если же там простой селект из 2-3 таблиц, то запрос станет тупо в два раза длиннее из-за условий.
С Oracle такое, скорее всего, не прокатит - там план строится централизовано (слишком накладно строить его при каждом выполнении) и получится комбинаторный взрыв уже в shared pool'е (вроде) с кучей дочерних планов и прилагающимися проблемами. При чём это в том случае, если планировщик догадается что очередной набор параметров достаточно сильно отличается от предыдущего набора (иначе будет неэффективный план). На сколько я помню, там 3 первых исполнения строится план с учётом параметров, а дальше всё зависит от полученных планов и появления значительных различий в значениях параметров. В общем, слишком многое зависит от слабоконтроллируемых нюансов.
При построении плана (негенерализованного) учитываются биндинг-параметры и всё, что может быть вычисленно, вычисляется до собственно исполнения запроса, соответственно, большая часть условий вообще вырезается.
А не подскажите где про данное поведение можно почитать? С PostgreSQL не работал, но это ж бомбовая фича, позволяет применять динамические условия и динамические сортировки, без использования querybuilder'ов. В Oracle это боль, в MySQL реализовано частично, но детерминированные функции и выражения не предвычисляет, увы.
Если интересно подробнее, придётся разбираться с архитектурой исполнения запросов в postgresql, есть несколько старое, но довольно подробное описание, часть про преобразование параметров описана в разделе 3.3.
Хочется глубже — только код читать, где-то в окресности файла src/backend/optimizer/util/clauses.c (там было изменение, которое исправляло проблему с nullif).
Если я правильно вас понял, речь идёт о том чтобы в запросе иметь нечто вроде:
SELECT * FROM table
WHERE (:parm1 IS NULL OR (col1 = :parm1))
AND (:parm2 IS NULL OR (col2 = :parm2))
Выглядит конечно заманчиво, но есть нюанс (о котором я говорил раньше) — нет абсолютно никаких гарантий что сначала будет проверено условие "IS NULL" для параметра, т.е. сервер может впустую сравнивать все параметры (которые не указаны) с данными из таблицы. Да, в идеале оптимизатор должен это прочухать — но всё же гарантий нет.
И ещё момент — кроме условия "=" может быть "<>" — а вот это в один запрос впихнуть уже проблематично. Можно конечно как-то так:
SELECT * FROM table
WHERE (:parm1eq IS NOT NULL AND (col1 = :parm1eq))
OR (:parm2ne IS NOT NULL AND (col2 <> :parm2ne))
и вынести выбор параметров в приложение (передавая только один в зависимости от условия), но проблема с неопределенным порядком остается, да и в отличие от большинства "нормальных" ЯП в SQL нет "короткого замыкания" в логических операторах, плюс есть шанс что планировщик сойдёт с ума и план для "универсального" запроса может быть значительно дороже чем для узкоспециализированного.
Собственно, в сложных системах где запросы ещё могут использовать другие операторы (типа LIKE или BETWEEN) всё уже просто становится неподъёмно и сильно усложняет код — так что динамическая генерация самый простой способ, а в сочетании с кэшем и учётом того что вариантов (реально используемых) всё же не очень много это отлично работает.
В MariaDB вообще работает. Только план оно не строит, пока не передашь параметр, а после передачи параметра фактически лезет в индекс (ибо cardinality для разных параметров оказывается разным. Это не есть плохо, но сломало мне шаблон)
Вообще прикольно. Это говорит о том, что оптимизатор в Маше таки динамический
Ловите плюсик в карму)
UPD: ух ёпт, это ж кто вас так заминусовал?
А between не нужно в таких случаях делать, даже при генерации через orm, как описано в статье. Просто делаете 2 отдельных условия, никаких отличий от дополнительной ветки с between не должно быть, зато во-первых, появляется вариант сделать строгое неравенство (between - это два нестрогих, а отбор, например, по диапазону дат, обычно подразумевает нестрогое с одной стороны, если дата в виде timestamo хранится), во-вторых, не будет лишних проверок.
Да спасибо, это то что я ожидал увидеть. Судя по коментам от @Rrad это не просто причудливая идея, а даже вполне рабочая, ну и теперь я знаю о минусах. Планировщик в моей СУБД должен быть подготовлен к такому подходу, а иначе все будет плохо по производительности.
То что запрос будет сложнее это ожидаемо, но в любом случае будет код который формирует динамический запрос или на орм или на процедурном языке, и тут конечно субьективщина, но совсем не очевидно какой код в конечном итоге проще получиться. Я очень не люблю читать смесь орм/кода и кусков sql, по мне так если что то может быть выраженно только на sql то лучше так и сделать.
Насчёт того что запросы не транслируется, @kai3341 не совсем правы(степень неправоты мне не известна), но я знаю точно как это работает в sqlite, который я много раз дебажил - там планировщик генерит байт код, который потом транслируется спец машиной. Ну т.е. да по факту это конечный автомат, но он записан в кодах виртуальной машины, и эти коды вобщем то легко переиспользывать кусками в разных запросах. Думаю во взрослых СУБД суть тоже самое плюс ещё сама СУБД умеет кешировать, как та же она должна представлять конечный автомат, скорее всего какой-то такой же байт код.
Тоже не знаю так ли это в больших СУБД, но в лайте иногда можно подсказать планировщику с помощью иного синтаксиса как строить план. Т.е. я не удивлюсь что короткие вычисления в условиях как то можно гарантировать либо синтаксисом либо порядком либо хинтами, но вообще да без гарантий это риск.
В любом случае всем неравнодушным спасибо за ответы, теперь я когда нибудь попробую этот подход в лайте.
там планировщик генерит байт код, который потом транслируется спец машиной
Я примерно об этом же. План запроса вместо операторов и имён колонок содержит опкоды и идентификаторы. План или является полным указанием, как вычитывать данные (начиная с какого индекса и в каком порядке. Актуально для Oracle), или хотя бы куда смотреть по мере появления новой информации (то, что я увидел в mariadb).
и эти коды вобщем то легко переиспользывать кусками в разных запросах
SQL не модульный и не поддерживает библиотеки.
Да и что толку от кусков байткода? Объединение сэкономит несколько байт памяти, а при исполнении будет задействовано несколько дополнительных тактов — суть future_select, только внутри БД.
иногда можно подсказать планировщику с помощью иного синтаксиса как строить план
Я в статье про хинты говорил. /*+ MATERIALIZE */
, например
UPD: не видел, что уже ответили
Опять всё про выборку данных. Скажите что-нибудь про эффективную модификацию.
Да как обычно, bulk update, когда данных много. Не забываем про ситуации, когда данные мы вычитали, что-то с ними сделали, и теперь собираемся записывать — а их сейчас процессит кто-то ещё, и он точно их перетрёт после того, как мы их запишем
Повесить версионирование на те, данные, которые находятся в зоне такого риска.
Тогда тот - второй получит ошибку, потому что попытается апнуть предыдущую версию данных
Вычитали-сделали-собираемся записывать - пусть даже с контролем версий строк. Как вы сделаете когерентный апдейт? Например, какое-нибудь сведение текущего баланса пользователей из агрегата их суточных расходов? Будете по одному итерироваться?
Когерентный чему? В самой СУБД апдейт будет атомарный и, как только пройдёт коммит, все последующие запросы получат новую версию (если только специально, например, repeatable read не выставить).
БД обладает некоторым информационным контекстом I - это память цифрового автомата. Чтобы перевести его из состояния Xn (соответствующего контексту In) в состояние Xn+1, нужно в общем случае зафиксировать In на все время вычисления In+1 = F(In), где F - бизнес-функция. Когда F может быть описана в терминах множеств, и требует большого количества данных одновременно (то есть принадлежащих одному номеру состояния n), способ обработки данных (на уровне множеств - T-SQL, или на уровне потоков выполнения - процедурные ЯП) и близость обработчика к самим данным становятся архикритичными. Никакие удобства разработки и сопровождения для этих случаев (а это собственно, основное предназначение СУБД, как систем массового обслуживания - многопоточная обработка множеств) не стоят отказа от использования этих свойств.
И обратно - как только вы отказались от СУБД, как от машины обработки множеств - вы отказались от большинства ее преимуществ, за которые заплачены деньги на покупку лицензии - то есть вы покупаете автобус, чтобы возить в нем пассажиров по-одному.
А в чём проблема для сложного и вариативного запроса (вроде вышеупомянутой страницы с фильтрами) написать собственный билдер SQL-запроса как обычной строки (вопрос немного риторический, потому что я такие билдеры писал, но мало ли, вдруг всё-таки есть какой-то кейс где проявляется преимущество ORM)? Таких запросов в большинстве проектов очень мало, так что сил на эти билдеры уйдёт не много (как правило сил на пляски вокруг ORM в конечном итоге уходит намного больше). И никто не мешает реализовать такой билдер из кучки ровно таких же вспомогательных функций, которые в статье делали это же самое возвращая кусок логики ORM — с тем же успехом они могут возвращать часть SQL-запроса.
В общем, есть подозрение, что в статье мы наблюдаем Стокгольмский синдром и/или сильное облегчение от того, что всё могло быть намного хуже, нечаянно затмило тот факт, что без ORM всё будет ещё немного лучше и проще. :)
Кажется, Вы статью недочитали. Дочитайте до собственных функций, там под катом код целого модуля. Там присмотритесь к макро-командам, задекорированным @register
Присмотритесь к json_arrayagg_workaround
— снаружи вы ему передаёте просто поле, он сам исходя из его типа корректно преобразует его в JSON.
Присмотритесь в json_object_wrapper
. Вы ему скармливаете просто список полей — он выгребает имя каждого поля и генерирует json_object.
В обоих случаях применяется интроспекция.
Оба примера показывают, как сгенерировать полотнища кода на SQL, и что самое интересное — кода работающего правильно и эффективно
Такие макроподстановки открывают для меня целый пласт вопросов. Можно ли базой нормализовать выборку, раскидав её на отдельные сущности в JSON?
Тема UDF не раскрыта от слова никак. Я показал, как зарегистрировать свою функцию в ORM. Где-то тут будет моё следующее исследование
Статью я прочитал внимательно. Код под спойлерами — нет. Потому что я сегодня кодил 11 часов подряд, плюс никогда не писал на питоне, так что вникать в код перед сном сил просто уже нет.
Если я правильно понял, Вам понравилось при генерации запроса использовать дополнительную информацию о схеме БД (типах полей). Это логично, и действительно может упростить некоторые генераторы. Но я всё ещё не совсем понимаю, насколько это реально необходимо в абсолютном большинстве случаев (я пока обходился и не страдал в процессе), и в чём проблема ручками получить эти метаданные точечно там, где они реально всё сильно упростили бы (варианты от захардкодить до считать схему конкретных пары нужных таблиц отдельным запросом при запуске сервиса и закешировать её в памяти). И стоит ли доступ из коробки к этой, не так уж часто необходимой фиче того, чтобы втаскивать в проект ORM.
Я предлагаю вам заценить EF.Core 5.0 (а в 6.0 обещают +30% к производительности еще) и понять, что есть вещи куда приятнее и быстрее.
1. Самые частые запросы к БД это что-то вроде
SELECT users . * FROM users WHERE users . id = ? LIMIT ?
или
SELECT currencies . * FROM currencies WHERE currencies . id = ? LIMIT ?
Дальше идут чуть сложнее запросы вроде:
SELECT SUM ( transactions . amount ) FROM transactions WHERE transactions . account_id = ? AND transactions . type = ? AND transactions . state = ?
Потом идут простые JOIN чтобы избежать N+1 в табличках.
На примере десятков крупных проектов в веб, с ORM — такие запросы к базе составляют 80% по коду, и 99% по количеству запросов к базе. И это не моя фантазия. Это аналитика, которую выдает мне тот же datadoghq, который собирает статистику по всем запросам к БД. И нет, это не простые проекты. Это проекты, где 100+ таблиц в БД, с сотнями foreign_key и сотнями индексов.
Поэтому утверждение автора
Пример крайне примитивен — он даже без подзапросов. Кажется, в моём текущем проекте таких запросов единицы.
как минимум пугает. Либо автор реально плохо себе представляет как разработчики используют ORM и смотрит только на тяжелые запросы, либо проект находится в полном аду, раз большинство запросов в проекте сложнее, чем запрос с двумя JOIN и агрегацией данных.
2. Проект с небольшой командой разработчиков (человек в 5 максимум) уже через пару лет имеет сотню/другую routes к приложению. Клиентская часть, API, админка, и на каждую сущность CRUD + Index + Search + Filter. И 95% запросов должны быть очень и очень простыми, потому что они делают очень и очень простые вещи. И ORM как раз тут наш лучший друг.
current_client.orders.paid.last(default_limit)
превращается в
SELECT "orders".* FROM "orders" WHERE "orders"."client_id" = ? AND "orders"."state" = ? ORDER BY "orders"."id" DESC LIMIT ?
и ORM как раз помогает нам кратко, с учетом наших классов и сущностей писать запросы в базу более читабельно, понятнее и быстрее. И в 99% случаев делает именно то, что нам нужно. Лишь единицы запросов требуют какого-то шаманства. В таких случаях чистый SQL частенько даже лучше чем ORM. Но опять же, таких случаев единицы.
3. Теперь про комбинаторных взрыв. Реляционные БД хороши связями между таблицами. Foreign key наше всё. Именно реляционные БД дают нам уверенность в консистентности данных.
Например в интернет-магазине
— у order_items есть order_id
— в таблице orders точно есть запись с этим id
— и в ней точно есть client_id
— а в clients есть точно запись с этим client id и т.д. и т.п.
И мы можем спокойно писать тысячи строк кода, с уверенностью, что если у нас есть order_item то у него точно если order_id, client_id, и еще куча нужных нам данных в нужном нам формате.
И если мы уперлись в производительность, то зачем насиловать нашу реляционную БД?
Она хороша для другого.
А вот для того, чтобы быстро отдавать данные по разным фильтрам — подходит тот же ElasticSearch.
Построил индекс используя десятки таблиц, и дальше очень быстро ищешь с помощью него. Elastic возвращает нам массив ID товаров, которые подходят под условия, мы идем с этим массивом ID в нашу реляционную БД и с простыми JOIN по ID дергаем наши данные для того, чтобы отобразить их пользователю. ВСЁ. Никакого комбинаторного взрыва. Простейшее горизонтальное масштабирование. Как по Elastic, так и по реляционной БД.
Миллионы записей в основной таблице, десятки таблиц из которых собирается индекс для Elastic и бешенная производительность со всеми хотелками вроде «тут ищем только по точному совпадению», тут «по 4 символам», тут «по 3 символам, но только с начала». И т.д.
Под клиентскую часть — свои индексы со своим набором полей.
Под админку — свои индексы со своим набором полей.
И всё это прекрасно работает и в интернет-магазинах с десятками параметров в фильтрах, сотнями тысяч товаров. И так же прекрасно работает в туризме, где реально «комбинаторный взрыв», потому что один маленьких отель в Турции генерит несколько сотен тысяч туров на лето и таких отелей тысячи, и курортных направлений десятки.
А на месте тимлида, CTO я бы очень внимательно посмотрел на мотивацию
Я обожаю реляционные базочки, и наконец-то я получаю удовольствие от реализации хитрозакрученной аналитики.
Никогда еще в моей практике эта мотивация не приводила ни к чему кроме ада) И очень много раз я разгребал эту «хитрозакрученную логику». Всё должно быть очень просто, так что аж скучно. Читаешь код, и аж тошно, от того как всё скучно) Зато всё работает. Быстро. Просто. Надежно.
Спасибо за развёрнутый комментарий
1) Таки вы не угадали. Я много раз сказал про навороченную аналитику. Навороченная аналитика на то и навороченная аналитика, что запросов много, и они хитрозакрученные
2) Тут я с вами соглашусь. Действительно, я перегнул. Смею заметить, что SQLAlchemy позволяет как решать сложные хитрозакрученные задачи, так и простые.
Я процитирую статью с lurkmore про debian: сабж позволяет варьировать степень красноглазия в широких пределах
Запрос снова примитивен… Ситуация — в процессе жизненного цикла для каждого Product появилась many2many связь с некоторым атрибутом — категорией. На фронт надо вывести список этих категорий. Придумайте, как этот список вывести в максимально удобном виде. Кусок статьи как раз об этом — ответ в статье.
Ограничения — релизный цикл фронта и бэка не совпадают. Вы обязаны не сломать обратную совместимость и ваше решение не должно заставить фронт-энд команду страдать.
3) Тут я с вами соглашусь — у меня в таблице даже 4 колонки, по которым может происходить фильтрация — это редкость. Чаще всего их 2.
Я много общался с крутыми разработчиками из других компаний. Многие из них говорили, что широкие таблицы спасают производительность проекта. Слышал и совсем дичь, когда ребята предлагали денормализовывать данные
Скорее всего, я бы строил таблицы каталога именно так, как вы и сказали — раскидал бы данные по разным табличкам. Я упоротый реляционщик. И в случае интернет-магазина с большим числом критериев пошёл бы по пути атрибутов — и там внезапно всё можно положить на малое число запросов.
Никогда еще в моей практике эта мотивация не приводила ни к чему кроме ада) И очень много раз я разгребал эту «хитрозакрученную логику».
Не путайте хитрозакрученную логику с хитрозакрученной аналитикой. Хитрозакрученной логики у меня как раз нет нигде — обработка всех запросов максимально линейна. Посмотрите на BaseViewMixin — это дубовый конечный автомат со стадиями парсинга запроса и построения плана ORM-запроса, непосредственно построения ORM-запроса по плану, исполнения запроса и сериализации ответа. Изячно же. Кажется, я что-то говорил про контроллер, и про его толщину. А, ну да, пост не читай, комментарий оставляй =/
Я немного неоднозначно выразился. Поправлю:
Ситуация — вы выводите список Product. Не важно, будь то список по одному Order или полотнище Product для оператора. В процессе жизненного цикла для каждого Product появилась many2many связь с некоторым атрибутом — категорией. На фронт надо вывести список этих категорий. Может так статься, что продукт не имеет категори. Придумайте, как этот список вывести в максимально удобном виде.
Ограничения — релизный цикл фронта и бэка не совпадают. Вы обязаны не сломать обратную совместимость и ваше решение не должно заставить фронт-энд команду страдать.
Кусок статьи как раз об этом — ответ в статье.
Попробуйте накидать SQL-запрос как решение такой проблемы
Рассмотреть по очереди 2 сценария:
- Категорий мало, фронт их запрашивает при инициализации
- Категорий (по какой-то неведомой причине) стало очень много
Категория имеет несколько полей: id, name, comment, color
На PHP в Yii будет как-то так.
Product::find()->where($filter)
->with('productCategory')
->with('productCategory.category')
->all();
SELECT * FROM product WHERE (:filter) LIMIT :offset, :pageSize;
SELECT category_id FROM product_category WHERE product_id IN (:productIdList);
SELECT * FROM category WHERE id IN (:categoryIdList);
4 строки, 3 простых запроса. К вопросу о кешировании результата, результат третего запроса неплохо кешируется, так как категории редко меняются.
Можно через JOIN сделать, но тогда пагинацию и общее количество надо будет по-особому обрабатывать. Если фронтенд кеширует категории у себя, второй with делать не надо.
В ответе сервера просто появится новое поле, фронтенд-фреймворки вроде нормально справляются с такими ситуациями.
ммм, ляпота. Собираем контроллером, итерируя выборку по продукту. Причём минимум дважды: сначала надо собрать productIdList.
А как вы собрались связи строить со своим 2м запросом? Допустим, в списке productIdList
несколько позиций, и каждого продукта по одной категории, и они все разные. Короче, косяк мелкий
В каком формате вы отдадите данные на фронт?
Всё это можно сделать в 1 запрос к БД, на выполняя потом join на контроллере, не итерируя полученнуые из БД данные
Собираем контроллером
Каким контроллером? Логика фильтрации находится в отдельном классе, который вызывается из контроллера, в самом контроллере работы с запросами нет.
итерируя выборку
Ничего там не итерируется, оператор просматривает только одну текущую страницу, вот данные для нее и запрашиваем.
Допустим, в списке productIdList несколько позиций, и каждого продукта по одной категории, и они все разные.
Не понял этот пример. productIdList берется из результатов первого запроса, далее мы просто подгружаем категории этих товаров через промежуточную таблицу. То есть то, что и требовалось сделать. ORM сама по первичным ключам распихивает все по объектам.
В каком формате вы отдадите данные на фронт?
В каком он запросил, в таком и отдадим. Запросил text/xml, отдадим XML, запросил application/json, отдадим JSON, а может он вообще GraphQL хочет получить, значит его и построим. Это вообще не должно иметь отношения к механизму построения SQL-запроса.
Всё это можно сделать в 1 запрос к БД, не выполняя потом join на контроллере
Можно. Только код будет сложнее. Обычно выбирают простоту поддержки, а оптимизируют потом, когда производительности начинает не хватать. И то обычно проще арендовать еще один сервер, чем нанимать еще одного программиста потому что из-за увеличения сложности кода время выполнения задач увеличилось.
На всякий случай — контроллером в веб-программировании называется вполне конкретная часть приложения, которая является точкой входа для HTTP-запроса, а не всё приложение на языке программирования целиком. Делать join на контроллере я не предлагаю ни в каком виде.
Можно. Только код будет сложнее. Обычно выбирают простоту поддержки, а оптимизируют потом, когда производительности начинает не хватать. И то обычно проще арендовать еще один сервер, чем нанимать еще одного программиста потому что из-за увеличения сложности кода время выполнения задач увеличилось.
Для БД это утверждение спорно. БД сложно маштабируются.
Делать в горячей точке приложения, а отрисовка списка товаров с фильтром пользователю это явно горячая точка, три запроса вместо одного черевато проблемами производительности.
Написать оптимальный запрос руками может выйти дешевле в итоге.
Ну самое простое это поставить реплику для SELECT-запросов. В данном случае это заметно поможет. Опять же для простых запросов проще организовать кеширование результатов. То есть один отдельный запрос может быть и медленее, зато следующие быстрее. А если всё джойнится в один запрос с произвольным фильтром, то конечно такой результат кешировать бессмысленно.
Может выйти, а может и не выйти. Надо делать замеры и действовать по результатам. Просто обычно начинают с простого кода, а не с настройки списка полей в каждом SELECT. Да, можно выгадать 20% производительности, но что толку, если пользователей стало много, и надо 60%, все равно понадобится второй сервер подключать.
Когда такое пишешь надо хорошо подумать. Пользователи любят когда сайт работает быстро. Увеличение времени отклика очень плохо влияет на деньги.
Кешировать можно и то и другое. В ситуации с одним запросом кешируем прямо результат. Самые популярные фильтры вытаскиваем кнопочками в интерфейс. Пользователь ленивый и если мы более-менее угадали с популярностью то он их жать и будет. Профит.
Сбоку прикручиваем сборку статистики и фоновое обновление популярных фильтров. Тогда вообще все само работать будет. Ручную модерацию для отсева тролей естественно оставить придется.
Подключение ro реплики помогает. Это в общем типовая оптимизация.
Но это не самое тривиальное действие. Особенно в нагруженных, старых архитектурах. Как правило именно в таких и бывают проблемы.
Вы все верно говорите, просто обычно не все так плохо. Я встречал по несколько десятков запросов на страницу с товарами — текущий регион, аккаунт пользователя, корзина, акции какие-нибудь, всякие вспомогательные данные для этого, поверх этого работа с микросервисами и сессиями. На фоне этого пара дополнительных запросов не так заметна. А если заметна, можно и join сделать, никто же не против. Только для пагинации все равно все one-to-many связи придется убирать. И кстати если пагинация делается отдельным запросом с COUNT(*), то даже в этой части различие не в 3 раза, а в 2 — 4 запроса вместо 2.
Я сталкивался с несколькими идеями, как бороться с пагинацией:
1) Explain. Для грубой оценки самое оно. Поехавшая пагинация у пользователей будет коррелировать с поехавшей же статистикой. А поехавшая статистика — это звездец
2) Мы исполняем запрос и кладём его в кэш — redis. Отдаём первые X результатов, но при этом у нас тут же на месте есть и их число
Я встречал по несколько десятков запросов на страницу с товарами — текущий регион, аккаунт пользователя, корзина, акции какие-нибудь, всякие вспомогательные данные для этого, поверх этого работа с микросервисами и сессиями.
Несколько десятков запросов на страницу это ужастно. Особенно если они все кешами не обмазаны и это именно sql запросы.
С такой архитектурой надо не эти три запроса лечить, а все переделывать. Постепенно переделывать, естесвенно.
Только для пагинации все равно все one-to-many связи придется убирать. И кстати если пагинация делается отдельным запросом с COUNT(*), то даже в этой части различие не в 3 раза, а в 2 — 4 запроса вместо 2.
Пагинация в таком виде это ужастно. Не надо так код писать. Как рядом предложили воспользуемся правилом что никто не ходит даже на вторую страницу Гугла и положим вторую страницу в кеш. До третьей точно никто не дойдет.
Оверхед на тех кто до третьей страницы дошел будет совсем небольшим. Заодно сделаем метрику сколько процентов людей доходит до третьей страницы. Если там много, то стоит сходить к продактам с вопросом А все ли нормально с дизайном? Тут люди ведут себя странно.
Особенно если они все кешами не обмазаны и это именно sql запросы.
Кеширование конечно есть где нужно, но суть в том, что это работает, и лишние запросы не так заметны. И "все переделывать" зачастую дороже, чем масштабировать железо.
А в Битрикс или Magento вообще по 600 бывает, встречал в каком-то из них. Жесть конечно, но тоже как-то работает.
Как рядом предложили воспользуемся правилом что никто не ходит даже на вторую страницу Гугла и положим вторую страницу в кеш.
В тех интернет-магазинах, которыми я пользовался, количество страниц указывается правильно. И это удобно. Поэтому без COUNT(*) не обойтись.
Кеширование конечно есть где нужно, но суть в том, что это работает, и лишние запросы не так заметны. И «все переделывать» зачастую дороже, чем масштабировать железо. А в Битрикс или Magento вообще по 600 бывает, встречал в каком-то из них. Жесть конечно, но тоже как-то работает.
А потом эти же люди спрашивают а чего это покупатели массово уходят на Озон с Вайлдберриз? Блин, потому что там сайты нормально работают и пользователи не должны страдать каждый раз когда хотят чего-то купить.
В тех интернет-магазинах, которыми я пользовался, количество страниц указывается правильно. И это удобно. Поэтому без COUNT(*) не обойтись.
Но зачем второй запрос?
Это делается оконными функциями. Одним запросом.
Просто для меня в приложении методы вроде
process_result_value
where_clause_iter
where_clause
__getattribute__
и классы вроде
FutureSelectWrapper
как красная тряпка для быка.
На мой взгляд существует две цели написания кода.
Первый — написание библиотеки/пакета/плагина/либы
Второй — написание приложения.
И IMHO они должны быть написаны по разному.
Библиотеками пользуются тысячи и миллионы проектов, они поддерживаются, они должны быть универсальными. Хорошая библиотека спроектирована в среднем лучше, чем среднее приложение. Если библиотеке 10 лет и все 10 лет она поддерживается, то шансов что ее и дальше будут поддерживать достаточно много. В библиотеке важно, чтобы ей было удобно пользоваться, и действительно очень важна универсальность. Поэтому внутри приходится писать всякие методы вроде __getattribute__ и т.д.
Приложение — это наоборот частный случай. Самый частный случай. В приложении конкретные сущности, конкретные запросы, конкретные пользователи с конкретным поведением. А еще в приложении средний срок работы программиста 1,5 года.
И очень часто приходится читать, поддерживать, дополнять чужой код. И чтобы это было просто делать потом за Вами — нужно делать код максимально простым.
А когда вы пишите:
это дубовый конечный автомат со стадиями парсинга запроса и построения плана ORM-запроса, непосредственно построения ORM-запроса по плану, исполнения запроса и сериализации ответа.
То в Вашей голове все очевидно. Вы это сами придумали и сами написали. И сейчас Вам всё очевидно. Но поверьте, что разбираться в этом никакого удовольствия.
Кстати, пример из жизни:
Подключают провайдера данных. Конечно же
А потом подключают второго провайдера, потом подключают третьего провайдера. Это делают другие разработчики.
И знаете что в приложении после этого? Три разных универсальных набора подключателей провайдеров. И три провайдера, каждый из которых подключен через свой универсальный подключатель провайдеров. Каждый написан по своему, в каждом есть свой набор методов вроде process_request, свой стиль, свой набор параметров, свой BaseClass, весь набор наследования, полиморфизма и т.д.
Смешно? Нет. Грустно. Лучше бы написали три раза не универсальный код. Кода было бы в 6 раз меньше (проверено, когда удалил все универсальные подключатели, стало кода в 6 раз меньше и никакой универсальности).
Судя по вашему ответу на следующий комментарий:
Всё это можно сделать в 1 запрос к БД
Мне кажется, что цель «сделать всё в 1 запрос» — это первичная цель. Не удобный поддерживаемый код. Даже не производительность. А именно сделать из трех запросов один, потому что три это «долго и много». Плюс сразу вернуть нужный набор полей и нужный формат. Нет, три простых запроса — это не много, и нет это не долго. Как раз простые запросы в одну таблицу отлично кешируются на уровне БД. База с этим сама отлично справляется.
А попытка снизить количество запросов путем усложнения запросов и усложнения логики приложения — это очень и очень опасно. И очень не хочется, чтобы это даже выглядело как Best Practice
Я вам тут всю статью расписываю, что код можно и нужно писать простой и максимально удобный для сопровождения, держа в голове производительность.
И тут влетаете вы и утверждаете, что я предлагаю усложнить код.
Я вас не понимаю. Я тут показываю: смотрите, алхимию легко плагинить! Смотрите, удобные макросы! А ещё тело макросов можно будет подменить — DIP же! Смотрите, как просто использовать свои типы! И всё это позволяет выжать максимум производительности! И код получается предельно простым!
То ли лыжи не едут, то ли я еб***тый
Я тоже делал свой набор провайдеров. Я не знаю, как это предлагали делать у вас — я исходил из того, что каждый эндпоинт — это отдельный класс (ибо каждый эндпоинт имеет какую-то собственную причину для существования). Код снаружи его инстанцирует, передаёт туда необходимый минимум данных и нажимает единственную кнопку "Пыщь!" — ибо LSP. Внутри код идентичен коду в моей статье — предельно простой конечный автомат с элементарными стадиями. Сам же код эндпоинта — это комбинация миксинов зачастую без единой строки кода — "всё уже написано до вас".
Более того, я сторонник "толстого common", и при малейших намёках на реюзаемость компонента тащу его в common. Это вовсе не значит, что я вообще весь код храню там. Это значит ровно то, что я написал: я активно реюзаю свой код. И, следуя OCP, я крайне редко (но это бывает) вношу в этот код изменения, чтобы какой-то частный случай заработал — всё это я делаю в классе-наследнике. И нет, мой код от этих изменений не ломался никогда: зачастую я всего лишь выделяю из метода какой-то блок и выношу его в другой метод, ибо сразу соблюсти SRP не всегда удаётся.
Такое впечатление, что вы пытаетесь сказать "какой бы код ты ни написал — это всегда говно. И самое лучшее, что ты можешь сделать этому миру — уменьшить количество говна". Но позвольте, я пишу код не первый год. Я умею писать код такой, который поддерживать просто и приятно.
Возможно, мы живём в разных вселенных.
Нет, я не хочу сказать, что любой код говно. Но у нас действительно разный взгляд на то, что есть хорошо. Это не хорошо и не плохо. Это просто так.
Вы имеете права написать «я сторонник толстого common», а я имею права написать комментарий: «я считаю, что толстый common — антипаттерн».
И еще раз: не «тот, кто делает толстый common — дурак», а «я считаю, что толстый common — говно».
И да, мы живем в разных вселенных. Вы судя по всему любите SOLID, DIP, OCP, LSP…
А я их терпеть не могу. Почему? Потому что, я не видел еще ни одной группы разработчиков более 5 человеков, которые бы одинаково трактовали и применяли эти принципы.
Вот с тем, что миграции БД надо делать откатываемыми никто не спорит.
С тем что на больших нагруженных системах API должен быть идемпотентным, а на маленьких по желанию — тоже. Не все делают, но никто не спорит. Никто не спорит с тем, что пользовательский ввод надо валидировать, и еще с кучей других нормальных подходов.
А вот принципы SOLID каждый трактует как хочет. Поэтому для меня принципы SOLID — это лишь мнемотехническое слово, которое позволяет нам запомнить пять направлений в которых нам следует подумать, и мы думая в этих направлениях рассмотрим бОльшее количество решений и возможно к нам придет в голову какая-то хорошая идея.
И для меня если разработчик аргументирует свое решение только «ну потому что DIP» — то это не аргумент. Если разработчик не может без всяких аббревиатур обосновать своё решение, то кажется у него нет внятных и веских причин зачем он так сделал.
Для меня фраза: «ну потому что DIP» — это тоже самое что фраза «ну потому что алюминий». Алюминий отличный металл, из него делают самолеты, иногда машины,
а еще ложки, миски, фляжки, пищевую фольгу, банки для пива и еще кучу полезных вещей. Но «ну потому что алюминий», извините, не аргумент. И аналогично, «ну потому что SOLID/DIP/OCP/LSP/etc» — для меня тоже не аргумент.
Кстати рекомендую статью про применение этих принципов самим Робертом Мартином. В его же книге «Чистый код». Очень интересно. И очень интересные комментарии.
Ну и да, у нас разные подходы.
У Вас
при малейших намёках на реюзаемость компонента тащу его в common. А у меня
должны быть очень веские причины, чтобы тащить код в common. Сначала напиши повторяемый код 7 раз. Заставь его работать. Покрой тестами. И только после этого, если всё еще есть желание вынести что-то в common — то тогда можно это сделать
Мы разные и это прекрасно)
Решение с elasticsearch имеет по крайней мере одну проблему это синхронизация данных. Вы так много в комментарии сказали о преимуществах целостности данных в sql но здесь мы ее теряем. Самый распространённый способ синхронизации это периодически бросать индекс и пересоздавать его на случай если синхронизация в режиме реального времени собьется. Мосты которые нам обещали в elasticsearch были для некоторых баз данных. Но они также не гарантировали согласованность данных если возникнуть проблемы. А сейчас они кажется вообще все задеприкейтились.
И потом если мы выдаем пользователю ленту с товарами, ничто не мешает нам запросить 25 товаров вместо 20, и перед выдачей проверить, что все они например в наличии.
Но скорее всего и этого делать не надо, потому что среднее время страницы каталога может быть 40 секунд, у нас задержка между обновлением индекса секунда, и каждую секунду у нас обновляется только одна десятитысячная каталога
То есть за время пока открыта страничка каталога произойдет в десятки раз больше изменений остатков, поэтому это задержкой мы можем пренебречь. Это погрешность третьего порядка.
Проблема не в задержке ирндексации а в том что рано или поздно из-за проблем в работе реального сетевого приложения данные рассогласовываются в основной базе и в поисковом индексе. И его приходится принудительно обновлять перечитыванием от начала и до конца. Действительно для магазина среднего это такое малое количество данных что можно этим перенбречь. Но не всегда задачи имеют малый объем. Например один из индексов у меня проходит проверку раз в сутки потому что перебор всех значений занимает 4 часа. И это к сожалению реальная проблема синхронизации. И несколько раз в год бывает негативная обратная связь от клиента что в индексе устаревшие данные.
Если что-то пошло не так, реиндексация не прошла, фоновая задача через 1,2,3,5,8… секунд попробует еще раз и в итоге дореиндексирует.
Ну и в любом случае в очереди фоновых задач я увижу, что у меня что-то не так с реиндексацией, почему-то выросла очередь, придет уведомление в телегу и этот вопрос тоже будет решен.
Ну и регулярная реиндексация тоже помогает. Но это так, подстраховка второго порядка.
А преобладание запросов вида select * from entity where ID = <id> - не следствие использование orm? Многие orm так и работают: тянут всю сущность из БД, при необходимости, лениво подтягивая связанные сущности.
На деле же, любая страница в сложном проекте потребует погрузки до десятка связанных сущностей, некоторые из которых могут быть связаны с основной достаточно нетривиальными связями (например - подтягивание последней записи в истории изменения). И получается, что вместо одного запроса, отбирающего всё, что нужно, получается десяток запросов по id.
Кроме того, использование select * - это, как правило, антипаттерн, убивающий пропускную способность базы (исключение - использование * с подзапросами, где набор полей ограничивается либо ниже, либо выше).
SELECT json_object( 'name', product.name, 'price', product.price ) AS product
Зачем генерировать json средствами БД?
Хочешь быть передовым? Сей квадратно-гнездовым!
Нет никакого смысла преобразовывать весь SELECT в JSON. Есть смысл группировать в JSON некоторые поля этого SELECT. Например, другие сущности, вытянутые через JOIN. Удобно аггрегировать в JSON-массив такие сущности из связей many2many
например во временной таблице, когда есть потребность в одном запросе обработать n таблиц, и вернуть полезный объект...
У меня было движение противоположное дв жению автора. Я, конечно, в начале разработки делал сылые запросы, конечно, хватал инъекции с фронтенда. Но когда начал программировать более профессионально то работал только с orm. Так как начал я c php Doctrine, и как теперь я понимаю это наиболее мощная orm которая по своим параметрам превзошла hibernate с которого она и списывалась в самом начале. После Doctrine когда я использовал другие orm в javascriot и go всегда испытывал нехватку мощности и гибкости.
Случай заставил меня вкорне пересмотреть свой взгляд на orm когда я захотел в одном из проектов использовать arangodb, а у нее просто нет orm или odm. В результате нескольких лет поисковая я пришел к переосмысление роли и места orm в проекте. Большинство orm тратит колоссальный ресурс на то чтобы повторить то что делает sql в объектной манере. В общем, после переосмысления роль orm я свел к тому что 1) сырой json объект полученный из базы данных превратить в типизированный объект у которого могут быть вычисляемые поля и т.п.и потом сериализоать его на фронтендом. 2) сырой json пришедший с фронтенда опять же превратить в типизированный объект ко орый проходит валидация и далее сырым запросом сохраняется в базу данных. Я описал это в своем сообщении https://habr.com/ru/post/522992/.
Однако это не означает что я что то поменял в процессе разработки. Исходя из позиций поддерживаемого я продолжаю использовать общепринятые orm.
return \
select(
func.count(Product.id).label("product_count"),
func.sum(Product.price).label("order_price"),
Customer.name,
)\
.select_from(Order)\
.join(
Product,
onclause=(Product.id == Order.product_id),
)\
.join(
Customer,
onclause=(Customer.id == Order.customer_id),
)\
.where(
Order.id == order_id,
)\
.group_by(
Order.id,
)\
.order_by(
Product.id.desc(),
)
vs
SELECT
COUNT("products"."id") AS "product_count",
SUM("products"."price") AS "order_price",
"customers"."name"
FROM
"orders"
JOIN
"products" ON "products"."id" = "orders"."product_id"
JOIN
"customers" ON "customers"."id" = "orders"."customer_id"
WHERE
"orders"."id" = {{ order_id }}
GROUP BY
"orders"."id"
ORDER BY
"products"."id"
По моему второе — значительно нагляднее, короче, меньше телодвижений.
у pure SQL только один недостаток — это передача ему аргументов, но этот недостаток отлично решается использованием какого-либо языка темплейтов. Например jinja. Который заодно порешает вопрос с injection итп.
PS: глядя на этот запрос могу сказать, что
- пишете Вы под MySQL (потому что остальные базы данных не позволят вам такой запрос написать).
- мало заботитесь о планах запросов
:)
По моему второе — значительно нагляднее, короче, меньше телодвижений.
Я вам тут про наследование и полиморфизм в SQL, и кодогенерацию =)
Например jinja.
Так, вот тут очень осторожно. Шаблонизатор ни разу не защищает вас от SQL injection. Проверено на личном жопыте. Гугл говорит, что с именованными параметрами в MySQL туговато, и это, скорее, фича драйвера.
Однако в своё время я делал нечто подобное, о чём вы говорите. Я писал SQL-запросы с переменными в ораклином стиле. На этапе вычитывания запроса я заменял :variable
на %s
, сохраняя порядок переменных, чтобы на основании словаря сгенерировать кортеж позиционных параметров — и вот это решило проблему с SQL Injection
PS: глядя на этот запрос могу сказать, что
А вот с этого места подробнее. Как вы сделали такие выводы? Какие были предпосылки?
PS: запрос из головы взят. Проект находится в другой предметной области, и сущностей Product
, Order
и Customer
в нём не существует
Я вам тут про наследование и полиморфизм в SQL, и кодогенерацию =)
и я Вам про неё.
полиморфизм — отлично на Template языке пишется
Так, вот тут очень осторожно. Шаблонизатор ни разу не защищает вас от SQL injection
мы делали так:
простая вставка {{ что-то }}
— вставляет всегда в защищённом от инжекшенов виде (то есть реально вставляется вопросик, позиционная переменная или процентик — в зависимости от того над чем пишется темплейт)
а ещё даём возможность сделать фильтр, позволяющий инжекшен-вставку (например имена таблиц): {{ что-то |i }}
и получалось очень хорошо. Выглядит как вставка переменной по месту, на деле инжекшеном не является итп.
Гугл говорит, что с именованными параметрами в MySQL туговато
поэтому для MySQL вопросики вставляли. По умолчанию большинство её драйверов к этому приводили.
А вот с этого места подробнее. Как вы сделали такие выводы?
ошибочная пара SELECT-списка и GROUP-списка. Такое только MySQL позволяет, в силу своей кривизны.
Другие базы дадут ошибку на данный запрос.
то есть реально вставляется вопросик, позиционная переменная или процентик — в зависимости от того над чем пишется темплейт
А, тогда всё гуд
ошибочная пара SELECT-списка и GROUP-списка. Такое только MySQL позволяет, в силу своей кривизны.
Другие базы дадут ошибку на данный запрос.
Я же говорил, что я этот запрос никогда не исполнял) У меня нет таких сущностей даже близко. Я на скорую руку менял запрос
ошибочная пара SELECT-списка и GROUP-списка. Такое только MySQL позволяет, в силу своей кривизны.
Увы, не только MySQL — ещё SQLite.
->execute()
А потом автор увольняется и наступает maintenance. Новая команда потыркается и решит ну его нафик и в лучшем случае перепишет куски на что-то вроде select * from xxx where Id=n
Uber переезжал с Postgres на MySql, пруф. Причина, емнип, с mvcc
причина не MVCC, а некомпетентность (в пруф я не смотрел, что переезжали — верю)
Про Яндекс и Uber читал. Про Guardian впервые слышу. Спасибо, с интересом прочитаю
Но смею заметить, что все эти истории можно охарактеризовать одном словом — однажды. И в статье я специально акцентировал внимание — однажды переехать на другую БД может любой проект. На этапе набросков большинство проекты используют SQLite, и потом переезжают на что-то другое по тем или иным причинам.
На этапе же сурового продакшона переезд на другую БД, даже с учётом того, что SQL92 почти везде реализован одинаково, а базоспецифичный синтаксис и API можно обновить почти автоматически (и вот тут кстати и проявляется та самая "переносимость" ORM), далее выплывает дикий объём нюансов похлеще, чем между Чапаевым и Петькой. Яндекс что-то около года переезжал, ЕМНИП
То есть переносимость номинально есть, но по факту нет
В любой проект с историей и нагрузкой (а другие зачем перевозить?) пролезают зависимые от БД оптимизации и конструкции. А уж поехавшие не туда планы запросов при переезде это всегда.
Это не проблемы с кодом, это реальность. Я видел проекты при проектировании которых неслабо вкладывались в независимость от БД. И это ничего не давало. Лет через 5 разработки вся эта независимость исчезает.
Боль не обязательна, например за счет паттерна Repository.
Если не меняется бизнес логика, а только хранилище, боль лишь признак проблем с кодом.
Я тут в статье красиво расписывал о разных структурах данные и разные применяемых инженерных решениях, и все они имеют свой набор преимуществ и болячек. Жаль, что статью вы не читали =/
История с uber и guardian это как раз не частный случай а довольно распространенный. Первпая история о том что sql базы ввиду своей не масштабируемость приводят к необходимости перехода на другие средства. Mysql в uber не используется как sql а фактически как mysql база данных. С guardian поучительная история о том что mongo не самый подходящий стек для 99% случаев где ее сейчас используют.
Uber не переезжал с postbres на mysql. Убер переезжал с sql на свою оригинальную модель данных которая в качестве хранилища почему то использовала mysql, хотя с той же вероятностью могла использовать berklydb или tarantool. Просто зачастую эта статья используется для подтверждения превосходства mysql над другими база и данных уж если сам убер на неепереехал.
Наверное, эта история о том, что профиль нагрузки, который создаёт приложение, определяет выбор той или иноё СУБД.
Вы в один список поставили реряционную и колоночную БД. Вы не задумывались, что они заточены под разные вещи?
Ещё раз повторю. Uber когда перешёл на MySql не использовал ее как sql базу данных. Просто как абстрактное хранилище. Просто когда упоминают uber и mysql дальше заголовка их статью никто не читает https://m.habr.com/ru/post/354050/ здесь мой перевод. Они называют это schemaless.
По моего — темсха с моего сильно перегрева. Кейсов где ее нужно использовать в качестве основной базы данных не больше чем скажем у кассандры. А в качестве специфического хранилища пожалуйста. Как редис. Но все сейчас хотят моего наше фсе
А как в Вашем случае Вы решаете вопрос с изменениями в модели данных во время жизненного цикла ПО?
В случае SQLAlchemy — есть же alembic
Только заранее предупреждаю — то, что высирает alembic, читать обязательно!
История "успеха" — я переименовал колонку, а alembic её снёс, как дедушке яичко =(
Благо, что от dev environment до прода, как до Луны.
Поясню, в одном из своих проектов как раз думал использовать связку SQLAlchemy — Alembic, но после прототипа от последнего решил оказаться, а изменения в модели реализовать самостоятельно средствами той же SQLAlchemy.
Интересно. А что послужило причиной отказа от alembic?
Да, там не всё гладко. Но в скрипте миграции я первым делом вкидываю в переменные окружения ALEMBIC=1
В некоторых местах я смотрю на наличие этой переменной. В частности, для alembic нужен синхронный драйвер БД, в том время как приложения использует асинхронный. Connection URI я собираю в специальном классе, где много смотрю в переменные окружения
Ещё нюанс с собственными типами возник. Приходится городить огород:
if 'ALEMBIC' in environ:
MyColor = Integer
MyIntEnum = Integer
else:
MyColor = ColorType
MyIntEnum = IntEnumType
Меня это не устраивает. Ещё придумаю чего. Есть мысль в __new__
залезть
В чем собственно преимущество? Код стал длиннее, больше скобок, дополнительный уровень абстракции.
В чем выигрышь то?
У меня есть проект, где ВСЕ поля возможны для сортировки и поиска, и все ок там с поддержкой. Нет, ну основной клас пейджинатора, конечно, не фонтан, зато его трогать вообще не надо в процессе дальнейшей разработки.
Я вот не увидел в вашем примере ничего, что сильно бы отличалося от sql с новым f-string.
А что вы подразумеваете под «ВСЕ поля возможны для сортировки и поиска»? В смысле все поля одной таблицы? Если да, то тут проблем нет. Они возникнут тогда, когда некие условия фильтрации потребуют дополнительных JOIN-ов или подзапросов. Вот тут автор статьи более подробно ответил на подобный вопрос.
Все значит все. Все поля, что есть в view можно использовать для поиска и сортировки. Сколько бы таблиц там не было проблемы в этом нет вообще.
Сколько бы таблиц там не было проблемы в этом нет вообще
Как раз-таки есть. Потому что для некоторых фильтров вам нужны некоторые таблицы, а для некоторых — нет. В зависимости от этого вам надо к строке запроса добавлять фрагменты с join-ами таблиц или не добавлять.
P.s. если вам не нужен в разработке шаблон «строитель», то, возможно, вам и другие паттерны проектирования не нужны?
Статья о другом. Приложение масштабируется, а БД нет. И вся статья о том, какую дичь позволяет творить SQLAlchemy с БД, чтобы выжать из последней максимум производительности. Дичь, рассмотренная в статье -- макросы. Их удобно использовать в коде приложения, и они сами корректно распепячиваются в код SQL.
Нет, я не умаляю важности сказанного вами. Просто статья о другом
Когда-то читал немного доку по SQLAlchemy - восхитился какие грамотные и универсальные паттерны лежат в основе, понравилось намного больше чем корявый ActiveRecord
SQLAlchemy: а ведь раньше я презирал ORM