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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Развертывание бекапов mySQL из консоли unix

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

Как развернуть дамп базы данных mySQL, созданный ранее? Дамп представляет из себя mysql скрипт, в котором последовательно создаются таблицы (CREATE TABLE ...) и, возможно, другие объекты и производиться их наполнение (INSERT INTO ...). Нужно лишь отправить ...

Читать

Нумерация при выборке строк в mySQL

Апрель 24, 2023 г.

В MSSQL есть функция ROW_NUMBER(), которая нумерует строки выборки. В MySQL такой функции пока нет, но мы можем использовать трюк с переменными для достижения нужного эффекта. К примеру, вы хотите выбрать название книг и пронумеровать список. Изначально ...

Читать

 

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

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



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

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

  1. Run:

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

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

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

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

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