La relation entre MySQL n'existe pas et les index

Dans certains scénarios commerciaux, l'instruction NOT EXISTS est utilisée pour garantir que les données renvoyées n'existent pas dans une collection spécifique. Certains collègues constateront que les performances de NOT EXISTS. est médiocre dans certains scénarios, et même certaines rumeurs en ligne disent "NOT EXISTS n'indexe pas ". Comment pouvons-nous optimiser l'instruction NOT EXISTS ?

Prenons l'exemple du SQL optimisé d'aujourd'hui. Avant l'optimisation, le SQL est :

SELECT count(1) FROM t_monitor m WHERE NOT exists (  SELECT 1   FROM t_alarm_realtime AS a   WHERE a.resource_id=m.resource_id   AND a.resource_type=m.resource_type   AND a.monitor_name=m.monitor_name)

Nous utilisons la méthode LEFT JOIN pour optimiser. Après l'optimisation, le SQL est :

SELECT count(1) FROM t_monitor m LEFT JOIN t_alarm_realtime AS a    ON a.resource_id=m.resource_id   AND a.resource_type=m.resource_type   AND a.monitor_name=m.monitor_name WHERE a.resource_id is NULL

Effet d'optimisation :

Le temps d'exécution avant l'optimisation était de plus de 29 secondes, et après l'optimisation, il était de 1,2 seconde, et l'optimisation a été améliorée de 25 fois.

NON EXISTE vraiment pas répertorié ?

Voir les plans d'exécution de deux SQL !

Plan d'exécution utilisant la méthode NOT EXIST :

Plan d'exécution utilisant la méthode LEFT JOIN :

D'après le plan d'exécution, les deux tables utilisent l'index , la différence est que NOT EXISTS utilise la méthode "DEPENDENT SUBQUERY", tandis que LEFT JOIN utilise une association de table ordinaire.

Lecture recommandée : Pourquoi les index peuvent-ils améliorer la vitesse des requêtes ?

Utilisez la méthode

Profiling fournie par MySQL pour visualiser le processus d'exécution des deux méthodes.

Processus d'exécution utilisant la méthode NOT EXIST :

Processus d'exécution utilisant la méthode LEFT JOIN :

Du point de vue du processus d'exécution, la consommation principale de la méthode LEFT JOIN est en Envoi de données Un élément (1,2s), tandis que la méthode NOT EXISTS consomme principalement deux éléments : l'exécution et l'Envoi de données, ce qui est limité par le fait que le Profilage ne stocke que 100 lignes d'enregistrements.

Depuis le profilage, nous ne pouvons voir que 47 combinaisons de "données d'exécution et d'envoi" (chaque combinaison représente environ 50us). D'après le plan d'exécution, nous pouvons voir que la quantité de données dans le t_monitor externe est de 578 436 lignes. , en ignorant les statistiques. Si les informations sont inexactes, l'utilisation de la méthode NOT EXISTS devrait produire 578436 combinaisons de "données d'exécution et d'envoi", avec une durée de consommation totale de =50μs*578436=28921800us=28,92s.

Cela peut être déduit du processus d'exécution ci-dessus :

Les performances d'exécution de la méthode NOT EXISTS dépendent fortement du nombre d'exécutions de NOT Sous-requête EXISTS, c'est-à-dire la quantité de données dans le jeu de résultats de la requête externe.

1. Lorsque la quantité de données N dans l'ensemble de résultats de la requête externe est faible, les performances d'exécution sont meilleures. Si N = 10, le temps d'exécution. est de 50 μs*10= 500us=0,005 s, plus une certaine consommation supplémentaire, le résultat de l'exécution peut également être compris entre 0,01 seconde ou 10 millisecondes. Ce temps de réponse devrait être acceptable pour la plupart des applications.

2. Lorsque le volume de données N de l'ensemble de résultats Chengxun externe est important, voire des dizaines de millions de données, les performances des requêtes de NOT EXISTS deviendront très mauvaises. Cela peut même consommer une grande quantité de ressources d’E/S et de processeur du serveur, affectant le fonctionnement normal d’autres entreprises.

En plus des problèmes ci-dessus, au cours du processus d'optimisation, il a été constaté que la colonne resource_id qui doit stocker les mêmes données est définie différemment dans les deux tables, une table est VARCHAR et l'autre table est BIGINT, Le type de champ de l'ensemble de résultats externe est différent du type de champ dans la table de mots NOT EXIST, ce qui fait que l'index ne peut pas être utilisé dans la sous-requête NOT EXISTS, ce qui entraîne de mauvaises performances de sous-requête et affecte finalement les performances d'exécution de l'ensemble de la requête.

Jingdong Mall a également vu un grand nombre de cas similaires. Certaines tables utilisent VARCHAR pour stocker les numéros de commande, tandis que d'autres tables utilisent BIGINT pour les stocker. Les performances sont extrêmement médiocres. les tables sont gérées. J'espère que mes collègues R&D prendront cela comme un avertissement. Suivez le compte public Java Technology Stack et répondez à m36 pour obtenir une copie de la réglementation militaire MySQL R&D.

Recommandations d'apprentissage associées : Tutoriel vidéo MySQL

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!