Pull to refresh

Comments 33

Почему заголовок такой желтый? Если пишете советы по правильному написанию SQL-запросов, то и статью называйте соответственно. При чем тут вообще Java-программисты?

Чтобы написать запрос, который «положит» базу, не обязательно быть программистом вообще, достаточно просто иметь доступ к БД. Конечно, программистов, неоптимально использующих ресурсы СУБД немало, но если Вы пишете конкретно про Java, до хотя бы статистику какую-то дайте, что именно на Java косячат больше всех, а Си, PHP, Delphi и остальные белые и пушистые.

PS Да, я Java-программист, и судя по отсутствию комментариев по поводу заголовка, хаб Java читают в основном не Java-программисты…
Это перевод.
Почему заголовок желтый, если автор даёт советы, опираясь на технологии Java? В нашей профессии не стоит воспринимать каждое «желтое» утверждение на свой счёт.
«опираясь на технологии Java»

Хорошо, давайте пройдемся по конкретике:
«Обработка данных в памяти Java», «Использование JDBC», «Соединение данных в памяти Java» — в общем-то и всё. В других ЯП нет памяти и коннекторов к БД? Вот скажите, какой именно из 10 пунктов неприменим к другим ЯП?
Я с вами согласен, мне не хотелось уходить слишком далеко от оригинала, это ведь перевод.
Но в целом вы правы, и т.к. хабр — самомодерирующееся сообщество, переименовал статью.
Спасибо! Мне, правда, даже как-то легче стало на душе…
при чтении статьи, вообще не обращал внимания на слово JAVA. Вы, я так понимаю, ява программист ?;)
Да, я Java программист, думаю по комментариям это сразу бросается в глаза :)

Я считаю, что автор статьи абсолютно неправ, связывая незнание SQL/принципов работы СУБД и какой-либо конкретный язык программирования (а переводчик неправ, защищая его точку зрения).

Мне не раз встречались, например, системные администраторы, не имеющие никакого отношения к программированию, которые одним простым запросом давали нагрузку на базу в тысячи раз большую, чем любой запрос, отправляемый из ПО. Любимая ошибка многих — в условии ограничения выборки взять в кавычки значение, накладываемое на числовое поле (или наоборот). Результат: мало того, что индексы не работают, так еще и выполняется конвертация значений поля во всех записях таблицы.

Похожая ошибка у программистов — при накладывании ограничений по дате применять функцию приведения значения не к передаваемому условию, а к полю в БД: вместо "to_date(моё_значение) = поле_таблицы" делать "моё_значение = to_чтонибудь(поле_таблицы)".
15 минут рисовал на листочке про NULL. Не понял, в чем тут заблуждение?! В том, что надо понимать приоритет операций? Так они скобками обозначены. Или это перевод такой, а на самом деле это просто ошибка? Как это вообще связано с тем, что кто-то чего-то в Java переносит в SQL? Разорвано повествование. Перевод вообще какой-то странный, я таких терминов нагуглить даже не могу, «со строковым значимым выражением». В общем ужасно, прочитал, ничего нового не узнал, а изломал весь «язык».
Wikipedia
При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0. Более того, NULL не равно NULL!
Простите, вы читали мой комментарий? И свой перевод? Причем тут вырезка из 3 ресурса? Я про предикаты, если вы не поняли. Как мне кажется, даю это понять указывая слова: приоритет операций, «со строковым значимым выражением».
А то, что NULL в SQL несколько иной (вернее его поведение) это известно любому, мало-мальски разбирающемуся, человеку в SQL.
«со строковым значимым выражением»

Думаю, что имелась ввиду ситуация в Oracle, где пустая строка есть NULL. И этот NULL ведет себя не всегда логично: любая операция с NULL в результате должна давать NULL, но в случае с конкатенацией строк это утверждение неверно. И в то же время функция LENGTH('') вместо 0 выдаст NULL.

P.S. Последняя используемая мною версия Oracle — 10.x.x.x
По ссылке перейдите, там имеется ввиду совершено другое. Там разбирается предикат NOT и IS NOT NULL и IS NULL. Строка как тип тут ни при чем, это видимо автор перевел так Row.
И в то же время функция LENGTH('') вместо 0 выдаст NULL
Насколько я помню в Oracle весьма странно реализовано хранение в строке NULL-значения и '', потому и результат такой. Имеющиеся под рукой PostgreSQL и MS SQL дают ожидаемый результат.
На самом деле, то, что пустая строка IS NULL, только сначала доставляет неудобства. Это действительно удобно, т.к. не нужно делать лишние проверки при фильтрации. Если бы ещё таких вот несоответствий не было, вообще была бы лепота.
мозгодробительный перевод! «партия (batch)»… общепринято — «пакетный набор» и т. п.
> Большинство БД поддерживают какие-то средства для постраничной разбивки через LIMIT
Ох если бы. Последний раз когда я пытался собрать вариации LIMIT для разных СУБД все крутые и дорогие СУБД заставляли меня рыдать.

