Альтернативный быстрый подход для получения случайных записей из Mysql таблиц вместо медленного "ORDER BY RAND()".
Сегодня сидя на работе получил письмо с сайта ruhighload.com.
Они прислали мне ссылку на тему интересующую множество людей. Далее по сабжу.
Как выбрать случайную запись из таблицы в Mysql?
SELECT id FROM files ORDER BY rand() LIMIT 1;
Но такие запросы работают очень медленно. Посмотрим на EXPLAIN:
EXPLAIN SELECT id FROM files ORDER BY rand() LIMIT 1;
Увидим, что Mysql создает временную таблицу и использует сортировку всех данных. Такой запрос будет работать все медленнее при наполнении таблицы:
+----+-----+------+----------------------------------------------+
| id | ... | rows | Extra |+----+-----+------+----------------------------------------------+| 1 | ... | 4921 | Using index; Using temporary; Using filesort |+----+-----+------+----------------------------------------------+
Правильным решением будет использование индекса и избавление от ORDER BY RAND(). Для этого нужно:
Определить максимальное значение ID (да, такая колонка должна быть и она должна быть ключом) в таблице.
Получить любое случайное число от нуля до максимального ID.
Выбрать первую запись из таблицы, где ID больше указанного случайного числа, отсортировав ее по этой же колонке.
Если перевести все в запрос:
SELECT f.id FROM files f JOIN ( SELECT RAND() * (SELECT MAX(id) FROM files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1;
# Эффективная замена ORDER BY RAND()
Как это работает
- Во вложенном запросе мы определяем максимальное значение ID. Допустим оно будет 100000.
- Дальше умножаем это значение на функцию RAND(). Она возвращает значение от 0 до 1. Пусть в примере будет 0.5. Тогда результат умножения будет 50000.
- После этого это значение с помощью JOIN прибавляется в каждой строке оригинальной таблицы.
- Фильтр f.id >= m.max_id выберет первую попавшуюся запись, ID которой будет больше 50000.
- Поскольку мы использовали сортировку ORDER BY f.id ASC, все пропущенные записи будут иметь значение меньше 50000.
- Это значит, что мы выбрали случайную запись из всей таблицы. Но в отличие от ORDER BY RAND(), мы использовали сортировку и фильтрацию по индексу ID (а значит эффективно).
Скорость такого запроса будет в несколько раз быстрее, чем оригинального:
mysql> SELECT id FROM files ORDER BY rand() LIMIT 1;+-------+| id |+-------+| 72643 |+-------+1 row in set (0.17 sec)
Ускоренная версия:
mysql> SELECT f.id FROM files f JOIN ( SELECT rand() * (SELECT max(id) from files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1;+-------+| id |+-------+| 86949 |+-------+1 row in set (0.00 sec)
Теперь работает быстро и не зависит от размера таблицы.
Комментарии отсутствуют