В своих вечных попытках обойти проблему N + 1 пользователи Hibernate часто прибегают к предикатам IN со списками идентификаторов . В этом посте мы увидим, как эти пользователи могут просто заменить ужасную вещь плохой, что лучше, но еще не хорошо. Вот почему:
Проблема N + 1
Проблема N + 1 — хорошо понятая проблема, задокументированная в различных постах блога . Ранее связанная статья показывает следующий набор запросов, чтобы объяснить природу этой проблемы:
|
1
2
3
4
5
6
|
SELECT id, name FROM albumsSELECT id, name FROM songs WHERE album_id = 1SELECT id, name FROM songs WHERE album_id = 2SELECT id, name FROM songs WHERE album_id = 3SELECT id, name FROM songs WHERE album_id = 4SELECT id, name FROM songs WHERE album_id = 5 |
Этот набор запросов часто генерируется ORM, такими как Hibernate, когда объекты настроены для отложенной выборки.
Статья также решает проблему, заменяя второй набор из N = 5 запросов одним запросом с предикатом IN:
|
1
2
|
SELECT id, title, filename FROM songsWHERE album_id IN (1, 2, 3, 4, 5) |
Это уменьшит количество запросов с N + 1 до 1 + 1, что, безусловно, быстрее.
Но давайте посмотрим на вещи со стороны SQL
Является ли такой предикат IN со списком идентификаторов хорошим решением? Это конечно жизнеспособно для очень маленьких списков. Но когда ваш список растет, подумайте над этим:
Размер списка
Не все базы данных поддерживают произвольные длины списков IN. В частности, существуют следующие ограничения:
- Предикат Oracle IN: 1000 элементов
- Ingres: 1024 значения общей привязки
- SQLite: 999 общих значений привязки
- Sybase ASE: 2000 значений общего связывания
- SQL Server 2008 R2: 2100 значений общего связывания
Это довольно раздражает, так как разработчики, вероятно, должны усвоить вышесказанное трудным путем. Если вы используете jOOQ , вы можете «безопасно» игнорировать вышеуказанные ограничения, поскольку jOOQ перезапишет ваш запрос так, что:
- Большие предикаты Oracle IN разделены на несколько предикатов
IN, связанных поOR, или предикатовNOT IN, связанных поAND - Большое количество значений привязки обнаруживается во время рендеринга SQL и заменяется встроенными значениями.
Переменная скорость привязки
В некоторых драйверах JDBC довольно много работы связано с привязкой переменных. По сути, некоторые протоколы базы данных должны будут передавать множество значений один за другим в базу данных. Этого не происходит, когда вы встраиваете значения связывания, поскольку единственное, что передается, это отдельная строка SQL. Слишком много значений привязки (я говорю о 10k или более), конечно, не очень хорошая идея.
Отсутствует кеш курсора
Сложные базы данных, такие как Oracle, поддерживают кеши курсоров , которые можно использовать для совместного использования курсоров . Это означает, что последующие выполнения идентичных операторов SQL получат выгоду от уже выполненных дорогостоящих вычислений плана выполнения наряду со сбором статистики курсора. Подумайте об этом таким образом:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
|
-- This is the first time Oracle encounters this-- query. The DB has to parse the query and -- calculate an execution plan, which can be quite -- expensive if you have lots of JOINsSELECT id, name FROM songs WHERE album_id IN (?, ?)-- This is the second time Oracle encounters this-- same query. The DB can now re-use the previous-- execution plan as it is likely to be optimal -- againSELECT id, name FROM songs WHERE album_id IN (?, ?)-- This is not the same query as the previous ones-- A new execution plan has to be calculatedSELECT id, name FROM songs WHERE album_id IN (?, ?, ?) |
Как вы можете быстро увидеть, приведенный выше пример показывает, что список идентификаторов в предикате IN является движущейся целью, которая, вероятно, полностью удалит полезность значений связывания, поскольку каждый запрос склонен создавать новые курсоры и новые планы выполнения в вашем база данных. С тем же успехом вы могли бы указать свои значения привязки, что могло бы даже помочь вам избежать проблем с просмотром значений привязки .
Так что же лучше, чем списки удостоверений личности?
Есть ряд вещей, которые лучше. Обратите внимание, что не все из них могут подходить для вашей конкретной проблемы, и не все из них всегда будут превосходить списки идентификаторов. Используйте здравый смысл и, возможно, нагрузочный тест и / или тест производительности, который является лучшим запросом в вашей ситуации.
Явная «активная» выборка с использованием JOIN
Иногда было бы проще денормализовать данные в базе данных. Вместо того, чтобы загружать песни одну за другой, просто загрузите их вместе с альбомами:
|
1
2
3
4
5
6
7
|
SELECT a.id a_id, a.name a_name, s.id s_id, s.name s_nameFROM albums aJOIN songs s ON s.album_id = a.id |
Это позволит передавать больше данных по проводам (повторяющуюся информацию об альбоме) в обмен на выполнение только одного запроса (сокращение N + 1 до 1). Это хорошо только для небольшой денормализации. Если вы ПРИСОЕДИНЯЕТЕСЬ к десяткам отношений 1: N, возможно, вам не понравится это решение.
Полусоединение исходного запроса
Если вы можете получить доступ к SQL-коду исходного запроса, просто присоединяйтесь к нему при получении песен! Это просто:
|
01
02
03
04
05
06
07
08
09
10
11
|
SELECT id, name FROM songs WHERE album_id IN ( SELECT id FROM albums)-- Or using EXISTSSELECT s.id, s.name FROM songs sWHERE EXISTS ( SELECT 1 FROM albums a WHERE a.id = s.album_id) |
Это потребует некоторого преобразования SQL. Опять же, используя конструктор безопасных типов / SQL-запросов для составления запросов, таких как jOOQ , JaQu или Criteria API , вы можете легче реализовать такое преобразование SQL / составление SQL.
Обратите внимание, что это, вероятно, самое быстрое решение, которое вы можете выбрать, по крайней мере, в сложных базах данных с мощными оптимизаторами запросов.
Использование массивов для списков идентификаторов
Если вы действительно не можете запросить свои песни без списка идентификаторов, по крайней мере, используйте один массив как переменную связывания как таковой (Oracle диалект):
|
1
2
3
4
|
SELECT id, name FROM songs WHERE album_id IN ( SELECT * FROM TABLE(?)) |
Приведенный выше синтаксис специфичен для Oracle . Проверьте этот вопрос переполнения стека для других альтернатив. Обратите внимание, что типы Oracle VARRAY и TABLE строго типизированы, т.е. сначала вам нужно будет иметь такой тип:
|
1
|
CREATE TYPE numbers AS TABLE OF NUMBER(38); |
В качестве альтернативы вы можете использовать один из этих «встроенных» типов таблиц :
-
ORA_MINING_NUMBER_NT -
ORA_MINING_VARCHAR2_NT
Создание IN-списков дискретного размера
Если ваша база данных не поддерживает массивы, и вам нужно полагаться на списки идентификаторов, есть еще один последний вариант, который вам может потребоваться, чтобы избежать слишком большого количества пропусков кэша курсора и жесткого анализа. Создайте дискретные списки IN, заполняя значения привязки до следующей дискретной длины. Давайте предположим, что длины 2, 3, 5, 8, 13. Это лучше всего объяснить на примере:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
|
-- Of course, this only makes sense with bind values-- Inlining is done for the purpose of the example-- only-- Two IDs fill up to 2album_id IN (1, 2)-- Three IDs fill up to 3album_id IN (1, 2, 3)-- Four IDs fill up to 5album_id IN (1, 2, 3, 4, 4)-- Five IDs fill up to 5album_id IN (1, 2, 3, 4, 5)-- Six IDs fill up to 8album_id IN (1, 2, 3, 4, 5, 6, 6, 6) |
Не существует практического правила относительно того, какие шаги должны увеличиваться в размерах вашего IN-списка, так что вы можете действительно измерить это.
Запись! : Вы можете использовать NULL для заполнения списков IN предиката IN , но не предиката NOT IN . Чтобы узнать больше об этом, прочтите этот пост в блоге о предикатах NULL и NOT IN .
TL; DR: вернуть контроль над SQL
Как только при обработке данных задействовано достаточное количество данных, общих моделей ORM может оказаться недостаточно, поскольку очень сложно настроить такие ORM. Возможно, вам придется прибегнуть к SQL и явно выразить ваши операторы SQL наиболее оптимальным способом для вашей проблемной области.