Прекратите пытаться эмулировать разбиение по страницам SQL OFFSET с помощью собственной базы данных БД!

Я уверен, что вы до сих пор ошиблись во многих отношениях. И вы, вероятно, не поймете это правильно в ближайшее время. Так зачем тратить свое драгоценное время на подстройку SQL, когда вы могли бы реализовать бизнес-логику?

Позволь мне объяснить…

До недавнего стандарта SQL: 2008 то, что пользователи MySQL знают как LIMIT .. OFFSET было стандартизировано в следующем простом утверждении:

SELECT * 
FROM BOOK 
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY

Да. Так много ключевых слов.

SQL действительно очень многословный язык. Лично нам действительно нравится краткость предложения LIMIT .. OFFSET в MySQL / PostgreSQL, поэтому мы выбрали его для jOOQ DSL API .

В SQL:

SELECT * FROM BOOK LIMIT 1 OFFSET 2

В jOOQ:

select().from(BOOK).limit(1).offset(2);

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

-- MySQL, H2, HSQLDB, Postgres, and SQLite
SELECT * FROM BOOK LIMIT 1 OFFSET 2
 
-- CUBRID supports a MySQL variant of the 
-- LIMIT .. OFFSET clause
SELECT * FROM BOOK LIMIT 2, 1
 
-- Derby, SQL Server 2012, Oracle 12, SQL:2008
SELECT * FROM BOOK 
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY
 
-- Ingres. Eek, almost the standard. Almost!
SELECT * FROM BOOK 
OFFSET 2 FETCH FIRST 1 ROWS ONLY
 
-- Firebird
SELECT * FROM BOOK ROWS 2 TO 3
 
-- Sybase SQL Anywhere
SELECT TOP 1 ROWS START AT 3 * FROM BOOK
 
-- DB2 (without OFFSET)
SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY
 
-- Sybase ASE, SQL Server 2008 (without OFFSET)
SELECT TOP 1 * FROM BOOK

Все идет нормально. Все это может быть обработано. Некоторые базы данных помещают смещения перед пределами, другие ставят ограничения перед смещениями, а семейство T-SQL помещает все предложение TOP перед списком SELECT . Это легко подражать. Теперь насчёт:

Oracle 11g и меньше
SQL Server 2008 и меньше
DB2 с OFFSET

( обратите внимание, что вы можете включить различные альтернативные синтаксисы в DB2 )

Когда вы заглянете в Google, вы найдете миллионы способов эмулировать OFFSET .. FETCH в этих старых базах данных. Оптимальные решения всегда включают в себя:

Использование дважды вложенных производных таблиц с фильтрацией ROWNUM в Oracle
Использование производных вкладок с ROW_NUMBER() вложением с фильтрацией ROW_NUMBER() в SQL Server и DB2

Итак, вы имитируете это.

Как вы думаете, вы поймете это правильно?

Давайте рассмотрим пару вопросов, о которых вы, возможно, не задумывались.

Прежде всего, Oracle. Очевидно, что Oracle стремился создать максимальную блокировку от поставщиков, которая превзошла только недавнее появление Apple в Swift . Вот почему решения ROWNUM работают лучше, даже лучше, чем решения на основе стандартных оконных функций SQL: 2003 . Не верь этому? Прочитайте эту очень интересную статью о производительности смещения страниц Oracle .

Итак, оптимальное решение в Oracle это:

-- PostgreSQL syntax:
SELECT ID, TITLE 
FROM BOOK 
LIMIT 1 OFFSET 2
 
-- Oracle equivalent:
SELECT *
FROM (
  SELECT b.*, ROWNUM rn
  FROM (
    SELECT ID, TITLE
    FROM BOOK
  ) b
  WHERE ROWNUM <= 3 -- (1 + 2)
)
WHERE rn > 2

Так это действительно эквивалент?

