Случайная выборка mysql - Форум успешных вебмастеров

rushter · 30.09.2011, 00:17

Кто-нибудь знает как можно оптимизировать получение случайных записей в mysql ?

Цитата:

SELECT * FROM `ai` ORDER BY RAND() LIMIT 0,10;

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

Юнга · 30.09.2011, 00:25

делал для MSSQL, но суть одинакова - если есть индексное поле упорядоченное, от 1 до 15.000.000 - рандомно сгенерить любых чисел в этом промежутке, и потом просто по индексу вытащить.
если есть разрывы - то с запасом сделать список.

Юнга · 30.09.2011, 00:29

второй вариант - случайный сдвиг (SKIP) туда-сюда по базе.

Revolver · 30.09.2011, 01:02

Рекомендую для очень быстрого поиска по MySQL с любым набором параметров использовать Sphinx. Очень шустрая технология.

Drunk Monk · 30.09.2011, 01:11

Или шафли пыхой результат

dealer · 30.09.2011, 01:21

ебанулся я. потерто.

dveredel · 30.09.2011, 01:27

Мда, конечно советовать Сфинкс для решения классической задачи "оптимизация рандомной выборки из огромной таблицы по айди", это либо непонимание задачи, либо сфинкса )

Можно сделать не очень красиво но все по честному, быстро и только мускулом -- т.е. сразу в одном запросе, а не в цикле кучу запросов из кода посылать.
Только вот запрос придется собирать скриптом в количестве равном требуемым строкам (ибо лимит там уже не поставишь).

Соотв выборка идет по числовому\автоинкрементному индексному (без индекса даже не думать) полю с поддержкой "разрывов" в айдишнике:

Код:

SET @max:=(SELECT MAX(id) FROM table_name);

(SELECT * FROM table_name
WHERE id >= ROUND(@max*RAND()) ORDER BY id LIMIT 1)
UNION
(SELECT * FROM table_name
WHERE id >= ROUND(@max*RAND()) ORDER BY id LIMIT 1)
UNION
(SELECT * FROM table_name
WHERE id >= ROUND(@max*RAND()) ORDER BY id LIMIT 1)
/* ... Тут повторяем всю байду столько раз сколько в итоге строк надо,
 в конце юнион уже не нужен если чо ... */ 
ORDER BY id

NTллигент · 30.09.2011, 02:17

Цитата:

Сообщение от rushter

Кто-нибудь знает как можно оптимизировать получение случайных записей в mysql ?

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

Тоже с таким сталкивался. Решилось тем, что нужное количество айдишников генерилось до запроса к БД, например в пыхе. А дальше уже
SELECT * FROM `ai`WHERE id IN (randomID1, randomID2, randomID3...)

chesser · 30.09.2011, 03:17

так это классическая задача

ORDER BY RAND() - при большом кол-ве данных это пиздец, да. На мелких пох.

Основная идея такая: нет ничего быстрее чем SELECT id FROM tbl WHERE id=100500, при условии, что id уник кей
если нужно несколько записей, то: ....WHERE id IN (100500, 500100, 1, 5, 100, 500)

Придумываем такой алгоритм:
- вводится уникальный числовой индекс, лучше primary key
- генерируется случайное число в диапазоне этого индекса и делает WHERE id=$randnum или WHERE id IN ()

генерация рандома может быть как на стороне сервера(mysql), так и на стороне клиента (php). Вариант с сервером более правильный.

Но будут проблемы: при частом обновлении таблицы возникнут проблемы из-за дыр в индексе. Их можно обойти разными способами:
можно заранее пытаться выбрать записей больше чем нужно, и если каких-то сгенерированных индексов не будет - то ничего страшно, раз все равно выбираем с запасом
можно выбирать впритык, и если не хватило, то добирать(делать sql запрос повторно, что не очень). Такой вариант у меня шпарит 120к/с на 100кк записей, но у меня прореженность индекса почти нулевая.

есть еще вариант с LIMIT 1, описанный dveredel - там фишка в том, что не надо точно подбирать id, но и расход памяти больше. Меня он не устроил в свое время, в том числе из-за невозможности использовать хеш-индексы. А они быстрее обычных b-tree

Вот тут еще толково описано:
http://sqlinfo.ru/forum/viewtopic.php?pid=11355#p11355

chesser · 30.09.2011, 08:16

еще добавлю

когда придешь к решению на индексе и если у тебя там не хеш-индексы, а обычные, то надо проверить системную переменную mysql: key_buffer_size, возможно ее нужно увеличить. (хеш-индексы так и так будут в памяти)
А то когда индекс не входит в буфер и читается с диска - это печально