Как делает РЭНД ORDER BY MySQL () работа?
Я проводил некоторое исследование и тестировал о том, как сделать быстро случайный выбор в MySQL. В процессе я столкнулся с некоторыми неожиданными результатами, и теперь я не полностью уверен, что знаю, как РЭНД ORDER BY () действительно работает.
Я всегда думал, что, когда Вы делаете РЭНД ORDER BY () на таблице, MySQL добавляет новый столбец к таблице, которая заполнена случайными значениями, затем это сортирует данные по тому столбцу и затем например, Вы принимаете вышеупомянутое значение, которое добралось там случайным образом. Я сделал большой поиск с помощью Google и тестирование и наконец нашел, что запрос предложения 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 () занимает 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 (), и лично я предпочел бы использовать
SELECT id FROM table ORDER BY RAND() LIMIT 1;
SELECT * FROM table WHERE id=ID_FROM_PREVIOUS_QUERY LIMIT 1;
который, да, медленнее, чем метод Jay, однако это меньше и легче понять. Мои запросы являются довольно большими с несколькими СОЕДИНЕНИЯМИ и с оператором Where и в то время как метод Jay все еще работает, запрос становится действительно большим и сложным, потому что я должен использовать все СОЕДИНЕНИЯ и ГДЕ в ПРИСОЕДИНЕННОМ (названный x в его запросе) sub запрашивают.
Спасибо за внимание!
4 ответа
Хотя не существует такой вещи, как «быстрый заказ с помощью rand ()», есть обходной путь для вашей конкретной задачи.
Чтобы получить любую одну случайную строку , вы можете сделать так, как это делает этот немецкий блоггер: http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of -alternatives / (Я не видел URL-адрес горячей ссылки. Если кто-то видит его, не стесняйтесь редактировать ссылку).
Текст на немецком языке, но код SQL находится немного ниже по странице и в большие белые коробки, так что это не сложно увидеть.
В основном то, что он делает, - это создание процедуры, которая выполняет работу по получению действительной строки. Это генерирует случайное число от 0 до max_id, попробуйте получить строку, и если она не существует, продолжайте, пока не нажмете ту, которая есть.Он позволяет извлекать x случайных строк, сохраняя их во временной таблице, так что вы, вероятно, можете переписать процедуру, чтобы она была немного быстрее, выбирая только одну строку.
Обратной стороной этого является то, что если вы удалите МНОГО строк и есть огромные пробелы, велика вероятность того, что он пропустит множество раз, что сделает его неэффективным.
Обновление: разное время выполнения
SELECT * FROM table ORDER BY RAND () LIMIT 1; / 30-40 секунд /
ВЫБРАТЬ идентификатор ИЗ таблицы ORDER BY RAND () LIMIT 1; / 0,25 секунды /
ВЫБРАТЬ идентификатор, имя пользователя ИЗ таблицы ORDER BY RAND () LIMIT 1; / 90 секунд /
Я как бы ожидал увидеть примерно одинаковое время для всех трех запросов, поскольку я всегда сортирую по одному столбцу. Но почему-то этого не произошло. Пожалуйста, дайте мне знать, если у вас есть какие-либо идеи по этому поводу.
Возможно, это связано с индексацией. id индексируется, и к нему можно быстро получить доступ, тогда как добавление имени пользователя к результату означает, что ему необходимо прочитать его из каждой строки и поместить в таблицу памяти. С * он также должен считывать все в память, но ему не нужно прыгать по файлу данных, что означает, что на поиск не нужно терять время.
Это имеет значение, только если есть столбцы переменной длины (varchar / text), что означает, что он должен проверить длину, а затем пропустить эту длину, а не просто пропускать заданную длину (или 0) между каждой строкой.
Я могу сказать вам, почему SELECT id FROM ... намного медленнее, чем два других, но я не уверен, почему SELECT id, имя пользователя в 2-3 раза быстрее, чем SELECT * .
Когда у вас есть индекс (первичный ключ в вашем случае) и результат включает только столбцы из индекса, оптимизатор MySQL может использовать данные только из индекса, даже не просматривая саму таблицу. Чем дороже каждая строка, тем больший эффект вы получите, поскольку вы заменяете операции ввода-вывода файловой системы чистыми операциями в памяти. Если у вас будет дополнительный индекс для (id, username), у вас будет аналогичная производительность и в третьем случае.
Почему бы вам не добавить индекс id, имя пользователя в таблицу, посмотрите, заставляет ли это mysql использовать индекс, а не только файловую сортировку и временную таблицу.
Возможно, это связано с индексацией. 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 |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+