
Выбор случайной строки из таблицы MySQL
Одна из часто встречающихся задач при создании сайтов - выборка случайной строки из таблицы MySQL. Это может быть случайный баннер в ротации, какая-нибудь "фраза дня" или афоризм, промо-блок на главной странице и т.п. Применений масса. Я решил на практике протестировать несколько вариантов решения этой задачи. Исходные данные: база MySQL, таблица типа MyISAM проиндексирована по полю id, количество записей около 500000, поле id обозначено как PRIMARY KEY, в нумерации есть "дырки" из-за удаленных строк.Переходим к тестированию. Традиционное решение сводится к такому запросу:
SELECT * FROM `table` ORDER BY RAND() LIMIT 1
Код на PHP для тестирования:
Code (PHP) : Убрать нумерацию
- // Тест №1
- $query="SELECT * FROM `table` ORDER BY RAND() LIMIT 1";
- $sql_result=mysql_query($query);
- $row=mysql_fetch_array($sql_result);
- mysql_free_result($sql_result);
Второй вариант. Предположим, что количество "дыр" в базе минимальное или же они имеют небольшой диапазон. В этом случае можно сперва выбрать значение ID из промежутка от 1 до максимального ID, а случайной строкой будет являться запись, расположенная сразу же после этого значения. В виде запроса это будет выглядеть следующим образом:
SELECT * FROM `table` WHERE `id`>(SELECT ROUND(MAX(`id`)*RAND()) FROM `table`) LIMIT 1
Код для тестирования такой же, за исключением текста запроса. А вот результат вообще не лезет ни в какие ворота: среднее время выполнения 63.7 секунды! Естественно, при таких показателях об использовании этого метода в реальных проектах речи быть не может.
Третий вариант. Попробуем сделать почти то же самое, но только теперь расчет случайного ID возложим на PHP. Для этого нам понадобится дополнительный запрос, с помощью которого мы получаем минимальное и максимальное значение ID:
SELECT MIN(`id`) AS `min`, MAX(`id`) AS `max` FROM `table`
Код для тестирования:
Code (PHP) : Убрать нумерацию
- // Тест №3
- $query="SELECT MIN(`id`) AS `min`, MAX(`id`) AS `max` FROM `table`";
- $sql_result=mysql_query($query);
- $row=mysql_fetch_array($sql_result);
- mysql_free_result($sql_result);
- // Теперь у нас есть минимальные и максимальные значения ID
- $min_id=$row['min'];
- $max_id=$row['max'];
- // Получить стартовый ID
- $id=rand($min_id,$max_id);
- $query="SELECT * FROM `table` WHERE `id`>".$id." LIMIT 1";
- $sql_result=mysql_query($query);
- $row=mysql_fetch_array($sql_result);
- mysql_free_result($sql_result);
И, наконец, четвертый вариант, также с использованием PHP. Теперь выберем все значения ID из таблицы, занесем их в промежуточный массив и затем выберем случайный ID уже из этого массива. Для этого воспользуемся таким вспомогательным запросом:
SELECT GROUP_CONCAT(`id` SEPARATOR ',') AS `id_list` FROM `table`
После выполнения этого запроса будут получены в виде строки все ID через запятую. По умолчанию объем данных, возвращаемых функцией GROUP_CONCAT ограничен 1024 байтами, поэтому потребуется внести изменения в файл конфигурации MySQL или изменить это ограничение динамически:
SET group_concat_max_len=4294967295
Код для тестирования:
Code (PHP) : Убрать нумерацию
- // Тест №4
- // Установить максимальный размер возвращаемых данные для GROUP_CONCAT
- $query="SET group_concat_max_len=4294967295";
- mysql_query($query);
- // Выбрать список всех ID из таблицы
- $query="SELECT GROUP_CONCAT(`id` SEPARATOR ',') AS `id_list` FROM `table`";
- $sql_result=mysql_query($query);
- $row=mysql_fetch_array($sql_result);
- mysql_free_result($sql_result);
- // Преобразовать строку в массив ID
- $id_list=explode(',',$row['id_list']);
- unset($row);
- // Выбрать случайное значение ID из массива
- $id=$id_list[rand(0,count($id_list)-1)];
- unset($id_list);
- // Выбрать строку со случайным ID
- $query="SELECT * FROM `table` WHERE `id`=".$id;
- $sql_result=mysql_query($query);
- $row=mysql_fetch_array($sql_result);
- mysql_free_result($sql_result);
Как видно из тестов, для реальных проектов лучше всего использовать методы 3 и 4, а для очень небольших таблиц допускается первый вариант. Выбор оптимального решения зависит от конкретной задачи.
UPD. Протестировал на таблице код из комментариев. Вот что у меня получилось:
SELECT * FROM `table` AS `r1`
JOIN (SELECT (RAND()*(SELECT MAX(`id`)-MIN(`id`) FROM `table`) +
(SELECT MIN(`id`) FROM `table`)) AS `id`) AS `r2`
WHERE `r1`.`id` >= `r2`.`id` ORDER BY `r1`.`id` ASC LIMIT 1
Тестовый код аналогичен первому и второму вариантам. Среднее время выполнения такого запроса получилось 0.014 секунды. Неплохой результат, тем более, что здесь не используются дополнительные запросы и все вычисления идут на стороне базы. Надо будет тоже принять на вооружение.
Просмотров: 14502 | Комментариев: 9

