Как я могу оптимизировать функцию MySQL ORDER BY RAND()?

Я хотел бы оптимизировать свои запросы, поэтому я просматриваю mysql-slow.log.

Большинство моих медленных запросов содержат ORDER BY RAND(). Я не могу найти реальное решение для решения этой проблемы. Theres - возможное решение в MySQLPerformanceBlog, но я не думаю, что этого достаточно. На плохо оптимизированных (или часто обновляемых, управляемых пользователем) таблицах это не сработает, или мне нужно запустить два или более запросов, прежде чем я смогу выбрать свою PHP -генерированную случайную строку.

Есть ли какое-либо решение для этой проблемы?

Пример фиктивного примера:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
ORDER BY
        RAND()
LIMIT 1

Ответы

Ответ 1

Попробуйте следующее:

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND(20090301) < @lim / @cnt
        ) i

Это особенно эффективно при MyISAM (так как COUNT(*) является мгновенным), но даже в InnoDB он 10 раз более эффективен, чем ORDER BY RAND().

Основная идея здесь заключается в том, что мы не сортируем, а вместо этого сохраняем две переменные и вычисляем running probability строки, которая будет выбрана на текущем шаге.

См. эту статью в своем блоге для более подробной информации:

Update:

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

SELECT  aco.*
FROM    (
        SELECT  minid + FLOOR((maxid - minid) * RAND()) AS randid
        FROM    (
                SELECT  MAX(ac_id) AS maxid, MIN(ac_id) AS minid
                FROM    accomodation
                ) q
        ) q2
JOIN    accomodation aco
ON      aco.ac_id =
        COALESCE
        (
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_id > randid
                AND ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        ),
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        )
        )

Это предполагает, что ваш ac_id распределен более или менее равномерно.

Ответ 2

Это зависит от того, насколько вы должны быть случайными. Решение, с которым вы связаны, очень хорошо работает с ИМО. Если у вас нет больших пробелов в поле ID, это все равно довольно случайное.

Однако вы можете сделать это в одном запросе, используя это (для выбора одного значения):

SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 1

Другие решения:

  • Добавьте постоянное поле float под названием random в таблицу и заполните его случайными числами. Затем вы можете создать случайное число в PHP и сделать "SELECT ... WHERE rnd > $random"
  • Захватите весь список идентификаторов и кешируйте их в текстовом файле. Прочитайте файл и выберите из него случайный идентификатор.
  • Загрузите результаты запроса как HTML и сохраните его в течение нескольких часов.

Ответ 3

Вот как бы я это сделал:

SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*)
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != 'draft'
        AND c.acat_slug != 'vendeglatohely'
        AND a.ac_images != 'b:0;';

SET @sql := CONCAT('
  SELECT  a.ac_id,
        a.ac_status,
        a.ac_name,
        a.ac_status,
        a.ac_images
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != ''draft''
        AND c.acat_slug != ''vendeglatohely''
        AND a.ac_images != ''b:0;''
  LIMIT ', @r, ', 1');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

Ответ 4

Это даст вам один дополнительный запрос, который будет использовать индекс для получения случайного идентификатора, тогда другой запрос будет запускать вашу объединенную таблицу.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
AND accomodation.ac_id IS IN (
        SELECT accomodation.ac_id FROM accomodation ORDER BY RAND() LIMIT 1
)

Ответ 5

Решение для вашего фиктивного примера будет:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation,
        JOIN 
            accomodation_category 
            ON accomodation.ac_category = accomodation_category.acat_id
        JOIN 
            ( 
               SELECT CEIL(RAND()*(SELECT MAX(ac_id) FROM accomodation)) AS ac_id
            ) AS Choices 
            USING (ac_id)
WHERE   accomodation.ac_id >= Choices.ac_id 
        AND accomodation.ac_status != 'draft'
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
LIMIT 1

Чтобы узнать больше об альтернативах ORDER BY RAND(), вы должны прочитать эту статью.

Ответ 6

Я оптимизирую много существующих запросов в моем проекте. Решение Quassnoi помогло мне значительно увеличить количество запросов! Однако мне сложно включить упомянутое решение во все запросы, особенно для сложных запросов, связанных с множеством подзапросов на нескольких больших таблицах.

Поэтому я использую менее оптимизированное решение. По сути, он работает так же, как и Quassnoi.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
        AND rand() <= $size * $factor / [accomodation_table_row_count]
LIMIT $size

$size * $factor / [accomodation_table_row_count] определяет вероятность выбора случайной строки. Rand() будет генерировать случайное число. Строка будет выбрана, если rand() меньше или равно вероятности. Это эффективно выполняет случайный выбор, чтобы ограничить размер таблицы. Поскольку есть вероятность, что он вернет меньше заданного предела, нам нужно увеличить вероятность, чтобы мы выбрали достаточно строк. Следовательно, мы умножаем $size на $factor (обычно я устанавливаю $factor = 2, работает в большинстве случаев). Наконец, мы делаем limit $size

Теперь проблема заключается в разработке accomodation_table_row_count. Если мы знаем размер таблицы, мы МОЖЕМ жестко закодировать размер таблицы. Это будет работать быстрее, но, очевидно, это не идеально. Если вы используете Myisam, получение таблицы считается очень эффективным. Поскольку я использую innodb, я просто делаю простой подсчет + выбор. В вашем случае это будет выглядеть так:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
        AND rand() <= $size * $factor / (select (SELECT count(*) FROM `accomodation`) * (SELECT count(*) FROM `accomodation_category`))
LIMIT $size

Сложная часть - это правильная вероятность. Как вы можете видеть, следующий код фактически вычисляет только приблизительный размер таблицы темпа (на самом деле, слишком грубый!): (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category)) Но вы можете уточнить эту логику, чтобы приблизиться к приближению размера таблицы. Обратите внимание, что лучше выбрать OVER-select, чем выбирать строки. то есть, если вероятность установлена ​​слишком низко, вы рискуете не выбирать достаточное количество строк.

Это решение работает медленнее, чем решение Quassnoi, поскольку нам нужно пересчитать размер таблицы. Тем не менее, я считаю, что это кодирование намного более управляемо. Это компромисс между точностью + производительность и сложностью кодирования. Сказав это, на больших таблицах это намного быстрее, чем Order by Rand().

Примечание. Если логика запроса разрешает, выполните произвольный выбор как можно раньше до любых операций соединения.

Ответ 7

(Да, я получу разное, чтобы не хватать мяса здесь, но разве ты не можешь быть веганом на один день?)

Случай: последовательный AUTO_INCREMENT без пробелов, 1 строка возвращается
Случай: последовательный AUTO_INCREMENT без пробелов, 10 строк
Случай: AUTO_INCREMENT с пробелами, 1 строка возвращена
Случай: дополнительный столбец FLOAT для рандомизации
Случай: столбец UUID или MD5

Эти 5 случаев могут быть сделаны очень эффективными для больших таблиц. Подробнее см. мой блог.

Ответ 8

function getRandomRow(){
    $id = rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT);
    $res = getRowById($id);
    if(!empty($res))
    return $res;
    return getRandomRow();
}

//rowid is a key on table
function getRowById($rowid=false){

   return db select from table where rowid = $rowid; 
}