Пользователь
0,0
рейтинг
11 марта 2009 в 20:11

Разработка → Выборка произвольных записей в 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.
Андрей Кравчук @WhiteD
карма
1,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (142)

  • 0
    Rand выборка всегда интересная тема. Предположим мы имеем таблицу, которая хранит выборку с rss. RSS выгребается последовательно, 1 лента — 30-50 записей, всего rss лент 10. Задача получить 10 уникальных записей, которые были бы не от одной ленты.
    • –1
      Ну тут интересно получается :)
      Вопрос возникает вот какой — т.к. в лентах сграбленных кол-во итемов различается, то при честной равномерной выборке из всех итемов всех лент — чаще будут встречаться итемы из тех лент, где наполнение больше.
      Если нужно чтоб вхождения из всех лент в результирующем наборе были по кол-ву равны, то можно сначала составить рандомный список лент, а потом из каждой выбрать рандомную запись в результирующий набор. Получится 10 записей из 10 разных лент.
  • +4
    Ну ORDER BY rand() это конечно не есть очень хорошо, особенно если в таблице планируется действительно большое кол-во записей, но по-моему по одному UNION'у на ряд тоже не лучший выход.
    • –1
      Вообще выборка произвольных записей — ресурсоемкий процесс. По моим тестам решение с UNION самое производительное на MySQL. Если владеете вопросом на достаточном уровне и располагаете более производительным решением — прошу поделиться :)
      • +2
        Ну я исхожу из того, что проблему падения скорости выборки с LIMIT'ом на большом кол-ве записей я лично решаю введением доп. синтетического ключа, первичный ключ, как известно, должен быть неизменен, а вот у дополнительного поля с индексом на нем этого органичения нет, соот-но вполне реально проследить чтобы в доп.ключе записи были пронумерованы строго по порядку. Так вот в таком случае 10 случайных записей выбираются очень просто — генерацией 10 случайных чисел в диапазоне от 1 до максимального кол-ва записей, и SELECT * FROM table WHERE synth_key IN ($rand1, $rand2, $rand3…
        Но вариант с доп.полем вообще конечно достаточно спорный, так как убыстряя выборку — он слегка замедляет добавление рядов (нужно сначала получить номер предыдущей записи) и сильно замедляет удаление (нужно перестроить всю нумерацию начиная с текущего номера, заканчивая последним).
        • 0
          Этот вариант я рассматривал, но отказался от него по обозначенным вами причинам.
          • +2
            Почему бы и нет если записи добавляются довольно редко, а случайная выборка ведется для каждого пользователя т.е. часто то данный вариант лучший.
            • 0
              Ну в моем случае записи часто добавляются и часто удаляются. Так что вариант с доп. ключом не подходит.
        • +1
          Ну, перестраивать весь хвост при удалении не обязательно. Synth_key же не обязан поддерживать порядок нумерации.
          То есть, UPDATE table SET synth_key=$synh_key_of_deleted_item WHERE synth_key=$max_synth_key
          • 0
            Да, вы правы, просто как я уже писал, я изначально использую синтетический ключ именно для того чтобы поддерживать нумерацию, но если использовать его исключительно для случайной выборки — то это вполне подходит, и тогда этот вариант подойдет даже в случае когда происходит частое добавление/удаление записей, по одному лишнему запросу на действие имхо погоды в плане производительности сильно не сделают, а выборка уже будет не в пример быстрее.
      • 0
        Я бы попробовал сделать так:
        select count(1) from table;

        rand_row1 = round(rand() * row_count);
        rand_row2 = round(rand() * row_count);
        rand_row3 = round(rand() * row_count);
        (для примера, понятно что здесь массив)

        SET @row = 0;
        SELECT @row := @row + 1 as rownum, table.* from table having rownum IN (rand_row1, rand_row2, rand_row3)

        Насчет производительности не знаю.
  • +2
    Кстати не факт, что записи будут все уникальными, даже больше, они частенько будут повторятся, если строк в среднем 300-400.
    • –3
      Про это я написал. И даже сказал того как от этого избавиться Читайте внимательно.
      • +1
        2 раза читал, недосмотрел ;)
        Жалко нет одного красивого решения этой проблемы.
        • +1
          Ну когда СУБД начнут говорить по человечески, то вполне возможны будут такие конструкции как «ВЫБЕРИ ЧТО-НИБУДЬ ИЗ ТАБЛИЦЫ БАННЕРОВ, ШТУК 10… ПОЖАЛУЙСТА :)»
          А пока приходится искать нетривиальные решения и пользваться некрасивым кодом…
          • 0
            так они (СУБД) и так понимают, вопрос в том как они это выполняют.
          • 0
            а когда вам неравные веса баннеров захочется, база тоже должна это уметь :)?
  • +14
    Представим что у нас 1 000 000 записей и rand_row у нас выпала где то в районе 900 000
    то что нам сделает вот этот запрос?

    SELECT * FROM tTable LIMIT rand_row, 1;

    Правильно, выберет все 900 001 записей а потом возьмет одну последнею и так десять раз думаете это оптимальней.
    • –4
      Оптимальней чем сортировать временную таблицу с предварительной записью этой таблицы с новым значением.
      Да и про LIMIT в mysql советую прочитать. Работает он очень быстро, поскольку строки MySQL попросту пропускает не записывая в результат.

      Напомню, топик был мною написан о том, как я нашел более производительное решение чем ORDER BY RAND(). Я ни в коем случае не утверждаю что мой вариант сравнится по скорости с нативной UDF процедурой MySQL. Я лишь говорю что это наиболее производительный вариант с обозначенными мною условиями.
      • +1
        Меня терзают смутные сомнения что будет с производительностью если все 10 попадут в конец. А также если надо больше записей при ORDER BY RAND() ведь вся выборка и сортировка делается один раз а в вашем варианте для каждой записи. Короче надо проверять. Но для малого количества записей все таки ваш вариант будет оптимальней.
        • +1
          А меня не терзают, я знаю на собственном горьком опыте :)

          LIMIT $row, 1 UNION… работало до поры до времени при выборке 100 случайных из приблизительно 30К записей пока одним прекрасным днём не уложило на лопатки выделенный сервак :)

          В общем, пришлось немного переделывать этот алгоритм в рамках конкретной задачи…
      • 0
        >Да и про LIMIT в mysql советую прочитать. Работает он очень быстро, поскольку строки MySQL попросту пропускает не записывая в результат.

        Ваши бы слова, да в эту физическую реальность… :)

        У меня на форуме всего 1,5млн. постингов. Уже после ~700 тыс. на топиках с десятками страниц записи вида SELECT * FROM posts WHERE topic_id =… ORDER BY… LIMIT 10000, 15; стали капитально вешать сервер. Не смотря на все оптимизации индексов и настроек :)

        Вылечилось только введением отдельного поля «номер страницы».

        MySQL очень болезненно относится к выборке последних значений в большой отсортированной таблице.
  • 0
    Имхо, учитывая наличие в любой таблице такого размера Primary Key (который, как правило, int), проще получить набор случайных значений в PHP:
    $n = rand()/getrandmax();

    После чего единичная выборка делается мгновенно запросом вида:
    select * from table where id=round(0.744296810005*(select count(*) from table));

    А множественная делается в два запроса, где первый у нас выбирает этот самый count(*), а второй выглядит как:
    select * from table where id in (round(0.744296810005*10000), round(0.544296810005*10000),....);

    Впрочем, если результат первого запроса выбирать, а не помещать в переменную, то перемножение и округление можно делать средствами всё того же PHP. Работать это будет еще быстрее, чем с LIMIT и UNION.
    • +2
      Так а если записи периодически удалялись? Тогда в стройной цепочке автоинкремента будут дыри, и если выборка попадет на них — единичная выборка не выберет вообще ничего, а множественная выберет неверное кол-во записей.
      • +3
        Тьфу, *дыры, насяльника :)
      • +2
        Нда, тогда будет облом. Как вариант, можно конечно выбирать заведомо больше, хотя тоже фигня получается. Можно попробовать организовать «довыборку» при помощи if и union всё в той же хранимой процедуре. Сдаётся мне, что это всё равно будет быстрее, чем выборка с LIMIT'ами. Хотя для не-интовых ключей номер не пройдёт в любом случае.
        • +1
          Именно про не числовые ключи и про составные ключи я и сделал оговорку в тексте топика. Если бы таблица была «простой», то я бы точно не изголялся с нетривиальным кодом и хитростями.
          • 0
            Я этот момент не заметил, извиняюсь :)
  • +3
    Как-то так:

    Псевдокод:
    random = randRange(min_id, max_id)

    SQL:
    SELECT * FROM table WHERE id <= random LIMIT 1
    • +2
      В общем RTFM: dev.mysql.com/doc/refman/5.0/en/select.html
      For large tables with auto incremented primary key values, I have found the following to be most efficient in obtaining one random row:

      SELECT * FROM my_table
      WHERE pk_column >=
      (SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table)
      ORDER BY pk_column
      LIMIT 1;
      • 0
        Задача была выбрать действительно произвольные записи и не одну, а несколько. Приведенный вами пример из комментариев к мануалу не годится, т.к. при наличии большой дырки в первичном ключе часто будет попадать в выборку запись стоящая после этой «дырки».
      • +1
        Хм, сделал табличку, загнал в нее около 12к записей, структура такая
        |int pk|varchar(255) data|
        выполняю:
        mysql> SELECT `pk`, substr(`data`,1,20) FROM `test` ORDER BY RAND() LIMIT 1;
        +------+----------------------+
        | pk   | substr(`data`,1,20)  |
        +------+----------------------+
        | 1612 | ark2K9bS0NeHWk34ZDAc |
        +------+----------------------+
        1 row in set (0.15 sec)
        
        mysql> SELECT `pk`, substr(`data`,1,20) FROM `test` WHERE `pk` >= (SELECT FLOOR( MAX(`pk`) * RAND()) FROM `test`) ORDER BY `pk` LIMIT 1;
        +----+----------------------+
        | pk | substr(`data`,1,20)  |
        +----+----------------------+
        | 48 | juuZeRXMUJxyp1GV8EZM |
        +----+----------------------+
        1 row in set (1.52 sec)
        
        mysql> SELECT `pk`, substr(`data`,1,20) FROM `test` WHERE `pk` >= (SELECT FLOOR( MAX(`pk`) * RAND()) FROM `test`) ORDER BY `pk` LIMIT 1;
        +-----+----------------------+
        | pk  | substr(`data`,1,20)  |
        +-----+----------------------+
        | 173 | qQOA1ptDKU9qrJTDzf2j |
        +-----+----------------------+
        1 row in set (5.35 sec)
        

        И т.п., для первого варианта время выполнения всегда примерно 0,15 сек, для предложенного, каждый раз отличается. Более того, значение pk в выбранных строках не большие (в районе 100). Весь фокус в том, что запрос (SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table) выполняется для каждой записи… Не надо верить комментариям к мануалу, как самому мануалу ;)
        P.S. mysql крутится на втором пне с частотой 450МГц, по тому для ощутимого времени выполнения большого кол-ва записей не понадобилось.
        • –1
          А теперь выполните запрос с ORDER BY RAND() сразу в пятьдесят потоков. И посмотрите в список тредов сервера. Вы увидите как они в сильной задумчивости подвиснут в статусе 'Copying to tmp table'
          • 0
            Вообще я не за ORDER BY RAND(), хотя считаю что в большинстве случаев это решение приемлемо, я против решения предложенного в сообщении выше :) Оно еще хуже и не правильно решает задачу, точнее вообще не решает ее, т.к. выдает полный бред.
        • 0
          А первый мой комментарий можно было прочитать? Я предложил сначала вычислить random, потом подставить его в запрос.
          Потом вспомнил где я видел подобное решение и дал ссылку.
          • 0
            По ссылке просто кривое решение… Далее, использовать < или > без сортировки несколько опасно. Совершенно нет гарантии, что в таблице строки идут по порядку. Может получится так, что SELECT без ORDER BY выдаст в начале запись с id 1000, а потом с id 10… Т.е. теоретически используя этот способ запись с id 10 вообще никогда не будет получена. Хотя на практике с LIMIT 1 все работает вроде корректно, но вообще поведение не очень предсказуемо. Я из своей тестовой таблицы я удалил несколько строк расположенных вначале и добавил новую, в результате получил следующее:
            mysql> select pk,substr(`data`,1,20) from test limit 10;
            +-------+----------------------+
            | pk    | substr(`data`,1,20)  |
            +-------+----------------------+
            |     1 | nA2XwySymLkX7Yy7RNUP |
            | 12876 | new                  |
            |     8 | MSWIuJzGdZl21DXSnA3l |
            |     9 | njcVy8iL7jtP5bGlCVwo |
            |    10 | zEf99WoNXZiSSQf9s8QS |
            |    11 | qgv8yyBsM3C2bImQ9YLs |
            |    12 | Y6jjudnvutqMardInwNy |
            |    13 | L4I30BhNBpYxyqNy0Lar |
            |    14 | a9nKmnPBGzxQs5kxjSpz |
            |    15 | BODaKjhmTziFTXjQXbrD |
            +-------+----------------------+
            10 rows in set (0.01 sec)

            Что в принципе соответствует моим ожиданиям. Но:
            mysql> select pk,substr(`data`,1,20) from test where pk >= 9 limit 10;
            +----+----------------------+
            | pk | substr(`data`,1,20)  |
            +----+----------------------+
            |  9 | njcVy8iL7jtP5bGlCVwo |
            | 10 | zEf99WoNXZiSSQf9s8QS |
            | 11 | qgv8yyBsM3C2bImQ9YLs |
            | 12 | Y6jjudnvutqMardInwNy |
            | 13 | L4I30BhNBpYxyqNy0Lar |
            | 14 | a9nKmnPBGzxQs5kxjSpz |
            | 15 | BODaKjhmTziFTXjQXbrD |
            | 16 | 6pKnrXAW431nKEzezPTM |
            | 17 | UuLA1t9NMopa5b2ZvOqT |
            | 18 | M7YxrpwekRcwTjYQHb3f |
            +----+----------------------+
            10 rows in set (0.00 sec)

            Что уже не очень соответствует ожиданиям, но без limit 10 запись 12876 выдается первой. Если добавить ORDER BY, сделав тем самым поведение полностью предсказуемым, то чем оно принципиально лучше предложенного автором LIMIT? Единственный плюс, используя этот подход можно обойтись одним SQL запросом с одним подзапросом.
            • 0
              Хотя нет, одним запросом с подзапросом вроде не обойтись, подзапрос все равно будет выполнятся для каждой записи. Тогда получается то же, что и с «LIMIT rand_row, 1», либо хранимая процедура, либо доп. код на каком-нибудь языке. Но предложенный тут вариант выдает более качественный результат.
            • 0
              Вот вам другой пример запроса, на этот раз я его проверил на работоспособность:
              SELECT pk, data
              FROM test AS r1 JOIN
                (SELECT (RAND() *
                  (SELECT MAX(pk)
                  FROM test)) AS id)
                AS r2
              WHERE r1.pk >= r2.id
              ORDER BY r1.pk ASC
              LIMIT 1;

              А лучше он тем, что делается LIMIT 1, а не LIMIT хxx, 1. К сожалению многие не знают как работает LIMIT в MySQL. Кажется что LIMIT 999, 1 выберет только одну строку, но на самом деле он сначала извлекает тысячу строк, а потом из этой тысячи берет одну. А если в таблице будет не тысяча строк, а намного больше — опять начнутся тормоза.
              • 0
                Так лучше, я что-то сразу не учел, что сортировка будет по ключу, а у него индекс на b-дереве, т.е. сортировка бесплатная. Но остается проблема с дырками в ключах.
  • +1
    Интересно. А если «угадывать»? Будет ли быстрее на огромных таблицах.
    Чем строить 10 юнионов — может просто наугад выбрать 10 уникальных праймари кеев. Если записи удаляются не очень часто (как обычно и происходит) то высока вероятность того, что сразу и получим все 10. А потом если не хватит — доберем еще.

    Или еще лучше — выберем 20 записей и опять таки случайным образом выкинем из них лишние или доберем недостающие.

    Мне кажется что это будет оптимальнее на таблицах с миллионами записей нежели 9 юнионов и 10 лимитов…
  • +2
    глупый аутсорсер — как-то звучит предвзято. Такое впечатление что все аутсорсеры глупые.
    • 0
      Далеко не все глупые. Но конкретно этот неоднократно удивил меня своей недальновидностью.
  • 0
    На больших таблицах юзаем
    SELECT * FROM table1 WHERE tid> (rand()*(SELECT count(*) FROM table1) ) LIMIT 1

    хотя можно подзапрос другой юзать-не помню что быстрее, вроде одинаково почти получалось
    SELECT TABLE_ROWS FROM information_schema.`TABLES` WHERE `TABLE_NAME`='table1'
    • 0
      забыл про floor вокруг рандома :(
      • 0
        Вы еще забыли прочитать текст моего топика. Вопрос идет о выборе нескольких действительно произвольных записей. Все варианты с выборкой по первичному ключу не дают равномерного распределения вероятности выборки, если ключ разряженный.
        • 0
          Действительно произвольных не получите, особенно юзаю в пыхе rand вместо mt_rand, хотя конечно вы правы при большой разряженности ключа равномерность пострадает, но только при большом.
        • 0
          действительно не так важно на больших таблицах, а вот LIMIT offset на таких будет очень сильно тормозить
    • +2
      SELECT * FROM table1 WHERE tid> (rand()*(SELECT count(*) FROM table1) ) LIMIT 1
      Если удалим любые сто записей, то последняя сотня не попадет в выборку никогда.
      • 0
        Потому что нужно не коунт а автоиндекс брать.
      • 0
        да знаю, на таблицах с большим количеством удалений по отношению к общему числу записей лучше не юзать…
    • 0
      Я чуть выше писал, вот эта часть «WHERE tid> (rand()*(SELECT count(*) FROM table1) )» приведет к тому, что «rand()*(SELECT count(*) FROM table1)» будет по новой вычисляться для сравнения с каждой строкой. Т.е. во-первых будет выполнено множество лишних подзапросов, а во-вторых новый rand для каждой строки… вероятность выпадения последний записи равна нулю :) даже вряд ли получится дотянуть до середины таблицы, все время будут выпадать записи из начала таблицы.
  • 0
    Ребята, я написал такую функцию выбора трех текстовых объявлений случайным образом.
    Хочу привести часть кода для поддержания дискуссии.
    Какие минусы моего подхода?
    $z — число объявлений, которые надо показать.
    function draw_text_reclama($reclama_type, $z)
    {
    global $sql;
    $result=$sql->query(«SELECT reclama_id
    FROM reclama
    WHERE reclama_active = '1'
    AND reclama_type = '». $sql->escape ($reclama_type) ."'
    AND UNIX_TIMESTAMP(reclama_begin) <= UNIX_TIMESTAMP()
    AND UNIX_TIMESTAMP(reclama_end) >= UNIX_TIMESTAMP()
    ");
    if ( $result )
    {
    if ( mysql_num_rows($result) != 0 )
    {

    $data = array ( );
    while( $row = mysql_fetch_array($result) ) // наполняем массив $data числами reclama_id
    {
    $data[] = $row[0];
    }
    $rand_keys = array_rand( $data, $z );
    for ( $i=0; $i<$z; $i++ )
    {
    $value = $data[$rand_keys[$i]];
    $result = $sql->query ("
    SELECT reclama_code
    FROM reclama
    WHERE reclama_id = '$value'
    LIMIT 1
    ");
    if ( $result )
    {
    $row=mysql_fetch_assoc($result);
    $reclama_code=$row[«reclama_code»];
    echo «div».$reclama_code."/div\n";
    }
    }
    }
    }
    }
    • 0
      В вашем коде много лишних действий.
      1) Вы делаете выборку всех идентификаторов. Если таких много — это лишняя нагрузка как на БД так и на интерпретатор PHP
      2) Вы делаете по одному запросу на каждую результирующую запись. Для БД это лишняя работа по инициализации запроса и блокировке таблиц.
      3) У вас часто идет лишнее присваивание промежуточным переменным. Не забывайте что PHP присваивает по значению, а значит происходит лишнее копирование участков памяти. Если текст в выборке большой, а сам этот код вызывается часто — то это довольно много лишней работы.

      Если вы хотите придерживаться вашего алгоритма с выборкой ключей и выбора по ним произвольных значений — то как минимум нужно сформировать составной запрос через UNION, а только потом отправлять его в БД.
      • 0
        Хорошо что я обновил список комментариев перед отправкой, а то у меня почти те же указания. Только в данном случае эффективней юзать не UNION, а IN — слепить нужные id в список и сравнивать с ним:
        WHERE reclama_id IN (....)
        • 0
          Ой да… С IN лучше… Устал под вечер, сглупил :)
          • 0
            Спасибо! Вот этот момент-то как раз мне и не понятен!
            Как составить запрос с IN(...)
            Про промежуточные переменные: вы имеете в виду вместо

            $reclama_code = $row[«reclama_code»];
            echo $reclama_code;

            Использовать

            echo «тэги тэги». $row[«reclama_code»] .«тэги тэги\n»;
            • 0
              Да. Бессмысленно делать присвоение $reclama_code, если $row[«reclama_code»] итак достаточно понятное название.

              Запрос с IN составляется таким образом:
              for ( $i=0; $i<$z; $i++ ) {
                  $value .= $data[$rand_keys[$i]] . ',' ;
              }
              $result = $sql->query ("
                  SELECT reclama_code
                  FROM reclama
                  WHERE reclama_id IN (" . rtrim($value, ',') . ")
                  LIMIT $z
              ");

              Таким образом, к базе делается только один запрос.
              • 0
                Спасибо! Почти отлично работает!
                Выдает ошибку MySQL… right syntax to use near ') LIMIT 1' at line 3
                В случае, если активных объявлений оказалось 1
              • +2
                Ой… вспоминаем нативные функции пыха:

                И делаем все проще:

                $rand_keys = array_rand( array_flip( $data ), $z );

                Вот это удаляем вообще:

                for ( $i=0; $i<$z; $i++ ) {
                $value .= $data[$rand_keys[$i]]. ',';
                }

                А вот это: «rtrim($value, ',')» заменяем на: «join( ',', $rand_keys )»
                • +1
                  Йеллопуки-Йеллопуки, перешей мне с жопы руки! Спасибо вам за дельное замечание. Теперь я запомню что такое join в php.
  • 0
    Как-то для одного проекта стояла подобная задача. Решили похожим способом, только выбирали одним запросом подряд N записей начиная со случайной. При этом перемешивали таблицу несколько раз в день по крону командой
    ALTER TABLE tab ORDER BY RAND()
    • 0
      Интересное решение :)
      Вот только в моих условиях оно не подойдет. Нужна действительно произвольная выборка и перемешивание таблицы хоть ночью хоть днем сильно бы сказалось на производительности, т.к. нагрузка на сервер постоянно очень большая и записей в таблице очень много.
      • 0
        Ну, в нашем случае это были картинки, и ничего страшного не случилось бы, если пользователь вдруг увидит одинаковую комбинацию. Так что пошли на такую хитрость.
  • +7
    А вот в статье предлагается несколько решений и проводится сравнительный анализ. Мне приглянулась идея создать отдельную табличку из двух полей — нашего id (из оригинальной таблицы) и случайного номера без дырок. Заполнением и актуализацией этой дополнительной таблички занимаются триггеры.

    Но если у вас операции удаления записей частые, триггеры замучаются апдейтить дополнительную табличку. Можно, конечно, пойти чуть дальше и только помечать удаленные записи, а потом раз в N минут проходить сборщиком мусора и удалять все разом.

    Кстати, в вашем решении я вижу одну досадную ошибку: вычисление count(*) записей и собственно их выборку надо брать в одну транзакцию. Иначе можно попытаться выбрать строку, которой уже нет.

    И не ругайте аутсорсера. Он сделал правильно по букварю. Для оптимизации же надо хорошо знать свои данные, что тяжело требовать от человека со стороны.
    • 0
      И вдобавок MySQL не поддерживает несколько триггеров на одном событии одной таблицы. Так что если там уже висит один триггер, его придется менять, дописывая функционал.
      • 0
        Две задачи в одном триггере несколько портят красоту кода, но жить вполне можно. Впрочем, я могу себе это только представлять, ибо на практике не сталкивался.
  • 0
    Кстати можно сделать забавную «оптимизацию» запроса который написал ваш аутсорсер:
    SELECT * FROM tTable WHERE RAND()
    • +2
      SELECT * FROM tTable WHERE RAND()<0.001 ORDER BY RAND() LIMIT 10
      Потеряем время на вычисление и сравнение лишних RAND(), зато на несколько порядков уменьшим размер таблицы, которая сортируется в памяти. Естественно можно брать не 0.001, а подбирать какое-то другое значение, в зависимости от размера таблицы.
      • 0
        Кстати, отличное решение, хоть и частичное. Спасибо.

        На моем примере, выборка 10 из 500.000, позволяет ускорить примерно в 3 раза. Забавно, что если вместо коэффициента 0.001 выбрать например 0.1 или 0.000001, но время выборки не изменяется вообще, по крайней мере на моих тестах.
      • 0
        Ну если уж уменьшать размер таблицы в памяти…
        • 0
          (почемуто само отправилось раньше времени)
          тогда
          SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RAND() )
          где id — первичный ключ.
          а то выбирать для сортировки все имеющиеся поля явно не айс…
          • 0
            ах да, еще LIMIT:

            SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RAND() LIMIT 0,10)
  • +1
    пришел в голову такой вариант:
    1) добавляем в таблицу еще одно поле типа bigint, например RandKey
    2) создаем по полю RandKey индекс
    2) в RandKey храним просто случайные числа, в диапазоне от 1 до MAX(BIGINT)
    3) Чтоб выбрать 10 случайных записей: (это псевдо-SQL, для простоты объяснения)
    SELECT * FROM table WHERE RandKey>( SELECT RAND(10,MAX(RandKey)-10) FROM table ) LIMIT 10

    надо только ввести поправки, для случаев когда RAND() будет слишком близким к краям диапазона поля RandKey.
    как думаете?
  • +1
    Не хотите в блог MySQL перенести?
    • +1
      Перенес
  • +1
    Неужели затраты на вызов запроса так малы, что 10 запросов выполняются быстрее 1го?
  • +6
    Вы статью написали дабы унизить одного человека и показать свое превосходство? Если нет, то странно, знаете ли. Все эти ругательства, нежелание признавать неэффективность вашего метода. У меня такое чувство, что вы один из тех работодателей-дебилов, которые орут на своих работников.
    • –2
      Вы хам и неадекватны.
      • +2
        Я хам? Наверное я написал топик чтобы унизить конкретного человека? Признайтесь, опере на подчиненных?
        • –2
          Вы говорите какую-то чушь. Ваше желание затеять ругань будет проигнорировано, как и все ваши дальнейшие комментарии, что вы так самозабвенно и с пеной у рта пишите. Неудивительно что у вас в карме одни минусы.
          • +2
            О нет, боже, он мне больше не ответит! Не волнуйтесь, переживу.
            328 голосов и в итоге -10 — это одни минусы? С математикой у вас туго, похоже.

            Все с вами понятно. Строите из себя целку, а на самом деле тиран на работе.
      • 0
        Это Вы неадекват.
        Человек говорит правильно. Ибо показав свою «умность» Вы лишь принизили себя в глазах спецов, ибо полнейшая глупость, работающая немногим быстрее стандартного RAND().

        Ничего другого не признаете.
        Уверен процентов на 99%, что разработчику в ТЗ не ставилось задачи сделать High-Load решения.
  • +2
    У вас столько желчи к аутсорсерам, а сами небось предложили неадекватную оплату типа «написать движок за 300 руб» и ожидали получить сертифицированного профессионала
    • –2
      Никакой желчи к аутсорсерам. Это недовольство конкретно этим человеком и его действиями. А предполагать и додумывать за меня не надо. Тем более оценивать ситуацию не располагая никакими сведениями о том, что это за проект, какая у него цена и какие выплаты сотрудникамм проекта.
      • 0
        А это тоже недовольство конкретным человеком?

        > а аутсорсеры думать не хотят, они работу сдают и идут пропивать деньги
  • +2
    >станет понятно почему я сложил дюжину матершинных ругательств в адрес глупого аутсорсера

    Вы бы лучше их внутреннему оптимизатору запросов MySQL сказали…
    • –3
      Неоднократно складываю. И уже перехожу на Postgre
  • НЛО прилетело и опубликовало эту надпись здесь
    • –11
      Вы хам и неадекват. Вас в клетку надо посадить, а не давать вам комментарии на хабре писать.
    • 0
      коротко и ясно размазали, а главное по делу) респект
  • –3
    Это очевидно. Как говорил кое-кто, даже недоразвитый восьмиклассник догадается.
  • +5
    Простите конечно, но за запросы в цикле => Нужно сразу отрывать руки)))
    А то что написал ,, аутсорсер,, считаю удовлетворительным.
    • –1
      Проведите тесты и подумайте еще раз. А еще почитайте текст топика — где вы запросы в цикле нашли?
  • +8
    Здравствуйте.

    Извините, но ваше решение отвратительно.

    Чтобы выбрать 10 случайных записей, вы делаете 11 операций, которые в терминологии баз данных называются «full scan» (полное чтение таблицы)

    SELECT COUNT(*) FROM tTable — раз фуллскан. база данных просматривает все записи, чтобы узнать их количество. (Да, база не получает данные строк, но сканировать начала записей ей надо.)
    SELECT * FROM tTable LIMIT X, 1 — то же самое, еще 10 сканирований таблицы от начала до числа X.

    Время работы вашего алгоритма прямо пропорционально количеству записей. Для больших баз данных (скажем, со 100 миллионами строк) он не пригоден. Для маленькой базы ваш алгоритм не намного лучше алгоритма бедного аутсорсера, зато содержит больше строк кода. Удивительно, почему нельзя сделать индекс с номером записи и отбирать по нему.

    И еще — некрасиво публично называть кого-то глупым.
    • –2
      Во-первых SELECT COUNT(*) FROM tTable не сканирует записи таблицы. Оптимизатор в этом случае выдает кол-во строк из метаданных таблицы (читайте мануал). Во-вторых я четко обозначил условия когда меня об этом спросили в одном из комментариев — записи часто удаляются и часто вставляются, поэтому поддержка дополнительного индекса неприемлема. Ну и в-третьих, выборка типа LIMIT X, 1 оптимизируется MySQL очень хорошо — идет не ресурсоемкий спуск по индексу (если в таблице есть хоть один индекс/ключ). Опять же, погуглите.
      • 0
        1) Рандомайзер выбирает последнюю запись
        2) Параллельный процесс удаляет последнюю запись
        3) Выборка не находит ничего.
        • –1
          С этим можно смириться и посчитать маловероятным. А можно легко исключить эту ситуацию выполняя запрос кол-ва записей и выборку произвольных в одну транзакцию.
          • +1
            О-ло-лол. У вас же только что был был высоко нагруженный проект с частыми вставками и удалениями? Хотя вы правы в том, что обойти такую ситуацию проще простого.
            • 0
              А как такую ситуацию обойти проще простого, кроме как сделать повторную попытку?
              Такая история ведь может произойти не только с самой последней записью — а если в промежутке удалили 10 записей? А если кто-то слелал КОММИТ (предположим, у них база транзакционная) и снес 1000 записей?
              Алгоритм повторных попыткок — это алгоритм из серии WHILE TRUE. А такие алгоритмы — это всегда плохие алгоритмы, и не только теоритически.
            • 0
              И еще. Если то, что запись не будет найдена с первой попытки — это не страшно, то почему бы не воспользоваться нумерацией и поиском по ключу? Ну, не найдет запись с первой попытки — найдет со второй. Зато поиск по индексу — ну ооочень быстро.
              • 0
                Ну вы меня за весь подход автора не спрашивайте, я только сказал что в случае промаха нет ничего страшного.
                Если удалений не много, можно использовать и первичный ключ.
          • +3
            Вот, вы и попались!
            Транзакция с блокировкой записи (а получается, что нужна такая) потребует этот процесс ждать, пока параллельные процессы закончат запись, чтобы захватить блокировку, а остальные процессы, которые хотят удалить или вставить строку, будут стоять в очереди, а кого-то вообще умирет по таймауту.
            Производительность будет никакая.
            Я куда клоню. Вы стоите в самом начале пути к довольно сложной вещи, как базы данных, а уже кого-то называете глупым.
            • –5
              Ясно, вы еще один представитель множества детей, которым надо во что бы то ни стало поспорить, причем поспорить не о решении проблемы а том что вы умнее всех. Ваши высокомерные комментарии со снисходительными упреками смешны и глупы. Растите над собой, премудрый вы наш :)
              • +2
                WhiteD: 25 августа 1985
                Nulldevice: 27 октября 1978

                ребеночек…
                • –4
                  В голове может быть не то что по паспорту.
            • –3
              А насчет транзакции с блокировкой — читайте мануалы. Остальные треды не заблокируются, просто результат их работы сначала попадет в лог, а потом уже актуализируется после снятия блока.
              • 0
                Поскольку я старший администратор баз данных, то мне чтение мануалов очень часто заменяют письма разгневанных пользователей (а иногда и сами пользователи очно) со словами «Почему все висит?». Вот такие вот «разработчики» вычитают в мануалах, что, оказывается, «при транзакции параллельные процессы и не ждут вовсе, а делают по-быстрому какие-то результаты работы». Не объяснишь ведь менеджерам, что я тут не причем.

                На собеседовании мы таким «software engineer», которые транзакций и блокировок не понимают, не говорим, что они глупые. Но на работу не берем.
                • –5
                  Пустые слова. Я сказал что происходит на самом деле и как это описанов документации. Вы же решили расскзать какой вы замечательный и кем вы работаете. Глупо.
                  • +6
                    Ты запарил всех обзывать хамами и идотами.
  • 0
    15 000 000 записей в таблице, по 3-8к ежечасно новых, 10-15к в сутки удаляется. Выборки от 10шт до 3к.

    собственно пока в системе мало пользователей — ORDER BY RAND() самое шоколадное. Но вот куда расти дальше — пока не ясно.

    • 0
      дальше — кеширование? :)
      • 0
        оно тут бессмысленно. кто кэшировать — результаты? они должны быть случайны в каждой выборке…
        • 0
          а ведь действительно, если не нужна совсем настоящая случайная последовательность, то можно и сделать дополнительное поле:
          а) либо его раз в сутки (или чаще) забивать значением RAND()
          б) в моем случае взяли crc32 важных данных записи, которое записывается при вставке и потом не меняется.

          Во втором случае удобно — что перетрясать таблицу не нужно каждый день, а просто брать order by по этому полю.
        • 0
          как уже писали ниже — вы можете держать в памяти таблицу и из нее делать выборку, хоть всех данных, хоть одних primary keys

          потом, если у вас 50 одновременных запросов на случайную выборку — ничего страшного если все 50 юзеров увидят одни и те же случайные результаты? важно что результаты случайные, не важно какие именно, ведь так?
          • 0
            ну 50 юзверей я просто не пущу в панель :)) и всё-таки при таком большом объеме одновременных выборок лучше разделить таблицу на несколько. Будет некий рандом внутри одной группы, не заходящий в другую.
        • 0
          А почему бы и не кешировать результаты? Если конечно это не жесткое требование «должны быть случайны в каждой выборке». Скажем, имеете 15 млн записей, даже если в памяти у Вас в кеше хотя бы несколько тысяч — этого уже достаточно чтобы пользователю устроить случайный хоровод из этих значений. А кеш можно время от времени обновлять.
    • 0
      Дальше — храните id всех этих миллионов записей в кеше в памяти, 15 млн id — это 60 Мб массив. Когда нужно выдать набор случайных — извлекайте id из кеша, и делайте например запрос вида SELECT… WHERE id IN (..., ..., ...). Будет работать практически мгновенно.

      Добавляемые записи накапливайте в отдельный массив, и раз в N времени перестраивайте основной, добавляя или удаляя из него записи в зависимости от изменений.

      Про ORDER BY RAND() рекомендую в данном случае забыть.

      Но вообще, как я и писал в сообщениях ниже, всегда, если нужно выдавать случайное значение из миллионов вариантов, стоит задуматься, а действительно ли нужна тут случайность по бизнес-логике?
      • 0
        да, в принципе кэш Primary Key таблицы тоже вариант. И выше вариант с частью этих ключей — вполне нормальная выборка для небольшой группы.

        Но даже выборка ID из кэша — тот еще велосипед получается — нужна же не последовательность с которой они добавлены, а случайная. Разве нет?
        • 0
          Нет, случайная последовательность не нужна, в порядке добавления — вполне достаточно. Нужно только знать количество элементов этого массива, и сгенерировать нужное количество случайных индексов (от 0 до N-1). Затем по этим индексам извлечь id из массива, и уже затем по этим id извлечь записи из базы данных.

          Ну смотрите, допустим есть массив из N = 15000000 элементов.
          1. получаете случайный индекс: i = rand(0, 15000000 — 1)
          2. извлекаете id: id = cache[i], или id = getIdFromCache(i) в зависимости от реализации

          То есть порядок элементов для случайной выборки не важен.
          • 0
            Вооот, вот и получается что для выборки в 3к записей придется делать 3к запросов к базе. Не лучше…
            • 0
              Нет, не придется. Я же написал выше, что можно использовать WHERE id IN (..., ..., ...). То есть запросов будет намного меньше (возможно, даже только один, это по ситуации, нужно замерять).
  • +3
    В конце дня лучшими программистами оказываются не те, кто написал самый потрясающе-элегантный, умопомрачительно-инновационный код. Настоящие рок-звёзды сдают проекты до намеченного срока и дешевле отведённого бюджета, и это именно то, в чём нуждается бизнес. И это именно те, кем все мы должны стремиться стать.
    Откуда-то скопипастил. откуда не помню Но думаю как раз в тему будет. Есть над чем подумать!
  • 0
    # Чистый SQL

    SELECT * FROM Table where TableID in (round(rand() * 2000), round(rand() * 2000), round(rand() *

    2000), round(rand() * 2000), round(rand() * 2000), round(rand() * 2000), round(rand() * 2000),

    round(rand() * 2000), round(rand() * 2000), round(rand() * 2000))

    Это для случая, когда надо 10 записей из таблицы с 2000 записей.

    # PHP + SQL
    $recordsCount = 2000; // Переменная устанавливается запросом select count(*) from Table
    $recordsLimit = 10;
    $randomIDs = array();
    for ($i = 0; $i < $recordsLimit; $i++) {
    $randomIDs[] = rand(1, $recordsCount);
    }
    $queryText = 'SELECT * FROM Table where TableID in ('. join(', ', $randomIDs). ')';
    // И т.д.
    • 0
      Ай-ай-ай! Ну как так можно!

      1) Поймите, как работает оператор IN. Он не работает как «выбрать по индексу запись 1, выбрать по индексу запись 2, и т.д.» Он работает так — «просмотреть ВСЕ записи и проверить, не находятся ли они в списке IN» — то есть это fullscan — полное сканирование таблицы. Кстати, проверить, является ли ваш алгоритм полным чтением таблицы, можно и без вещей вроде разбора плана запроса. Просто замерьте время для 10000 записей, а потом для 100000. Если время одного порядка — то вы молодец, если время выросло в 10 раз — читается вся таблица и вы не молодец

      2) ваш алгоритм не гарантирует 10 записей. если дважды выпадет одинаковый ключ — вы получите 9 записей. Теоретически, если экспериментировать порядка возраста Вселенной — можно вернуть вообще одну запись когда-нибудь :)
      • 0
        1) Я в курсе, как он работает во многих типах СУБД. Если запрос идет по колонке, которая в индексе, например, PK, то он смотрит в индекс. А если колонка не проиндексирована, то только тогда идет полный просмотр.

        Попробуйте сделать подобный запрос для индексированной и неиндексированной колонок и посмотрите в explain

        2) Это уже детали, чтобы проверить — Вы же мне не платите, чтобы я качественный код выдавал?
  • –1
    WhiteD, добавьте чтоли апдейт в статью, не все комментарии читают. В комментариях неоднократно указали как правильно.

    На всякий случай уточняю. Выборка одной записи:

    $max_id = select id from table order by id desc; // max id, в такой записи не зависит от того оптимизирован ли max() в конкретной реализации

    $rand_id = rand(1, $max_id)

    select <нужные поля> from table where id >= $rand_id limit 1

    для нескольких записей, думаю понятно.
    • 0
      Упс…

      $max_id = select id from table order by id desc limit 1
    • +1
      В комментариях неоднократно указали как правильно.

      И что тут правильного?
      Как будет выглядеть результат например на таблице с такими id: 1, 2, 3, 1000? Что мы будем видеть? В 997 из 1000 просмотров будет запись с id 1000. Это правильно? На мой взгляд тут вообще нету единого правильного решения. Все зависит от конкретной ситуация, от того, сколь в таблице строк, как часто удаляются эти строки, насколько важно, что бы строки выдавались с действительно равной вероятностью и т.п. Предложенное автором решение вполне рабочее и имеет право на жизнь, как выдаваемое вами за «правильное», но они не универсальны.
      • 0
        Про универсальность никто и не говорил. Как вы правильно подметили — все решения имеют право на жизнь и некоторые из них вполне применимы при определенных условиях. В тексте топика я сделал ошибку и не выписал в отдельный параграф всех поставленных передо мной условий. В комментарии habrahabr.ru/blogs/mysql/54176/#comment_1453063 я резюмировал все что упустил в топике. Но ничего не поделаешь, я уже дал пищу для хабровских троллей и они уже с пеной у рта начали кричать «все дураки, один я умный». Увы и ах…
      • 0
        Правильное с точки зрения производительности, если нужно «показать что-то от балды». Именно для этого начинающие используют order by rand().

        Если нужно выдерживать распределения, то для этого надо строить вспомогательные таблицы строить.

        Если же пофиг на производительность — то и order by rand() покатит.
  • 0
    Сталкивался с подобной проблемой. Применил следующее:
    1. Ввёл в таблицу дополнительный столбей int(11)
    2. Раз в сутки этот столбец обновляется и в него пишутся значения rand
    3. Выборка идёт примерно следующая: SELECT * FROM table WHERE rand_field > RAND() LIMIT 10

    Таким образом функция RAND() вызывается один раз, нет копирования записей в темповую таблицу, обновление «случайного» столбца происходит ночью, когда большенство пользователей спит
    • 0
      Решение достаточно нестабильно. Некоторые выборки в некоторые дни будут возвращать меньше 10 записей (чем больше записей в таблице, тем вероятность этого меньше, но есть всегда). Плюс, интуиция подсказывает, что выборка не совсем равновероятная в рамках одного дня.

      Но вцелом, тоже мысль.
  • +9
    Данный аутсорсер вовсе не ленивый и не глупый. Он применил наиболее надежное решение из всех возможных, при этом соблюдая приемлемую производительность и потратив на данное решение минимум времени с средств заказчика.

    Остальные вышеуказанные решения или ненадежны, как было указано выше, или еще более неэффективны с точки зрения производительности, например требуют блокировок или транзакций с уровнем изоляции минимум repeatable read.

    А решение автора статьи вообще категорически неприемлемо IMHO. Например, хорошо если нужно извлечь 10 случайных записей. А если нужно извлечь 100 или 1000 случаных, что тогда? Кошмар получится. Не говоря о том, что при этом для надежности нужно или блокировать таблицу или использовать ту же транзакцию с уровнем изоляции repeatable read или serializable. И не говоря о том, что нужно учитывать возможные повторы.

    Приемлемым и надежным решением может быть использование хранимой процедуры, которая использует курсор (нужен минимум MySQL 5). Например так:

    1. получаем количество записей, допустим N
    2. получаем M (в нашем случае M = 10) случайных значений от 0 до N — 1 и помещаем их в массив в порядке возрастания
    3. открываем курсор вида DECLARE cur CURSOR FOR SELECT id,<другие_поля> FROM test
    4. так как полученные нами случаные значения расположены по порядку, то вычитываем курсором по очереди все записи, и когда встречаются номера записей, соответствующие нашим выбранным случайным значениям, сохраняем эти записи.
    5. когда выбрали запись, номер которой соответствует последнему случайному значению, закрываем курсор.
    6. все, мы за один проход выбрали все M случайных записей, при этом не использовали никаких сортировок

    Таким образом, если мы имеем миллионы записей, и номера случайных записей будут небольшими, то такая выборка будет очень быстрой. В большинстве случаев это решение считается оптимальным.

    Но вообще скажу по секрету, что если в высокозагруженной системе нужно делать частые выборки случайных записей из некоторой таблицы или вьюва, даже если это миллионы записей, то намного правильнее хранить ID этих записей (а если позволяют ресурсы, то и таблицу целиком, но это уже опционально) в памяти в кеше, и делать поиск случайных записей именно в памяти, затем при необходимости извлекая записи из таблицы сразу по ID. Даже если там 10 млн записей, это всего лишь 40 Mb в кеше, не так уж и много для выделенного для данной задачи сервера.

    Обычно, по опыту, в бизнес-проектах не требуется получать случайные некешируемые выборки из миллионов записей при каждом запросе. Даже если это огромная баннерная система, но и там совсем другие принципы, и «случайные» выборки вовсе не случайны… То есть мне воистину сложно представить себе бизнес-задачу, где нужна была бы выборка именно случайных значений из базы данных при каждом запросе пользователя, скорее всего тут имеет место проблема аналитической стадии и проектирования.

    P.S. Называть кого-то глупым из-за написанного им кода считаю неправильным в принципе. По карйней мере автор, который привел свой неприемлемый вариант решения, уж точно не имеет права так делать IMHO. Пожалуйста, без обид, все мы учимся на ошибках, и я тоже могу ошибаться.
    • 0
      > и я тоже могу ошибаться.
      И ошибаетесь. Опять же без обид.
      Приведенный вами выше алгоритм невозможно реализовать, используя возможности написания хранимых процедур в MySQL.
      Моя ошибка в том, что я не обозначил рамки использования моего варианта решения в тексте топика, но оговорил их в одном из комментариев. Про проектирование и анализ/постановку задачи и говорить нечего — железное решение «правящих менеджеров», с которым не поспоришь.

      Повторю на всякий случай условия и рамки: выборка абсолютно произвольна (вариант с выборкой по первичному ключу отпадает, с условием его разряженности), кол-во записей большое, но не гигантское (порядка 100 000 — 500 000 записей, и не зависит от масштабов и роста проекта), частота вставок и удалений записей относительно велика (порядка 20 записей в минуту, поддержка второго суррогатного не разряженного индекса неприемлема в виду частых обновлений). При обозначенных выше условиях мой вариант при тестировании показал наилучшие результаты. И если уж говорить о грамотности стороннего разработчика — то он изначально знал условия и особенности работы его кода, но все равно применил столь не производительный вариант (как потом выяснилось из личного общения — по незнанию).

      ЗЫ: вы правы насчет кеширования результатов. В итоге пришлось применить (в обход требований менеджмента) кеширование произвольно выбранных записей, но на порядок большего кол-ва чем требуется. При каждом же выводе результатов происходит произвольная выборка нужного кол-ва записей из результатов первого (большого) набора.
      • 0
        Вы уверены, что это невозможно реализовать? Я только что попробовал, успешно. MySQL 5.1.30.

        Курсоры в MySQL работают прекрасно, хотя и имеют ряд ограничений (например, нет возможности пропускать записи, что сразу убивает идею использовать курсоры в полной мере для данной задачи) и относительно тормозные. Только что написал тест, извлечение 10 случайных записей из 500.000 с использованием курсоров обработка занимает 3.20 sec. Это конечно медленнее чем даже предложенный автором пример, но зато если, например, нужно было бы вернуть, скажем, 100 случайных, то это заняло бы примерно те же 3.20 sec, а другие примеры будут все более и более тормозить (пример
        автора, скажем, выполняется более 10 секунд на моем наборе данных).
      • +5
        Лабораторная работа N1, случайные выборки из таблицы.

        Создаем таблицу table1 с первичным ключом id, генерим 500.000 записей.
        Делаем ряд запросов (тесты проводим с MyISAM и InnoDB без блокировок и транзакций), запускаем по 10 раз, вычисляем среднее и минимальное время.

        1. Делаем запрос с ORDER BY RAND()

        SELECT id FROM table1 ORDER BY RAND() LIMIT 10;

        MyISAM 0.78 sec
        InnoDB 1.20 sec

        2. Делаем запрос, предложенный автором, с использованием UNION

        (SELECT id FROM table1 LIMIT 10000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 60000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 110000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 160000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 210000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 260000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 310000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 360000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 410000, 1)
        UNION
        (SELECT id FROM table1 LIMIT 460000, 1);

        MyISAM 1.31 sec
        InnoDB 2.90 sec

        3. Ускоряем предыдущий запрос, заменяем UNION на UNION ALL

        MyISAM 1.31 sec
        InnoDB 2.89 sec

        4. Используем один запрос с WHERE id IN (...) вместо набора запросов, объединенных по UNION.

        SELECT id FROM table1 WHERE id IN (10000, 60000, 110000, 160000, 210000, 260000, 310000, 360000, 410000, 460000);

        MyISAM 0.00 sec (менее 10 миллисекунд)
        InnoDB 0.01 sec

        Выводы.

        Способ N1 с ORDER BY RAND(), несмотря на то что он очень неэффективный, работает очень надежно, гарантированно выдавая результат независимо от состояния набора данных, в том числе и при условии конкурентного доступа.

        Для способов N 2-4 придется или использовать а) предварительный запрос SELECT COUNT(...) и б) блокировки (для MyISAM) или транзакции с уровнем изоляции repeatable read или sirializable (для InnoDB) чтобы гарантировать корректный результат в условиях конкурентного доступа к данным. Если это делать, то время выполнения этих запросов можно увеличить еще примерно на 50%.

        Использование набора запросов, объединенных через UNION, работает медленнее на больших наборах данных чем даже тормозной ORDER BY RAND().
        UNION ALL позволяет выиграть несколько микросекунд, но проблему не решает.

        Самый эффективный способ — вычислить заранее случайные значения первичных ключей, и делать запрос по ним. При этом нужно не забывать про описанный выше конкурентный доступ (записи могут быть удалены после того как выполнен запрос SELECT COUNT и вычислены значения ключей, но еще не выполнен основной запрос).

        Предложенный автором способ действительно работает немного быстрее на
        небольшом количестве записей, но в средних и больших таблицах (от 200-500 тыс. записей) уступает даже неэффективному ORDER BY RAND() если нужно получить несколько случайных значений, и чем больше значений нужно получить, тем более неэффективен этот метод.

        Вот, почти что статья получилась ))
      • 0
        Дополнение к лабораторной работе N1.

        Как и подсказали выше (TimTowdy), если сужать исходный набор данных, используя например WHERE RAND() < 0.01, то ORDER BY RAND работает значительно быстрее. Например, на используемом мной в примере выше c 500.000 записями запрос

        SELECT id FROM table1 WHERE RAND() < 0.1 ORDER BY RAND() LIMIT 10;

        отрабатывает за 0.34 sec, почти в 2.5 раза быстрее.

        или например

        SELECT id FROM table1 WHERE id % 100 = 0 ORDER BY RAND() LIMIT 10;

        отработает за 0.30 sec (в том случае 0 — это вычисленное ранее случайное значение из диапазона 0..99).

        Но все равно это не хороший путь, я не спорю. Хотя и его нельзя отвергать в ряде случаев.
        • 0
          order by rand() это temporary table + filesort соответственно есть резкий переход от использования памяти к диску, который зависит от размера буферов. четко предсказать когда этот переход произойдет невозможно, разве что провести эксперимент для конкретного запроса с конкретными настройками и конкретными данными
  • 0
    У меня была аналогичная задача, только надо было не случайно выбирать линии, а последовательно. При этом выборки должны быть закольцованы, а в последовательности ключей регулярно появляются «дыры». Решил примерно вот так:

    	$cntcache = 'current-state/laste';
    	if (file_exists($cntcache)){
    		$lastID = file_get_contents($cntcache);
    	} else $lastID = 0;
    
    	for($i = 0; $i < 2; $i++){
    		$lines = mysql_query( "SELECT id FROM table WHERE id > '$lastID' LIMIT 1");
    		if (mysql_num_rows($lines) != 0){
    			$line = mysql_fetch_row($anketes);
    			$lastID = $line[0];
    			break;
    		} else $lastID = 0;
    	}
    	file_put_contents($cntcache, $lastID);
    

    Может кто-то подскажет способ лучше и производительнее?
  • 0
    Я храню в мемкеше массив с айдишниками, оттуда беру и мешаю, дальше IN().

    Вроде шустро бегает.
  • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    «Глупый и ленивый аутсорсер» решает поставленную задачу. Максимально простым для него способом. Поскольку его время — его деньги, а за «проявление смекалки» его никто никогда не поблагодарит. Может быть «умный и работящий постановщик» поленился расписать требования и поставил задачу неправильно?

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.