Quelles sont les méthodes pour améliorer l'efficacité des requêtes MySQL et optimiser la vitesse des requêtes ?

1. Utilisez le mot-clé EXPLAIN pour évaluer les défauts de l'instruction de requête

L'auteur a exécuté une simple instruction de requête Select dans la base de données pour obtenir toutes les informations d'un tableau, comme le montre la figure ci-dessous Afficher. L'administrateur de la base de données souhaite maintenant savoir quel travail la base de données a effectué lors de l'exécution de cette instruction, ou s'il existe une possibilité d'optimisation supplémentaire de cette instruction de requête. Si vous souhaitez connaître ces informations, vous pouvez ajouter un mot-clé Explain à l'instruction de requête.

Les données d'une table peuvent être interrogées à partir de la base de données via l'instruction de requête Select. L’efficacité d’exécution et la marge d’optimisation ne peuvent pas être dérivées de cette simple instruction de requête. Afin de comprendre des informations plus détaillées, vous devez ajouter le mot-clé Explain. Comme le montre la figure ci-dessous :

Après avoir ajouté le mot-clé Explain, le système n'a pas interrogé les données du tableau, mais a seulement affiché certaines des données pendant le processus de requête. Ces informations sont très utiles pour l’optimisation ultérieure de nos requêtes de base de données. À partir des informations ci-dessus, nous pouvons voir que l’utilisateur vient de faire une simple requête. Dans cette requête, aucun index, mot-clé ou instruction Where n'est utilisé. Pour cette raison, cette instruction de requête n’est pas très raisonnable. Bien qu'il puisse trouver le résultat final correct, l'efficacité de ses requêtes peut ne pas être très évidente. À cette fin, les experts en bases de données peuvent effectuer des optimisations basées sur les informations présentées ci-dessus. Quel serait le résultat si nous ajoutions maintenant une instruction WHERE à notre requête, comme le montre l'image suivante ?.

Lorsque l'instruction Where est utilisée dans le dernier champ Extra, le système montrera qu'elle a été utilisée. Lors de l'optimisation de la base de données, nous devons capturer les champs NULL ou les champs avec un contenu vide dans les résultats. Ces lieux font souvent l’objet de notre optimisation. Vous pouvez optimiser cette instruction Select et améliorer l'efficacité des requêtes en définissant des mots-clés ou des index dans le tableau, comme indiqué dans la figure.

2. Utilisez des colonnes du même type lors de la comparaison des données pour améliorer l'efficacité des requêtes

Lors de l'interrogation de données, des conditions de jugement sont parfois ajoutées à l'instruction conditionnelle. Il existe désormais deux tableaux : le tableau des informations de base sur l'utilisateur et le tableau des autorisations utilisateur, qui sont liés par numéro d'utilisateur. Pour interroger les informations d'autorisation de chaque utilisateur, vous devez utiliser le numéro d'utilisateur comme condition de requête. Supposons maintenant que le champ du numéro d'utilisateur dans la table d'informations de base de l'utilisateur soit de type CHAR et que le champ du numéro d'utilisateur dans la table des autorisations utilisateur soit de type VARCHAR ; Bien que ces deux types de données soient tous deux des types de caractères, ils ne sont pas du même type. Effectuez maintenant une requête de corrélation sur ces deux tables. Quelle est l'efficacité de la requête ? La première chose à déterminer est que, bien qu'il s'agisse de types de données caractère différents, ils sont compatibles entre eux. Vous pouvez toujours obtenir le bon résultat à la fin. Après avoir clarifié ce point, voyons si cette instruction de requête peut être optimisée

Faisons une autre hypothèse. Désormais, le type de données du numéro d'utilisateur dans les deux tables est CHAR. Effectuez maintenant une requête associée sur ces deux tables. Les résultats obtenus sont-ils les mêmes ? Le résultat de notre test est que les résultats de la requête sont les mêmes, mais le temps passé est différent. Et à mesure que la quantité de données augmente, le décalage horaire entre les deux requêtes deviendra de plus en plus long. De là, nous pouvons savoir que même si les deux instructions de requête sont équivalentes, leur efficacité de requête est différente.

