Architecture d'optimisation MySql et moteur de stockage (partage de résumé)-tutoriel mysql-php.cn

Cet article vous apporte des connaissances pertinentes sur l'architecture MySQL et le résumé du moteur de stockage. J'espère qu'il vous sera utile.

Architecture d'optimisation MySql et moteur de stockage (partage de résumé)

1. Système structurel MySQL

En général, nous pouvons diviser MySQL en trois couches. Si la superposition est plus détaillée, la couche moteur de stockage peut être divisée en couche moteur et en couche moteur. Couche de stockage :

Couche de connexion
La couche supérieure est constituée de certains clients et services de liaison, y compris la communication sock locale et la plupart des communications de type TCP/IP basées sur des outils client/serveur. Il complète principalement certains traitements de connexion, authentification d'autorisation et solutions de sécurité associées. Le concept de pool de threads est introduit sur cette couche pour fournir des threads aux clients qui accèdent en toute sécurité via l'authentification. Des liens sécurisés basés sur SSL peuvent également être implémentés sur cette couche. Le serveur vérifie également les autorisations d'exploitation dont il dispose pour chaque client qui y accède en toute sécurité.

Couche de service
L'architecture de la deuxième couche complète principalement la plupart des fonctions de service de base, telles que l'interface SQL, et complète les requêtes mises en cache, l'analyse et l'optimisation SQL et l'exécution de certaines fonctions intégrées. Toutes les fonctions du moteur de stockage croisé sont également implémentées dans cette couche, telles que les procédures, les fonctions, etc. À cette couche, le serveur analysera la requête et créera l'arbre d'analyse interne correspondant, et terminera l'optimisation correspondante telle que déterminer l'ordre des requêtes de table, s'il faut utiliser des index, etc., et enfin générer les opérations d'exécution correspondantes. S'il s'agit d'une instruction select, le serveur interrogera également le cache interne. Si l'espace du cache est suffisamment grand, cela peut grandement améliorer les performances du système dans un environnement qui résout un grand nombre d'opérations de lecture.

Couche moteur
Couche moteur de stockage, le moteur de stockage est réellement responsable du stockage et de la récupération des données dans MySQL. Le serveur communique avec le moteur de stockage via l'API. Différents moteurs de stockage ont des fonctions différentes, nous pouvons donc choisir le moteur de stockage approprié en fonction de nos besoins.

Couche de stockage
La couche de stockage de données stocke principalement les données sur le système de fichiers et complète l'interaction avec le moteur de stockage. Par rapport à d'autres bases de données, MySQL est un peu différente. Son architecture peut être appliquée et fonctionner correctement dans de nombreux scénarios différents. Principalement reflétée dans le moteur de stockage, l'architecture du moteur de stockage plug-in sépare le traitement des requêtes des autres tâches système ainsi que du stockage et de l'extraction des données. Cette architecture permet la sélection de moteurs de stockage appropriés en fonction des besoins métier et des besoins réels.

2. Composants du serveur MySQL

Chaque couche de la structure MySQL est composée d'une ou plusieurs parties :

Composants de la couche de connexion

Connecteurs : interface interactive

MySQL outward Fourni interactif les composants, tels que Java, .net, php et d'autres langages, peuvent utiliser ce composant pour faire fonctionner des instructions SQL et réaliser une interaction avec SQL

Composant de couche de service

Pool de connexions : composant de pool de connexions
Utiliser pour la gestion , mise en mémoire tampon des connexions utilisateur, traitement des threads et autres exigences nécessitant une mise en cache

Services et utilitaires de gestion : services de gestion et composants d'outils
Utilisés pour les outils de gestion et de contrôle du système, tels que la sauvegarde et la récupération, la réplication MySQL, le clustering, la gestion de la sécurité , etc.

Interface SQL : composant d'interface SQL
Utilisé pour accepter les commandes SQL des utilisateurs, telles que DML, DDL et procédures stockées, etc., et renvoyer les résultats finaux à l'utilisateur

Parser : composant analyseur de requêtes
Les commandes SQL sont transmises à Il sera vérifié et analysé par l'analyseur. Il analyse d'abord la légalité de la syntaxe de la commande SQL et essaie de décomposer la commande SQL en une structure de données. Si la décomposition échoue, il demandera que le SQL. l'instruction est déraisonnable

