SQL Explain

C’est quoi ?

Published in

BPAM Tech Blog

3 min readMar 29, 2017

Quand une base de données reçoit une requête SQL, plusieurs étapes sont nécessaires pour envoyer le résultat correspondant (parsing, optimization, évaluation, …).

Comme il y a souvent plusieurs moyens pour arriver au résultat voulu et que la performance est un critère crucial, un plan d’exécution est d’abord calculé afin de trouver la meilleure stratégie pour exécuter la requête.

Ce plan d’exécution :

est propre à chaque requête
est mis en cache par la base de données (car le calcul de ce plan a bien sûr un coût)
est consultable grâce à l’instruction explain

Les informations importantes remontées sont :

le coût des différentes étapes de la requête
l’usage des index
la manière d’effectuer les jointures

Comment l’utiliser ?

Chaque base de données a ses spécificités mais la syntaxe est généralement :

explain SQL_REQUEST

Exemple avec MySQL

mysql> explain SELECT customer.* FROM customer
    -> INNER JOIN "order" ON customer.id = "order".customer_id
    ->                   AND "order".delivered IS TRUE;
+----+-------------+----------+--------+---------+------+----------+
| id | select_type | table    | type   | key     | rows | filtered |
+----+-------------+----------+--------+---------+------+----------+
|  1 | SIMPLE      | order    | ALL    | NULL    | 6775 |    10.00 |
|  1 | SIMPLE      | customer | eq_ref | PRIMARY |  127 |   100.00 |
+----+-------------+----------+--------+---------+------+----------+
2 rows in set, 1 warning (0.00 sec)

Les explications des colonnes se trouvent ici

Ce qu’on peut voir :

MySQL va lire toute la table order (type = ALL) malgré le filtre sur delivered => il doit manquer un index !
MySQL va ensuite lire la table customer en utilisant la clé primaire pour filtrer sur les customer_id obtenus précédemment

La colonne rows donne une estimation du nombre de ligne à lire et le colonne filtered le pourcentage de lignes qui seront gardées après l’application des filtres.

Encore plus de détails avec MySQL

Pour avoir le coût avec MySQL, il faut utiliser le format JSON :

mysql> explain format = json SELECT customer.* FROM customer
    -> INNER JOIN "order" ON customer.id = "order".customer_id
    ->                   AND "order".delivered IS TRUE;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19496.69"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "order",
          "cost_info": {
            "read_cost": "1316.50",
            "eval_cost": "135.50",
            "prefix_cost": "1452.00",
            "data_read_per_join": "2M"
          }
        }
      },
      {
        "table": {
          "table_name": "customer",
          "cost_info": {
            "read_cost": "820.66",
            "eval_cost": "17224.03",
            "prefix_cost": "19496.69",
            "data_read_per_join": "150M"
          }
        }
      }
    ]
  }
}

Le but est généralement de diminuer le coût en ajoutant des index ou en écrivant la requête différemment :

mysql> create index idx_order_delivered on "order" (delivered);
Query OK, 0 rows affected (0.32 sec)
mysql> explain format = json SELECT customer.* FROM customer
    -> INNER JOIN "order" ON customer.id = "order".customer_id
    ->                   AND "order".delivered IS TRUE;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "250.51"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "order",
          "access_type": "ref",
          "key": "idx_order_delivered",
          "used_key_parts": [
            "delivered"
          ],
          "key_length": "363",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 9,
          "rows_produced_per_join": 9,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "9.00",
            "eval_cost": "1.80",
            "prefix_cost": "10.80",
            "data_read_per_join": "32K"
          }
        }
      },
      {
        "table": {
          "table_name": "customer",
          "cost_info": {
            "read_cost": "10.90",
            "eval_cost": "228.81",
            "prefix_cost": "250.51",
            "data_read_per_join": "1M"
          }
        }
      }
    ]
  }
}

On voit ci-dessus que les coûts ont fortement diminué car, en utilisant l’index créé, on ne lit plus que 9 lignes de la table order !

Attention cependant car si un index permet de réduire le temps d’un SELECT, il ralentit les INSERT et les UPDATE !

Conseils

Il faut essayer d’éviter les full scan (i.e. le parcours de toute une table), aussi libellé type = ALL en MySQL, surtout sur les tables à forte volumétrie !
Vérifier que les clés étrangères ont des index (ce qui est fait automatiquement en MySQL)
Se méfier des nested loops