Вот такая вышла подборка (если в чем ошибки — извиняйте, делалось на скорую руку):
подборка
# SqLite
Select * from Table LIMIT {count} OFFSET {offset}
Select * from Table LIMIT {offset},{count}

# Hypersonic SQL
SELECT LIMIT {offset} {limit} * FROM Table

# MySQL:
SELECT * FROM Table LIMIT {offset}, {count}

# Postgree SQL:
SELECT * FROM Table LIMIT {count} OFFSET {offset}

# Apache Derby
SELECT * FROM Table OFFSET {offset} ROWS FETCH NEXT {count} ROWS ONLY;

# MS SQL 2005+
SELECT t1.*
FROM (
SELECT ROW_NUMBER OVER(ORDER BY id) AS row, t1.*
FROM ( SELECT * from Table ) t1
) t2
WHERE t2.row BETWEEN @offset+1 AND @offset+count;

# MS SQL 2000:
SELECT TOP offset *
FROM Table
WHERE Id NOT IN (
SELECT TOP count Id
FROM Table
ORDER BY Id
);

# Oracle
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;

# IBM DB2, version 1:
SELECT * FROM (SELECT * FROM Table
ORDER BY Id DESC FETCH FIRST {offset} ROWS ONLY ) AS t1
ORDER BY t1.id ASC FETCH FIRST {count} ROWS ONLY

# IBM DB2, version 2:
SELECT *
FROM (
SELECT row_number() OVER ( ORDER BY Id ) AS rid, *
FROM Table
) AS t
WHERE t.rid BETWEEN {offset+1} AND {offset}+{count};

# Sybase:
* в Sybase нужно провести рисерчь о «set rowcount», зарыдать и сдаться пойти писать эмуляцию rowcount, с которой потом пытаться сделать то же что в Oracle, DB2 и MS SQL *

Дополню:
--Firebird
SELECT FIRST {count} [SKIP {offset}] FROM Table

Sybase разный и это компания была такая. В SQL Anywhere есть нормальное TOP {n} START AT {m} (точно не помню, вроде ещё через LIMIT...OFFSET есть), а вот Enterprise они не любят.
Ваша правда.
В тексте же имелся в виду Sybase ASE какой-нибудь средне-бородатой версии.
Полагаю что мало что там изменилось с тех пор, да и IQ вроде как ногами из ASE растёт.
А может просто почитать книгу про то как писать SQL и не будет тогда проблем с парадигмами программирования?
Но JOIN'ы в самом деле выполняются медленно. Особенно на больших объёмах данных. Зачастую куда выгоднее отдать соединение на клиентскую (относительно СУБД) часть, чем грузить сервер.
Клиентская часть может быть ещё медленнее.
Я придерживаюсь правила что все необходимые данные я должен получить в уже готовом виде и по возможности (даже если данные логически не связаны) единственным запросом. И довольно странно обрабатывать данные на платформе, которая не заточена под обработку больших об'ёмов.
Клиентская часть почти наверняка будет медленнее в абсолютных значениях, зато она гораздо лучше масштабируется горизонтально (серверов приложений можно наставить сколько угодно, с отдельными клиентскими приложениями ещё проще), в то время как СУБД под тяжёлыми запросами может стать бутылочным горлышком, которое расширить будет существенно сложнее.

Из практики, у нас в проекте используется Firebird (архитектура сервера СУБД), в БД есть одна достаточно большая таблица (~100 полей, несколько миллионов записей, постоянно обновляемая, ~3/4 полей — ссылки на другие таблицы-справочники). Поскольку справочники обновляются существенно реже и их величина существенно меньше, их содержимое кэшируется на клиенте, а выборка по большой таблице идёт без объединений. И это на практике работает в разы быстрее, чем с объединениями (в зависимости от количества необходимых для отображения полей).

Я понимаю, что это сильно зависит от используемой СУБД, и FB зачастую теряет голову от обилия полей и индексов, но в общем случае объединение операция достаточно накладная и как поведёт себя планировщик запроса, предсказать не всегда возможно (а ещё его поведение может, например, отличаться от версии к версии или измениться после пересчёта статистики индексов по БД). Поэтому если выборка идёт по большому объёму данных, а результатом её является маленький объём данных, то лучше перебдеть, чем в какой-нибудь прекрасный момент повесить СУБД.
11. Если мы говорим о Java, то еще можно добавить избегание всяческих ORM фреймворков. ORM парадигма представляет граф объектов как если бы они лежали в памяти, автоматически запрашивая данные из базы. Соответственно пользователь пишет код, не задумываясь о том, как это реально выполняется. На настольной системе все работает и выглядит красиво. С реальными же объемами данных наступают проблемы.
P.S. Недавно открыл для себя jOOQ.

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

