MySQL. Выбор случайных строк в один запрос

[xel]

Проблему с дырами во втором способе можно решить заменив знак равенства на «меньше либо равно» (и не забыть добавить LIMIT).
Статистическое распределение конечно ухудшится, но выбирать можно будет очень быстро.
Особенно если вместо count подзапроса использовать передаваемую в запрос константу, примерно равную числу записей в таблице.

[DeathCore]

<= выдаст самую первую запись. Может «больше или равно»?

Или я что-то не так понял…

[xel]

Ну это смотря какую сортировку использовать.

Допустим у нас на сайте чуть больше 100 000 комментариев, и мы хотим выбрать случайный. Мы не стремимся выводить самые свежие, там может быть спам, да и пересечение с виджетом «Последние комментарии» нежелательно. Мы можем смело передавать константу 100 000 в запрос. А ещё лучше, сразу случайное число от 1 до 100000.

тогда запрос будет
SELECT * FROM comments WHERE id <= (rand()*100000) ORDER BY id DESC LIMIT 1;
или соответственно SELECT * FROM comments WHERE id <= 63244 ORDER BY id DESC LIMIT 1;

[DeathCore]

хм… на моей таблице ваш запрос срабатывает за 3 секунды на 1 строку…

C rand() естественно.

[xel]

Даже не знаю, у меня на таблице в миллион за 1/1000 проходит, SQL_NO_CACHE добавил.
RAND() и умножение не может выполнятся 3 секунды.
Один вариант есть на уме — возможно из-за того что результат вычисления случайного числа дробный mysql пытается все id привести к этому же типу. Попробуйте взять вычисление во FLOOR().

[DeathCore]

у меня просто ноутбук старенький)

Я думаю у вас и ORDER BY rand() LIMIT * будет выполняться не 6 секунд)

За округления совершенно забыл…

[AlexeyVD]

Не забывайте, что функция RAND() — детерминированная, и соответственно в WHERE у вас каждый раз для каждой строки будет вычисляться новое число rand()*100000, так что про индексы тут можете смело забыть.

[zerkms]

Недетерминированная

[AlexeyVD]

Ага, спасибо, что поправили. Почему-то все время путаю эти термины.

[zerkms]

Если PK «не имеет дыр и начинается с 1» — тогда дешевле всего сгенерировать случайные значения отдельно и передать их в запрос.

Это я к тому, что один запрос — не всегда лучше, чем два (вообще непонятно, откуда такая точка зрения возникает).

[akalend]

>Если PK «не имеет дыр и начинается с 1» — тогда дешевле всего сгенерировать случайные значения отдельно и передать их в запрос.
это первое что мне пришло в голову, когда я прочитал название статьи :)

[Daedmen]

Еще можно юзать колонку в рандомным числом

[UncleAndy]

Если заранее известно количество элементов, тогда выборка по случайному offset будет намного эффективнее. Я проверял это на postgresql и mysql — разница, насколько я помню, в 5-10 раз в пользу выборки по смещению.

Хочется отметить, что при использовании rand() в SQL выражении идут лесом любые индексы и оптимизации по ним. Так что категорически не советую его использовать в выражениях WHERE или ORDER.

[zerkms]

Выборка по смещению не быстрее выборки по PK. Попробуйте сдвиньтесь на 100000000 рядов, а потом выберите тот же ряд по PK.

[UncleAndy]

Выборка по смещению и есть выборка по PK. И такая выборка быстрее чем выборка с использованием random в запросе.

[zerkms]

> Выборка по смещению и есть выборка по PK

С чего вы это взяли? Смещение — это OFFSET в postgresql или N в LIMIT N, 1 в mysql. А выборка по PK это выборка по PK.

[UncleAndy]

Если вы по третий метод, то это надо тестировать. Я не уверен что именно быстрее. Возможно вы и правы. Но, думаю, разница по времени будет не существенная.

[zerkms]

Я вообще вне контекста статьи — LIMIT операция медленная. Чем дальше от начала — тем медленнее.

А выборка по PK практически константная

[UncleAndy]

Возможно, что это справедливо для MySQL. В postgresql я это тестировал и обнаружил что для LIMIT практически нет зависимости от величины смещения. По крайней мере если сортировка идет по PK.

[akalend]

думаю — это зависит от конкретной реализации в БД, но zerkms по своему прав и я его мнение полностью поддерживаю

[akalend]

полностью согласен — проверено на практике.

[Regis]

Если дыр нет, известно количество строк, а из базы вам нужно извлечь только pk_id, то… вам вообще не нужно делать запросы в базу. :) Либо нужно один раз запросить количество строк.

Просто генерируете в приложении позиции строк путем умножения COUNT на rand(), а так как у вас ключи соответствуют ряду натуральных чисел, то pk_id собственно и равен позиции строки.

