Я уверен, что вы до сих пор ошиблись во многих отношениях. И вы, вероятно, не поймете это правильно в ближайшее время. Так зачем тратить свое драгоценное время на подстройку 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 варианта использования, с которыми вы уже сталкивались, верно?
Ты можешь это сделать. Таким образом, вы будете автоматически выполнять поиск по столбцам в именах столбцов, чтобы получить вышеуказанное.
Так теперь это правильно?
Конечно нет! Потому что вы можете иметь неоднозначные имена столбцов на верхнем уровне SELECT
s, но не во вложенных выборках. Что делать, если вы хотите сделать это:
-- 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, здесь .