Après test, une requête conditionnelle était effectuée sur une table contenant plus de 4 millions d’enregistrements, et le temps de requête pouvait atteindre 40 secondes. Par conséquent, il est très important d’améliorer l’efficacité de la requête de requête SQL dans les requêtes. Voici quelques méthodes d’optimisation des requêtes largement diffusées sur Internet :
Tout d’abord, lorsque le volume de données est important, vous devriez éviter de scanner la table entière et envisager de construire des index sur les colonnes concernées par où et par ordre, ce qui peut grandement accélérer la récupération des données. Cependant, il existe certaines situations où l’indexation ne fonctionne pas :
1. Essayez d’éviter d’utiliser != ou <> opérateurs dans la clause where, sinon le moteur abandonnera l’utilisation des index et effectuera un balayage complet de table.
2. Essayez d’éviter le jugement de valeur nul sur les champs de la clause where, sinon le moteur abandonnera l’utilisation des index et effectuera un balayage complet des tables, tels que :
Sélectionner l’ID à partir de T où num est nul
Vous pouvez définir la valeur par défaut de 0 sur le num, vous assurer qu’il n’y a pas de valeur nulle dans la colonne numum du tableau, puis interroger comme ceci :
sélectionner l’id à partir de t où num=0
3. Essayez d’éviter d’utiliser OR dans les conditions où pour joindre, sinon le moteur abandonnera l’utilisation de l’index et effectuera un balayage complet de la table, par exemple :
Sélectionnez l’ID à partir de t où num=10 ou num=20
Vous pouvez interroger ainsi :
sélectionner l’ID à partir de t où num=10
Syndicalisation générale
sélectionner l’ID à partir de t où num=20
4. La requête suivante aboutira également à un balayage complet de la table :
Sélectionnez l’ID à partir de T où le nom est comme ' %ABC %'
Pour améliorer l’efficacité, envisagez la recherche en texte intégral.
5. En entrée et en non dans doivent également être utilisées avec prudence, sinon cela entraînera un balayage complet de la table, comme :
Sélectionnez l’ID à partir de T où num dans (1,2,3)
Pour les valeurs continues, si vous pouvez utiliser entre les deux, n’utilisez pas dans :
sélectionner l’ID à partir de t où num entre 1 et 3
6. Si vous utilisez le paramètre dans la clause where, cela provoquera également le balayage complet de la table. Parce que SQL ne résout que les variables locales à l’exécution, mais que l’optimiseur ne peut pas reporter la sélection des plans d’accès à l’exécution ; Il doit être sélectionné au moment de la compilation. Cependant, si un plan d’accès est établi au moment de la compilation, la valeur de la variable reste inconnue et ne peut donc pas être utilisée comme élément d’entrée pour la sélection de l’index. Les déclarations suivantes seront numérisées dans son intégralité :
sélectionner l’ID à partir de t où num=@num
Vous pouvez forcer la requête à utiliser un index à la place :
Sélectionnez l’ID à partir de t avec(index(index(index name)) où num=@num
7. Essayez d’éviter d’exprimer des champs dans la clause where, ce qui ferait que le moteur abandonnera l’utilisation de l’index et effectuerait un balayage complet de la table. Par exemple :
sélectionner l’ID de T où num/2=100
devrait être modifié en :
sélectionner l’ID à partir de t où num=100*2
8. Essayez d’éviter d’effectuer des opérations de fonction sur les champs de la clause where, ce qui poussera le moteur à abandonner l’utilisation des index et à effectuer un balayage complet de table. Par exemple :
Select ID from t où substring(name,1,3)='abc' – identifiant de nom qui commence par abc
Sélectionnez l’ID à partir de T où DateDiff(Day,Createdate,'2005-11-30′)=0–'2005-11-30′ a généré l’ID
devrait être modifié en :
Sélectionnez l’ID à partir de T où le nom est comme 'ABC %'
sélectionner l’ID à partir de T où créer>='2005-11-30′ et créer<'2005-12-1′
9. Ne pas effectuer de fonctions, d’opérations arithmétiques ou d’autres opérations d’expression à gauche de la clause « = » dans la clause where, sinon le système pourrait ne pas être en mesure d’utiliser correctement l’index.
10. Lors de l’utilisation d’un champ d’index comme condition, si l’indice est un indice composite, alors le premier champ de l’index doit être utilisé comme condition pour garantir que le système utilise l’index, sinon l’indice ne sera pas utilisé, et l’ordre des champs doit être cohérent autant que possible avec l’ordre de l’index.
11. N’écrivez pas certaines requêtes sans signification, comme la génération d’une structure de table vide :
Sélectionnez col1,col2 dans #t à partir de t où 1=0
Ce type de code ne retourne aucun ensemble de résultats, mais consomme les ressources système, donc il devrait être changé pour quelque chose comme ceci :
créer un tableau #t(...)
12. Souvent, il est judicieux d’utiliser existes plutôt que dans :
Sélectionnez num depuis A où Num In(Sélectionnez Num depuis B)
Remplacez par l’affirmation suivante :
Sélectionnez num à partir de A où existe (sélectionnez 1 à partir de B où num=A.num)
Points à surveiller lors de la constitution d’un indice :
1. Tous les index ne sont pas valides pour les requêtes, SQL est basé sur les données du tableau pour optimiser la requête, lorsque la colonne d’index comporte une grande duplication de données, les requêtes SQL peuvent ne pas utiliser l’index, par exemple une table a des champs sexe, mâle, femelle presque la moitié chacun, et même si l’index est basé sur le sexe, cela ne jouera pas d’impact sur l’efficacité des requêtes.
2. Plus il y a d’indices, mieux c’est, l’index peut certainement améliorer l’efficacité du select correspondant, mais il réduit aussi 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, donc la manière de construire un index doit être soigneusement réfléchie, selon la situation spécifique. Il est préférable de ne pas avoir plus de 6 index dans un tableau, et s’il y en a trop, de se demander s’il est nécessaire de construire des index sur certaines colonnes peu utilisées.
3. Éviter autant que possible de mettre à jour les colonnes de données indexées groupées, car l’ordre des colonnes indexées regroupées est l’ordre physique de stockage des enregistrements de table, et une fois que la valeur de la colonne change, cela entraînera un ajustement de l’ordre de l’ensemble des enregistrements de table, ce qui consommera des ressources considérables. Si le système applicatif doit mettre à jour fréquemment les colonnes d’index groupées, il doit envisager si l’index doit être construit comme un index clusteré.
Autres points à noter :
1. Essayez d’utiliser des champs numériques, et essayez de ne pas concevoir des champs contenant uniquement des informations numériques sous forme de caractères, ce qui réduira la performance des requêtes et des connexions, et augmentera la surcharge de stockage. Cela s’explique par le fait que le moteur compare chaque caractère de la chaîne un par un lors du traitement des requêtes et des jointures, alors que pour les types numériques, il n’a besoin d’être comparé qu’une seule fois.
2. N’utilisez nulle part sélectionner * depuis t, remplacez « * » par une liste de champs spécifique, et ne retournez aucun champ non utilisé.
3. Essayez d’utiliser des variables de table au lieu de tables temporaires. Si la variable de table contient une grande quantité de données, notez que l’index est très limité (seul l’index de clé primaire).
4. Éviter de créer et supprimer fréquemment des tables temporaires afin de réduire la consommation des ressources des tables système.
5. Les tables temporaires ne sont pas inutilisables, et les utiliser de manière appropriée peut rendre certaines routines plus efficaces, par exemple lorsqu’il faut référencer à plusieurs reprises une grande table ou un jeu de données dans une table couramment utilisée. Cependant, pour les événements ponctuels, il vaut mieux utiliser une table d’exportation.
6. Lors de la création d’une table temporaire, si la quantité de données insérées à un moment donné 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 n’est pas importante, afin de faciliter les ressources de la table système, vous devez d’abord créer la table puis l’insérer.
7. Si une table temporaire est utilisée, veillez à supprimer explicitement toutes les tables temporaires à la fin de la procédure stockée, tronquez d’abord la table, puis supprimez la table, afin d’éviter un verrouillage long de la table système.
8. Essayez d’éviter d’utiliser le curseur, car l’efficacité du curseur est faible ; si les données exploitées par le curseur dépassent 10 000 lignes, vous devriez envisager de réécrire.
9. Avant d’utiliser la méthode basée sur le curseur ou la méthode de table temporaire, vous devez d’abord chercher des solutions basées sur des ensembles pour résoudre le problème, et la méthode basée sur les ensembles est généralement plus efficace.
10. Comme les tables temporaires, le curseur n’est pas inutilisable. Utiliser FAST_FORWARD curseurs pour de petits ensembles de données est souvent préférable à d’autres méthodes de traitement ligne par ligne, surtout si vous devez référencer plusieurs tables pour obtenir les données nécessaires. Les routines qui incluent le « total » dans le jeu de résultats sont généralement plus rapides que celles exécutées avec le curseur. Si le temps de développement le permet, on peut essayer des méthodes basées sur des curseurs et sur des ensembles pour voir laquelle fonctionne mieux.
11. Définir SET NOCOUNT ON au début de toutes les procédures et déclencheurs stockés, et SET NOCOUNT OFF à la fin. Il n’est pas nécessaire d’envoyer DONE_IN_PROC messages au client après avoir exécuté chaque instruction de la procédure stockée et le déclencheur.
12. Essayez d’éviter de retourner de grosses données au client ; si le volume de données est trop important, vous devez considérer si la demande correspondante est raisonnable.
13. Essayer d’éviter les opérations de transaction importantes et améliorer la capacité de concurrence du système.
|
Publié sur 11/05/2018 13:57:06
|
|
|
Publié sur 17/05/2018 10:12:27