Optimiser MySQL et MariaDB : améliorer les per­for­mances sur les serveurs Linux

Les per­for­mances d’une base de données in­fluen­cent di­rec­te­ment la rapidité et la stabilité des ap­pli­ca­tions Web. MySQL et MariaDB offrent de nom­breuses options d’op­ti­mi­sa­tion, allant de la con­fi­gu­ra­tion aux index, en passant par le cache et la ré­pli­ca­tion. Des ajus­te­ments ciblés per­met­tent d’exploiter plus ef­fi­ca­ce­ment les res­sources et de réduire nettement les temps de réponse des requêtes.

Pourquoi optimiser MariaDB et MySQL ?

L’op­ti­mi­sa­tion de MariaDB et de MySQL est es­sen­tielle, car des bases de données non op­ti­mi­sées at­teig­nent vite leurs limites. Une per­for­mance in­suf­fi­sante peut entraîner des temps de char­ge­ment élevés, des délais d’ex­pi­ra­tion ou même des in­ter­rup­tions du système.

Lorsque le volume de données augmente ou que plusieurs uti­li­sa­teurs accèdent si­mul­ta­né­ment à la base, chaque requête inef­fi­cace sollicite inu­ti­le­ment le pro­ces­seur et la mémoire vive. Le stockage de données re­don­dantes ou l’absence d’index ra­len­tis­sent également le trai­te­ment. Une base de données bien optimisée réduit la charge du serveur, améliore l’évo­lu­ti­vité et garantit la stabilité des ap­pli­ca­tions, même en cas de forte activité.

Comment optimiser MySQL/MariaDB ?

Il existe de nom­breuses façons d’optimiser une base de données MariaDB ou MySQL sur un serveur Linux. Cela comprend les ajus­te­ments de con­fi­gu­ra­tion, l’op­ti­mi­sa­tion des index, l’amé­lio­ra­tion des requêtes, le réglage d’InnoDB ainsi que l’uti­li­sa­tion du cache et de la ré­pli­ca­tion. Les prin­ci­pales mesures sont pré­sen­tées ci-dessous.

Option 1 : iden­ti­fier les requêtes lentes

Une étape im­por­tante pour optimiser MySQL/MariaDB consiste à repérer les requêtes lentes ou inef­fi­caces. Une requête mal struc­tu­rée peut ralentir l’ensemble de la base de données.

MySQL et MariaDB peuvent être con­fi­gu­rés pour consigner toutes les requêtes dont l’exécution dépasse un certain seuil défini. Cela vous permet d’analyser les requêtes lentes et de les corriger au besoin.

Pour activer la jour­na­li­sa­tion des requêtes lentes, connectez-vous à MySQL ou MariaDB :

mysql -u root -p

Entrez la commande suivante pour activer la jour­na­li­sa­tion :

SET GLOBAL slow_query_log = 'ON';

La valeur seuil par défaut est fixée à 10 secondes. Utilisez la commande suivante pour activer la jour­na­li­sa­tion de toutes les requêtes dont l’exécution dépasse une seconde :

SET GLOBAL long_query_time = 1;

Ces requêtes seront en­re­gis­trées dans le fichier /var/lib/mysql/hostname-slow.log.

Des outils de sur­veil­lance comme mysqltuner ou performance_schema peuvent également être utilisés pour iden­ti­fier les requêtes à optimiser et fournir des in­for­ma­tions pré­cieuses sur les per­for­mances.

Option 2 : adapter la con­fi­gu­ra­tion InnoDB

La con­fi­gu­ra­tion de MariaDB/MySQL est l’un des moyens les plus efficaces pour améliorer du­ra­ble­ment les per­for­mances d’une base de données. De nom­breuses ins­tal­la­tions par défaut utilisent des valeurs gé­né­riques adaptées à de petits en­vi­ron­ne­ments de test, mais inap­pro­priées pour des systèmes en pro­duc­tion avec un grand nombre de requêtes. En ajustant finement les pa­ra­mètres InnoDB, la base de données exploite les res­sources de manière bien plus efficace.

