Выборка произвольных записей в MySQL

Rand выборка всегда интересная тема. Предположим мы имеем таблицу, которая хранит выборку с rss. RSS выгребается последовательно, 1 лента — 30-50 записей, всего rss лент 10. Задача получить 10 уникальных записей, которые были бы не от одной ленты.

Ну ORDER BY rand() это конечно не есть очень хорошо, особенно если в таблице планируется действительно большое кол-во записей, но по-моему по одному UNION'у на ряд тоже не лучший выход.

Кстати не факт, что записи будут все уникальными, даже больше, они частенько будут повторятся, если строк в среднем 300-400.

Представим что у нас 1 000 000 записей и rand_row у нас выпала где то в районе 900 000
то что нам сделает вот этот запрос?

SELECT * FROM tTable LIMIT rand_row, 1;

Правильно, выберет все 900 001 записей а потом возьмет одну последнею и так десять раз думаете это оптимальней.

Имхо, учитывая наличие в любой таблице такого размера Primary Key (который, как правило, int), проще получить набор случайных значений в PHP:

$n = rand()/getrandmax();

После чего единичная выборка делается мгновенно запросом вида:

select * from table where id=round(0.744296810005*(select count(*) from table));

А множественная делается в два запроса, где первый у нас выбирает этот самый count(*), а второй выглядит как:

select * from table where id in (round(0.744296810005*10000), round(0.544296810005*10000),....);

Впрочем, если результат первого запроса выбирать, а не помещать в переменную, то перемножение и округление можно делать средствами всё того же PHP. Работать это будет еще быстрее, чем с LIMIT и UNION.

Как-то так:

Псевдокод:
random = randRange(min_id, max_id)

SQL:
SELECT * FROM table WHERE id <= random LIMIT 1

Интересно. А если «угадывать»? Будет ли быстрее на огромных таблицах.
Чем строить 10 юнионов — может просто наугад выбрать 10 уникальных праймари кеев. Если записи удаляются не очень часто (как обычно и происходит) то высока вероятность того, что сразу и получим все 10. А потом если не хватит — доберем еще.

Или еще лучше — выберем 20 записей и опять таки случайным образом выкинем из них лишние или доберем недостающие.

Мне кажется что это будет оптимальнее на таблицах с миллионами записей нежели 9 юнионов и 10 лимитов…

глупый аутсорсер — как-то звучит предвзято. Такое впечатление что все аутсорсеры глупые.

На больших таблицах юзаем
SELECT * FROM table1 WHERE tid> (rand()*(SELECT count(*) FROM table1) ) LIMIT 1

хотя можно подзапрос другой юзать-не помню что быстрее, вроде одинаково почти получалось
SELECT TABLE_ROWS FROM information_schema.`TABLES` WHERE `TABLE_NAME`='table1'

Ребята, я написал такую функцию выбора трех текстовых объявлений случайным образом.
Хочу привести часть кода для поддержания дискуссии.
Какие минусы моего подхода?
$z — число объявлений, которые надо показать.
function draw_text_reclama($reclama_type, $z)
{
global $sql;
$result=$sql->query(«SELECT reclama_id
FROM reclama
WHERE reclama_active = '1'
AND reclama_type = '». $sql->escape ($reclama_type) ."'
AND UNIX_TIMESTAMP(reclama_begin) <= UNIX_TIMESTAMP()
AND UNIX_TIMESTAMP(reclama_end) >= UNIX_TIMESTAMP()
");
if ( $result )
{
if ( mysql_num_rows($result) != 0 )
{

$data = array ( );
while( $row = mysql_fetch_array($result) ) // наполняем массив $data числами reclama_id
{
$data[] = $row[0];
}
$rand_keys = array_rand( $data, $z );
for ( $i=0; $i<$z; $i++ )
{
$value = $data[$rand_keys[$i]];
$result = $sql->query ("
SELECT reclama_code
FROM reclama
WHERE reclama_id = '$value'
LIMIT 1
");
if ( $result )
{
$row=mysql_fetch_assoc($result);
$reclama_code=$row[«reclama_code»];
echo «div».$reclama_code."/div\n";
}
}
}
}
}

Как-то для одного проекта стояла подобная задача. Решили похожим способом, только выбирали одним запросом подряд N записей начиная со случайной. При этом перемешивали таблицу несколько раз в день по крону командой
ALTER TABLE tab ORDER BY RAND()

А вот в статье предлагается несколько решений и проводится сравнительный анализ. Мне приглянулась идея создать отдельную табличку из двух полей — нашего id (из оригинальной таблицы) и случайного номера без дырок. Заполнением и актуализацией этой дополнительной таблички занимаются триггеры.

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

Кстати, в вашем решении я вижу одну досадную ошибку: вычисление count(*) записей и собственно их выборку надо брать в одну транзакцию. Иначе можно попытаться выбрать строку, которой уже нет.

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

Кстати можно сделать забавную «оптимизацию» запроса который написал ваш аутсорсер:
SELECT * FROM tTable WHERE RAND()

пришел в голову такой вариант:
1) добавляем в таблицу еще одно поле типа bigint, например RandKey
2) создаем по полю RandKey индекс
2) в RandKey храним просто случайные числа, в диапазоне от 1 до MAX(BIGINT)
3) Чтоб выбрать 10 случайных записей: (это псевдо-SQL, для простоты объяснения)
SELECT * FROM table WHERE RandKey>( SELECT RAND(10,MAX(RandKey)-10) FROM table ) LIMIT 10

