Блог

В нескольких проектах, над которыми я работал, я столкнулся с необходимостью извлекать случайные строки из больших (> 1 МЛН строк) таблиц. С таблицами такого размера ORDER BY rand() LIMIT 1 это не вариант, поскольку это быстро поставит базу данных на колени.

Обычным решением было сгенерировать случайное число между MIN(id) и MAX(id) и выбрать эту строку напрямую. Однако, если в последовательности идентификаторов есть большие пробелы, это потребует либо большого количества повторных запусков, либо использования WHERE id >= :myrandomnumber , что приведет к тому, что строки, которые следуют за большими пробелами, получат значительно больше обращений, чем в среднем.

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

Вопрос: каков был бы наилучший способ сохранить эту последовательность без пропусков?

Первое решение, которое приходит на ум, — это создание вспомогательной таблицы, recycled_ids которая будет содержать столбцы tablename и id2 . Всякий раз, когда строка удаляется из tablename , id2 эта строка вставляется в recycled_ids . Когда вставляются новые строки, id2 либо выбирается из recycled_ids , либо, если ни одна из них не доступна, создается новая. Есть ли более простой способ?

Дополнительные вопросы:Существуют ли ORM или фреймворки, которые уже делают это или иным образом обеспечивают эффективный случайный выбор строк? Это существующий шаблон, есть ли у него имя?

Обновление: Я написал для этого быстрый тест и сравнил его с таблицей со 125 000 строками и 30 000 пробелами между ними. Результаты довольно многообещающие:

 Fetch a random row 100 times using id2: 0.0234689712524 seconds
Fetch a random row 100 times using ORDER BY rand() LIMIT 1: 54.992347002 seconds
  

При вставке тестовых данных я удалил одну случайную строку на каждые пять вставленных строк. Последовательность остается без пробелов все время.

 for($i=1; $i<=$amount; $i  ) {
    insert_row();
    if($i % 5 == 0)
        delete_random_row();
}
  

Повторный запуск этого цикла с $amount = 10000 занимает 9 секунд на моем vserver низкого уровня. Это 0,009 секунды на строку, и это включает удаление случайной строки каждые пять итераций. По мере роста таблицы он становится медленнее, но выборка случайной строки этого не делает.

Мои первоначальные вопросы все еще актуальны.

Вот как я бы это сделал —

1. Выберите МАКСИМАЛЬНОЕ значение (id) из вашей таблицы
2. В PHP (или любом другом языке, который вы используете) сгенерируйте случайное целое число от 1 до MAX (id)
3. SELECT * FROM table WHERE id >= $random ORDER BY id ASC LIMIT 1
4. Если 3 ничего не возвращает, SELECT * FROM table WHERE id < $random ORDER BY id DESC LIMIT 1

Позволяет избежать выполнения любых запросов, которые были бы ужасно медленными. Это также позволяет избежать дополнительного столбца, который, сохраняя пробелы, был бы действительно неприятной работой!

Я бы сказал, что рейтинг помогает.

 SET @rank:= 1;  

SELECT * FROM
  (
  SELECT @rank:= @rank   1 as rank, * FROM table1  
  ) s
WHERE s.rank = $random;