Blog. Just Blog

Выбор случайной строки из таблицы MySQL

Версия для печати Добавить в Избранное Отправить на E-Mail | Категория: Web-мастеру и не только | Автор: ManHunter
Одна из часто встречающихся задач при создании сайтов - выборка случайной строки из таблицы MySQL. Это может быть случайный баннер в ротации, какая-нибудь "фраза дня" или афоризм, промо-блок на главной странице и т.п. Применений масса. Я решил на практике протестировать несколько вариантов решения этой задачи. Исходные данные: база MySQL, таблица типа MyISAM проиндексирована по полю id, количество записей около 500000, поле id обозначено как PRIMARY KEY, в нумерации есть "дырки" из-за удаленных строк.

Переходим к тестированию. Традиционное решение сводится к такому запросу:

SELECT * FROM `table` ORDER BY RAND() LIMIT 1

Код на PHP для тестирования:
  1. // Тест №1
  2. $query="SELECT * FROM `table` ORDER BY RAND() LIMIT 1";
  3. $sql_result=mysql_query($query);
  4. $row=mysql_fetch_array($sql_result);
  5. mysql_free_result($sql_result);
Результаты не радуют. Среднее время выполнения одного такого запроса на моем компьютере занимает 2.5 секунды. На реальном проекте, особенно если он работает под нагрузкой, такие затраты времени недопустимы.

Второй вариант. Предположим, что количество "дыр" в базе минимальное или же они имеют небольшой диапазон. В этом случае можно сперва выбрать значение 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`

Код для тестирования:
  1. // Тест №3
  2. $query="SELECT MIN(`id`) AS `min`, MAX(`id`) AS `max` FROM `table`";
  3. $sql_result=mysql_query($query);
  4. $row=mysql_fetch_array($sql_result);
  5. mysql_free_result($sql_result);
  6.  
  7. // Теперь у нас есть минимальные и максимальные значения ID
  8. $min_id=$row['min'];
  9. $max_id=$row['max'];
  10.  
  11. // Получить стартовый ID
  12. $id=rand($min_id,$max_id);
  13.  
  14. $query="SELECT * FROM `table` WHERE `id`>".$id." LIMIT 1";
  15. $sql_result=mysql_query($query);
  16. $row=mysql_fetch_array($sql_result);
  17. mysql_free_result($sql_result);
Среднее время выполнения одного запроса всего 0.00013 секунды, отличный результат! Небольшой минус этого способа заключается в необходимости дополнительного запроса для получения граничных значений. Но если данные в таблицу добавляются не очень часто, то этот минус можно устранить за счет кэширования. Есть и более серьезный недостаток. Если таблица очень сильно фрагментирована, или же нумерация ID по какой-то причине идет с большим шагом, (например 1, 2, 3, 4, 50, 70, 200), то значения на правых границах "дыр" будут попадать в выборку гораздо чаще остальных.

И, наконец, четвертый вариант, также с использованием PHP. Теперь выберем все значения ID из таблицы, занесем их в промежуточный массив и затем выберем случайный ID уже из этого массива. Для этого воспользуемся таким вспомогательным запросом:

SELECT GROUP_CONCAT(`id` SEPARATOR ',') AS `id_list` FROM `table`

После выполнения этого запроса будут получены в виде строки все ID через запятую. По умолчанию объем данных, возвращаемых функцией GROUP_CONCAT ограничен 1024 байтами, поэтому потребуется внести изменения в файл конфигурации MySQL или изменить это ограничение динамически:

SET group_concat_max_len=4294967295

Код для тестирования:
  1. // Тест №4
  2. // Установить максимальный размер возвращаемых данные для GROUP_CONCAT
  3. $query="SET group_concat_max_len=4294967295";
  4. mysql_query($query);
  5.  
  6. // Выбрать список всех ID из таблицы
  7. $query="SELECT GROUP_CONCAT(`id` SEPARATOR ',') AS `id_list` FROM `table`";
  8. $sql_result=mysql_query($query);
  9. $row=mysql_fetch_array($sql_result);
  10. mysql_free_result($sql_result);
  11.  
  12. // Преобразовать строку в массив ID
  13. $id_list=explode(',',$row['id_list']);
  14. unset($row);
  15.  
  16. // Выбрать случайное значение ID из массива    
  17. $id=$id_list[rand(0,count($id_list)-1)];
  18. unset($id_list);
  19.  
  20. // Выбрать строку со случайным ID    
  21. $query="SELECT * FROM `table` WHERE `id`=".$id;
  22. $sql_result=mysql_query($query);
  23. $row=mysql_fetch_array($sql_result);
  24. mysql_free_result($sql_result);
Скорость выполнения превышает даже предыдущий вариант - 0.00012 секунды. Минус этого метода в потреблении памяти для получения и обработки списка всех ID. При большом объеме данных это может стать критичным. Частично это также решается кэшированием результатов выборки, если таблица изменяется нечасто. Но зато ID выбираются равномерно, даже при сильно фрагментированной таблице. Также этот способ удобно использовать, когда список ID предварительно отбирается по какому-то условию.

Как видно из тестов, для реальных проектов лучше всего использовать методы 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 секунды. Неплохой результат, тем более, что здесь не используются дополнительные запросы и все вычисления идут на стороне базы. Надо будет тоже принять на вооружение.

Поделиться ссылкой ВКонтакте Поделиться ссылкой на Facebook Поделиться ссылкой на LiveJournal Поделиться ссылкой в Мой Круг Добавить в Мой мир Добавить на ЛиРу (Liveinternet) Добавить в закладки Memori Добавить в закладки Google
Просмотров: 10138 | Комментариев: 9

Метки: MySQL, PHP
Внимание! Статья опубликована больше года назад, информация могла устареть!

Комментарии

Отзывы посетителей сайта о статье
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());

А если использовать такой вариант? по идее писанины не много, но время выполнения запроса мне не известно. и вроде как с разрывами проблем не должно быть
Андрей (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/ (если ссылку нельзя - то удали)

там например описывается методы решения проблемы равномерности/случайности результатов при больших разрывах значений.

Добавить комментарий

Заполните форму для добавления комментария
Имя*:
Текст комментария (не более 2000 символов)*:

*Все поля обязательны для заполнения.
Комментарии, содержащие рекламу, ненормативную лексику, оскорбления и т.п., а также флуд и сообщения не по теме, будут удаляться. Нарушителям может быть заблокирован доступ к сайту.
Наверх
Powered by PCL's Speckled Band Engine 0.2 RC3
© ManHunter / PCL, 2008-2017
При использовании материалов ссылка на сайт обязательна
Время генерации: 0.07 сек. / MySQL: 2 (0.0031 сек.) / Память: 4.5 Mb
Наверх