Как получить случайную строку из большой таблицы
Метод 1, плохой: ORDER BY NEWID()
Легко пиitncz, но он работает как горячий, горячий мусор, потому что он сканирует весь кластерный индекс, вычисляя NEWID () в каждой строке:
План со сканированием
На моей машине это заняло 6 секунд параллельно с несколькими потоками, используя десятки секунд процессора для всех этих вычислений и сортировки (а таблица Users не составляет даже 1GB.)
Метод 2, лучше, но странный: TABLESAMPLE
Это появилось в 2005 году и имеет массу ошибок. Это своего рода выбор случайной страницы, а затем возврат нескольких строк с этой страницы. Первая строка произвольна, но остальные - нет.
|
SELECT * FROM StackOverflow.dbo.Users TABLESAMPLE (.01 PERCENT);
|
План выглядит так, как будто он выполняет сканирование таблицы, но он выполняет только 7 логических чтений:
План с поддельным сканированием
Но вот результаты - вы можете видеть, что он перескакивает на случайную страницу, а затем начинает считывать строки по порядку. Это не случайные строки.
Случайные, как номера лотереи
Вместо этого вы можете использовать размер выборки ROWS, но он имеет некоторые довольно странные результаты. Например, в таблице «Stack Overflow Users», когда я указал TABLESAMPLE (50 ROWS), я на самом деле получил 75 строк назад. Это связано с тем, что вместо этого SQL Server преобразует размер строки в проценты.
Метод 3, лучший, но требует кода: Random Primary Key
Получите верхнее поле идентификатора в таблице, сгенерируйте случайное число и найдите этот идентификатор. Здесь мы сортируем по идентификатору, потому что хотим найти верхнюю запись, которая фактически существует (тогда как случайное число могло быть удалено.) Довольно быстро, но это хорошо только для одной случайной строки. Если вы хотите 10 строк, вам придется вызывать такой код 10 раз (или генерировать 10 случайных чисел и использовать условие IN).
/* Get a random number smaller than the table's top ID */
DECLARE @rand BIGINT;
DECLARE @maxid INT = (SELECT MAX(Id) FROM dbo.Users);
SELECT @rand = ABS((CHECKSUM(NEWID()))) % @maxid;
/* Get the first row around that ID */
SELECT TOP 1 *
FROM dbo.Users AS u
WHERE u.Id >= @rand;
План выполнения показывает кластерное сканирование индекса, но он только захватывает одну строку (мы заявляем только 6 логических операцуий для всего, что вы здесь видите) и заканчивается почти мгновенно:
Есть один недочет: если идентификатор имеет отрицательные числа, он не будет работать должным образом. (Например, скажите, что вы запустили свое поле идентификации в -1 и шаг -1)
Метод 4, OFFSET-FETCH (2012+)
|
1
2
3
4
5
6
7
8
|
DECLARE @row bigint=(
SELECT RAND(CHECKSUM(NEWID()))*SUM([rows]) FROM sys.partitions
WHERE index_id IN (0, 1) AND [object_id]=OBJECT_ID(‘dbo.thetable’));
SELECT *
FROM dbo.thetable
ORDER BY (SELECT NULL)
OFFSET @row ROWS FETCH NEXT 1 ROWS ONLY;
|
Этот метод работает только с кластеризованным индексом. Скорее всего это из-за того, что он проверяет строки в массе вместо того, чтобы делать поиск по индексу.