MySQL. Выбор случайных строк в один запрос

в 19:55, , рубрики: mysql, sql, Песочница, метки: ,

Что имеем?

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

Таблица(test) имеет следующую структуру:

  • — pk_id ( первичный ключ )
  • — id ( поле заполненное разными числами )
  • — value ( поле заполненной с помощью rand() )

Первичный ключ не имеет дыр и начинается с 1.

Способы получения

  1. ORDER BY rand + LIMIT

    image

    Получение одной строки:

    SELECT pk_id FROM test ORDER BY rand() LIMIT 1
    

    Среднее время выполнения в MySQL — 6.150 секунд

    Попробуем взять 100 записей

    SELECT pk_id FROM test ORDER BY rand() LIMIT 100
    

    Среднее время выполнения 6.170-6180 секунды
    То есть разница во времени между получением 1 и 100 случайных строк не существенна.

  2. COUNT * rand()

    image

    Получение одной строки:

    SELECT t.pk_id FROM test as t,
    (SELECT SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd FROM test LIMIT 1) t
    WHERE t.pk_id = rnd
    

    С помощью

    SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1)
    

    получаем случайное число от 0 до количества строк в таблице.
    Далее нашему случайному числу присваиваем алиас «rnd» и используем в WHERE для эквивалентного сравнения с pk_id.
    Среднее время выполнения — 1.04 секунды
    Далее нужно немного изменить данный запрос, что бы можно было вытягивать несколько строк.
    Добавим еще несколько получаемых полей в наш подзапрос и изменим проверку в WHERE с "=" на IN

    SELECT t.pk_id FROM test as t,
    (SELECT SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd,
    SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd2,
    SUBSTRING_INDEX((COUNT(pk_id)*rand()),'.', 1) as rnd3
    FROM test LIMIT 1) t
    WHERE t.pk_id IN (rnd,rnd2,rnd3)
    

    Среднее время выполнения — 1.163 секунды.
    При увеличении количества получаемых строк заметно увеличивается время выполнения запроса.
    Про 100 строк даже страшно подумать :)

  3. INFORMATION_SCHEMA + LIMIT

    image

    Получение одной строки:

    SELECT t.pk_id FROM test as t,
    (SELECT SUBSTRING_INDEX((SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test') *rand(),'.', 1) as rnd 
    FROM test LIMIT 1) tmp
    WHERE t.pk_id = rnd
    

    С помощью подподзапроса получаем количество строк в таблице 'test', не используя агрегатную функцию COUNT и дальнейшее сравнение происходит как в способе 2.
    Среднее время выполнения - 0.042 секунды
    Минимально замеченное время выполнения — 0.003 секунды.
    Попробуем получить 100 строк:

    SELECT t.pk_id FROM test as t,
    (SELECT SUBSTRING_INDEX((SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test') *rand(),'.', 1) as rnd 
    FROM test LIMIT 100) tmp
    WHERE t.pk_id in (rnd)
    ORDER BY pk_id
    

    Меняем в WHERE "=" на IN и изменяем лимит возвращаемых строк подзапросом на 100.
    Среднее время выполнения - 0.047 секунды
    Время на получения 1000 записей — 0.053 секунды
    Время на получение 10000 записей ~ 0.21 cекунды
    И напоследок 100 000 записей берем за 1.9 секунды
    Минус данного подхода в том, что в получаемом количество строк из INFORMATION_SCHEMA немного больше, чем COUNT(*) и по этому при возврате 100 000 строк теряется 7-8 строк. На 1-100 такого практически нету(Чем больше таблица, тем меньше шанс)

Выводы

  • Первый способ хороший тем, что в любом случае вернет вам случайную строку, независимо от дыр в полях и их начального значения, но самый медленный
  • Второй способ намного лучше подойдет в таблицах, где нету дыр. Работает в 6 раз быстрее, чем первый способ (на возврате одной строки).
  • Третий способ можно использовать на свой страх и риск(который очень уж незначительный), потому что можно потерять строку(строки) при значение rand() максимально приближенного к 1. Скорость возврата одной строки, по сравнению с первым способом, отличается в 150 раз.
    Если уж вернулось не 100 строк, а 99, то можно еще раз послать запрос на сервер.

Автор: DeathCore


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js