MySQL – получение неправильного результата при попытке получить случайный результат из большой таблицы
У меня очень большая таблица (около 19 000 000 записей), и я хочу случайным образом выбрать 10 строк.
Я нашел это решение:
select *
from my_table as one
where one.id >=
(
select FLOOR(rand() * (select max(two.id) from my_table as two)) as currid
)
limit 10
Это должно сработать. Но если я выполняю только случайную часть, я очень часто получаю большие идентификаторы (например, 5, 6, 12, 15 миллионов), но полный запрос всегда возвращает низкие идентификаторы (2k, 5k, 8k,...).
Если я заменю условиеwhere статическим значением (например, 12 миллионов), я получу правильный результат.
Не могу понять, как это возможно.
С уважением
РЕДАКТИРОВАТЬ
Возможно, я неправильно объяснил. Выполняя запрос все больше и больше раз, я ВСЕГДА получал результаты со «случайным» идентификатором от 1 до 10 КБ. По статистике, на 19 миллионах записей я имею 0,05% вероятности получить идентификатор меньше 10 тысяч. Умножив x10, я имею 0,5% шансов получить один из десяти (мой LIMIT 10) идентификаторов ниже 10 000, что крайне нелепо по сравнению со 100% случаев, когда я получаю идентификаторы < 10 000.
Вместо этого, если я выполняю только подзапрос, я получаю идентификаторы, которые кажутся одинаково вероятно распределенными.
«Если я заменю условиеwhere статическим значением...» ?? (см.: dbfiddle.uk/ixNfflJF, что, похоже, не является желаемым результатом!)
LIMIT 10 без ORDER BY всегда будет глючить. Без него порядок проверки строк будет произвольным. Либо упорядочите по случайному числу и выберите первые 10, либо упорядочите детерминированно и отфильтруйте (в предложенииwhere) по случайному числу. (Вероятно, в настоящее время он оценивает строки в порядке id и выбирает первые 10, удовлетворяющие предложениюwhere.)
Ответы 2
У вас 19 миллионов данных.
Теперь вы ожидали результат в пределах 10 миллионов.
Итак, вероятность получить 10 миллионов из 19 миллионов, 10/19 = 0.52.
Попробуйте использовать этот запрос:
select '19 M' as total_record,
concat(FLOOR((rand()*19)), ' M') as result_with_in19M;
Проверьте, сколько раз вы получаете запись в течение 10M. Должно быть примерно 50%.
Это не проблема. Это ваше заблуждение.
Думайте логически.
Есть много логики для получения случайного значения.
Например, вы хотите получить более низкое значение.
Теперь мы знаем, что если мы умножим два десятичных значения (x, y), которые меньше 1, то результат умножения (x * y) будет близок к нулю, и оба числа больше результата умножения.
Итак, согласно этой логике, вы можете изменить свой запрос следующим образом:
select '19 M' as total_record,
concat(FLOOR((rand()*rand()*19)), ' M') as low_result_with_in19M;
Это даст вам приблизительно меньшее значение, чем предыдущий запрос.
Надеюсь, это исправит ваше заблуждение.
Спасибо!!!!!!
Здесь определенно есть языковой барьер, но, похоже, вы совершенно неправильно поняли, о чем спрашивает ОП.
Причина, по которой ваш запрос не работает, заключается в том, что подзапрос (select FLOOR(rand() * (select max(two.id) from my_table as two)) as currid) выполняется несколько раз.
Чтобы получить первые 10 случайных чисел, сделайте следующее:
SELECT
id,
FLOOR(rand() * (select max(two.id) from my_table as two)) as currid
FROM my_table
ORDER BY currid
LIMIT 10;
см.: DBFIDDLE
ПРИМЕЧАНИЕ. округление/уменьшение результатов RAND() даже не требуется.
Обновлено:
Это может быть быстрее, потому что случайный идентификатор определяется только 10 раз (при выполнении LIMIT 10). Однако обратите внимание, что тогда идентификаторы не должны иметь пробелов (каждый идентификатор от 1 до 10 должен существовать, иначе вы пропустите результаты в ens)
SELECT id
FROM my_table
INNER JOIN (
SELECT
FLOOR(rand() * (select max(two.id) from my_table as two)) as currid
FROM my_table WHERE id between 1 and 20
) x on x.currid = my_table.id
LIMIT 10
см.: DBFIDDLE
Ух ты! Это работает, как и ожидалось (пример изображения), не могли бы объяснить мне, почему? «currid» всегда возвращает низкие числа, как это возможно?
currid не нужен. Простой ORDER BY RAND() лучше
@yotheguitou ORDER BY RAND() очень медленно работает на больших столах (в моем случае это неосуществимо)
@ Luca4k4: добавлено кое-что, что должно ускорить работу даже при запросе 19 миллионов записей.
@Luuk Это хорошо, но это не работает, если какой-то идентификатор отсутствует
Я обновил редактирование с помощью: обновил EDIT с помощью ... 20 и LIMIT 10 (теперь 50% процентов идентификатора можно пропустить 😉)
Я предполагаю, что он повторно оценивает подзапрос для каждой строки в
my_table, поэтому у него есть много возможностей генерировать небольшое число. Вместо того, чтобы просто оценивать его один раз и использовать «статическое значение»