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

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

большинство моих медленных запросов содержит ORDER BY RAND(). Я не могу найти реальное решение для решения этой проблемы. Существует возможное решение в 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
8 87
mysql performance random

8 ответов:

попробуйте это:

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 строки, которая будет выбрана на текущем шаге.

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

обновление:

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

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 ' s распределены более или менее равномерно.

63

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

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

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

других решений:

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

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

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;
1

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

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
)
0

решение для вашего пустышка-пример:

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() следует читать в этой статье.

0

я оптимизирую много существующих запросов в моем проекте. Решение 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

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

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

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

0

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

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

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

0
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; 
}
-1