Compétences d'optimisation SQL que les programmeurs ordinaires doivent maîtriser

Que ce soit au travail ou lors d'un entretien, vous devez essentiellement maîtriser certaines compétences d'optimisation SQL, comme utiliser expliquer pour visualiser le plan d'exécution de SQL, puis optimiser le SQL en fonction du plan d'exécution.

L'utilisation d'explication et l'analyse des champs associés sont désormais fondamentalement standard pour les programmeurs.

Non, veuillez le lire attentivement.

1. Qu'est-ce qu'un plan d'exécution MySQL ? Pour avoir une meilleure compréhension du plan d'exécution, vous devez avoir une compréhension simple de la structure de base de MySQL et des principes de base des requêtes.

L'architecture fonctionnelle de MySQL elle-même est divisée en trois parties, à savoir la couche application, la couche logique et la couche physique. Non seulement MySQL, mais la plupart des autres produits de base de données sont divisés selon cette architecture.

La couche application est principalement responsable de l'interaction avec le client, de l'établissement des liens, de la mémorisation de l'état des liens, du renvoi des données et de la réponse aux demandes. Cette couche s'occupe du client.
La couche logique est principalement responsable du traitement des requêtes, de la gestion des transactions et du traitement d'autres fonctions de base de données, en prenant les requêtes comme exemple.
Après avoir reçu pour la première fois la requête SQL, la base de données allouera immédiatement un thread pour la traiter. Dans un premier temps, le processeur de requêtes optimisera la requête SQL. Après optimisation, un plan d'exécution sera généré puis transmis à. l'exécuteur du plan pour l'exécution.

L'exécuteur du plan doit accéder au gestionnaire de transactions de niveau inférieur et au gestionnaire de stockage pour exploiter les données. Leurs divisions de travail respectives sont différentes. Enfin, les informations sur la structure de la requête sont obtenues en appelant le fichier de la couche physique, et le résultat final. est répondu à la couche application.

Couche physique, les fichiers stockés sur le disque physique réel comprennent principalement des fichiers de données penny et des fichiers journaux.
Grâce à la description ci-dessus, la génération d'un plan d'exécution est une étape essentielle pour exécuter un SQL. Les performances d'un SQL peuvent être vues intuitivement en visualisant le plan d'exécution fournit différents types et niveaux de requêtes. vérifiez-le et utilisez-le comme base pour l’analyse des performances.

2. Comment analyser le plan d'exécutionMySQL nous fournit le mot-clé expliquer pour visualiser visuellement le plan d'exécution d'un SQL.

explain montre comment MySQL utilise les index pour traiter les instructions de sélection et les tables de jointure, ce qui peut aider à choisir de meilleurs index et à écrire des instructions de requête plus optimisées.

Ci-dessous, nous utilisons expliquer pour créer une requête, comme suit :

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Il y a 12 colonnes dans la structure de la requête. Comprendre la signification de chaque colonne est crucial pour comprendre le plan d'exécution. Ce qui suit est expliqué sous la forme d'un tableau. .

