SQL: Не используйте ORDER BY RAND() или как получить случайные строки из таблицы

Довольно часто люди используют такие запросы, как...

SELECT quote FROM quotes ORDER BY RAND() LIMIT 1

чтобы получить случайную строку (строки) из таблицы. Это довольно плохая идея. Для больших таблиц, если в вашей таблице всего 50-100 строк, используйте все, что хотите.

Что происходит, когда вы запускаете такой запрос? Допустим, вы запускаете этот запрос для таблицы с 10 000 строк, затем SQL-сервер генерирует 10 000 случайных чисел, сканирует эти числа в поисках наименьшего и выдает вам эту строку. Генерация случайных чисел является относительно дорогой операцией, поиск наименьшего из них (если у вас LIMIT 10, нужно будет найти 10 наименьших чисел) также не так быстро (если цитата является текстом, она медленнее, если это что-то с фиксированным размером, это быстрее, возможно из-за необходимости создания временной таблицы).

Так. Как вы можете выбрать случайную строку (строки) без этих накладных расходов? Легкой замены не бывает. Вы можете использовать что-то вроде:

SELECT COUNT(*) AS cnt FROM quotes

сгенерируйте случайное число от 0 до cnt-1 на вашем языке программирования и выполните запрос:

SELECT quote FROM quotes LIMIT $generated_number, 1

Да. Это два запроса, но они НАМНОГО быстрее, чем первый. Этот вариант хорош, если вам нужна только одна случайная строка. Если вам нужно больше строк, вы все равно можете использовать этот трюк, просто вычтите X (X — количество строк, которые вам нужны) из cnt при генерации случайного числа и измените запрос на:

SELECT quote FROM quotes LIMIT $generated_number, X

Но это даст вам X последующих строк, начиная со случайной позиции. Если это не вариант, вы можете использовать другой подход: большую часть времени у вас есть уникальное числовое поле в таблицах, которое начинается с 1 и продолжает расти, поэтому вы можете сделать что-то вроде:

SELECT MAX(id) AS maxid FROM quotes

сгенерируйте X случайных чисел от 1 до X, соедините эти числа в строке с разделителем ',' и запустите этот запрос:

SELECT quote FROM quotes WHERE id IN ($idlist)

Конечно, у вас могут быть некоторые удаленные идентификаторы, и запрос может вернуть меньше строк, чем вам нужно. Но вы можете сгенерировать в 10 раз больше случайных идентификаторов и изменить запрос, чтобы он выглядел так:

SELECT quote FROM quotes WHERE id IN ($list_with_10_times_more_ids_than_x) LIMIT $x

теперь, если у вас не слишком много строк, удаленных из таблицы, шансы, что вы не найдете, скажем, 10 существующих идентификаторов из списка со 100 случайными числами, близки к нулю, и вы можете включить в свою программу код, который проверит, есть ли у вас $x строк в результате, и если нет (скажем, один раз из 10000 раз), он запустит эквивалентный код ORDER BY RAND(). Вы также можете сгенерировать в 100 раз больше идентификаторов, чем вам нужно - единственные накладные расходы, которые вы вводите, - это генерация в 100 раз больше случайных чисел, чем $x (если вам нужно 5 случайных строк из таблицы с 10000 строками, лучше сгенерировать 500 случайных чисел, чем 10000) и синтаксический анализ запроса будет немного медленнее, но сканирование таблицы не будет медленнее — SQL-сервер прекратит поиск строк с соответствующими идентификаторами из списка, как только найдет $x существующих строк.