Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 142
Rand выборка всегда интересная тема. Предположим мы имеем таблицу, которая хранит выборку с rss. RSS выгребается последовательно, 1 лента — 30-50 записей, всего rss лент 10. Задача получить 10 уникальных записей, которые были бы не от одной ленты.
Ну тут интересно получается :)
Вопрос возникает вот какой — т.к. в лентах сграбленных кол-во итемов различается, то при честной равномерной выборке из всех итемов всех лент — чаще будут встречаться итемы из тех лент, где наполнение больше.
Если нужно чтоб вхождения из всех лент в результирующем наборе были по кол-ву равны, то можно сначала составить рандомный список лент, а потом из каждой выбрать рандомную запись в результирующий набор. Получится 10 записей из 10 разных лент.
Вопрос возникает вот какой — т.к. в лентах сграбленных кол-во итемов различается, то при честной равномерной выборке из всех итемов всех лент — чаще будут встречаться итемы из тех лент, где наполнение больше.
Если нужно чтоб вхождения из всех лент в результирующем наборе были по кол-ву равны, то можно сначала составить рандомный список лент, а потом из каждой выбрать рандомную запись в результирующий набор. Получится 10 записей из 10 разных лент.
Ну ORDER BY rand() это конечно не есть очень хорошо, особенно если в таблице планируется действительно большое кол-во записей, но по-моему по одному UNION'у на ряд тоже не лучший выход.
Вообще выборка произвольных записей — ресурсоемкий процесс. По моим тестам решение с UNION самое производительное на MySQL. Если владеете вопросом на достаточном уровне и располагаете более производительным решением — прошу поделиться :)
Ну я исхожу из того, что проблему падения скорости выборки с LIMIT'ом на большом кол-ве записей я лично решаю введением доп. синтетического ключа, первичный ключ, как известно, должен быть неизменен, а вот у дополнительного поля с индексом на нем этого органичения нет, соот-но вполне реально проследить чтобы в доп.ключе записи были пронумерованы строго по порядку. Так вот в таком случае 10 случайных записей выбираются очень просто — генерацией 10 случайных чисел в диапазоне от 1 до максимального кол-ва записей, и SELECT * FROM table WHERE synth_key IN ($rand1, $rand2, $rand3…
Но вариант с доп.полем вообще конечно достаточно спорный, так как убыстряя выборку — он слегка замедляет добавление рядов (нужно сначала получить номер предыдущей записи) и сильно замедляет удаление (нужно перестроить всю нумерацию начиная с текущего номера, заканчивая последним).
Но вариант с доп.полем вообще конечно достаточно спорный, так как убыстряя выборку — он слегка замедляет добавление рядов (нужно сначала получить номер предыдущей записи) и сильно замедляет удаление (нужно перестроить всю нумерацию начиная с текущего номера, заканчивая последним).
Этот вариант я рассматривал, но отказался от него по обозначенным вами причинам.
Ну, перестраивать весь хвост при удалении не обязательно. Synth_key же не обязан поддерживать порядок нумерации.
То есть, UPDATE table SET synth_key=$synh_key_of_deleted_item WHERE synth_key=$max_synth_key
То есть, 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 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)
Насчет производительности не знаю.
Кстати не факт, что записи будут все уникальными, даже больше, они частенько будут повторятся, если строк в среднем 300-400.
Про это я написал. И даже сказал того как от этого избавиться Читайте внимательно.
2 раза читал, недосмотрел ;)
Жалко нет одного красивого решения этой проблемы.
Жалко нет одного красивого решения этой проблемы.
Представим что у нас 1 000 000 записей и rand_row у нас выпала где то в районе 900 000
то что нам сделает вот этот запрос?
SELECT * FROM tTable LIMIT rand_row, 1;
Правильно, выберет все 900 001 записей а потом возьмет одну последнею и так десять раз думаете это оптимальней.
то что нам сделает вот этот запрос?
SELECT * FROM tTable LIMIT rand_row, 1;
Правильно, выберет все 900 001 записей а потом возьмет одну последнею и так десять раз думаете это оптимальней.
Оптимальней чем сортировать временную таблицу с предварительной записью этой таблицы с новым значением.
Да и про LIMIT в mysql советую прочитать. Работает он очень быстро, поскольку строки MySQL попросту пропускает не записывая в результат.
Напомню, топик был мною написан о том, как я нашел более производительное решение чем ORDER BY RAND(). Я ни в коем случае не утверждаю что мой вариант сравнится по скорости с нативной UDF процедурой MySQL. Я лишь говорю что это наиболее производительный вариант с обозначенными мною условиями.
Да и про LIMIT в mysql советую прочитать. Работает он очень быстро, поскольку строки MySQL попросту пропускает не записывая в результат.
Напомню, топик был мною написан о том, как я нашел более производительное решение чем ORDER BY RAND(). Я ни в коем случае не утверждаю что мой вариант сравнится по скорости с нативной UDF процедурой MySQL. Я лишь говорю что это наиболее производительный вариант с обозначенными мною условиями.
Меня терзают смутные сомнения что будет с производительностью если все 10 попадут в конец. А также если надо больше записей при ORDER BY RAND() ведь вся выборка и сортировка делается один раз а в вашем варианте для каждой записи. Короче надо проверять. Но для малого количества записей все таки ваш вариант будет оптимальней.
А меня не терзают, я знаю на собственном горьком опыте :)
LIMIT $row, 1 UNION… работало до поры до времени при выборке 100 случайных из приблизительно 30К записей пока одним прекрасным днём не уложило на лопатки выделенный сервак :)
В общем, пришлось немного переделывать этот алгоритм в рамках конкретной задачи…
LIMIT $row, 1 UNION… работало до поры до времени при выборке 100 случайных из приблизительно 30К записей пока одним прекрасным днём не уложило на лопатки выделенный сервак :)
В общем, пришлось немного переделывать этот алгоритм в рамках конкретной задачи…
>Да и про LIMIT в mysql советую прочитать. Работает он очень быстро, поскольку строки MySQL попросту пропускает не записывая в результат.
Ваши бы слова, да в эту физическую реальность… :)
У меня на форуме всего 1,5млн. постингов. Уже после ~700 тыс. на топиках с десятками страниц записи вида SELECT * FROM posts WHERE topic_id =… ORDER BY… LIMIT 10000, 15; стали капитально вешать сервер. Не смотря на все оптимизации индексов и настроек :)
Вылечилось только введением отдельного поля «номер страницы».
MySQL очень болезненно относится к выборке последних значений в большой отсортированной таблице.
Ваши бы слова, да в эту физическую реальность… :)
У меня на форуме всего 1,5млн. постингов. Уже после ~700 тыс. на топиках с десятками страниц записи вида SELECT * FROM posts WHERE topic_id =… ORDER BY… LIMIT 10000, 15; стали капитально вешать сервер. Не смотря на все оптимизации индексов и настроек :)
Вылечилось только введением отдельного поля «номер страницы».
MySQL очень болезненно относится к выборке последних значений в большой отсортированной таблице.
Имхо, учитывая наличие в любой таблице такого размера Primary Key (который, как правило, int), проще получить набор случайных значений в PHP:
После чего единичная выборка делается мгновенно запросом вида:
А множественная делается в два запроса, где первый у нас выбирает этот самый count(*), а второй выглядит как:
Впрочем, если результат первого запроса выбирать, а не помещать в переменную, то перемножение и округление можно делать средствами всё того же PHP. Работать это будет еще быстрее, чем с LIMIT и UNION.
$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.
Так а если записи периодически удалялись? Тогда в стройной цепочке автоинкремента будут дыри, и если выборка попадет на них — единичная выборка не выберет вообще ничего, а множественная выберет неверное кол-во записей.
Тьфу, *дыры, насяльника :)
Нда, тогда будет облом. Как вариант, можно конечно выбирать заведомо больше, хотя тоже фигня получается. Можно попробовать организовать «довыборку» при помощи if и union всё в той же хранимой процедуре. Сдаётся мне, что это всё равно будет быстрее, чем выборка с LIMIT'ами. Хотя для не-интовых ключей номер не пройдёт в любом случае.
Как-то так:
Псевдокод:
random = randRange(min_id, max_id)
SQL:
SELECT * FROM table WHERE id <= random LIMIT 1
Псевдокод:
random = randRange(min_id, max_id)
SQL:
SELECT * FROM table WHERE id <= random LIMIT 1
В общем RTFM: dev.mysql.com/doc/refman/5.0/en/select.html
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;
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;
Задача была выбрать действительно произвольные записи и не одну, а несколько. Приведенный вами пример из комментариев к мануалу не годится, т.к. при наличии большой дырки в первичном ключе часто будет попадать в выборку запись стоящая после этой «дырки».
Хм, сделал табличку, загнал в нее около 12к записей, структура такая
|int pk|varchar(255) data|
выполняю:
И т.п., для первого варианта время выполнения всегда примерно 0,15 сек, для предложенного, каждый раз отличается. Более того, значение pk в выбранных строках не большие (в районе 100). Весь фокус в том, что запрос (SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table) выполняется для каждой записи… Не надо верить комментариям к мануалу, как самому мануалу ;)
P.S. mysql крутится на втором пне с частотой 450МГц, по тому для ощутимого времени выполнения большого кол-ва записей не понадобилось.
|int pk|varchar(255) data|
выполняю:
mysql> SELECT `pk`, substr(`data`,1,20) FROM `test` ORDER BY RAND() LIMIT 1; +------+----------------------+ | pk | substr(`data`,1,20) | +------+----------------------+ | 1612 | ark2K9bS0NeHWk34ZDAc | +------+----------------------+ 1 row in set (0.15 sec) mysql> SELECT `pk`, substr(`data`,1,20) FROM `test` WHERE `pk` >= (SELECT FLOOR( MAX(`pk`) * RAND()) FROM `test`) ORDER BY `pk` LIMIT 1; +----+----------------------+ | pk | substr(`data`,1,20) | +----+----------------------+ | 48 | juuZeRXMUJxyp1GV8EZM | +----+----------------------+ 1 row in set (1.52 sec) mysql> SELECT `pk`, substr(`data`,1,20) FROM `test` WHERE `pk` >= (SELECT FLOOR( MAX(`pk`) * RAND()) FROM `test`) ORDER BY `pk` LIMIT 1; +-----+----------------------+ | pk | substr(`data`,1,20) | +-----+----------------------+ | 173 | qQOA1ptDKU9qrJTDzf2j | +-----+----------------------+ 1 row in set (5.35 sec)
И т.п., для первого варианта время выполнения всегда примерно 0,15 сек, для предложенного, каждый раз отличается. Более того, значение pk в выбранных строках не большие (в районе 100). Весь фокус в том, что запрос (SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table) выполняется для каждой записи… Не надо верить комментариям к мануалу, как самому мануалу ;)
P.S. mysql крутится на втором пне с частотой 450МГц, по тому для ощутимого времени выполнения большого кол-ва записей не понадобилось.
А теперь выполните запрос с ORDER BY RAND() сразу в пятьдесят потоков. И посмотрите в список тредов сервера. Вы увидите как они в сильной задумчивости подвиснут в статусе 'Copying to tmp table'
А первый мой комментарий можно было прочитать? Я предложил сначала вычислить random, потом подставить его в запрос.
Потом вспомнил где я видел подобное решение и дал ссылку.
Потом вспомнил где я видел подобное решение и дал ссылку.
По ссылке просто кривое решение… Далее, использовать < или > без сортировки несколько опасно. Совершенно нет гарантии, что в таблице строки идут по порядку. Может получится так, что SELECT без ORDER BY выдаст в начале запись с id 1000, а потом с id 10… Т.е. теоретически используя этот способ запись с id 10 вообще никогда не будет получена. Хотя на практике с LIMIT 1 все работает вроде корректно, но вообще поведение не очень предсказуемо. Я из своей тестовой таблицы я удалил несколько строк расположенных вначале и добавил новую, в результате получил следующее:
Что в принципе соответствует моим ожиданиям. Но:
Что уже не очень соответствует ожиданиям, но без limit 10 запись 12876 выдается первой. Если добавить ORDER BY, сделав тем самым поведение полностью предсказуемым, то чем оно принципиально лучше предложенного автором LIMIT? Единственный плюс, используя этот подход можно обойтись одним SQL запросом с одним подзапросом.
mysql> select pk,substr(`data`,1,20) from test limit 10; +-------+----------------------+ | pk | substr(`data`,1,20) | +-------+----------------------+ | 1 | nA2XwySymLkX7Yy7RNUP | | 12876 | new | | 8 | MSWIuJzGdZl21DXSnA3l | | 9 | njcVy8iL7jtP5bGlCVwo | | 10 | zEf99WoNXZiSSQf9s8QS | | 11 | qgv8yyBsM3C2bImQ9YLs | | 12 | Y6jjudnvutqMardInwNy | | 13 | L4I30BhNBpYxyqNy0Lar | | 14 | a9nKmnPBGzxQs5kxjSpz | | 15 | BODaKjhmTziFTXjQXbrD | +-------+----------------------+ 10 rows in set (0.01 sec)
Что в принципе соответствует моим ожиданиям. Но:
mysql> select pk,substr(`data`,1,20) from test where pk >= 9 limit 10; +----+----------------------+ | pk | substr(`data`,1,20) | +----+----------------------+ | 9 | njcVy8iL7jtP5bGlCVwo | | 10 | zEf99WoNXZiSSQf9s8QS | | 11 | qgv8yyBsM3C2bImQ9YLs | | 12 | Y6jjudnvutqMardInwNy | | 13 | L4I30BhNBpYxyqNy0Lar | | 14 | a9nKmnPBGzxQs5kxjSpz | | 15 | BODaKjhmTziFTXjQXbrD | | 16 | 6pKnrXAW431nKEzezPTM | | 17 | UuLA1t9NMopa5b2ZvOqT | | 18 | M7YxrpwekRcwTjYQHb3f | +----+----------------------+ 10 rows in set (0.00 sec)
Что уже не очень соответствует ожиданиям, но без limit 10 запись 12876 выдается первой. Если добавить ORDER BY, сделав тем самым поведение полностью предсказуемым, то чем оно принципиально лучше предложенного автором LIMIT? Единственный плюс, используя этот подход можно обойтись одним SQL запросом с одним подзапросом.
Хотя нет, одним запросом с подзапросом вроде не обойтись, подзапрос все равно будет выполнятся для каждой записи. Тогда получается то же, что и с «LIMIT rand_row, 1», либо хранимая процедура, либо доп. код на каком-нибудь языке. Но предложенный тут вариант выдает более качественный результат.
Вот вам другой пример запроса, на этот раз я его проверил на работоспособность:
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 выберет только одну строку, но на самом деле он сначала извлекает тысячу строк, а потом из этой тысячи берет одну. А если в таблице будет не тысяча строк, а намного больше — опять начнутся тормоза.
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 лимитов…
Чем строить 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'
SELECT * FROM table1 WHERE tid> (rand()*(SELECT count(*) FROM table1) ) LIMIT 1
хотя можно подзапрос другой юзать-не помню что быстрее, вроде одинаково почти получалось
SELECT TABLE_ROWS FROM information_schema.`TABLES` WHERE `TABLE_NAME`='table1'
забыл про floor вокруг рандома :(
SELECT * FROM table1 WHERE tid> (rand()*(SELECT count(*) FROM table1) ) LIMIT 1
Если удалим любые сто записей, то последняя сотня не попадет в выборку никогда.
Если удалим любые сто записей, то последняя сотня не попадет в выборку никогда.
Я чуть выше писал, вот эта часть «WHERE tid> (rand()*(SELECT count(*) FROM table1) )» приведет к тому, что «rand()*(SELECT count(*) FROM table1)» будет по новой вычисляться для сравнения с каждой строкой. Т.е. во-первых будет выполнено множество лишних подзапросов, а во-вторых новый rand для каждой строки… вероятность выпадения последний записи равна нулю :) даже вряд ли получится дотянуть до середины таблицы, все время будут выпадать записи из начала таблицы.
Ребята, я написал такую функцию выбора трех текстовых объявлений случайным образом.
Хочу привести часть кода для поддержания дискуссии.
Какие минусы моего подхода?
$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";
}
}
}
}
}
Хочу привести часть кода для поддержания дискуссии.
Какие минусы моего подхода?
$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, а только потом отправлять его в БД.
1) Вы делаете выборку всех идентификаторов. Если таких много — это лишняя нагрузка как на БД так и на интерпретатор PHP
2) Вы делаете по одному запросу на каждую результирующую запись. Для БД это лишняя работа по инициализации запроса и блокировке таблиц.
3) У вас часто идет лишнее присваивание промежуточным переменным. Не забывайте что PHP присваивает по значению, а значит происходит лишнее копирование участков памяти. Если текст в выборке большой, а сам этот код вызывается часто — то это довольно много лишней работы.
Если вы хотите придерживаться вашего алгоритма с выборкой ключей и выбора по ним произвольных значений — то как минимум нужно сформировать составной запрос через UNION, а только потом отправлять его в БД.
Хорошо что я обновил список комментариев перед отправкой, а то у меня почти те же указания. Только в данном случае эффективней юзать не UNION, а IN — слепить нужные id в список и сравнивать с ним:
WHERE reclama_id IN (....)
WHERE reclama_id IN (....)
Ой да… С IN лучше… Устал под вечер, сглупил :)
Спасибо! Вот этот момент-то как раз мне и не понятен!
Как составить запрос с IN(...)
Про промежуточные переменные: вы имеете в виду вместо
$reclama_code = $row[«reclama_code»];
echo $reclama_code;
Использовать
echo «тэги тэги». $row[«reclama_code»] .«тэги тэги\n»;
Как составить запрос с IN(...)
Про промежуточные переменные: вы имеете в виду вместо
$reclama_code = $row[«reclama_code»];
echo $reclama_code;
Использовать
echo «тэги тэги». $row[«reclama_code»] .«тэги тэги\n»;
Да. Бессмысленно делать присвоение $reclama_code, если $row[«reclama_code»] итак достаточно понятное название.
Запрос с IN составляется таким образом:
Таким образом, к базе делается только один запрос.
Запрос с 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
Выдает ошибку 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 )»
И делаем все проще:
$rand_keys = array_rand( array_flip( $data ), $z );
Вот это удаляем вообще:
for ( $i=0; $i<$z; $i++ ) {
$value .= $data[$rand_keys[$i]]. ',';
}
А вот это: «rtrim($value, ',')» заменяем на: «join( ',', $rand_keys )»
Как-то для одного проекта стояла подобная задача. Решили похожим способом, только выбирали одним запросом подряд N записей начиная со случайной. При этом перемешивали таблицу несколько раз в день по крону командой
ALTER TABLE tab ORDER BY RAND()
ALTER TABLE tab ORDER BY RAND()
А вот в статье предлагается несколько решений и проводится сравнительный анализ. Мне приглянулась идея создать отдельную табличку из двух полей — нашего id (из оригинальной таблицы) и случайного номера без дырок. Заполнением и актуализацией этой дополнительной таблички занимаются триггеры.
Но если у вас операции удаления записей частые, триггеры замучаются апдейтить дополнительную табличку. Можно, конечно, пойти чуть дальше и только помечать удаленные записи, а потом раз в N минут проходить сборщиком мусора и удалять все разом.
Кстати, в вашем решении я вижу одну досадную ошибку: вычисление count(*) записей и собственно их выборку надо брать в одну транзакцию. Иначе можно попытаться выбрать строку, которой уже нет.
И не ругайте аутсорсера. Он сделал правильно по букварю. Для оптимизации же надо хорошо знать свои данные, что тяжело требовать от человека со стороны.
Но если у вас операции удаления записей частые, триггеры замучаются апдейтить дополнительную табличку. Можно, конечно, пойти чуть дальше и только помечать удаленные записи, а потом раз в N минут проходить сборщиком мусора и удалять все разом.
Кстати, в вашем решении я вижу одну досадную ошибку: вычисление count(*) записей и собственно их выборку надо брать в одну транзакцию. Иначе можно попытаться выбрать строку, которой уже нет.
И не ругайте аутсорсера. Он сделал правильно по букварю. Для оптимизации же надо хорошо знать свои данные, что тяжело требовать от человека со стороны.
Кстати можно сделать забавную «оптимизацию» запроса который написал ваш аутсорсер:
SELECT * FROM tTable WHERE RAND()
SELECT * FROM tTable WHERE RAND()
SELECT * FROM tTable WHERE RAND()<0.001 ORDER BY RAND() LIMIT 10
Потеряем время на вычисление и сравнение лишних RAND(), зато на несколько порядков уменьшим размер таблицы, которая сортируется в памяти. Естественно можно брать не 0.001, а подбирать какое-то другое значение, в зависимости от размера таблицы.
Потеряем время на вычисление и сравнение лишних RAND(), зато на несколько порядков уменьшим размер таблицы, которая сортируется в памяти. Естественно можно брать не 0.001, а подбирать какое-то другое значение, в зависимости от размера таблицы.
Кстати, отличное решение, хоть и частичное. Спасибо.
На моем примере, выборка 10 из 500.000, позволяет ускорить примерно в 3 раза. Забавно, что если вместо коэффициента 0.001 выбрать например 0.1 или 0.000001, но время выборки не изменяется вообще, по крайней мере на моих тестах.
На моем примере, выборка 10 из 500.000, позволяет ускорить примерно в 3 раза. Забавно, что если вместо коэффициента 0.001 выбрать например 0.1 или 0.000001, но время выборки не изменяется вообще, по крайней мере на моих тестах.
Ну если уж уменьшать размер таблицы в памяти…
пришел в голову такой вариант:
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.
как думаете?
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го?
Вы статью написали дабы унизить одного человека и показать свое превосходство? Если нет, то странно, знаете ли. Все эти ругательства, нежелание признавать неэффективность вашего метода. У меня такое чувство, что вы один из тех работодателей-дебилов, которые орут на своих работников.
Вы хам и неадекватны.
Я хам? Наверное я написал топик чтобы унизить конкретного человека? Признайтесь, опере на подчиненных?
Это Вы неадекват.
Человек говорит правильно. Ибо показав свою «умность» Вы лишь принизили себя в глазах спецов, ибо полнейшая глупость, работающая немногим быстрее стандартного RAND().
Ничего другого не признаете.
Уверен процентов на 99%, что разработчику в ТЗ не ставилось задачи сделать High-Load решения.
Человек говорит правильно. Ибо показав свою «умность» Вы лишь принизили себя в глазах спецов, ибо полнейшая глупость, работающая немногим быстрее стандартного RAND().
Ничего другого не признаете.
Уверен процентов на 99%, что разработчику в ТЗ не ставилось задачи сделать High-Load решения.
У вас столько желчи к аутсорсерам, а сами небось предложили неадекватную оплату типа «написать движок за 300 руб» и ожидали получить сертифицированного профессионала
>станет понятно почему я сложил дюжину матершинных ругательств в адрес глупого аутсорсера
Вы бы лучше их внутреннему оптимизатору запросов MySQL сказали…
Вы бы лучше их внутреннему оптимизатору запросов MySQL сказали…
Это очевидно. Как говорил кое-кто, даже недоразвитый восьмиклассник догадается.
Простите конечно, но за запросы в цикле => Нужно сразу отрывать руки)))
А то что написал ,, аутсорсер,, считаю удовлетворительным.
А то что написал ,, аутсорсер,, считаю удовлетворительным.
Здравствуйте.
Извините, но ваше решение отвратительно.
Чтобы выбрать 10 случайных записей, вы делаете 11 операций, которые в терминологии баз данных называются «full scan» (полное чтение таблицы)
SELECT COUNT(*) FROM tTable — раз фуллскан. база данных просматривает все записи, чтобы узнать их количество. (Да, база не получает данные строк, но сканировать начала записей ей надо.)
SELECT * FROM tTable LIMIT X, 1 — то же самое, еще 10 сканирований таблицы от начала до числа X.
Время работы вашего алгоритма прямо пропорционально количеству записей. Для больших баз данных (скажем, со 100 миллионами строк) он не пригоден. Для маленькой базы ваш алгоритм не намного лучше алгоритма бедного аутсорсера, зато содержит больше строк кода. Удивительно, почему нельзя сделать индекс с номером записи и отбирать по нему.
И еще — некрасиво публично называть кого-то глупым.
Извините, но ваше решение отвратительно.
Чтобы выбрать 10 случайных записей, вы делаете 11 операций, которые в терминологии баз данных называются «full scan» (полное чтение таблицы)
SELECT COUNT(*) FROM tTable — раз фуллскан. база данных просматривает все записи, чтобы узнать их количество. (Да, база не получает данные строк, но сканировать начала записей ей надо.)
SELECT * FROM tTable LIMIT X, 1 — то же самое, еще 10 сканирований таблицы от начала до числа X.
Время работы вашего алгоритма прямо пропорционально количеству записей. Для больших баз данных (скажем, со 100 миллионами строк) он не пригоден. Для маленькой базы ваш алгоритм не намного лучше алгоритма бедного аутсорсера, зато содержит больше строк кода. Удивительно, почему нельзя сделать индекс с номером записи и отбирать по нему.
И еще — некрасиво публично называть кого-то глупым.
Во-первых SELECT COUNT(*) FROM tTable не сканирует записи таблицы. Оптимизатор в этом случае выдает кол-во строк из метаданных таблицы (читайте мануал). Во-вторых я четко обозначил условия когда меня об этом спросили в одном из комментариев — записи часто удаляются и часто вставляются, поэтому поддержка дополнительного индекса неприемлема. Ну и в-третьих, выборка типа LIMIT X, 1 оптимизируется MySQL очень хорошо — идет не ресурсоемкий спуск по индексу (если в таблице есть хоть один индекс/ключ). Опять же, погуглите.
1) Рандомайзер выбирает последнюю запись
2) Параллельный процесс удаляет последнюю запись
3) Выборка не находит ничего.
2) Параллельный процесс удаляет последнюю запись
3) Выборка не находит ничего.
С этим можно смириться и посчитать маловероятным. А можно легко исключить эту ситуацию выполняя запрос кол-ва записей и выборку произвольных в одну транзакцию.
О-ло-лол. У вас же только что был был высоко нагруженный проект с частыми вставками и удалениями? Хотя вы правы в том, что обойти такую ситуацию проще простого.
А как такую ситуацию обойти проще простого, кроме как сделать повторную попытку?
Такая история ведь может произойти не только с самой последней записью — а если в промежутке удалили 10 записей? А если кто-то слелал КОММИТ (предположим, у них база транзакционная) и снес 1000 записей?
Алгоритм повторных попыткок — это алгоритм из серии WHILE TRUE. А такие алгоритмы — это всегда плохие алгоритмы, и не только теоритически.
Такая история ведь может произойти не только с самой последней записью — а если в промежутке удалили 10 записей? А если кто-то слелал КОММИТ (предположим, у них база транзакционная) и снес 1000 записей?
Алгоритм повторных попыткок — это алгоритм из серии WHILE TRUE. А такие алгоритмы — это всегда плохие алгоритмы, и не только теоритически.
И еще. Если то, что запись не будет найдена с первой попытки — это не страшно, то почему бы не воспользоваться нумерацией и поиском по ключу? Ну, не найдет запись с первой попытки — найдет со второй. Зато поиск по индексу — ну ооочень быстро.
Вот, вы и попались!
Транзакция с блокировкой записи (а получается, что нужна такая) потребует этот процесс ждать, пока параллельные процессы закончат запись, чтобы захватить блокировку, а остальные процессы, которые хотят удалить или вставить строку, будут стоять в очереди, а кого-то вообще умирет по таймауту.
Производительность будет никакая.
Я куда клоню. Вы стоите в самом начале пути к довольно сложной вещи, как базы данных, а уже кого-то называете глупым.
Транзакция с блокировкой записи (а получается, что нужна такая) потребует этот процесс ждать, пока параллельные процессы закончат запись, чтобы захватить блокировку, а остальные процессы, которые хотят удалить или вставить строку, будут стоять в очереди, а кого-то вообще умирет по таймауту.
Производительность будет никакая.
Я куда клоню. Вы стоите в самом начале пути к довольно сложной вещи, как базы данных, а уже кого-то называете глупым.
Ясно, вы еще один представитель множества детей, которым надо во что бы то ни стало поспорить, причем поспорить не о решении проблемы а том что вы умнее всех. Ваши высокомерные комментарии со снисходительными упреками смешны и глупы. Растите над собой, премудрый вы наш :)
А насчет транзакции с блокировкой — читайте мануалы. Остальные треды не заблокируются, просто результат их работы сначала попадет в лог, а потом уже актуализируется после снятия блока.
Поскольку я старший администратор баз данных, то мне чтение мануалов очень часто заменяют письма разгневанных пользователей (а иногда и сами пользователи очно) со словами «Почему все висит?». Вот такие вот «разработчики» вычитают в мануалах, что, оказывается, «при транзакции параллельные процессы и не ждут вовсе, а делают по-быстрому какие-то результаты работы». Не объяснишь ведь менеджерам, что я тут не причем.
На собеседовании мы таким «software engineer», которые транзакций и блокировок не понимают, не говорим, что они глупые. Но на работу не берем.
На собеседовании мы таким «software engineer», которые транзакций и блокировок не понимают, не говорим, что они глупые. Но на работу не берем.
15 000 000 записей в таблице, по 3-8к ежечасно новых, 10-15к в сутки удаляется. Выборки от 10шт до 3к.
собственно пока в системе мало пользователей — ORDER BY RAND() самое шоколадное. Но вот куда расти дальше — пока не ясно.
собственно пока в системе мало пользователей — ORDER BY RAND() самое шоколадное. Но вот куда расти дальше — пока не ясно.
дальше — кеширование? :)
оно тут бессмысленно. кто кэшировать — результаты? они должны быть случайны в каждой выборке…
а ведь действительно, если не нужна совсем настоящая случайная последовательность, то можно и сделать дополнительное поле:
а) либо его раз в сутки (или чаще) забивать значением RAND()
б) в моем случае взяли crc32 важных данных записи, которое записывается при вставке и потом не меняется.
Во втором случае удобно — что перетрясать таблицу не нужно каждый день, а просто брать order by по этому полю.
а) либо его раз в сутки (или чаще) забивать значением RAND()
б) в моем случае взяли crc32 важных данных записи, которое записывается при вставке и потом не меняется.
Во втором случае удобно — что перетрясать таблицу не нужно каждый день, а просто брать order by по этому полю.
как уже писали ниже — вы можете держать в памяти таблицу и из нее делать выборку, хоть всех данных, хоть одних primary keys
потом, если у вас 50 одновременных запросов на случайную выборку — ничего страшного если все 50 юзеров увидят одни и те же случайные результаты? важно что результаты случайные, не важно какие именно, ведь так?
потом, если у вас 50 одновременных запросов на случайную выборку — ничего страшного если все 50 юзеров увидят одни и те же случайные результаты? важно что результаты случайные, не важно какие именно, ведь так?
А почему бы и не кешировать результаты? Если конечно это не жесткое требование «должны быть случайны в каждой выборке». Скажем, имеете 15 млн записей, даже если в памяти у Вас в кеше хотя бы несколько тысяч — этого уже достаточно чтобы пользователю устроить случайный хоровод из этих значений. А кеш можно время от времени обновлять.
Дальше — храните id всех этих миллионов записей в кеше в памяти, 15 млн id — это 60 Мб массив. Когда нужно выдать набор случайных — извлекайте id из кеша, и делайте например запрос вида SELECT… WHERE id IN (..., ..., ...). Будет работать практически мгновенно.
Добавляемые записи накапливайте в отдельный массив, и раз в N времени перестраивайте основной, добавляя или удаляя из него записи в зависимости от изменений.
Про ORDER BY RAND() рекомендую в данном случае забыть.
Но вообще, как я и писал в сообщениях ниже, всегда, если нужно выдавать случайное значение из миллионов вариантов, стоит задуматься, а действительно ли нужна тут случайность по бизнес-логике?
Добавляемые записи накапливайте в отдельный массив, и раз в N времени перестраивайте основной, добавляя или удаляя из него записи в зависимости от изменений.
Про ORDER BY RAND() рекомендую в данном случае забыть.
Но вообще, как я и писал в сообщениях ниже, всегда, если нужно выдавать случайное значение из миллионов вариантов, стоит задуматься, а действительно ли нужна тут случайность по бизнес-логике?
да, в принципе кэш Primary Key таблицы тоже вариант. И выше вариант с частью этих ключей — вполне нормальная выборка для небольшой группы.
Но даже выборка ID из кэша — тот еще велосипед получается — нужна же не последовательность с которой они добавлены, а случайная. Разве нет?
Но даже выборка ID из кэша — тот еще велосипед получается — нужна же не последовательность с которой они добавлены, а случайная. Разве нет?
Нет, случайная последовательность не нужна, в порядке добавления — вполне достаточно. Нужно только знать количество элементов этого массива, и сгенерировать нужное количество случайных индексов (от 0 до N-1). Затем по этим индексам извлечь id из массива, и уже затем по этим id извлечь записи из базы данных.
Ну смотрите, допустим есть массив из N = 15000000 элементов.
1. получаете случайный индекс: i = rand(0, 15000000 — 1)
2. извлекаете id: id = cache[i], или id = getIdFromCache(i) в зависимости от реализации
То есть порядок элементов для случайной выборки не важен.
Ну смотрите, допустим есть массив из N = 15000000 элементов.
1. получаете случайный индекс: i = rand(0, 15000000 — 1)
2. извлекаете id: id = cache[i], или id = getIdFromCache(i) в зависимости от реализации
То есть порядок элементов для случайной выборки не важен.
В конце дня лучшими программистами оказываются не те, кто написал самый потрясающе-элегантный, умопомрачительно-инновационный код. Настоящие рок-звёзды сдают проекты до намеченного срока и дешевле отведённого бюджета, и это именно то, в чём нуждается бизнес. И это именно те, кем все мы должны стремиться стать.
Откуда-то скопипастил. откуда не помню Но думаю как раз в тему будет. Есть над чем подумать!
Откуда-то скопипастил. откуда не помню Но думаю как раз в тему будет. Есть над чем подумать!
# Чистый 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). ')';
// И т.д.
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 записей. Теоретически, если экспериментировать порядка возраста Вселенной — можно вернуть вообще одну запись когда-нибудь :)
1) Поймите, как работает оператор IN. Он не работает как «выбрать по индексу запись 1, выбрать по индексу запись 2, и т.д.» Он работает так — «просмотреть ВСЕ записи и проверить, не находятся ли они в списке IN» — то есть это fullscan — полное сканирование таблицы. Кстати, проверить, является ли ваш алгоритм полным чтением таблицы, можно и без вещей вроде разбора плана запроса. Просто замерьте время для 10000 записей, а потом для 100000. Если время одного порядка — то вы молодец, если время выросло в 10 раз — читается вся таблица и вы не молодец
2) ваш алгоритм не гарантирует 10 записей. если дважды выпадет одинаковый ключ — вы получите 9 записей. Теоретически, если экспериментировать порядка возраста Вселенной — можно вернуть вообще одну запись когда-нибудь :)
1) Я в курсе, как он работает во многих типах СУБД. Если запрос идет по колонке, которая в индексе, например, PK, то он смотрит в индекс. А если колонка не проиндексирована, то только тогда идет полный просмотр.
Попробуйте сделать подобный запрос для индексированной и неиндексированной колонок и посмотрите в explain
2) Это уже детали, чтобы проверить — Вы же мне не платите, чтобы я качественный код выдавал?
Попробуйте сделать подобный запрос для индексированной и неиндексированной колонок и посмотрите в explain
2) Это уже детали, чтобы проверить — Вы же мне не платите, чтобы я качественный код выдавал?
WhiteD, добавьте чтоли апдейт в статью, не все комментарии читают. В комментариях неоднократно указали как правильно.
На всякий случай уточняю. Выборка одной записи:
$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; // 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
$max_id = select id from table order by id desc limit 1
В комментариях неоднократно указали как правильно.
И что тут правильного?
Как будет выглядеть результат например на таблице с такими id: 1, 2, 3, 1000? Что мы будем видеть? В 997 из 1000 просмотров будет запись с id 1000. Это правильно? На мой взгляд тут вообще нету единого правильного решения. Все зависит от конкретной ситуация, от того, сколь в таблице строк, как часто удаляются эти строки, насколько важно, что бы строки выдавались с действительно равной вероятностью и т.п. Предложенное автором решение вполне рабочее и имеет право на жизнь, как выдаваемое вами за «правильное», но они не универсальны.
Про универсальность никто и не говорил. Как вы правильно подметили — все решения имеют право на жизнь и некоторые из них вполне применимы при определенных условиях. В тексте топика я сделал ошибку и не выписал в отдельный параграф всех поставленных передо мной условий. В комментарии habrahabr.ru/blogs/mysql/54176/#comment_1453063 я резюмировал все что упустил в топике. Но ничего не поделаешь, я уже дал пищу для хабровских троллей и они уже с пеной у рта начали кричать «все дураки, один я умный». Увы и ах…
Правильное с точки зрения производительности, если нужно «показать что-то от балды». Именно для этого начинающие используют order by rand().
Если нужно выдерживать распределения, то для этого надо строить вспомогательные таблицы строить.
Если же пофиг на производительность — то и order by rand() покатит.
Если нужно выдерживать распределения, то для этого надо строить вспомогательные таблицы строить.
Если же пофиг на производительность — то и order by rand() покатит.
Сталкивался с подобной проблемой. Применил следующее:
1. Ввёл в таблицу дополнительный столбей int(11)
2. Раз в сутки этот столбец обновляется и в него пишутся значения rand
3. Выборка идёт примерно следующая: SELECT * FROM table WHERE rand_field > RAND() LIMIT 10
Таким образом функция 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. Пожалуйста, без обид, все мы учимся на ошибках, и я тоже могу ошибаться.
Остальные вышеуказанные решения или ненадежны, как было указано выше, или еще более неэффективны с точки зрения производительности, например требуют блокировок или транзакций с уровнем изоляции минимум 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.
Моя ошибка в том, что я не обозначил рамки использования моего варианта решения в тексте топика, но оговорил их в одном из комментариев. Про проектирование и анализ/постановку задачи и говорить нечего — железное решение «правящих менеджеров», с которым не поспоришь.
Повторю на всякий случай условия и рамки: выборка абсолютно произвольна (вариант с выборкой по первичному ключу отпадает, с условием его разряженности), кол-во записей большое, но не гигантское (порядка 100 000 — 500 000 записей, и не зависит от масштабов и роста проекта), частота вставок и удалений записей относительно велика (порядка 20 записей в минуту, поддержка второго суррогатного не разряженного индекса неприемлема в виду частых обновлений). При обозначенных выше условиях мой вариант при тестировании показал наилучшие результаты. И если уж говорить о грамотности стороннего разработчика — то он изначально знал условия и особенности работы его кода, но все равно применил столь не производительный вариант (как потом выяснилось из личного общения — по незнанию).
ЗЫ: вы правы насчет кеширования результатов. В итоге пришлось применить (в обход требований менеджмента) кеширование произвольно выбранных записей, но на порядок большего кол-ва чем требуется. При каждом же выводе результатов происходит произвольная выборка нужного кол-ва записей из результатов первого (большого) набора.
Вы уверены, что это невозможно реализовать? Я только что попробовал, успешно. MySQL 5.1.30.
Курсоры в MySQL работают прекрасно, хотя и имеют ряд ограничений (например, нет возможности пропускать записи, что сразу убивает идею использовать курсоры в полной мере для данной задачи) и относительно тормозные. Только что написал тест, извлечение 10 случайных записей из 500.000 с использованием курсоров обработка занимает 3.20 sec. Это конечно медленнее чем даже предложенный автором пример, но зато если, например, нужно было бы вернуть, скажем, 100 случайных, то это заняло бы примерно те же 3.20 sec, а другие примеры будут все более и более тормозить (пример
автора, скажем, выполняется более 10 секунд на моем наборе данных).
Курсоры в MySQL работают прекрасно, хотя и имеют ряд ограничений (например, нет возможности пропускать записи, что сразу убивает идею использовать курсоры в полной мере для данной задачи) и относительно тормозные. Только что написал тест, извлечение 10 случайных записей из 500.000 с использованием курсоров обработка занимает 3.20 sec. Это конечно медленнее чем даже предложенный автором пример, но зато если, например, нужно было бы вернуть, скажем, 100 случайных, то это заняло бы примерно те же 3.20 sec, а другие примеры будут все более и более тормозить (пример
автора, скажем, выполняется более 10 секунд на моем наборе данных).
Лабораторная работа N1, случайные выборки из таблицы.
Создаем таблицу 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() если нужно получить несколько случайных значений, и чем больше значений нужно получить, тем более неэффективен этот метод.
Вот, почти что статья получилась ))
Создаем таблицу 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() если нужно получить несколько случайных значений, и чем больше значений нужно получить, тем более неэффективен этот метод.
Вот, почти что статья получилась ))
Дополнение к лабораторной работе N1.
Как и подсказали выше (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).
Но все равно это не хороший путь, я не спорю. Хотя и его нельзя отвергать в ряде случаев.
Как и подсказали выше (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).
Но все равно это не хороший путь, я не спорю. Хотя и его нельзя отвергать в ряде случаев.
order by rand() это temporary table + filesort соответственно есть резкий переход от использования памяти к диску, который зависит от размера буферов. четко предсказать когда этот переход произойдет невозможно, разве что провести эксперимент для конкретного запроса с конкретными настройками и конкретными данными
У меня была аналогичная задача, только надо было не случайно выбирать линии, а последовательно. При этом выборки должны быть закольцованы, а в последовательности ключей регулярно появляются «дыры». Решил примерно вот так:
Может кто-то подскажет способ лучше и производительнее?
$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.
Если записей действительно миллион, и выбирать надо быстро, я бы сделал так: завёл бы поле, куда помещал бы случайные значения при вставке. При нормальном распределении случайных чисел и большом количестве записей можно получать выборку с запасом, и затем ограничивать её с помощью LIMIT.
«Глупый и ленивый аутсорсер» решает поставленную задачу. Максимально простым для него способом. Поскольку его время — его деньги, а за «проявление смекалки» его никто никогда не поблагодарит. Может быть «умный и работящий постановщик» поленился расписать требования и поставил задачу неправильно?
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Выборка произвольных записей в MySQL