Производительность SQL: ГДЕ против ГДЕ (ROW_NUMBER)

Я хочу получить n-ые m-ые записи в таблице, что является лучшим выбором в 2 решениях ниже:

Решение 1:

    SELECT * FROM Table WHERE ID >= n AND ID <= m

Решение 2:

    SELECT * FROM 
                (SELECT *, 
                        ROW_NUMBER() OVER (ORDER BY ID) AS row 
                 FROM Table 
                )a 
    WHERE row >= n AND row <= m
Источник

4 ответа

Решение

Второй ответ - ваш лучший выбор. Принимается во внимание тот факт, что у вас могут быть дыры в столбце идентификатора. Я бы переписал его как CTE, хотя вместо подзапроса...

;WITH MyCTE AS
(SELECT  *,  
         ROW_NUMBER() OVER (ORDER BY ID) AS row  
FROM     Table)
SELECT   *
FROM     MyCTE
WHERE    row >= @start 
         AND row <= @end
Источник

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

Но основной вопрос остается: что быстрее: пейджинг на основе набора ключей или пейджинг на основе номера?

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

Пейджинг на основе набора ключей основан на запоминании верхней и нижней клавиш последней отображаемой страницы и запросе следующего или предыдущего набора строк на основе верхнего / последнего набора ключей:

Следущая страница:

select top (<pagesize>) ...
from <table>
where key > @last_key_on_current_page
order by key;

Предыдущая страница:

select top (<pagesize>)
from <table>
where key < @first_key_on_current_page
order by key desc;

Этот подход имеет два основных преимущества перед подходом ROW_NUMBER или эквивалентным подходом LIMIT в MySQL:

  • это правильно: в отличие от подхода, основанного на номере строки, он корректно обрабатывает новые записи и удаленные записи. Последняя строка на странице 4 не отображается как первая строка на странице 5 только потому, что строка 23 на странице 2 тем временем была удалена. Строки не загадочно исчезают между страницами. Эти аномалии характерны для подхода, основанного на row_number, но решение, основанное на наборе ключей, намного лучше помогает избежать их.
  • быстро: все операции могут быть решены с помощью быстрого позиционирования строки с последующим сканированием диапазона в нужном направлении

Однако этот подход трудно реализовать, трудно понять среднему программисту и не поддерживается инструментами.

Номер строки управляется

Это общий подход, представленный в запросах Linq:

select ...
from (
  select ..., row_number() over (...) as rn
  from table)
where rn between @firstRow and @lastRow;

(или аналогичный запрос с использованием TOP) Этот подход прост в реализации и поддерживается инструментами (в частности, операторами Linq .Limit и.Take). Но этот подход гарантирует сканирование индекса для подсчета строк. Этот подход обычно работает очень быстро для страницы 1 и постепенно замедляется при переходе к номерам страниц все выше и выше.

В качестве бонуса, с этим решением очень легко изменить порядок сортировки (просто измените предложение OVER).

В целом, учитывая простоту решений на основе ROW_NUMBER(), поддержку, которую они имеют от Linq, простоту использования произвольных порядков для умеренных наборов данных, решения на основе ROW_NUMBER являются адекватными. Для больших и очень больших наборов данных ROW_NUMBER() может вызвать серьезные проблемы с производительностью.

Еще одна вещь, которую следует учитывать, - это то, что часто существует определенный шаблон доступа. Часто первые несколько страниц горячие, и страницы после 10 в основном никогда не просматриваются (например, самые последние сообщения). В этом случае штраф за использование ROW_NUMBER() за посещение нижних страниц (страниц отображения, для которых необходимо рассчитать большое количество строк, чтобы получить начальную строку результатов) может быть полностью проигнорирован.

И, наконец, разбиение на страницы набора ключей отлично подходит для навигации по словарю, которую ROW_NUMBER() не может легко разместить. Навигация по словарю - это место, где вместо использования номера страницы пользователи могут переходить к определенным якорям, таким как буквы алфавита. Типичным примером является контактная боковая панель, подобная Rolodex, вы нажимаете M и переходите к первому имени клиента, которое начинается с M.

Источник

Это разные запросы.

Предполагая, что ID является суррогатным ключом, он может иметь пробелы. ROW_NUMBER будет смежным.

Если вы можете гарантировать, что у вас нет пробелов в данных, то 1-й, потому что я надеюсь, что он проиндексирован. 2-й является более "правильным", хотя.

Источник 
SELECT * FROM Table WHERE ID BETWEEN N AND N

может быть? (непроверенный и я ржавый)

Источник
Другие вопросы по тегам