Как работает MySQL ORDER BY RAND()?

Я занимаюсь некоторыми исследованиями и тестированием того, как сделать быстрый случайный выбор в MySQL. В процессе я столкнулся с некоторыми неожиданными результатами, и теперь я не совсем уверен, что знаю, как работает ORDER BY RAND().

Я всегда думал, что когда вы делаете ORDER BY RAND() в таблице, MySQL добавляет новый столбец в таблицу, заполненную случайными значениями, затем сортирует данные по этому столбцу, а затем, например, вы берете вышеуказанное значение, которое попало туда случайно. Я сделал много поиска и тестирования и, наконец, нашел, что запрос Jay предлагает в своем блоге - самое быстрое решение:

SELECT * FROM Table T JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1;

В то время как обычный ORDER BY RAND() занимает 30-40 секунд в моей тестовой таблице, его запрос выполняет работу за 0,1 секунды. Он объясняет, как это работает в блоге, поэтому я просто пропущу это и, наконец, перейду к странной вещи.

Моя таблица является общей таблицей с PRIMARY KEY id и другими неиндексированными материалами вроде username, age и т.д. Вот что я боюсь объяснить

SELECT * FROM table ORDER BY RAND() LIMIT 1; /*30-40 seconds*/
SELECT id FROM table ORDER BY RAND() LIMIT 1; /*0.25 seconds*/
SELECT id, username FROM table ORDER BY RAND() LIMIT 1; /*90 seconds*/

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

SELECT id FROM table ORDER BY RAND() LIMIT 1;
SELECT * FROM table WHERE id=ID_FROM_PREVIOUS_QUERY LIMIT 1;

который, да, медленнее, чем метод Джей, однако он меньше и легче понять. Мои запросы довольно большие с несколькими JOIN и с предложением WHERE, и в то время как метод Jay все еще работает, запрос становится действительно большим и сложным, потому что мне нужно использовать все JOIN и WHERE в запросе sub-запроса JOINed (так называемый x в его запросе).

Спасибо за ваше время!

Ответ 1

Пока нет такой вещи, как "быстрый порядок по rand()", существует обходное решение для вашей конкретной задачи.

Для получения какой-либо одной случайной строки вы можете сделать, как это делает немецкий блоггер: http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of-alternatives/ (I не мог видеть URL-адрес hotlink. Если кто-то его видит, не стесняйтесь редактировать ссылку.)

Текст находится на немецком языке, но код SQL немного вниз по странице и в больших белых квадратах, поэтому его не трудно увидеть.

В основном, он делает процедуру, которая выполняет работу по получению допустимой строки. Это генерирует случайное число между 0 и max_id, попробуйте выборку строки, и если она не существует, продолжайте движение, пока не нажмете тот, который делает. Он позволяет извлекать x число случайных строк, сохраняя их в таблице temp, поэтому вы можете, вероятно, переписать процедуру, чтобы немного быстрее получить только одну строку.

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

Обновление: разные времена выполнения

SELECT * FROM table ORDER BY RAND() LIMIT 1;/30-40 секунд /

SELECT id FROM table ORDER BY RAND() LIMIT 1;/0.25 секунд /

SELECT id, username FROM table ORDER BY RAND() LIMIT 1;/90 секунд /

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

Возможно, это связано с индексацией. id индексируется и получает быстрый доступ, тогда как добавление username к результату означает, что ему необходимо прочитать это из каждой строки и поместить в таблицу памяти. С помощью * он также должен читать все в памяти, но ему не нужно перескакивать по файлу данных, а это означает, что нет никакого времени для поиска.

Это имеет значение только при наличии столбцов переменной длины (varchar/text), что означает, что он должен проверять длину, а затем пропускать эту длину, а не просто пропускать заданную длину (или 0) между каждой строкой.

Ответ 2

Возможно, это связано с индексацией. id - индексируются и быстро доступны, тогда как добавление имени пользователя в результат, означает ему необходимо прочитать, что из каждой строки и поместите его в таблицу памяти. С * он также должен читать все в память, но не нужно скачок файла данных, что означает там не теряется время. Эта имеет значение, только если есть столбцы переменной длины, что означает он должен проверить длину, затем пропустить эта длина, в отличие от просто пропуская заданную длину (или 0) между каждая строка

Практика лучше всех теорий! Почему бы просто не проверить планы?:)

mysql> explain select name from avatar order by RAND() limit 1;
+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows  | Extra                                        |
+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | avatar | index | NULL          | IDX_AVATAR_NAME | 302     | NULL | 30062 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from avatar order by RAND() limit 1;
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 30062 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)

 mysql> explain select name, experience from avatar order by RAND() limit 1;
+----+-------------+--------+------+--------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 30064 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+

Ответ 3

Я могу сказать вам, почему SELECT id FROM ... намного медленнее, чем два других, но я не уверен, почему SELECT id, username в 2-3 раза быстрее, чем SELECT *.

Когда у вас есть индекс (первичный ключ в вашем случае), и результат включает только столбцы из индекса, оптимизатор MySQL может использовать данные только из индекса, даже не заглядывает в таблицу. Чем дороже каждая строка, тем больше эффекта вы наблюдаете, поскольку вы заменяете операции ввода-вывода файловой системы чистыми операциями в памяти. Если у вас будет дополнительный индекс (id, username), у вас будет аналогичная производительность и в третьем случае.

Ответ 4

Почему бы вам не добавить индекс id, username в таблицу, посмотрите, не заставляет mysql использовать индекс, а не только таблицу файлов и temp.