Comment MySQL optimise-t-il les sous-requêtes ?

Dans le développement réel, nous utilisons souvent la sous-requête in, qui équivaut à un ensemble de valeurs correspondant aux champs spécifiés, nous permettant de filtrer et d'interroger plus facilement. Cependant, cette méthode de sous-requête entraînera d’importants problèmes de performances lorsque la quantité de données est importante. Cet article vous présentera comment MySQL optimise la sous-requête in.

1. Évitez d'utiliser dans la sous-requête

Dans les projets réels, nous voyons souvent cette façon d'écrire :

SELECT *
FROM table
WHERE col1 IN (SELECT col1 FROM table2 WHERE condition);

Cette instruction est la plus simple dans la sous-requête. Selon les conditions de la table2, les valeurs de plusieurs lignes de col1 sont. retiré, fait correspondre la valeur dans le tableau et renvoie la ligne correspondante. Cependant, écrire de cette façon entraînera un goulot d'étranglement en termes de performances, car la façon dont MySQL exécute en interne la sous-requête in mettra en cache le jeu de résultats de la sous-requête en mémoire (ou sur le disque). Ensuite, chaque fois qu'un jugement est exécuté, la mémoire (ou le disque). ) sera mis en cache. ), cela entraînera un grand nombre d'opérations d'E/S, et lorsque le jeu de résultats de la sous-requête est volumineux, il occupera également une grande quantité de mémoire.

Par conséquent, essayez d'éviter d'utiliser une sous-requête dans le développement réel, et vous pouvez utiliser join à la place.

2. Utilisez join au lieu de dans la sous-requête

Utilisez join pour remplacer dans la sous-requête Il n'y a aucune différence entre la méthode d'écriture de la sous-requête et la manière d'écrire la sous-requête. Elle convertit simplement l'original dans la sous-requête en jointure pour optimiser la syntaxe SQL. , et l'efficacité d'exécution est meilleure que celle des sous-requêtes. Recherchez col1, puis joignez-le à col1 dans la table1, comme indiqué ci-dessous :

SELECT table.*
FROM table
JOIN table2 ON table.col1 = table2.col1
WHERE table2.condition;

Par rapport à la sous-requête in, l'utilisation de join peut connecter l'ensemble de résultats de la sous-requête à la table, réduisant ainsi beaucoup de mémoire (disque) en lecture. opération.

3. Utiliser existe au lieu de dans la sous-requête

Utiliser existe au lieu de dans la sous-requête, c'est en fait utiliser la jointure. Contrairement à la sous-requête in, la sous-requête exist n'a besoin que d'effectuer un jugement simple, quelle que soit la taille du jeu de résultats. Ce qui suit est un exemple de syntaxe de sous-requête exist :

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition);

L'utilisation de la sous-requête exist au lieu de in a considérablement amélioré l'efficacité et peut économiser beaucoup d'E/S et de consommation de mémoire.

4. Utilisez l'index pour optimiser l'instruction in

Si vous pouvez utiliser l'index pour accélérer la sous-requête in pendant la requête, l'efficacité de la requête sera également grandement améliorée. Les index MySQL sont divisés en trois types : index de clé primaire, index unique et index ordinaire. Si vous pouvez créer un index approprié, vous pouvez éviter que MySQL n'effectue une analyse complète de la table et améliorer l'efficacité des requêtes.

CREATE INDEX idx_col1 ON table (col1);

Lorsque la valeur col1 est grande, l'utilisation de l'index optimisera considérablement l'efficacité des requêtes et réduira les problèmes d'efficacité causés par l'utilisation dans une sous-requête.

5. Utilisez la limite et existe pour optimiser dans la sous-requête

Si l'ensemble de résultats de la sous-requête est très volumineux, nous pouvons utiliser la limite et existe pour effectuer une requête de pagination dessus tout en évitant une analyse complète de la table pour atteindre l'objectif d'optimisation de l'efficacité des requêtes. .

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition LIMIT 1000, 20);

La fonction de cette instruction SQL est de trouver l'ensemble de résultats de la table2, puis d'utiliser col1 et la table pour effectuer des existe, limiter l'ensemble de résultats de la requête à 20 et interroger à partir de la 1000ème ligne.

6. Utilisation appropriée de l'optimisation de la mémoire dans l'instruction

Si le résultat de la sous-requête in utilisé dans la requête n'a pas beaucoup de lignes, nous pouvons utiliser set au lieu de in. set stocke l'ensemble de résultats de la sous-requête in en mémoire pour une correspondance de requête ultérieure. L'utilisation de la mémoire pour optimiser l'instruction in peut également améliorer considérablement les performances.

SET @col1 = (SELECT GROUP_CONCAT(DISTINCT col1) FROM table2 WHERE condition);
SELECT *
FROM table
WHERE FIND_IN_SET(table.col1, @col1);

Cette instruction utilise d'abord select pour la correspondance des données, puis utilise GROUP_CONCAT pour connecter la liste de valeurs col1 dans une chaîne, qui est stockée dans @col1. Dans les requêtes suivantes, FIND_IN_SET est utilisé pour la correspondance et la mise en cache mémoire est utilisée pour optimiser l'efficacité des requêtes.

7. Résumé

dans Lorsque vous utilisez des sous-requêtes, veillez à éviter les analyses de table complètes, en particulier lorsque la quantité de données est importante, sinon cela entraînerait de graves problèmes de performances. En rejoignant, existe, en optimisant les index, en utilisant les limites de manière appropriée, en utilisant la mémoire et d'autres méthodes, vous pouvez améliorer l'efficacité des requêtes et optimiser les performances des sous-requêtes. Dans les projets réels, nous devons choisir la meilleure solution en fonction de la situation spécifique pour obtenir le meilleur effet d'optimisation des performances.

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!