Le deuxième article présentant MYSQL avancé

Recommandation d'apprentissage gratuite : Tutoriel vidéo MySQL

5 Architecture MySQL

Ci-dessous, nous sélectionnons certains des moteurs de stockage les plus couramment utilisés pour une brève explication. J'espère que le moteur de stockage utilisé par MySQL aura un impact direct sur les performances de la base de données. vous y ferez attention. Comprenez certaines des caractéristiques du moteur de stockage et n'utilisez le moteur de stockage qu'après avoir terminé.

5.1 MyISAM

MyISAM était le moteur de stockage par défaut avant MySQL5.5. Pour cette raison, de nombreux serveurs utilisent encore le moteur de stockage MyISAM. Dans le même temps, MyISAM est actuellement le moteur de stockage utilisé par de nombreuses tables système et tables temporaires. Les tables temporaires mentionnées ici ne sont pas les tables que nous créons via create table. Elles font référence lorsque le nombre dépasse une certaine taille lors d'opérations telles que. tri et regroupement , il existe des tables temporaires créées par l'optimiseur de requêtes.
Le moteur de stockage MyISAM est composé de MYD et MYI est l'extension du fichier de données et MYI est l'extension du fichier d'index. Ce moteur de stockage stocke les tables dans les fichiers de données et les fichiers d'index avec ces deux extensions.

Caractéristiques :

• Concurrence et niveau de verrouillage
  MyISAM utilise des verrous au niveau de la table, et non des verrous au niveau de la ligne, ce qui signifie que les données de la table sont lors de la modification, le la table entière doit être verrouillée, et lors de la lecture de la table, des verrous partagés sont également ajoutés à toutes les tables. De là, nous pouvons voir que les opérations de lecture et d'écriture de table utilisant MyISAM comme moteur s'excluent mutuellement. On peut voir que MyISAM n'est pas très doué pour les opérations de lecture et d'écriture simultanées. S'il s'agit uniquement d'opérations en lecture seule, les performances ne sont pas mauvaises en termes de concurrence car le verrou partagé ne bloque pas le verrou partagé.
• Réparation des dommages aux tables
  MyISAM prend en charge la vérification et la réparation des tables MyISAM qui sont endommagées en raison d'un arrêt inattendu, mais la réparation mentionnée ici n'est pas une récupération de données, car MyISAM n'est pas un moteur de stockage transactionnel, il ne peut donc pas effectuer journaux associés requis pour la récupération des transactions, sachez donc que la récupération des tables MyISAM peut entraîner une perte de données.
  Nous pouvons vérifier la table via check table tablename et restaurer la table via repair table tablename.
• Types d'index pris en charge par les tables MyISAM
  MyISAM prend en charge l'indexation en texte intégral et était le seul moteur de stockage officiel prenant en charge nativement l'indexation en texte intégral avant mysql5.7.
• La table MyISAM prend en charge la compression des données
  Si MyISAM représente une grande table en lecture seule, c'est-à-dire qu'une fois la table créée et les données importées, aucune modification ne sera apportée à la table, alors nous le ferons Les tables peuvent être compressées pour réduire les E/S disque. Nous pouvons utiliser la commande myisampack pour compresser le tableau. La compression compresse la table de manière indépendante, donc lors de la lecture d'une ligne de données, il n'est pas nécessaire de décompresser la table entière.

Restrictions :

• Version
• Si vous stockez une grande table, vous devez la modifier MAX_Rows et AVG_ROW_LENGTH
• Version> mysql5.0 prend en charge 256 To par défaut

Scénarios applicables :

• Applications non transactionnelles
• Lire -applications uniquement (rapports et autres)
• Applications spatiales

5.2 Innodb

Innodb est le moteur de stockage par défaut pour MySQL5.5 et les versions ultérieures Innodb est un moteur de stockage pour le stockage transactionnel, ce qui signifie qu'il prend en charge le traitement des transactions.
Innodb a son propre concept d'espace table, et les données sont stockées dans l'espace table, qui est déterminé par le paramètre innodb_file_per_table Si ce paramètre est ON, chaque table Innodb sera créée Un fichier système avec. une extension de ibd. Si ce paramètre est OFF, les données seront stockées dans l'espace table partagé du système, c'est-à-dire que ibdataX représente un nombre, commençant par 1 par défaut. X La commande pour visualiser ce paramètre est :

show variables like 'innodb_file_per_table'; La commande pour modifier ce paramètre est :

set global innodb_file_per_table=off;

5.2.1 Espace table système et table indépendante Comment choisir un espace

Comparaison :

Recommandation :

• Utiliser un espace table indépendant pour Innodb

Transférer les tables qui existaient à l'origine dans l'espace table système vers un espace table indépendant méthode de l'espace table dans.
Étapes :

