Performances d'opérateur "IN" de MySQL sur (grand?) Nombre de valeurs

Question

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 d' 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 données MySQL À l'opérateur.

Je me demandais comment performant, il est de la liste d'un grand nombre (300-3000) de l'id de 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 des produits et des catégories de table qui vous pourrait normalement s'unissent afin d'obtenir les produits d'une certaine 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 retourner tous les id de produit à partir de la catégorie avec l'id 4, et placez-les dans le ci-dessus SELECT de la requête à l'intérieur de l' IN de l'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 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)

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

Answer 1

De manière générale, si la liste est 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 que ce soit des lacunes à combler.

Answer 2

J'ai fait quelques tests, et que David Abat dit, il est assez bien optimisé. Comme une référence, j'ai créé une table InnoDB avec 1000000 registres et de faire un select avec l'opérateur "IN" avec 500000 nombres aléatoires, il faut seulement 2,5 s dans mon MAC. (En sélectionnant uniquement les registres prend 0,5 s).

Le seul problème que j'ai eu, c'est que j'ai dû augmenter la max_allowed_packet paramètre à partir de la mes.cnf de 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

Answer 3

IN est bien, et bien optimisé. Assurez-vous de l'utiliser sur un champ indexé et tout va bien. Son fonctionnement est équivalent à (x = 1 OU x = 2 OU x = 3 ... OU x = 99) en ce qui concerne le moteur.

Answer 4

Lorsque vous fournir de nombreuses valeurs pour l' 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.