<rmcreative>

RSS

Оптимизация 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

  1. №3688
    Чистяков Денис
    Чистяков Денис 10 янв. 2011 г., 20:48:09

    Не так давно на 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, очень интересный вариант.

  2. №3689
    phpdreamer.ru
    phpdreamer.ru 10 янв. 2011 г., 20:49:20

    определенно для больших проектов лучше делать отдельную таблицу random_post, но еще лучше посты выдавать не рандомные, а самые популярные

  3. №3690
    Ekstazi
    Ekstazi 10 янв. 2011 г., 21:21:44

    На хабре встречал похожую статейку. Но тут подход немного другой :) Там было через count и limit (+ calculate не помню как её дальше для примерного подсчета записей). Так что инфа однозначно полезная.

  4. №3691
    Anatoly
    Anatoly 10 янв. 2011 г., 22:30:53

    А почему просто не сгенерить случайные числа, а потом выбрать записи с соответствующими id?

  5. №3692
    Sam
    Sam 10 янв. 2011 г., 23:13:56

    phpdreamer.ru, иногда всё-таки надо рандомные. Да и речь не только про посты.

    Ekstazi, а как там решалась проблема дыр в id?

    Anatoly, могут быть дыры в id: 1, 2, 10, 11, 12. Достаточно поудалять из середины.

  6. №3693
    Anatoly
    Anatoly 10 янв. 2011 г., 23:21:31

    дыры на практике большой проблемы не представляют. Просто генерится чисел больше, чем нужно (можно и намного больше), а выбор ограничить LIMIT, например...

  7. №3694
    Content Provider
    Content Provider 10 янв. 2011 г., 23:58:13

    Ekstazi, а как там решалась проблема дыр в id?

    Делайте сначала запрос 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]

  8. №3695
    White-Shadow
    White-Shadow 11 янв. 2011 г., 0:50:14

    А если так:

    SELECT *
    FROM (
        SELECT round(RAND()*( SELECT MAX(id) FROM `posts`)) as id
        FROM (SELECT id FROM `posts` as tmp LIMIT 1000) as tmp
        order by rand()
    )  
    AS ids JOIN `posts` as t ON t.id = ids.id
    limit 10

    на таблице чуть больше чем 3 миллионами записей выполняется 0.1-0.3 секунды

  9. №3696
    White-Shadow
    White-Shadow 11 янв. 2011 г., 1:02:25

    вернее в среднем 0.03 - 0.05 иногда бывают всплески до 0.1-0.15. просто order by RAND() около 4-5 секунд всплески видимо из-за того, что есть нагрузка на сервер и в частности на эту таблицу на запись - MyISAM

  10. №3697
    Ekstazi
    Ekstazi 11 янв. 2011 г., 1:25:18

    Sam, Вот ссылка на ту статью: http://habrahabr.ru/blogs/mysql/54176/

    Еще по теме: http://hudson.su/2010/09/16/mysql-optimizaciya-order-by-rand/

  11. №3698
    Ekstazi
    Ekstazi 11 янв. 2011 г., 1:25:49

    Ой, кстати, автоподсветка ссылок не помешала бы в блоге..

  12. №3710
    Alex Volkov
    Alex Volkov 11 янв. 2011 г., 20:26:07

    Не так давно об этом курил гугл. Остановился на варианте SELECT * FROM table WHERE id >= (SELECT FLOOR( MAX( id ) * RAND( ) ) FROM table ) ORDER BY id LIMIT 1

  13. №6033
    Аня
    Аня 09 марта 2012 г., 21:56:44

    Пробую конструкцию: 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 миллиона в таблице и ещё всё виснет.

  14. №8554
    plutov
    plutov 11 нояб. 2013 г., 23:18:08

    Вот так я оптимизировал order by для 1млн записей: plutov.by/post/order_by_rand_performance

  1. Почта опубликована не будет.

  2. Можно использовать синтаксис Markdown или HTML.

  3. Введите ответ в поле. Щёлкните, чтобы получить другую задачу.