Статьи

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

Декабрь 19, 2018

Я был приятно удивлен, увидев, насколько популярным был мой недавний список о 10 распространенных ошибках, которые делают Java-разработчики при написании SQL , как в моем собственном блоге, так и в моем партнере по синдикации DZone . Популярность показывает пару вещей:

  • Насколько важен SQL для профессионального мира Java.
  • Как часто забывают о некоторых элементах SQL.
  • Насколько хорошо SQL-ориентированные библиотеки, такие как jOOQ или MyBatis , отвечают потребностям рынка, используя SQL . Забавный факт заключается в том, что пользователи даже упомянули мой пост в списке рассылки SLICK . SLICK — не-SQL-ориентированная библиотека доступа к базам данных в Scala. Как и LINQ (и LINQ-to-SQL), он сосредоточен на языковой интеграции , а не на генерации кода SQL.

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

1. Не использовать PreparedStatements

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

  • Они не знают о PreparedStatements
  • Они думают, что PreparedStatements медленнее
  • Они думают, что написание PreparedStatement требует больше усилий

Во-первых, давайте разберем вышеупомянутые мифы. В 96% случаев лучше писать PreparedStatement, а не статический оператор. Почему? По простым причинам:

  • Вы можете пропустить синтаксические ошибки, возникающие из-за неправильной конкатенации строк при встраивании значений связывания.
  • Вы можете исключить уязвимости SQL-инъекций из-за плохой конкатенации строк при встраивании значений связывания.
  • Вы можете избежать крайних случаев, когда встраиваете более «сложные» типы данных, такие как TIMESTAMP, двоичные данные и другие.
  • Вы можете некоторое время держать открытыми PreparedStatements, повторно используя их с новыми значениями связывания, вместо того, чтобы закрывать их немедленно (полезно, например, в Postgres).
  • Вы можете использовать адаптивное совместное использование курсоров (на языке Oracle) в более сложных базах данных. Это помогает предотвратить жесткий синтаксический анализ операторов SQL для каждого нового набора значений связывания.

Будучи убеждена? Да. Обратите внимание: в некоторых редких случаях вам действительно нужно встроить значения привязки, чтобы оптимизатор на основе затрат вашей базы данных получил некоторую информацию о том, на какие данные действительно повлияет запрос. Как правило, это приводит к «постоянным» предикатам, таким как:

  • УДАЛЕНО = 1
  • СОСТОЯНИЕ = 42

Но это не должно приводить к «переменным» предикатам, таким как:

  • FIRST_NAME НРАВИТСЯ «Jon%»
  • СУММА> 19,95

Обратите внимание, что современные базы данных реализуют просмотр переменных bind. Следовательно, по умолчанию вы также можете использовать значения связывания для всех параметров запроса. Также обратите внимание, что высокоуровневые API, такие как JPA CriteriaQuery или jOOQ , помогут вам генерировать PreparedStatements и очень просто и прозрачно связывать значения при написании встроенного JPQL или встроенного SQL.

Дополнительная справочная информация:

Лечение:

По умолчанию всегда используйте PreparedStatements вместо статических операторов. По умолчанию никогда не вставляйте встроенные значения привязки в ваш SQL.

2. Возврат слишком большого количества столбцов

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

Плохое влияние на Java-приложение:

Если вы выбираете * (звездочка) или набор из 50 столбцов «по умолчанию», который вы повторно используете в различных DAO, вы переносите много данных из базы данных в JDBC ResultSet. Даже если вы не читаете данные из ResultSet, они были переданы по проводам и загружены в вашу память драйвером JDBC. Это пустая трата ввода-вывода и памяти, если вы знаете, что вам понадобятся только 2-3 из этих столбцов.

Это было очевидно, но остерегайтесь также …

Плохое влияние на план выполнения базы данных:

Эти эффекты могут быть намного хуже, чем эффекты на Java-приложении. Сложные базы данных выполняют много преобразований SQL при расчете наилучшего плана выполнения для вашего запроса. Вполне возможно, что некоторые части вашего запроса можно «преобразовать», зная, что они не будут вносить вклад в проекцию (предложение SELECT) или в предикаты фильтрации. Недавно я писал об этом в контексте метаданных схемы:

Как метаданные схемы влияют на преобразования запросов Oracle

Теперь это настоящий зверь. Подумайте о сложном SELECT, который объединит два представления:

1
2
3
4
SELECT *
FROM   customer_view c
JOIN   order_view o
  ON   c.cust_id = o.cust_id

Каждое из представлений, которые присоединяются к указанной выше ссылке на объединенную таблицу, может снова объединять данные из десятков таблиц, таких как CUSTOMER_ADDRESS, ORDER_HISTORY, ORDER_SETTLEMENT и т. Д. Учитывая проекцию SELECT *, ваша база данных не имеет другого выбора, кроме как полностью выполнить загрузку все эти объединенные таблицы, когда единственное, что вас интересовало, это:

