Вопрос по mysql, sql – Самый быстрый случайный выбор ГДЕ столбец X - Y (NULL)

2

В настоящее время я использую:

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.

Спасибо.

да, он индексируется, и все же запрос начинает занимать слишком много времени по мере роста базы данных, и большая часть column_x становится NOT NULL. Phil
Можете ли вы отредактировать свой вопрос и добавитьEXPLAIN план? ypercubeᵀᴹ
Таблица использует движок MyISAM или InnoDB? Можете ли вы попробовать добавить составной индекс на(column_x, id)? ypercubeᵀᴹ
Столбец индексирован? ChrisWue
Линия с Order By содержит опечатку, верно? Так должно быть:ORDER BY t1.id ASC ypercubeᵀᴹ

Ваш Ответ

4   ответа
0

но копая немного дальше, я думаю, что динамический запрос может работать. Мы выбираем 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;
Запрос с огромным смещением очень неэффективен
3

самом деле не много обойти этот факт; если вы хотите, чтобы он был действительно случайным, то запрос должен загрузить все соответствующие данные, чтобы узнать, из каких записей он должен выбрать.

К счастью, есть более быстрые способы сделать это. Они не совсем случайные, но если вы рады обменять немного чистой случайности на скорость, то они должны быть достаточно хороши для большинства целей.

Имея это в виду, самый быстрый способ получить "случайный" запись заключается в добавлении дополнительного столбца в вашу БД, который заполняется случайным значением. Возможно соленый хеш MD5 первичного ключа? Без разницы. Добавьте соответствующие индексы для этого столбца, а затем просто добавьте столбец кORDER BY в запросе, и вы вернете свои записи в случайном порядке.

Чтобы получить одну случайную запись, просто укажитеLIMIT 1 и добавитьWHERE random_field > $random_value где случайное значение будет значением в диапазоне вашего нового поля (например, скажем, MD5-хэш случайного числа).

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

Другим недостатком является то, что добавление дополнительного столбца может быть слишком сложным, чтобы спросить, есть ли у вас ограничения хранения, и ваша БД уже имеет большой размер, или если у вас есть строгий администратор баз данных, чтобы пройти мимо, прежде чем вы сможете добавлять столбцы. Но опять же, вы должны что-то поменять; если вы хотите скорость запроса, вам нужен этот дополнительный столбец.

Во всяком случае, я надеюсь, что это помогло.

Это единственный метод, предложенный до сих пор, который обеспечивает действительно однородные вероятности. Пробелы в идентификаторах не имеют значения. Легко реализовать и индексировать. +1
1

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 получать последовательно случайные результаты с такого рода запросами.

Значение столбца X постоянно меняется. С каждым запросом необходимо проверять все column_x, чтобы выяснить, какие из них все еще равны NULL. Phil
Это звучит немного странно, но не меняет мой ответ - вам действительно не нужно ставитьcolumn_x is null в вашемMAX(id) запрос. В редких случаях вы не получите результата, вы можете просто запросить снова инвертируя логический тестt1.id < $rand, Вам все еще нужно добавитьexplain результат к вопросу.
1

что вам нужно объединение, ни заказ, ни ограничение 1 (при условии, что идентификаторы уникальны).

SELECT *
FROM   myTable
WHERE  column_x IS NULL
   AND id = ROUND(RAND() * (SELECT MAX(Id) FROM myTable), 0)
за исключением того, что идентификаторы не являются последовательными, чтобы "возвращалось 1"or 0 строк.
Это может не сработать сейчас, ноcan заставить работать. +1
вы могли бы, однако, использоватьAND id >= ... LIMIT 1, Объединение в запросе в вопросе не обязательно.
Идентификаторы не обязательно должны быть последовательными - но повторять - если идентификатор строки имеет значение column_x, тогда ничего не будет возвращено - поэтому у меня будет другой взгляд.
Здравствуйте, этот запрос, к сожалению, не будет работать, когда задействован столбец. Он вернется пустым, поскольку случайный идентификатор между 0 и максимальным идентификатором записи, где столбец X равен NULL, может принадлежать столбцу, в котором столбец X не равен NULL. Phil

Похожие вопросы