Ce quatrième article concerne les statistiques d’histogramme.
Depuis la version 5.6, il est possible de créer des statistiques persistés avec l’option innodb_stats_persistent=1 (par défaut depuis la 5.6.6) ou par table avec stats_persistent=1.
Les statistiques sont alors stockées dans la colonne last_update des tables mysql.innodb_table_ stats et mysql.innodb_index_stats.
Contrairement à MyISAM, les statistiques en InnoDB sont calculées seulement sur un échantillon et pas la totalité des valeurs dans la colonne (innodb_stats_persistent_sample_pages ou stats_sample_pages de CREATE TABLE ou ALTER TABLE).
MariaDB était un des seul moteur à les implémenter en 2017 depuis la 10.0.2 (mysql.column_stats). Maintenant, avec MySQL 8, nous avons également l’implémentation d’histogrammes.
Par défaut depuis 5.6.6, les statistiques persistés seront recalculées automatiquement avec innodb_stats_auto_recalc=1, lorsque plus de 10% des valeurs de la table auront été modifiées. Le recalcul automatique peut aussi être paramétré par table en utilisant le mot-clé stats_auto_recalc de CREATE TABLE ou ALTER TABLE.
Les statistiques sont ensuite recalculées en arrière plan et de manière asynchrone, donc elles peuvent ne pas être immédiatement disponibles. Pour assurer une disponibilité certaine des statistiques, il faut quand même programmer des ANALYZE TABLE réguliers, surtout suite à des changements importants de données (chargements, purges, etc…).
Depuis la 8.0.3, on a la possibilité de créer des statistiques d’histogramme pour en fournir plus à l’optimizer.
Nous allons donc voir dans cet article, comment créer et voir des statistiques d’histogrammes ainsi que leurs utilités.
L’optimizer de requêtes permet de traduire une requête en un plan d’exécution le plus optimal possible. Cependant, l’optimizer peut ne pas trouver le meilleur plan d’exécution pour cette requête et donc dégrader son temps d’exécution. Ce mauvais plan choisit par l’optimizer peut être dû à un manque d’informations sur la table en question (nombre de lignes, nombre de valeurs distinctes …).
Comment peut-on savoir si des statistiques peuvent être importantes pour les données de tables ?
Nous allons donc le voir dans l’exemple ci-dessous.
On va créer une table travail afin de faire des tests de SELECT sur un champ de type TIME.
mysql> create table travail ( -> personne_id mediumint(8), -> heure_de_travail TIME, -> localisation varchar(30) NOT NULL); Query OK, 0 rows affected (1.24 sec) mysql> select * from travail where heure_de_travail between "08:00:00" and "10:00:00"; +-------------+------------------+--------------+ | personne_id | heure_de_travail | localisation | +-------------+------------------+--------------+ | 1 | 08:00:00 | Nantes | | 1 | 09:00:00 | Nantes | | 3 | 09:00:00 | Rennes | | 4 | 10:00:00 | Saint Brieuc | | 1 | 08:00:00 | Bordeaux | | 5 | 08:00:00 | Lyon | | 5 | 09:00:00 | Lyon | | 6 | 09:00:00 | Paris | | 7 | 10:00:00 | Boulogne | | 7 | 09:00:00 | Boulogne | | 8 | 10:00:00 | Londres | | 9 | 10:00:00 | Berlin | | 9 | 09:00:00 | Berlin | | 10 | 08:00:00 | Amsterdam | +-------------+------------------+--------------+ 14 rows in set (0.39 sec) mysql> select * from travail where heure_de_travail between "10:00:00" and "12:00:00"; +-------------+------------------+--------------+ | personne_id | heure_de_travail | localisation | +-------------+------------------+--------------+ | 4 | 10:00:00 | Saint Brieuc | | 7 | 10:00:00 | Boulogne | | 8 | 11:00:00 | Londres | | 8 | 10:00:00 | Londres | | 9 | 10:00:00 | Berlin | +-------------+------------------+--------------+ 5 rows in set (0.00 sec)
Par exemple, si on exécute différentes requêtes sur la table travail avec une condition sur la colonne heure_de_travail, nous n’aurons pas le même nombre de lignes retournées. En effet, la première requête nous retournera plus de lignes car il y a plus de personnes travaillant dans l’horaire 08h00-10h00 plutôt que 10h00-12h00 dans la seconde requête.
Si aucune statistique n’est mise en place pour cette table travail, l’optimizer va supposer que les valeurs de heure_de_travail sont toutes égales, même si les valeurs sont comprises entre 23h et 6h par exemple.
Il faut donc informer l’optimizer de cette différence dans les données et cela en créant des statistiques d’histogramme pour cette colonne heure_de_travail.
Un histogramme permet de donner une approximation de la distribution de données pour une colonne.
Nous allons faire maintenant nos tests sur 2 tables que l’on a créé : personne et ville.
mysql> select table_schema, table_name, engine, version, create_time, update_time, check_time from information_schema.tables where table_schema = 'world' and table_name in ('personne','ville');
+--------------+------------+--------+---------+---------------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | VERSION | CREATE_TIME | UPDATE_TIME | CHECK_TIME |
+--------------+------------+--------+---------+---------------------+-------------+------------+
| world | personne | InnoDB | 10 | 2019-03-18 11:55:58 | NULL | NULL |
| world | ville | InnoDB | 10 | 2019-03-21 12:21:13 | NULL | NULL |
+--------------+------------+--------+---------+---------------------+-------------+------------+
2 rows in set (0.00 sec)J’ai inséré 10 lignes dans la table ville. Ensuite, j’ai supprimé 2 indexes sur la table ville et j’en ai créé 3 autres dont 1 clé primaire. Ici, la somme des indexes (sans compter la clé primaire) est pourtant de 0 pour la table world.ville. Enfin, j’ai également modifié les noms de ville pour la table personne en enlevant certains accents et trait d’union le 21/03/2019 peu avant 12h00.
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-02-27 15:27:59 | 10 | 1 | 4 |
| world | ville | 2019-03-21 12:20:52 | 10 | 1 | 0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.13 sec)
mysql> select * from mysql.innodb_index_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| world | personne | PRIMARY | 2019-02-27 15:27:59 | n_diff_pfx01 | 10 | 1 | personne_id |
| world | personne | PRIMARY | 2019-02-27 15:27:59 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | PRIMARY | 2019-02-27 15:27:59 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_age | 2019-02-27 15:27:59 | n_diff_pfx01 | 6 | 1 | age |
| world | personne | idx_personne_age | 2019-02-27 15:27:59 | n_diff_pfx02 | 10 | 1 | age,personne_id |
| world | personne | idx_personne_age | 2019-02-27 15:27:59 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_age | 2019-02-27 15:27:59 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_nom | 2019-02-27 15:27:59 | n_diff_pfx01 | 10 | 1 | nom |
| world | personne | idx_personne_nom | 2019-02-27 15:27:59 | n_diff_pfx02 | 10 | 1 | nom,personne_id |
| world | personne | idx_personne_nom | 2019-02-27 15:27:59 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_nom | 2019-02-27 15:27:59 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_prenom | 2019-02-27 15:27:59 | n_diff_pfx01 | 10 | 1 | prenom |
| world | personne | idx_personne_prenom | 2019-02-27 15:27:59 | n_diff_pfx02 | 10 | 1 | prenom,personne_id |
| world | personne | idx_personne_prenom | 2019-02-27 15:27:59 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_prenom | 2019-02-27 15:27:59 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_ville | 2019-02-27 15:27:59 | n_diff_pfx01 | 10 | 1 | ville |
| world | personne | idx_personne_ville | 2019-02-27 15:27:59 | n_diff_pfx02 | 10 | 1 | ville,personne_id |
| world | personne | idx_personne_ville | 2019-02-27 15:27:59 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_ville | 2019-02-27 15:27:59 | size | 1 | NULL | Number of pages in the index |
| world | ville | PRIMARY | 2019-03-21 12:20:52 | n_diff_pfx01 | 10 | 1 | ville_id |
| world | ville | PRIMARY | 2019-03-21 12:20:52 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | ville | PRIMARY | 2019-03-21 12:20:52 | size | 1 | NULL | Number of pages in the index |
| world | ville | idx_ville_cp | 2019-03-21 12:20:52 | n_diff_pfx01 | 9 | 1 | code_postal |
| world | ville | idx_ville_cp | 2019-03-21 12:20:52 | n_diff_pfx02 | 10 | 1 | code_postal,ville_id |
| world | ville | idx_ville_cp | 2019-03-21 12:20:52 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | ville | idx_ville_cp | 2019-03-21 12:20:52 | size | 1 | NULL | Number of pages in the index |
| world | ville | idx_ville_nom | 2019-03-21 12:20:52 | n_diff_pfx01 | 10 | 1 | nom |
| world | ville | idx_ville_nom | 2019-03-21 12:20:52 | n_diff_pfx02 | 10 | 1 | nom,ville_id |
| world | ville | idx_ville_nom | 2019-03-21 12:20:52 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | ville | idx_ville_nom | 2019-03-21 12:20:52 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+La table ville et l’ajout de données à celle-ci a été fait autour de 12h00.
Les dernières statistiques pour la table personne datent du 27/02/2019 15h27 et du 21/03/2019 12h20 pour la table ville.
Je lance un ANALYZE TABLE tout simple, sans options sur les tables personne et ville.
mysql> analyze table world.personne;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| world.personne | analyze | status | OK |
+----------------+---------+----------+----------+
1 row in set (0.43 sec)
mysql> analyze table world.ville;
+-------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| world.ville | analyze | status | OK |
+-------------+---------+----------+----------+
1 row in set (0.13 sec)
mysql> select table_schema, table_name, engine, version, create_time, update_time, check_time from information_schema.tables where table_schema = 'world' and table_name in ('personne','ville');
+--------------+------------+--------+---------+---------------------+---------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | VERSION | CREATE_TIME | UPDATE_TIME | CHECK_TIME |
+--------------+------------+--------+---------+---------------------+---------------------+------------+
| world | personne | InnoDB | 10 | 2019-03-18 11:55:58 | 2019-03-21 11:59:42 | NULL |
| world | ville | InnoDB | 10 | 2019-03-21 12:21:13 | NULL | NULL |
+--------------+------------+--------+---------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)On constate que l’utilisation du ANALYZE TABLE a mis à jour la colonne UPDATE_TIME au 21/03/2019 à 11h59 pour la table personne, moment ou j’ai fait les modifications de certains noms de ville.
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-21 14:59:47 | 10 | 1 | 4 |
| world | ville | 2019-03-21 14:59:54 | 10 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| world | personne | PRIMARY | 2019-03-21 14:59:47 | n_diff_pfx01 | 10 | 1 | personne_id |
| world | personne | PRIMARY | 2019-03-21 14:59:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | PRIMARY | 2019-03-21 14:59:47 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_age | 2019-03-21 14:59:47 | n_diff_pfx01 | 6 | 1 | age |
| world | personne | idx_personne_age | 2019-03-21 14:59:47 | n_diff_pfx02 | 10 | 1 | age,personne_id |
| world | personne | idx_personne_age | 2019-03-21 14:59:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_age | 2019-03-21 14:59:47 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_nom | 2019-03-21 14:59:47 | n_diff_pfx01 | 10 | 1 | nom |
| world | personne | idx_personne_nom | 2019-03-21 14:59:47 | n_diff_pfx02 | 10 | 1 | nom,personne_id |
| world | personne | idx_personne_nom | 2019-03-21 14:59:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_nom | 2019-03-21 14:59:47 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_prenom | 2019-03-21 14:59:47 | n_diff_pfx01 | 10 | 1 | prenom |
| world | personne | idx_personne_prenom | 2019-03-21 14:59:47 | n_diff_pfx02 | 10 | 1 | prenom,personne_id |
| world | personne | idx_personne_prenom | 2019-03-21 14:59:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_prenom | 2019-03-21 14:59:47 | size | 1 | NULL | Number of pages in the index |
| world | personne | idx_personne_ville | 2019-03-21 14:59:47 | n_diff_pfx01 | 9 | 1 | ville |
| world | personne | idx_personne_ville | 2019-03-21 14:59:47 | n_diff_pfx02 | 10 | 1 | ville,personne_id |
| world | personne | idx_personne_ville | 2019-03-21 14:59:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | personne | idx_personne_ville | 2019-03-21 14:59:47 | size | 1 | NULL | Number of pages in the index |
| world | ville | PRIMARY | 2019-03-21 14:59:54 | n_diff_pfx01 | 10 | 1 | ville_id |
| world | ville | PRIMARY | 2019-03-21 14:59:54 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | ville | PRIMARY | 2019-03-21 14:59:54 | size | 1 | NULL | Number of pages in the index |
| world | ville | idx_ville_cp | 2019-03-21 14:59:54 | n_diff_pfx01 | 9 | 1 | code_postal |
| world | ville | idx_ville_cp | 2019-03-21 14:59:54 | n_diff_pfx02 | 10 | 1 | code_postal,ville_id |
| world | ville | idx_ville_cp | 2019-03-21 14:59:54 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | ville | idx_ville_cp | 2019-03-21 14:59:54 | size | 1 | NULL | Number of pages in the index |
| world | ville | idx_ville_nom | 2019-03-21 14:59:54 | n_diff_pfx01 | 10 | 1 | nom |
| world | ville | idx_ville_nom | 2019-03-21 14:59:54 | n_diff_pfx02 | 10 | 1 | nom,ville_id |
| world | ville | idx_ville_nom | 2019-03-21 14:59:54 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| world | ville | idx_ville_nom | 2019-03-21 14:59:54 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+---------------------+---------------------+--------------+------------+-------------+-----------------------------------+
30 rows in set (0.00 sec)Suite au ANALYZE TABLE, les tables personne et ville ainsi que leurs indexes ont vu leurs statistiques être mises à jour.
Nous allons maintenant tester la création de statistiques d’histogramme.
Mais avant tout, il faut regarder de quand date les dernières mise à jour de statistiques des tables personne et ville.
mysql> select table_schema, table_name, engine, version, create_time, update_time, check_time from information_schema.tables where table_schema = 'world' and table_name in ('personne','ville');
+--------------+------------+--------+---------+---------------------+---------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | VERSION | CREATE_TIME | UPDATE_TIME | CHECK_TIME |
+--------------+------------+--------+---------+---------------------+---------------------+------------+
| world | personne | InnoDB | 10 | 2019-03-21 15:56:19 | 2019-03-21 11:59:42 | NULL |
| world | ville | InnoDB | 10 | 2019-03-21 12:21:13 | NULL | NULL |
+--------------+------------+--------+---------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-21 14:59:47 | 10 | 1 | 4 |
| world | ville | 2019-03-21 15:52:07 | 11 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)On constate que les dernières mises à jour de statistiques datent du 21/03/2019 pour les tables personne et ville.
Maintenant, je vais modifier plusieurs valeurs sur les données de la colonne age et ville de la table personne ainsi que la colonne code_postal de la table ville. De plus, je vais également ajouter une ligne aux tables ville et personne, pour ensuite créer des statistiques d’histogramme sur la colonne age de la table personne et la colonne code_postal de la table ville.
mysql> update personne set age=26 where personne_id=2; Query OK, 1 row affected (0.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update personne set age=35 where personne_id=3; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update personne set age=41 where personne_id=4; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update personne set age=29 where personne_id=5; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ville set code_postal=44300 where ville_id=1; Query OK, 1 row affected (0.12 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ville set code_postal=44470 where ville_id=2; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ville set code_postal=44490 where ville_id=8; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ville set code_postal=44710 where ville_id=6; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0
La variable autocommit étant positionnée par défaut à ON, il n’y a pas besoin de faire de commit.
mysql> analyze table personne update histogram on ville with 16 buckets; +----------------+-----------+----------+--------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+-----------+----------+--------------------------------------------------+ | world.personne | histogram | status | Histogram statistics created for column 'ville'. | +----------------+-----------+----------+--------------------------------------------------+ 1 row in set (0.32 sec) mysql> analyze table ville update histogram on code_postal with 16 buckets; +-------------+-----------+----------+--------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------+-----------+----------+--------------------------------------------------------+ | world.ville | histogram | status | Histogram statistics created for column 'code_postal'. | +-------------+-----------+----------+--------------------------------------------------------+ 1 row in set (0.15 sec)
Nous avons créé des statistiques d’histogramme pour la colonne ville et code_postal avec 32 buckets. Le nombre de buckets doit être spécifié quand on créé un histograme et il doit être compris entre 1 et 1024. Ce chiffre dépend de plusieurs facteurs dont le nombre de valeurs distinctes, la qualité de la données, le besoin de précision …
Cela ne sert donc pas à grand chose de mettre une grande valeur mais plutôt commencer bas et augmenter plus tard si besoin est.
mysql> select table_schema, table_name, engine, version, create_time, update_time, check_time from information_schema.tables where table_schema = 'world' and table_name in ('personne','ville');
+--------------+------------+--------+---------+---------------------+---------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | VERSION | CREATE_TIME | UPDATE_TIME | CHECK_TIME |
+--------------+------------+--------+---------+---------------------+---------------------+------------+
| world | personne | InnoDB | 10 | 2019-03-21 15:56:19 | 2019-03-21 15:57:22 | NULL |
| world | ville | InnoDB | 10 | 2019-03-21 12:21:13 | 2019-03-21 15:52:07 | NULL |
+--------------+------------+--------+---------+---------------------+---------------------+------------+
2 rows in set (0.12 sec)
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-21 14:59:47 | 10 | 1 | 4 |
| world | ville | 2019-03-27 11:40:11 | 11 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.05 sec)On constate que la mise à jour des statistiques a été faite pour la table ville grâce aux statistiques d’histogramme sur la colonne code_postal. Pourtant, la table personne n’a pas eu ses statistiques mises à jour !
Je peux voir de quand date les dernières mises à jour des histogrammes créés précédement sur les colonnes des tables grâce à la table column_statistics de information_schema.
mysql> SELECT schema_name, table_name, column_name, histogram->'$."number-of-buckets-specified"' as number_of_buckets, histogram->'$."sampling-rate"' as sampling_rate, HISTOGRAM->'$."last-updated"' as last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE SCHEMA_NAME = "world"
-> AND TABLE_NAME IN ('personne','ville');
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | number_of_buckets | sampling_rate | last_updated |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| world | personne | ville | 16 | 1.0 | 2019-03-28 08:46:42.629179 |
| world | ville | code_postal | 16 | 1.0 | 2019-03-28 08:46:57.299199 |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
3 rows in set (0.00 sec)Ici en version MySQL 8.0.14, les paramètres innodb_stats_persistent et innodb_stats_auto_recalc sont bien activés. Ce qui veut dire que dire que les statistiques d’index sont conservées sur disque quand innodb_stats_persistant est activé (par défaut) ou lors de la création/modification d’une table. De plus, innob_stats_auto_recalc permet de recalculer automatiquement les statistiques persistantes si les données ont été modifiées récemment avec un seuil de 10% des lignes de la table.
mysql> show global variables like 'innodb_stats_persistent'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_stats_persistent | ON | +-------------------------+-------+ 1 row in set (0.06 sec) mysql> show global variables like 'innodb_stats_auto_recalc'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_auto_recalc | ON | +--------------------------+-------+ 1 row in set (0.00 sec)
Je désactive le recalcul automatique des statistiques si 10% de la table est modifiée.
set global innodb_stats_auto_recalc=OFF;
Maintenant, je mets à jour les données de la personne et regarde si les statistiques ont été mises à jour automatiquement.
mysql> update personne set ville ='Paris' where personne_id in (7,10,2);
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> update personne set ville ='Lyon' where personne_id in (5,8);
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-27 17:07:38 | 11 | 1 | 4 |
| world | ville | 2019-03-27 11:40:11 | 11 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
3 rows in set (0.00 sec)Effectivement, malgré la modification de 5 lignes sur les 11 lignes de la table personne, les statistiques de la table personne n’ont pas été mises à jour.
Avec la commande update, nous pouvons remettre à jour les statistiques des histogrammes sur plusieurs colonnes de la table personne.
mysql> analyze table personne update histogram on age, ville with 16 buckets; +----------------+-----------+----------+--------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+-----------+----------+--------------------------------------------------+ | world.personne | histogram | status | Histogram statistics created for column 'age'. | | world.personne | histogram | status | Histogram statistics created for column 'ville'. | +----------------+-----------+----------+--------------------------------------------------+ 2 rows in set (0.11 sec)
L’histogramme sur la colonne age va être créé et celui pour la colonne ville va être écrasé par le nouveau.
mysql> SELECT schema_name, table_name, column_name, histogram->'$."number-of-buckets-specified"' as number_of_buckets, histogram->'$."sampling-rate"' as sampling_rate, HISTOGRAM->'$."last-updated"' as last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE SCHEMA_NAME = "world"
-> AND TABLE_NAME IN ('personne','ville');
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | number_of_buckets | sampling_rate | last_updated |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| world | personne | age | 16 | 1.0 | 2019-03-28 10:46:04.115520 |
| world | personne | ville | 16 | 1.0 | 2019-03-28 10:46:04.135520 |
| world | ville | code_postal | 16 | 1.0 | 2019-03-28 08:46:57.299199 |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
3 rows in set (0.00 sec)On peut supprimer également un histogramme avec un DROP histogram.
mysql> analyze table personne drop histogram on age;
+----------------+-----------+----------+------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+------------------------------------------------+
| world.personne | histogram | status | Histogram statistics removed for column 'age'. |
+----------------+-----------+----------+------------------------------------------------+
1 row in set (0.09 sec)
mysql> SELECT schema_name, table_name, column_name, histogram->'$."number-of-buckets-specified"' as number_of_buckets, histogram->'$."sampling-rate"' as sampling_rate, HISTOGRAM->'$."last-updated"' as last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE SCHEMA_NAME = "world"
-> AND TABLE_NAME IN ('personne','ville');
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | number_of_buckets | sampling_rate | last_updated |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| world | personne | ville | 16 | 1.0 | 2019-03-28 10:46:04.135520 |
| world | ville | code_postal | 16 | 1.0 | 2019-03-28 08:46:57.299199 |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
2 rows in set (0.00 sec)Pour autant, si nous créons un histogramme de statistiques sur plusieurs colonnes d’une table et qu’une de ces colonnes n’existe pas, le serveur va quand même créer et stocker l’histogramme pour les autres colonnes présentes dans la table.
mysql> analyze table personne update histogram on ville,age,nom,pays with 16 buckets;
+----------------+-----------+----------+--------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+--------------------------------------------------+
| world.personne | histogram | status | Histogram statistics created for column 'age'. |
| world.personne | histogram | status | Histogram statistics created for column 'nom'. |
| world.personne | histogram | Error | The column 'pays' does not exist. |
| world.personne | histogram | status | Histogram statistics created for column 'ville'. |
+----------------+-----------+----------+--------------------------------------------------+
4 rows in set (0.08 sec)
mysql> SELECT schema_name, table_name, column_name, histogram->'$."number-of-buckets-specified"' as number_of_buckets, histogram->'$."sampling-rate"' as sampling_rate, HISTOGRAM->'$."last-updated"'
as last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE SCHEMA_NAME = "world"
-> AND TABLE_NAME IN ('personne','ville');
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | number_of_buckets | sampling_rate | last_updated |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| world | personne | ville | 16 | 1.0 | 2019-03-28 14:01:41.921075 |
| world | ville | code_postal | 16 | 1.0 | 2019-03-28 08:46:57.299199 |
| world | personne | age | 16 | 1.0 | 2019-03-28 14:01:41.901075 |
| world | personne | nom | 16 | 1.0 | 2019-03-28 14:01:41.921075 |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
4 rows in set (0.00 sec)Si on réactive le recalcul automatique des statistiques si 10% de la table est modifié et que l’on ajoute ou que l’on met à jour la table personne, nous pouvons voir que ces statistiques sont mises à jour après modification de 2 lignes sur ces 11.
mysql> set global innodb_stats_auto_recalc=ON;
Query OK, 0 rows affected (0.04 sec)
mysql> show global variables like 'innodb_stats_auto_recalc';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
+--------------------------------------+-------------+
1 rows in set (0.12 sec)
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-27 17:07:38 | 11 | 1 | 4 |
| world | ville | 2019-03-27 11:40:11 | 11 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> update personne set ville='Lille' where personne_id=3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update personne set ville='Bordeaux' where personne_id=9;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update personne set ville='Montpellier' where personne_id=6;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-28 15:58:31 | 11 | 1 | 4 |
| world | ville | 2019-03-27 11:40:11 | 11 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> insert into personne (prenom,nom,ville,age) values ('Frank','Lamande','Nantes',44);
Query OK, 1 row affected (0.09 sec)
mysql> insert into personne (prenom,nom,ville,age) values ('Ripaux','David','Nantes',43);
Query OK, 1 row affected (0.06 sec)
mysql> insert into personne (prenom,nom,ville,age) values ('Lobreau','Benoit','Nantes',37);
Query OK, 1 row affected (0.07 sec)
mysql> select * from mysql.innodb_table_stats where database_name='world' and table_name in ('personne','ville');
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| world | personne | 2019-03-28 16:04:11 | 14 | 1 | 4 |
| world | ville | 2019-03-27 11:40:11 | 11 | 1 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
2 rows in set (0.00 sec)On constate bien qu’après la modification ou l’ajout de minimum 3 lignes de la table personne, les statistiques ont été mises à jour grâce au paramètre innodb_stats_auto_recalc.
A partir de MySQL 8.0.2, il existe aussi une nouvelle variable système histogram_generation_max_mem_size. Cette nouvelle variable permet de contrôler un peu la quantité de mémoire que le serveur MySQL va autoriser à utiliser pour générer les statistiques d’histogramme. La valeur par défaut de cette variable est de 20Mo et sa valeur minimale est de 1Mo.
Cependant, pourquoi irons-nous changer la valeur de cette variable pour les statistiques d’histogramme ?
Quand on créé un histogramme sur une colonne, le serveur MySQL va lire toutes les données en mémoire et va faire le travail aussi en mémoire même avec le tri. Pour autant, si on veut créer un histogramme sur de très grosses tables, cela va lire des méga voir giga de données en mémoire et ce n’est peut-être pas ce que l’on souhaite.
Du coup, en fonction de la mémoire spcécifiée par la variable histogram_generation_max_mem_size, le serveur MySQL va calculer le nombre de lignes de données qui peuvent être stockées en mémoire. Si MySQL ne peut pas tout contenir en mémoire, il va contenir un sous-ensemble de lignes en fonction de la mémoire donnée et utilisera un “échantillonage”.
Ce “taux d’échantillonage” peut être observé dans la colonne histogram de la table column_statistics.
Avec l’exemple ci-dessous, nous allons modifier la mémoire spécifiée de la variable histogram_generation_max_mem_size afin de voir les changements qui peuvent s’opérer sur la colonne amount de la table payment pour la base sakila.
La table payment de la base sakila est l’une des 2 plus grosses tables du jeu de données d’exemple fourni par la version MySQL.
mysql> show global variables like 'histogram%'; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | histogram_generation_max_mem_size | 20000000 | +-----------------------------------+----------+ 1 row in set, 1 warning (0.83 sec) mysql> set histogram_generation_max_mem_size=1000000; Query OK, 0 rows affected (0.09 sec) mysql> show global variables like 'histogram%'; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | histogram_generation_max_mem_size | 20000000 | +-----------------------------------+----------+ 1 row in set, 1 warning (0.01 sec) mysql> set global histogram_generation_max_mem_size=1000000; Query OK, 0 rows affected (0.02 sec) mysql> show global variables like 'histogram%'; +-----------------------------------+---------+ | Variable_name | Value | +-----------------------------------+---------+ | histogram_generation_max_mem_size | 1000000 | +-----------------------------------+---------+ 1 row in set, 1 warning (0.01 sec)
On a modifier la variable histogram_generation_max_mem_size a sa valeur la plus petite.
mysql> SELECT schema_name, table_name, column_name, histogram->'$."number-of-buckets-specified"' as number_of_buckets, histogram->'$."sampling-rate"' as sampling_rate, HISTOGRAM->'$."last-updated"'
-> as last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE SCHEMA_NAME = "sakila"
-> AND TABLE_NAME IN ('customer','payment');
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | number_of_buckets | sampling_rate | last_updated |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
| sakila | payment | rental_id | 16 | 1.0 | 2019-03-29 10:55:55.343710 |
+-------------+------------+-------------+-------------------+---------------+----------------------------+
1 row in set (0.00 sec)
mysql> analyze table sakila.payment update histogram on amount with 16 buckets;
+----------------+-----------+----------+---------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+---------------------------------------------------+
| sakila.payment | histogram | status | Histogram statistics created for column 'amount'. |
+----------------+-----------+----------+---------------------------------------------------+
1 row in set (0.10 sec)
mysql> SELECT schema_name, table_name, column_name, histogram->'$."number-of-buckets-specified"' as number_of_buckets, histogram->'$."sampling-rate"' as sampling_rate, HISTOGRAM->'$."last-updated"'
-> as last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE SCHEMA_NAME = "sakila"
-> AND TABLE_NAME IN ('customer','payment');
+-------------+------------+-------------+-------------------+-------------------+----------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | number_of_buckets | sampling_rate | last_updated |
+-------------+------------+-------------+-------------------+-------------------+----------------------------+
| sakila | payment | rental_id | 16 | 1.0 | 2019-03-29 10:55:55.343710 |
| sakila | payment | amount | 16 | 0.863414701534806 | 2019-04-01 14:44:55.735269 |
+-------------+------------+-------------+-------------------+-------------------+----------------------------+
2 rows in set (0.00 sec)
On constate que l’optimizer aura créé un histogramme en lisant environ 86% des données de la colonne amount de la table payment sur la base sakila.
Cependant, l’échantillonage n’est pas déterministe et donc si on fait deux fois une mise à jour de l’histogramme sur le même jeu de données, on peut avoir 2 histogrammes différents si l’échantillonage est utilisé.
L’utilisation d’un histogramme peut être autant intéressante voir plus efficace qu’un index sur une colonne afin d’améliorer une requête.
Vous en savez donc un peu plus maintenant sur les statistiques d’histogrammes et leur utilisation en MySQL 8.
A très vite pour de nouveaux articles sur MySQL !
Erwan Ollitrault
Continuez votre lecture sur le blog :
- Nouveautés MySQL 8.0 : Les indexes invisibles (Capdata team) [MySQL]
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]
- Nouveautés MySQL 8.0 : Variables persistés (Capdata team) [MySQL]
- Nouveautés MySQL 8.0 : Configuration automatique de variables avec innodb_dedicated_server (Capdata team) [MySQL]
- Un trigger fait-il parti d’une transaction ? (Benjamin VESAN) [GénéralMySQLOracleSQL ServerSybase]