Внимание! Статья опубликована больше года назад, информация могла устареть!
Комментарии
Отзывы посетителей сайта о статье
ManHunter
(29.04.2014 в 22:24):
Да, все правильно, должен быть минус. А по поводу sizeof, то это те же яйца, только названные чуть иначе: http://ru2.php.net/manual/en/f...n.sizeof.php

index
(29.04.2014 в 22:06):
Вроде в Тест №4 маленькая ошибка закралась. Вместо $id=$id_list[rand(0,count($id_list)+1)]; должно быть $id=$id_list[rand(0,count($id_list)-1)]; то есть вместо + надо -. И лучше вместо count использовать sizeof.

grassus
(31.05.2012 в 13:22):
$id_count = mysql_numrows(mysql_query('select id from `table`'));
$query = 'select * from `table` order by `id` desc limit '.rand(1, $id_count).', 1';
$result = mysql_query($query) or die(mysql_error());
А если использовать такой вариант? по идее писанины не много, но время выполнения запроса мне не известно. и вроде как с разрывами проблем не должно быть
$query = 'select * from `table` order by `id` desc limit '.rand(1, $id_count).', 1';
$result = mysql_query($query) or die(mysql_error());
А если использовать такой вариант? по идее писанины не много, но время выполнения запроса мне не известно. и вроде как с разрывами проблем не должно быть

Андрей
(30.12.2011 в 09:57):
Как вариант - не забываем оптимизировать таблицу.

64-ядерный процессор
(21.04.2011 в 22:26):
ManHunter, так там же есть запрос, которые может изменить текущее значение автоинкремента.

ManHunter
(21.04.2011 в 10:28):
Ручное вмешательство в индексы, тем более с автоинкрементом - очень хреновая практика.

64-ядерный процессор
(20.04.2011 в 22:16):
ManHunter, раньше в своих скриптах я использовал самопальную функцию, которая обрабатывала таблицу и убирала из неё дырки.

ManHunter
(20.04.2011 в 10:15):
kruvas, проверил, результат тестовых замеров в статье. Спасибо!

kruvas
(20.04.2011 в 01:46):
еще можно так, на чистом скуле. были бы интересны сравнительные замеры на той же таблице (из статьи)...
SELECT *
FROM `table` AS `r1` JOIN
(
SELECT (RAND() * (SELECT MAX(`id`)-MIN(`id`) FROM `table`) + (SELECT MIN(`id`) FROM `table`) ) AS `id`
)
AS `r2`
WHERE `r1`.`id` >= `r2`.`id`
ORDER BY `r1`.`id` ASC
LIMIT 1;
вот тут еще занимательная статья на аналогичную тему (это решение оттуда) http://jan.kneschke.de/project...der-by-rand/ (если ссылку нельзя - то удали)
там например описывается методы решения проблемы равномерности/случайности результатов при больших разрывах значений.
SELECT *
FROM `table` AS `r1` JOIN
(
SELECT (RAND() * (SELECT MAX(`id`)-MIN(`id`) FROM `table`) + (SELECT MIN(`id`) FROM `table`) ) AS `id`
)
AS `r2`
WHERE `r1`.`id` >= `r2`.`id`
ORDER BY `r1`.`id` ASC
LIMIT 1;
вот тут еще занимательная статья на аналогичную тему (это решение оттуда) http://jan.kneschke.de/project...der-by-rand/ (если ссылку нельзя - то удали)
там например описывается методы решения проблемы равномерности/случайности результатов при больших разрывах значений.

Добавить комментарий
Заполните форму для добавления комментария
