Utilitaires
Scripts
Divers
Jeux
Rechercher
Quillevere.net
Réflexions informatiques

Optimiser une table MySQL

25/06/2019
Au fil des usages d'une base de données, celle-ci va évoluer et en cas de lenteur, certaines choses sont à étudier afin d'optimiser les performances.

Types des champs utilisés

Afin de gagner à la fois en performance et en espace disque sur une base de données, quel que soit son type, il est judicieux d'utiliser le format des champs le plus adapté. 

MySQL dispose d'une commande particulière, PROCEDURE ANALYSE, permettant de parcourir une partie ou l'intégralité des lignes d'une table afin de déterminer, pour chaque champ, le type à utiliser permettant de couvrir tous les cas.

La commande s'écrit ainsi et est à exécuter pour chaque table à analyser :

SELECT * FROM matable PROCEDURE ANALYSE()

Elle prend 2 paramètres optionnels : le nombre de lignes maximum à évaluer et la quantité de mémoire à utiliser pour l'évaluation. Plus de détails sur le site de MySQL.

Un exemple de résultat proposé par la commande :

Il faut bien faire attention car les résultats dépendront des données en base ou de l'échantillon sur lequel l'analyse est faite.

Utilisation ou non des index

Les index créés sur une table occupent un certain espace disque. Il faut donc s'assurer que cet espace est nécessaire. La requête suivante permet d'afficher l'occupation disque des données et des index d'une table donnée :

SELECT database_name, TABLE_NAME, index_name,
 ROUND((stat_value*@@innodb_page_size)/1024/1024, 2) AS tailleMo,
ROUND(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES schem
        WHERE schem.TABLE_NAME = stats.table_name AND schem.TABLE_SCHEMA = stats.database_name))*(stat_value*@@innodb_page_size)), 2) AS Taux
FROM mysql.innodb_index_stats stats
 WHERE stat_name='size'
 AND TABLE_NAME = 'matable'
AND database_name = 'mabase'

Il faut remplacer matable et mabase par la table à analyser. Si des index occupent beaucoup de place, il faut se poser la question de leur utilité : sont-ils vraiment utilisés ? La requête suivante affiche les index inutilisés depuis le démarragede MySQL :

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema='mabase';

En croisant ces 2 informations, on a ainsi la taille disque des index inutilisés (depuis le démarrage de MySQL) :

SELECT index_inutilises.object_schema, index_inutilises.object_name, index_inutilises.index_name,
    ROUND((stat_value*@@innodb_page_size)/1024/1024, 2) AS tailleMo
FROM sys.schema_unused_indexes index_inutilises
INNER JOIN mysql.innodb_index_stats stats
ON index_inutilises.object_schema=stats.database_name
AND index_inutilises.object_name=stats.table_name
AND index_inutilises.index_name=stats.index_name
WHERE stat_name='size'
AND object_schema='mabase'
AND object_name='matable'
Dernière modification le 06/07/2019 - Quillevere.net

Rechercher sur le site

rss RSS info Informations