MySQL “DANS” le rendement de l'opérateur sur des (grands?) nombre de valeurs

J'ai fait des expériences avec MongoDB et Redis ces derniers temps, et il semblerait qu'il y a souvent des cas où l'on serait de stocker un tableau de id dans MongoDB ou Redis. Je vais rester avec le Redis, pour cette question, depuis que j'ai fais une demande sur la base de DANS opérateur.

Je me demandais comment performant, il est de la liste d'un grand nombre (300-3000) de id à l'intérieur de l'opérateur, qui ressemblerait à quelque chose comme ceci:

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

Imaginer quelque chose d'aussi simple que d'une produits et catégories tableau que vous pourrait normalement s'unissent afin d'obtenir le produits à partir d'un certain catégorie. Dans l'exemple ci-dessus, vous pouvez voir qu'en vertu d'une catégorie donnée dans le Redis ( category:4:product_ids ) je retourne tous les id de produit à partir de la catégorie avec l'id 4, et placez-la au-dessus de SELECT requête à l'intérieur de la IN opérateur.

Comment performant est-ce?

Est-ce un "ça dépend" de la situation? Ou est-il un béton "c'est (in)acceptable" ou "rapide" ou "lent" ou devrais-je ajouter un LIMIT 25, ou n'est-ce pas de l'aide?

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

Ou dois-je couper le tableau des id de produit renvoyé par le Redis à limiter à 25 ans et seulement ajouter 25 id de la requête plutôt que de 3000 et LIMIT-ing à 25 à partir de l'intérieur de la requête? 

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

