Mysql order by rand

Mysql order by rand

Задача: выбрать 5 случайных активных пользователей из таблицы user
Решение:
SELECT user >

ORDER BY RAND() — удобная конструкция для выборки случайных записей из таблицы. Она не очень быстрая, но удобная в случае выборки более одной случайной записи, и для небольших таблиц то, что доктор прописал. Для таблицы 20 000 записей вышеприведенный запрос занимает 0.02 с.

Вот такой интересный способ выбора случайной записи встречается иногда 🙂

  • Встроенный функционал MySQL (30)
  • SQL_CALC_FOUND_ROWS (4)
  • Оптимизация работы с MySQL (1)

Долго смеялся над последним вариантом 🙂

Да, крутая штука. Мне надо выбрать 10 случайных записей и отсортировать их по другому полю. Код ORDER BY RAND(), `rating` DESC не срабатывает.

SELECT * FROM %tablename$% WHERE %PK_KEY% IN (SELECT %PK_KEY% FROM %tablename% ORDER BY RAND() LIMIT 10) ORDER BY %fieldname% DESC

Да да. order by rand() оооочень каварная штучка. На больших таблицах и выборке нескольких случайных записей подвисает не на шутку.

Способов реализации придумано много, и все они направлены на определение "случайных" не в SQL, а программных скриптах.

А приведённый пример — лишь одна из попыток какого-то студента снять с SQL эту задачу

I’ve read about a few alternatives to MySQL’s ORDER BY RAND() function, but most of the alternatives apply only to where on a single random result is needed.

Does anyone have any idea how to optimize a query that returns multiple random results, such as this:

7 Answers 7

This solution works best using an indexed column.

Here is a simple example of and optimized query bench marked with 100,000 rows.

OPTIMIZED: 300ms

note about limit ammount: limit 4 and 4/count(*). The 4s need to be the same number. Changing how many you return doesn’t effect the speed that much. Benchmark at limit 4 and limit 1000 are the same. Limit 10,000 took it up to 600ms

note about join: Randomizing just the id is faster than randomizing a whole row. Since it has to copy the entire row into memory then randomize it. The join can be any table that is linked to the subquery Its to prevent tablescans.

Читайте также:  Fd century куртки отзывы

note where clause: The where count limits down the ammount of results that are being randomized. It takes a percentage of the results and sorts them rather than the whole table.

note sub query: The if doing joins and extra where clause conditions you need to put them both in the subquery and the subsubquery. To have an accurate count and pull back correct data.

UNOPTIMIZED: 1200ms

PROS

4x faster than order by rand() . This solution can work with any table with a indexed column.

CONS

It is a bit complex with complex queries. Need to maintain 2 code bases in the subqueries

Довольно часто у нас возникает потребность выборки случайных данных из mysql базы данных. Как правило времени нет и используется самая простая конструкция вида SELECT [что-то] FROM [где-то] WHERE [то и сё] ORDER BY RAND(). Эта конструкция работает на ура. Но вот прототип выезжает на продуктовые сервера и такой милый сердцу запрос вдруг начинает выпадать в топы медленных логов. Ниже будут рассмотрены несколько возможностей для оптимизации этого запроса по нарастанию их эффективности:

В первых примерах мы полагаем что ID стартует с номера 1 и в ID нет разрывов между 1 и максимальным ID.

#1. Передать всю работу в приложение

Мы можем тупо слить всю работу по определению случайного номера в приложение.