1. Utilisez mysqldump pour exporter toutes les données des tables de base de données
2. Arrêtez le service MySQL, modifiez les paramètres et supprimez les fichiers liés à Innodb
3. Redémarrez le service MySQL et reconstruisez l'espace table système Innodb
4. Réimportez les données

5.2.2 Fonctionnalités du moteur de stockage Innodb

• Innodb est un moteur de stockage transactionnel
• Prend entièrement en charge les caractéristiques ACID des transactions (atomicité, cohérence, etc. introduites précédemment)
• Redo Log et Undo Log
  Redo Implémentation du journal La durabilité de la transaction est composée de deux parties, dont l'une est le tampon persistant du journal de travail en mémoire, dont la taille est déterminée par innodb_log_buffer_size, et l'autre est le fichier journal reconstruit, qui est le ib_logflie que nous voyons dans le fichier. documents relatifs au système. Undo Log réalise l'atomicité des transactions et effectue des opérations de restauration en cas d'échec des transactions. Redo Log est lu et écrit de manière séquentielle, et Undo Log est lu et écrit de manière aléatoire. Si possible, les données peuvent être stockées sur un disque SSD pour améliorer les performances.
• Innodb prend en charge les verrous au niveau de la ligne
  Les verrous au niveau de la ligne et les verrous au niveau de la table sont différents. La caractéristique des verrous au niveau de la ligne est qu'ils peuvent prendre en charge la concurrence dans la plus grande mesure. implémenté par la couche moteur de stockage.

5.2.3 Vérification de l'état Innodb

Vous pouvez utiliser la commande suivante pour vérifier l'état Innodb :
show engine innodb status

5.2 .4 Scénarios applicables

Innodb convient à la plupart des applications OLTP, car après la version mysql5.7, Innodb prend déjà en charge l'index de texte intégral et les fonctions spatiales.

5.2.4 (Étendu) Qu'est-ce qu'un verrou

5.2.3.1 Qu'est-ce qu'un verrou ?

• La fonction principale des verrous est de gérer l'accès simultané aux ressources partagées
• Les verrous sont utilisés pour réaliser l'isolation des transactions

5.2.3.2 Types de verrous :

• Verrouillage partagé (également appelé verrouillage en lecture)
• Verrouillage exclusif (également appelé verrouillage en écriture)

5.2.3.3 Compatibilité du verrouillage en écriture et en lecture lock Relation (situation de compatibilité pour une ligne)

	写锁	读锁
写锁	不兼容	不兼容
读锁	不兼容	兼容

Verrouillage en écriture

Verrouillage en lecture

Verrouillage en écriture

Incompatible

Incompatible

Verrouillage en lecture

Incompatible

Compatible

Dans les situations réelles, les résultats peuvent être différents du tableau ci-dessus, principalement parce que le mécanisme de verrouillage dans Innodb est une chose très complexe et qu'il existe de nombreux verrous qui affectent le résultat final.

5.2.3.4 Granularité du verrouillage :

• Verrouillage au niveau de la table
• Verrouillage au niveau de la ligne

5.2.3.5 Blocage et blocage

Blocage : le blocage est dû à la relation de compatibilité entre différents verrous. À un moment donné, le verrou d'une transaction doit attendre que le verrou d'une autre transaction soit libéré, et les ressources qu'il occupe forment un blocage.
Deadlock : Deadlock fait référence à une exception qui se produit lorsque deux transactions ou plus occupent les ressources que l'autre partie attend lors de l'exécution de la transaction. Comme le montre la définition, plusieurs transactions bloquées occupent les ressources qu'attend la transaction bloquée, et l'impasse se produit lorsque plusieurs transactions bloquées occupent les ressources que l'autre attend.

5.3 Moteur de stockage CSV

Le moteur de stockage CSV peut traiter les fichiers CSV comme des fichiers de table MySQL. Le format de stockage de ce moteur de stockage est celui des fichiers CSV ordinaires. du moteur de stockage CSV est tout à fait unique. Si nous stockons la table dans MyISAM ou Innodb, nous ne pouvons pas visualiser directement les fichiers de données car ces deux fichiers sont stockés au format binaire. Le moteur de stockage CSV est différent dans des fichiers. sous forme de texte, c'est-à-dire que nous pouvons le visualiser via la commande pour afficher le fichier, comme plus, ou utiliser la commande vi pour afficher et modifier le tableau dans le moteur de stockage CSV, à condition que le format et les exigences du fichier soient respectés. Les fichiers CSV sont remplis, nous n'avons pas à nous soucier de corrompre les données.
Lorsque nous créons la table du moteur de stockage CSV dans MySQL, nous devrions pouvoir voir les fichiers dans les 3 systèmes de fichiers. Ces trois noms de fichiers sont tous basés sur le nom de la table, mais ont respectivement les suffixes csv, csm et frm. Le fichier csv est le fichier de données du moteur de stockage CSV. Le fichier csm stocke les métadonnées de la table ainsi que l'état de la table et le volume de données. Le fichier FRM stocke les informations sur la structure de la table.

