Еще 10 распространенных ошибок, которые делают Java-разработчики при написании SQL (вы не поверите последнему)

Еда на вынос:

Хотя вышеперечисленное, безусловно, поможет вам обойти некоторые реальные проблемы, которые могут возникнуть у вас с вашим любимым ORM, вы также можете сделать еще один шаг вперед и подумать об этом. После всех этих лет боли и страданий от  несоответствия объектно-реляционного импеданса экспертная группа JPA 2.1 теперь пытается вырваться из этого безумия аннотаций, добавив больше декларативных, основанных на аннотациях подсказок графика выборки к запросам JPQL, что нет можно отлаживать, не говоря уже о поддержке.

Альтернатива — простой и понятный SQL. А с Java 8 мы добавим функциональное преобразование через Streams API. Это трудно победить .

Но, разумеется, ваши взгляды и опыт по этому вопросу могут отличаться от наших, поэтому давайте перейдем к более объективному обсуждению…

6. Не использовать общие табличные выражения

Хотя обычные табличные выражения, очевидно, предлагают улучшения читабельности, они также могут предлагать улучшения производительности. Рассмотрим следующий запрос, с которым я недавно столкнулся в клиентском пакете PL / SQL (не фактический запрос):

SELECT round (
  (SELECT amount FROM payments WHERE id = :p_id)
    *
  (
    SELECT e.bid
    FROM   currencies c, exchange_rates e
    WHERE  c.id     =
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.cur_id =
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.org_id =
      (SELECT org_id FROM payments WHERE id = :p_id)
  ) / (
    SELECT c.factor
    FROM   currencies c, exchange_rates e
    WHERE  c.id     =
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.cur_id =
      (SELECT cur_id FROM payments WHERE id = :p_id)
    AND    e.org_id =
      (SELECT org_id FROM payments WHERE id = :p_id)
  ), 0
)
INTO amount
FROM dual;

Так, что это делает? Это по существу конвертирует сумму платежа из одной валюты в другую. Давайте не будем слишком углубляться в бизнес-логику, давайте сразу перейдем к технической проблеме. Приведенный выше запрос приводит к следующему плану выполнения (в Oracle):

------------------------------------------------------
| Operation                         | Name           |
------------------------------------------------------
| SELECT STATEMENT                  |                |
|  TABLE ACCESS BY INDEX ROWID      | PAYMENTS       |
|   INDEX UNIQUE SCAN               | PAYM_PK        |
|   NESTED LOOPS                    |                |
|    INDEX UNIQUE SCAN              | CURR_PK        |
|     TABLE ACCESS BY INDEX ROWID   | PAYMENTS       |
|      INDEX UNIQUE SCAN            | PAYM_PK        |
|    TABLE ACCESS BY INDEX ROWID    | EXCHANGE_RATES |
|     INDEX UNIQUE SCAN             | EXCH_PK        |
|      TABLE ACCESS BY INDEX ROWID  | PAYMENTS       |
|       INDEX UNIQUE SCAN           | PAYM_PK        |
|      TABLE ACCESS BY INDEX ROWID  | PAYMENTS       |
|       INDEX UNIQUE SCAN           | PAYM_PK        |
|     NESTED LOOPS                  |                |
|      TABLE ACCESS BY INDEX ROWID  | CURRENCIES     |
|       INDEX UNIQUE SCAN           | CURR_PK        |
|        TABLE ACCESS BY INDEX ROWID| PAYMENTS       |
|         INDEX UNIQUE SCAN         | PAYM_PK        |
|      INDEX UNIQUE SCAN            | EXCH_PK        |
|       TABLE ACCESS BY INDEX ROWID | PAYMENTS       |
|        INDEX UNIQUE SCAN          | PAYM_PK        |
|       TABLE ACCESS BY INDEX ROWID | PAYMENTS       |
|        INDEX UNIQUE SCAN          | PAYM_PK        |
|  FAST DUAL                        |                |
------------------------------------------------------

Фактическое время выполнения в этом случае незначительно, но, как вы можете видеть, одни и те же объекты снова и снова доступны в запросе. Это нарушение  распространенной ошибки № 4: выполнение одного и того же запроса все время .

Все это было бы намного проще читать, поддерживать и выполнять Oracle, если бы мы использовали общее табличное выражение. Из исходного исходного кода обратите внимание на следующее:

-- We're always accessing a single payment:
  FROM payments WHERE id = :p_id
 
-- Joining currencies and exchange_rates twice:
  FROM currencies c, exchange_rates e