надо только ввести поправки, для случаев когда RAND() будет слишком близким к краям диапазона поля RandKey.
как думаете?

Не хотите в блог MySQL перенести?

Неужели затраты на вызов запроса так малы, что 10 запросов выполняются быстрее 1го?

Вы статью написали дабы унизить одного человека и показать свое превосходство? Если нет, то странно, знаете ли. Все эти ругательства, нежелание признавать неэффективность вашего метода. У меня такое чувство, что вы один из тех работодателей-дебилов, которые орут на своих работников.

У вас столько желчи к аутсорсерам, а сами небось предложили неадекватную оплату типа «написать движок за 300 руб» и ожидали получить сертифицированного профессионала

>станет понятно почему я сложил дюжину матершинных ругательств в адрес глупого аутсорсера

Вы бы лучше их внутреннему оптимизатору запросов MySQL сказали…

Простите конечно, но за запросы в цикле => Нужно сразу отрывать руки)))
А то что написал ,, аутсорсер,, считаю удовлетворительным.

Здравствуйте.

Извините, но ваше решение отвратительно.

Чтобы выбрать 10 случайных записей, вы делаете 11 операций, которые в терминологии баз данных называются «full scan» (полное чтение таблицы)

SELECT COUNT(*) FROM tTable — раз фуллскан. база данных просматривает все записи, чтобы узнать их количество. (Да, база не получает данные строк, но сканировать начала записей ей надо.)
SELECT * FROM tTable LIMIT X, 1 — то же самое, еще 10 сканирований таблицы от начала до числа X.

Время работы вашего алгоритма прямо пропорционально количеству записей. Для больших баз данных (скажем, со 100 миллионами строк) он не пригоден. Для маленькой базы ваш алгоритм не намного лучше алгоритма бедного аутсорсера, зато содержит больше строк кода. Удивительно, почему нельзя сделать индекс с номером записи и отбирать по нему.

И еще — некрасиво публично называть кого-то глупым.

15 000 000 записей в таблице, по 3-8к ежечасно новых, 10-15к в сутки удаляется. Выборки от 10шт до 3к.

собственно пока в системе мало пользователей — ORDER BY RAND() самое шоколадное. Но вот куда расти дальше — пока не ясно.

В конце дня лучшими программистами оказываются не те, кто написал самый потрясающе-элегантный, умопомрачительно-инновационный код. Настоящие рок-звёзды сдают проекты до намеченного срока и дешевле отведённого бюджета, и это именно то, в чём нуждается бизнес. И это именно те, кем все мы должны стремиться стать.
Откуда-то скопипастил. откуда не помню Но думаю как раз в тему будет. Есть над чем подумать!

# Чистый SQL

SELECT * FROM Table where TableID in (round(rand() * 2000), round(rand() * 2000), round(rand() *

2000), round(rand() * 2000), round(rand() * 2000), round(rand() * 2000), round(rand() * 2000),

round(rand() * 2000), round(rand() * 2000), round(rand() * 2000))

Это для случая, когда надо 10 записей из таблицы с 2000 записей.

# PHP + SQL
$recordsCount = 2000; // Переменная устанавливается запросом select count(*) from Table
$recordsLimit = 10;
$randomIDs = array();
for ($i = 0; $i < $recordsLimit; $i++) {
$randomIDs[] = rand(1, $recordsCount);
}
$queryText = 'SELECT * FROM Table where TableID in ('. join(', ', $randomIDs). ')';
// И т.д.

Сталкивался с подобной проблемой. Применил следующее:
1. Ввёл в таблицу дополнительный столбей int(11)
2. Раз в сутки этот столбец обновляется и в него пишутся значения rand
3. Выборка идёт примерно следующая: SELECT * FROM table WHERE rand_field > RAND() LIMIT 10