Des suggestions/commentaires est très apprécié!

  • Je ne suis pas sûr exactement ce que vous demandez? Une requête avec "id IN(1,2,3, ...3000))" est plus rapide que 3000 requêtes avec "id = valeur". Mais une jointure avec la catégorie "= 4" sera plus rapide que les deux ci-dessus.
  • Droit, mais, depuis un produit peut appartenir à plusieurs catégories I vous ne pouvez pas faire de la "catégorie = 4". Utilisation de Redis je voudrais stocker toutes les id des produits qui appartiennent à certaines catégories et ensuite interroger sur qui. Je suppose que la vraie question est de savoir comment le id IN (1,2,3 ... 3000) par rapport à la table de JOINTURE de products_categories. Ou est-ce que vous avez dit?
  • Il suffit de faire attention à partir de ce bogue dans MySql stackoverflow.com/questions/3417074/...
  • Bien sûr, il n'ya aucune raison pourquoi il ne devrait pas être aussi efficace que toute autre méthode de récupération de indexés lignes; tout dépend si la base de données auteurs ont testé et optimisé pour cela. En termes de complexité de calcul que nous allons faire, au pire, une O(n log N) tri sur le IN de l'alinéa (cela peut même être linéaire sur une liste triée comme vous le montrez, en fonction de l'algorithme), puis linéaire intersection/recherches.
InformationsquelleAutor Michael van Rooijen | 2010-12-22
  1. 32

    Règle générale, si l' IN liste devient trop grand (pour certains mal définie, la valeur de "trop grand", qui est généralement de l'ordre de 100 ou plus petit), il est plus efficace d'utiliser une jointure, la création d'une table temporaire en cas de besoin donc de tenir les nombres.

    Si les chiffres sont un ensemble dense (pas de lacunes dans laquelle l'échantillon de données suggère), alors vous pouvez faire encore mieux avec WHERE id BETWEEN 300 AND 3000.

    Toutefois, sans doute il y a des lacunes dans l'ensemble, à quel point il peut être préférable d'aller avec la liste des valeurs valides après tout (à moins que les écarts sont relativement peu nombreux, dans ce cas, vous pouvez utiliser:

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

    Ou ce que sont les lacunes.

    • Pouvez-vous veuillez donner un exemple de l'utilisation d'une jointure, la création d'une table temporaire"?
    • si l'ensemble de données est issu d'une interface multi-élément select) et il y a des lacunes dans les données et les lacunes ne sont pas séquentielles écart (manquant: 457, 490, 658, ..), puis AND id NOT BETWEEN XXX AND XXX ne fonctionnera pas et il est préférable de coller avec l'équivalent (x = 1 OR x = 2 OR x = 3 ... OR x = 99) comme @David Abat écrit.
    InformationsquelleAutor Jonathan Leffler
  2. 19

    J'ai fait quelques tests, et comme David Abat dit dans sa réponse, il est assez bien optimisé. Comme une référence, j'ai créé une table InnoDB avec 1 000 000 de registres et de faire un select avec l'opérateur "IN" avec 500 000 nombres aléatoires, il faut seulement 2,5 secondes sur mon MAC; en sélectionnant uniquement les registres prend 0,5 secondes.

    Le seul problème que j'ai eu, c'est que j'ai dû augmenter la max_allowed_packet paramètre à partir de la my.cnf fichier. Si non, un mystérieux “MYSQL a disparu” d'erreur est généré.

    Voici le code PHP que j'utilise pour faire le test:

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

    Et les résultats:

    Insert execution time =35.2927210331s
Prepare execution time =0.0161771774292s
Random selection = 499102 Time execution time =2.40285992622s
Pairs = 500000 Exdcution time=0.465420007706s
    • Pour le bien des autres, je vais ajouter que, dans VirtualBox (CentOS) sur ma Fin 2013 MBP avec un core i7, la troisième ligne (un intérêt pour la question) de la sortie était: la sélection Aléatoire = 500744 de Temps en temps d'exécution =53.458173036575 s.. 53 secondes pourrait être tolérable en fonction de votre application. Pour mes utilisations, pas vraiment. Aussi, notez que le test pour le même nombre n'est pas pertinent pour la question à portée de main car il utilise l'opérateur modulo (%) avec un opérateur = ( = ) au lieu de IN().
    • Elle est pertinente, parce que c'est une façon de comparer une requête avec l'opérateur avec une requête similaire sans cette fonctionnalité. Peut-être la higer fois que vous obtenez est parce que c'est un temps de téléchargement, parce que votre machine est swapipng ou de travailler dans une autre machine virtuelle.
    InformationsquelleAutor jbaylina
  3. 11

    Vous pouvez créer une table temporaire où vous pouvez mettre n'importe quel nombre d'Identifiants et d'exécuter des requêtes imbriquées
    Exemple: 

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

    et sélectionnez:

    SELECT id, name, price
FROM products
WHERE id IN (SELECT ID FROM tmp_IDs);
    • il est préférable de rejoindre votre table temporaire au lieu d'utiliser une sous-requête
    • pouvez-vous expliquer comment vous voulez faire cela avec une jointure par rapport à un sous-requête s'il vous plaît?
    • Une sélection de produits.id, nom, prix à PARTIR de produits de REJOINDRE tmp_IDs sur les produits.id = tmp_IDs.ID;
    • CETTE RÉPONSE! est ce que je cherchais, très très vite pour de longues registres
    InformationsquelleAutor Vladimir Jotov
  4. 4

    IN est très bien, et bien optimisé. Assurez-vous que vous l'utilisez sur un champ indexé et que vous êtes bien.

    Il est fonctionnellement équivalent à:

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

    Autant que le moteur de base est concerné.

    • Pas vraiment. - Je l'utiliser DANS le paragraphe chercher 5k enregistrements de la DB. DANS le paragraphe contient la liste des PKs liés de telle sorte colonne est indexée et garantie unique. EXPLIQUEZ dit, que full table scan est effectué insteed de l'aide PK recherche dans "fifo-file d'attente-alike" de style.
    InformationsquelleAutor David Fells
  5. 2

    À l'aide de IN avec un grand jeu de paramètres sur une grande liste d'enregistrements seront en fait ralentir.

    Dans le cas que j'ai résolu récemment, j'ai eu deux clauses where, l'un avec 2,50 paramètres et l'autre avec 3 500 les paramètres, de l'interrogation d'une table de 40 Millions de disques.

    Ma requête a pris 5 minutes à l'aide de la norme WHERE IN. En utilisant une sous-requête pour l' DANS déclaration (mettre les paramètres dans leurs propres indexés table), j'ai reçu la requête de DEUX secondes.

    Travaillé pour MySQL et Oracle dans mon expérience.

    InformationsquelleAutor yoyodunno
  6. -2

    Lorsque vous fournir de nombreuses valeurs pour la IN opérateur, il faut d'abord trier supprimer les doublons. Au moins je soupçonne que. Donc, il serait pas bon de donner trop grand nombre de valeurs, comme le tri prend N log N fois.

    Mon expérience a prouvé que le découpage de l'ensemble des valeurs en sous-ensembles plus petits et en combinant les résultats de toutes les requêtes de l'application vous donne les meilleures performances. J'avoue que j'ai acquis de l'expérience sur une autre base de données (Omniprésente), mais la même chose peut s'appliquer à tous les moteurs. Mon nombre de valeurs par l'ensemble ont été 500-1000. Plus ou moins a été significativement plus lent.

    • Je sais que c'est 7 ans, mais le problème avec cette réponse, c'est simplement que c'est un commentaire basé sur une supposition.
    InformationsquelleAutor Jarekczek