5.3.1 Fonctionnalités du moteur de stockage CSV

• La plus grande fonctionnalité est que les données sont stockées au format CSV
  Chaque colonne dans CSV est , pour séparer, et le contenu du texte est placé entre guillemets doubles, comme indiqué ci-dessous :
  
• Toutes les colonnes ne doivent pas être NULL
  dans Lors de la création d'un tableau, toutes les colonnes ne doivent pas être NULL -vide et ne peut pas être stocké sous forme de valeurs NULL
• Ne prend pas en charge les index
  Ne convient pas aux grandes tables et au traitement en ligne
• Peut être utilisé pour modifier des fichiers de données Modifier directement
  Enregistrer le contenu du fichier texte

5.3.2 Scénarios applicables du moteur de stockage CSV

Le moteur de stockage CSV convient comme table intermédiaire pour l'échange de données

5.4 Moteur de stockage d'archives

5.4.1 Fonctionnalités de stockage du système de fichiers

Archive Le moteur de stockage met en cache toutes les écritures et utilise zlib pour compresser les lignes insérées. Par conséquent, le moteur de stockage d'archive enregistre les E/S de disque par rapport aux tables du moteur de stockage MyISAM. Pour le même ordre de grandeur, le moteur de stockage d'archive enregistre les E/S de disque par rapport aux tables. du moteur de stockage MyISAM et Innodb économisent plus d'espace de stockage. Une table Innodb de plusieurs téraoctets stockée dans le moteur de stockage Archive peut nécessiter seulement quelques centaines de mégaoctets d'espace de stockage.
Les données de la table du moteur de stockage Archive sont un fichier avec le suffixe ARZ Comme les autres moteurs, il existe également un fichier système avec le suffixe frm utilisé pour stocker les informations structurelles de la table.

5.4.2 Fonctionnalités du moteur de stockage d'archives

• ne prend en charge que les opérations insert et select
• n'est autorisée qu'en mode automatique. incrément Ajoutez un index à la colonne ID

5.4.3 Scénarios d'utilisation du moteur de stockage d'archives

Scénario 1 : Données de journalisation et de collecte de données
Parce qu'Archive ne le prend pas en charge Modification et suppression, et notre ORDB modifiera certainement les données, mais il est toujours utile pour certaines applications de type entrepôt ou certaines tables spéciales, telles que les tables d'enregistrement de journaux ou les tables de collecte de données, car il s'agit d'une grande quantité de données doit être collecté, le moteur de stockage Archive est donc plus adapté. Étant donné que le moteur de stockage Archive dispose du plus petit espace de stockage parmi tous les moteurs, il convient de noter que même dans les applications de collecte de données ou de journalisation, le moteur de stockage Archive ne peut pas mettre à jour ces données, donc lors de l'enregistrement des journaux ou si vous modifiez les données dans une collection de données. application, vous ne pourrez peut-être pas utiliser le moteur de stockage d'archives.

5.5 Moteur de stockage de mémoire

5.5.1 Caractéristiques de stockage du système de fichiers

Le moteur de stockage en mémoire est également appelé moteur de stockage HEAP, les données sont donc stockées en mémoire. Cela signifie que la table de données est jetable. Une fois le service MySQL redémarré, toutes les données du moteur de stockage en mémoire disparaîtront. sera conservé, car la création d'une table sous le moteur de stockage mémoire générera uniquement un fichier système frm, qui est utilisé pour enregistrer la structure de la table. C'est pourquoi les données seront perdues lors du redémarrage du serveur MySQL, mais pas la structure des tables.
D'après ses caractéristiques de stockage de fichiers, nous pouvons savoir que l'efficacité des E/S du moteur de stockage mémoire sera bien supérieure à celle de MyISAM, car seul l'index de MyISAM est stocké dans la mémoire et les données sont mises en cache par le système d'exploitation, tandis que le moteur de stockage mémoire Toutes les données et index du moteur sont stockés en mémoire. Jetons un coup d'œil aux caractéristiques fonctionnelles du moteur de stockage mémoire.

5.5.2 Fonctionnalités de mémoire

Caractéristiques :

• Prend en charge index HASH (par défaut) et Index BTree
  S'il s'agit d'un index HASH, il sera très rapide lors des requêtes équivalentes. S'il effectue des requêtes par plage, l'index HASH ne peut pas être utilisé, nous devons donc faire attention lors de la création de la table. Si la table nécessite un grand nombre de requêtes équivalentes, utilisez l'index HASH et utilisez l'index BTree pour les requêtes par plage. Différents types d'index peuvent avoir un impact important sur les performances.
