MySQL
11 March 2009

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

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


Проблема

Взяли тут аутсорсера написать небольшой и несложный код на PHP и MySQL. Была одна из задач — выбрать несколько произвольных записей из таблицы в базе 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.

+12
91.3k 233
Comments 142