30 méthodes courantes pour optimiser SQL

mysqlQuelques optimisations lors du traitement de données massivesRequêteMéthode Speed ​​​​

Récemment, en raison de besoins professionnels, j'ai a commencé à prêter attention aux méthodes d'optimisation pertinentes pour l'instruction de requête select de la base de données MySQL.

Parce que dans le projet auquel j'ai participé, il a été constaté que lorsque le volume de données de la table mysql atteint des millions, l'efficacité des requêtes SQL ordinaires s'effondre, et s'il existe de nombreuses conditions de requête dans lesquelles, la requête la vitesse est tout simplement intolérable. Une fois, j'ai testé une requête conditionnelle sur une table contenant plus de 4 millions d'enregistrements (avec index ), et le temps de requête atteignait 40 secondes. Je pense qu'un délai de requête aussi élevé rendra fou n'importe quel utilisateur. . Par conséquent, il est très important de savoir comment améliorer l’efficacité des requêtes d’instructions SQL. Voici 30 méthodes d'optimisation des instructions de requête SQL largement diffusées sur Internet :

1. Essayez d'éviter d'utiliser les opérateurs != ou <> dans les clauses Where , 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. Pensez d'abord à créer des index sur les colonnes impliquées dans Where et trierpar.

3. Essayez d'éviter de porter des jugements de valeur null sur les champs de 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
Vous pouvez définir la valeur par défaut 0 sur num pour vous assurer qu'il n'y a pas de valeur nulle dans la colonne num du tableau, puis interroger comme ceci :
sélectionnez l'identifiant à partir de t où num=0

4. Essayez d'é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 de t où num=10 ou num=20
Vous pouvez interroger comme ceci :
sélectionnez l'identifiant à partir de t où num=10
union tous
sélectionnez l'identifiant à partir de t où 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 de pourcentage)
sélectionnez l'identifiant à partir de t où le nom ressemble à '�c%'
Pour améliorer l'efficacité, vous pouvez envisager la 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)
Pour les valeurs continues, si vous pouvez utiliser between, n'utilisez pas in :
sélectionnez l'identifiant de t où num entre 1 et 3

7. Si des paramètres sont utilisés dans la clause Where, cela sera provoque également une analyse complète de la table. Étant donné que SQL ne résout que les variables locales au moment de l'exécution, l'optimiseur ne peut pas différer la sélection d'un plan d'accès jusqu'à 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 devriez essayer d'éviter d'effectuer des opérations expression sur les champs de la clause Where, ce qui entraînerait l'abandon du moteur en utilisant la indexer et effectuer une analyse complète de la table. Par exemple :
sélectionnez l'identifiant à partir de t où num/2=100
doit être remplacé par :
sélectionnez l'identifiant à partir de t où num=100*2

9. Essayez d'éviter d'effectuer des opérations de fonction sur les champs de la clause Where , ce qui entraînerait l'abandon par le moteur de l'utilisation de l'index et une analyse complète de la table. Tels que :
sélectionnez l'identifiant à partir de t où substring(name,1,3)='abc'–name id commençant par abc
select id from twheredatediff ( 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ù createate>='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 le où Dans le cas contraire, le système pourrait ne pas être en mesure d'utiliser l'index correctement.

11. Lors de l'utilisation d'un champ d'index comme condition, si l'index est un index composé, le premier champ de l'index doit être utilisé comme condition pour garantir que le système utilise l'index, sinon l'index sera not 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 le code ne renverra rien. Le jeu de résultats, mais il consommera des ressources système, doit être modifié comme suit :
create table #t(...)

13. Dans de nombreux cas, using exist à la place. de in est un bon choix :
sélectionnez num depuis a où num in(sélectionnez num depuis b)
Remplacez par l'instruction suivante :
sélectionnez num depuis a où existe (sélectionnez 1 depuis b où num=a.num)

14. Tous les index ne sont pas valides pour les requêtes. Les requêtes SQL sont optimisées en fonction des données de la table. Lorsqu'il y a une grande quantité de duplication de données dans la colonne d'index, le Les requêtes SQL ne peuvent pas utiliser d'index, par exemple, s'il y a un champ sexe dans une table et que près de la moitié sont des hommes et l'autre moitié des femmes, alors même si un index est construit sur le sexe, cela n'aura aucun effet sur l'efficacité de la requête.

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, et alors. ? L'indexation nécessite un examen attentif et dépendra des circonstances. 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 de mettre à jour les colonnes de données d'index clusterisées autant que possible, car l'ordre des colonnes de données d'index clusterisé est l'ordre de stockage physique des enregistrements de table. Une fois la valeur de la colonne modifiée, l'ensemble de l'enregistrement de la table sera affecté. L'ajustement de l'ordre consommera des ressources considérables. Si le système d'application doit mettre à jour fréquemment les colonnes de données de l'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éduira les performances des requêtes et des connexions et augmentera 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 autant que possible varchar/nvarchar au lieu de char/nchar, car tout d'abord, les champs de longueur variable ont un petit espace de stockage et peuvent économiser de l'espace de stockage. Deuxièmement, pour les requêtes, dans un champ relativement petit. La Recherche est évidemment plus efficace.

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 certaines données dans une grande table ou une table couramment utilisée. Réglez l'heure . Cependant, pour les événements ponctuels, il est préférable d'utiliser une table d'exportation.

23. Lors de la création d'une table temporaire, si la quantité de données insérée en même temps est très importante, vous pouvez utiliser select into au lieu de create table pour éviter qu'un grand nombre de journaux n'augmentent la table. vitesse ; si la quantité de données n'est pas grande. Afin d'alléger les ressources de la table système, vous devez d'abord créer la 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 Tout d'abord, tronquer la table, puis supprimer la table, afin d'éviter le système. surcharge de table. Verrouillé depuis longtemps.

25. Essayez d'éviter

d'utiliser des curseurs, car les curseurs sont moins efficaces si les données de opération du curseur dépassent 10 000 lignes, alors 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. Utiliser pour les petits ensembles de données Les curseurs FAST_

FORWARD sont souvent supérieurs aux autres méthodes de traitement ligne par ligne, notamment 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 NO

COUNT 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 transaction volumineuses pour 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!