Nom de la colonne	Description
id	Identifiant SELECT, qui est le numéro de séquence de requête de SELECT.
select_type	SELECT type, qui peut être l'un des suivants : SIMPLE : simple SELECT (n'utilise pas UNION ni de sous-requête) PRIMARY : SELECT le plus externe UNION : deuxième dans UNION ou ce qui suit Instruction SELECTDEPENDENT UNION : La deuxième instruction SELECT ou suivante dans UNION, en fonction de la requête externeUNION RESULT : Le résultat de UNIONSUBQUERY : Le premier SELECTDEPENDENT dans la sous-requête SUBQUERY : Le premier SELECT dans la sous-requête, dépend de la requête externeDERIVED : SELECT de la table dérivée (sous-requête de la clause FROM)
table	La table référencée par la ligne de sortie
partitions	Affiche la partitions auxquelles la requête accédera si la requête est basée sur une table partitionnée.
type	Rejoignez le type. Les différents types de jointure sont donnés ci-dessous, classés du meilleur au pire : system : La table n'a qu'une seule ligne (=table système). Il s’agit d’un cas particulier du type de jointure const. const : La table comporte au plus une ligne correspondante, qui sera lue au début de la requête. Puisqu'il n'y a qu'une seule ligne, les valeurs des colonnes de cette ligne peuvent être traitées comme des constantes par le reste de l'optimiseur. Les tables const sont rapides car elles ne sont lues qu'une seule fois ! eq_ref : Pour chaque combinaison de lignes du tableau précédent, lisez une ligne de ce tableau. C'est probablement le meilleur type de jointure, outre les types const. ref : Pour chaque combinaison de lignes du tableau précédent, toutes les lignes avec des valeurs d'index correspondantes seront lues à partir de ce tableau. ref_or_null : Ce type de jointure est comme ref, mais ajoute MySQL pour rechercher spécifiquement les lignes contenant des valeurs NULL. index_merge : Ce type de jointure indique que la méthode d'optimisation de fusion d'index est utilisée. unique_subquery : Ce type remplace la référence de la sous-requête IN sous la forme suivante : value IN (SELECT Primary_key FROM single_table WHERE some_expr) unique_subquery est une fonction de recherche d'index qui peut remplacer complètement la sous-requête et est plus efficace. index_subquery : ce type de jointure est similaire à unique_subquery. Les sous-requêtes IN peuvent être remplacées, mais uniquement pour les index non uniques dans les sous-requêtes de la forme suivante : value IN (SELECT key_column FROM single_table WHERE some_expr) range : Récupère uniquement les lignes d'une plage donnée, en utilisant un index pour sélectionner les lignes ( Recommandation, le pire c'est ce niveau). index : Ce type de jointure est le même que ALL, sauf que seule l'arborescence d'index est analysée. C'est généralement plus rapide que ALL car les fichiers d'index sont généralement plus petits que les fichiers de données. TOUS : effectuez une analyse complète du tableau pour chaque combinaison de lignes du tableau précédent, indiquant que la requête doit être optimisée. D'une manière générale, il faut s'assurer que la requête atteint au moins le niveau range, et de préférence atteint la ref. Le système ci-dessus est le meilleur, par ordre décroissant, ALL est le pire
possible_keys	indique quel index MySQL peut utiliser pour trouver des lignes dans la table
key	montre la clé qui MySQL décide en fait d'utiliser ( index). Si aucun index n'est sélectionné, la clé est NULL.
key_len	Affiche la longueur de clé que MySQL décide d'utiliser. Si la clé est NULL, la longueur est NULL. Plus la longueur est courte, mieux c'est sans perdre en précision
ref	Affiche quelle colonne ou constante est utilisée ainsi que la clé pour sélectionner les lignes du tableau.
rows	Affiche le nombre de lignes que MySQL pense devoir vérifier lors de l'exécution de la requête. La multiplication des données sur plusieurs lignes fournit une estimation du nombre de lignes à traiter.
filtered	affiche l'estimation en pourcentage du nombre de lignes filtrées par la condition.
Extra	Cette colonne contient les détails de la façon dont MySQL a résolu la requête Distinct : ​​Une fois que MySQL a trouvé la 1ère ligne correspondante, il arrête de rechercher d'autres lignes pour la combinaison de lignes actuelle. Sélectionnez les tables optimiséesMySQL renvoie les données sans parcourir la table ou l'index du tout, indiquant qu'elles ont été optimisées au point qu'elles ne peuvent plus être optimisées N'existe pas : MySQL peut optimiser la requête avec LEFT JOIN et trouvé 1 correspondant à la norme LEFT JOIN Après une ligne, plus aucune ligne n'est vérifiée dans le tableau pour la combinaison de lignes précédente. plage vérifiée pour chaque enregistrement (carte d'index : #) : MySQL n'a pas trouvé un bon index pouvant être utilisé, mais a constaté que si les valeurs de colonne de la table précédente sont connues, certains index peuvent être utilisés. Utilisation du tri de fichiers : MySQL nécessite une passe supplémentaire pour comprendre comment récupérer les lignes dans l'ordre trié, indiquant que la requête doit être optimisée. Utilisation de l'index : récupérez les informations sur les colonnes d'une table en lisant les lignes réelles en utilisant uniquement les informations de l'arborescence d'index sans autre recherche. Utilisation de temporaire : afin de résoudre la requête, MySQL doit créer une table temporaire pour accueillir les résultats, ce qui signifie que la requête doit être optimisée. Utilisation de la clause Where:WHERE est utilisée pour limiter quelle ligne correspond à la table suivante ou est envoyée au client. Using sort_union(...), Using union(...), Using intersect(...) : Ces fonctions illustrent comment fusionner des analyses d'index pour le type de jointure index_merge. Utilisation d'un index pour group-by : similaire à la méthode Using index pour accéder à une table, Using index for group-by signifie que MySQL a trouvé un index qui peut être utilisé pour interroger toutes les colonnes des requêtes GROUP BY ou DISTINCT sans frais supplémentaires. recherche du disque dur. Accédez à la table actuelle.

Selon le tableau ci-dessus, il peut fournir une bonne aide dans l'analyse du plan d'exécution.

Remarque : Si c'est pour faire face à l'entretien, il est préférable de le mémoriser. Si vous ne pouvez pas tout mémoriser, vous devriez également être capable de dire 123, puis de dire que vous ne vous en souvenez pas beaucoup. Vous pouvez lire les documents pertinents pour comparer.

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!