IP Girl
  1. IP Girl
  3. Performances de l’opérateur MySQL «IN» sur (grand?) Nombre de valeurs
Intereting Posts
Explication du «motif du réacteur» Fenêtre de dialog de l’interface utilisateur jQuery chargée dans le style AJAX Onglets de l’interface utilisateur jQuery Une alternative HTML5 à ZeroClipboard à base de flash pour une copie sécurisée des données dans le presse papier? Pourquoi utiliser des assemblys nommés forts? Instanciation d’objects C ++ Livres de langue / Tutoriels pour les langues populaires Comment une fonction temporelle peut-elle exister dans la functional programming? Sublime Text: Regex pour convertir les majuscules en majuscules? Comment activer le code de format automatique pour Intellij IDEA? Coin arrondi SVG différence entre #if défini (WIN32) et #ifdef (WIN32) Bloc statique en Java Comment retourner un code de statut 200 HTTP à partir du contrôleur ASP.NET MVC 3 Comment trouve-t-on les prix du site d’Amazon par programmation? Vérifier le nombre d’arguments transmis à un script Bash

Performances de l’opérateur MySQL «IN» sur (grand?) Nombre de valeurs

J’ai expérimenté avec Redis et MongoDB dernièrement et il semblerait qu’il y ait souvent des cas où vous stockeriez un tableau d’ id dans MongoDB ou Redis. Je vais restr avec Redis pour cette question car je parle de l’opérateur MySQL IN .

Je me demandais comment il était performant de lister un grand nombre (300-3000) d’ id dans l’opérateur IN, ce qui ressemblerait à ceci: 

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

Imaginez quelque chose d’aussi simple qu’une table de produits et de catégories que vous pourriez normalement rejoindre pour obtenir les produits d’une certaine catégorie . Dans l’exemple ci-dessus, vous pouvez voir cela dans une catégorie donnée dans Redis ( category:4:product_ids ) Je retourne tous les identifiants de produit de la catégorie avec l’ID 4 et les place dans la requête SELECT ci-dessus dans l’opérateur IN .

Est-ce que c’est performant?

Est-ce une situation “ça dépend”? Ou y a-t-il un “ceci est (un) acceptable” ou “rapide” ou “lent” concret ou devrais-je append un LIMIT 25 , ou cela n’aide-t-il pas? 

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

Ou devrais-je réduire le tableau des identifiants de produit renvoyés par Redis pour le limiter à 25 et append seulement 25 ID à la requête plutôt que 3000 et LIMIT it à 25 à partir de la requête? 

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

Toutes les suggestions / commentaires sont très appréciés!

En règle générale, si la liste IN devient trop grande (pour une valeur mal définie de «trop grand» qui est généralement de l’ordre de 100 ou moins), il devient plus efficace d’utiliser une jointure, créant une table temporaire si nécessaire. être à tenir les chiffres.

Si les nombres sont un ensemble dense (pas de lacunes – ce que l’échantillon suggère de données), alors vous pouvez faire encore mieux avec WHERE id BETWEEN 300 AND 3000 . Cependant, il y a probablement des lacunes dans l’ensemble, à ce stade, il peut être préférable d’aller avec la liste des valeurs valides après tout (sauf si les écarts sont relativement peu nombreux, auquel cas vous pourriez utiliser: WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836 ou peu importe les lacunes.

J’ai fait des tests et, comme le dit David Fells, c’est très bien optimisé. À titre de référence, j’ai créé une table InnoDB avec 1000000 Registers et faisant un select avec l’opérateur “IN” avec 500 000 nombres aléatoires, il ne prend que 2,5s dans mon MAC. (La sélection des registres pairs ne prend que 0,5).

Le seul problème que j’ai eu, c’est que je devais augmenter le paramètre max_allowed_packet à partir du fichier my.cnf. Sinon, une erreur mystérieuse “MYSQL est partie” est générée.

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->setAtsortingbute(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 "
"; $after = microtime(true); echo "Insert execution time =" . ($after-$before) . "s
"; $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
"; $before = microtime(true); $result->execute(); $c = $result->fetchColumn(); $after = microtime(true); echo "Random selection = $c Time execution time =" . ($after-$before) . "s
"; $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
";

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

Vous pouvez créer une table temporaire dans laquelle vous pouvez placer un nombre quelconque d’ID et exécuter une requête nestede. 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);

IN est bien et bien optimisé. Assurez-vous de l’utiliser sur un champ indexé et vous allez bien. C’est fonctionnellement équivalent à (x = 1 OU x = 2 OU x = 3 … OU x = 99) jusqu’au moteur concerné.

L’utilisation de IN avec un grand jeu de parameters sur une grande liste d’enregistrements sera en fait lente.

Dans le cas que j’ai résolu récemment, j’avais deux clauses where, une avec 250 parameters et l’autre avec 3500 parameters, interrogeant une table de 40 millions d’enregistrements. Ma requête a pris 5 minutes en utilisant la norme WHERE IN. En utilisant plutôt une sous-requête pour l’instruction IN (en mettant les parameters dans leur propre table indexée), j’ai obtenu la requête à DEUX secondes. J’ai travaillé pour MySQL et Oracle dans mon expérience.

Lorsque vous fournissez de nombreuses valeurs pour l’opérateur IN , il doit d’abord être sortingé pour supprimer les doublons. Au moins je le soupçonne. Il ne serait donc pas bon de fournir trop de valeurs, car le sorting prend N log N heure.

Mon expérience a prouvé que le découpage de l’ensemble de valeurs en sous-ensembles plus petits et la combinaison des résultats de toutes les requêtes dans l’application offrent les meilleures performances. J’admets que j’ai accumulé de l’expérience sur une firebase database différente (Pervasive), mais la même chose peut s’appliquer à tous les moteurs. Mon compte de valeurs par set était de 500-1000. Plus ou moins était significativement plus lent.