Главная » Категории » MySQL

MySQL: Выбор случайных записей

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

  1. SELECT * FROM table_name ORDER BY RAND() LIMIT 5


и в результате мы получим 5 записей. На первый взгляд все хорошо, но может быть здесь все же есть какие-то подводные камни? Возможно и есть, не зря же статья на этом не заканчивается. Давайте попробуем выполнить этот запрос в какой-то степени большой базе данных. В моем распоряжении имеется MySQL 5.1 и база данных KLADR (классификатор адресов). Таблица STREET содержит чуть меньше 1 миллиона записей. Над ней то мы и будем проводить эксперименты. Попробуем выбрать таким способом 5 случайных записей:

  1. mysql> SELECT `NAME` FROM street ORDER BY RAND() LIMIT 5;
  2. +-----------------+
  3. | NAME |
  4. +-----------------+
  5. | Центральная |
  6. | гк 30 ПК |
  7. | Коммунальная |
  8. | Молодежная |
  9. | Дж.Висингириева |
  10. +-----------------+
  11. 5 rows IN SET (9.14 sec)


И что же мы видим, запрос на выборку каких-то 5 записей отнял у нас 9 секунд! Все это время я наблюда как беспрерывно горел индикатор HDD на сервере и слышал треск каретки винчестера. Теперь таблица находится в кэше и такого "серьезного" обращения к жесткому диску больше быть не должно, давайте попробуем выполнить этот запрос еще раз, что-то мне подсказывает что теперь он выполнится быстрее:

  1. mysql> SELECT `NAME` FROM street ORDER BY RAND() LIMIT 5;
  2. +------------+
  3. | NAME |
  4. +------------+
  5. | Кубанская |
  6. | Линховоина |
  7. | Мира |
  8. | Набережная |
  9. | Вотинова |
  10. +------------+
  11. 5 rows IN SET (3.38 sec)

И действительно, теперь все почти в 3 раза быстрей, но все равно, три секунды это очень долго для запроса на выборку...

Многие уже наверно смекнули, а что если сгенерировать 5 чисел и использовать оператор IN? При этом следует учесть, что каждое число должно быть не больше максимально возможного значения пронумерованного поля (как правило это поле ID). Запрос будет выглядеть следующим образом:

  1. mysql> SELECT `name` FROM street WHERE id IN (4, 5, 2233, 483984, 54894);
  2. +------------------+
  3. | name |
  4. +------------------+
  5. | Вахтовый поселок |
  6. | Гостиная |
  7. | Аграрная |
  8. | Дачная |
  9. | Алданская 3-я |
  10. +------------------+
  11. 5 rows IN SET (0.06 sec)


Как видите запрос занял всего 0.06 секунд! Не обошлось и без помощи индесков, которые в случае с перывм запросом были просто бесползены. Но опять есть одно "но", что если мы удаляли записи из таблицы, тогда в поле ID будут "дыры", тоесть могла получиться такая ситуация, что записи с полем id = 4 мого и не существовать, тогда результатом выполнения данного запроса было бы всего 4 записи. В таком случае конечно можно проверить количество выбранных записей и "довыбрать" и так до тех пор пока в нашем распоряжении не окажется нужное нам количество записей. Опять же одно "но", но что если записи в таблице довольно часто добавляются и удаляются, тогда вообще нет гарантий, что наш скрипт выполнится быстро, и выполнится ли вообще. Значит этот способ подходит не для всех случаев, и если же наш случай не позволяет его использовать (тоесть как уже говорилось данные из таблицы часто и много удаляются) попробуем придумать что-нибудь еще.

  1. mysql> (SELECT `NAME` FROM street WHERE ID >= 4 LIMIT 1)
  2. -> UNION (SELECT `NAME` FROM street WHERE ID >= 5 LIMIT 1)
  3. -> UNION (SELECT `NAME` FROM street WHERE ID >= 23492 LIMIT 1)
  4. -> UNION (SELECT `NAME` FROM street WHERE ID >= 433443 LIMIT 1)
  5. -> UNION (SELECT `NAME` FROM street WHERE ID >= 23232 LIMIT 1);
  6. +------------------+
  7. | NAME |
  8. +------------------+
  9. | Вахтовый поселок |
  10. | Гостиная |
  11. | Заозерная |
  12. | Центральная |
  13. | Лермонтова |
  14. +------------------+
  15. 5 rows IN SET (0.06 sec)


В данном случаем нам опять же потребуется 5 рандомно сгенерированных числа. Смотрим что происходит: по сути делаем 5 селектов но за один запрос, объединенных с помощью UNION. Время выполнения примерно то же (оно конечно варьируется если выполнить несколько запросов, но средний показатель все же увидеть можно). Как вы видите, запрос построен так, что при отсуствии "нужного" нам ID мы не получим пустой результат. Правда может получиться такая ситуация, что запрос вернет 2 (или более раз, но менее вероятно) одинаковых значения (к примеру выбирали 4 5 10 66 55, запись с ID=4 была удалена и в этом случае запись с ID=5 будет участвовать в полученных данных 2 раза), но при таблицах очень большого объема эта вероятность очень и очень мала и если она недопустима то можно сделать и проверку, она займет мало ресурсов, т.к. никаких запросов делать больше не надо, а достаточно получить в запросе все ID которые выбрали и сравнить их.

Какой из этих вариантов выборки применить к конкретной задаче - решать вам ;)

Категория: MySQL Опубликовано: Hett в Sat, 15/10/2011 - 00:00
Первый нах
Опубликовано: Hett в Sun, 16/10/2011 - 18:30
Второй нах
Опубликовано: Hett в Sun, 16/10/2011 - 18:30
ТЕст
Опубликовано: Hett в Mon, 17/10/2011 - 20:59
ТЕст
Опубликовано: Hett в Mon, 17/10/2011 - 21:00
ффф
Опубликовано: Hett в Mon, 17/10/2011 - 21:26
ффф
Опубликовано: Hett в Mon, 17/10/2011 - 21:26
ффф
Опубликовано: Hett в Mon, 17/10/2011 - 21:26