Оптимизация ORDER BY RAND()
10 января 2011
Задача «выбрать 10 случайных постов» часто решается так:
SELECT * FROM post ORDER BY RAND() LIMIT 10;
Для совсем небольших проектов это будет работать, но когда проект начнёт расти начнутся проблемы. И начнутся они уже с 1500 записей. На моей рабочей машине этот запрос выполняется где-то 400мс.
EXPLAIN
показывает нам Using temporary; Using filesort
, что означает создание временной таблицы (а для большого количества записей она ещё и на диск пишется) и не использование индекса. Виновник, как вы уже догадались — ORDER BY RAND()
.
Чтобы использовать индекс можно выбрать не все данные, а только id
:
SELECT id FROM post ORDER BY RAND() LIMIT 10
Теперь имея 10 id
получим посты:
SELECT post.* FROM ( SELECT id FROM post ORDER BY RAND() LIMIT 10 ) AS ids JOIN post ON post.id = ids.id
Получаем результат примерно за 10мс, что несомненно лучше. Но и этого может не хватить.
В этом случае можно поступить следующим образом:
- По крону получить 10—20 вариантов случайных записей.
- Записать в отдельную таблицу, предварительно очистив её.
Далее делаем rand(1, 20)
на сервере и выполняем очень простой и быстрый запрос:
SELECT * FROM random_post WHERE random_id = :random_id
Если нужно ещё быстрее — делаем аналог на Redis или memcached.
Комментарии RSS по email OK
Не так давно на MySQL Performance Blog`е писали об использовании MySQL, как NoSQL key => value базу данных: Percona Server now both SQL and NOSQL и http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html, очень интересный вариант.
определенно для больших проектов лучше делать отдельную таблицу random_post, но еще лучше посты выдавать не рандомные, а самые популярные
На хабре встречал похожую статейку. Но тут подход немного другой :) Там было через count и limit (+ calculate не помню как её дальше для примерного подсчета записей). Так что инфа однозначно полезная.
А почему просто не сгенерить случайные числа, а потом выбрать записи с соответствующими id?
phpdreamer.ru, иногда всё-таки надо рандомные. Да и речь не только про посты.
Ekstazi, а как там решалась проблема дыр в id?
Anatoly, могут быть дыры в id: 1, 2, 10, 11, 12. Достаточно поудалять из середины.
дыры на практике большой проблемы не представляют. Просто генерится чисел больше, чем нужно (можно и намного больше), а выбор ограничить LIMIT, например...
Делайте сначала запрос COUNT и получаете количество строк в таблице, назовем его NUM.
Затем в скрипте генерируете случайное число X в пределах [0, NUM-1]
А потом делаете запрос с LIMIT X, 1
Если вам нужно 10 случайных записей, то можно сделать 10 запросов с LIMIT и это все равно будет в разы (или десятки раз) быстрее. чем ORDER BY RAND()
А можно сделать один запрос вида ... LIMIT X, 10 или скажем три запроса ... LIMIT X1, 3 ... LIMIT X2, 3 ... LIMIT X3, 4
X1, 2, 3 сгенерируете в скрипте в пределах [0, NUM-1]
А если так:
на таблице чуть больше чем 3 миллионами записей выполняется 0.1-0.3 секунды
вернее в среднем 0.03 - 0.05 иногда бывают всплески до 0.1-0.15. просто order by RAND() около 4-5 секунд всплески видимо из-за того, что есть нагрузка на сервер и в частности на эту таблицу на запись - MyISAM
Sam, Вот ссылка на ту статью: http://habrahabr.ru/blogs/mysql/54176/
Еще по теме: http://hudson.su/2010/09/16/mysql-optimizaciya-order-by-rand/
Ой, кстати, автоподсветка ссылок не помешала бы в блоге..
Не так давно об этом курил гугл. Остановился на варианте SELECT * FROM
table
WHERE id >= (SELECT FLOOR( MAX( id ) * RAND( ) ) FROMtable
) ORDER BY id LIMIT 1Пробую конструкцию: SELECT tbl.value FROM ( SELECT DISTINCT value FROM tbl WHERE type_id = 2 ORDER BY RAND()) AS tbl1 JOIN tbl ON tbl.id = tbl.id И у меня возвращается 2 миллиарда записей при 2 миллиона в таблице и ещё всё виснет.
Вот так я оптимизировал order by для 1млн записей: plutov.by/post/order_by_rand_performance