Я был приятно удивлен, увидев, насколько популярным был мой недавний список о 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.
Дополнительная справочная информация:
- Предостережения о ценности привязки: интересное сообщение в блоге Oracle Guru Tanel Poder на эту тему
- Курсор обмена. Интересный вопрос переполнения стека .
The Cure :
По умолчанию всегда используйте PreparedStatements вместо статических операторов. По умолчанию никогда не вставляйте встроенные значения в ваш SQL.
2. Возврат слишком большого количества столбцов
Эта ошибка встречается довольно часто и может привести к очень плохим последствиям как в плане выполнения вашей базы данных, так и в вашем приложении Java. Давайте сначала посмотрим на второй эффект:
Плохое влияние на Java-приложение:
Если вы выбираете * (звездочка) или набор из 50 столбцов «по умолчанию», который вы повторно используете в различных DAO, вы переносите много данных из базы данных в JDBC ResultSet. Даже если вы не читаете данные из ResultSet, они были переданы по проводам и загружены в вашу память драйвером JDBC. Это пустая трата ввода-вывода и памяти, если вы знаете, что вам понадобятся только 2-3 из этих столбцов.
Это было очевидно, но остерегайтесь также …
Плохое влияние на план выполнения базы данных:
Эти эффекты могут быть намного хуже, чем эффекты на Java-приложении. Сложные базы данных выполняют много преобразований SQL при расчете наилучшего плана выполнения для вашего запроса. Вполне возможно, что некоторые части вашего запроса можно «преобразовать», зная, что они не будут вносить вклад в проекцию (предложение SELECT) или в предикаты фильтрации. Недавно я писал об этом в контексте метаданных схемы:
как метаданные схемы влияют на преобразования запросов Oracle
Теперь это настоящий зверь. Подумайте о сложном SELECT, который объединит два представления:
SELECT * FROM customer_view c JOIN order_view o ON c.cust_id = o.cust_id
Каждое из представлений, которые присоединяются к указанной выше ссылке на объединенную таблицу, может снова объединять данные из десятков таблиц, таких как CUSTOMER_ADDRESS, ORDER_HISTORY, ORDER_SETTLEMENT и т. Д. Учитывая проекцию SELECT *, ваша база данных не имеет другого выбора, кроме как полностью выполнить загрузку все эти объединенные таблицы, когда единственное, что вас интересовало, это:
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 таким образом, что большинство «скрытых» объединений может быть удалено, что приводит к гораздо меньшему количеству операций ввода-вывода и потребления памяти в базе данных.
The Cure :
Никогда не выполняйте SELECT *. Никогда не используйте один и тот же прогноз для различных запросов. Всегда старайтесь уменьшить проекцию на данные, которые вам действительно нужны.
Обратите внимание, что это может быть довольно трудно достичь с помощью ORM.
3. Думая, что JOIN является предложением SELECT
Это не ошибка, которая сильно влияет на производительность или корректность SQL, но, тем не менее, разработчики SQL должны учитывать тот факт, что предложение JOIN не является частью оператора SELECT как такового. Стандарт SQL 1992 определяет table reference как таковой:
6.3 <table reference>
<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>
В FROM предложении, а также в joined table банке можно использовать такие table references:
7.4 <from clause>
<from clause> ::=
FROM <table reference> [ { <comma> <table reference> }... ]
7.5 <joined table>
<joined table> ::=
<cross join>
| <qualified join>
| <left paren> <joined table> <right paren>
<cross join> ::=
<table reference> CROSS JOIN <table reference>
<qualified join> ::=
<table reference> [ NATURAL ] [ <join type> ] JOIN
<table reference> [ <join specification> ]
Реляционные базы данных очень ориентированы на таблицы. Многие операции выполняются с физическими, объединенными или производными таблицами тем или иным способом. Чтобы эффективно писать SQL, важно понимать, что в этом SELECT .. FROM предложении ожидается разделенный запятыми список ссылок на таблицы в любой форме, в которой они могут быть предоставлены.
В зависимости от сложности ссылки на таблицу, некоторые базы данных также принимают сложные ссылки на таблицы в других операторах, таких как INSERT, UPDATE, DELETE, MERGE. См. , Например , руководства Oracle , объясняющие, как создавать обновляемые представления.
The Cure :
Всегда думайте о своем FROM предложении, чтобы ожидать ссылки на таблицу в целом. Если вы пишете JOIN предложение, думайте, что это JOIN предложение является частью сложной ссылки на таблицу:
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 условия как для предикатов фильтрации, так и для предикатов соединения является серьезной проблемой. Рассмотрим этот простой пример:
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 (+) .
The Cure :
Всегда используйте синтаксис ANSI JOIN. Никогда не помещайте предикаты JOIN в WHERE предложение. Использование синтаксиса pre-ANSI JOIN абсолютно бесполезно.
5. Забыть экранирование от предиката LIKE
Стандарт SQL 1992 определяет like predicate как таковой:
8.5 <like predicate>
<like predicate> ::=
<match value> [ NOT ] LIKE <pattern>
[ ESCAPE <escape character> ]
ESCAPE Ключевое слово следует использовать почти всегда , когда позволяет пользовательский ввод , которые будут использоваться в запросах SQL. Хотя это может быть редко, когда знак процента (%) фактически должен быть частью данных, подчеркивание (_) вполне может быть:
SELECT * FROM t WHERE t.x LIKE 'some!_prefix%' ESCAPE '!'
The Cure :
Всегда думайте о правильном экранировании при использовании предиката LIKE.
6. Думая, что NOT (A IN (X, Y)) является булевой инверсией A IN (X, Y)
Это тонкий, но очень важный по отношению к NULL! Давайте рассмотрим, что на A IN (X, Y) самом деле означает:
A IN (X, Y) is the same as A = ANY (X, Y) is the same as A = X OR A = Y
Когда в то же время NOT (A IN (X, Y)) действительно означает:
NOT (A IN (X, Y)) is the same as A NOT IN (X, Y) is the same as A != ANY (X, Y) is the same as 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 для себя. Это показывает, что следующий запрос не дает результата:
SELECT 1 WHERE 1 IN (NULL) UNION ALL SELECT 2 WHERE NOT(1 IN (NULL))
Более подробную информацию можно увидеть в моем предыдущем посте на эту тему , где также показаны некоторые несовместимости диалектов SQL в этой области.
The Cure :
Остерегайтесь NOT IN предикатов, когда участвуют обнуляемые столбцы!
7. Думая, что NOT (A IS NULL) совпадает с A NOT NULL
Итак, мы вспомнили, что SQL реализует трехзначную логику, когда дело доходит до обработки NULL. Вот почему мы можем использовать предикат NULL для проверки значений NULL. Правильно? Правильно.
Но даже предикат NULL является тонким. Помните, что два следующих предиката эквивалентны только для выражений значения строки степени 1:
NOT (A IS NULL) is not the same as A IS 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
Подробности смотрите в моем предыдущем посте на эту тему .
The Cure :
При использовании выражений значения строки остерегайтесь предиката NULL, который может работать не так, как ожидалось.
8. Не использовать выражения значения строки там, где они поддерживаются
Выражения значений строк — это отличная функция SQL. Когда SQL является языком, очень ориентированным на таблицы, таблицы также очень ориентированы на строки. Выражения значений строк позволяют гораздо проще описывать сложные предикаты, создавая локальные специальные строки, которые можно сравнивать с другими строками той же степени и типа строки. Простой пример — запросить у клиентов имена и фамилии одновременно.
SELECT c.address FROM customer c, WHERE (c.first_name, c.last_name) = (?, ?)
Как можно видеть, этот синтаксис немного более лаконичен, чем эквивалентный синтаксис, где каждый столбец с левой стороны предиката сравнивается с соответствующим столбцом с правой стороны. Это особенно верно, если многие независимые предикаты объединяются с AND. Использование выражений значений строк позволяет объединять коррелированные предикаты в один. Это наиболее полезно для выражений соединения на составных внешних ключах:
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, могут использовать их для расчета более эффективных планов выполнения. Это объясняется на популярной странице « Использование индекса», Люк .
The Cure :
Используйте выражения значения строки всякий раз, когда можете. Они сделают ваш SQL более кратким и, возможно, даже быстрее.
9. Не достаточно определения ограничений
Итак, я собираюсь процитировать Тома Кайта и снова использовать «Индекс», Люк . Вы не можете иметь достаточно ограничений в своих метаданных. Прежде всего, ограничения помогают вам предотвратить повреждение данных, что уже очень полезно. Но для меня, что более важно, ограничения помогут базе данных выполнять преобразования SQL, так как база данных может решить, что
- Некоторые значения эквивалентны
- Некоторые пункты излишни
- Некоторые пункты являются «пустыми» (т.е. они не будут возвращать никаких значений)
Некоторые разработчики могут подумать, что ограничения медленные. Наоборот, если вы не вставляете много-много данных, в случае чего вы можете либо отключить ограничения для большой операции, либо использовать временную «таблицу загрузки» без ограничений, перенося данные в автономный режим в реальную таблицу.
The Cure :
Определите столько ограничений, сколько сможете. Они помогут вашей базе данных работать лучше при запросах.
10. Думая, что 50 мс — быстрое выполнение запроса
Шумиха вокруг NoSQL все еще продолжается, и многие компании по-прежнему считают, что им Twitter или Facebook крайне необходимы более быстрые и масштабируемые решения, позволяющие избежать масштабирования по горизонтали с помощью ACID и реляционных моделей. Некоторые могут преуспеть (например, Twitter или Facebook), другие могут столкнуться с этим:
Найдено здесь: https://twitter.com/codinghorror/status/347070841059692545
Для тех, кто вынужден (или выбрал) придерживаться проверенных реляционных баз данных, не думайте, что современные базы данных работают медленно. Они очень быстрые. На самом деле они настолько быстрые, что могут анализировать ваш 20-килобайтный текст запроса, рассчитывать планы выполнения на 2000 строк и фактически выполнять этого монстра менее чем за миллисекунду, если вы и ваш администратор баз данных хорошо ладите и настраиваете свою базу данных на максимум ,
Они могут быть медленными из-за того, что ваше приложение неправильно использует популярный ORM или потому что ORM не сможет генерировать быстрый SQL для вашей сложной логики запросов. В этом случае вы можете выбрать более ориентированный на SQL API, такой как JDBC , jOOQ или MyBatis , который позволит вам вернуть контроль над SQL.
Поэтому не думайте, что выполнение запроса в 50 мс является быстрым или даже приемлемым. Это не. Если вы получаете эти скорости во время разработки, обязательно изучите планы выполнения. Эти монстры могут взорваться в производстве, где у вас есть более сложные контексты и данные.
Вывод
SQL — это очень весело, но очень тонко и по-разному. Нелегко сделать это правильно, как показал мой предыдущий пост в блоге о 10 распространенных ошибках . Но SQL можно освоить, и оно того стоит. Данные — ваш самый ценный актив. Относитесь к данным с уважением и лучше пишите SQL.