Скорость выборки order by rand() в MySQL

Категория: / DEV Блог / PHP (LAMP)
Дано: таблица mysql 1К записей
Задача: нужно выбрать случайные 5 записей,

SELECT * FROM table ORDER BY rand() LIMIT 0,5 --0.014628


Запрос достаточно медленный, попробуем по-другому.

Попробуем выбрать произвольные идентификаторы до основного запроса данных

SELECT id FROM table ORDER BY rand() LIMIT 1; --0.000592
SELECT id FROM table ORDER BY rand() LIMIT 1; --0.000385
SELECT id FROM table ORDER BY rand() LIMIT 1; --0.000374
SELECT id FROM table ORDER BY rand() LIMIT 1; --0.000380
SELECT id FROM table ORDER BY rand() LIMIT 1; --0.000377
-- всего 0.002075
 
-- или так, быстрее
SELECT id FROM table ORDER BY rand() LIMIT 0,5 --0.000483
 
-- сама выборка
SELECT * FROM table WHERE id IN (80,413,81,127,113) --0.000464


Последние два запроса в сумме затрачивают 0,000947 секунд.

Пробуем еще.

SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY rand()) LIMIT 0,5 --0.000750


И с джоином напоследок.

SELECT p1.* FROM table p1 JOIN (SELECT id FROM table ORDER BY rand() LIMIT 0,5) p2 ON p1.id = p2.id LIMIT 0,5 --0.000852


Вариант с подзапросом в IN получился самым быстрым. (не работает на старых версиях mysql)

-- IN
id      select_type     table   type    possible_keys   key     key_len         ref     rows    filtered        Extra
1       PRIMARY         table   ALL             999             Using where
2       DEPENDENT SUBQUERY      sat_sat_node    unique_subquery         PRIMARY         PRIMARY         4       func    1       Using index
 
-- JOIN
1       PRIMARY         <derived2>      ALL             5              
1       PRIMARY         p1      eq_ref  PRIMARY         PRIMARY         4       p2.id   1              
2       DERIVED         table   index   PRIMARY         4       999     Using index; Using temporary; Using filesort


Оптимальное решение сортировки по случайному числу подсмотрено на explain extended

DELIMITER $$
CREATE PROCEDURE `posts#getRandom`(IN lim INT UNSIGNED)
BEGIN
SELECT *
FROM (
SELECT @cnt := COUNT(*) + 1, @lim := lim
FROM ph_posts
) params
STRAIGHT_JOIN
 (
SELECT r.*,
 @lim := @lim - 1
FROM ph_posts r
WHERE (@cnt := @cnt - 1) AND RAND() < @lim / @cnt
) data
;
END$$
 
-- вызов
CALL `posts#getRandom`(10);


Первый подзапрос создаст переменные cnt и lim.
STRAIGHT_JOIN указывает что mysql выполнит первый запрос первым (бывает оптимизатор перемешивает порядок таблиц в запросе)
Условие WHERE вычисляется при перечислении для каждой строки (cnt = cnt - 1),
а выражение в SELECT lim = lim - 1 вычисляется когда строка УЖЕ выбрана (пока не дойдет до 0 и сработает условие остановки).
RAND() генерирует числа от 0.0000 до 1.0000. В условии проверяем что rand() < lim/cnt, так выборка получается равномерно распределенной по всему диапазону.