Explication détaillée de l'optimisation des requêtes MySQL

Cet article partage principalement avec vous l'explication détaillée de l'optimisation des requêtes MySQL. Il l'explique principalement sous forme de texte. J'espère qu'il pourra vous aider.

Les méthodes d'optimisation SQL populaires sur Internet sont les suivantes :

1 Essayez d'éviter de l'utiliser dans la clause Where ! = ou <>, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table.

2. Pour optimiser la requête, essayez d'éviter les analyses de table complètes. Tout d'abord, pensez à créer des index sur les colonnes impliquées dans Where et à trier par.

3. Essayez d'éviter de juger la valeur nulle du champ dans la clause Where, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table, telle que :
sélectionnez l'identifiant à partir de t où num est nul.
OK Définissez la valeur par défaut 0 sur num, assurez-vous qu'il n'y a pas de valeur nulle dans la colonne num du tableau, puis interrogez comme ceci :
sélectionnez l'identifiant à partir de t où num=0
4. Essayez pour éviter d'utiliser ou dans la clause Where pour connecter les conditions. Sinon, le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table, telle que :
sélectionnez l'identifiant à partir de t où num=10 ou num=20
Vous pouvez interroger comme ceci :
select id from t Where num=10
union all
select id from t Where num=20
5. La requête suivante entraînera également une analyse complète de la table : (ne peut pas précéder le signe pourcentage)
sélectionnez l'identifiant à partir de t où le nom est comme '?c %'
Pour améliorer l'efficacité, vous pouvez envisager une recherche en texte intégral.
6. In et not in doivent également être utilisés avec prudence, sinon cela entraînera une analyse complète de la table, telle que :
select id from t Where num in(1,2,3)
For valeurs continues, vous pouvez utiliser Ne pas utiliser entre :
sélectionnez l'identifiant à partir de t où num entre 1 et 3
7. Si des paramètres sont utilisés dans la clause Where, cela entraînera également une analyse complète de la table. Étant donné que SQL résout les variables locales uniquement au moment de l'exécution, l'optimiseur ne peut pas différer la sélection d'un plan d'accès jusqu'au moment de l'exécution ; il doit effectuer la sélection au moment de la compilation ; Cependant, si le plan d'accès est créé au moment de la compilation, les valeurs des variables sont toujours inconnues et ne peuvent pas être utilisées comme entrée pour la sélection d'index. Par exemple, l'instruction suivante effectuera une analyse complète de la table :
select id from twhere num=@num
Vous pouvez la modifier pour forcer la requête à utiliser l'index :
select id from t with ( index (nom de l'index)) où num= @num
8. Vous devez essayer d'éviter d'effectuer des opérations d'expression sur les champs de la clause Where, ce qui obligerait le moteur à abandonner l'utilisation de l'index et à effectuer une analyse complète de la table. Par exemple :
sélectionnez l'identifiant de t où num/2=100
doit être remplacé par :
sélectionnez l'identifiant de t où num=100*2
9. Essayez d'éviter d'associer des champs dans où clause Effectuer une opération de fonction, ce qui obligera le moteur à abandonner l'utilisation de l'index et à effectuer une analyse complète de la table. Par exemple :
select id from twhere substring(name,1,3)='abc'–name id commençant par abc
select id from twhere datediff(day,createdate,'2005-11-30′ )=0–'2005-11-30' l'identifiant généré
doit être remplacé par :
sélectionnez l'identifiant à partir de t où le nom est comme 'abc%'
sélectionnez l'identifiant à partir de t où créé>='2005-11 -30′ et createate<'2005-12-1′
10. N'effectuez pas de fonctions, d'opérations arithmétiques ou d'autres opérations d'expression sur le côté gauche de "=" dans la clause Where, sinon le système pourrait ne pas être en mesure de utiliser l'index correctement.
11. Lors de l'utilisation d'un champ d'index comme condition, si l'index est un index composite, le premier champ de l'index doit être utilisé comme condition pour garantir que le système utilise l'index, sinon l'index ne sera pas utilisé , et l'ordre des champs doit être autant que possible cohérent avec l'ordre de l'index.
12. N'écrivez pas de requêtes dénuées de sens. Par exemple, si vous devez générer une structure de table vide :
sélectionnez col1,col2 dans #t à partir de t où 1=0
Ce type de code ne le fera pas. renvoie n'importe quel jeu de résultats. Cependant, il consommera des ressources système et doit être remplacé par ceci :
create table #t(...)
13. Dans de nombreux cas, c'est un bon choix d'utiliser exist au lieu de dans :
sélectionnez num à partir de a où num in( sélectionnez num à partir de b)
Remplacez par l'instruction suivante :
sélectionnez num à partir de a où existe (sélectionnez 1 à partir de b où num=a.num)
14. Tous les index ne sont pas valides pour les requêtes, l'optimisation des requêtes SQL est basée sur les données de la table. Lorsqu'il y a une grande quantité de données en double dans la colonne d'index, la requête SQL peut ne pas utiliser l'index. il y a un champ sexe dans une table, les hommes et les femmes sont presque la moitié, alors même si le champ est construit sur le sexe, les index n'ont également aucun effet sur l'efficacité des requêtes.
15. Plus il y a d'index, mieux c'est. Bien que l'index puisse améliorer l'efficacité de la sélection correspondante, il réduit également l'efficacité de l'insertion et de la mise à jour, car l'index peut être reconstruit lors de l'insertion ou de la mise à jour, alors comment créer un index nécessite À considérer attentivement et au cas par cas. Il est préférable de ne pas avoir plus de 6 index sur une table. S'il y en a trop, vous devez vous demander s'il est nécessaire de créer des index sur certaines colonnes qui ne sont pas couramment utilisées.
16. Vous devez éviter autant que possible de mettre à jour les colonnes de données d'index clusterisé, car l'ordre des colonnes de données d'index clusterisé est l'ordre de stockage physique des enregistrements de la table. Une fois la valeur de la colonne modifiée, cela entraînera un ajustement de l'ordre des enregistrements. l'intégralité des enregistrements de la table, ce qui coûtera beaucoup d'argent. Si le système d'application doit mettre à jour fréquemment les colonnes de données d'index clusterisé, vous devez alors déterminer si l'index doit être construit en tant qu'index clusterisé.
17. Essayez d'utiliser des champs numériques. Si les champs contiennent uniquement des informations numériques, essayez de ne pas les concevoir comme des champs de caractères. Cela réduirait les performances des requêtes et des connexions et augmenterait la surcharge de stockage. En effet, le moteur comparera chaque caractère de la chaîne un par un lors du traitement des requêtes et des connexions, et une seule comparaison suffit pour les types numériques.
18. Utilisez /n au lieu de /n autant que possible, car premièrement, l'espace de stockage des champs de longueur variable est petit, ce qui peut économiser de l'espace de stockage. Deuxièmement, pour les requêtes, l'efficacité de la recherche dans un champ relativement petit. est évidemment plus élevé.
19. N'utilisez select * from t nulle part, remplacez "*" par une liste de champs spécifique et ne renvoyez aucun champ inutilisé.
20. Essayez d'utiliser des variables de table au lieu de tables temporaires. Si la variable de table contient une grande quantité de données, sachez que les index sont très limités (uniquement les index de clé primaire).
21. Évitez de créer et de supprimer fréquemment des tables temporaires pour réduire la consommation des ressources des tables système.
22. Les tables temporaires ne sont pas inutilisables. Leur utilisation appropriée peut rendre certaines routines plus efficaces, par exemple lorsque vous devez référencer à plusieurs reprises une grande table ou un certain ensemble de données dans une table couramment utilisée. Cependant, pour les événements ponctuels, il est préférable d'utiliser des tables d'export.
23. Lors de la création d'une table temporaire, si la quantité de données insérées en même temps est importante, vous pouvez utiliser select into au lieu de create table pour éviter d'augmenter la vitesse d'un grand nombre de journaux si la quantité de données est élevée ; pas grand, afin d'alléger les ressources de la table système, vous devez d'abord créer une table, puis l'insérer.
24. Si des tables temporaires sont utilisées, toutes les tables temporaires doivent être explicitement supprimées à la fin de la procédure stockée, d'abord tronquer la table, puis supprimer la table. Cela peut éviter le verrouillage à long terme des tables système.
25. Essayez d'éviter d'utiliser des curseurs car les curseurs sont moins efficaces. Si les données exploitées par le curseur dépassent 10 000 lignes, vous devriez envisager de les réécrire.
26. Avant d'utiliser la méthode basée sur le curseur ou la méthode de table temporaire, vous devez d'abord rechercher une solution basée sur un ensemble pour résoudre le problème. La méthode basée sur un ensemble est généralement plus efficace.
27. Comme les tables temporaires, les curseurs ne sont pas inutilisables. L'utilisation de curseurs FAST_FORWARD avec de petits ensembles de données est souvent meilleure que d'autres méthodes de traitement ligne par ligne, en particulier lorsque plusieurs tables doivent être référencées pour obtenir les données requises. Les routines qui incluent des « totaux » dans un jeu de résultats sont généralement plus rapides que l'utilisation d'un curseur. Si le temps de développement le permet, vous pouvez essayer à la fois la méthode basée sur le curseur et la méthode basée sur les ensembles pour voir quelle méthode fonctionne le mieux.
28. Définissez SET NOCOUNT ON au début de toutes les procédures stockées et déclencheurs, et définissez SET NOCOUNT OFF à la fin. Il n'est pas nécessaire d'envoyer un message DONE_IN_PROC au client après chaque instruction de procédures stockées et de déclencheurs.
29. Essayez d'éviter de renvoyer de grandes quantités de données au client. Si la quantité de données est trop importante, vous devez vous demander si les exigences correspondantes sont raisonnables.
30. Essayez d'éviter les opérations de transactions volumineuses et d'améliorer la simultanéité du système.

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!