Выборка произвольных записей в базе данных 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: Если Вам интересна тема минимализма, то можете принять участие в бесплатном марафоне, который проводит Алексей Опанасенко у себя на блоге. Главный принцип минимализма гласит, при минимуме усилий, получать максимум результатов и удовольствия. Если Вы хотите концентрироваться на самом главном, то этот марафон для Вас.
- Выбор SEO-специалиста: как не ошибиться
- formdesigner.ru – новый онлайн конструктор веб-форм любой сложности + МИНИ-КОНКУРС
- Как создать сайт о развивающейся компании по организации системы автоматического полива
- Выбор хостинга и доменного имени, для своего сайта!
- Продвижение мебельного интернет-магазина
-
Комментарии (0)
- Сайт
Facebook