Optimiseur : composant optimiseur
L'instruction SQL utilisera l'optimiseur de requête pour optimiser la requête avant l'interrogation.
Par exemple : sélectionnez l'identifiant, le nom de l'utilisateur où l'âge = 20 ;

Cette requête de sélection sélectionne d'abord en fonction de l'instruction Where, au lieu d'interroger d'abord toutes les tables, puis de filtrer l'âge
Cette requête de sélection sélectionne d'abord en fonction de l'identifiant et nom Effectuez une projection d'attributs au lieu de supprimer tous les attributs puis de filtrer
Connectez ces deux conditions de requête pour générer le résultat final de la requête

Caches et tampons : composant de pool de tampons
Si le cache de requête a un résultat de requête réussi , L'instruction de requête peut récupérer directement les données du cache de requêtes. Ce mécanisme de mise en cache est composé d'une série de petits caches. Par exemple, le cache de table, le cache d'enregistrement, le cache de clé, le cache d'autorisation, etc.

Composants de la couche moteur

Moteurs de stockage enfichables : moteurs de stockage
Les moteurs de stockage sont la mise en œuvre de technologies telles que le stockage de données, la création d'index, la mise à jour des données de requête, etc. Le moteur de stockage est basé sur des tables et non sur des bibliothèques. Les moteurs de stockage peuvent donc également être appelés types de tables. La création de tables, le stockage de données, la récupération, les mises à jour, etc. sont tous effectués par le moteur de stockage MySQL, ce qui constitue également le rôle important que joue le moteur de stockage MySQL dans MySQL.

MYSQL fournit une architecture de moteur de stockage plug-in, afin que les utilisateurs puissent choisir différents moteurs de stockage pour les tables de données en fonction de différents besoins. Les utilisateurs peuvent également écrire leurs propres moteurs de stockage ou écrire des moteurs de stockage en fonction de leurs propres besoins. Même différentes tables dans une bibliothèque utilisant différents moteurs de stockage sont autorisées.

Composants de la couche de stockage

Système de fichiers : système de fichiers

Un système utilisé pour stocker réellement les fichiers de base de données MySQL et certains fichiers journaux, etc. Les plus courants incluent Linux, Windows, etc.

3. Moteur de stockage

Différences dans les moteurs de stockage couramment utilisés

Avant la version 5.5 de MySQL, le moteur de stockage par défaut était MyISAM, fourni avec MySQL. Après la version 5.5, le moteur de stockage par défaut a été remplacé par InnoDB, développé par une société tierce pour MySQL. Pourquoi le changer ?

La raison principale est qu'InnoDB prend en charge les transactions et les verrous au niveau des lignes, ce qui est plus adapté aux scénarios ayant des exigences élevées en matière de cohérence commerciale.

Instructions de création de tables pour différents moteurs de stockage :

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

Copier après la connexion

InnoDB
Le moteur de stockage InnoDB est aujourd'hui le moteur de stockage par défaut de MySQL. Le moteur de stockage InnoDB assure la sécurité des transactions avec des capacités de validation, de restauration et de récupération après incident. Cependant, par rapport au moteur de stockage de MyISAM, l'efficacité du traitement d'écriture d'InnoDB est moins efficace et prend plus d'espace disque pour conserver les données et les index.

Les caractéristiques du moteur de stockage InnoDB sont différentes des autres moteurs de stockage :

Contrôle des transactions
Contraintes de clés étrangères

InnoDB propose les deux manières suivantes de stocker des tables et des index :

Utiliser le stockage d'espace de table partagé, créé de cette manière La structure de la table est enregistrée dans le fichier .frm et les données et index sont enregistrés dans l'espace table défini par innodb_data_home_dir et innodb_data_file_path, qui peuvent être plusieurs fichiers.
En utilisant le stockage d'espace multi-table, la structure de la table ainsi créée existe toujours dans le fichier .frm, mais les données et les index de chaque table sont enregistrés séparément dans .ibd.

InnoDB Environnement d'utilisation approprié :
InnoDB est le moteur de stockage par défaut de MySQL, utilisé pour les applications de traitement des transactions et prend en charge les clés étrangères. Si l'application a des exigences relativement élevées en matière d'intégrité des transactions et nécessite une cohérence des données dans des conditions simultanées, et que les opérations de données incluent de nombreuses opérations de mise à jour et de suppression en plus de l'insertion et de la requête, alors le moteur de stockage InnoDB est un choix plus approprié.

