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

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

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

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

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

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

SQL действительно очень многословный язык. Лично нам действительно нравится краткость предложения MySQL / PostgreSQL LIMIT .. OFFSET, поэтому мы выбрали его для 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()фильтрацией в 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 варианта использования, с которыми вы уже сталкивались, верно?

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

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

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

-- 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нумерация страниц зачастую является плохим выбором. Вы должны знать, что нумерация клавиш почти всегда превосходит OFFSETнумерацию страниц.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Категории

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

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

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

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

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

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