Strict Standards: Non-static method nggallery::get_option() should not be called statically in /home/mchedlishvili.com/mike/WWW/wp-content/plugins/nextgen-gallery/nggfunctions.php on line 10

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

Strict Standards: Only variables should be assigned by reference in /home/mchedlishvili.com/mike/WWW/wp-includes/post.php on line 173

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

Проблема

Взяли тут аутсорсера написать небольшой и несложный код на 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(, $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 > 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.

Источник: http://habrahabr.ru/blogs/mysql/41968/

C этой записью сегодня также читали: