Вы здесь

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

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

Материал взят отсюда

 

Tags: