Pull to refresh

Быстрый выбор случайных значений из больших таблиц MySQL по условию

SQLBig Data
Sandbox
Задача выбора случайных строчек из таблицы довольно часто возникает перед разработчиками.
В случае, если используется СУБД MySQL, обычно она решается примерно следующим способом:

SELECT *
FROM users
WHERE role_id=5
ORDER BY rand()
LIMIT 10


Такой код работает крайне медленно для больших таблиц.
Если в запросе не нужно использовать WHERE или таблица небольшая, есть эффективные решения, например habrahabr.ru/post/54176 или habrahabr.ru/post/55864.
Но готовых решений для большой таблицы и необходимости фильтровать по условию, получая при каждом запросе новые значения, я не нашел, поэтому описание моего способа под катом.


Как оказалось, MySQL не умеет эффективно выбирать случайные строки с помощью ORDER BY rand() LIMIT N, где необходимо отфильтровать строки по условию (хотя тот же MSSQL отлично справляется с выбором случайных строк из таблицы с большим количеством записей).

Итак, решая задачу «в лоб», запрос (в таблице 5млн. записей):

SELECT *
FROM users
WHERE role_id=5
ORDER BY rand()
LIMIT 10


Запрос занял 41.3544 сек., что недопустимо долго. Найти максимальный и минимальный id, а затем выбрать случайные id из промежутка в данном случае нельзя: из-за условия WHERE, id идут уже не по порядку и разряженно.

Мое решение следующее: добавляется таблица random_seed, содержащая поля id и random_seed, заполняемая случайными числами, на данную колонку добавляется индекс, также индекс добавляется на колонку, по которой будет происходить выборка.
Теперь, чтобы выбрать случайные строки по условию, запрос нужно поменять следующим образом (в таблице 5млн. записей):

SELECT
u1.*
FROM
users u1,
random_seed rs
WHERE
u1.role_id=5 AND u1.id=(rs.id+random_from_php)
ORDER BY
rs.random_seed
LIMIT 10


Запрос занял 0.0460 сек., что является уже более чем приемлемым результатом. Переменная random_from_php генерируется вызывающим запрос кодом, что обеспечивает случайный набор значений при каждом запросе, это число обеспечит выборку по новым случайным числам. В таблице random_seed должно быть столько же значений, сколько в таблице, из которой нужно брать случайные строки + N записей, где N это максимально возможное значение random_from_php.

Реальный пример из моей работы — выбор случайных категорий товаров из разных комбинаций (всего 4000000 записей):

«Обычный»: запрос «Ускоренный» запрос:
SELECT
oc1.*
FROM
object_category oc1
WHERE oc1.region_id=6
ORDER BY RAND()
LIMIT 10
SELECT
oc1.*
FROM
object_category oc1, random_seed rs
WHERE
oc1.id=(rs.id+564756) AND oc1.region_id=6
ORDER BY
rs.random_seed
LIMIT 10
Время выполнения:
1.726с 0.007с
1.851с 0.010с
1.803с 0.006с
1.784с 0.008с


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

Минусы:
— Необходимость ввода дополнительной таблицы
— Необходимость изменения привычных запросов
Tags:mysql performancesqlrandom
Hubs: SQL Big Data
Total votes 20: ↑12 and ↓8 +4
Views32.2K

Popular right now

Программа Data Scientist
June 29, 202168,800 ₽Luxoft Training
Курс Data Scientist
July 4, 202125,900 ₽Level UP
Data Science Bootcamp
July 5, 2021250,000 ₽Elbrus Coding Bootcamp
Специалист по Data Science плюс
July 15, 2021228,000 ₽Яндекс.Практикум
Специалист по Data Science
July 15, 202195,000 ₽Яндекс.Практикум

Top of the last 24 hours