Ну, это очень старый вопрос, который никогда не получал реального решения. Мы хотим, чтобы 3 случайные строки из таблицы содержали около 30 тыс. Записей. Таблица не такая большая, с точки зрения MySQL, но если она представляет продукты магазина, она является представительной. Случайный выбор полезен, когда вы представляете 3 случайных продукта на веб-странице, например. Мы хотели бы, чтобы одно стандартное SQL-решение отвечало следующим условиям:
Таблица имеет следующие поля:
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.
В поисках совершенства мы сначала отбросили существующие решения, у которых есть недостатки:
слишком медленный, но гарантирует 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. С такой плохой производительностью, чтобы избежать веб-страницы, это не так?
из 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;
* CPU около 0,01 – 0,19 с, сканирование 3200, 9690, 12000 строк или около того случайным образом, но в основном 9690 записей, с использованием где.
замеченный в MySQL, выберите 10 случайных строк из 600K строк, предложенных bernardo-siu :
SELECT Products.ID, Products.Name FROM Products WHERE ((Products.Hasimages=1) AND RAND() < 16 * 3/30000) LIMIT 3;
* ЦП около 0,01 – 0,03 с, сканирование 9690 строк, Использование где.
Здесь 3 – количество желаемых строк, 30000 – RecordCount таблицы Products, 16 – экспериментальный коэффициент, чтобы увеличить выбор, чтобы гарантировать выбор трех записей. Я не знаю, на каком основании коэффициент 16 является приемлемым приближением.
В большинстве случаев мы получаем 3 случайные записи, и это очень быстро, но это не оправдано: иногда запрос возвращает только 2 строки, а иногда даже вообще никакой записи.
Три вышеуказанных метода проверяют все записи таблицы, в которой заключено предложение WHERE, здесь 9690 строк.
Уродливые, но быстрые и случайные. Может стать очень уродливым очень быстро, особенно с настройкой, описанной ниже, поэтому убедитесь, что вы действительно этого хотите.
(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 LIMIT 1) UNION ALL (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 LIMIT 1) UNION ALL (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 LIMIT 1)
Если у вас большие пробелы между идентификаторами в таблице, строки сразу после таких пробелов будут иметь больше шансов получить этот запрос. В некоторых случаях они появятся значительно чаще, чем должны. Это не может быть решено в целом, но есть исправление для общего частного случая: когда между 0 и первым существующим ID в таблице существует разрыв.
Вместо подзапроса (SELECT RAND()*<max_id> AS ID) используйте что-то вроде (SELECT <min_id> + RAND()*(<max_id> - <min_id>) AS ID)
Запрос, если он используется как есть, может возвращать повторяющиеся строки. Этого можно избежать, используя UNION вместо UNION ALL . Таким образом, дубликаты будут объединены, но запрос больше не гарантирует возврата ровно 3 строки. Вы можете обойти это тоже, извлекая больше строк, чем вам нужно, и ограничивая внешний результат следующим образом:
(SELECT ... LIMIT 1) UNION (SELECT ... LIMIT 1) UNION (SELECT ... LIMIT 1) ... UNION (SELECT ... LIMIT 1) LIMIT 3
Тем не менее, по-прежнему нет никакой гарантии, что 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;
Разумеется, вышеприведенные «близкие» непрерывные записи вы каждый раз кормите его одним и тем же ID не обращая особого внимания на seed функции rand .
Это должно дать больше «случайности»,
SELECT Products.ID, Products.Name FROM Products INNER JOIN (SELECT (ROUND((RAND() * (max-min))+min)) AS ID) AS t ON Products.ID >= t.ID WHERE (Products.HasImages=1) ORDER BY Products.ID ASC LIMIT 3;
Где max и min – два значения, которые вы выберете, скажем, например, сакэ:
max = select max(id) min = 225
Этот оператор выполняется очень быстро (19 мс в таблице 30 тыс. Записей):
$db = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'username', 'password'); $stmt = $db->query("SELECT p.ID, p.Name, p.HasImages FROM (SELECT @count := COUNT(*) + 1, @limit := 3 FROM Products WHERE HasImages = 1) vars STRAIGHT_JOIN (SELECT t.*, @limit := @limit - 1 FROM Products t WHERE t.HasImages = 1 AND (@count := @count -1) AND RAND() < @limit / @count) p"); $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
Идея состоит в том, чтобы «ввести» новый столбец со случайными значениями, а затем отсортировать по этому столбцу. Генерация и сортировка по этому введенному столбцу выполняется быстрее, чем команда «ORDER BY RAND ()».
Там может быть одно предостережение: вы должны дважды включить запрос WHERE.
Как создать другую таблицу, содержащую только элементы с изображением? Эта таблица будет намного легче, так как она будет содержать только одну треть предметов, которые есть в оригинальной таблице!
------------------------------------------ |ID | Item ID (on the original table)| ------------------------------------------ |0 | 0 | ------------------------------------------ |1 | 123 | ------------------------------------------ . . . ------------------------------------------ |10 000 | 30 000 | ------------------------------------------
Затем вы можете сгенерировать три случайных идентификатора в части кода PHP и просто получить из базы данных.
Я тестировал следующую группу SQL-запросов в 10-мегапиксельной, плохо разработанной базе данных.
SELECT COUNT(ID) INTO @count FROM Products WHERE HasImages = 1; PREPARE random_records FROM '( SELECT * FROM Products WHERE HasImages = 1 LIMIT ?, 1 ) UNION ( SELECT * FROM Products WHERE HasImages = 1 LIMIT ?, 1 ) UNION ( SELECT * FROM Products WHERE HasImages = 1 LIMIT ?, 1 )'; SET @l1 = ROUND(RAND() * @count); SET @l2 = ROUND(RAND() * @count); SET @l3 = ROUND(RAND() * @count); EXECUTE random_records USING @l1 , @l2 , @l3; DEALLOCATE PREPARE random_records;
Чтобы получить три результата, потребовалось почти 7 минут. Но я уверен, что его производительность будет намного лучше в вашем случае. Тем не менее, если вы ищете лучшую производительность, я предлагаю следующие, так как мне потребовалось менее 30 секунд для выполнения этой работы (в одной базе данных).
SELECT COUNT(ID) INTO @count FROM Products WHERE HasImages = 1; PREPARE random_records FROM 'SELECT * FROM Products WHERE HasImages = 1 LIMIT ?, 1'; SET @l1 = ROUND(RAND() * @count); SET @l2 = ROUND(RAND() * @count); SET @l3 = ROUND(RAND() * @count); EXECUTE random_records USING @l1; EXECUTE random_records USING @l2; EXECUTE random_records USING @l3; DEALLOCATE PREPARE random_records;
Имейте в виду, что обе эти команды требуют MySQLi-драйвера в PHP, если вы хотите выполнить их за один раз. И их единственное отличие состоит в том, что для более позднего требуется вызвать метод next_result MySQLi для извлечения всех трех результатов.
Мое личное убеждение в том, что это самый быстрый способ сделать это.
Если вы не согласны с тем, что хотите принять ответ «вне поля», я повторю то, что я сказал в некоторых комментариях.
Лучшим способом подойти к вашей проблеме является кеширование ваших данных заранее (будь то во внешнем JSON или XML-файле или в отдельной таблице базы данных, возможно, даже в таблице в памяти).
Таким образом, вы можете запланировать поражение производительности в таблице продуктов до тех пор, пока вы не узнаете, что сервер будет тихим, и уменьшите беспокойство о создании удара производительности в «случайные» моменты, когда посетитель прибудет на ваш сайт.
Я не собираюсь предлагать явное решение, потому что слишком много возможностей для построения решения. Однако ответ, предложенный @ahmed, не является глупым. Если вы не хотите создавать соединение в своем запросе, просто загрузите больше данных, которые вам нужны, в новую таблицу.