Ну, это очень старый вопрос, который никогда не получал реального решения. Мы хотим, чтобы 3 случайные строки из таблицы содержали около 30 тыс. Записей. Таблица не такая большая, с точки зрения MySQL, но если она представляет продукты магазина, она является представительной. Случайный выбор полезен, когда вы представляете 3 случайных продукта на веб-странице, например. Мы хотели бы, чтобы единственное SQL-строковое решение отвечало следующим условиям:
- В PHP набор записей с помощью PDO или MySQLi должен иметь ровно 3 строки.
- Они должны быть получены одним запросом MySQL без использования хранимой процедуры.
- Решение должно быть быстрым, например, как оживленный сервер apache2, MySQL-запрос во многих ситуациях является узким местом. Поэтому он должен избегать создания временных таблиц и т.д.
- 3 записи должны быть не смежными, т.е. они не должны находиться рядом друг с другом.
В таблице есть следующие поля:
CREATE TABLE Products (
ID INT(8) NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) default NULL,
HasImages INT default 0,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ограничение WHERE - это Products.HasImages = 1, позволяющее извлекать только записи, на которых есть изображения, доступные для показа на веб-странице. Примерно одна треть записей соответствует условию HasImages = 1.
Поиск совершенства, сначала отбросим существующие решения, у которых есть недостатки:
I. Это базовое решение с помощью ORDER BY RAND(),
слишком медленный, но гарантирует 3 действительно случайных записи в каждом запросе:
SELECT ID, Name FROM Products WHERE HasImages=1 ORDER BY RAND() LIMIT 3;
* CPU около 0,10 с, сканирование 9690 строк из-за предложения WHERE, использование где; Использование временных; Использование filesort, в Debian Squeeze Double-Core Linux, не так уж плохо, но
не настолько масштабируема для более крупной таблицы, поскольку временная таблица и filesort используются и берут меня 8.52 для первого запроса в тестовой системе Windows7:: MySQL. С такой низкой производительностью, чтобы избежать для веб-страницы не-это?
II. Яркое решение riedsio с использованием JOIN... RAND(),
from MySQL выбирает 10 случайных строк из 600K строк быстро, адаптированные здесь действительны только для одной случайной записи, так как следующий запрос приводит к почти всегда смежные записи. Фактически он получает только случайный набор из 3 непрерывных записей в идентификаторах:
SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT (RAND() * (SELECT MAX(ID) FROM Products)) AS ID)
AS t ON Products.ID >= t.ID
WHERE (Products.HasImages=1)
ORDER BY Products.ID ASC
LIMIT 3;
* ЦП около 0,01 - 0,19 с, сканирование 3200, 9690, 12000 строк или около того случайным образом, но в основном 9690 записей, используя где.
III. Лучшее решение выглядит следующим образом: WHERE... RAND(),
видно на MySQL выбирает 10 случайных строк из 600K строк быстро, предложенных bernardo-siu:
SELECT Products.ID, Products.Name FROM Products
WHERE ((Products.Hasimages=1) AND RAND() < 16 * 3/30000) LIMIT 3;
* CPU около 0.01 - 0.03s, сканирование 9690 строк, Использование где.
Здесь 3 - количество желаемых строк, 30000 - RecordCount таблицы Products, 16 - экспериментальный коэффициент, чтобы увеличить выбор, чтобы гарантировать выбор трех записей. Я не знаю, на каком основании коэффициент 16 является приемлемым приближением.
В большинстве случаев мы получаем 3 случайные записи, и это очень быстро, но это не оправдано: иногда запрос возвращает только 2 строки, иногда даже никакой записи.
Три вышеуказанных метода проверяют все записи таблицы, следующей за предложением WHERE, здесь 9690 строк.
