Recherche d'architecture MySQL et optimisation des performances

Résumé

MySQL est un système de gestion de bases de données relationnelles développé sur les langages de programmation C et C++, créé par MySQL AB, fondé par David Axmark, Allan Larsson et Michael Widenius. La version la plus récente est MySQL 9.0.

MySQL est un projet open source avec une licence GNU.

MySQL est actuellement le système de gestion de bases de données le plus populaire et le plus utile car il est open source, possède des fonctionnalités complètes et des performances élevées.

Architecture

• Schéma d'architecture MySQL : Source

Couche client

• La première couche de l'architecture système MySQL.
• Les services importants de cette couche sont la gestion des connexions, l'authentification et la sécurité.
  • Gestion des connexions : gère les connexions des clients. Lorsqu'un client souhaite se connecter au serveur MySQL, un nouveau thread est créé pour la connexion. Le serveur met en cache les threads lorsque de nouvelles connexions sont créées.
  • Authentification : Lorsqu'un client se connecte à un serveur, il doit fournir des informations d'authentification pour établir une connexion. En règle générale, les clients s'authentifient à l'aide d'un nom d'utilisateur et d'un mot de passe.
  • Sécurité : MySQL fournit de nombreuses fonctionnalités pour protéger la base de données et vérifier les autorisations des utilisateurs après l'authentification.

Couche serveur

• La couche suivante de MySQL est responsable du traitement des instructions de requête et de la fourniture de nombreux utilitaires.
• Les principales caractéristiques :
  • Parseur : lorsqu'un client demande une instruction de requête, le serveur l'analyse pour créer une arborescence de requête, réécrit la requête et définit l'ordre et les index à utiliser pour exécuter l'instruction de requête.
  • Optimiseur : il optimise l'instruction de requête avant son exécution. L'optimiseur ne se soucie pas du moteur utilisé, mais le moteur de stockage peut avoir un impact sur l'optimisation pour la requête spécifique.
  • Cache de requêtes : le cache de requêtes stocke le résultat d'une instruction de requête. Si un client demande à nouveau la même requête, le serveur renvoie le résultat mis en cache, en contournant les étapes d'analyse et d'optimisation. Cette fonctionnalité était obsolète dans la version 8.0 ou supérieure en raison de son problème de goulot d'étranglement.
  • Services et utilitaires :
    • Sauvegarde et restauration
    • Sécurité : utilisateur du fournisseur et système d'autorisations
    • Réplication : il s'agit d'un processus permettant de copier et de synchroniser les données du serveur principal vers de nombreux serveurs enfants.
    • Cluster
    • Partitionnement : divisez la table en plusieurs parties en utilisant une logique spécifique.
    • Workbench : c'est un outil visuel conçu pour interagir avec et utiliser MySQL.

Couche de stockage

• C'est une couche de responsabilité quant à la façon dont nous stockons les données dans la base de données.
• Par défaut, MySQL utilise le moteur de stockage InnoDB.
• MySQL prend en charge de nombreux moteurs de stockage différents :
  • InnoDB (par défaut)
  • MonISAM
  • MÉMOIRE
  • CSV
  • ARCHIVES
  • TROU NOIR
  • FUSION
  • FÉDÉRÉ

Optimiser les performances pour MySQL

Optimiser l'instruction SQL

Expliquer la déclaration

• Prédiction de requête sans exécution

explain sql_statememnt

• Expliquez après l'exécution de la requête

explain analyze sql_statement

Technique d'indexation

• Il améliore les performances lors des requêtes et des recherches.
• Lors de la création d'un index pour plusieurs colonnes, la première colonne est très importante. Il décide des performances d'une instruction de requête. Par exemple, si vous créez un index sur (nom, anniversaire), une requête utilisant la colonne « nom » utilisera cet index, mais pas une requête utilisant la colonne « anniversaire ».
• Syntaxe de création d'un nouvel index :

create index idx_birthday on customers(birthday);

• La commande rendra l'index spécifié inaccessible à l'optimiseur de requêtes, l'empêchant ainsi d'être pris en compte pour les plans d'exécution des requêtes :

alter table customers alter index idx_birthday invisible;

Partition

• Lorsque la taille d'une base de données est supérieure à 2 Go (ou supérieure à 10 millions d'enregistrements), vous devez utiliser le partitionnement pour cette base de données.
• Lors de l'utilisation du partitionnement, les performances s'amélioreront.
• Le plan de partition doit être basé sur la colonne fréquemment utilisée dans les clauses WHERE.

Optimiser les paramètres de la base de données

Accès au cache tampon

• C'est le pourcentage de données que le système charge depuis le cache tampon plutôt que depuis le stockage physique.
• Si le pourcentage est inférieur à 90%, alors vous devez l'optimiser.
• Dépend de 2 paramètres :
  • Innodb_buffer_pool_read_requests : requête totale envoyée au pool de tampons. Commande : afficher l'état global comme '%Innodb_buffer_pool_read_requests%
  • Innodb_buffer_pool_reads : le total des requêtes qui ne sont pas trouvées dans le pool de mémoire tampon en mémoire doit être lu à partir du disque. Commande : afficher l'état global comme '%Innodb_buffer_pool_reads%
• Formule de calcul :

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

Accès au cache de table

• MySQL mettra la table en cache pour l'interroger rapidement en mémoire.
• Si le pourcentage est inférieur à 80%, alors vous devez l'optimiser.
• Dépend de 2 paramètres :
  • Open_tables : le total des tables est ouvert dans le cache. Commande : afficher l'état global comme "Open_tables"
  • Opened_tables : le total des tables est ouvert. Commande : afficher l'état global comme "Opened_tables"
• Formule de calcul : Open_tables/Opened_tables

Accès au cache de définition de table

• Lors de l'exécution d'une requête, MySQL requiert des informations sur la table, telles que son nom, son nombre de colonnes et son nombre de lignes. Ces informations sont appelées définition du tableau.
• Si le pourcentage est inférieur à 80%, alors vous devez l'optimiser.
• Dépend de 2 paramètres :
  • Open_table_definitions : Les tables de définition totales sont en cache. Commande : afficher l'état global comme "Open_table_definitions"
  • Opened_tables : les tables de définition totale sont demandées. Commande : afficher le statut global comme "Opened_tables"
• Formule de calcul : Open_table_definitions/Opened_table_definitions

Table temporaire en mémoire

• De nombreuses instructions SQL comme Order, Group By, ... doivent utiliser une table temporaire.
• Si le pourcentage est inférieur à 80%, alors vous devez l'optimiser.
• Dépend de 2 paramètres :
  • Created_tmp_disk_tables : le total des tables temporaires est créé sur le disque. Commande : afficher l'état global comme '%Created_tmp_disk_tables%
  • Created_tmp_tables : le total des tables temporaires est créé. Commande : afficher l'état global comme '%Created_tmp_tables%
• Formule de calcul : (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

Conclusion

Ce blog propose des connaissances basées sur la recherche sur MySQL. MySQL est un système de gestion de bases de données relationnelles connu pour son faible coût, ses utilitaires étendus et ses hautes performances, ce qui le rend adapté à un large éventail de projets. Bien que ce blog couvre de nombreuses fonctionnalités et aspects de MySQL, il reste encore beaucoup à explorer. J'approfondirai les fonctionnalités et connaissances supplémentaires dans les prochains blogs.

Merci d'avoir lu et à bientôt sur mon prochain blog.

Document de référence

• Documentation MySQL
• Cours MySQL - Tran Quoc Huy
• Wikipédia - MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!