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

Что имеем?

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

Таблица(test) имеет следующую структуру:

• — pk_id ( первичный ключ )
• — id ( поле заполненное разными числами )
• — value ( поле заполненной с помощью rand() )

Первичный ключ не имеет дыр и начинается с 1.

Способы получения

1. ORDER BY rand + LIMIT

   

   Получение одной строки:

   SELECT pk_id FROM test ORDER BY rand() LIMIT 1
   

   Среднее время выполнения в MySQL — 6.150 секунд

   Попробуем взять 100 записей

   SELECT pk_id FROM test ORDER BY rand() LIMIT 100
   

   Среднее время выполнения 6.170-6180 секунды
   То есть разница во времени между получением 1 и 100 случайных строк не существенна.

2. COUNT * rand()

   

   Получение одной строки:

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

   С помощью

   SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1)
   

   получаем случайное число от 0 до количества строк в таблице.
   Далее нашему случайному числу присваиваем алиас «rnd» и используем в WHERE для эквивалентного сравнения с pk_id.
   Среднее время выполнения — 1.04 секунды
   Далее нужно немного изменить данный запрос, что бы можно было вытягивать несколько строк.
   Добавим еще несколько получаемых полей в наш подзапрос и изменим проверку в WHERE с "=" на IN

   SELECT t.pk_id FROM test as t,
   (SELECT SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd,
   SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd2,
   SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd3
   FROM test LIMIT 1) t
   WHERE t.pk_id IN (rnd,rnd2,rnd3)
   

   Среднее время выполнения — 1.163 секунды.
   При увеличении количества получаемых строк заметно увеличивается время выполнения запроса.
   Про 100 строк даже страшно подумать :)

3. INFORMATION_SCHEMA + LIMIT

   

   Получение одной строки:

   SELECT t.pk_id FROM test as t,
   (SELECT SUBSTRING_INDEX((SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test') *rand(),'.', 1) as rnd 
   FROM test LIMIT 1) tmp
   WHERE t.pk_id = rnd
   

   С помощью подподзапроса получаем количество строк в таблице 'test', не используя агрегатную функцию COUNT и дальнейшее сравнение происходит как в способе 2.
   Среднее время выполнения - 0.042 секунды
   Минимально замеченное время выполнения — 0.003 секунды.
   Попробуем получить 100 строк:

   SELECT t.pk_id FROM test as t,
   (SELECT SUBSTRING_INDEX((SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test') *rand(),'.', 1) as rnd 
   FROM test LIMIT 100) tmp
   WHERE t.pk_id in (rnd)
   ORDER BY pk_id
   

   Меняем в WHERE "=" на IN и изменяем лимит возвращаемых строк подзапросом на 100.
   Среднее время выполнения - 0.047 секунды
   Время на получения 1000 записей — 0.053 секунды
   Время на получение 10000 записей ~ 0.21 cекунды
   И напоследок 100 000 записей берем за 1.9 секунды
   Минус данного подхода в том, что в получаемом количество строк из INFORMATION_SCHEMA немного больше, чем COUNT(*) и по этому при возврате 100 000 строк теряется 7-8 строк. На 1-100 такого практически нету(Чем больше таблица, тем меньше шанс)

Выводы

• Первый способ хороший тем, что в любом случае вернет вам случайную строку, независимо от дыр в полях и их начального значения, но самый медленный
• Второй способ намного лучше подойдет в таблицах, где нету дыр. Работает в 6 раз быстрее, чем первый способ (на возврате одной строки).
• Третий способ можно использовать на свой страх и риск(который очень уж незначительный), потому что можно потерять строку(строки) при значение rand() максимально приближенного к 1. Скорость возврата одной строки, по сравнению с первым способом, отличается в 150 раз.
  Если уж вернулось не 100 строк, а 99, то можно еще раз послать запрос на сервер.

Автор: DeathCore