Optimisation de requêtes: comprendre l’optimiseur de MySQL

février 18, 2009
Tags: ,


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:

SHOW CREATE TABLE city\G
*************************** 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:

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

Ajoutons un index sur la colonne countrycode:

ALTER TABLE city ADD INDEX Idx_cc(CountryCode);

SHOW CREATE TABLE city\G
*************************** 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),
KEY Idx_cc (CountryCode)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary; Using filesort



L’index Idx_cc ne sert à rien

Ajoutons alors un index sur la colonne population

ALTER TABLE city ADD INDEX Idx_population(Population);

SHOW CREATE TABLE city\G
*************************** 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),
KEY Idx_cc (CountryCode),
KEY Idx_population (Population)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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


Pas mieux ! L’index Idx_population ne sert à rien.

Effacement des 2 index:

ALTER TABLE city DROP INDEX Idx_cc, DROP INDEX Idx_population;


Ajoutons un index composite sur les colonnes population, countrycode:

ALTER TABLE city ADD INDEX Idx_population_cc(Population, CountryCode);

SHOW CREATE TABLE city\G
*************************** 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`),
KEY `Idx_population_cc` (`Population`,`CountryCode`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: index
possible_keys: NULL
key: Idx_population_cc
key_len: 7
ref: NULL
rows: 4079
Extra: Using index; Using temporary; Using filesort



Ç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:

SHOW STATUS LIKE ‘Last_query_cost’\G
*************************** 1. row ***************************
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

ALTER TABLE test2.city ADD INDEX Idx_cc_population(CountryCode,Population);

SHOW CREATE TABLE city\G
*************************** 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`),
KEY `Idx_population_cc` (`Population`,`CountryCode`),
KEY `Idx_cc_population` (`CountryCode`,`Population`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT AVG(Population) FROM city GROUP BY CountryCode\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: index
possible_keys: NULL
key: Idx_cc_population
key_len: 7
ref: NULL
rows: 4079
Extra: Using index
1 row in set (0.00 sec)

SHOW STATUS LIKE ‘Last_query_cost’\G
*************************** 1. row ***************************
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

FLUSH STATUS;


En utilisant l’index idx_population_cc (le mauvais 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           | 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:

SHOW SESSION STATUS LIKE ‘sort%’;
 +-------------------+-------+
 | 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.

SHOW SESSION STATUS LIKE ‘created%’;
 +-------------------------+-------+
 | 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

FLUSH STATUS;

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 🙂

SHOW SESSION STATUS LIKE ‘sort%’;
 +-------------------+-------+
 | Variable_name     | Value |
 +-------------------+-------+
 | Sort_merge_passes | 0     |
 | Sort_range        | 0     |
 | Sort_rows         | 0     |
 | Sort_scan         | 0     |
 +-------------------+-------+


pas de tri

SHOW SESSION STATUS LIKE ‘created%’;
 +-------------------------+-------+
 | Variable_name           | Value |
 +-------------------------+-------+
 | Created_tmp_disk_tables | 0     |
 | Created_tmp_files       | 0     |
 | Created_tmp_tables      | 0     |
 +-------------------------+-------+

pas de table(s) temporaire(s)

cqfd

Filed under:
MySQL, optimisation by Olivier DASINI

6 Responses to “Optimisation de requêtes: comprendre l’optimiseur de MySQL”

  1. olive
    décembre 11th, 2009 at 0:15

    bonjour,

    article tres interressant. cependant je n’arrive pas a appliquer cette methode à des requetes avec des joins et autre, une petites idee ?

    exemple:

    SELECT keyword AS tag, nbview AS quantity
    FROM search
    WHERE search.type =0
    AND (
    search.lg = «  »
    OR search.lg = « en »
    )
    AND char_length( search.keyword ) 1
    GROUP BY keyword
    ORDER BY RAND( )
    LIMIT 90

    ou encore:

    SELECT * FROM videos v JOIN videosviews vv ON v.id = vv.idvideo WHERE v.type=0 AND v.genre=’humour’ AND v.id 73740 AND v.enable=1 AND v.mobile=1 LIMIT 2891,8

    merci d’avance
    olivier

  2. freshdaz
    décembre 22nd, 2009 at 18:38

    Bonjour Olivier,
    2 choses m’interpellent:
    * ORDER BY RAND( ). pratique mais à éviter, car on fait difficilement moins performant. C’est création d’un table temporaire automatiquement et peut être sur disque !
    * LIMIT 2891,8 également pratique, mais également à éviter. Le gain est essentiellement pour le développeur mais pas pour la bdd

    En ce qui concerne le covering index, on ne peut malheureusement pas l’avoir à tout les coups 🙂
    ++
    Olivier DASINI

  3. Riplay777
    janvier 12th, 2010 at 15:51

    Merci. Ce petit article m’a (enfin) apporté une information très claire sur l’utilisation des index. Reste plus qu’à appliquer. Avec toutes les requêtes que j’ai à optimiser, grosse somme de travail en perspective

  4. admin
    juin 22nd, 2011 at 10:44

    Super article. un cookie s’est glissé dans la requête suivante :
    En utilisant l’index idx_cc_population (le bon index)
    SELECT AVG(Population) FROM city use index(___idx_population_cc___) GROUP BY CountryCode;
    …
    Il serait sympa de remplacer les ‘xxx%’ par des ‘xxx%’ (apostrophes);
    ce qui permet de copier / coller les requêtes dans PhpMyAdmin.

    Curieusement, je n’est pas le même retour d’informations sur les requêtes avec ‘SHOW’.

  5. Mon site est lent, que faire (serveurs dédiés) « Astuces de Webmaster
    août 14th, 2012 at 12:37

    […] Lorsque vous avez un doute sur une requête, utilisez le mode EXPLAIN prévu par SQL pour expliciter la façon dont sont exécutées les requêtes, pour identifier les jointures sans index, etc. Un article très bien fait sur ce sujet : http://dasini.net/blog/2009/02/18/optimisation-de-requetes-comprendre-loptimiseur-de-mysql/ […]

  6. Explain : optimisation de requêtes MySQL - Arnaud Guignant
    octobre 14th, 2016 at 15:19

    […] officielle Article complet sur le sujet sur le site dasini.net Un article pour avoir toutes les clés pour bien concevoir ses […]