Introduction détaillée à l'optimisation des requêtes MySQL

1. Introduction

La chose la plus importante pour une bonne application Web est d'avoir d'excellentes performances d'accès. La base de données MySQL fait partie intégrante de l'application Web et constitue un élément important qui détermine ses performances. Il est donc crucial d’améliorer les performances de MySQL.

L'amélioration des performances de MySQL peut être divisée en trois parties, à savoir le matériel, le réseau et les logiciels. Parmi eux, le matériel et le réseau dépendent des ressources financières de l'entreprise et nécessitent beaucoup d'argent, je ne les aborderai donc pas ici. Le logiciel est subdivisé en plusieurs types. Ici, nous obtenons une amélioration des performances grâce à l'optimisation des requêtes MySQL.

Récemment, j'ai lu des livres sur l'optimisation des requêtes, ainsi que des articles écrits par des seniors en ligne.

Voici quelques résumés sur l'optimisation des requêtes que j'ai compilés et dont j'ai appris :

2. Intercepter les instructions SQL

1. Journal complet des requêtes

2. . Journal des requêtes lentes

3. Journal binaire

4. Liste des processus

AFFICHER LA LISTE COMPLÈTE DES PROCESSUS ;

. . .

3. Commandes d'analyse de base pour l'optimisation des requêtes

1. EXPLAIN {PARTITIONS|EXTENDED}

2. AFFICHER l'onglet CRÉER TABLE ;

3. AFFICHER INDEX DE l'onglet ;

　4. AFFICHER LE STATUT DE LA TABLE COMME 'onglet';

　5. AFFICHER LE STATUT [GLOBAL|SESSION] COMME '';

　6. AFFICHER LES VARIABLES

​. . . .

ps : J'ai personnellement l'impression qu'ils sont tous nutritionnellement dépourvus de tout nutriment. Voici les vraies choses.

4. Plusieurs directions pour l'optimisation des requêtes

1. Essayez d'éviter l'analyse de texte intégral, ajoutez des index aux champs correspondants et utilisez des index pour interroger

2. Supprimez les index inutilisés. ou des index en double

3. Réécriture de requêtes, conversion équivalente (prédicat, sous-requête, requête de jointure)

4. Supprimer les instructions inutiles qui répètent le contenu et rationaliser les instructions

5. Intégrer déclarations exécutées à plusieurs reprises

6. Mettre en cache les résultats des requêtes

5. Optimisation de l'index

5.1 Avantages de l'index :

1. Maintenir l'intégrité des données

　 2. Améliorer les performances des requêtes de données

3. Améliorer les opérations de connexion aux tables (jion)

4. Trier les résultats des requêtes. S'il n'y a pas d'index, l'algorithme de tri des fichiers interne sera utilisé pour le tri, ce qui est plus efficace

5. Simplifier l'opération d'agrégation des données

5.2 Inconvénients de l'index

. 1. L'index doit occuper une certaine quantité d'espace Espace de stockage

2. L'insertion, la mise à jour et la suppression de données seront affectées par l'index et les performances seront réduites. Parce que les données changent, l'index doit également être mis à jour

3. Plusieurs index, si l'optimiseur prend du temps, le meilleur choix est

5.3 Sélection de l'index

1. . Lorsque la quantité de données est importante, utilisez

2. Lorsque les données sont très répétitives, n'utilisez pas

3. Si la requête récupère plus de 20 % des données, texte intégral. le scanning sera utilisé sans indexation

5.4. Etude détaillée de l'index

Requête de données :

InnoDB et MyISAM dans MySQL sont des index de type B-Tree

B-Tree comprend : PRIMARY KEY, UNIQUE, INDEX et FULLTEXT

L'index de type B-Tree n'est pas pris en charge (c'est-à-dire que lorsque le champ utilise les symboles suivants, l'index ne sera pas utilisé) :

　>, <, >=, <=, BETWEEN, !=, < >,like '%**'

　【Permettez-moi d'abord de vous présenter l'index de couverture】

　Permettez-moi de le présenter d'une manière que je comprends. Les index de couverture n'existent pas vraiment comme les index de clé primaire et les index uniques. Il s'agit simplement d'une définition de certains scénarios spécifiques d'application d'index [une autre compréhension : la colonne interrogée est une colonne d'index, donc la colonne est couverte par l'index]. Il peut dépasser les limitations traditionnelles, utiliser les opérateurs ci-dessus et continuer à utiliser des index pour les requêtes.