1
2
3
4
SELECT c.first_name, c.last_name, o.amount
FROM   customer_view c
JOIN   order_view o
  ON   c.cust_id = o.cust_id

Хорошая база данных преобразует ваш SQL таким образом, что большинство «скрытых» объединений может быть удалено, что приводит к гораздо меньшему количеству операций ввода-вывода и потребления памяти в базе данных.

Лечение:

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

Обратите внимание, что это может быть довольно трудно достичь с помощью ORM.

3. Думая, что JOIN является предложением SELECT

Это не ошибка, которая сильно влияет на производительность или корректность SQL, но, тем не менее, разработчики SQL должны учитывать тот факт, что предложение JOIN не является частью оператора SELECT как такового. Стандарт SQL 1992 определяет table reference на table reference следующим образом:

6.3 <ссылка на таблицу>

<ссылка на таблицу> :: =
<имя таблицы> [[AS] <имя корреляции>
[<left paren> <производный список столбцов> <right paren>]] | <производная таблица> [AS] <имя корреляции>
[<left paren> <производный список столбцов> <right paren>] | <объединенная таблица>

Предложение FROM , а также joined table могут затем использовать такие table references :

7.4 <из пункта>

<из предложения> :: =
FROM <ссылка на таблицу> [{<запятая> <ссылка на таблицу>}…]

7.5 <объединенная таблица>

<объединенная таблица> :: =
<перекрестное соединение>
| <квалифицированное соединение>
| <левый парень> <объединенный стол> <правый парень>

<cross join> :: =
<ссылка на таблицу> CROSS JOIN <ссылка на таблицу>

<квалифицированное соединение> :: =
<ссылка на таблицу> [NATURAL] [<тип соединения>] JOIN
<ссылка на таблицу> [<спецификация соединения>]

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

В зависимости от сложности ссылки на таблицу, некоторые базы данных также принимают сложные ссылки на таблицы в других операторах, таких как INSERT, UPDATE, DELETE, MERGE. См. , Например , руководства Oracle , объясняющие, как создавать обновляемые представления.

Лечение:

Всегда думайте о своем предложении FROM , чтобы ожидать ссылки на таблицу в целом. Если вы пишете предложение JOIN , представьте, что это предложение JOIN является частью ссылки на сложную таблицу:

1
2
3
4
5
6
SELECT c.first_name, c.last_name, o.amount
FROM
 
    customer_view c
      JOIN order_view o
      ON c.cust_id = o.cust_id

4. Использование pre-ANSI JOIN синтаксиса

Теперь, когда мы выяснили, как работают ссылки на таблицы (см. Предыдущий пункт), должно стать немного более очевидным, что синтаксис pre-ANSI JOIN следует избегать любой ценой. Для планов выполнения обычно не имеет значения, если указать предикаты соединения в предложении JOIN .. ON или в WHERE . Но с точки зрения удобочитаемости и обслуживания использование WHERE как для фильтрации предикатов, так и для предикатов соединения является серьезной проблемой. Рассмотрим этот простой пример:

1
2
3
4
5
6
SELECT c.first_name, c.last_name, o.amount
FROM   customer_view c,
       order_view o
WHERE  o.amount > 100
AND    c.cust_id = o.cust_id
AND    c.language = 'en'

Можете ли вы определить предикат соединения? Что, если мы присоединились к десяткам столов? Это становится намного хуже при применении проприетарных синтаксисов для внешнего объединения, таких как синтаксис Oracle (+) .

Лечение:

Всегда используйте синтаксис ANSI JOIN. Никогда не помещайте предикаты JOIN в WHERE . Использование синтаксиса pre-ANSI JOIN абсолютно бесполезно.

5. Забыть экранирование от предиката LIKE

Стандарт SQL 1992 определяет like predicate как таковой:

8.5 <как предикат>

<как предикат> :: =
<значение совпадения> [НЕ] НРАВИТСЯ <шаблон>
[ESCAPE <escape-символ>]

Ключевое слово ESCAPE должно использоваться почти всегда, когда допускается использование пользовательского ввода в ваших SQL-запросах. Хотя это может быть редко, когда знак процента (%) фактически должен быть частью данных, подчеркивание (_) вполне может быть:

1
2
3
SELECT *
FROM   t
WHERE  t.x LIKE 'some!_prefix%' ESCAPE '!'

Лечение:

Всегда думайте о правильном экранировании при использовании предиката LIKE.

6. Думая, что NOT (A IN (X, Y)) является булевой инверсией A IN (X, Y)

Это тонкий, но очень важный по отношению к NULL! Давайте рассмотрим, что на самом деле означает A IN (X, Y) :