Bien que les types de données soient compatibles entre eux dans la base de données MySQL, des comparaisons peuvent toujours être faites. Cependant, l’efficacité de sa requête en sera affectée. Afin d'améliorer l'efficacité des requêtes de base de données, l'auteur recommande qu'il soit préférable de comparer les colonnes du même type dans l'instruction de condition de requête. Dans les mêmes conditions, le même type de colonne peut offrir de meilleures performances que des colonnes de types différents. Ceci est particulièrement important dans les bases de données contenant de grandes quantités de données.

Cependant, cette optimisation doit impliquer le type de colonne de la table de données. Pour cette raison, vous devez en tenir compte lors de la conception du tableau de données. Pour le cas ci-dessus, nous pouvons spécifiquement ajouter une colonne ID utilisateur à ces deux tables. Vous pouvez utiliser une séquence de types entiers pour permettre au système de numéroter automatiquement. Comparez ensuite via cette colonne d'ID utilisateur lors de l'interrogation, au lieu de comparer via la colonne de numéro d'utilisateur d'origine. Relativement parlant, l'efficacité des opérations de requête sera plus élevée.

3. Utilisez les caractères génériques au début du mot-clé J'aime avec prudence

Dans le travail réel, l'auteur a constaté que de nombreux administrateurs de bases de données ont une mauvaise habitude. Lorsqu'ils utilisent des mots-clés comme Like, ils utilisent des caractères génériques de manière aléatoire. Par exemple, l'utilisateur doit désormais trouver toutes les informations sur le produit préfixées par « LOOK ». Lors des requêtes, les utilisateurs sont généralement habitués à utiliser des instructions similaires à celles-ci : utilisez comme « %LOOK% ». Cette instruction conditionnelle obtiendra tous les enregistrements contenant le mot LOOK dans le nom du produit, au lieu d'obtenir uniquement les informations sur le produit préfixées par LOOK.

Même si le résultat final peut être le même. Cependant, l’efficacité des requêtes des deux est différente. En fait, cela est dû en grande partie à une mauvaise conception des applications client. Par exemple, lors de la conception d'une application client, le système affichera par défaut un symbole %. Comme indiqué ci-dessous.

L'intention de cette conception est bonne, afin que le système puisse prendre en charge les requêtes floues. Mais les utilisateurs peuvent rencontrer des problèmes de fonctionnement réel. Si l'utilisateur ne saisit pas le mot LOOK avant % lors de la requête, mais saisit le mot après %. Car lors de l’interrogation, le curseur sera automatiquement positionné derrière le signe %. Normalement, les utilisateurs n'ajustent pas la position du curseur lors de la saisie. A cette époque, la situation mentionnée ci-dessus s'est produite.

Afin d'éviter des situations inattendues, l'auteur recommande fortement d'être très prudent lors de l'utilisation de mots-clés tels que J'aime suivis de caractères génériques. En particulier lors de la recherche d'enregistrements à partir d'une grande quantité de données, la position de ce caractère générique doit être utilisée au bon endroit. Si vous pouvez utiliser différents caractères génériques au début, essayez de ne pas utiliser de caractères génériques.

4. Essayez d'utiliser d'autres formulaires pour remplacer le mot-clé Like

Comme mentionné ci-dessus, vous devez faire attention à la position du caractère générique lorsque vous utilisez le mot-clé Like. En termes d'efficacité des requêtes, nous devons faire attention à la position des caractères génériques et éviter autant que possible d'utiliser le mot-clé « J'aime ». En fait, dans les instructions SQL, d'autres méthodes peuvent être utilisées pour remplacer le mot-clé Like. Par exemple, il existe une table de produits avec un numéro à 6 chiffres. Nous devons maintenant interroger le numéro de produit commençant par 9. Comment faire cela

Tout d'abord, vous pouvez utiliser des mots-clés J'aime, tels que LIKE « 9 % ». Notez la position de ce caractère générique. Cette instruction conditionnelle peut trouver les résultats requis. Mais du point de vue de l'optimisation des performances, cette affirmation n'est pas une bonne façon de gérer le problème. Nous pouvons également y parvenir grâce à certains compromis.

La seconde est obtenue en comparant des symboles. Cette phrase peut être reformulée ainsi : Ceci peut être réalisé en utilisant une méthode avec une valeur comprise entre 900 000 et 999 999. Bien que les résultats des deux requêtes soient les mêmes. Cependant, le temps d'interrogation de cette instruction est beaucoup plus court que celui de l'instruction ci-dessus utilisant le symbole Like.

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!