Étant donné que la colonne de requête est une colonne d'index, il n'est pas nécessaire de lire la ligne, seules les données du champ de la colonne doivent être lues. [Par exemple, si vous lisez un livre et avez besoin de trouver un certain contenu, et que ce contenu apparaît dans la table des matières, alors vous n'avez pas besoin de tourner page par page, localisez simplement la page dans la table des matières. contenu et recherche]

Comment activer Qu'en est-il de la couverture des index ? Qu'est-ce qu'un scénario spécifique ?

Le champ d'index apparaît simplement dans la sélection.

L'indice composé peut également avoir d'autres scénarios spéciaux. Par exemple, pour un index composite à trois colonnes, il suffit que la colonne la plus à gauche de l'index composite apparaisse une fois dans select,where, group by et order by pour activer l'utilisation de l'index de couverture.

Vue :

Extra dans EXPLAIN affiche Using index, indiquant que cette instruction utilise un index de couverture.

Conclusion :

Il n'est pas recommandé d'utiliser select*from lors de l'interrogation. Vous devez écrire les champs requis et ajouter les index correspondants pour améliorer les performances des requêtes.

Résultats de mesure réels pour les opérateurs ci-dessus :

1. Sous la forme de select*from, la clé primaire dans où peut être utilisée pour tuer [sauf comme] (utilisez la clé primaire pour requête); l'index ne fonctionne pas du tout Can.

2. Test sous forme de champ de sélection a partir de l'onglet où champ un "opérateur ci-dessus", le résultat peut toujours être interrogé à l'aide de l'index. [Utilisation de l'index de couverture]

　 Autres méthodes d'optimisation d'index :

1. Utiliser des mots-clés d'index comme conditions de connexion

2. Utiliser des index composites

3. La fusion d'index ou et, impliquera Les champs impliqués sont fusionnés dans un index composite

4. Ajouter un index aux champs impliqués dans Where, et regrouper par

6. Optimisation des sous-requêtes

Dans from, c'est une sous-requête non corrélée peut être extraite vers la couche parent. Dans les requêtes de jointure multi-tables, tenez compte du coût de jointure avant de sélectionner.

L'optimiseur de requêtes utilise généralement une exécution imbriquée pour les sous-requêtes, c'est-à-dire qu'elle exécute la sous-requête une fois pour chaque ligne de la requête parent, de sorte que la sous-requête soit exécutée plusieurs fois. Cette méthode d'exécution est très inefficace.

Avantages de la conversion des sous-requêtes en requêtes de jointure :

1. La sous-requête n'a pas besoin d'être exécutée plusieurs fois

2. L'optimiseur peut choisir différentes méthodes et ordres de connexion en fonction sur les informations

3. Les conditions de connexion et les conditions de filtrage de la sous-requête deviennent les conditions de filtrage de la requête parent pour améliorer l'efficacité.

Optimisation :

Fusion de sous-requêtes S'il existe plusieurs sous-requêtes, essayez de les fusionner autant que possible.

Expansion de sous-requête, c'est-à-dire que le pull-up devient une requête multi-tables (les modifications équivalentes sont garanties à tout moment)

Remarque :

L'expansion de sous-requête ne peut s'étendre que de manière simple requêtes. Si la sous-requête Si la requête contient des fonctions d'agrégation, GROUP BY et DISTINCT, elle ne peut pas être extraite.

