Выборка произвольных записей

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

Проблема

Одна из задач — выбрать несколько произвольных записей из таблицы в базе MySQL.

SELECT * FROM tTable ORDER BY RAND() LIMIT 10;

На первый взгляд все логично да и работает правильно. Выбираются 10 произвольных записей.

Но в процессе выполнения этого запроса MySQL записывает во временную таблицу все (!!!) строки исходной таблицы, с одним новым полем, в которое записываются результаты функции RAND () — т.е. набор произвольных значений. Затем эта временная таблица сортируется filesort по добавленному полю с произвольными значениями и далее выбираются первые 10 записей. А если в исходной таблице 10 000 записей? А что если 1 000 000? А что если эту выборку надо делать раз десять в секунду. Да тут любой супер-пупер сервер надолго уйдет в раздумья.

Задумка

Итак начнем потихоньку. Сначала упростим задачу, предположим что нам надо выбрать не 10, а всего одну запись. Тут все довольно просто получается. Нам нужно оперировать только кол-вом записей в таблице, т.к. ключ может быть любым (составным, не числовым), а так же он может быть «разряженным» в результате удаления записей. Для начала узнаем общее кол-во записей в таблице:

SELECT COUNT(*) FROM tTable;

Далее просто вычислим произвольное число от 0 до кол-ва записей в этой таблице

rand_row = round(rand() * row_count);

Теперь без проблем можно сделать выборку произвольной записи:

SELECT * FROM tTable LIMIT rand_row, 1;

Решение на PHP

Так, с упрощенной задачей справились. Теперь нужно одолеть изначально поставленную, т.е. выбрать 10 записей. Логика тут проста: нужно посчитать 10 произвольных чисел от 0 до кол-ва записей в таблице, а затем сделать 10 запросов типа предыдущего и объединить их с помощью UNION.

Есть два варианта как это сделать: можно оформить это в виде куска PHP кода, а можно в виде MySQL хранимой процедуры.

На PHP все очень просто:

$row_count = mysql_result(mysql_query('SELECT COUNT(*) FROM tTable;'), 0);
$query = array();
while (count($query) < 10) {
    $query[] = '(SELECT * FROM tTable LIMIT '.rand(0, $row_count).', 1)';
}
$query = implode(' UNION ', $query);
$res = mysql_query($query);

Все просто и быстро. На исходной таблице с десятью тысячами записей прирост производительности по сравнению с первоначальным «ленивым» вариантом более чем в 12 раз. Если записей в исходной таблице не так много и появление повторяющихся строк в выборке неприемлемо — то можно предварительно сформировать список не повторяющихся произвольных значений, а потом уже составить по ним запрос.

Решение на MySQL

Как вариант можно еще сделать это в виде хранимой процедуры:

CREATE PROCEDURE `spRandomSelect`(IN aSchema VARCHAR(50), IN aTable VARCHAR(50), IN aNumRows INTEGER(11))
    NOT DETERMINISTIC
    READS SQL DАТА
BEGIN
  DECLARE iQuery VARCHAR(10000);
  DECLARE iNumRows INTEGER(11);

  SET iNumRows = (SELECT `TABLE_ROWS` FROM `information_schema`.`TABLES` t
    WHERE t.`TABLE_SCHEMA` = aSchema AND t.`TABLE_NAME` = aTable);
  SET iQuery = '';
  loop1: LOOP
    SET iQuery = CONCAT(iQuery, '(SELECT * FROM `', aSchema, '`.`', aTable,
      '` LIMIT ', ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows), ', 1)');
    IF aNumRows > 1 THEN
      SET iQuery = CONCAT(iQuery, ' UNION ');
    END IF;
    SET aNumRows = aNumRows - 1;
    IF aNumRows > 0 THEN
      ITERATE loop1;
    END IF;
    LEAVE loop1;
  END LOOP loop1;
  SET @iQuery = iQuery;
  PREPARE iExecStmt FROM @iQuery;
  EXECUTE iExecStmt;
  DRОP PREPARE iExecStmt;
END;

Производительность этого решения поменьше чем при подготовке составного запроса в PHP, но смысл в том чтоб показать возможность реализации и на «чистом» SQL.