Что имеем?
Есть слабенький ноутбук, таблица на несколько миллионов строк и нужно выбирать разное количество случайных строк в одном запросе. Дальнейшие выборки нас не интересуют.
Таблица(test) имеет следующую структуру:
- — pk_id ( первичный ключ )
- — id ( поле заполненное разными числами )
- — value ( поле заполненной с помощью rand() )
Первичный ключ не имеет дыр и начинается с 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 случайных строк не существенна.
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
С помощью
получаем случайное число от 0 до количества строк в таблице.ROUND(COUNT(pk_id)*rand())
Далее нашему случайному числу присваиваем алиас «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 строк даже страшно подумать :)
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 строки больше, для перестраховки :)
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 за идею.