sélectionnez * dans t1 (sélectionnez*dans l'onglet où id>10) comme t2 où t1.age>10 et t2.age<25;

sélectionnez*dans t1,onglet comme t2 où t1.age>10 et t2.age<25 et t2.id>10;

Étapes spécifiques :

1. Fusionner de et depuis, modifier les paramètres correspondants

2 , fusionner où avec où, utiliser et pour connecter

3. Modifier le prédicat correspondant (in est changé en =)

7. Réécrire le prédicat équivalent :

1 . ENTRE ET Réécrivez-le comme >=, <= et ainsi de suite. Mesure réelle : 100 000 données, temps avant et après réécriture, 1,45 s, 0,06 s

　2. En convertit plusieurs ou. Lorsque le champ est un index, les deux peuvent utiliser l'index, ou est plus efficace que dans

3. Le nom comme 'abc%' est réécrit comme nom>='abc' et nom<'abd';

Remarque : dans le test de données au niveau d'un million, like est plus rapide que cette dernière requête avant que le nom ne soit indexé après avoir ajouté un index au champ, cette dernière requête est un peu plus rapide, mais il n'y a pas beaucoup de différence, car les deux méthodes sont utilisées lors de l'interrogation de l'index.

. . . .

8. Simplification et optimisation conditionnelles

1. Combinez où, avoir (quand il n'y a pas de fonctions groupby et d'agrégation) et conditions de jointure autant que possible

2. Supprimez les parenthèses inutiles, réduisez les couches de syntaxe ou et et et réduisez la consommation du processeur

3. Transfert constant. a=b et b=2 sont convertis en a=2 et b=2. Essayez de ne pas utiliser les variables a=b ou a=@var

4. Éliminez les conditions SQL inutiles

5. Essayez de ne pas calculer d'expressions à droite du signe où égal ; utilisez des champs dans lesquels Calculer des expressions et utiliser des fonctions

6. Transformation d'identité et transformation des inégalités. Exemple : tester des millions de données a>b et b>10 devient a>b et a>10 et b>10 avec une optimisation significative

9. Optimisation des connexions externes

C'est-à-dire convertir des données externes connexions Pour les jointures internes

Avantages :

1. Le processeur d'optimisation gère les jointures externes avec plus d'étapes que les jointures internes et prend du temps

2. Une fois les jointures externes éliminé, l'optimiseur sélectionne plusieurs tables Il y a plus de choix pour la séquence de connexion, vous pouvez choisir la meilleure

3. Vous pouvez utiliser la table avec les conditions de filtrage les plus strictes comme apparence (le recto de la séquence de connexion est la couche de boucle externe du corps de boucle multicouche),

Cela peut réduire les frais d'E/S inutiles et accélérer l'exécution de l'algorithme.

　La différence entre on a.id=b.id et où a.id=b.id, on signifie que la table sera connectée, et où fera la comparaison des données

Remarque : Le principe doit être que le résultat est une abstinence NULL (c'est-à-dire que la condition n'est limitée à aucune ligne de données NULL, sémantiquement il s'agit d'une jointure interne)

Principes d'optimisation :

Rationaliser la requête, la connexion élimination, conversion équivalente, suppression des connexions d'objets de table redondantes

Par exemple : la clé primaire/clé unique est utilisée comme condition de connexion, et la colonne de table intermédiaire n'est utilisée que comme condition équivalente, la connexion de table intermédiaire peut être supprimé

10. Autre optimisation des requêtes

1. Ce qui suit sera Provoque l'abandon de la requête d'index et l'analyse du texte intégral

1.1. > dans la clause Where Remarque : prise en charge de la clé primaire. Les clés non primaires ne prennent pas en charge

1.2 Évitez d'utiliser ou d'utiliser, la situation spécifique doit donc être analysée au cas par cas.

　 Optimisation similaire :

　　sélectionnez * dans l'onglet name='aa' ou name='bb';

　　=>

　　sélectionnez * dans l'onglet name='aa'

　　union all

　　select * from tab name='bb';

　 Mesure réelle :

　1. Cent mille données test, Sans aucun index, la requête ci-dessus est deux fois plus rapide que la requête ci-dessous.

2. Dans le test de 300 000 données, lorsque aa et bb sont indexés séparément, la vitesse de requête suivante est un peu plus rapide que ou.

　1.3. Évitez d'utiliser not in

　not in ne peut généralement pas utiliser d'index ; le champ de clé primaire peut être

　1.4. 🎜>　1.5. like ne peut pas être précédé d'un signe de pourcentage comme '%.com'

Solution :

1. Si vous devez utiliser % devant et que la longueur des données n'est pas grande, comme URL, vous pouvez retourner les données et les enregistrer. Entrez dans la base de données et vérifiez à nouveau. LIKE REVERSE'%.com';

2. Utiliser l'index de couverture

1.6 Lors de l'utilisation de champs d'index comme conditions, s'il s'agit d'un index composite, le nom du champ avec le préfixe le plus à gauche du. l'index doit être utilisé

2. Remplacer existe dans in

sélectionnez num à partir de a où num in (sélectionnez num à partir de b)

sélectionnez num à partir de a où existe (sélectionnez 1 à partir de b où num =a.num)

Avec un million de données, il faut 6,65 s et 4,18 s pour filtrer 59 417 données. Aucune autre optimisation n'a été effectuée, il suffit de remplacer exist par in.

3. La définition du champ est une chaîne. Il n'y a pas de guillemets lors de l'interrogation et aucune analyse de texte intégral ne sera effectuée.

[Ce qui suit est un extrait du billet de blog de Luantanqin http://www.cnblogs.com/lingiu/p/3414134.html Je n'ai pas effectué le test correspondant]

4. Essayez de l'utiliser autant que possible. Les variables de table remplacent les tables temporaires

5. Évitez de créer et de supprimer fréquemment des tables temporaires pour réduire la consommation des ressources des tables système

6. Si une table temporaire est utilisée , assurez-vous de l'ajouter à la fin de la procédure stockée. Supprimez explicitement toutes les tables temporaires, tronquez d'abord la table, puis supprimez la table. Cela peut éviter un verrouillage de longue durée des tables système

7. Essayez d'éviter d'utiliser. curseurs, car les curseurs sont moins efficaces. Si l'opération du curseur est Si les données dépassent 10 000 lignes, vous devriez alors envisager de réécrire

8. Volume de données important Si le volume de données est trop important, vous devez vous demander si le volume de données est trop important. les exigences correspondantes sont raisonnables.

9. 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!