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-6.180 секунды
   То есть разница во времени между получением 1 и 100 случайных строк не существенна.
   
   

2. COUNT * rand()

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

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

   
   С помощью

   ROUND(COUNT(pk_id)*rand())
   

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

   SELECT t.pk_id FROM test as t,
   (SELECT ROUND(COUNT(pk_id)*rand()) as rnd,
   ROUND(COUNT(pk_id)*rand()) as rnd2,
   ROUND(COUNT(pk_id)*rand()) 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 ROUND((SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test') *rand()) 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 ROUND((SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test') *rand()) 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 такого практически нету(Чем больше таблица, тем меньше шанс). Но всегда можно взять на 1-2 строки больше, для перестраховки :)
   
   

4. MAX * rand()

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

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

   
   Среднее время выполнения — 0.001 секунды
   Получение 100 строк:
   

   SELECT t.pk_id FROM test as t,
   (SELECT ROUND((SELECT MAX(pk_id) FROM test) *rand()) as rnd 
   FROM test LIMIT 100) tmp
   WHERE t.pk_id in (rnd)
   ORDER BY pk_id
   

   
   Среднее время выполнение — 0.003 секунды
   
   Самый быстрый способ, относительно предыдущих.
   

Выводы

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

UPD: В случае дыр в таблице, при возврате одной строки вторым и третьим способом можно делать не эквивалентную проверку а >= и добавить LIMIT 1. Тогда значение будет возвращено, даже если оно попало в «дырку»
Спасибо xel за это замечание.
UPD2: Добавлен 4 способ получение. Спасибо smagen за идею.