Конечно нет. Вы выбираете дополнительный столбец, столбец rn . В большинстве случаев вам может быть все равно, но что, если вы хотите создать ограниченный подзапрос для использования с предикатом IN ?

-- PostgreSQL syntax:
SELECT *
FROM BOOK
WHERE AUTHOR_ID IN (
  SELECT ID
  FROM AUTHOR
  LIMIT 1 OFFSET 2
)
 
-- Oracle equivalent:
SELECT *
FROM BOOK
WHERE AUTHOR_ID IN (
  SELECT * -- Ouch. These are two columns!
  FROM (
    SELECT b.*, ROWNUM rn
    FROM (
      SELECT ID
      FROM AUTHOR
    ) b
    WHERE ROWNUM <= 3
  )
  WHERE rn > 2
)

Итак, как вы можете видеть, вам придется выполнить более сложное преобразование SQL. Если вы эмулируете LIMIT .. OFFSET вручную, тогда вы можете просто вставить столбец ID в подзапрос:

SELECT *
FROM BOOK
WHERE AUTHOR_ID IN (
  SELECT ID -- better
  FROM (
    SELECT b.ID, ROWNUM rn -- better
    FROM (
      SELECT ID
      FROM AUTHOR
    ) b
    WHERE ROWNUM <= 3
  )
  WHERE rn > 2
)

Итак, это больше похоже на это, верно? Но так как вы не пишете это каждый раз вручную, вы собираетесь начать создавать собственную изящную внутреннюю среду SQL, охватывающую 2-3 варианта использования, с которыми вы уже сталкивались, верно?

Ты можешь это сделать. Таким образом, вы будете автоматически выполнять поиск по столбцам в именах столбцов, чтобы получить вышеуказанное.

Так теперь это правильно?

Конечно нет! Потому что вы можете иметь неоднозначные имена столбцов в SELECT верхнего уровня, но не во вложенных выборках. Что делать, если вы хотите сделать это:

-- PostgreSQL syntax:
-- Perfectly valid repetition of two ID columns
SELECT BOOK.ID, AUTHOR.ID
FROM BOOK
JOIN AUTHOR
ON BOOK.AUTHOR_ID = AUTHOR.ID
LIMIT 1 OFFSET 2
 
-- Oracle equivalent:
SELECT *
FROM (
  SELECT b.*, ROWNUM rn
  FROM (
    -- Ouch! ORA-00918: column ambiguously defined
    SELECT BOOK.ID, AUTHOR.ID
    FROM BOOK
    JOIN AUTHOR
    ON BOOK.AUTHOR_ID = AUTHOR.ID
  ) b
  WHERE ROWNUM <= 3
)
WHERE rn > 2

Нет. А хитрость ручного исправления столбцов идентификаторов из предыдущего примера не работает, потому что у вас есть несколько экземпляров ID . А переименование столбцов в случайные значения — это неприятно, потому что пользователь вашей собственной внутренней базы данных хочет получить четко определенные имена столбцов. Т.е. ID и… ID .

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

-- Oracle equivalent:
-- Rename synthetic column names back to original
SELECT c1 ID, c2 ID
FROM (
  SELECT b.c1, b.c2, ROWNUM rn
  FROM (
    -- synthetic column names here
    SELECT BOOK.ID c1, AUTHOR.ID c2
    FROM BOOK
    JOIN AUTHOR
    ON BOOK.AUTHOR_ID = AUTHOR.ID
  ) b
  WHERE ROWNUM <= 3
)
WHERE rn > 2

Но теперь мы закончили?

Конечно нет! Что если вы вдвойне вложите такой запрос? Будете ли вы думать о переименовании столбцов ID в синтетические имена и обратно? … Давайте оставим это здесь и поговорим о чем-то совершенно ином:

Работает ли то же самое для SQL Server 2008?

Конечно нет! В SQL Server 2008 наиболее популярным подходом является использование оконных функций. А именно, ROW_NUMBER() . Итак, давайте рассмотрим:

-- PostgreSQL syntax:
SELECT ID, TITLE 
FROM BOOK 
LIMIT 1 OFFSET 2
 
-- SQL Server equivalent:
SELECT b.*
FROM (
  SELECT ID, TITLE, 
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Так вот и все, верно?

Конечно нет!

ОК, у нас уже была эта проблема. Мы не должны выбирать * , потому что это сгенерирует слишком много столбцов в случае, если мы используем это как подзапрос для предиката IN . Итак, давайте рассмотрим правильное решение с синтетическими именами столбцов:

-- SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT ID c1, TITLE c2,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Но теперь мы получили это, верно?

Сделайте обоснованное предположение: Нет!

Что произойдет, если вы добавите предложение ORDER BY к исходному запросу?

-- PostgreSQL syntax:
SELECT ID, TITLE 
FROM BOOK 
ORDER BY SOME_COLUMN
LIMIT 1 OFFSET 2
 
-- Naive SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT ID c1, TITLE c2,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
  ORDER BY SOME_COLUMN
) b
WHERE rn > 2 AND rn <= 3

Теперь это не работает в SQL Server. Подзапросам не разрешается иметь предложение ORDER BY , если только они не содержат предложение TOP (или предложение OFFSET .. FETCH в SQL Server 2012).

Хорошо, мы, вероятно, можем настроить это, используя TOP 100 PERCENT чтобы сделать SQL Server счастливым.

-- Better SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT TOP 100 PERCENT
    ID c1, TITLE c2,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
  ORDER BY SOME_COLUMN
) b
WHERE rn > 2 AND rn <= 3

Теперь это правильный SQL согласно SQL Server, хотя у вас нет гарантии, что порядок выполнения производной таблицы сохранится после выполнения запроса. Вполне может быть, что порядок снова меняется под влиянием некоторых факторов.

Если вы хотите упорядочить по SOME_COLUMN во внешнем запросе, вам придется снова преобразовать инструкцию SQL, чтобы добавить еще один синтетический столбец:

-- Better SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT TOP 100 PERCENT
    ID c1, TITLE c2,
    SOME_COLUMN c99,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3
ORDER BY b.c99

Это начинает становиться немного противным. И давайте догадаемся,

Это правильное решение!

Конечно нет! Что если в исходном запросе есть DISTINCT ?

-- PostgreSQL syntax:
SELECT DISTINCT AUTHOR_ID
FROM BOOK 
LIMIT 1 OFFSET 2
 