ORM фреймворки — это лишь инструмент. В умелых руках ORM делает в точности то, что от него требуется, в неумелых же даже чистый SQL может работать хуже ORM. А уж если писать код "не задумываясь о том, как это реально выполняется", то и без СУБД можно такое Г сваять, что мама не горюй…

Насчет «реальных» объемов данных: я 10+ лет в Enterprise Java, на всех проектах ORM Hibernate, и мне ни разу не встречались такие объемы данных, при которых проблемы с производительностью возникали бы из-за применения ORM, а не из-за кривых рук программиста. Да, мне не довелось разрабатывать для майл.ру или гугла с их объемами, также, как и 99.9-ти процентам программистов мира. Максимум, с которым довелось поработать — десятки миллионов записей и производительность в десятки миллионов запросов к этим записям в сутки.

Так что, Вы, мягко говоря, рано крест ставите на ORM…
Это понятно, что при правильном использовании ORM также эффективен. Но мой опыт работы в реальных проектах показывает, что ORM везде используется неэффективно. Знание принципов работы ORM требует большей подготовки, нежели просто знание SQL, а его использование — меньшей. Поэтому сайтоклепатель или джавист-джуниор с легкостью напишет:
for ( Invoice in: em.createQuery(«SELECT i FROM Invoice i»).getResultList() ) {
… in.getClient().getName();
}
Для него это логично, несмотря на то, что клиент лежит в другой талице, и будет запрашиваться для каждого счета. Если Вы работаете в команде над достаточно большим проектом, за этим не уследить. Тем более что сам дизайн ORM всячески агитирует всех так делать.

В последнем проекте пришлось работать с относительно небольшим объемом связанных специальным образом данных, для которых приходилось производить процедуру проверки на целостность — много массивных запросов и джоинов. В итоге:
— 3 багрепорта в Hibernate JRA: проблемы связаны с compound PK: некорректной генерацией JOIN-ов для H2, некорректным связыванием в IN(), некорректной генерацией UPDATE WHERE x IN(SELECT...)
— 2 багрепорта в EclipseLink: неверной генерацией NOT IN(SELECT...), неверной генерацией UPDATE WHERE x IN(SELECT...)
При этом использовались последние версии обеих ORM и базы Oracle/H2.
— Стандарт ORM сильно ограничивал возможности запросов. В частности не поддерживаются: подзапросы FROM (SELECT), JOIN-ы между любыи сущностями (только те, для которых определено отношение в ORM).
— странности в поведении при попытке делать merge сущности с коллекцией элементов, замепленных в другую таблицу (EclipseLink).
— непредсказуемость, обусловленная внутренним кешированием объектов: UPDATE не изменяет полей объекта, если он ранее где-то испльзовался и был запрошен. Повторный SELECT и em.find() опять вернет старую версию объекта (тем не менее WHERE будет реагировать на изменения). Единственно верный способ — вызывать em.refresh() для каждого объекта (долго).
— сложности в передаче объектов по сети (deproxying, lazy fetch).

В итоге я не увидел практически никаких преимуществ использования ORM в проекте перед голым SQL-ем. Автоматический меппинг данных в структуры в состоянии написать любой java-программист. Библиотеки вроде jOOQ и QueryDSL делают код запросов typesafe и независимым от СУБД, полагаясь на SQL.
Согласен, ORM подталкивает к неэффективному использованию ресурсов СУБД. И, да — бывают неявные побочные эффекты и ошибки фреймворков (у себя в блоге не так давно писал об этом). Но, как мне кажется, главная задача ORM — чистота кода, к сожалению, нередко за это приходится платить ресурсами СУБД. Но ведь апгрейд даже фирменного «железа» стоит меньше, чем оплата труда программистов. Я потому и писал про личный опыт с объемами данных — в большинстве случаев (по крайней мере, в моей практике) можно пренебречь двух-трехкратным увеличением нагрузки на СУБД, в угоду чистоте кода, который легче поддерживать.

Вообще, насчет производительности СУБД мне понравилась фраза, которую написали в анонсе JBoss DataGrid. Это технология распределенного кеширования данных, и они написали о том, что она может сильно пригодиться тем, у кого нет нормальной СУБД (не дословно, но смысл примерно такой). Я это к тому, что Enterprise проекты (которые очень любят ORM) обычно крутятся на серьезных СУБД, например, Oracle. Я не специалист по СУБД, и ничего не рекламирую, но с Oracle я не включаю даже базовых функций кеширования, встроенных в Hibernate потому, что уверен на 100% в том, что Oracle гораздо лучше знает что и как кешировать.

