Вопрос по mysql, sql – Самый быстрый случайный выбор ГДЕ столбец X - Y (NULL)
В настоящее время я использую:
SELECT *
FROM
table AS t1
JOIN (
SELECT (RAND() * (SELECT MAX(id) FROM table where column_x is null)) AS id
) AS t2
WHERE
t1.id >= t2.id
and column_x is null
ORDER BY t1.id ASC
LIMIT 1
Это обычно очень быстро, однако, когда я включаю выделенное column_x, являющееся условием Y (null), оно становится медленным.
Какое было бы самое быстрое решение для случайных запросов, когда записи & apos; столбец X равен нулю?
ID - это PK, столбец X - это int (4). Таблица содержит около миллиона записей и более 1 ГБ в общем объеме, удваивающемся каждые 24 часа.
column_x индексируется.
Идентификатор столбца не может быть последовательным.
Используемый в этом случае механизм БД - InnoDB.
Спасибо.
EXPLAIN план?
ypercubeᵀᴹ
(column_x, id)?
ypercubeᵀᴹ
ORDER BY t1.id ASC
ypercubeᵀᴹ
но копая немного дальше, я думаю, что динамический запрос может работать. Мы выбираем N-ую строку, где N-й случайный:
SELECT @r := CAST(COUNT(1)*RAND() AS UNSIGNED) FROM table WHERE column_x is null;
PREPARE stmt FROM
'SELECT *
FROM table
WHERE column_x is null
LIMIT 1 OFFSET ?';
EXECUTE stmt USING @r;
самом деле не много обойти этот факт; если вы хотите, чтобы он был действительно случайным, то запрос должен загрузить все соответствующие данные, чтобы узнать, из каких записей он должен выбрать.
К счастью, есть более быстрые способы сделать это. Они не совсем случайные, но если вы рады обменять немного чистой случайности на скорость, то они должны быть достаточно хороши для большинства целей.
Имея это в виду, самый быстрый способ получить "случайный" запись заключается в добавлении дополнительного столбца в вашу БД, который заполняется случайным значением. Возможно соленый хеш MD5 первичного ключа? Без разницы. Добавьте соответствующие индексы для этого столбца, а затем просто добавьте столбец кORDER BY в запросе, и вы вернете свои записи в случайном порядке.
Чтобы получить одну случайную запись, просто укажитеLIMIT 1 и добавитьWHERE random_field > $random_value где случайное значение будет значением в диапазоне вашего нового поля (например, скажем, MD5-хэш случайного числа).
Конечно, недостатком здесь является то, что хотя ваши записи будут в случайном порядке, они будут вставлены в том же случайном порядке. Я сказал, что это было идеальным обменом на скорость запроса. Вы можете обойти это, периодически обновляя их новыми значениями, но я думаю, что это может быть проблемой для вас, если вам нужно сохранить это свежим.
Другим недостатком является то, что добавление дополнительного столбца может быть слишком сложным, чтобы спросить, есть ли у вас ограничения хранения, и ваша БД уже имеет большой размер, или если у вас есть строгий администратор баз данных, чтобы пройти мимо, прежде чем вы сможете добавлять столбцы. Но опять же, вы должны что-то поменять; если вы хотите скорость запроса, вам нужен этот дополнительный столбец.
Во всяком случае, я надеюсь, что это помогло.
explain на запрос? Какой был выход?
Почему бы не сохранить или не кэшировать значение:SELECT MAX(id) FROM table where column_x is null и использовать это как переменную. Ваш запрос станет:
$rand = rand(0, $storedOrCachedMaxId);
SELECT *
FROM
table AS t1
WHERE
t1.id >= $rand
and column_x is null
ORDER BY t1.id ASC
LIMIT 1
Более простой запрос, вероятно, будет проще на БД.
Знайте, что если ваши данные содержат значительные дыры - выaren't получать последовательно случайные результаты с такого рода запросами.
что вам нужно объединение, ни заказ, ни ограничение 1 (при условии, что идентификаторы уникальны).
SELECT *
FROM myTable
WHERE column_x IS NULL
AND id = ROUND(RAND() * (SELECT MAX(Id) FROM myTable), 0)