Таким образом функция RAND() вызывается один раз, нет копирования записей в темповую таблицу, обновление «случайного» столбца происходит ночью, когда большенство пользователей спит

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

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

А решение автора статьи вообще категорически неприемлемо IMHO. Например, хорошо если нужно извлечь 10 случайных записей. А если нужно извлечь 100 или 1000 случаных, что тогда? Кошмар получится. Не говоря о том, что при этом для надежности нужно или блокировать таблицу или использовать ту же транзакцию с уровнем изоляции repeatable read или serializable. И не говоря о том, что нужно учитывать возможные повторы.

Приемлемым и надежным решением может быть использование хранимой процедуры, которая использует курсор (нужен минимум MySQL 5). Например так:

1. получаем количество записей, допустим N
2. получаем M (в нашем случае M = 10) случайных значений от 0 до N — 1 и помещаем их в массив в порядке возрастания
3. открываем курсор вида DECLARE cur CURSOR FOR SELECT id,<другие_поля> FROM test
4. так как полученные нами случаные значения расположены по порядку, то вычитываем курсором по очереди все записи, и когда встречаются номера записей, соответствующие нашим выбранным случайным значениям, сохраняем эти записи.
5. когда выбрали запись, номер которой соответствует последнему случайному значению, закрываем курсор.
6. все, мы за один проход выбрали все M случайных записей, при этом не использовали никаких сортировок

Таким образом, если мы имеем миллионы записей, и номера случайных записей будут небольшими, то такая выборка будет очень быстрой. В большинстве случаев это решение считается оптимальным.

Но вообще скажу по секрету, что если в высокозагруженной системе нужно делать частые выборки случайных записей из некоторой таблицы или вьюва, даже если это миллионы записей, то намного правильнее хранить ID этих записей (а если позволяют ресурсы, то и таблицу целиком, но это уже опционально) в памяти в кеше, и делать поиск случайных записей именно в памяти, затем при необходимости извлекая записи из таблицы сразу по ID. Даже если там 10 млн записей, это всего лишь 40 Mb в кеше, не так уж и много для выделенного для данной задачи сервера.

Обычно, по опыту, в бизнес-проектах не требуется получать случайные некешируемые выборки из миллионов записей при каждом запросе. Даже если это огромная баннерная система, но и там совсем другие принципы, и «случайные» выборки вовсе не случайны… То есть мне воистину сложно представить себе бизнес-задачу, где нужна была бы выборка именно случайных значений из базы данных при каждом запросе пользователя, скорее всего тут имеет место проблема аналитической стадии и проектирования.

P.S. Называть кого-то глупым из-за написанного им кода считаю неправильным в принципе. По карйней мере автор, который привел свой неприемлемый вариант решения, уж точно не имеет права так делать IMHO. Пожалуйста, без обид, все мы учимся на ошибках, и я тоже могу ошибаться.

У меня была аналогичная задача, только надо было не случайно выбирать линии, а последовательно. При этом выборки должны быть закольцованы, а в последовательности ключей регулярно появляются «дыры». Решил примерно вот так:

	$cntcache = 'current-state/laste';
	if (file_exists($cntcache)){
		$lastID = file_get_contents($cntcache);
	} else $lastID = 0;

	for($i = 0; $i < 2; $i++){
		$lines = mysql_query( "SELECT id FROM table WHERE id > '$lastID' LIMIT 1");
		if (mysql_num_rows($lines) != 0){
			$line = mysql_fetch_row($anketes);
			$lastID = $line[0];
			break;
		} else $lastID = 0;
	}
	file_put_contents($cntcache, $lastID);

Может кто-то подскажет способ лучше и производительнее?

Я храню в мемкеше массив с айдишниками, оттуда беру и мешаю, дальше IN().

Вроде шустро бегает.

Что-то как-то строго Вы к аутсорсерам. Вопрос не в том, «что будет если в исходной таблице 10 000 записей. А что если 1 000 000?», а в том, сколько их там будет, и какие вообще требования к задаче. Предложенный аутсорсером вариант вполне себе рабочий для небольших таблиц, если в ТЗ размер таблиц не оговорен, то это, скорее, Ваш косяк.

Если записей действительно миллион, и выбирать надо быстро, я бы сделал так: завёл бы поле, куда помещал бы случайные значения при вставке. При нормальном распределении случайных чисел и большом количестве записей можно получать выборку с запасом, и затем ограничивать её с помощью LIMIT.

«Глупый и ленивый аутсорсер» решает поставленную задачу. Максимально простым для него способом. Поскольку его время — его деньги, а за «проявление смекалки» его никто никогда не поблагодарит. Может быть «умный и работящий постановщик» поленился расписать требования и поставил задачу неправильно?