1763 читателя, 79 постов
Администрация
Модераторы
Все, что касается СУБД MySQL. Настройка и администрирование сервера, оптимизация, практика использования.
Выборка произвольных записей в MySQL
Есть вроде бы обычные задачи, которые можно решить сразу и не задумываясь, но при интенсивном использовании таких решений возникают проблемы, причем не маленькие. Об одной из таких задач я и хочу рассказать.
В процессе выполнения этого запроса MySQL записывает во временную таблицу все (!!!) строки исходной таблицы, с одним новым полем, в которое записываются результаты функции RAND () — т.е. набор произвольных значений. Затем эта временная таблица сортируется filesort по добавленному полю с произвольными значениями и далее выбираются первые 10 записей. Полный ппц. А теперь представтье что будет если в исходной таблице 10 000 записей. А что если 1 000 000? А что если эту выборку надо делать раз десять в секунду. Да тут любой супер-пупер сервер надолго уйдет в раздумья.
А ведь если немного проявить смекалку (а аутсорсеры думать не хотят, они работу сдают и идут пропивать деньги), то можно придумать элегантный и быстрый вариант, скорость работы которого не зависит от кол-ва строк в таблице.
Тут все довольно просто получается. Нам нужно оперировать только кол-вом записей в таблице, т.к. ключ может быть любым (составным, не числовым), а так же он может быть «разряженным» в результате удаления записей. Для начала узнаем общее кол-во записей в таблице:
Есть два варианта как это сделать: можно оформить это в виде куска PHP кода, а можно в виде MySQL хранимой процедуры.
На PHP все очень просто:
Если записей в исходной таблице не так много и появление повторяющихся строк в выборке неприемлемо — то можно предварительно сформировать список неповторяющихся произвольных значений, а потом уже составить по ним запрос.
Проблема
Взяли тут аутсорсера написать небольшой и несложный код на PHP и MySQL. Была одна из задач — выбрать несколько произвольных записей из таблицы в базе MySQL. И что же сделал этот ленивый и глупый аутсорсер? Конечно же написал бред типа такого:На первый взгляд все логично да и работает правильно. Выбираются 10 произвольных записей. Но если взглянуть на план выполнения этого запроса, то станет понятно почему я сложил дюжину матершинных ругательств в адрес глупого аутсорсера.SELECT * FROM tTable ORDER BY RAND() LIMIT 10;
В процессе выполнения этого запроса MySQL записывает во временную таблицу все (!!!) строки исходной таблицы, с одним новым полем, в которое записываются результаты функции RAND () — т.е. набор произвольных значений. Затем эта временная таблица сортируется filesort по добавленному полю с произвольными значениями и далее выбираются первые 10 записей. Полный ппц. А теперь представтье что будет если в исходной таблице 10 000 записей. А что если 1 000 000? А что если эту выборку надо делать раз десять в секунду. Да тут любой супер-пупер сервер надолго уйдет в раздумья.
А ведь если немного проявить смекалку (а аутсорсеры думать не хотят, они работу сдают и идут пропивать деньги), то можно придумать элегантный и быстрый вариант, скорость работы которого не зависит от кол-ва строк в таблице.
Задумка
Итак начнем потихоньку. Сначала упростим задачу, предположим что нам надо выбрать не 10, а всего одну запись.Тут все довольно просто получается. Нам нужно оперировать только кол-вом записей в таблице, т.к. ключ может быть любым (составным, не числовым), а так же он может быть «разряженным» в результате удаления записей. Для начала узнаем общее кол-во записей в таблице:
Далее просто вычислим произвольное число от 0 до кол-ва записей в этой таблицеSELECT COUNT(*) FROM tTable;
Теперь без проблем можно сделать выборку произвольной записи:rand_row = round(rand() * row_count);
SELECT * FROM tTable LIMIT rand_row, 1;
Решение на PHP
Так, с упрощенной задачей справились. Теперь нужно одолеть изначально поставленную, т.е. выбрать 10 записей. Логика тут проста: нужно посчитать 10 произвольных чисел от 0 до кол-ва записей в таблице, а затем сделать 10 запросов типа предыдущего и объединить их с помощью UNION.Есть два варианта как это сделать: можно оформить это в виде куска PHP кода, а можно в виде MySQL хранимой процедуры.
На PHP все очень просто:
Все просто и быстро. На исходной таблице с десятью тысячами записей прирост производительности по сравнению с первоначальным «ленивым» вариантом более чем в 12 раз.$row_count = mysql_result(mysql_query('SELECT COUNT(*) FROM tTable;'), 0);
$query = array();
while (count($query) < 10) {
$query[] = '(SELECT * FROM tTable LIMIT '.rand(, $row_count).', 1)';
}
$query = implode(' UNION ', $query);
$res = mysql_query($query);
Если записей в исходной таблице не так много и появление повторяющихся строк в выборке неприемлемо — то можно предварительно сформировать список неповторяющихся произвольных значений, а потом уже составить по ним запрос.
Решение на MySQL
Как вариант можно еще сделать это в виде хранимой процедуры:Производительность этого решения поменьше чем при подготовке составного запроса в PHP, но смысл в том чтоб показать возможность реализации и на «чистом» SQL.CREATE PROCEDURE `spRandomSelect`(IN aSchema VARCHAR(50), IN aTable VARCHAR(50), IN aNumRows INTEGER(11))
NOT DETERMINISTIC
READS SQL DАТА
BEGIN
DECLARE iQuery VARCHAR(10000);
DECLARE iNumRows INTEGER(11);
SET iNumRows = (SELECT `TABLE_ROWS` FROM `information_schema`.`TABLES` t
WHERE t.`TABLE_SCHEMA` = aSchema AND t.`TABLE_NAME` = aTable);
SET iQuery = '';
loop1: LOOP
SET iQuery = CONCAT(iQuery, '(SELECT * FROM `', aSchema, '`.`', aTable,
'` LIMIT ', ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows), ', 1)');
IF aNumRows > 1 THEN
SET iQuery = CONCAT(iQuery, ' UNION ');
END IF;
SET aNumRows = aNumRows - 1;
IF aNumRows > THEN
ITERATE loop1;
END IF;
LEAVE loop1;
END LOOP loop1;
SET @iQuery = iQuery;
PREPARE iExecStmt FROM @iQuery;
EXECUTE iExecStmt;
DRОP PREPARE iExecStmt;
END;
комментарии (140)
Вопрос возникает вот какой — т.к. в лентах сграбленных кол-во итемов различается, то при честной равномерной выборке из всех итемов всех лент — чаще будут встречаться итемы из тех лент, где наполнение больше.
Если нужно чтоб вхождения из всех лент в результирующем наборе были по кол-ву равны, то можно сначала составить рандомный список лент, а потом из каждой выбрать рандомную запись в результирующий набор. Получится 10 записей из 10 разных лент.
Но вариант с доп.полем вообще конечно достаточно спорный, так как убыстряя выборку — он слегка замедляет добавление рядов (нужно сначала получить номер предыдущей записи) и сильно замедляет удаление (нужно перестроить всю нумерацию начиная с текущего номера, заканчивая последним).
То есть, UPDATE table SET synth_key=$synh_key_of_deleted_item WHERE synth_key=$max_synth_key
select count(1) from table;
rand_row1 = round(rand() * row_count);
rand_row2 = round(rand() * row_count);
rand_row3 = round(rand() * row_count);
(для примера, понятно что здесь массив)
SET @row = 0;
SELECT @row := @row + 1 as rownum, table.* from table having rownum IN (rand_row1, rand_row2, rand_row3)
Насчет производительности не знаю.
Жалко нет одного красивого решения этой проблемы.
А пока приходится искать нетривиальные решения и пользваться некрасивым кодом…
то что нам сделает вот этот запрос?
SELECT * FROM tTable LIMIT rand_row, 1;
Правильно, выберет все 900 001 записей а потом возьмет одну последнею и так десять раз думаете это оптимальней.
Да и про LIMIT в mysql советую прочитать. Работает он очень быстро, поскольку строки MySQL попросту пропускает не записывая в результат.
Напомню, топик был мною написан о том, как я нашел более производительное решение чем ORDER BY RAND(). Я ни в коем случае не утверждаю что мой вариант сравнится по скорости с нативной UDF процедурой MySQL. Я лишь говорю что это наиболее производительный вариант с обозначенными мною условиями.
LIMIT $row, 1 UNION… работало до поры до времени при выборке 100 случайных из приблизительно 30К записей пока одним прекрасным днём не уложило на лопатки выделенный сервак :)
В общем, пришлось немного переделывать этот алгоритм в рамках конкретной задачи…
Ваши бы слова, да в эту физическую реальность… :)
У меня на форуме всего 1,5млн. постингов. Уже после ~700 тыс. на топиках с десятками страниц записи вида SELECT * FROM posts WHERE topic_id =… ORDER BY… LIMIT 10000, 15; стали капитально вешать сервер. Не смотря на все оптимизации индексов и настроек :)
Вылечилось только введением отдельного поля «номер страницы».
MySQL очень болезненно относится к выборке последних значений в большой отсортированной таблице.
После чего единичная выборка делается мгновенно запросом вида:
А множественная делается в два запроса, где первый у нас выбирает этот самый count(*), а второй выглядит как:
Впрочем, если результат первого запроса выбирать, а не помещать в переменную, то перемножение и округление можно делать средствами всё того же PHP. Работать это будет еще быстрее, чем с LIMIT и UNION.
Псевдокод:
random = randRange(min_id, max_id)
SQL:
SELECT * FROM table WHERE id <= random LIMIT 1
For large tables with auto incremented primary key values, I have found the following to be most efficient in obtaining one random row:
SELECT * FROM my_table
WHERE pk_column >=
(SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table)
ORDER BY pk_column
LIMIT 1;
|int pk|varchar(255) data|
выполняю:
И т.п., для первого варианта время выполнения всегда примерно 0,15 сек, для предложенного, каждый раз отличается. Более того, значение pk в выбранных строках не большие (в районе 100). Весь фокус в том, что запрос (SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table) выполняется для каждой записи… Не надо верить комментариям к мануалу, как самому мануалу ;)
P.S. mysql крутится на втором пне с частотой 450МГц, по тому для ощутимого времени выполнения большого кол-ва записей не понадобилось.
Потом вспомнил где я видел подобное решение и дал ссылку.
Что в принципе соответствует моим ожиданиям. Но:
Что уже не очень соответствует ожиданиям, но без limit 10 запись 12876 выдается первой. Если добавить ORDER BY, сделав тем самым поведение полностью предсказуемым, то чем оно принципиально лучше предложенного автором LIMIT? Единственный плюс, используя этот подход можно обойтись одним SQL запросом с одним подзапросом.
SELECT pk, data
FROM test AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(pk)
FROM test)) AS id)
AS r2
WHERE r1.pk >= r2.id
ORDER BY r1.pk ASC
LIMIT 1;
А лучше он тем, что делается LIMIT 1, а не LIMIT хxx, 1. К сожалению многие не знают как работает LIMIT в MySQL. Кажется что LIMIT 999, 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";
}
}
}
}
}
1) Вы делаете выборку всех идентификаторов. Если таких много — это лишняя нагрузка как на БД так и на интерпретатор PHP
2) Вы делаете по одному запросу на каждую результирующую запись. Для БД это лишняя работа по инициализации запроса и блокировке таблиц.
3) У вас часто идет лишнее присваивание промежуточным переменным. Не забывайте что PHP присваивает по значению, а значит происходит лишнее копирование участков памяти. Если текст в выборке большой, а сам этот код вызывается часто — то это довольно много лишней работы.
Если вы хотите придерживаться вашего алгоритма с выборкой ключей и выбора по ним произвольных значений — то как минимум нужно сформировать составной запрос через UNION, а только потом отправлять его в БД.
WHERE reclama_id IN (....)
Как составить запрос с IN(...)
Про промежуточные переменные: вы имеете в виду вместо
$reclama_code = $row[«reclama_code»];
echo $reclama_code;
Использовать
echo «тэги тэги». $row[«reclama_code»] .«тэги тэги\n»;
Запрос с IN составляется таким образом:
for ( $i=0; $i<$z; $i++ ) { $value .= $data[$rand_keys[$i]] . ',' ; } $result = $sql->query (" SELECT reclama_code FROM reclama WHERE reclama_id IN (" . rtrim($value, ',') . ") LIMIT $z ");Таким образом, к базе делается только один запрос.
Выдает ошибку MySQL… right syntax to use near ') LIMIT 1' at line 3
В случае, если активных объявлений оказалось 1
И делаем все проще:
$rand_keys = array_rand( array_flip( $data ), $z );
Вот это удаляем вообще:
for ( $i=0; $i<$z; $i++ ) {
$value .= $data[$rand_keys[$i]]. ',';
}
А вот это: «rtrim($value, ',')» заменяем на: «join( ',', $rand_keys )»
ALTER TABLE tab ORDER BY RAND()
Вот только в моих условиях оно не подойдет. Нужна действительно произвольная выборка и перемешивание таблицы хоть ночью хоть днем сильно бы сказалось на производительности, т.к. нагрузка на сервер постоянно очень большая и записей в таблице очень много.
Но если у вас операции удаления записей частые, триггеры замучаются апдейтить дополнительную табличку. Можно, конечно, пойти чуть дальше и только помечать удаленные записи, а потом раз в N минут проходить сборщиком мусора и удалять все разом.
Кстати, в вашем решении я вижу одну досадную ошибку: вычисление count(*) записей и собственно их выборку надо брать в одну транзакцию. Иначе можно попытаться выбрать строку, которой уже нет.
И не ругайте аутсорсера. Он сделал правильно по букварю. Для оптимизации же надо хорошо знать свои данные, что тяжело требовать от человека со стороны.
SELECT * FROM tTable WHERE RAND()
Потеряем время на вычисление и сравнение лишних RAND(), зато на несколько порядков уменьшим размер таблицы, которая сортируется в памяти. Естественно можно брать не 0.001, а подбирать какое-то другое значение, в зависимости от размера таблицы.
На моем примере, выборка 10 из 500.000, позволяет ускорить примерно в 3 раза. Забавно, что если вместо коэффициента 0.001 выбрать например 0.1 или 0.000001, но время выборки не изменяется вообще, по крайней мере на моих тестах.
тогда
SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RAND() )
где id — первичный ключ.
а то выбирать для сортировки все имеющиеся поля явно не айс…
SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RAND() LIMIT 0,10)
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.
как думаете?
328 голосов и в итоге -10 — это одни минусы? С математикой у вас туго, похоже.
Все с вами понятно. Строите из себя целку, а на самом деле тиран на работе.
> а аутсорсеры думать не хотят, они работу сдают и идут пропивать деньги
Вы бы лучше их внутреннему оптимизатору запросов MySQL сказали…
А то что написал ,, аутсорсер,, считаю удовлетворительным.
Извините, но ваше решение отвратительно.
Чтобы выбрать 10 случайных записей, вы делаете 11 операций, которые в терминологии баз данных называются «full scan» (полное чтение таблицы)
SELECT COUNT(*) FROM tTable — раз фуллскан. база данных просматривает все записи, чтобы узнать их количество. (Да, база не получает данные строк, но сканировать начала записей ей надо.)
SELECT * FROM tTable LIMIT X, 1 — то же самое, еще 10 сканирований таблицы от начала до числа X.
Время работы вашего алгоритма прямо пропорционально количеству записей. Для больших баз данных (скажем, со 100 миллионами строк) он не пригоден. Для маленькой базы ваш алгоритм не намного лучше алгоритма бедного аутсорсера, зато содержит больше строк кода. Удивительно, почему нельзя сделать индекс с номером записи и отбирать по нему.
И еще — некрасиво публично называть кого-то глупым.
2) Параллельный процесс удаляет последнюю запись
3) Выборка не находит ничего.
Такая история ведь может произойти не только с самой последней записью — а если в промежутке удалили 10 записей? А если кто-то слелал КОММИТ (предположим, у них база транзакционная) и снес 1000 записей?
Алгоритм повторных попыткок — это алгоритм из серии WHILE TRUE. А такие алгоритмы — это всегда плохие алгоритмы, и не только теоритически.
Если удалений не много, можно использовать и первичный ключ.
Транзакция с блокировкой записи (а получается, что нужна такая) потребует этот процесс ждать, пока параллельные процессы закончат запись, чтобы захватить блокировку, а остальные процессы, которые хотят удалить или вставить строку, будут стоять в очереди, а кого-то вообще умирет по таймауту.
Производительность будет никакая.
Я куда клоню. Вы стоите в самом начале пути к довольно сложной вещи, как базы данных, а уже кого-то называете глупым.
Nulldevice: 27 октября 1978
ребеночек…
На собеседовании мы таким «software engineer», которые транзакций и блокировок не понимают, не говорим, что они глупые. Но на работу не берем.
собственно пока в системе мало пользователей — ORDER BY RAND() самое шоколадное. Но вот куда расти дальше — пока не ясно.
а) либо его раз в сутки (или чаще) забивать значением RAND()
б) в моем случае взяли crc32 важных данных записи, которое записывается при вставке и потом не меняется.
Во втором случае удобно — что перетрясать таблицу не нужно каждый день, а просто брать order by по этому полю.
потом, если у вас 50 одновременных запросов на случайную выборку — ничего страшного если все 50 юзеров увидят одни и те же случайные результаты? важно что результаты случайные, не важно какие именно, ведь так?
Добавляемые записи накапливайте в отдельный массив, и раз в N времени перестраивайте основной, добавляя или удаляя из него записи в зависимости от изменений.
Про ORDER BY RAND() рекомендую в данном случае забыть.
Но вообще, как я и писал в сообщениях ниже, всегда, если нужно выдавать случайное значение из миллионов вариантов, стоит задуматься, а действительно ли нужна тут случайность по бизнес-логике?
Но даже выборка ID из кэша — тот еще велосипед получается — нужна же не последовательность с которой они добавлены, а случайная. Разве нет?
Ну смотрите, допустим есть массив из N = 15000000 элементов.
1. получаете случайный индекс: i = rand(0, 15000000 — 1)
2. извлекаете id: id = cache[i], или id = getIdFromCache(i) в зависимости от реализации
То есть порядок элементов для случайной выборки не важен.
Откуда-то скопипастил. откуда не помню Но думаю как раз в тему будет. Есть над чем подумать!
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) Поймите, как работает оператор IN. Он не работает как «выбрать по индексу запись 1, выбрать по индексу запись 2, и т.д.» Он работает так — «просмотреть ВСЕ записи и проверить, не находятся ли они в списке IN» — то есть это fullscan — полное сканирование таблицы. Кстати, проверить, является ли ваш алгоритм полным чтением таблицы, можно и без вещей вроде разбора плана запроса. Просто замерьте время для 10000 записей, а потом для 100000. Если время одного порядка — то вы молодец, если время выросло в 10 раз — читается вся таблица и вы не молодец
2) ваш алгоритм не гарантирует 10 записей. если дважды выпадет одинаковый ключ — вы получите 9 записей. Теоретически, если экспериментировать порядка возраста Вселенной — можно вернуть вообще одну запись когда-нибудь :)
Попробуйте сделать подобный запрос для индексированной и неиндексированной колонок и посмотрите в explain
2) Это уже детали, чтобы проверить — Вы же мне не платите, чтобы я качественный код выдавал?
На всякий случай уточняю. Выборка одной записи:
$max_id = select id from table order by id desc; // max id, в такой записи не зависит от того оптимизирован ли max() в конкретной реализации
$rand_id = rand(1, $max_id)
select <нужные поля> from table where id >= $rand_id limit 1
для нескольких записей, думаю понятно.
$max_id = select id from table order by id desc limit 1
И что тут правильного?
Как будет выглядеть результат например на таблице с такими id: 1, 2, 3, 1000? Что мы будем видеть? В 997 из 1000 просмотров будет запись с id 1000. Это правильно? На мой взгляд тут вообще нету единого правильного решения. Все зависит от конкретной ситуация, от того, сколь в таблице строк, как часто удаляются эти строки, насколько важно, что бы строки выдавались с действительно равной вероятностью и т.п. Предложенное автором решение вполне рабочее и имеет право на жизнь, как выдаваемое вами за «правильное», но они не универсальны.
Если нужно выдерживать распределения, то для этого надо строить вспомогательные таблицы строить.
Если же пофиг на производительность — то и order by rand() покатит.
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. Пожалуйста, без обид, все мы учимся на ошибках, и я тоже могу ошибаться.
И ошибаетесь. Опять же без обид.
Приведенный вами выше алгоритм невозможно реализовать, используя возможности написания хранимых процедур в MySQL.
Моя ошибка в том, что я не обозначил рамки использования моего варианта решения в тексте топика, но оговорил их в одном из комментариев. Про проектирование и анализ/постановку задачи и говорить нечего — железное решение «правящих менеджеров», с которым не поспоришь.
Повторю на всякий случай условия и рамки: выборка абсолютно произвольна (вариант с выборкой по первичному ключу отпадает, с условием его разряженности), кол-во записей большое, но не гигантское (порядка 100 000 — 500 000 записей, и не зависит от масштабов и роста проекта), частота вставок и удалений записей относительно велика (порядка 20 записей в минуту, поддержка второго суррогатного не разряженного индекса неприемлема в виду частых обновлений). При обозначенных выше условиях мой вариант при тестировании показал наилучшие результаты. И если уж говорить о грамотности стороннего разработчика — то он изначально знал условия и особенности работы его кода, но все равно применил столь не производительный вариант (как потом выяснилось из личного общения — по незнанию).
ЗЫ: вы правы насчет кеширования результатов. В итоге пришлось применить (в обход требований менеджмента) кеширование произвольно выбранных записей, но на порядок большего кол-ва чем требуется. При каждом же выводе результатов происходит произвольная выборка нужного кол-ва записей из результатов первого (большого) набора.
Курсоры в MySQL работают прекрасно, хотя и имеют ряд ограничений (например, нет возможности пропускать записи, что сразу убивает идею использовать курсоры в полной мере для данной задачи) и относительно тормозные. Только что написал тест, извлечение 10 случайных записей из 500.000 с использованием курсоров обработка занимает 3.20 sec. Это конечно медленнее чем даже предложенный автором пример, но зато если, например, нужно было бы вернуть, скажем, 100 случайных, то это заняло бы примерно те же 3.20 sec, а другие примеры будут все более и более тормозить (пример
автора, скажем, выполняется более 10 секунд на моем наборе данных).
Создаем таблицу table1 с первичным ключом id, генерим 500.000 записей.
Делаем ряд запросов (тесты проводим с MyISAM и InnoDB без блокировок и транзакций), запускаем по 10 раз, вычисляем среднее и минимальное время.
1. Делаем запрос с ORDER BY RAND()
SELECT id FROM table1 ORDER BY RAND() LIMIT 10;
MyISAM 0.78 sec
InnoDB 1.20 sec
2. Делаем запрос, предложенный автором, с использованием UNION
(SELECT id FROM table1 LIMIT 10000, 1)
UNION
(SELECT id FROM table1 LIMIT 60000, 1)
UNION
(SELECT id FROM table1 LIMIT 110000, 1)
UNION
(SELECT id FROM table1 LIMIT 160000, 1)
UNION
(SELECT id FROM table1 LIMIT 210000, 1)
UNION
(SELECT id FROM table1 LIMIT 260000, 1)
UNION
(SELECT id FROM table1 LIMIT 310000, 1)
UNION
(SELECT id FROM table1 LIMIT 360000, 1)
UNION
(SELECT id FROM table1 LIMIT 410000, 1)
UNION
(SELECT id FROM table1 LIMIT 460000, 1);
MyISAM 1.31 sec
InnoDB 2.90 sec
3. Ускоряем предыдущий запрос, заменяем UNION на UNION ALL
MyISAM 1.31 sec
InnoDB 2.89 sec
4. Используем один запрос с WHERE id IN (...) вместо набора запросов, объединенных по UNION.
SELECT id FROM table1 WHERE id IN (10000, 60000, 110000, 160000, 210000, 260000, 310000, 360000, 410000, 460000);
MyISAM 0.00 sec (менее 10 миллисекунд)
InnoDB 0.01 sec
Выводы.
Способ N1 с ORDER BY RAND(), несмотря на то что он очень неэффективный, работает очень надежно, гарантированно выдавая результат независимо от состояния набора данных, в том числе и при условии конкурентного доступа.
Для способов N 2-4 придется или использовать а) предварительный запрос SELECT COUNT(...) и б) блокировки (для MyISAM) или транзакции с уровнем изоляции repeatable read или sirializable (для InnoDB) чтобы гарантировать корректный результат в условиях конкурентного доступа к данным. Если это делать, то время выполнения этих запросов можно увеличить еще примерно на 50%.
Использование набора запросов, объединенных через UNION, работает медленнее на больших наборах данных чем даже тормозной ORDER BY RAND().
UNION ALL позволяет выиграть несколько микросекунд, но проблему не решает.
Самый эффективный способ — вычислить заранее случайные значения первичных ключей, и делать запрос по ним. При этом нужно не забывать про описанный выше конкурентный доступ (записи могут быть удалены после того как выполнен запрос SELECT COUNT и вычислены значения ключей, но еще не выполнен основной запрос).
Предложенный автором способ действительно работает немного быстрее на
небольшом количестве записей, но в средних и больших таблицах (от 200-500 тыс. записей) уступает даже неэффективному ORDER BY RAND() если нужно получить несколько случайных значений, и чем больше значений нужно получить, тем более неэффективен этот метод.
Вот, почти что статья получилась ))
Как и подсказали выше (TimTowdy), если сужать исходный набор данных, используя например WHERE RAND() < 0.01, то ORDER BY RAND работает значительно быстрее. Например, на используемом мной в примере выше c 500.000 записями запрос
SELECT id FROM table1 WHERE RAND() < 0.1 ORDER BY RAND() LIMIT 10;
отрабатывает за 0.34 sec, почти в 2.5 раза быстрее.
или например
SELECT id FROM table1 WHERE id % 100 = 0 ORDER BY RAND() LIMIT 10;
отработает за 0.30 sec (в том случае 0 — это вычисленное ранее случайное значение из диапазона 0..99).
Но все равно это не хороший путь, я не спорю. Хотя и его нельзя отвергать в ряде случаев.
$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);Может кто-то подскажет способ лучше и производительнее?
Вроде шустро бегает.