Wydajność operatora MySQL „IN” dla (dużej?) Liczby wartości

Ostatnio eksperymentowałem z Redis i MongoDB i wydaje się, że często zdarza się, że przechowujesz tablicę identyfikatorów w MongoDB lub Redis. W przypadku tego pytania zostanę przy Redis, ponieważ pytam o operator MySQL IN .

Zastanawiałem się, jak wydajne jest umieszczenie dużej liczby (300-3000) identyfikatorów wewnątrz operatora IN, które wyglądałyby mniej więcej tak:

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

Wyobraź sobie coś tak prostego, jak tabela produktów i kategorii, do której normalnie możesz DOŁĄCZYĆ razem, aby uzyskać produkty z określonej kategorii . W powyższym przykładzie widać, że pod daną kategorią w Redis ( category:4:product_ids) zwracam wszystkie identyfikatory produktów z kategorii o id 4 i umieszczam je w powyższym SELECTzapytaniu wewnątrz INoperatora.

Jak wydajne jest to?

Czy jest to sytuacja typu „to zależy”? A może jest konkretne „to jest (nie) akceptowane”, „szybko” lub „wolno”, czy powinienem dodać LIMIT 25, czy to nie pomaga?

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

A może powinienem przyciąć tablicę identyfikatorów produktów zwracanych przez Redis, aby ograniczyć ją do 25 i dodać do zapytania tylko 25, a nie 3000, a LIMITdo 25 z wewnątrz zapytania?

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

Wszelkie sugestie / opinie są bardzo mile widziane!

Ogólnie rzecz biorąc, jeśli INlista stanie się zbyt duża (dla jakiejś źle zdefiniowanej wartości `` zbyt dużej '', która zwykle jest w zakresie 100 lub mniejszym), bardziej efektywne staje się użycie złączenia, tworząc tymczasową tabelę, jeśli zajdzie taka potrzeba. trzymać numery.

Jeśli liczby są gęstym zbiorem (bez luk - co sugerują przykładowe dane), możesz zrobić jeszcze lepiej WHERE id BETWEEN 300 AND 3000.

Jednak przypuszczalnie są luki w zbiorze, w którym to momencie może być lepiej pójść z listą prawidłowych wartości (chyba, że ​​luk jest stosunkowo niewiele, w takim przypadku możesz użyć:

WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836

Lub jakiekolwiek są luki.

Robiłem kilka testów i jak mówi David Fells w swojej odpowiedzi , jest to dość dobrze zoptymalizowane. Dla porównania, utworzyłem tabelę InnoDB z 1 000 000 rejestrów i dokonując wyboru za pomocą operatora „IN” z 500 000 liczb losowych, zajmuje to tylko 2,5 sekundy na moim MAC; wybranie tylko rejestrów parzystych zajmuje 0,5 sekundy.

Jedyny problem jaki miałem to to, że musiałem zwiększyć max_allowed_packetparametr z my.cnfpliku. Jeśli nie, generowany jest tajemniczy błąd „MYSQL zniknął”.

Oto kod PHP, którego używam do wykonania testu:

$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>";

A wyniki:

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

Możesz utworzyć tymczasową tabelę, w której możesz umieścić dowolną liczbę identyfikatorów i uruchomić zagnieżdżone zapytanie Przykład:

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

i wybierz:

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

Używanie INz dużym parametrem ustawionym na dużej liście rekordów będzie w rzeczywistości powolne.

W przypadku, który ostatnio rozwiązałem, miałem dwie klauzule where, jedną z 2,50 parametrami, a drugą z 3500 parametrami, odpytując tabelę zawierającą 40 milionów rekordów.

Moje zapytanie zajęło 5 minut przy użyciu standardu WHERE IN. Używając zamiast tego podzapytania dla instrukcji IN (umieszczając parametry we własnej indeksowanej tabeli), sprowadziłem zapytanie do DWÓCH sekund.

Z mojego doświadczenia wynika, że ​​pracowałem zarówno dla MySQL, jak i Oracle.

INjest w porządku i dobrze zoptymalizowany. Upewnij się, że używasz go na indeksowanym polu i wszystko w porządku.

Jest funkcjonalnie równoważne z:

(x = 1 OR x = 2 OR x = 3 ... OR x = 99)

Jeśli chodzi o silnik DB.

Jeśli INoperator podaje wiele wartości , musi najpierw je posortować, aby usunąć duplikaty. Przynajmniej to podejrzewam. Nie byłoby więc dobrze podawać zbyt wielu wartości, ponieważ sortowanie zajmuje N log N.

Z mojego doświadczenia wynika, że ​​najlepszą wydajność daje pocięcie zbioru wartości na mniejsze podzbiory i połączenie wyników wszystkich zapytań w aplikacji. Przyznaję, że zbierałem doświadczenie na innej bazie danych (Pervasive), ale to samo może dotyczyć wszystkich silników. Moja liczba wartości w zestawie wynosiła 500-1000. Mniej więcej było znacznie wolniej.