Comment écrire deux sous-requêtes dans MySQL

Environnement de test MySQL

Le tableau de test est le suivant

create table test_table2
(
    id int auto_increment primary key,
    pay_id int,
    pay_time datetime,
    other_col varchar(100)
)

Construire Une procédure stockée insère des données de test. La caractéristique des données de test est que pay_id est répétable. Ici, lorsque la procédure stockée est traitée et que 3 millions de données sont insérées dans une boucle, un pay_id répété est inséré tous les 100 éléments de. data.Le champ d'heure est aléatoire dans une certaine plage

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
    LANGUAGE SQLNOT DETERMINISTICCONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''BEGINdeclare cnt int;set  cnt = 0;while cnt< loopcount doinsert into test_table2 (pay_id,pay_time,other_col) values  (cnt,date_add(now(), interval floor(300*rand()) day),uuid());if (cnt mod 100 = 0) theninsert into test_table2 (pay_id,pay_time,other_col) values  (cnt,date_add(now(), interval floor(300*rand()) day),uuid());end if;set cnt = cnt + 1;    end while;END

Exécuter l'appel test_insert(3000000); Insérer 303 000 lignes de données

<🎜. >

Deux façons d'écrire des sous-requêtes

La signification générale de la requête est d'interroger les données avec un identifiant d'entreprise supérieur à 1 dans une certaine période de temps, il existe donc deux façons d'écrire.

La première façon d'écrire est la suivante : La sous-requête IN est l'ID d'entreprise dont les lignes de statistiques commerciales sont supérieures à 1 dans un certain laps de temps. La couche externe est interrogée en fonction des résultats de la sous-requête IN. . Il y a un index sur la colonne pay_id de l'identifiant de l'entreprise. , la logique est également relativement simple

Cette méthode d'écriture est en effet moins efficace lorsque la quantité de données est importante, et aucun index n'est nécessaire
<🎜. >

select * from test_table2 force index(idx_pay_id)where pay_id in (　　select pay_id from test_table2 
　　where pay_time>="2016-06-01 00:00:00" 
　　　　AND pay_time<="2017-07-03 12:59:59" 
　　group by pay_id 
　　having count(pay_id) > 1);

Résultat de l'exécution : 2,23 secondes

La deuxième façon d'écrire est de se joindre à la sous-requête de cette façon. d'écriture est équivalente à la méthode d'écriture de sous-requête IN ci-dessus. Le test suivant a révélé que l'efficacité est effectivement bonne. Beaucoup d'améliorations

select tpp1.* from test_table2 tpp1,
(     select pay_id 
     from test_table2 
      WHERE pay_time>="2016-07-01 00:00:00" 
     AND pay_time<="2017-07-03 12:59:59" 
     group by pay_id 
     having count(pay_id) > 1) tpp2 
where tpp1.pay_id=tpp2.pay_id

Résultat d'exécution : 0,48 seconde

Dans le plan d'exécution de la sous-requête, recherchez la requête externe. Il s'agit d'une méthode d'analyse de table complète. L'index sur pay_id n'est pas utilisé.

　

　

Plus tard, j'ai voulu utiliser l'indexation forcée pour la première méthode de requête. Bien qu'aucune erreur n'ait été signalée, j'ai trouvé que c'était le cas. était inutile du tout

　

Si la sous-requête est une valeur directe, l'index peut être utilisé normalement.

　

　

　 On peut voir que la prise en charge par MySQL des sous-requêtes IN n'est en effet pas très bonne.

De plus : l'ajout d'une table temporaire, bien que plus efficace que de nombreuses méthodes de jointure, est également plus efficace que l'utilisation directe de sous-requêtes IN. Dans ce cas, c'est également possible. sont utilisés, mais dans ce cas simple, il n'est pas nécessaire d'utiliser une table temporaire.

　

　

Ce qui suit est un test d'un cas similaire dans sqlserver 2014, Il existe des dizaines de milliers de structures et de quantités de tables de test identiques. On peut voir que dans ce cas, les deux méthodes d'écriture peuvent être considérées comme exactement les mêmes dans SQL Server (plan d'exécution + efficacité). SQL Server est bien meilleur que MySQL

Ce qui suit est le script d'environnement de test dans sqlserver.

Résumé : Dans les données MySQL, à partir de la version 5.7.18, les sous-requêtes IN doivent toujours être utilisées avec prudence

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!