Итак, давайте сначала рассмотрим оплату:

-- "payment" contains only a single payment
-- But it contains all the columns that we'll need
-- afterwards
WITH payment AS (
    SELECT cur_id, org_id, amount
    FROM   payments
    WHERE  id = :p_id
)
SELECT round(p.amount * e.bid / c.factor, 0)
 
-- Then, we simply don't need to repeat the
-- currencies / exchange_rates joins twice
FROM   payment p
JOIN   currencies c     ON  p.cur_id = c.id
JOIN   exchange_rates e ON  e.cur_id = p.cur_id
                        AND e.org_id = p.org_id

Обратите внимание, что мы также заменили списки таблиц на ANSI JOIN, как предложено  в нашем предыдущем списке

Вы не поверите, что это тот же запрос, не так ли? А как насчет плана выполнения? Вот!

---------------------------------------------------
| Operation                      | Name           |
---------------------------------------------------
| SELECT STATEMENT               |                |
|  NESTED LOOPS                  |                |
|   NESTED LOOPS                 |                |
|    NESTED LOOPS                |                |
|     FAST DUAL                  |                |
|     TABLE ACCESS BY INDEX ROWID| PAYMENTS       |
|      INDEX UNIQUE SCAN         | PAYM_PK        |
|    TABLE ACCESS BY INDEX ROWID | EXCHANGE_RATES |
|     INDEX UNIQUE SCAN          | EXCH_PK        |
|   TABLE ACCESS BY INDEX ROWID  | CURRENCIES     |
|    INDEX UNIQUE SCAN           | CURR_PK        |
---------------------------------------------------

Нет сомнений, что это намного лучше.

Лечение:

Если вам повезло, и вы используете одну из тех баз данных, которая поддерживает оконные функции, шансы невероятно высоки (100%), что у вас также есть поддержка общих табличных выражений. Это еще одна причина для вас перейти с MySQL на PostgreSQL или оценить тот факт, что вы можете работать с потрясающей коммерческой базой данных.

Общие табличные выражения похожи на локальные переменные в SQL. В каждом крупном утверждении вы должны рассмотреть возможность их использования, как только почувствуете, что написали что-то раньше.

Еда на вынос:

Некоторые базы данных (например,  PostgreSQL или  SQL Server ) также поддерживают общие табличные выражения для операторов DML. Другими словами, вы можете написать:

WITH ...
UPDATE ...

Это делает DML невероятно более мощным.

7. Не использовать выражения значения строки для ОБНОВЛЕНИЙ

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

Но мало кто знает, что они также могут быть использованы в  UPDATE утверждении, в большинстве баз данных. Посмотрите на следующий запрос, который я снова нашел в клиентском PL / SQL-пакете (опять же, конечно, еще проще):

UPDATE u
SET n = (SELECT n + 1    FROM t WHERE u.n = t.n),
    s = (SELECT 'x' || s FROM t WHERE u.n = t.n),
    x = 3;

Таким образом, этот запрос принимает подзапрос в качестве источника данных для обновления двух столбцов, а третий столбец обновляется «регулярно». Как это работает? Умеренно:

-----------------------------
| Operation          | Name |
-----------------------------
| UPDATE STATEMENT   |      |
|  UPDATE            | U    |
|   TABLE ACCESS FULL| U    |
|   TABLE ACCESS FULL| T    |
|   TABLE ACCESS FULL| T    |
-----------------------------

Давайте проигнорируем полное сканирование таблицы, так как этот запрос построен. Фактический запрос может использовать индексы. Но  T доступен дважды, т.е. в обоих подзапросах. В этом случае Oracle, похоже, не смог применить  скалярное кеширование подзапросов .

На помощь: выражения значений строк. Давайте просто перефразируем наше  UPDATE к этому:

UPDATE u
SET (n, s) = ((
      SELECT n + 1, 'x' || s FROM t WHERE u.n = t.n
    )),
    x = 3;

Давайте проигнорируем забавный, специфичный для Oracle синтаксис двойных скобок для правой части такого назначения выражения значения строки, но давайте оценим тот факт, что мы можем легко назначить новое значение кортежу  (n, s) за один раз ! Обратите внимание, что мы могли бы также написать это и назначить  x:

UPDATE u
SET (n, s, x) = ((
      SELECT n + 1, 'x' || s, 3
      FROM t WHERE u.n = t.n
    ));

Как и следовало ожидать, план выполнения также улучшился и  T доступен только один раз:

