[ishamshur]

Довольно таки часто при разработке того или иного приложения необходимо выполнить выборку произвольных значений из таблицы в базе данных MySQL. И данный вопрос довольно таки часто обсуждается на различных форумах и сообществах. В данной статье я решил собрать в одном месте, наиболее распространенные способы решения поставленной задичи.

На первый взгляд, кажется, что здесь нет ничего сложного. Достаточно выполнить один SQL-запрос к базе данных:

PHP код:

  
SELECT field FROM  table ORDER BY RAND() LIMIT 1 


Этот запрос на самом деле выберет одну произвольную запись из таблицы table и многие новички успешно используют его в своих проектах, даже не догадываясь как будет обрабатываться этот запрос в MySQL. А зря.


В процессе выполнения этого запроса MySQL копирует все записи (обращаю Ваше внимание на слово "Все") из таблицы table во временную таблицу, добавляя одно новое поле, в которое записывается какое-то произвольное значение. После этого вся временная таблица сортируется по полю с произвольным значением и только после этого выбирается n первых записей. В нашем случае одна первая. А теперь представьте таблицу, в которой находится 10000 записей или еще больше? А если веб-сайт имеет большую посещаемость и эти операции нужно делать несколько раз в секунду? В общем, такой запрос может надолго заставить задуматься любой сервер.


Что же делать, спросите Вы?


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

PHP код:

$result = mysql_query("SELECT MIN( `id` ) AS min, MAX( `id` ) AS max FROM table "); 
 $offset_row = mysql_fetch_object( $offset_result );  
$RID = mt_rand($offset_row->min,$offset_row->max);  
mysql_query("SELECT field FROM  table WHERE id = ".$RID." LIMIT 1"); 


Но, к сожалению, в большинстве случаев, данный метод нельзя применить, так как таблица в базе данных может содержать «дыры», т.е. пропуски, которые образуются при удалении записей. Поэтому случайное число, которое Вы сгенерируете, может не существовать в таблице базы данных MySQL. Поэтому можно делать выборку по условию ID <= или >= сгенерированного случайного числа:

PHP код:

mysql_query("SELECT field FROM  table WHERE id >= ".$RID." LIMIT 1"); 


А что делать, если в таблице нет уникального числового идентификатора?

Решение заключается в использовании параметра MySQL LIMIT. Данный параметр принимает 2 аргумента. Первый аргумент задает смещение первой возвращаемой строки, а второй задает максимальное количество возвращаемых строк. Смещение начальной строки равно 0 (не 1).


Идея заключается в том, чтобы генерировать случайное число для смещения первой возвращаемой строки, в диапазоне от 0 до количества строк в этой таблице.

PHP код:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` "); 
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset; 
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " ); 


Немного пояснений. Для того чтобы вычислить смещение первой строки мы определяем общее количество записей в таблице функцией COUNT(*). Дальше используя Mysql функцию RAND(), получаем случайное число в диапазоне от 0 до 1. Затем перемножаем результат этих функций и округляем до целого числа, используя функцию FLOOR(). Т.о. мы получим произвольное число, для смещения первой строки, которое и подставляем в параметр LIMIT.

Но и этот запрос не является оптимальным при большом количестве записей в таблице. Это связано со спецификой работы оператора LIMIT. Попробую объяснить на простом примере. Допустим, у нас есть таблица в базе данных MySQL, в которой содержится 1000000 записей. Попробуем выполнить вот такой запрос:

PHP код:

SELECT * FROM `table` LIMIT 990000, 1 


Как же себя ведет MySQL при таком запросе? Отвечаю, если кто не в курсе, MySQL выберет 990001 запись и только потом, вернет одну последнюю запись. Так что при большом количестве записей данный метод не желательно использовать.
Существует еще один способ, который объединяет в себе два предыдущих:

PHP код:

SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1; 


Как видно из запроса, мы в одном запросе делаем один подзапрос, в котором высчитываем случайное значение для id, оперируя максимальным его значением. Данный способ можно использовать, если у Вас есть уникальный числовой ключ и Вам нужно выбрать всего лишь одну запись из таблицы. Если же Вам надо будет больше, то это будут не совсем произвольные записи, так как данный запрос вернет записи, у которых id >= случайно сгенерированного числа, отсортированных по id. Достоинством данного метода является то, что все выполняется за один запрос и делается LIMIT 1, а не LIMIT xxx,1. Недостатком является то, что если нужно выбрать несколько записей, то данный подзапрос будет выполняться для каждой записи снова, высчитывая случайное число.


Если же Вам нужно получить n-е количество записей, то можно использовать такой способ:

PHP код:

$offset_result = mysql_query("SELECT MAX(id) AS max, MIN(id) AS min FROM `table`"); 
$offset_row = mysql_fetch_object( $offset_result ); 

$max = $offset_row->max;
 $min = $offset_row->min;  

$n = 5; //Количесто возвращаемых записей 
$i = 0; 
 $ids = array();

 while($i < $n){ 
   $ids[] = mt_rand($min,$max); 
}  

$result = mysql_query( "SELECT * FROM `table` WHERE id IN(".implode(',',$ids).") LIMIT ".$n); 


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

Оптимальным вариантом, как мне кажется, добавить в таблицу еще одно поле для сортировки с индексом, в которое писать порядок сортировки. Так как первичный ключ должен оставаться не измененным, то на это поле это ограничение не распространяется и вполне реально проследить, чтобы нумерация в этом поле всегда была по порядку, а главное без пропусков. Тогда этот запрос:

PHP код:

$result = mysql_query( "SELECT * FROM `table` WHERE id IN(".implode(',',$ids).") LIMIT ".$n); 


Будет выполняться довольно таки быстро. Но быстрая выборка накладывает определенные сложности на поддержание целостности таблицы, так как при добавлении новых записей необходимо получить последний индекс сортировки в таблице, увеличивая его на единицу. А при удалении записи, нужно значение сортировки удаляемой записи, переписать вместо максимального значения сортировки, существующего в этой таблице. Ведь нам не так важен порядок сортировки, как целостность значений. Поэтому при удалении можно выполнить такой запрос:

PHP код:

UPDATE table SET sort_order=$sort_order_of_deleted_item WHERE sort_order=$max_sort_order 


Где: sort_order – это поле сортировки
$sort_order_of_deleted_item – значение поля сортировки удаляемой записи
$max_sort_order – максимальное значение поля сортировки с таблице

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

Какой из данных способов использовать Вам, зависит от конкретной ситуации и структуры базы данных, от частоты добавления и удаления запесей в таблице базы данных MySQL. К сожалению, на сегодняшний день нет одного красивого решения данной проблемы. Я лишь привел наиболее распространенные способы решения данной проблемы, с краткими комментариями по каждому из них.


А как Вы выбираете произвольные записи из таблицы в базе данных MySQL?

[easymisc]

добавить практически нечего)

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