• Tous les champs sont de longueur fixe varchar(10) = char(10)
  Cela nous oblige à respecter les exigences minimales de longueur de champ lors de la définition de la structure de la table, sinon beaucoup de mémoire est gaspillée.
• Ne prend pas en charge les champs volumineux tels que BLOG et TEXT
• Le moteur de stockage en mémoire utilise des verrous au niveau de la table
• La taille maximale est déterminée par le paramètre max_heap_table_size
  La valeur par défaut La valeur de ce paramètre n'est que de 16 Mo, si nous voulons stocker une grande quantité de données dans la table du moteur de stockage mémoire, nous devons modifier ce paramètre, et cette modification de paramètre ne prendra pas effet sur la table du moteur de stockage mémoire existante si. nous devons prendre effet sur la table existante, nous devons la modifier. Les tables existantes sont reconstruites.

5.5.3 Concepts confus en mémoire

Tableau des moteurs de stockage en mémoire :
Il peut être utilisé sur tous les systèmes, ce n'est pas le même A tableau temporaire.
Table temporaire :
Il existe deux types de tables temporaires. L'une est la table temporaire utilisée par le système utilisée par l'optimiseur de requêtes lors de l'optimisation des requêtes, qui est la table temporaire interne. La table temporaire utilisée par le système dépasse la limite. (en utilisant un grand champ BLOB ou TEXT), utilisez la table temporaire MyISAM et utilisez la table Mémoire si la limite n'est pas dépassée.
L'autre est une table temporaire créée via la commande create temporary table La table créée peut utiliser n'importe quel moteur de stockage.
Quel que soit le type de table temporaire, elle n'est visible qu'en interne.

5.5.4 Scénarios d'utilisation de la mémoire

• est utilisé pour les tables de recherche ou de mappage, telles que les tables de correspondance de codes postaux et de régions
• est utilisé pour Enregistrer la table intermédiaire générée lors de l'analyse des données
• Table de résultats utilisée pour mettre en cache les données d'agrégation périodique

Les données en mémoire sont faciles à perdre, les données doivent donc être reproductible.

5.6 Moteur de stockage fédéré

5.6.1 Fonctionnalités de Federated

• Fournit un accès à distance Méthodes pour les tables sur le serveur MySQL
  Puisque le moteur de stockage fédéré établit uniquement une connexion locale avec le serveur distant, on peut dire que toutes les tables auxquelles nous souhaitons accéder sont toujours placées sur le serveur distant et qu'aucune donnée n'est stockée localement. Chaque fois qu'une table du moteur de stockage fédéré est accédée, la requête est envoyée au serveur distant pour être exécutée et les données pertinentes sont obtenues à partir du serveur MySQL distant.
• Aucune donnée n'est stockée localement, toutes les données sont placées sur le serveur distant
• La structure de la table et les informations de connexion du serveur distant doivent être enregistrées localement
  Par conséquent, il y aura également être un fichier frm dans le système, utilisant des informations sur le stockage des informations distantes et sur la façon de se connecter aux tables distantes.

5.6.2 Comment utiliser Federated

Le moteur de stockage fédéré peut réaliser la fonction de connexion de SQL Server au serveur, mais en raison de ses propres performances n'est pas très bon, généralement le même objectif peut être atteint via la réplication, etc., donc dans la version actuelle de MySQL, le moteur de stockage fédéré est désactivé par défaut. Si vous devez utiliser le moteur de stockage fédéré, vous devez ajouter /usr/local/mysql/my.cnf à federated=1, puis redémarrer le serveur MySQL. Nous pouvons utiliser show engine pour confirmer si le serveur MySQL actuel prend en charge le moteur de stockage fédéré.
Et utilisez la chaîne de connexion suivante dans l'instruction create table,
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
connexion de liaison au serveur distant :
grant select,update,insert,delete on remote.remote_fet to fred_link@'127.0.0.1' identified by '123456'
pour déterminer la pertinence des informations interrogées sur le serveur distant et quelques informations sur les tables de base de données associées.

5.6.3 Scénarios applicables de Federated

• Analyse statistique occasionnelle et requête manuelle
  En raison des performances plus lentes de Federated, il ne convient que pour Analyse statistique occasionnelle et requête manuelle.

6 Comment choisir le bon moteur de stockage

Conditions de référence :

• Transactions
• Sauvegarde
• Récupération après incident
• Fonctionnalités uniques des moteurs de stockage
  Essayez d'éviter de mélanger les moteurs de stockage.

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!