Les pa­ra­mètres les plus im­por­tants incluent notamment :

• innodb_flush_log_at_trx_commit : ce paramètre équilibre per­for­mance et fiabilité. La valeur par défaut 1 écrit chaque tran­sac­tion im­mé­dia­te­ment sur le disque, ga­ran­tis­sant une sécurité maximale, mais ra­len­tis­sant les per­for­mances en cas de forte charge tran­sac­tion­nelle. La valeur 2 réduit con­si­dé­ra­ble­ment les opé­ra­tions d’E/S, avec un risque minime de perte de données en cas de crash.
• innodb_log_file_size : la taille des fichiers journaux InnoDB influence di­rec­te­ment la vitesse d’écriture. Des fichiers plus grands per­met­tent de stocker davantage de tran­sac­tions en mémoire avant leur écriture sur le disque.
• innodb_file_per_table : un fichier de ta­bles­pace distinct est créé pour chaque table InnoDB. Cela simplifie la gestion des grandes tables, réduit la frag­men­ta­tion du ta­bles­pace commun et améliore la per­for­mance lors des sau­ve­gardes.
• innodb_buffer_pool_size : cette valeur doit gé­né­ra­le­ment re­pré­sen­ter entre 50 et 80 % de la mémoire vive dis­po­nible, afin de conserver un maximum de données et d’index en mémoire.
• innodb_flush_method : ce paramètre définit la méthode d’écriture des données et journaux sur le disque. Réglez cette option sur O_DIRECT pour éviter la double mise en tampon.

Pour modifier les pa­ra­mètres InnoDB, ouvrez le fichier my.cnf et ajustez la con­fi­gu­ra­tion. Voici un exemple de con­fi­gu­ra­tion typique :

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT

Re­dé­mar­rez MariaDB/MySQL pour que les mo­di­fi­ca­tions prennent effet.

Option 3 : adapter les index

Les index sont es­sen­tiels pour accélérer les requêtes MySQL ou MariaDB. Plutôt que de parcourir chaque ligne d’une table, la base de données peut accéder di­rec­te­ment aux en­re­gis­tre­ments per­ti­nents via l’index.

Cependant, un trop grand nombre d’index ou des index mal choisis peuvent nuire aux per­for­mances. Chaque index consomme de l’espace et alourdit les opé­ra­tions d’écriture. Il est donc re­com­mandé d’indexer uni­que­ment les colonnes fré­quem­ment utilisées dans les clauses WHERE, JOIN ou ORDER BY.

Exemple : si la table users est souvent in­ter­ro­gée par la colonne email, créez un index pour accélérer ces requêtes :

CREATE INDEX idx_user_email ON users(email);

Avec cet index, des requêtes telles que

SELECT * FROM users WHERE email='xyz@example.com';

…s’exécutent nettement plus ra­pi­de­ment, car la base de données n’a pas besoin de parcourir chaque ligne de la table, mais accède di­rec­te­ment aux entrées cor­res­pon­dantes.

Les index combinés peuvent également être utiles lorsque plusieurs colonnes sont fré­quem­ment utilisées ensemble dans des requêtes.

Les index devenus inutiles ou rarement sol­li­ci­tés doivent être supprimés ré­gu­liè­re­ment afin d’éco­no­mi­ser de l’espace et d’améliorer la per­for­mance des opé­ra­tions d’écriture. Par exemple, pour supprimer l’index idx_old_column :

DROP INDEX idx_old_column ON users;

Option 4 : optimiser les requêtes

Les requêtes SQL complexes ou mal conçues peuvent sur­char­ger la base de données et ralentir con­si­dé­ra­ble­ment les per­for­mances, surtout lorsqu’elles con­cer­nent de grandes tables.

Pour optimiser les requêtes, commencez par analyser leur mode d’exécution à l’aide de la commande EXPLAIN, qui affiche le plan d’exécution détaillé et aide à repérer les points à améliorer.

EXPLAIN SELECT id, email FROM users WHERE status='active';

