Utilisation d'expliquer pour écrire de meilleures requêtes mysql

Optimisation de la requête MySQL avec Explication: une plongée profonde

Lorsque vous exécutez une requête MySQL, l'optimiseur de requête élabore un plan d'exécution. Pour inspecter ce plan, utilisez la commande EXPLAIN. EXPLAIN est inestimable pour comprendre et optimiser les requêtes lentes, mais de nombreux développeurs le sous-utilisent. Cet article explore la sortie de EXPLAIN et son application dans le schéma et l'optimisation des requêtes.

Prise des clés:

• Levier EXPLAIN pour analyser les plans d'exécution de la requête, identifier les inefficacités et améliorer les performances.
• Les colonnes de sortie de Depiffher EXPLAIN (par exemple, type, possible_keys, key, rows, Extra) pour comprendre le traitement de la requête et identifier les zones d'amélioration.
• Ajouter stratégiquement les index aux tableaux en fonction des colonnes dans les clauses JOIN ou WHERE pour réduire considérablement les scans de ligne, augmenter la vitesse et minimiser les temps de chargement.
• Utiliser EXPLAIN EXTENDED et SHOW WARNINGS pour des informations détaillées sur les transformations et l'exécution des requêtes, en particulier pour les tâches d'optimisation complexes.
• Examiner et optimiser régulièrement les requêtes SQL en utilisant EXPLAIN pour maintenir les performances optimales de la base de données, en particulier dans les applications dynamiques avec des données en évolution.

Compréhension de la sortie EXPLAIN Sortie

Préfilez simplement votre requête SELECT avec EXPLAIN. Analysons un exemple de base:

EXPLAIN SELECT * FROM categoriesG;

Un exemple de sortie peut ressembler à ceci:

<code>********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)</code>

Cette sortie apparemment concise est riche en informations. Les colonnes clés sont:

• id: identifiant séquentiel pour chaque SELECT dans la requête (pertinente pour les sous-requêtes imbriquées).
• select_type: type de SELECT requête (simple, primaire, dérivé, sous-requête, etc.). SIMPLE indique une requête simple sans sous-questionnaires ou UNION s.
• table: Tableau référencé par la ligne.
• type: Comment MySQL rejoint les tables. Crucial pour identifier les index ou les zones manquantes de réécriture de requête. Les valeurs vont de très efficaces (system, const, eq_ref) à inefficace (ALL, indiquant une analyse de table complète).
• possible_keys: Clés potentiellement utilisables par MySQL. NULL ne suggère aucun index pertinent.
• key: Index réel utilisé. Peut différer de possible_keys en raison des choix d'optimiseur.
• key_len: longueur de l'index choisi.
• ref: colonnes ou constantes par rapport à l'index dans la colonne key.
• rows: Nombre de lignes examinées. Une valeur élevée indique les besoins d'optimisation potentiels, en particulier avec les JOIN s et les sous-requêtes.
• Extra: Informations supplémentaires (par exemple, "Utilisation du temporaire" "" Utilisation de fichiers de fichiers "). Consultez la documentation MySQL pour des interprétations détaillées.

EXPLAIN EXTENDED fournit plus de détails. Utilisez SHOW WARNINGS après pour afficher les transformations de requête effectuées par l'optimiseur:

EXPLAIN SELECT * FROM categoriesG;

Dépannage des performances avec EXPLAIN

Illustrons l'optimisation d'une requête mal performante. Considérez une base de données de commerce électronique (schéma disponible sur github) manquant d'index. Une requête mal écrite peut ressembler à ceci:

<code>********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)</code>

La sortie EXPLAIN révélera probablement des types de types "tous", NULL pour les valeurs possible_keys et key, et extrêmement élevées rows, indiquant une analyse de table complète pour chaque tableau. Ceci est extrêmement inefficace.

L'ajout de clés et d'index primaires (par exemple, sur les colonnes utilisées dans les clauses JOIN) améliore considérablement les performances. Le relâchement du EXPLAIN après l'ajout d'index affichera des valeurs rows beaucoup plus faibles et des types de jointures plus efficaces ("const," "eq_ref").

Un autre exemple implique un UNION de deux tables, chacune jointe à productlines:

EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia';
SHOW WARNINGS;

sans index appropriés, EXPLAIN affichera des scans de table complets. L'ajout d'index et les conditions WHERE stratégiquement dans les sous-requêtes UNION peut réduire considérablement le nombre de lignes scannées.

Résumé

EXPLAIN est votre allié dans l'optimisation des requêtes MySQL. En analysant sa sortie, vous pouvez identifier et traiter les goulots d'étranglement des performances, conduisant à des requêtes plus efficaces et plus rapides. N'oubliez pas que l'ajout d'index n'est pas toujours suffisant; La structure des requêtes joue également un rôle vital. L'utilisation régulière de EXPLAIN est la clé pour maintenir la santé de la base de données, en particulier dans les applications dynamiques.

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!