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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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