-- Naive SQL Server equivalent:
SELECT b.c1 AUTHOR_ID
FROM (
  SELECT DISTINCT AUTHOR_ID c1,
    ROW_NUMBER() OVER (ORDER BY AUTHOR_ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Что произойдет, если автор написал несколько книг? Да, ключевое слово DISTINCT должно удалять такие дубликаты, и эффективно, запрос PostgreSQL сначала корректно удалит дубликаты, а затем применяет LIMIT и OFFSET .

Однако предикат ROW_NUMBER() всегда генерирует различные номера строк, прежде чем DISTINCT сможет удалить их снова. Другими словами, DISTINCT имеет никакого эффекта.

К счастью, мы можем снова настроить этот SQL, используя этот аккуратный трюк :

-- Better SQL Server equivalent:
SELECT b.c1 AUTHOR_ID
FROM (
  SELECT DISTINCT AUTHOR_ID c1,
    DENSE_RANK() OVER (ORDER BY AUTHOR_ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Подробнее об этом трюке читайте здесь:

SQL-трюк: row_number () — это ВЫБРАТЬ, а плотная_ранка () — это ВЫБРАТЬ ОТЛИЧИЕ .

Обратите внимание, что предложение ORDER BY должно содержать все столбцы из списка полей SELECT . Очевидно, это ограничит допустимые столбцы в списке полей SELECT DISTINCT столбцами, которые разрешены в предложении ORDER BY оконной функции (например, никакие другие оконные функции).

Конечно, мы можем попытаться исправить это, используя обычные табличные выражения, или мы рассмотрим

Еще одна проблема ??

Ну конечно; естественно!

Вы даже знаете, какими должны быть столбцы в предложении ORDER BY оконной функции? Вы только что выбрали случайную колонку, случайно? Что если этот столбец не имеет индекса, будет ли по-прежнему работать ваша оконная функция?

Ответ прост, когда ваш исходный SELECT также содержит предложение ORDER BY , тогда вам, вероятно, следует принять его (плюс все столбцы из предложения SELECT DISTINCT , если применимо).

Но что, если у вас нет предложения ORDER BY ?

Еще один трюк! Используйте «постоянную» переменную:

-- Better SQL Server equivalent:
SELECT b.c1 AUTHOR_ID
FROM (
  SELECT AUTHOR_ID c1,
    ROW_NUMBER() OVER (ORDER BY @@version) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Да, вам нужно использовать переменную, потому что константы не разрешены в этих предложениях ORDER BY в SQL Server. Больно, я знаю.

Узнайте больше об этом трюке @@ version здесь .

Мы уже закончили!?!?

Возможно нет! Но мы, вероятно, охватили около 99% общих и крайних случаев. Теперь мы можем спать спокойно.

Обратите внимание, что все эти преобразования SQL реализованы в jOOQ . jOOQ — это единственная среда абстракции SQL, которая серьезно относится к SQL (со всеми его недостатками и предостережениями), стандартизируя все это безумие.

Как уже упоминалось в начале, с jOOQ вы просто пишете:

// Don't worry about general emulation
select().from(BOOK).limit(1).offset(2);
 
// Don't worry about duplicate column names
// in subselects
select(BOOK.ID, AUTHOR.ID)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.limit(1).offset(2);
 
// Don't worry about invalid IN predicates
select()
.from(BOOK)
.where(BOOK.AUTHOR_ID).in(
    select(AUTHOR.ID)
    .from(AUTHOR)
    .limit(1).offset(2)
);
 
// Don't worry about the ROW_NUMBER() vs.
// DENSE_RANK() distinction
selectDistinct(AUTHOR_ID)
    .from(BOOK).limit(1).offset(2);

С помощью jOOQ вы можете просто написать свой Oracle SQL или Transact SQL, как если бы он был таким же классным, как PostgreSQL! … Без прыжков с корабля SQL и перехода к JPA.

Пейджинг клавиатуры

Теперь, конечно, если вы читали наш блог или наш партнерский блог, посвященный объяснению производительности SQL , к настоящему времени вы должны знать, что разбиение на страницы OFFSET часто является плохим выбором. Вы должны знать, что нумерация клавиш набора почти всегда превосходит нумерацию смещения.

Прочитайте о том, как jOOQ изначально поддерживает разбиение на страницы набора ключей, используя предложение SEEK, здесь .

Ссылка:

Прекратите пытаться эмулировать разбиение по страницам SQL OFFSET с помощью собственной базы данных БД! от нашего партнера JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ .

Прекратите пытаться эмулировать разбиение по страницам SQL OFFSET с помощью собственной базы данных БД!

Позволь мне объяснить…

В SQL:

В jOOQ:

Как вы думаете, вы поймете это правильно?

Так это действительно эквивалент?

Так теперь это правильно?

Но теперь мы закончили?

Работает ли то же самое для SQL Server 2008?

Так вот и все, верно?

Но теперь мы получили это, верно?

Это правильное решение!

Еще одна проблема ??

Мы уже закончили!?!?

Пейджинг клавиатуры

Категории

Последние статьи

Рефакторинг Hudson God Class

Альтернативы синтаксиса Java лямбда

Morphia и MongoDB: развивающиеся структуры документов

OpenShift Express: развертывание приложения Java EE (с поддержкой AS7)

Интеграция jqGrid, REST, AJAX и Spring MVC