-----------------------------
| Operation          | Name |
-----------------------------
| UPDATE STATEMENT   |      |
| UPDATE             | U    |
| TABLE ACCESS FULL  | U    |
| TABLE ACCESS FULL  | T    |
-----------------------------

Лечение:

Используйте выражения значения строки. Где бы вы ни могли. Они делают ваш SQL-код невероятно более выразительным, и, скорее всего, они также ускоряют его.

Обратите внимание, что вышесказанное поддерживается  оператором UPDATE в jOOQ . В этот момент мы хотим, чтобы вы узнали об этой дешевой рекламе в статье:

😉

8. Использование MySQL, когда вы можете использовать PostgreSQL

Для некоторых это может показаться чем-то вроде хипстерской дискуссии. Но давайте рассмотрим факты:

• MySQL претендует на звание «самой популярной базы данных с открытым кодом».
• PostgreSQL претендует на звание «самой совершенной базы данных с открытым исходным кодом».

Давайте рассмотрим немного истории. MySQL всегда был очень прост в установке, обслуживании, и у него было отличное и активное сообщество. Это привело к тому, что MySQL по-прежнему остается предпочтительной СУБД практически для каждого веб-хостера на этой планете. Эти хостеры также поддерживают PHP, который был одинаково прост в установке и обслуживании.

НО!

Мы, Java-разработчики, склонны иметь мнение о PHP, верно? Это суммировано этим изображением здесь:

PHP Молот

Ну, это работает, но как это работает?

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

Многие люди выбирают MySQL в первую очередь из-за его цены (0,00 долларов США). Но часто одни и те же люди находят MySQL медленным и быстро приходят к выводу, что SQL сам по себе медленный — без оценки параметров. По этой же причине все хранилища NoSQL сравнивают себя с MySQL, а не с Oracle, базой данных, которая почти всегда выигрывает   тесты производительности Совета по обработке транзакций (TPC) . Несколько примеров:

• Сравнение Cassandra, MongoDB, MySQL
• Переключение с MySQL на Кассандру. За и против
• Миграции с MySQL на Кассандру
• Когда использовать MongoDB, а не MySQL

Хотя последняя статья прямо добавляет  «(и другие СУБД)», в  ней нет никаких подробностей, что эти  «другие СУБД»  делают неправильно. Это действительно только сравнивает MongoDB с MySQL.

Лечение:

Мы говорим: перестаньте жаловаться на SQL, когда на самом деле вы действительно жалуетесь на MySQL. Существует как минимум четыре очень популярные базы данных, которые невероятно хороши и в миллионы раз лучше, чем MySQL. Это:

• База данных Oracle
• SQL Server
• PostgreSQL
• MS Access

(шучу насчет последнего, конечно)

Еда на вынос:

Не поддавайтесь агрессивному маркетингу NoSQL. 10gen  —  чрезвычайно  хорошо финансируемая  компания, даже если MongoDB продолжает разочаровывать технически.

То же самое  верно и для  Datastax .

Обе компании решают проблему, с которой сталкиваются немногие. Они продают нам нишевые продукты как товары, заставляя нас думать, что наши  базы данных о  реальных товарах (RDBMS) больше не отвечают нашим потребностям. Они хорошо финансируются, и у них есть большие маркетинговые команды, которые могут выступать с прямыми заявлениями.

Тем временем  PostgreSQL  стал еще лучше, и вы, как читатель этого блога / поста, собираетесь сделать ставку на команду-победителя 🙂

… просто чтобы процитировать  Марка Мэдсена  еще раз:

История NoSQL в соответствии с @markmadsen #strataconf pic.twitter.com/XHXMJsXHjV

— Эдд Дамбилл (@edd)
12 ноября 2013 г.

Отказ от ответственности:

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

9. Забыть о журналах UNDO / REDO

Мы утверждали, что заявления MERGE или массовые / пакетные обновления хороши. Это правильно, но, тем не менее, вы должны быть осторожны при обновлении огромных наборов данных в транзакционных контекстах. Если ваша транзакция  «занимает слишком много времени» , то есть если вы обновляете 10 миллионов записей одновременно, вы столкнетесь с двумя проблемами:

• Вы увеличиваете риск состояния гонки, если другой процесс также записывает данные в ту же таблицу. Это может вызвать откат их или вашей транзакции, возможно, заставит вас снова развернуть огромное обновление
• Вы вызываете много параллелизма в вашей системе, потому что каждая другая транзакция / сеанс, которая хочет увидеть данные, которые вы собираетесь обновить, должна будет сначала временно откатить все ваши обновления, прежде чем они достигнут состояния на диске. это было там  до  вашего огромного обновления. Это цена КИСЛОТЫ.

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

