Выборка случайных строк в mysql

Иногда нужно выбрать строки из базы данных в случайном порядке. При сайтостроительстве эта задача возникает довольно часто — вывести случайную тему из форума, показать в блоке случайный продвигаемый товар, показать случайный ответ посетителя за последнюю неделю и т.п.

Подходов к решению задачи довольно много. Первое решение «в лоб» и чисто средствами MYSQL.

Выборка случайных строк средствами самого MYSQL

Начиная, кажется, с 4й версии mysql стала работать следующая конструкция запроса:

Запрос опирается на случайную сортировку с использованием псевдослучайного генератора чисел функции rand(). При этом запрос выбирает N случайных строк. Очень эффектная конструкция, но производительность запроса сильно падает с ростом размеров выбираемого блока данных. Исходя из целей выборки, можно сократить число предварительно выбираемых строк, задав условия в части запроса ‘where’. Что то вроде :

Понятно, что если мы зададим ‘тяжелые’ условия, связанные с не индексируемыми полями или требующие сложных вычислений, то опять же проиграем в производительности.

Тем не менее, при работе с небольшим числом строк, такой подход вполне приемлем.

Выборка с предварительным расчетом

Что делать, когда Mysql не умеет, или база данных не позволяет делать такие варварские запросы? Будем использовать часть запроса LIMIT. Для начала нам нужно узнать, сколько всего строк в таблице.

В столбце ‘Rows’ мы получим текущее число строк таблицы. А дальше вычислим произвольный номер из известного уже диапазона и выполним запрос на выборку строки с (псевдо)случайным номером. На PHP это будет выглядеть примерно так:

Для того чтобы выбрать N случайных строк, придется выполнить последние две операции N раз, либо, если можно пренебречь случайностью набора строк, выполнить запрос так:

Для большого числа строк, в выбираемом блоке данных, опять же получим снижение быстродействия, особенно для случая N + 1 запросов. Подход более эффективен по сравнению с предыдущим подходом (когда выборка у нас большая) и работает для более ранних версий MySQL, что иногда очень важно.

Двухступенчатая выборка

Этот подход опирается на два ключевых момента и является развитием предыдущего метода. Первое требование — небольшое число строк выборки, а второе — наличие ключевого столбца в таблице. Если первое требование не всегда удаётся удовлетворить в полной мере, то второе условие практически всегда удовлетворяется.

Суть метода. Что если сначала выбрать все идентификаторы подходящих строк средствами СУБД,  выбрать из них N случайных идентификаторов средствами PHP (или другого, используемого вами языка), а потом снова средствами СУБД выбрать строки с найденными ID?

Гипотетический пример кода на PHP:

В таком подходе мы избавляемся от потенциально неэффективного использования части запроса LIMIT. Быстродействие в большей мере зависит от скорости первого запроса и объема выборки, чем от выборки уже самих данных.

Написать комментарий

Мало букафф? Читайте есчо !

Как получить номер строки в MySql?

Ноябрь 5, 2016 г.

Что делать, если нужно пронумеровать строки прямо в запросе? Полистав руководство MySql 5.5, нужной функции я не нашел. Мне нужно было сохранять позиции пользователей в рейтинге, т.е. пронумеровать пользователей в зависимости от значения их рейтинга. ...

Читать

Создание бекапов базы mySQL из консоли unix

Февраль 13, 2018 г.

Ещё одна шпаргалка по администрированию UNIX серверов. На этот раз рассмотрим несколько полезных команд для создания бекапа базы данных из консоли. Нам понадобится команда mysqldump, которая позволяет создавать sql скрипт базы или её части. Создание ...

Читать

 

Комментарии к «Выборка случайных строк в mysql»

Понравилась статья? Есть вопросы? - пишите в комментариях.



Комментарий:

Много комментариев в “Выборка случайных строк в mysql”

  1. Run:

    Спасибо, автор! Инфа — что надо! Как раз думал над выборками случайных строк. Узнал пару интересных способов.

  2. Александр:

    Вот еще одна похожая статья на эту тему: http://plutov.by/post/order_by_rand_performance

  3. Александр Плютов:

    Я вот такой провел эксперимент по ORDER BY: http://plutov.by/post/order_by_rand_performance