Выборка произвольных записей в базе данных Mysql. Проблемы и решения.

(Ответов: 1, Просмотров: 4150)
  1. Студент
    • Регистрация: 09.11.2011
    • Сообщений: 41
    • Репутация: 10
    Довольно таки часто при разработке того или иного приложения необходимо выполнить выборку произвольных значений из таблицы в базе данных 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 `tableLIMIT 990000

    Как же себя ведет MySQL при таком запросе?
    Отвечаю, если кто не в курсе, MySQL выберет 990001 запись и только потом, вернет одну последнюю запись. Так что при большом количестве записей данный метод не желательно использовать.
    Существует еще один способ, который объединяет в себе два предыдущих:
    PHP код:
    SELECT FROM `tableWHERE id >= (SELECT FLOORMAX(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?
    Последний раз редактировалось ishamshur; 13.02.2012 в 16:56.
    • 2

    Спасибо сказали:

    cOAPerator(13.02.2012), Zvеr(13.02.2012),
  2. Дипломник
    • Регистрация: 06.06.2011
    • Сообщений: 105
    • Репутация: 11
    добавить практически нечего)

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

Похожие темы

Темы Раздел Ответов Последний пост
Проблемы с добавлением нового типа записей в WP
Web программирование 2 08.02.2012 23:05
Проблемы с базой данных на wordpress сайте
Web программирование 10 07.02.2012 17:15
Уделяем внимание базе данных MySQL
Дайджест блогосферы 4 27.01.2011 14:48
Запрос для поиск и замены текста в базе данных
Дайджест блогосферы 2 12.10.2010 20:05

У кого попросить инвайт?

Вы можете попросить инвайт у любого модератора:

Информеры