Верх страницы
Обложка к записи Оптимизация ORDER BY RAND()
Время для прочтения: 0 мин. 50 сек.

Оптимизация ORDER BY RAND()

Как выбрать случайную запись из таблицы в 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;

Как это работает

  • Во вложенном запросе мы определяем максимальное значение 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)

Теперь работает быстро и не зависит от размера таблицы.

Ссылки

Автор: Кобзарёв Михаил

Русский разработчик с 20-ти летним стажем. Работаю с PHP, ООП, JavaScript, Git, WordPress, Битрикс, Joomla, Drupal, Opencart, DLE, Laravel, Moonshine, SuiteCRM.

Оптимизирую сайты под Google Page Speed, настраиваю импорты для больших магазинов на WooCommerce + WP All Import. Пишу плагины на заказ. Все мои услуги.

Веду блог о разработке, дайджест в телеграмме и в ВК.

Вы всегда можете нанять меня.

Комментарии
Подписаться
Уведомить о
guest

0 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии
Предыдущая запись

Давайте дружить
в Telegram

Авторский блог вашего покорного слуги в Telegram про web, программирование, алгоритмы, инструменты разработчика, WordPress, Joomla, Opencart, Laravel, Moonshine, фильмы и сериалы