Le but de cet article est d’optimiser une simple requête (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l’optimiseur procède, en étudiant les résultats donnés par les variables qui permettent de surveiller le serveur MySQL.
Le schéma utilisé est le schéma world téléchargeable ici
Voici la structure de la table city:
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE city (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL DEFAULT »,
CountryCode char(3) NOT NULL DEFAULT »,
District char(20) NOT NULL DEFAULT »,
Population int(11) NOT NULL DEFAULT ’0′,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
La commande EXPLAIN, permet d’avoir le plan d’exécution:
EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary; Using filesort
L’optimiseur fait un full table scan (type: ALL) ce qui n’est pas forcement une bonne nouvelle. De plus aucun index n’est utilisé (key: NULL), ce qui est logique car la table n’en contient pas (sic !)
Using temporary; Using filesort indiquent la création d’un table temporaire et le tri des données (pas très bon pour les performances surtout si la table temporaire est créée sur le disque)
La variable Last_query_cost permet de récupérer le coût de la requête:
*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924
Ajoutons un index sur la colonne countrycode:
L’index Idx_cc ne sert à rien
Ajoutons alors un index sur la colonne population
Pas mieux ! L’index Idx_population ne sert à rien.
Effacement des 2 index:
Ajoutons un index composite sur les colonnes population, countrycode:
Çaparait un peu meilleur, l’index Idx_population_cc est utilisé.
Extra: using index nous indique que MySQL utilise un index couvrant (covering index), c’est à dire que l’information est entièrement accessible en parcourant l’index (pas d’accès aux données).
De plus l’optimiseur fait un full index scan: type: index
Voyons le coût de la requête:
Variable_name: Last_query_cost
Value: 4963.520924
La valeur de (l’obscure) Last_query_cost est cependant le même que pour les requêtes précédentes…
Ajoutons un index composite sur les colonnes countrycode,population
Variable_name: Last_query_cost
Value: 4963.520924
L’optimiseur estime que le coût de la requête est toujours le même, cependant l’index Idx_cc_population(CountryCode,Population) optimise les performance de notre requête car il n’y a plus de Using temporary ni de Using filesort.
Il suffit de pousser encore un peu plus loin notre analyse pour en être définitivement (?) convaincu…
Initialisation des variables de sessions
En utilisant l’index idx_population_cc (le mauvais index)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 4079 |
| Handler_read_next | 4079 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 232 |
| Handler_read_rnd_next | 233 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 3847 |
| Handler_write | 232 |
+----------------------------+-------+
Les handler sont des indicateurs liés au moteur de stockage.
Handler_read_first comptabilise le nombre de fois que la première valeur de l’index est lue.
Handler_read_key indique le nombre d’enregistrement récupéré graçe à l’index.
Handler_read_next indique une lecture ordonnée de l’index (une valeur, puis la suivante, puis la suivante…).
Handler_read_first, Handler_read_key & Handler_read_next, indiquent là, un full index scan
Handler_read_rnd & Handler_read_rnd_next indiquent un full table scan sur la table temporaire
Handler_update nous donne une indication sur le nombre de mise à jours dans la table temporaire (à cause du tri)
Handler_write indique le nombre de lignes insérées dans la table temporaire
Ces 2 derniers paramètres confirme donc la création de la table temporaire et l’opération de tri
Pour avoir plus d’informations sur le tri:
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 232 |
| Sort_scan | 1 |
+-------------------+-------+
Sort_rows: nombre de lignes triées (nombre d’enregistrements de la table temporaire).
Sort_scan: nombre de tri.
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
Created_tmp_tables: nombre de table temporaire crée. La bonne nouvelle est que la table temporaire est créee en mémoire (Created_tmp_disk_tables=0)
réinitialise les variables de sessions
En utilisant l’index idx_cc_population (le bon index)
SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode; … 232 rows in set (0.00 sec)SHOW STATUS LIKE ‘handler%’;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 4079 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
Handler_read_first & Handler_read_next valident le full index scan et puis c’est tout 
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
pas de tri
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
pas de table(s) temporaire(s)
cqfd
Tags: group by, optimisation