По поводу ваших проблем с джойнами и прочими сложными запросами: лично я никогда не формирую сложные запросы на языке ORM (в моем случае Hibernate-овский HQL). В Hibernate отличная поддержка native query: можно отдать Hibernate-у чистый SQL, а получить в ответ замапленные сущности (т.е., те же, что вернул бы HQL).

Насчет Вашего последнего абзаца — очень, очень спорное заявление. То, что "в состоянии написать любой java-программист", должен будет когда-то поддерживать и дорабатывать другой java-программист, и с почти 100% уверенностью можно сказать, что проклятий в сторону первого будет немало. ORM, кроме непосредственно технологий, являются еще и неким подмножеством языка, знакомым большинству программистов в отрасли. Когда приходит новый человек на проект и видит Hibernate, у него вряд ли возникнет вопрос «зачем это здесь», а вот если он видит очередной велосипед… Опять же, на личном опыте: 10 лет назад, когда я был совсем джуниор, старший программист решил внедрить ORM в один из общих модулей проекта. Я тогда разве что головой об стол не бился, пытаясь понять какой велосипедист придумал Hibernate, а потом проникся. Я сейчас даже в микропроектах его использую, очень удобно…
ORM следует использовать ОЧЕНЬ осторожно на больших объемах данных.
Да и на маленьких тоже, особенно когда начинаются каскадные операции средствами Hibernate и много-много действий в пределах одной транзакции. Особенно все усугубляется при использовании JPA на кластере.
При использовании подобных инструментов сервисы для работы с БД должны быть очень хорошо продуманными, и делать их должен хорошо прокачанный спец. А остальные юзают имеющиеся интерфейсы. Да, и, кстати: долой LAZY-инициализацию! Лучше покрыть код тестами и доставать ровно то, что нужно. Иначе длительные раздумия над неэффективными запросами светят вам до конца проекта. Если он вообще доживет до конца. :-)
Как ни печально, в наших реалиях лишь 5-10% разработчиков серверного кода понимают и умеют работать с разными типами блокировок и уровнями изоляции транзакций.
"На больших объемах данных" вообще ВСЁ следует делать осторожно, "да и на маленьких тоже" ;) Опять же личный опыт: пару месяцев назад промышленный сервер приложений начал грузить СУБД под 100%, в конце-концов «съев» долгими (от нескольких секунд до 10-20 секунд каждый) запросами все свободные соединения к БД. Когда начали разбираться, оказалось, что один из разработчиков, создав кучу индексов в тестовой базе, забыл добавить в сборку скрипт на создание одного из них. Отсутствие одного только индекса «положило» критичную промышленную Систему, причем на запросах к маленькой по меркам Oracle таблице (200-300 тысяч записей). Имеет ли это какое-то отношение к ORM? Нет, т.к. база нагружалась еще до того, как дело доходило до выборки Hibernate-ом «неоптимальных» каскадных данных.

"усугубляется при использовании JPA на кластере" — даже не знаю, к чему Вы это. Я две легаси Системы переводил на кластерную конфигурацию, и не припомню, чтобы были проблемы с ORM. Вообще, ни одной проблемы, по крайней мере, появившейся именно из-за перехода на кластер. Да, конечно, пришлось менять логику, делать синхронизацию, но подвохов от ORM не было, т.е., если бы Hibernate не применялся в проекте, переход не стал бы проще или быстрее.

"лишь 5-10% разработчиков серверного кода понимают и умеют" — только в этом и кроется главная проблема с производительностью СУБД. Ни конкретный язык программирования, ни применение/отсутствие ORM не играют столь же ощутимой роли, как степень «кривизны рук» разработчика.

PS Насчет широко распространенного мнения о том, что ORM неоптимально выбирает из БД кучу ненужной информации: если ваша бизнес-логика сделана так, что, например, для отображения списка каких-либо документов требуются данные из 10-ти таблиц, то хоть с ORM, хоть без него — вам таки придется вытащить эту «кучу ненужной информации» и показать пользователю. Поэтому, проблемы повышенной нагрузки на СУБД гораздо эффективней решать пересмотром логики/архитектуры Системы, а не плевками в сторону ORM. Если переиначить известную народную мудрость: плохому программисту ORM мешает… ;)

Спасибо за перевод! Очень интересно. Даже где-то актуально в 2021ом году. Только тогда партиционирования и наследования таблиц почти не было.

Only those users with full accounts are able to leave comments. Log in, please.