Другой способ обойти эту проблему — часто фиксировать свою собственную работу, например, после 1000 вставок / обновлений.

В любом случае из-за  теоремы CAP вам придется пойти на компромисс. Частые коммиты создадут риск несовместимости базы данных в случае сбоя многомиллионного обновления после 5 миллионов (подтвержденных) записей. Откат тогда означал бы возврат  всех  изменений базы данных в сторону резервной копии.

Лечение:

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

Еда на вынос:

К этому времени поклонники NoSQL будут утверждать (опять же из-за чрезмерного маркетинга вышеупомянутыми компаниями), что NoSQL решил эту проблему, отбросив схемы и безопасность типов. «Не обновляйте, просто добавьте другое свойство!»  — Они сказали.

Но это не правда !

Во-первых, я могу добавлять столбцы в свою базу данных без каких-либо проблем. Выписка ALTER TABLE ADD выполняется мгновенно в реальных базах данных. Заполнение столбца данными также никого не беспокоит, потому что никто еще не читает столбец (помните, не надо  SELECT * !). Таким образом, добавление  столбцов в RDBMS столь  же дешево, как и добавление свойств JSON в документ MongoDB.

Но как насчет изменения столбцов? Удалить их? Слияние их?

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

Не верьте маркетологам. И пока вы занимаетесь этим, подумайте дважды  и забудьте, что мы  сами являемся  поставщиками инструментов SQL 😉 Вот снова «правильное» сообщение:

10. Не правильно использовать тип BOOLEAN

Это не совсем ошибка как таковая. Это просто то, что вряд ли кто-нибудь знает. Когда стандарт  SQL: 1999  ввел новый  BOOLEANтип данных, они действительно сделали это правильно. Потому что раньше у нас уже было что-то вроде логического значения в SQL. У нас были  <search condition> в SQL-92 , которые по существу являются предикатами для использования в  WHERE,  ONи  HAVINGстатьях, а также в  CASE выражении.

SQL: 1999, однако, просто определил новое  <boolean value expression> как регулярное  <value expression>и переопределил <search condition> так:

<search condition> ::=
    <boolean value expression>

Готово! Теперь, для большинства из нас, Java / Scala / и т.д. разработчиков, это не похоже на такое новшество. Черт возьми  boolean. Очевидно, что он может быть взаимозаменяемо использован как предикат и как переменная.

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

Теперь, что это значит? Это означает, что вы можете использовать любой предикат также в качестве столбца! Например:

SELECT a, b, c
FROM (
  SELECT EXISTS (SELECT ...) a,
         MY_COL IN (1, 2, 3) b,
         3 BETWEEN 4 AND 5   c
  FROM   MY_TABLE
) t
WHERE a AND b AND NOT(c)

Согласен, это немного глупый запрос, но знаете ли вы, насколько это мощно?

К счастью, PostgreSQL снова полностью поддерживает это (в отличие от Oracle, у которого до сих пор нет типа данных BOOLEAN в SQL).

Лечение:

Время от времени использование  BOOLEAN типов кажется очень правильным, так что сделайте это! Вы можете преобразовать выражения логических значений в предикаты, а предикаты — в выражения логических значений. Они одинаковые. Это делает SQL таким мощным.

Вывод

В последние годы SQL постоянно развивался благодаря таким прекрасным стандартам, как SQL: 1999 ,  SQL: 2003 ,  SQL: 2008,  а теперь и  SQL: 2011 . Это единственный сохранившийся основной декларативный язык, теперь, когда XQuery можно считать довольно мертвым для основного потока. Это может быть легко смешано с процедурными языками, как показали PL / SQL и T-SQL (и другие процедурные диалекты). Он может быть легко смешан с объектно-ориентированными или функциональными языками, как  показал jOOQ  .

В  Data Geekery мы считаем, что SQL — лучший способ запроса данных. Вы не согласны с любым из вышеперечисленного? Это нормально, вам не нужно. Иногда даже мы согласны с Уинстоном Черчиллем, который, как известно, сказал:

SQL — худшая форма запросов к базе данных, за исключением всех других форм.

Но, как недавно сказал Яков Файн:

Вы можете запустить из SQL, но вы не можете скрыть

Итак, давайте лучше вернемся к работе и изучим этого зверя! Спасибо за чтение.