L'interrogation est l'opération la plus couramment utilisée dans la technologie des bases de données. Le processus d'opération de requête est relativement simple.Tout d'abord, l'instruction SQL de requête est émise par le client. Après avoir reçu l'instruction SQL envoyée par le client, le serveur de base de données exécute l'instruction SQL, puis renvoie les résultats de la requête au client. Bien que le processus soit très simple, différentes méthodes de requête et paramètres de base de données auront un impact important sur les performances des requêtes.
Par conséquent, cet article traite des techniques d'optimisation des requêtes couramment utilisées dans MySQL. Les discussions incluent : l'amélioration de la vitesse des requêtes grâce à la mise en mémoire tampon des requêtes ; l'optimisation automatique des requêtes par index ; la détection des requêtes inaccessibles et l'utilisation de diverses options de requête pour améliorer les performances ;
1. Améliorez la vitesse des requêtes grâce à la mise en mémoire tampon des requêtes
Généralement, lorsque nous utilisons des instructions SQL pour interroger, le serveur de base de données exécute cette instruction SQL chaque fois qu'il reçoit du SQL du client. Mais lorsque la même instruction SQL est reçue dans un certain intervalle (par exemple dans une minute), elle sera exécutée de la même manière. Bien que cela puisse garantir la nature en temps réel des données, la plupart du temps, les données ne nécessitent pas un temps réel complet, ce qui signifie qu'il peut y avoir un certain retard. Si tel est le cas, exécuter exactement le même SQL dans un court laps de temps n’en vaut pas la peine.
Heureusement, MySQL nous fournit la fonction de mise en mémoire tampon des requêtes (la mise en mémoire tampon des requêtes ne peut être utilisée que dans MySQL 4.0.1 et supérieur). Nous pouvons améliorer les performances des requêtes dans une certaine mesure grâce à la mise en cache des requêtes.
Nous pouvons définir le tampon de requête via le fichier my.ini dans le répertoire d'installation de MySQL. Le réglage est également très simple, il suffit de définir query_cache_type sur 1. Après avoir défini cet attribut, avant d'exécuter une instruction SELECT, MySQL vérifiera dans son tampon si la même instruction SELECT a été exécutée. Si tel est le cas et que le résultat de l'exécution n'a pas expiré, alors le résultat de la requête sera renvoyé directement au client. Mais lors de l'écriture d'instructions SQL, veuillez noter que le tampon de requête de MySQL est sensible à la casse. Les deux instructions SELECT suivantes sont les suivantes : SELECT * from TABLE1
SÉLECTIONNER * DEPUIS TABLEAU1
Les deux instructions SQL ci-dessus sont des SELECT complètement différents pour la mise en mémoire tampon des requêtes. De plus, le tampon de requête ne gère pas automatiquement les espaces. Par conséquent, lors de l'écriture d'instructions SQL, vous devez essayer de réduire l'utilisation des espaces, en particulier les espaces au début et à la fin de SQL (car le cache de requêtes n'intercepte pas automatiquement les espaces au début et à la fin de SQL). le début et la fin).
Bien que ne pas configurer de tampon de requête puisse parfois entraîner des pertes de performances, certaines instructions SQL doivent interroger les données en temps réel ou ne sont pas utilisées fréquemment (peut-être exécutées une ou deux fois par jour). Cela nécessite de désactiver la mise en mémoire tampon. Bien sûr, vous pouvez désactiver le cache de requêtes en définissant la valeur de query_cache_type, mais cela désactivera définitivement le cache de requêtes. MySQL 5.0 fournit une méthode pour désactiver temporairement le cache des requêtes : SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
Étant donné que l'instruction SQL ci-dessus utilise SQL_NO_CACHE, que cette instruction SQL ait déjà été exécutée ou non, le serveur ne recherchera pas dans le tampon et l'exécutera à chaque fois.
Nous pouvons également définir query_cache_type dans my.ini sur 2, de sorte que le cache de requêtes ne soit utilisé qu'après l'utilisation de SQL_CACHE. SELECT SQL_CALHE * FROM TABLE1
2. Optimisation automatique des requêtes par MySQL
Les index sont très importants pour les bases de données. Les index peuvent être utilisés pour améliorer les performances lors des requêtes. Mais parfois, l’utilisation d’index peut réduire les performances. Nous pouvons regarder la table SALES suivante : CREATE TABLE SALES
(
ID INT(10) NON NULL AUTO_INCREMENT,
NOM VARCHAR(100) NON NULL,
PRIX FLOTTANT NON NUL,
SALE_COUNT INT NON NULL,
SALE_DATE DATE NON NULLE,
CLÉ PRIMAIRE (ID),
INDICE (NOM),
INDEX (SALE_DATE)
)
Supposons qu'il y ait des millions de données stockées dans ce tableau et que nous souhaitions interroger le prix moyen du produit numéro 1 000 en 2004 et 2005. Nous pouvons écrire l'instruction SQL suivante : SELECT AVG(PRICE) FROM SALES
OÙ ID = 1000 ET SALE_DATE ENTRE '2004-01-01' ET '2005-12-31' ;
Si la quantité de ce produit est très importante, elle représente près de 50 % ou plus des enregistrements du tableau VENTES. Ensuite, utiliser l'index sur le champ SALE_DATE pour calculer la moyenne est un peu lent. Parce que si vous utilisez un index, vous devez trier l'index. Lorsqu'un très grand nombre d'enregistrements remplissent les conditions (par exemple, représentant 50 % ou plus des enregistrements dans l'ensemble de la table), la vitesse ralentira, il est donc préférable d'analyser l'intégralité de la table. Par conséquent, MySQL décidera automatiquement d'utiliser ou non l'index pour la requête en fonction de la proportion de données qui remplissent les conditions dans l'ensemble de la table.
Pour MySQL, l'index n'est pas utilisé lorsque la proportion des résultats de la requête ci-dessus par rapport aux enregistrements de la table entière est d'environ 30 %. Cette proportion est dérivée par les développeurs MySQL sur la base de leur expérience. Cependant, la valeur réelle de l'échelle varie en fonction du moteur de base de données utilisé.
3. Tri basé sur un index
L'une des faiblesses de MySQL est son tri. Bien que MySQL puisse interroger environ 15 000 enregistrements en 1 seconde, MySQL ne peut utiliser qu'un seul index lors de l'interrogation. Par conséquent, si la condition WHERE occupe déjà l’index, celui-ci ne sera pas utilisé dans le tri, ce qui réduira considérablement la vitesse de la requête. Nous pouvons examiner l'instruction SQL suivante : SELECT * FROM SALES WHERE NAME = "name" ORDER BY SALE_DATE DESC;
L'index sur le champ NAME a été utilisé dans la clause WHERE du SQL ci-dessus, donc l'index ne sera plus utilisé lors du tri de SALE_DATE. Afin de résoudre ce problème, nous pouvons créer un index composite sur la table SALES : ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
De cette façon, la vitesse sera grandement améliorée lors de l'utilisation de l'instruction SELECT ci-dessus pour interroger. Mais attention, lorsque vous utilisez cette méthode, assurez-vous qu'il n'y a pas de champ de tri dans la clause WHERE. Dans l'exemple ci-dessus, vous ne pouvez pas utiliser SALE_DATE pour interroger. Sinon, bien que le tri soit plus rapide, il n'y a pas d'index séparé sur le champ SALE_DATE. , donc la requête ralentira.
4. Détection des requêtes inaccessibles
Lors de l'exécution d'instructions SQL, vous rencontrerez inévitablement certaines conditions qui doivent être fausses. La condition dite « obligatoirement fausse » est que, quelle que soit la façon dont les données du tableau changent, cette condition est fausse. Tel que WHERE valeur < 100 ET valeur > 200. Nous ne pouvons jamais trouver un nombre qui soit à la fois inférieur à 100 et supérieur à 200.
Si vous rencontrez de telles conditions de requête, il n'est pas nécessaire d'exécuter de telles instructions SQL. Heureusement, MySQL peut détecter automatiquement cette situation. Par exemple, nous pouvons regarder l'instruction SQL suivante : SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"
L'instruction de requête ci-dessus recherche les enregistrements dont le NOM est égal à la fois à nom1 et à nom2. Évidemment, il s’agit d’une requête inaccessible et la condition WHERE doit être fausse. Avant que MySQL n'exécute l'instruction SQL, il analysera d'abord si la condition WHERE est une requête inaccessible. Si tel est le cas, l'instruction SQL ne sera plus exécutée. Pour vérifier cela. Nous utilisons d'abord EXPLAIN pour tester le SQL suivant : EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"
La requête ci-dessus est une requête normale. Nous pouvons voir que l'élément de table dans les données d'informations d'exécution renvoyées par EXPLAIN est SALES. Cela montre que MySQL gère SALES. Examinez à nouveau les instructions suivantes : EXPLAIN SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"
On voit que l'élément de la table est vide, ce qui signifie que MySQL n'a pas opéré sur la table SALES.
5. Utilisez diverses sélections de requêtes pour améliorer les performances
En plus de l'utilisation normale de l'instruction SELECT, MySQL nous fournit également de nombreuses options pouvant améliorer les performances des requêtes. Comme mentionné ci-dessus, SQL_NO_CACHE et SQL_CACHE, qui sont utilisés pour contrôler la mise en mémoire tampon des requêtes, sont deux des options. Dans cette section, je présenterai quelques options de requête couramment utilisées.
1. STRAIGHT_JOIN : forcer l'ordre de connexion
Lorsque nous connectons deux tables ou plus pour une requête, nous n'avons pas besoin de nous soucier de quelle table MySQL se connecte en premier et de quelle table elle se connecte en dernier. Tout cela est déterminé par une séquence de connexion déterminée par MySQL à travers une série de calculs et d'évaluations internes. Dans les instructions SQL suivantes, TABLE1 et TABLE2 ne sont pas nécessairement connectés l'un à l'autre : SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE …
Si les développeurs doivent intervenir manuellement dans l'ordre des connexions, ils doivent utiliser le mot-clé STRAIGHT_JOIN, comme l'instruction SQL suivante : SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE…
Comme le montre l'instruction SQL ci-dessus, STRAIGHT_JOIN est utilisé pour forcer MySQL à joindre des tables dans l'ordre TABLE1 et TABLE2. Si vous pensez qu'il est plus efficace de rejoindre votre propre ordre que l'ordre recommandé par MySQL, vous pouvez utiliser STRAIGHT_JOIN pour déterminer l'ordre de connexion.
2. Intervenir dans l'utilisation des index et améliorer les performances
L'utilisation d'index a été mentionnée ci-dessus. Dans des circonstances normales, MySQL décidera d'utiliser ou non un index et quel index utiliser lors de l'interrogation. Mais dans certains cas particuliers, nous souhaitons que MySQL n'utilise qu'un ou quelques index, ou nous ne souhaitons pas utiliser un certain index. Cela nécessite l'utilisation de certaines options de requête de MySQL pour contrôler l'index.
Limiter la portée de l'utilisation des index
Parfois, nous créons plusieurs index dans la table de données. Lorsque MySQL sélectionne l'index, ces index sont tous pris en compte. Mais parfois, nous souhaitons que MySQL ne considère que quelques index au lieu de tous les index. Cela nécessite l'utilisation de USE INDEX pour définir l'instruction de requête. SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …
Comme le montre l'instruction SQL ci-dessus, quel que soit le nombre d'index établis dans TABLE1, MySQL ne prend en compte que les index établis sur FIELD1 et FIELD2 lors de la sélection des index.
Limiter la plage d'index qui ne sont pas utilisés
Si nous avons de nombreux index à considérer et peu d’index inutilisés, nous pouvons utiliser IGNORE INDEX pour la sélection inversée. Dans l'exemple ci-dessus, l'index considéré est sélectionné, tandis que l'utilisation de IGNORE INDEX consiste à sélectionner l'index qui n'est pas pris en compte. SELECT * FROM TABLE1 IGNORER L'INDEX (FIELD1, FIELD2) …
Dans l'instruction SQL ci-dessus, seuls les index sur FIELD1 et FIELD2 dans la table TABLE1 ne sont pas utilisés.
Forcer l'utilisation d'un index
Les deux exemples ci-dessus offrent tous deux un choix à MySQL, ce qui signifie que MySQL n'est pas obligé d'utiliser ces index. Parfois, nous espérons que MySQL devra utiliser un certain index (puisque MySQL ne peut utiliser qu'un seul index lors d'une requête, il ne peut forcer MySQL à utiliser qu'un seul index). Cela nécessite l'utilisation de FORCE INDEX pour compléter cette fonction. SÉLECTIONNER * DANS LE TABLEAU 1 INDICE DE FORCE (CHAMP 1) …
L'instruction SQL ci-dessus utilise uniquement l'index construit sur FIELD1, pas les index sur d'autres champs.
3. Utilisez des tables temporaires pour améliorer les performances des requêtes
Lorsqu'il y a beaucoup de données dans l'ensemble de résultats de notre requête, nous pouvons forcer l'ensemble de résultats dans une table temporaire via l'option SQL_BUFFER_RESULT, afin que le verrou de la table MySQL puisse être rapidement libéré (afin que d'autres instructions SQL puissent les interroger). records) ) et peut servir de grands ensembles d’enregistrements aux clients pendant de longues périodes. SELECT SQL_BUFFER_RESULT * FROM TABLE1 OÙ …
Semblable à l'option SQL_BUFFER_RESULT., il existe SQL_BIG_RESULT. Cette option est généralement utilisée pour le regroupement ou les mots-clés DISTINCT. Cette option informe MySQL que, si nécessaire, les résultats de la requête seront placés dans une table temporaire, voire triés dans la table temporaire. SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1
6.Conclusion
Il existe également un « principe 20/80 » en programmation, c'est-à-dire que 20 % du code prend 80 % du temps. Il en va de même pour le développement d’applications de bases de données. L'optimisation des applications de bases de données se concentre sur l'efficacité d'exécution de SQL. L'objectif de l'optimisation des requêtes de données est de faire en sorte que le serveur de base de données lise moins de données sur le disque et lise les pages de manière séquentielle plutôt que non séquentielle.