Выборка записей в случайном порядке.

В случае, когда из таблицы нужно выбрать записи и при этом отсортировать их случайным образом, крайне не желательно использовать запрос следующего вида:
SELECT * FROM table_name ORDER BY RAND() LIMIT 1000

Нежелательной, с точки зрения производительности, является конструкция
ORDER BY RAND()
В таких случаях более производительным вариантом будет использование следующей хранимой процедуры с последующим запросом:
CREATE PROCEDURE get_rands(IN cnt INT, IN tbl CHAR(32))
BEGIN
SET @iQuery = CONCAT('DROP TEMPORARY TABLE IF EXISTS ', tbl,'_rands');
PREPARE iExec FROM @iQuery;
EXECUTE iExec;
DROP PREPARE iExec;
SET @iQuery = CONCAT('CREATE TEMPORARY TABLE ', tbl,'_rands ( rand_id INT )');
PREPARE iExec FROM @iQuery;
EXECUTE iExec;
DROP PREPARE iExec;

loop_me: LOOP
IF cnt < 1   THEN LEAVE loop_me; END IF; 
SET @iQuery = CONCAT('INSERT INTO ', tbl,'_rands SELECT r1.id FROM ', tbl ,  
' AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM ', tbl , ')) AS id) AS r2  
WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1');
PREPARE iExec FROM @iQuery;
EXECUTE iExec;
DROP PREPARE iExec;
SET cnt = cnt - 1;
END LOOP loop_me;
END

При вызове данной процедуры, нужно указать необходимое количество записей для генерации и имя таблицы, id из которой будут выбираться случайным образом:
CALL get_rands(1000, 'table_name');

В данном случае процедура создаст временную таблицу table_name_rands и запишет в нее 1000 случайных id из таблицы table_name.
Теперь можно производить выборку данных:
SELECT t.id, t.name
FROM table_name t
INNER JOIN table_name_rands r ON t.id = r.rand_id

В результате выполнения этого запроса получаем 1000 записей из таблицы table_name, отсортированных случайным образом.
При выборке большого количества записей львиную долю времени забирает выполнение процедуры, поскольку там в цикле производится вставка n-записей. Но, суммарно, время, затраченное на выполнение процедуры (генерация случайных id и запись их во временную таблицу) + последующая выборка, или обновление, или удаление, меньше, чем если бы выборка и сортировка выполнялась с использованием конструкции ORDER BY RAND(). Поскольку, при выполнении ORDER BY RAND(), происходит то же, что мы делали в процедуре, только для всей исходной таблицы! Т.е. создается временный столбец, в который записываются случайные id и потом происходит сортировка по этому столбцу.
А теперь представьте, что в исходной таблице, например, 3 млн. записей, а вам нужно выбрать 500.
Преимущество описанного способа – очевидно.
Будьте здоровы!
Теги:
RAND
Добавлено: 12 Июля 2018 21:27:11 Добавил: Андрей Ковальчук Нравится 0
Добавить
Комментарии:
Нету комментариев для вывода...