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

Я бы хотел оптимизировать свои запросы, поэтому я просматриваю 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 

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

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

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

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

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

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

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

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

 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; 

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

 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 ) 

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

 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() , вы должны прочитать эту статью .

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

В настоящее время проблема заключается в том, что выставляем место проживания_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 ().

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

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

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

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

 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; } 
  • Выберите случайную строку из таблицы sqlite
  • Разверните случайный диапазон от 1-5 до 1-7
  • Создание m различных случайных чисел в диапазоне
  • Принудительный модуль Arc4random
  • Структура данных для загруженных кубиков?
  • Как рандомизировать (или переместить) кадр данных и разбить по столбцу?
  • Каков оптимальный алгоритм для создания несмещенного случайного целого в пределах диапазона?
  • Как решить медленную Java `SecureRandom`?
  • Как выбрать предмет по его вероятности?
  • Как я могу перетасовать строки текстового файла в командной строке Unix или в сценарии оболочки?
  • Давайте будем гением компьютера.