Случайный ряд из таблицы MySql, сейчас пол секунды. А максимально быстро как?

Question

[chelkaz]

В таблице пол миллиона записей. Нужно случайно выбирать одну для заполнения другой, тестовой таблицы.
Сейчас уходит пол секунды на получение случайной строки... Это очень долго,.
Пробую так:

$loc = DB::table("localities")
                ->select("id")
                ->orderBy(DB::raw('RAND()'))
                ->take(1)
                ->get();

Это равносильно:

0 => array:3 [▼
    "query" => "select `id` from `localities` order by RAND() asc limit 1"
    "bindings" => []
    "time" => 95.69
  ]

Пол секунды:
И так пол секунды:
$loc = Locality::inRandomOrder()->first();
Это равносильно:

0 => array:3 [▼
    "query" => "select * from `localities` order by RAND() limit 1"
    "bindings" => []
    "time" => 432.94
  ]

И это все долго, индексы на id таблицы стоят по умолчанию:

Неуникальный: 0
Имя ключа: PRIMARY
Порядок поля: 1
Имя колонки: id
Сравнение: A
Размер: 186060
Обработка: NULL
Сжатие: NULL

Answer 1

[Виталий Хоменко]

1. Определите кол-во записей в таблице. Не последний ID, а именно кол-во
2. Определите рандомное значение между 0 и кол-вом-1 записей, это будет смещение
3. Получите нужную запись с таблицы:

select `id` from `localities` offset {$offset} limit 1

Не сравнивал скорость, но должно быть в разы быстрее.
Рандомное смещение можно получить как на стороне PHP, так и на стороне MySQL.
А о RAND() вам уже написали.

Детально:
У вас N записей в таблице, вам нужно получить через mt_rand(), к примеру, случайное число от 0 до N-1. Это и будет ваше смещение.

$count = 100;
$offset = mt_rand( 0, $count-1 );

Comments

[chelkaz]

Не совсем пойму, что значит - между 0 и кол-вом-1 записей. Опишите пожалуйста подробнее.

[xmoonlight]

это грамотный и верный ответ!

[chelkaz]

А... Я так сейчас и делаю, я не мог понять что это такое "между 0 и кол-вом-1 записей" Вы странно написали просто. $offset = rand( 0, $count - 1 )

[Виталий Хоменко]

chelkaz: У вас N записей в таблице, вам нужно получить через mt_rand(), к примеру, случайное число от 0 до N-1. Это и будет ваше смещение.

$count = 100;
$offset = mt_rand( 0, $count-1 );

[Виталий Хоменко]

chelkaz: Отпишитесь пожалуйста о результате сравнения на реальных данных. Интересно.

[chelkaz]

Результат в пару раз побыстрее, но очень медленно. Мне нужно забить таблицы данными в 5 миллионов проходов через for. В этот момент за один проход, я добавляю пост в одну таблицу, где для него выбираю случайный элемент (это типа блога и их пол миллиона) так же случайного юзера их 50 тысяч. Потом рандомлю комменты и прочие, но по логам дольше всего это рандомное из таблицы где пол миллиона и + случайный юзер. Поэтому мне это не подошло, так как по расчетам мне потребуется 277 часов) Я сделал по другому, так как у меня id почти все подряд и не более 100 отсутствуют, я делаю рандом от 1 до последнего id и вытягиваю просто по id это в десятки раз быстрее и потом просто проверяю вытянутый результат на null. Сейчас все работает быстро, в ИТОГЕ сейчас как сделал я за секунду 15-20 готовых записей идет, а если через offset то 3-4

[Stalker_RED]

chelkaz: Это ужасно. Причем ужас начинается еще с 5 млн запросов в for. На том месте уже можно начинать оптимизоровать и получать ускорение на порядки (в сотни, тысячи, десятки тысяч раз).

[chelkaz]

Stalker_RED: Не совсем ясно, напишите пожалуйста подробнее. В чем ужас и что значит - на порядки (в сотни, тысячи, десятки тысяч раз)

[Виталий Хоменко]

chelkaz: У вас явные проблемы с логикой в приложении. Я не знаю что вы там делаете и как, но судя по вашим словам о цикле for на 5 миллионов итераций и прочем, понимаю, что MySQL у вас не самое медленное место. Я не Stalker_RED

[chelkaz]

Виталий IIIFX Хоменко: Логика правильная, мне нужно просто 5 миллионов раз повторить одно и тоже действие. Поэтому просто for. А вот за один цикл я делаю случайный элемент из таблицы, где 500 000 row и один рандомный Юзер из таблицы, где 50 000 row. Дальше логика не важна, для тестов я все убрал и оставил только echo в конце одного цикла с номером цикла, что бы на глаз увидеть скорость вывода. Так вот: RAND() очень медленно, offset на порядок быстрее, но самый быстрый все таки select by id что я и сделал. А то что написал Stalker_RED я не совсем понял, по его словам можно увеличить в десятки тысяч. Предположим у меня очень медленно по id выборка, допустим 0.1 секунда, и возьмем минимум по его мнению и увеличим всего в один десяток) Это уже 0.1/10000 = 0.00001 как то не понятно получается что он имел ввиду и как достичь такой скорости)

[Stalker_RED]

chelkaz: 0.00025150 - время выборки по id из таблицы в которой более 500000 записей на железе которому 9 лет.
А если выбирать сразу несколько записей (а что, так можно было?) то в пересчете на каждую придется еще меньше.

5 млн почти одинаковых запросов - это кошмар. Я верю что вам нужны эти данные, но скорее всего эти запросы можно объединять. Выбирать сразу 100 случайных юзеров, например. Или тысячу. Или вообще делать эти случайные штуки внутри БД.
277 часов на 5млн записей - это очень кошмарный кошмар.

Answer 2

[Максим Федоров]

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

Answer 3

[Rsa97]

ORDER BY RAND() не индексируется в принципе
Можно попробовать

SET @rand = (SELECT (MAX(`id`)-MIN(`id`))*RAND()+MIN(`id`) FROM `localities``);
SELECT * FROM `localities` WHERE WHERE `id` > @rand LIMIT 1;

Но распределение будет неравномерным, в местах, где есть пропуски `id` следующие за ними строки будут выпадать с большей вероятностью.