Introduction détaillée à la théorie MySQL et aux connaissances de base

Cet article vous donnera une introduction détaillée à la théorie de MySQL et aux connaissances de base. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer. J'espère qu'il sera utile à tout le monde.

Architecture mysql

1. Couche de connexion réseau

Connecteurs clients : support intégré fourni pour MySQL serveur. Actuellement, presque toutes les technologies de programmation côté serveur traditionnelles sont prises en charge, telles que Java, C, Python, .NET, etc., qui établissent des connexions avec MySQL via leurs technologies API respectives

2. Service couche (MySQL Server)

La couche de service est le cœur de MySQL Server et comprend principalement six parties : outils de gestion et de contrôle du système, pool de connexions, interface SQL, analyseur, optimiseur de requêtes et cache.

Pool de connexions : responsable du stockage et de la gestion de la connexion entre le client et la base de données. Un thread est responsable de la gestion d'une connexion.

Outils de gestion et de contrôle du système (Management Services & Utilities) : tels que la sauvegarde et la restauration, la gestion de la sécurité, la gestion des clusters, etc.

Interface SQL : utilisée pour accepter diverses données envoyées par le client Commande SQL et renvoie les résultats que l'utilisateur doit interroger. Tels que DML, DDL, procédures stockées, vues, déclencheurs, etc.

Parseur : Responsable de l'analyse du SQL demandé pour générer un "arbre d'analyse". Vérifiez ensuite si l'arbre d'analyse est légal selon certaines règles MySQL.

Optimiseur de requête (Optimiseur) : lorsque "l'arbre d'analyse" réussit la vérification de la syntaxe de l'analyseur, il sera remis à l'optimiseur pour le convertir en plan d'exécution, puis interagir avec le moteur de stockage.

sélectionnez l'uid, le nom de l'utilisateur où le sexe = 1 ;

Sélectionnez-》Projection-》Stratégie de jointure

1) Sélectionnez d'abord les sélections en fonction de l'instruction Where, pas d'une requête Extrayez toutes les données puis filtrez

2) La requête de sélection effectue une projection d'attribut basée sur l'uid et le nom, pas sur tous les champs

3) Connectez la sélection et la projection précédentes pour enfin générer le résultat de la requête

Cache(Cache&Buffer) : Le 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 d'autorisations, le cache de moteur, etc. Si le cache de requêtes 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.

3. Moteurs de stockage enfichables

Le moteur de stockage est responsable du stockage et de l'extraction des données dans MySQL et interagit avec les fichiers système sous-jacents. Le moteur de stockage MySQL est un plug-in. Le moteur d'exécution des requêtes sur le serveur communique avec le moteur de stockage via une interface qui masque les différences entre les différents moteurs de stockage. Il existe désormais de nombreux moteurs de stockage, chacun avec ses propres caractéristiques. Les plus courants sont MyISAM et InnoDB.

4. Système de fichiers

Cette couche est responsable du stockage des données de la base de données et des journaux sur le système de fichiers et de l'interaction avec le moteur de stockage, est la couche de stockage physique. de fichiers. Comprend principalement les fichiers journaux, les fichiers de données, les fichiers de configuration, les fichiers pid, les fichiers socket, etc.

Fichier journal

Journal des erreurs (Journal des erreurs)

Activé par défaut, afficher des variables telles que '%log_error%'

Journal des requêtes générales (Journal des requêtes générales)

Enregistre les instructions de requête générales, affiche des variables telles que '%general%';

Journal binaire (journal binaire)

Enregistre les modifications effectuées sur la base de données MySQL opération, et enregistre l'heure d'occurrence et l'heure d'exécution de l'instruction ; cependant, il n'enregistre pas select, show, etc. SQL qui ne modifie pas la base de données. Principalement utilisé pour la récupération de bases de données et la réplication maître-esclave.

afficher les variables comme '%log_bin%' ; //S'il faut activer

afficher les variables comme '%binlog%' ; //Vue des paramètres

afficher les journaux binaires ;/ / Afficher le fichier journal Journal des requêtes lentes

Journal des requêtes lentes

Enregistre toutes les requêtes SQL dont le temps d'exécution a expiré. La valeur par défaut est de 10 secondes.

afficher les variables comme '%slow_query%' ; //Activer ou non

afficher les variables comme '%long_query_time%' ; //Durée

Fichier de configuration

Utilisé pour stocker tous les fichiers d'informations de configuration MySQL, tels que my.cnf, my.ini, etc.

Fichier de données

fichier db.opt : enregistre le jeu de caractères par défaut et les règles de vérification utilisées par cette bibliothèque.

fichier frm : stocke les informations de métadonnées (méta) liées à la table, y compris les informations de définition de la structure de la table, etc. Chaque table aura un fichier frm.

Fichier MYD : Il est dédié au moteur de stockage MyISAM et stocke les données de la table MyISAM. Chaque table aura un fichier .MYD.

Fichier MYI : dédié au moteur de stockage MyISAM, qui stocke les informations liées à l'index de la table MyISAM. Chaque table MyISAM correspond à un fichier .MYI.

