Оптимизация ORDER BY RAND()
Как выбрать случайную запись из таблицы в MySQL?
Во всех учебниках и мануалах случайную запись выбирают так:
SELECT id FROM files ORDER BY rand() LIMIT 1;
Но такие запросы работают очень медленно. Посмотрим на EXPLAIN:
EXPLAIN SELECT id FROM files ORDER BY rand() LIMIT 1;
Увидим, что MySQL создает временную таблицу и использует сортировку всех данных. Такой запрос будет работать все медленнее при наполнении таблицы:
+----+-----+------+----------------------------------------------+ | id | ... | rows | Extra | +----+-----+------+----------------------------------------------+ | 1 | ... | 4921 | Using index; Using temporary; Using filesort | +----+-----+------+----------------------------------------------+
Правильным решением будет использование индекса и избавление от ORDER BY RAND(). Для этого нужно:
- Определить максимальное значение ID (да, такая колонка должна быть и она должна быть ключом) в таблице.
- Получить любое случайное число от нуля до максимального ID.
- Выбрать первую запись из таблицы, где ID больше указанного случайного числа, отсортировав ее по этой же колонке.
Если перевести все в запрос:
SELECT f.id FROM files f JOIN ( SELECT RAND() * (SELECT MAX(id) FROM files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1;
Как это работает
- Во вложенном запросе мы определяем максимальное значение ID. Допустим оно будет 100000.
- Дальше умножаем это значение на функцию RAND(). Она возвращает значение от 0 до 1. Пусть в примере будет 0.5. Тогда результат умножения будет 50000.
- После этого это значение с помощью JOIN прибавляется в каждой строке оригинальной таблицы.
- Фильтр f.id >= m.max_id выберет первую попавшуюся запись, ID которой будет больше 50000.
- Поскольку мы использовали сортировку ORDER BY f.id ASC, все пропущенные записи будут иметь значение меньше 50000.
- Это значит, что мы выбрали случайную запись из всей таблицы. Но в отличие от ORDER BY RAND(), мы использовали сортировку и фильтрацию по индексу ID (а значит эффективно).
Скорость такого запроса будет в несколько раз быстрее, чем оригинального:
mysql> SELECT id FROM files ORDER BY rand() LIMIT 1; +-------+ | id | +-------+ | 72643 | +-------+ 1 row in set (0.17 sec)
Ускоренная версия:
mysql> SELECT f.id FROM files f JOIN ( SELECT rand() * (SELECT max(id) from files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1; +-------+ | id | +-------+ | 86949 | +-------+ 1 row in set (0.00 sec)
Теперь работает быстро и не зависит от размера таблицы.
Ссылки
- Оптимизация ORDER BY RAND()
ВКонтакте
Одноклассники
Telegram
MySQL PHP random WordPress Производительность
Оптимизация ORDER BY RAND() — WordPressify
от Михаил Кобзарёв
1 Как это работает
2 Ссылки
Как выбрать случайную запись из таблицы в MySQL?
Во всех учебниках и мануалах случайную запись выбирают так:
SELECT id FROM files ORDER BY rand() LIMIT 1;
Но такие запросы работают очень медленно. Посмотрим на EXPLAIN:
EXPLAIN SELECT id FROM files ORDER BY rand() LIMIT 1;
Увидим, что MySQL создает временную таблицу и использует сортировку всех данных. Такой запрос будет работать все медленнее при наполнении таблицы:
+----+-----+------+----------------------------------------------+ | id | ... | rows | Extra | +----+-----+------+----------------------------------------------+ | 1 | ... | 4921 | Using index; Using temporary; Using filesort | +----+-----+------+----------------------------------------------+
Правильным решением будет использование индекса и избавление от ORDER BY RAND().
Для этого нужно:
- Определить максимальное значение ID (да, такая колонка должна быть и она должна быть ключом) в таблице.
- Получить любое случайное число от нуля до максимального ID.
- Выбрать первую запись из таблицы, где ID больше указанного случайного числа, отсортировав ее по этой же колонке.
Если перевести все в запрос:
SELECT f.id FROM files f JOIN ( SELECT RAND() * (SELECT MAX(id) FROM files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1;
Как это работает
- Во вложенном запросе мы определяем максимальное значение ID. Допустим оно будет 100000.
- Дальше умножаем это значение на функцию RAND(). Она возвращает значение от 0 до 1. Пусть в примере будет 0.5. Тогда результат умножения будет 50000.
- После этого это значение с помощью JOIN прибавляется в каждой строке оригинальной таблицы.
- Фильтр f.id >= m.max_id выберет первую попавшуюся запись, ID которой будет больше 50000.
- Поскольку мы использовали сортировку ORDER BY f.id ASC, все пропущенные записи будут иметь значение меньше 50000.
- Это значит, что мы выбрали случайную запись из всей таблицы. Но в отличие от ORDER BY RAND(), мы использовали сортировку и фильтрацию по индексу ID (а значит эффективно).
Скорость такого запроса будет в несколько раз быстрее, чем оригинального:
mysql> SELECT id FROM files ORDER BY rand() LIMIT 1; +-------+ | id | +-------+ | 72643 | +-------+ 1 row in set (0.17 sec)
Ускоренная версия:
mysql> SELECT f.id FROM files f JOIN ( SELECT rand() * (SELECT max(id) from files) AS max_id ) AS m WHERE f.id >= m.max_id ORDER BY f.id ASC LIMIT 1; +-------+ | id | +-------+ | 86949 | +-------+ 1 row in set (0.00 sec)
Теперь работает быстро и не зависит от размера таблицы.
Ссылки
- Оптимизация ORDER BY RAND()
Источник: https://www.kobzarev.com/programming/optimizatsiya-order-by-rand/
Понравилось это:
Нравится Загрузка.
..
Михаил Кобзарёв
Суровый русский тимлид. Жил в Магадане, в офисе московских веб студий и в Тульской деревне. Виртуозно знает WordPress, PHP, ООП, Vue.js и вот это вот все. Делает крутые высоконагруженные сайты, поэтому уже почти захватил весь рынок WordPress разработки в России. Не дает никому делать сайты без спроса. Ведет блог о разработке, дайджест в телеграмме и в ВК. Любит сиськи, баню и радиоэлектронику. 100% патриот (но это не точно). Тролль 542 уровня. Ездит в отпуск раз в 5 лет.
select — Как работает MySQL ORDER BY RAND()?
спросил
Изменено 10 месяцев назад
Просмотрено 31к раз
Я провел небольшое исследование и тестирование того, как сделать быстрый случайный выбор в MySQL.
В процессе я столкнулся с некоторыми неожиданными результатами, и теперь я не совсем уверен, что знаю, как на самом деле работает ORDER BY RAND().
Я всегда думал, что когда вы выполняете ORDER BY RAND() в таблице, MySQL добавляет в таблицу новый столбец, который заполняется случайными значениями, затем сортирует данные по этому столбцу, а затем, например, вы берете указанное выше значение, которое попало туда случайным образом. Я много искал в гугле и тестировал и, наконец, обнаружил, что запрос, который Джей предлагает в своем блоге, действительно является самым быстрым решением:
SELECT * FROM Table T JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Таблица) AS x ON T.ID >= x.ID LIMIT 1;
В то время как обычный ORDER BY RAND() занимает 30-40 секунд в моей тестовой таблице, его запрос выполняет работу за 0,1 секунды. Он объясняет, как это работает в блоге, поэтому я просто пропущу это и, наконец, перейду к странным вещам.
Моя таблица — это обычная таблица с PRIMARY KEY имя пользователя , age и т.
д. Вот что я пытаюсь объяснить
SELECT * FROM table ORDER BY RAND( ) ПРЕДЕЛ 1; /*30-40 секунд*/ SELECT id FROM table ORDER BY RAND() LIMIT 1; /*0,25 секунды*/ ВЫБЕРИТЕ идентификатор, имя пользователя ИЗ таблицы ORDER BY RAND() LIMIT 1; /*90 секунд*/
Я ожидал увидеть примерно одинаковое время для всех трех запросов, так как я всегда сортирую по одному столбцу. Но этого почему-то не произошло. Пожалуйста, дайте мне знать, если у вас есть идеи по этому поводу. У меня есть проект, в котором мне нужно быстро выполнить 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, и хотя метод Джея все еще работает, запрос становится действительно большим и сложным, потому что мне нужно использовать все JOIN и WHERE в подзапросе JOINed (называемого x в его запросе).
Спасибо за ваше время!
- mysql
- выбрать
- случайно
2
Хотя не существует такого понятия, как «быстрый заказ с помощью rand()», существует обходной путь для вашей конкретной задачи.
Для получения любой одиночной случайной строки вы можете сделать так, как это делает этот немецкий блоггер: http://web.archive.org/web/20200211210404/http://www.roberthartung.de/mysql-order-by- rand-a-case-study-of-alternatives/ (Я не смог увидеть ссылку на горячую ссылку. Если кто-то ее увидит, не стесняйтесь редактировать ссылку.)
Текст на немецком языке, но код SQL немного ниже по странице и в больших белых прямоугольниках, так что его нетрудно увидеть.
По сути, он создает процедуру, которая выполняет работу по получению допустимой строки. Это генерирует случайное число от 0 до max_id, попробуйте получить строку, и если она не существует, продолжайте, пока не нажмете ту, которая существует.
Он позволяет извлекать x случайных строк, сохраняя их во временной таблице, поэтому вы, вероятно, можете переписать процедуру, чтобы она была немного быстрее, извлекая только одну строку.
Недостатком этого является то, что если вы удаляете МНОГО строк и есть огромные промежутки, велика вероятность того, что он пропустит множество раз, что сделает его неэффективным.
Обновление: разное время выполнения
SELECT * FROM table ORDER BY RAND() LIMIT 1; / 30-40 секунд /
SELECT id FROM table ORDER BY RAND() LIMIT 1; / 0,25 секунды /
SELECT id, имя пользователя из таблицы ORDER BY RAND() LIMIT 1; / 90 секунд /
Я ожидал увидеть примерно одно и то же время для всех трех запросов, так как я всегда сортирую по одному столбцу. Но этого почему-то не произошло. Пожалуйста, дайте мне знать, если у вас есть идеи по этому поводу.
Возможно, это связано с индексацией.
id проиндексирован и быстро доступен, тогда как добавление имени пользователя к результату означает, что ему нужно прочитать это из каждой строки и поместить в таблицу памяти. С * он также должен считывать все в память, но ему не нужно прыгать по файлу данных, что означает, что нет потери времени на поиск.
Это имеет значение, только если есть столбцы переменной длины (varchar/text), что означает, что необходимо проверить длину, а затем пропустить эту длину, а не просто пропустить заданную длину (или 0) между каждой строкой.
4
Возможно, это связано с индексацией. идентификатор проиндексированы и быстро доступны, в то время как добавление имени пользователя к результату означает это нужно прочитать из каждой строки и поместите его в таблицу памяти. С * он также должен прочитать все в память, но это не обязательно прыгать по файлу данных, что означает нет времени на поиски.
Этот
имеет значение только при наличии
столбцы переменной длины, что означает
он должен проверить длину, а затем пропустить
эта длина, в отличие от просто
пропуск установленной длины (или 0) между
каждый рядПрактика лучше всех теорий! Почему бы просто не проверить планы? 🙂
mysql> объяснить выбор имени из аватара с помощью RAND() limit 1; +----+-------------+--------+-------+------------- --+-----------------+---------+------+-------+---- ------------------------------------------+ | идентификатор | тип_выбора | стол | тип | возможные_ключи | ключ | key_len | ссылка | строки | Экстра | +----+-------------+--------+-------+------------- --+-----------------+---------+------+-------+---- ------------------------------------------+ | 1 | ПРОСТО | аватар | индекс | НУЛЕВОЙ | IDX_АВАТАР_ИМЯ | 302 | НУЛЕВОЙ | 30062 | Использование индекса; Использование временного; Использование сортировки файлов | +----+-------------+--------+-------+------------- --+-----------------+---------+------+-------+---- ------------------------------------------+ 1 ряд в наборе (0,00 сек) mysql> объяснить выбор * из порядка аватара с помощью RAND() limit 1; +----+-------------+--------+------+--------------- -+------+---------+-------+--------+---------------- ------------------+ | идентификатор | тип_выбора | стол | тип | возможные_ключи | ключ | key_len | ссылка | строки | Экстра | +----+-------------+--------+------+--------------- -+------+---------+-------+--------+---------------- ------------------+ | 1 | ПРОСТО | аватар | ВСЕ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | 30062 | Использование временного; Использование сортировки файлов | +----+-------------+--------+------+--------------- -+------+---------+-------+--------+---------------- ------------------+ 1 ряд в наборе (0,00 сек) mysql> объяснить выбор имени, опыта от аватара в порядке RAND() limit 1; +----+-------------+--------+------+--------------- +------+---------+------+-------+--- --+ | идентификатор | тип_выбора | стол | тип | возможные_ключи | ключ | key_len | ссылка | строки | Экстра | +----+-------------+--------+------+--------------- -+------+---------+-------+--------+---------------- ------------------+ | 1 | ПРОСТО | аватар | ВСЕ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | 30064 | Использование временного; Использование сортировки файлов | +----+-------------+--------+------+--------------- -+------+---------+-------+--------+---------------- ------------------+
Я могу сказать вам, почему SELECT id FROM . намного медленнее двух других, но я не уверен, почему 
.. SELECT id, имя пользователя в 2-3 раза быстрее, чем SELECT * .
Когда у вас есть индекс (первичный ключ в вашем случае) и результат включает только столбцы из индекса, оптимизатор MySQL может использовать данные только из индекса, даже не заглядывая в саму таблицу. Чем дороже каждая строка, тем больший эффект вы заметите, поскольку вы заменяете операции ввода-вывода файловой системы чистыми операциями в памяти. Если у вас будет дополнительный индекс (id, username), у вас будет аналогичная производительность и в третьем случае.
Почему бы вам не добавить в таблицу индекс id, имя пользователя , посмотрите, не заставит ли это mysql использовать индекс, а не только таблицу сортировки файлов и временную таблицу.
Первичные ключи проиндексированы. Так те «находятся» быстрее.
Если вам нужен случайный (целая строка), но скорость использования PrimaryKey с функцией Random.
.. вы можете попробовать это (код ниже):
Вы используете производную таблицу, чтобы «найти» первичный ключ один случайный ряд. Затем вы присоединяетесь к нему .. чтобы получить весь ряд.
Выберите * из my_thing mainTable
ПРИСОЕДИНИТЬСЯ
(
Выберите my_thing_key из порядка my_thing с помощью RAND() LIMIT 1
) производное1
на mainTable.my_thing_key = производный1.my_thing_key;
Использование RAND() медленнее. И * тоже медленнее.
Чего я не могу объяснить, так это почему id, имя пользователя медленнее, чем * .
Это странное явление, которое я не могу воспроизвести.
Самый быстрый способ — получить MAX(id) и сохранить в памяти. Затем, используя ваше программное обеспечение, вытяните случайное число с ним в качестве потолка, а затем в SQL
SELECT id, username FROM table WHERE id > ? ПРЕДЕЛ 1;
и если нет строки, вернуться к
SELECT id, username FROM table LIMIT 1;
Если ваша установка MySQL не содержит ошибок, вы должны сделать
SELECT id, username FROM table ORDER BY RAND() LIMIT 1;
с малым-средним набором данных.
Выполнение двух выборок не может быть быстрее. Но софт глючит.
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя электронную почту и пароль
Опубликовать как гость
Электронная почта
Обязательно, но не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Производительность.
Когда функция MySQL ORDER BY RAND() заказывает?спросил
Изменено 1 месяц назад
Просмотрено 7к раз
Я читал о функции ORDER BY RAND() и ее проблемах с производительностью. Они применимы только к запросам, возвращающим большие наборы данных? Например, если у меня есть таблица со 100 000 строк и я возвращаю набор данных с 10 записями, используя предложение WHERE, а затем использую ORDER BY RAND() LIMIT 1, будет ли этот ORDER BY RAND() применяться ПОСЛЕ того, как моя таблица будет отфильтрована до записи, соответствующие предложению WHERE, и поэтому имеют незначительные проблемы с производительностью?
- mysql
- производительность
- случайное
2
Вы правы, он применит ORDER BY после уменьшения количества строк с помощью WHERE, GROUP BY и HAVING.
Но он будет применять ORDER BY до LIMIT.
Итак, если вы достаточно отфильтруете количество строк, то да, ORDER BY RAND() может достичь того, чего вы хотите, без большого влияния на производительность. Простой и легко читаемый код имеет законное преимущество.
Проблема возникает, когда вы думаете, что ваш запрос должен уменьшить количество строк до чего-то небольшого, но со временем, по мере роста ваших данных, количество строк, которые необходимо отсортировать, снова становится большим. Поскольку ваш запрос затем выполняет LIMIT 10 для отсортированного результата, скрывает тот факт, что вы выполняете ORDER BY RAND() для 500 тыс. строк. Вы просто видите, как производительность таинственным образом ухудшается.
Я писал об альтернативных методах выбора случайной строки в своей книге «Антипаттерны SQL, том 1: избегая ловушек программирования баз данных» или в других ответах здесь, в Stack Overflow:
- Выбор случайных строк с помощью MySQL
- рандомизация большого набора данных
- быстрый выбор случайной строки из большой таблицы в mysql
2
Неважно, сколько строк вы выберете.
Если вы ORDER BY RAND() , случайное число вычисляется для каждой отдельной строки в таблице. Это связано с тем, что он должен вычислять случайное значение для каждой строки, чтобы узнать, какая строка сгенерировала наибольшее значение. Итак, если у вас есть таблица со 100 000 строк, а затем вызовите ORDER BY RAND() LIMIT 1 Вы указываете MySQL сгенерировать случайное число для 100 000 строк, отсортировать их по этому числу, а затем предоставить вам первое.
Намного быстрее:
SELECT COUNT(*) FROM
TableГенерировать случайное число между 0 и результатом вышеуказанного запроса минус 1 на вашем языке сценариев/программирования.
SELECT * FROM
ТаблицаLIMIT random_number_here,1
1
Основываясь на быстром тесте, я должен заключить, что ORDER BY RAND() применяется только после оператор WHERE применяется, а не ко всему набору данных.
Результаты из таблицы с 50 000 строк:
SELECT * FROM `mytable` LIMIT 1 (всего 1, запрос занял 0,0007 с) SELECT * FROM `mytable` WHERE First = 'Hilda' LIMIT 1 (всего 1, запрос занял 0,0010 сек) SELECT * FROM `mytable` WHERE First = 'Hilda' (всего 142, запрос занял 0,0201 секунды) SELECT * FROM `mytable` WHERE First = 'Hilda' ORDER BY RAND() LIMIT 1 (всего 1, запрос занял 0,0229сек) SELECT * FROM `mytable` WHERE First = 'Hilda' ORDER BY RAND() (всего 142, запрос занял 0,0236 секунды) SELECT * FROM `mytable` ORDER BY RAND() LIMIT 1 (всего 1, запрос занял 0,4224 секунды)
Значение RAND() будет вычисляться для каждой строки, поэтому это не очень эффективно для больших наборов данных, предложение LIMIT не меняет этого. Обычный способ обойти это — заранее вычислить случайное число, а затем получить соответствующую ему строку на основе некоторого предварительно сгенерированного индексированного столбца.
Вот одно подробное объяснение:
http://jan.