[DeathCore]

Количество строк никак не известно)

[akalend]

его всегда можно вытянуть из БД

[Dzuba]

Насколько мне известно, нет никакой необходимости залезать в information_schema, поскольку это эквивалентно простому запросу

SELECT COUNT(*) FROM test

Этот запрос выполняется условно-мгновенно, не обращаясь к собственно данным таблицы.

Заменив COUNT(pk_id) на (SELECT COUNT(*) FROM test) в вашем запросе из второго варианта:

SELECT t.pk_id FROM test as t,
(SELECT ROUND((SELECT COUNT(*) FROM test) * rand()) as rnd FROM test LIMIT 1) t
WHERE t.pk_id = rnd

я получил более чем десятикратное ускорение выполнения.

К слову, у меня такой запрос выполняется примерно на 50% быстрее вашего третьего варианта с information_schema: 0.0008 сек против 0.0012 сек на таблице в 3 млн записей. Полагаю, на таблицах существенно большего размера эта разница сойдет на нет. Проверить, к сожалению, сейчас не могу.

[zerkms]

> Этот запрос выполняется условно-мгновенно, не обращаясь к собственно данным таблицы.

Только если у вас myisam

[Dzuba]

Да, именно. Вы правы. Это только в MyISAM и, возможно, еще в Aria (в MariaDB).

[DeathCore]

Второй вариант с COUNT(*) прошел за 0.95 против 1.04… быстрее!

Просто проблема в том, что с COUNT даже 10 случайных строк получить будет довольно таки сложно.

[ztxn]

Надо просто добиться того, чтобы count считался один лишь раз
сорри не спец в mySql, думаю это будет выглядить как-то так

select * from table
where id in (
   select trunc(rand()*cnt.cnt+1)
   from (select count(*) as cnt from table) as cnt
   cross join (select null from table limit :demanded_ranom_records_count) as pivot
)

[ztxn]

впрочем, этот способ мало отличается от приведенного вами max+rand. Если вы в ваш max+rand поставите вместо max, count, по идее, получите похожий результат

[DeathCore]

Если взять COUNT(*) вместо MAX(pk_id), то делает в 1000 раз медленнее…

не знаю даже, почему)

Вообще с COUNT не удалось получать результат менее чем за секунду.

P.s таблица InnoDB

[ztxn]

>>не знаю даже, почему)
хм… тоже не знаю… возможно при max(pk_id) просто берется с хвоста индекса. Т.е. де факто отрабатывает запрос select id from table order by id desc limit 1. Было бы интересно, если бы это было действительно так.

[petropavel]

Это действительно так.
А SELECT COUNT(*) читает всю таблицу, пробегает по всему индексу, то есть. В InnoDB.

[ztxn]

Спасибо, впредь буду знать эту особенность MySQL. Меня всегда удивляло почему так не поступают прочие платформы, с которыми мне доводилось иметь дело.

[AlexeyVD]

На information_schema.tables я бы на вашем месте опираться не стал, т.к., например, для InnoDB столбец TABLE_ROWS содержит лишь грубую оценку количества строк в таблице, а не точное значение.

[smagen]

А почему бы не сделать вместо

(SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test')

вот так

(SELECT tt.pk_id FROM test AS tt ORDER BY tt.pk_id DESC LIMIT 1)

?

[petropavel]

Если InnoDB — то да. А если MyISAM — то SELECT COUNT(*).

[DeathCore]

Хм, для одной строки прирост почти в 10 раз с вашим вариантом подзапроса.

Но в таком случае можно было бы использовать MAX а не ORDER BY + LIMIT.

Хотя особой разницы не вижу, что-то, что то делается за 0.000 — 0.001 секунды…

[petropavel]

разница есть. MAX или ORDER BY + LIMIT — практически все равно.

А запрос к I_S… там две проблемы. Во-первых, нет TABLE_SCHEMA="foobar"; в WHERE. Это значит, что при запросе сервер построит список всех каталогов в datadir-е, и в каждом будет пытаться найти файл test.frm. У хостеров этих каталогов могут быть тысячи. Во-вторых, table_rows из I_S.TABLES — это быстрая, но приблизительная оценка количества строк в таблице. Точная она в MyISAM (где COUNT(*) и так быстрый), но не в InnoDB (где точный результат — COUNT(*) — весьма медленный).

[netp]

Большое спасибо! Очень полезно. Как раз сейчас этим занимаюсь

[Jemeron]

А что если нужны дополнительные ограничения на поля? Где ограничивать?

[DeathCore]

С ограничениями тут сложность. Можно сделать так

WHERE t.pk_id >= rnd AND column = value
LIMIT 1

Но тут есть свои проблемы…