fichier ibd et fichier IBDATA : stockez les fichiers de données InnoDB (y compris les index). Le moteur de stockage InnoDB dispose de deux modes d'espace table : espace table exclusif et espace table partagé. Les espaces table exclusifs utilisent des fichiers .ibd pour stocker les données, et chaque table InnoDB correspond à un fichier .ibd. Les espaces table partagés utilisent des fichiers .ibdata et toutes les tables utilisent un (ou plusieurs fichiers .ibdata auto-configurés).

fichier ibdata1 : fichier de données de l'espace table système, qui stocke les métadonnées de la table, les journaux d'annulation, etc.

Fichiers ib_logfile0, ib_logfile1 : refaire les fichiers journaux.

fichier pid

Le fichier pid est un fichier de processus de l'application mysqld dans l'environnement Unix/Linux. Comme beaucoup d'autres programmes serveur Unix/Linux, il stocke son propre identifiant de processus.

fichier socket

le fichier socket est également disponible dans l'environnement Unix/Linux. Les utilisateurs peuvent directement utiliser Unix Socket pour connecter le client dans un environnement Unix/Linux sans passer par le réseau TCP/IP. .

InnoDB et MyISAM

Transactions et clés étrangères

InnoDB prend en charge les transactions et les clés étrangères, avec sécurité et intégrité, adaptées à un grand nombre d'opérations d'insertion ou de mise à jour

MyISAM ne prend pas en charge les transactions et les clés étrangères. Il fournit un stockage et une récupération à grande vitesse, adaptés à un grand nombre d'opérations de requête sélectionnées

Mécanisme de verrouillage.

InnoDB prend en charge le verrouillage au niveau des lignes et verrouille les enregistrements spécifiés. Le verrouillage est implémenté en fonction de l'index.

MyISAM prend en charge le verrouillage au niveau de la table, verrouillant ainsi la table entière.

Structure de l'index

InnoDB utilise un index clusterisé (index clusterisé). L'index et les enregistrements sont stockés ensemble, mettant en cache à la fois l'index et les enregistrements.

MyISAM utilise un index non clusterisé (index non clusterisé), et l'index et l'enregistrement sont séparés.

Capacité de traitement simultané

MyISAM utilise des verrous de table, ce qui entraînera un faible taux de concurrence des opérations d'écriture, aucun blocage entre les lectures et le blocage des lectures et des écritures.

Le blocage de lecture et d'écriture d'InnoDB peut être lié au niveau d'isolement, et le contrôle de concurrence multi-versions (MVCC) peut être utilisé pour prendre en charge une concurrence élevée

Fichiers de stockage

InnoDB La table correspond à deux fichiers, un fichier de structure de table .frm et un fichier de données .ibd. La table InnoDB prend en charge jusqu'à 64 To ;

La table MyISAM correspond à trois fichiers, un fichier de structure de table .frm, un fichier de données de table MYD et un fichier d'index .MYI. À partir de MySQL 5.0, la limite par défaut est de 256 To.

La différence entre Redo Log et Binlog

Redo Log est une fonction du moteur InnoDB, tandis que Binlog est une fonction intégrée de MySQL Server et est enregistré dans des fichiers binaires.

Redo Log est un journal physique qui enregistre le contenu de l'état de mise à jour de la page de données. Binlog est un journal logique qui enregistre le processus de mise à jour.

Le journal Redo Log est écrit en boucle, la taille de l'espace de journal est fixe, Binlog est écrit en plus, après l'écriture d'un journal, le suivant est écrit et il ne sera pas écrasé.

Redo Log peut être utilisé pour restaurer automatiquement les données de transaction après une panne anormale du serveur. Binlog peut être utilisé pour la réplication maître-esclave et la récupération de données. Binlog ne dispose pas de capacités automatiques de sécurité contre les crashs.

Dans l'application, plusieurs index peuvent être ajoutés à la base de données esclave pour optimiser les requêtes. Ces index dans la base de données principale peuvent être omis pour améliorer l'efficacité d'écriture.

Schéma de séparation en lecture et en écriture

1 Lire immédiatement après l'écriture

Après l'écriture dans la base de données, l'opération de lecture se produira dans un délai certaine période de temps Accédez à la bibliothèque principale, puis les opérations de lecture accèdent à la bibliothèque esclave.

2 Requête secondaire

Lisez d'abord les données de la base de données esclave. Si elles ne sont pas trouvées, accédez à la base de données principale pour lire les données. Cette opération renverra facilement la pression de lecture à la bibliothèque principale. Afin d'éviter les attaques malveillantes, il est recommandé d'encapsuler les opérations de l'API d'accès à la base de données, ce qui est bénéfique pour la sécurité et un faible couplage.

3 Traitements spéciaux selon l'entreprise

Ajuster en fonction des caractéristiques et de l'importance de l'entreprise Par exemple, la lecture et l'écriture des données commerciales importantes et en temps réel peuvent être placées dans la base de données principale. Pour les activités secondaires qui ne nécessitent pas de performances élevées en temps réel, la lecture et l'écriture peuvent être séparées et des requêtes peuvent être effectuées à partir de la base de données lors de l'interrogation.

Recommandations associées : "Tutoriel 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!