Apprentissage du Big Data, partie 2 MYSQL Advanced

Répertoire d'articles

• 5 Architecture MySQL
• • 5.1 MyISAM
  • 5.2 Innodb
  • • 5.2.1 Comment choisir entre l'espace table système et l'espace table indépendant
    • 5.2.2 Fonctionnalités du moteur de stockage Innodb
    • 5.2.3 Vérification de l'état d'Innodb
    • 5.2.4 Scénarios applicables
    • 5.2.4 (étendu) Qu'est-ce qu'un verrou
    • • 5.2.3.1 Qu'est-ce qu'un verrou une serrure ?
      • 5.2.3.2 Type de verrou :
      • 5.2.3.3 Relation de compatibilité entre le verrou en écriture et le verrou en lecture (compatibilité pour une ligne)
      • 5.2.3.4 Granularité du verrou :
      • 5.2.3.5 Blocage et blocage
  • 5.3 Moteur de stockage CSV
  • • 5.3. Caractéristiques du stockage CSV. moteur
    • 5.3.2 Scénarios applicables du moteur de stockage CSV
  • 5.4 Moteur de stockage d'archives
  • • 5.4 .1 Stockage du système de fichiers caractéristiques
    • 5.4.2 Caractéristiques du moteur de stockage d'archives
    • 5.4.3 Scénarios d'utilisation du 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
    • 5.5.2 Caractéristiques fonctionnelles de la mémoire
    • 5.5.3 Concepts facilement confus en mémoire
    • 5.5.4 Mémoire scénarios d'utilisation
  • 5.6 Moteur de stockage fédéré
  • • 5.6.1 Fonctionnalités fédérées
    • 5.6.2 Comment utiliser Federated
    • 5.6.3 Scénarios applicables de fédéré
• 6 Comment choisir le bon moteur de stockage

Suite de l'article précédent

5 Architecture MySQL

Ci-dessous, nous sélectionnons certains des stockages les plus couramment utilisés Une brève explication du moteur .Le moteur de stockage utilisé par MySQL aura un impact direct sur les performances de la base de données. J'espère également que vous pourrez bien comprendre certaines des caractéristiques du moteur de stockage avant d'utiliser le moteur de stockage.

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 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 les 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 indépendamment, 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 MySQL 5.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 l'espace

Comparaison :

系统表空间	独立表空间
无法简单的收缩文件大小	可以通过optimize table命令收缩系统文件
会产生IO瓶颈	可以同时向多个文件刷新数据

Recommandation :

Utilisation pour l'espace table indépendant Innodb

Une méthode pour transférer des tables qui existaient à l'origine dans l'espace table système vers un espace table indépendant.

Étapes :

Utilisez mysqldump pour exporter toutes les données des tables de base de données
1. Arrêtez le service MySQL, modifiez les paramètres et supprimez les fichiers liés à Innodb
Redémarrez le service MySQL et reconstruisez l'espace table système Innodb
3. 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 ?

Le rôle principal 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 :

Verrou partagé (également appelé verrou en lecture)
• Verrou exclusif (également appelé verrou en écriture)

5.2.3.3 Compatibilité entre le verrouillage en écriture et le verrouillage en lecture (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 le verrouillage d'une autre transaction. être libérée. Les ressources qu’elle 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 sera 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
• La 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 reproductibles.

5.6 Moteur de stockage fédéré

5.6.1 Fonctionnalités du

• Fournit un accès aux serveurs MySQL distants Tableau méthode
  Étant donné que 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, il 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 de la lenteur des performances 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.

Plus de recommandations d'apprentissage gratuites associées : tutoriel mysql(vidéo)

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!