Генератор форм

Для веб-мастера

Реклама

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

Рейтинг:
Дата: 12 февраля 2012 Просмотры 13351
Выборка произвольных записей в базе данных Mysql. Проблемы и решения.

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

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

SELECT field FROM  table ORDER BY RAND() LIMIT 1

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

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

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

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

$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 <= или >= сгенерированного случайного числа:

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

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

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

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

$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 записей. Попробуем выполнить вот такой запрос:

SELECT * FROM `table` LIMIT 990000, 1

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

Существует еще один способ, который объединяет в себе два предыдущих:

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-е количество записей, то можно использовать такой способ:

$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);

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

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

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

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

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?

Если знаете еще, какие-либо способы решения данной проблемы, отписуйтесь в комментариях!

P.S: Если Вам интересна тема минимализма, то можете принять участие в бесплатном марафоне, который проводит Алексей Опанасенко у себя на блоге. Главный принцип минимализма гласит, при минимуме усилий, получать максимум результатов и удовольствия. Если Вы хотите концентрироваться на самом главном, то этот марафон для Вас.

Оцените эту статью:



Интересно почитать

    Получать новые материалы этого сайта на свой email адрес?

    Отправлять мне комментарии по эл. почте?

Простая CRM

Нашли ошибку в тексте

Система Orphus
https://biznesguide.ru/coding/172.html
4,11 из 5 на основе 11 оценок.