dasini.net – Journal d'un expert MySQL

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%’;

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%’;

Sort_rows: nombre de lignes triées (nombre d’enregistrements de la table temporaire).
Sort_scan: nombre de tri.

SHOW SESSION STATUS LIKE ‘created%’;

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%’;

Handler_read_first &  Handler_read_next valident le full index scan et puis c’est tout 🙂

SHOW SESSION STATUS LIKE ‘sort%’;

pas de tri

SHOW SESSION STATUS LIKE ‘created%’;

pas de table(s) temporaire(s)

cqfd

Filed under:
MySQL, optimisation by Olivier DASINI