En plus de réduire efficacement les blocages causés par les suppressions et les mises à jour, le moteur de stockage InnoDB peut également garantir une soumission et une annulation complètes des transactions. Pour les systèmes ayant des exigences élevées en matière de précision des données, tels que les systèmes de facturation ou les systèmes financiers, InnoDB est le choix le plus approprié.

MyISAM
MyISAM ne prend pas en charge les transactions ou les clés étrangères. Son avantage est qu'il a une vitesse d'accès rapide et n'a aucune exigence concernant l'intégrité des transactions ou que les applications basées sur SELECT et INSERT peuvent essentiellement être créées à l'aide de cette surface.

possède les deux caractéristiques importantes suivantes :

Ne prend pas en charge les transactions
Méthode de stockage de fichiers

Chaque MyISAM est stocké sous forme de 3 fichiers sur le disque, les noms de fichiers sont les mêmes que les noms de tables, mais les extensions sont différent Il s'agit de :

.frm (définition de la table de stockage) ;
.MYD (MYData, données de stockage) ;
.MYI (MYIndex, index de stockage) ;

Environnement d'utilisation approprié MyISAM :
Si l'application est read Principalement des opérations et des opérations d'insertion, il n'y a que quelques opérations de mise à jour et de suppression, et les exigences en matière d'intégrité et de concurrence des transactions ne sont pas très élevées, le choix de ce moteur de stockage est donc très approprié.

MEMORY
Le moteur de stockage en mémoire stocke les données de la table en mémoire. Chaque table MEMORY correspond en fait à un fichier disque au format .frm. Ce fichier stocke uniquement la structure de la table, et ses fichiers de données sont stockés en mémoire. Cela favorise un traitement rapide des données et améliore l'efficacité de l'ensemble de la table. .

L'accès aux tables de type MEMORY est très rapide car ses données sont stockées en mémoire et utilisent l'index HASH par défaut. Cependant, une fois le service arrêté, les données de la table seront perdues.

MEMOIRE Environnement d'utilisation approprié :
Enregistrez toutes les données dans la RAM, ce qui peut fournir plusieurs blocs d'accès lorsque des enregistrements de localisation rapides et d'autres environnements de données similaires sont requis.

L'inconvénient de MEMORY est qu'il existe une limite sur la taille de la table. Les tables trop volumineuses ne peuvent pas être mises en cache en mémoire. Deuxièmement, il est nécessaire de s'assurer que les données de la table peuvent être restaurées. la table peut être restaurée après une fermeture anormale de la base de données. Les tables MEMORY sont généralement utilisées pour les petites tables qui sont mises à jour moins fréquemment afin d'obtenir rapidement les résultats d'accès.

MERGE
Le moteur de stockage MERGE est une combinaison d'un ensemble de tables MyISAM. Ces tables MyISAM doivent avoir la même structure. La table MERGE elle-même ne stocke pas de données. Les opérations de requête, de mise à jour et de suppression peuvent être effectuées sur le type MERGE. Ces opérations sont en réalité effectuées sur la table interne MyISAM.

Pour l'opération d'insertion de la table de type MERGE, la table insérée est définie via la clause INSERT_METHOD, qui peut avoir trois valeurs différentes. L'utilisation de la valeur FIRST ou LAST entraîne l'application de l'opération d'insertion à la première ou à la dernière table en conséquence. Définissez cette clause ou définissez-la comme NO, indiquant que l'opération d'insertion ne peut pas être effectuée sur cette table MERGE.

Vous pouvez effectuer une opération DROP sur la table MERGE, mais cette opération supprime uniquement la définition de la table MERGE et n'a aucun impact sur les tables internes.

MERGE Environnement d'utilisation approprié :
Utilisé pour combiner logiquement une série de tables MyISAM équivalentes et les référencer en tant qu'objet.

L'avantage de la table MERGE est qu'elle peut dépasser la limite de taille d'une seule table MyISAM, et en distribuant différentes tables sur plusieurs disques, l'efficacité d'accès de la table MERGE peut être efficacement améliorée. Ceci est très approprié pour les environnements VLDB tels que l'entreposage de données.

Apprentissage recommandé : Tutoriel vidéo 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!