Оптимизация запроса mysql JOIN ORDER BY RAND () в большой базе данных

php mysql random

501 просмотра

4 ответа

Я работаю над проектом, в котором есть большой Questionбанк, и для Tests addedсистемы 20 вопросов динамически выбираются во время выполнения на основе следующего запроса:

SELECT Question.* from Question JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level 
ORDER BY RAND() 
LIMIT 20;

Однако, как известно, что RAND()функция MySQL убивает ваш сервер, я искал лучшие решения.

Результат EXPLAIN [above query] :

+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | Test     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | Question | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+

Результат EXPLAIN Question :

+-------------------+------------------------------------------+------+-----+---------+----------------+
| Field             | Type                                     | Null | Key | Default | Extra          |
+-------------------+------------------------------------------+------+-----+---------+----------------+
| Question_ID       | int(11)                                  | NO   | PRI | NULL    | auto_increment |
| Questions         | varchar(100)                             | NO   |     | NULL    |                |
| Available_Options | varchar(200)                             | NO   |     | NULL    |                |
| Correct_Answer    | varchar(50)                              | NO   |     | NULL    |                |
| Subject_ID        | int(11)                                  | NO   |     | NULL    |                |
| Question_Level    | enum('Beginner','Intermediate','Expert') | NO   |     | NULL    |                |
| Created_By        | int(11)                                  | NO   |     | NULL    |                |
+-------------------+------------------------------------------+------+-----+---------+----------------+

Результат EXPLAIN Test :

+----------------+------------------------------------------+------+-----+---------+----------------+
| Field          | Type                                     | Null | Key | Default | Extra          |
+----------------+------------------------------------------+------+-----+---------+----------------+
| Test_ID        | int(11)                                  | NO   | PRI | NULL    | auto_increment |
| Test_Name      | varchar(50)                              | NO   |     | NULL    |                |
| Test_Level     | enum('Beginner','Intermediate','Expert') | NO   |     | NULL    |                |
| Subject_ID     | int(11)                                  | NO   |     | NULL    |                |
| Question_Count | int(11)                                  | NO   |     | NULL    |                |
| Created_By     | int(11)                                  | NO   |     | NULL    |                |
+----------------+------------------------------------------+------+-----+---------+----------------+

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

PS В системе также предусмотрена возможность удаления, поэтому ключ AUTO_INCREMENT PRIMARY таблицы QUESTION и TEST может иметь большие пропуски.

Автор: Ayush Gupta Источник Размещён: 08.11.2019 11:24

Ответы (4)


1 плюс

Решение

Почему бы вам не просмотреть числа в PHP, а затем выбрать вопросы по идентификатору? Вот логика моей точки зрения:

$MIN       = 1;
$MAX       = 50000; // You may want to get the MAX from your database
$questions = '';

for($i = 0; $i < 20; $i++)
   $questions .= mt_rand($MIN, $MAX) . ',';

// Removes last comma
$questions = rtrim($questions, ',');

$query = "SELECT * FROM Question WHERE Question.id IN ($questions)";

Изменить 1:

Я думал о проблеме, и я понял, что вы можете выбрать все идентификаторы из вашей базы данных, а затем выбрать 20 элементов, используя функцию array_rand () .

$values    = array(1, 5, 10000, 102021, 1000000); // Your database ID's
$questions = array_rand($values, 20);

$questions[0];
$questions[1];
$questions[2]; // etc
Автор: Linesofcode Размещён: 20.08.2016 02:27

2 плюса

Мне нравится этот вопрос. Это очень хорошая головоломка для оптимизации, и давайте пока предположим, что производительность очень важна для этого запроса, и что вы не можете использовать какие-либо динамически вставляемые значения (например, из PHP).

Одним из высокопроизводительных решений было бы добавить столбец со случайными значениями (скажем, «Rand»), упорядочить таблицу по этому значению, а также периодически перегенерировать и переупорядочивать таблицу. Вы могли бы тогда использовать запрос как этот:

SELECT Question.* from Question 
JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level  
WHERE Question.Rand > RAND() 
LIMIT 20

Это будет выполнено при O (n) , требуя только одного сканирования таблицы, но с риском возврата менее 20 результатов, если будет сгенерировано значение, очень близкое к 1. Если бы это был приемлемый риск (например, вы могли бы программно проверить неадекватный результат и повторить запрос), вы бы получили хорошую производительность во время выполнения.

Периодическая повторная генерация и переупорядочение чисел необходимы, потому что строки в начале таблицы с высокими значениями Rand будут предпочтительнее и будут отображаться непропорционально часто в результатах. (Представьте, что первой строке повезло получить значение Рэнда 0,95)

Еще лучше было бы создать столбец с непрерывными целыми числами, индексировать этот столбец, а затем случайным образом выбрать точку вставки, чтобы получить 20 результатов. Такой запрос может выглядеть так:

SELECT Question.* from Question 
JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level  
CROSS JOIN (SELECT MAX(Rand_id) AS max_id FROM Question)
WHERE Question.Rand_Id > ROUND(RAND() * max_id)
LIMIT 20

Но что, если вы не можете изменить свой стол в любом случае? Если не имеет значения, насколько грязен ваш SQL, и доля пропущенных идентификаторов относительно мала (скажем, примерно 1/10). Вы можете получить 20 случайных вопросов с хорошей степенью вероятности с помощью следующего SQL:

SELECT Question.* from Question JOIN Test 
  ON Question.Subject_ID = Test.Subject_ID 
  AND Question.Question_Level = Test.Test_Level 
  WHERE Question.Question_ID IN (
    SELECT DISTINCT(ROUND(rand * max_id)) AS rand_id 
    FROM ( --generate 30 random numbers to make sure we get 20 results
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      ...
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand
    ) a 
    CROSS JOIN ( --get the max possible id from the Question table
      SELECT MAX(id) AS max_id FROM Question
    ) b
  )
LIMIT 20 --finally pare our results down to 20 in case we got too many

Однако это вызовет проблемы в вашем случае использования, потому что вы фактически не можете знать, сколько результатов (и их идентификаторов) будет в наборе результатов после объединения. После объединения по теме и сложности доля пропущенных идентификаторов может быть очень высокой, и вы можете получить гораздо меньше, чем 20 результатов, даже с несколькими сотнями случайных предположений о том, какие идентификаторы могут быть в таблице.

Если вы можете использовать логику из PHP (звучит так, как вы), откроется множество высокопроизводительных решений. Вы можете, например, создать в PHP объект, задачей которого было хранить массивы всех идентификаторов Вопросов с определенным предметом и уровнем сложности. Затем вы можете выбрать 20 индексов случайных массивов и вернуть 20 действительных идентификаторов, что позволит вам выполнить очень простой запрос.

SELECT Question.* from Question WHERE Question_ID IN ($dynamically_inserted_ids)

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

Автор: Nate Vaughan Размещён: 21.08.2016 01:19

1 плюс

Создайте следующие индексы:

CREATE INDEX Question_Subject_ID_idx ON Question (Subject_ID);
CREATE INDEX Test_Subject_ID_idx ON Test (Subject_ID);
CREATE INDEX Question_Question_Level_idx ON Question (Question_Level);
CREATE INDEX Test_Test_Level_idx ON Test (Test_Level);
Автор: mnv Размещён: 20.08.2016 05:25

0 плюса

Некоторое время назад я исследовал ту же проблему, и мой первый подход состоял в том, чтобы сначала загрузить все идентификаторы, выбрать случайные в PHP (см .: Эффективно выбрать n случайных элементов из массива PHP (без перемешивания) ), а затем запросить эти идентификаторы непосредственно в MySQL.

Это было улучшение, но занимало много памяти для больших наборов данных. При дальнейшем исследовании я нашел лучший способ: выбрать случайные идентификаторы в одном запросе без каких-либо других полей или соединений, а затем выполнить реальный запрос по этим идентификаторам:

SELECT Question.* from Question JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level 
WHERE Question_ID IN (
    SELECT Question_ID from Question
    ORDER BY RAND() 
    LIMIT 20
);

Вот запись в блоге с тестами для моего конкретного случая: Показать случайные продукты в Magento .

Соответствующие части:

Помимо проблем с памятью, может ли это быть ORDER BY RAND()проблемой само по себе, а использовать ее вместе со всеми объединениями таблиц Magento? Что если я предварительно выберу случайные идентификаторы ORDER BY RAND()?

[...]

Это было немного медленнее, чем подход предварительного выбора PHP, но все же явно в пользу чистого порядка по ранду и без увеличения использования памяти в PHP.

[...]

Проблема подхода с чистым MySQL ORDER BY RAND()стала еще более очевидной. При мониторинге MySQL с помощью mytopя заметил, что, помимо сортировки , много времени уходит на копирование . Проблема здесь, кажется, в том, что сортировка без индекса, как при ORDER BY RAND()копировании данных во временную таблицу и упорядочении. При использовании плоского индекса все атрибуты продукта выбираются из одной таблицы, что увеличивает объем данных, копируемых во временную таблицу и из нее для сортировки. Я мог бы упустить что-то еще здесь, но производительность упала с плохой до ужасной, и это даже вызвало сбой моего Vagrantbox с первой попытки, потому что его диск был заполнен (40 ГБ). Таким образом, в то время как PHP использует меньше памяти при таком подходе, MySQL все больше требует ресурсов.

Я не знаю, насколько велика ваша таблица вопросов, в какой-то момент этот подход все еще несовершенен:

Во-вторых, как указано выше, для больших каталогов вы должны искать что-то другое. Проблема в ORDER BY RAND()том, что, хотя мы минимизировали данные, которые должны быть скопированы, он все равно копирует все строки во временную таблицу и генерирует случайное число для каждой. Сама сортировка оптимизирована таким образом, чтобы сортировать не все строки (см. « Оптимизация LIMIT» ), но копирование требует времени.

Есть еще одно известное сообщение в блоге о выборе случайных строк в MySQL, написанное Яном Кнешке. Он предлагает использовать индексную таблицу со всеми идентификаторами, которая имеет свой собственный первичный ключ без пробелов . Эта индексная таблица будет автоматически обновляться с помощью триггеров, и случайные строки могут выбираться индексной таблицей с использованием случайных ключей между min (ключ) и max (ключ).

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

Автор: Fabian Schmengler Размещён: 21.08.2016 09:21
Вопросы из категории :
32x32