A IN (X, Y)
такой же, как A = ЛЮБОЙ (X, Y)
такой же, как A = X ИЛИ A = Y

Когда в то же время NOT (A IN (X, Y)) действительно означает:

НЕ (A IN (X, Y))
такой же, как A NOT IN (X, Y)
такой же, как A! = ЛЮБОЙ (X, Y)
такой же, как A! = X AND A! = Y

Это выглядит как логическое обратное к предыдущему предикату, но это не так! Если какой-либо из X или Y имеет NULL , предикат NOT IN приведет к UNKNOWN тогда как предикат IN может все еще возвращать логическое значение.

Или, другими словами, когда A IN (X, Y) возвращает TRUE или FALSE , NOT(A IN (X, Y)) может по-прежнему UNKNOWN вместо FALSE или TRUE . Обратите внимание, что это также верно, если правая часть предиката IN является подзапросом.

Не верь этому? Посмотрите эту SQL Fiddle для себя. Это показывает, что следующий запрос не дает результата:

1
2
3
4
5
SELECT 1
WHERE     1 IN (NULL)
UNION ALL
SELECT 2
WHERE NOT(1 IN (NULL))

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

Лечение:

Остерегайтесь предиката NOT IN когда участвуют обнуляемые столбцы!

7. Думая, что NOT (A IS NULL) совпадает с A NOT NULL

Итак, мы вспомнили, что SQL реализует трехзначную логику, когда дело доходит до обработки NULL. Вот почему мы можем использовать предикат NULL для проверки значений NULL. Правильно? Правильно.

Но даже предикат NULL является тонким. Помните, что два следующих предиката эквивалентны только для выражений значения строки степени 1:

НЕ (НУЛЬ)
не совпадает с A NOT NOT NULL

Если A является выражением значения строки со степенью больше 1, то таблица истинности преобразуется так, что:

  • A IS NULL возвращает true, только если все значения в A равны NULL
  • NOT (A IS NULL) возвращает false, только если все значения в A равны NULL
  • A NOT NOT NULL возвращает true, только если все значения в A не равны NULL
  • NOT (A NOT NOT NULL) возвращает false, только если все значения в A не равны NULL

Подробности смотрите в моем предыдущем посте на эту тему .

Лечение:

При использовании выражений значения строки остерегайтесь предиката NULL, который может работать не так, как ожидалось.

8. Не использовать выражения значения строки там, где они поддерживаются

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

1
2
3
SELECT c.address
FROM   customer c,
WHERE (c.first_name, c.last_name) = (?, ?)

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

1
2
3
4
SELECT c.first_name, c.last_name, a.street
FROM   customer c
JOIN   address a
  ON  (c.id, c.tenant_id) = (a.id, a.tenant_id)

К сожалению, не все базы данных поддерживают выражения значений строк одинаково. Но стандарт SQL определил их уже в 1992 году , и если вы используете их, сложные базы данных, такие как Oracle или Postgres, могут использовать их для расчета лучших планов выполнения. Это объясняется на популярной странице « Использование индекса», Люк .

Лечение:

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

9. Не достаточно определения ограничений

Итак, я собираюсь процитировать Тома Кайта и снова использовать «Индекс», Люк . Вы не можете иметь достаточно ограничений в своих метаданных. Прежде всего, ограничения помогают вам предотвратить повреждение данных, что уже очень полезно. Но для меня, что более важно, ограничения помогут базе данных выполнять преобразования SQL, так как база данных может решить, что

  • Некоторые значения эквивалентны
  • Некоторые пункты излишни
  • Некоторые пункты являются «пустыми» (т.е. они не будут возвращать никаких значений)

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

Лечение:

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

10. Думая, что 50 мс — быстрое выполнение запроса

Шумиха вокруг NoSQL все еще продолжается, и многие компании по-прежнему считают, что им Twitter или Facebook крайне необходимы более быстрые, более масштабируемые решения, позволяющие избегать ACID и реляционных моделей для горизонтального масштабирования. Некоторые могут преуспеть (например, Twitter или Facebook), другие могут столкнуться с этим:

bnelf1gcuaexynu

Найдено здесь: https://twitter.com/codinghorror/status/347070841059692545

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

Они могут быть медленными из-за того, что ваше приложение неправильно использует популярный ORM или потому что ORM не сможет генерировать быстрый SQL для вашей сложной логики запросов. В этом случае вы можете выбрать более ориентированный на SQL API, такой как JDBC , jOOQ или MyBatis , который позволит вам вернуть контроль над SQL.

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

Вывод

SQL — это очень весело, но очень тонко и по-разному. Нелегко сделать это правильно, как показал мой предыдущий пост в блоге о 10 распространенных ошибках . Но SQL можно освоить, и оно того стоит. Данные — ваш самый ценный актив. Относитесь к данным с уважением и лучше пишите SQL.

37
Share: