Операция оператора MySQL "IN" по (большому?) Числу значений

В последнее время я экспериментировал с Redis и MongoDB, и, похоже, часто бывают случаи, когда вы храните массив id в MongoDB или Redis. Я буду придерживаться Redis для этого вопроса, так как я спрашиваю об операторе MySQL IN.

Мне было интересно, как выполнить переименование большого числа (300-3000) id внутри оператора IN, который будет выглядеть примерно так:

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)

Представьте себе что-то столь же простое, как таблица продуктов и категорий, которую вы обычно можете объединить, чтобы получить продукты из определенного категория. В приведенном выше примере вы можете увидеть, что в данной категории в Redis (category:4:product_ids) я возвращаю все идентификаторы продуктов из категории с id 4 и помещаю их в вышеуказанный запрос SELECT внутри оператора IN.

Насколько это возможно?

Является ли это ситуацией? Или существует конкретное "это (un) приемлемо" или "быстро" или "медленно", или я должен добавить LIMIT 25, или это не помогает?

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
LIMIT 25

Или мне нужно обрезать массив идентификатора продукта, возвращаемого Redis, чтобы ограничить его до 25 и добавить 25 запросов к запросу, а не 3000, а LIMIT - до 25 изнутри запроса?

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)

Любые предложения/отзывы очень ценятся!

Ответ 1

Вообще говоря, если список IN становится слишком большим (для некоторого некорректного значения "слишком большого", которое обычно находится в области 100 или меньше), становится более эффективным использовать соединение, создавая временную таблицу если необходимо, чтобы удерживать числа.

Если числа представляют собой плотное множество (без пробелов - что предлагает выборка данных), то вы можете сделать еще лучше с WHERE id BETWEEN 300 AND 3000. Однако, по-видимому, в наборе есть пробелы, и в этом случае лучше всего перейти со списком допустимых значений (если только промежутки относительно немногочисленны, и в этом случае вы могли бы использовать: WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836 или что угодно пробелы.

Ответ 2

Я делал некоторые тесты, и, как говорит Дэвид Феллс, он довольно хорошо оптимизирован. В качестве ссылки я создал таблицу InnoDB со 1000000 регистрами и выполнил выбор с помощью оператора "IN" с 500000 случайными числами, для моего MAC-адреса требуется всего 2,5 секунды. (Выбор только четных регистров занимает 0,5 с).

Единственная проблема, с которой я столкнулся, это то, что мне пришлось увеличить параметр max_allowed_packet из файла my.cnf. Если нет, генерируется загадочная ошибка "MYSQL".

Вот код PHP, который я использую для проведения теста:

$NROWS =1000000;
$SELECTED = 50;
$NROWSINSERT =15000;

$dsn="mysql:host=localhost;port=8889;dbname=testschema";
$pdo = new PDO($dsn, "root", "root");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("drop table if exists `uniclau`.`testtable`");
$pdo->exec("CREATE  TABLE `testtable` (
        `id` INT NOT NULL ,
        `text` VARCHAR(45) NULL ,
        PRIMARY KEY (`id`) )");

$before = microtime(true);

$Values='';
$SelValues='(';
$c=0;
for ($i=0; $i<$NROWS; $i++) {
    $r = rand(0,99);
    if ($c>0) $Values .= ",";
    $Values .= "( $i , 'This is value $i and r= $r')";
    if ($r<$SELECTED) {
        if ($SelValues!="(") $SelValues .= ",";
        $SelValues .= $i;
    }
    $c++;

    if (($c==100)||(($i==$NROWS-1)&&($c>0))) {
        $pdo->exec("INSERT INTO `testtable` VALUES $Values");
        $Values = "";
        $c=0;
    }
}
$SelValues .=')';
echo "<br>";


$after = microtime(true);
echo "Insert execution time =" . ($after-$before) . "s<br>";

$before = microtime(true);  
$sql = "SELECT count(*) FROM `testtable` WHERE id IN $SelValues";
$result = $pdo->prepare($sql);  
$after = microtime(true);
echo "Prepare execution time =" . ($after-$before) . "s<br>";

$before = microtime(true);

$result->execute();
$c = $result->fetchColumn();

$after = microtime(true);
echo "Random selection = $c Time execution time =" . ($after-$before) . "s<br>";



$before = microtime(true);

$sql = "SELECT count(*) FROM `testtable` WHERE id %2 = 1";
$result = $pdo->prepare($sql);
$result->execute();
$c = $result->fetchColumn();

$after = microtime(true);
echo "Pairs = $c Exdcution time=" . ($after-$before) . "s<br>";

И результаты:

Insert execution time =35.2927210331s
Prepare execution time =0.0161771774292s
Random selection = 499102 Time execution time =2.40285992622s
Pairs = 500000 Exdcution time=0.465420007706s

Ответ 3

Вы можете создать временную таблицу, в которой вы можете поместить любое количество идентификаторов и запустить вложенный запрос Пример:

CREATE [TEMPORARY] TABLE tmp_IDs (`ID` INT NOT NULL,PRIMARY KEY (`ID`));

и выберите:

SELECT id, name, price
FROM products
WHERE id IN (SELECT ID FROM tmp_IDs);

Ответ 4

IN отлично и хорошо оптимизирован. Убедитесь, что вы используете его в индексированном поле, и все в порядке. Он функционально эквивалентен (x = 1 OR x = 2 OR x = 3... OR x = 99) в отношении соответствующего двигателя.

Ответ 5

Использование IN с большим набором параметров в большом списке записей на самом деле будет медленным.

В том случае, когда я недавно решил, у меня было два предложения: один с 250 параметрами, а другой с параметрами 3500, запрашивающий таблицу в 40 миллионов записей. Мой запрос занял 5 минут, используя стандартный WHERE IN. Вместо этого, используя подзапрос для оператора IN (помещая параметры в свою собственную индексированную таблицу), я получил запрос до двух секунд. Работал как для MySQL, так и для Oracle в моем опыте.

Ответ 6

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

Мой опыт показал, что нарезка набора значений на более мелкие подмножества и объединение результатов всех запросов в приложении дает лучшую производительность. Я признаю, что я собрал опыт в другой базе данных (Pervasive), но то же самое можно применить ко всем моделям. Мой подсчет значений за набор составлял 500-1000. Более-менее значительно медленнее.