10 потенциальных SQL ошибок, которые делают программисты
Оригинал статьи носит название «10 SQL ошибок, которые делают Java разработчики», но, по большому счёту, приведённые в ней принципы можно отнести к любому языку.
Java программисты мешают объектно-ориентированное и императивное мышление в зависимости от их уровня:
— мастерства (каждый может программировать императивно)
— догмы (шаблон для применения шаблонов где-либо и их именование)
— настроения (применять истинный объектный подход немного сложнее чем императивный)
Но всё меняется, когда Java разработчики пишут SQL код.
SQL — это декларативный язык, который не имеет ничего общего с объектно-ориентированным или императивным мышлением. Очень легко выразить запрос в SQ, но довольно трудно выразить его корректно и оптимально. Разработчикам не только необходимо переосмыслить их парадигму программирования, им нужно ещё и думать в рамках теории множеств (set theory).
Ниже перечислены общие ошибки, которые делают Java разработчики, использующие SQL в JDBC или jOOQ (без определённого порядка).
1. Забыл о NULL
Непонимание NULL — это скорее всего самая большая ошибка, которую Java разработчик может сделать, когда пишет SQL. Это может быть потому, что NULL ещё называется UNKNOWN. Если бы он назывался просто UNKNOWN, его было бы проще понять. Другая причина в том, что при получении данных и связывании переменных JDBC отражает SQL NULL в Java None. Это может привести к тому, что NULL = NULL (SQL) будет вести себя так же, как и None == None (JAVA).
Один из самых ярких примеров заблуждения о NULL — это когда NULL предикаты используются со строковым выражением (row value expression).
Другая, более специфическая проблема появляется при отсутствии понимания значения NULL в NOT IN anti-joins.
Лекарство:
Тренируй себя. Ничего сложного — во время написания SQL всегда думай о NULL:
— Этот предикат корректен относительно NULL?
— Влияет ли NULL на результат этой функции?
2. Обработка данных в памяти Java
Не многие Java программисты знают SQL очень хорошо. Случайный JOIN, странный UNION и ладно. А оконные функции? Группирующие наборы? Многие Java разработчики загружают SQL данные в память, трансформируют их в какую-нибудь подходящую коллекцию и выполняют нужные вычисления на этих коллекциях с многословными циклическими структурами (по-крайней мере до улучшения коллекций в JAVA 8).
Но некоторые SQL базы данных поддерживают дополнительные (SQL стандарт!) OLAP функции, которые подходят для этого лучше и являются более простыми в написании. Один из примеров (не стандарт) — это отличный оператор MODEL от Oracle. Просто позволь БД сделать обработку и вытащить результаты в память Java. Потому что, в конце концов, какой-то умный парень уже оптимизировал эти дорогие продукты. Итак, используя OLAP в БД, ты получаешь две вещи:
— Простоту. Скорее всего, проще писать правильно на SQL, чем на Java.
— Производительность. БД скорее всего будут быстрее чем твой алгоритм. И, что важнее, тебе не придётся тянуть миллионы записей по проводам.
Лекарство:
Каждый раз когда ты пишешь ориентированный на данные алгоритм с помощью Java, спрашивай себя: «Есть ли возможность переложить эту работу на базу данных?»
3. Использование UNION вместо UNION ALL
Позор тому, что UNION ALL требует дополнительного слова относительно UNION. Было бы намного лучше, если бы SQL стандарт был определён поддерживать:
— UNION (позволяет дублирование)
— UNION DISTINCT (убирает дублирование)
Удаление дубликатов не только реже используется, оно ещё и довольно медленно на больших результатах выборки, т.к. два под запроса должны быть упорядочены, и каждый кортеж должен быть сравнен с его последующим кортежем.
Помни, что даже если SQL стандарт определяет INTERSECT ALL и EXCEPT ALL, не каждая БД может реализовывать эти мало используемые наборы операций.
Лекарство:
Думай, хотел ли ты написать UNION ALL каждый раз, когда пишешь UNION.
4. Использование JDBC для постраничной разбивки большой выборки
Большинство БД поддерживают какие-то средства для постраничной разбивки через LIMIT… OFFSET, TOP… START AT, OFFSET… FETCH операторов. В отсутствии поддержки этих операторов всё ещё есть возможность наличия ROWNUM (Oracle) или ROW_NUMBER() OVER() фильтрации (DB2, SQL Server 2008 и другие), которые намного быстрее разбивки в памяти. Это относится преимущественно к большим смещениям!
Лекарство:
Просто используйте эти операторы, или инструмент(такой, как jOOQ), который может имитировать эти операторы за вас.
5. Соединение данных в памяти Java
С ранних дней SQL и до сих пор некоторые Java программисты с тяжелым сердцем пишут JOINы. У них есть устаревший страх того, что JOINы выполняются медленно. Это может быть так, если оптимизатор накладных расходов выбирает сделать вложенный цикл, загружая целые таблицы в память перед созданием ячеек присоединённой таблицы. Но это случается редко. С нормальными предикатами, ограничениями, индексами, MERGE JOIN или HASH JOIN операции выполняются очень быстро — всё зависит от корректных метаданных (Tom Kyte хорошо написал об этом). Тем не менее, наверняка ещё остались немногие Java разработчики, которые загружают две таблицы двумя отдельными запросами и соединяют их в памяти Java тем или иным способом.
Лекарство:
Если бы выбираете из разных таблиц на различных этапах, подумайте ещё раз, вдруг вы можете выразить ваши запросы одним.
6. Использование DISTINCT или UNION для удаления дубликатов из случайного декартова произведения
Из-за сложных соединений (JOIN) любой разработчик может потерять след в значащих связях SQL запроса. Если конкретнее, то при использовании связи с составными внешними ключами можно забыть добавить значащие предикаты в JOIN… ON утверждения. Это может привести к дублированию строк всегда или только в исключительных ситуациях. Тогда некоторые разработчики могут добавить оператор DISTINCT для прекращения дублирования данных. Это не правильно по трём причинам:
— Это может излечить последствия, но не причину. А ещё это может не решить последствия при граничных условиях.
— Это медленно для больших выборок. DISTINCT выполняет ORDER BY операцию для удаления дублирования.
— Это медленно для больших декартовых произведений которые всё равно будут загружены в память.
Лекарство:
Как правило, если Вы получаете нежелательные дубликаты, пересмотрите свои JOIN предикаты. Вероятно там где-то образовалось небольшое декартово произведение.
7. Избегание оператора MERGE
На самом деле это не ошибка, но, возможно, это отсутствие знаний или страхи мощного оператора MERGE. Некоторые БД знают другие формы UPSERT оператора, например MySQL ON DUPLICATE KEY UPDATE. На самом деле MERGE очень мощен, особенно в БД, которые сильно расширяют SQL стандарт, таких как SQL Server.
Лекарство:
Если Вы делаете UPSERT, выстраивая цепочку из INSERT и UPDATE или SELECT… FOR UPDATE и INSERT/UPDATE, задумайтесь ещё раз. Вместо риска гонки за ресурсами, вы можете написать более простое MERGE запрос.
8. Использование агрегатных функций вместо оконных функций
Перед появлением оконных функций, единственным средством для агрегации данных в SQL было использование GROUP BY вместе с агрегатными функциями в проекции. Это хорошо работает в большинстве случаев, и если агрегированные данные должны быть наполнены обычными данными, то сгруппированный запрос может быть написан в присоединённом под запросе.
Но SQL:2003 определяет оконные функции, которые реализованы многими поставщиками БД. Оконные функции могут агрегировать данные на не группированных выборках. По факту, каждая оконная функция поддерживает свою собственную, независимую PARTITION BY операцию, которая является отличным инструментом для построения отчётов.
Использование оконных функций позволит:
— Построить более читаемый SQL (меньше выделенных GROUP BY выражений в под запросах)
— Улучшить производительность т.к. RDBMS может легче оптимизировать оконные функции
Лекарство:
Когда вы пишите GROUP BY выражение в под запросе, задумайтесь, может ли он быть выражен оконной функцией?
9. Использование сортировки в памяти при разных параметрах
Оператор ORDER BY поддерживает множество типов выражений, включая CASE, который может быть очень полезен при определении параметра сортировки. Вам никогда не следует сортировать данные в памяти Java только потому, что:
— SQL сортировка слишком медленная.
— SQL сортировка не может сделать этого.
Лекарство:
Если вы сортируете какие-либо SQL данные в памяти Java, задумайтесь, возможно ли перенести эту сортировку в БД? Это отлично сочетается со страничной разбивкой в БД.
10. Поочерёдная вставка множества записей
JDBC знает, что такое пакет (batch), и Вам следует использовать это. Не делайте INSERT тысяч записей одной за другой, создавая новый PreparedStatement каждый раз. Если все ваши записи идут в одну таблицу, создайте партию INSERT запросов с одним SQL запросом и несколькими связываемыми наборами данных. В зависимости от вашей БД и её конфигурации, что бы сохранить UNDO лог чистым, Вам может потребоваться делать commit спустя какое-то количество вставленных записей.
Лекарство:
Всегда используйте пакетную вставку больших наборов данных.
©habrahabr