Avec EXPLAIN, MySQL et MariaDB indiquent quels index sont utilisés, combien de lignes sont lues et dans quel ordre les tables sont traitées. Vous pouvez ainsi évaluer l’ef­fi­ca­cité d’une requête et dé­ter­mi­ner si des op­ti­mi­sa­tions sont né­ces­saires, par exemple en ajoutant des index ou en ajustant les jointures.

Évitez les requêtes du type SELECT *, car elles chargent toutes les colonnes, y compris celles dont vous n’avez pas besoin. Sé­lec­tion­nez uni­que­ment les colonnes né­ces­saires afin de réduire le volume de données trans­fé­rées et d’accélérer l’exécution. Pour les jointures complexes, formulez les con­di­tions dans la clause WHERE de manière aussi précise que possible afin d’éviter les scans complets de tables.

Option 5 : ré­pli­ca­tion et mise en cache

La ré­pli­ca­tion, c’est-à-dire la ré­par­ti­tion de la charge sur plusieurs serveurs, et la mise en cache, qui réduit le nombre d’accès directs à la base de données, con­tri­buent également à optimiser MariaDB et MySQL.

La ré­pli­ca­tion repose gé­né­ra­le­ment sur le principe primaire/réplique : le serveur maître gère toutes les opé­ra­tions d’écriture, tandis qu’un ou plusieurs serveurs ré­pli­quent les données et prennent en charge les requêtes de lecture. Ce modèle permet de répartir la charge et d’éviter la sa­tu­ra­tion du serveur principal. Bien que la mise en place de la ré­pli­ca­tion demande un certain travail initial, elle améliore nettement la per­for­mance, notamment pour les ap­pli­ca­tions à fort trafic.

La mise en cache peut, elle aussi, accélérer sen­si­ble­ment les temps de réponse. MySQL et MariaDB disposent du Query Cache, qui conserve en mémoire les résultats des requêtes fré­quem­ment exécutées. Ainsi, les mêmes requêtes n’ont pas besoin d’être re­cal­cu­lées.

Vous pouvez définir la taille du cache et activer le Query Cache à l’aide des pa­ra­mètres suivants :

SET GLOBAL query_cache_size = 64*1024*1024;
SET GLOBAL query_cache_type = 1;

Pour les ap­pli­ca­tions modernes, il est également judicieux d’utiliser des solutions de mise en cache externes comme Redis, qui per­met­tent d’accéder encore plus ra­pi­de­ment aux données fré­quem­ment demandées.

Option 6 : par­ti­tion­ne­ment des tables

Pour les tables très vo­lu­mi­neuses, le trai­te­ment des requêtes peut devenir plus lent, car la base de données doit parcourir un grand nombre de lignes. Le par­ti­tion­ne­ment permet de diviser une table en segments plus petits et lo­gi­que­ment séparés, par exemple selon la date, l’in­ter­valle d’ID ou d’autres critères.

Chaque partition est gérée en interne comme une table distincte, ce qui accélère les requêtes ciblant uni­que­ment certaines par­ti­tions.

Voici un exemple de par­ti­tion­ne­ment par année pour une table de commandes :

CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

Dans cet exemple, toutes les commandes de 2023 sont stockées dans la partition p2023, et celles de 2024 dans la partition p2024.

Option 7 : utiliser le pool de con­nexions

Chaque nouvelle connexion à MySQL ou MariaDB consomme du temps et des res­sources. Si votre ap­pli­ca­tion ouvre et ferme une connexion à chaque requête, cela génère une charge inutile sur le serveur.

Le pool de con­nexions, utilisé pour optimiser MariaDB et MySQL, permet de maintenir un certain nombre de con­nexions ouvertes. Les ap­pli­ca­tions réu­ti­li­sent ensuite ces con­nexions exis­tantes au lieu d’en créer de nouvelles à chaque requête.

Voici un exemple en PHP avec mysqli :

$mysqli = new mysqli('localhost', 'user', 'password', 'db');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

Au lieu d’ouvrir une nouvelle connexion pour chaque requête, le pool réutilise les con­nexions déjà établies. Cela réduit la charge sur le serveur de base de données et améliore la rapidité des temps de réponse.