Так как MAX( > нам всеголишь нужно сгенерировать случайное число между 1 и MAX(id), передать его в запрос к БД и получить свою случайную строку.

Первый SELECT у нас фактически NO-OP и он оптимизирован по самое “не балуйся”. Второй запрос это eq_ref по константе и он тоже очень быстр.

#2. Делаем всю работу на стороне базы данных

Но настолько ли необходимо делать случайные выборки через приложение? Может стоит вынести “грязную” работу на сторону базы данных (прим. пер.: на самом деле первый способ это практически сферический конь в вакууме – большинство реальных задач будет выходить за рамки его применимости)

упс, это число типа double, а нам нужен int

Читайте также:  Localhost где находится windows 10

уже лучше, но что насчет скорости?

index scan? похоже мы потеряли оптимизацию MAX()

Ура! Простой подзапрос возвращает нашу потерянную производительность!

Окей, теперь мы знаем как сгенерировать случайный ID, теперь надо получить и соответствующую ему строку:

Нет нет нет! Не идите этим путем! Это самый очевидный, но также самый неверный способ! Почему? А вот почему: SELECT в условии WHERE будет выполняться для каждой строки! А это число может составлять от 0 до 4091 строки, в зависимости от того насколько вы будете удачливы.

Нам нужен такой способ выборки, при котором мы будем уверены что случайный номер генерируется только однажды:

Внутренний SELECT генерирует константу в TEMPORARY таблицу и JOIN выбирает одну строку. Великолепно! Нет сортировок, нет вмешательства приложения. Все части запроса оптимизированы.

#3. Добавляем “дыры” в primary key

Для того чтобы сделать наше предыдущее решение более универсальным, нам нужно учесть возможность “дыр” в ID (как если бы вы удалили некоторые строки).

Теперь JOIN добавляет все ID который больше или равны нашему случайному значению и мы выбираем ближайшего соседа, если равенство не возможно. НО как только одна строка найдена мы останавливаемся (LIMIT 1). И мы читаем строки в соответствии с индексом (ORDER BY id ASC). Так как мы используем знак “>=” вместо строгого равенства “=” мы можем избавиться от CEIL и получить тот же резудьтат при немного меньших затратах.

#4. Равномерное распределение

Поскольку распределение ID не равномерно, наша выборка на самом деле стала не совсем случайной (прим. пер.: насколько я понимаю, чем больше “больших” дыр в ID тем менее равномерно распределение и тем “менее случайной” будет выборка).

Читайте также:  Prestigio geovision 5066 обновление карт

Функция RAND генерирует ID от 9 до 15, которые попадают в “дыру” перед 16 и как следствие, 16 выбирается намного чаще чем остальные.

Для этой проблемы не существует нормального решения, но если ваши данные более-менее постоянны, вы можете добавить таблицу для маппинга номера строки с ее ID:

Идентификатор row_id теперь не содержит дыр и мы опять можем воспользоваться нашим запросом:

После 1000 попыток опять имеем равномерное распределение:

#5. Обслуживание таблицы Holes при помощи триггеров

Давайте подготовим таблицы как описано ниже:

Когда мы что-то меняем в r2, мы хотим чтобы r2_equi_dist также изменялась.

INSERT весьма прост. При DELETE же мы хотим поддерживать equi-dist-id в состоянии “без дыр”:

UPDATE также прост. Мы должны обслужить лишь Foreign Key constraint:

#6. Несколько случайных строк за один раз

Если вы хотите получить более одной случайной строки за раз вы можете:

  1. Выполнить запрос несколько раз
  2. Написать хранимую процедуру, которая выполняет запрос и хранит результат во временной таблице
  3. Выполнить UNION наконец

Хранимая процедура:

Хранимая процедура позволяет вам использовать структуры, известные в любом популярном языке программирования:

Для нашей задачи нам нужен только цикл LOOP:

Оставляю в качестве заданий читателю следующие задачки:

  1. Динамически составлять запрос, генерируя название временной таблицы (спасибо 2 Evgeny Babin)
  2. Используя UNIQUE index отлавливать нарушения UNIQUE key для удаления возможных дублей.

#7. Быстродействие

Чтоже стало с быстродействием? У нас есть 3 различные запроса, решающие нашу проблему:

  1. Q1. ORDER BY RAND()
  2. Q2. RAND() * MAX(ID)
  3. Q3. RAND() * MAX(ID) + ORDER BY ID

Q1 можно оценить как N * log2(N), Q2 и Q3 что-то около константы.

Чтобы получить реальные значения мы провели несколько тестов с числом строк от 100 до миллиона и выполнили каждый запрос 1000 раз.

Как вы можете видеть, простой ORDER BY RAND() оптимизирован для выполнения при количестве строк не более 100.

Ссылка на основную публикацию
Adblock detector