Introduction détaillée au moteur de stockage MySQL

Qu'est-ce qu'un moteur de stockage ?

Un tableau de base de données relationnelle est une structure de données utilisée pour stocker et organiser des informations. Un tableau peut être compris comme un tableau composé de lignes et de colonnes, semblable à la forme d'une feuille de calcul Excel. Certaines tables sont simples, certaines sont complexes, certaines tables ne sont pas du tout utilisées pour stocker des données à long terme, et certaines tables sont très rapides à lire, mais très mauvaises lors de l'insertion de données dans le processus de développement réel, nous pouvons avoir besoin de divers ; des tables, des tables différentes, signifient que différents types de données sont stockés et qu'il y aura des différences dans le traitement des données. Pour MySQL, il fournit de nombreux types de moteurs de stockage. Nous pouvons choisir différents moteurs de stockage en fonction de nos besoins en traitement de données, afin de maximiser l'utilisation des puissantes fonctions de MySQL. Cet article de blog résumera et analysera les caractéristiques de chaque moteur et ses situations applicables, et ne s'impliquera pas dans des choses plus profondes. Ma méthode d'apprentissage consiste d'abord à apprendre à l'utiliser, à savoir comment l'utiliser, puis à découvrir comment l'utiliser. Ce qui suit est une brève introduction aux moteurs de stockage pris en charge par MySQL.

MySQL 5.5 et versions ultérieures utilisent le moteur de stockage InnoDB par défaut. InnoDB et BDB fournissent des tables sécurisées pour les transactions, et les autres moteurs de stockage sont des tables non sécurisées pour les transactions.

Pour modifier le moteur par défaut, vous pouvez modifier le moteur de stockage par défaut dans le fichier de configuration. Vous pouvez utiliser : showvariableslike'default_storage_engine'; pour afficher la base de données actuelle sur le moteur par défaut. Commandes : showengines et showvariableslike'have%' peuvent lister les moteurs supportés par la base de données actuelle. Les enregistrements dans lesquels la valeur est affichée comme désactivée indiquent que la base de données prend en charge ce moteur et est désactivée au démarrage de la base de données. Après MySQL 5.1, il existe une table ENGINES dans la base de données INFORMATION_SCHEMA. Les informations qu'elle fournit sont exactement les mêmes que celles de l'instruction showengines ; vous pouvez utiliser l'instruction suivante pour demander quels moteurs de stockage prennent en charge le traitement des transactions : selectenginefrominformation_chema.engineswheretransactions='yes'. ;

Vous pouvez utiliser le mot-clé moteur pour spécifier le moteur utilisé lors de la création ou de la modification de la base de données.

Principaux moteurs de stockage : MyISAM, InnoDB, MEMORY et MERGE Introduction :

Lors de la création d'une table, spécifiez le moteur à utiliser via engine=... ou type=.... showtablestatusfromDBname pour afficher la table spécifiée dans le moteur.

　(1) MyISAM

Il ne prend pas en charge les transactions ou les clés étrangères, en particulier les applications avec une vitesse d'accès rapide, aucune exigence d'intégrité des transactions ou principalement des applications SELECT et INSERT ne peuvent être créées à l'aide de cela. moteur.

Chaque MyISAM est stocké sous forme de 3 fichiers sur le disque. Le nom du fichier et le nom de la table sont les mêmes, mais les extensions sont :

.frm (définition de la table de stockage)

MYD (MYData, données de stockage)

MYI (MYIndex, index de stockage)

Les fichiers de données et les fichiers d'index peuvent être placés dans différents répertoires pour répartir uniformément les E/S et obtenir des vitesses plus rapides. Pour spécifier le chemin du fichier de données et du fichier d'index, vous devez le spécifier via les instructions DATADIRECTORY et INDEXDIRECTORY lors de la création de la table. Le chemin du fichier doit utiliser un chemin absolu.

Chaque table MyISAM a un indicateur qui est défini par le serveur ou le programme myisamchk lors de la vérification de la table de données MyISAM. La table MyISAM possède également un indicateur pour indiquer si la table de données a été fermée normalement après la dernière utilisation. Si le serveur est en panne ou en panne, cet indicateur peut être utilisé pour déterminer si la table de données doit être vérifiée et réparée. Si vous souhaitez que cette vérification se fasse automatiquement, vous pouvez utiliser le phénomène --myisam-recover lors du démarrage du serveur. Cela amènera le serveur à vérifier automatiquement les indicateurs de la table à chaque fois qu'il ouvrira une table de données MyISAM et effectuera les réparations nécessaires. Les tables de type MyISAM peuvent être endommagées. Vous pouvez utiliser l'instruction CHECKTABLE pour vérifier l'état de la table MyISAM et utiliser l'instruction REPAIRTABLE pour réparer une table MyISAM endommagée.

Les tables MyISAM prennent également en charge 3 formats de stockage différents :

Table statique (longueur fixe)

Table dynamique

Table compressée

　Statique table est le format de stockage par défaut. Les champs des tables statiques sont tous des champs de longueur non variable, donc chaque enregistrement a une longueur fixe. Les avantages de cette méthode de stockage sont qu'il est stocké très rapidement, qu'il est facile à mettre en cache et qu'il est facile de récupérer en cas d'échec. c'est qu'elle prend généralement plus de place qu'une table dynamique. Les tables statiques rempliront les espaces en fonction de la définition de largeur des définitions de colonnes lorsque les données sont stockées, mais ces espaces ne seront pas obtenus lors de l'accès. Ces espaces ont été supprimés avant d'être renvoyés à l'application. Dans le même temps, veuillez noter : dans certains cas, il peut être nécessaire de renvoyer des espaces après le champ, et lors de l'utilisation de ce format, les espaces suivants seront automatiquement traités.

Les tableaux dynamiques contiennent des champs de longueur variable et les enregistrements ne sont pas de longueur fixe. L'avantage d'un tel stockage est qu'il prend moins de place. Cependant, les mises à jour et suppressions fréquentes des enregistrements entraîneront une fragmentation. exécutez régulièrement l'instruction OPTIMIZETABLE ou la commande myisamchk-r. Améliore les performances et rend la récupération après des échecs relativement difficile.

La table compressée est créée par l'outil myisamchk et occupe un très petit espace. Étant donné que chaque enregistrement est compressé individuellement, il n'y a qu'une très petite surcharge d'accès.

　(2) InnoDB

InnoDB est un moteur de stockage transactionnel robuste. Ce moteur de stockage a été utilisé par de nombreuses sociétés Internet et fournit un outil puissant permettant aux utilisateurs d'exploiter une solution de stockage de données de très grande taille. MySQL version 5.6.13 est installé sur mon ordinateur et InnoDB est le moteur de stockage par défaut. InnoDB introduit également le verrouillage au niveau des lignes et les contraintes de clé étrangère Dans les situations suivantes, l'utilisation d'InnoDB est le choix le plus idéal :

　1. Mettre à jour les tables intensives. Le moteur de stockage InnoDB est particulièrement adapté à la gestion de plusieurs demandes de mise à jour simultanées.

　2. Affaires. Le moteur de stockage InnoDB est un moteur de stockage MySQL standard qui prend en charge les transactions.

　3. Reprise automatique après sinistre. Contrairement à d'autres moteurs de stockage, les tables InnoDB peuvent automatiquement récupérer après un sinistre.

4. Contraintes de clé étrangère. Le seul moteur de stockage prenant en charge les clés étrangères dans MySQL est InnoDB.

5. Prise en charge de l'ajout automatique de l'attribut AUTO_INCREMENT de colonne.

Le moteur de stockage InnoDB offre une 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.

1) Colonne à croissance automatique :

La colonne à croissance automatique de la table InnoDB peut être insérée manuellement, mais si la valeur insérée est vide ou 0, la valeur réellement insérée sera la valeur après croissance automatique. La valeur de départ de la valeur de croissance automatique peut être définie de force via l'instruction "ALTERTABLE...AUTO_INCREMENT=n;". La valeur par défaut est 1, mais la valeur par défaut forcée est stockée en mémoire et sera perdue après le redémarrage de la base de données. . Vous pouvez utiliser LAST_INSERT_ID() pour interroger la valeur utilisée par le thread actuel pour insérer l'enregistrement la dernière fois. Si plusieurs enregistrements sont insérés en même temps, la valeur d'incrémentation automatique utilisée par le premier enregistrement est renvoyée.

Pour les tables InnoDB, la colonne à croissance automatique doit être un index. S'il s'agit d'un index composite, il doit également s'agir de la première colonne de l'index composite. Cependant, pour les tables MyISAM, la colonne de croissance automatique peut être d'autres colonnes de l'index composite. De cette manière, après l'insertion de l'enregistrement, l'automatique. La colonne de croissance est incrémentée en fonction de l'indice de combinaison des colonnes précédentes.

2) Contraintes des clés étrangères :

Le seul moteur de stockage qui prend en charge les clés étrangères dans MySQL est InnoDB. Lors de la création d'une clé étrangère, la table parent doit avoir un index correspondant et la table enfant. doit avoir un index correspondant lors de la création de la clé étrangère. Les index correspondants seront également automatiquement créés.

Lors de la création d'un index, vous pouvez spécifier les opérations correspondantes sur la table enfant lors de la suppression ou de la mise à jour de la table parent, notamment restrict, cascade, setnull et noaction. Parmi eux, restrict et noaction sont identiques, ce qui signifie que la table parent ne peut pas être mise à jour lorsque la table enfant est liée ; la casecade signifie que lorsque la table parent est mise à jour ou supprimée, les enregistrements correspondant à la table enfant sont mis à jour ou supprimés ; setnull signifie que la table parent est mise à jour ou supprimée. Lors de la suppression, le champ correspondant de la sous-table est setnull.

Lorsqu'une table est référencée par une clé étrangère créée par une autre table, il est interdit de supprimer l'index ou la clé primaire correspondante de la table.

Vous pouvez utiliser setforeign_key_checks=0; pour désactiver temporairement les contraintes de clé étrangère, et setforeign_key_checks=1 pour activer les contraintes.

　(3) MEMORY

La mémoire utilise le contenu stocké en mémoire pour créer des tables. Chaque table MEMORY correspond en fait à un fichier disque, le format est .frm. L'accès aux tables de type MEMORY est très rapide car les données sont placées en mémoire et les index HASH sont utilisés par défaut. Cependant, une fois le serveur arrêté, les données de la table seront perdues, mais la table continuera d'exister.

Par défaut, la table de données mémoire utilise un index de hachage. L'utilisation de cet index pour effectuer une "comparaison d'égalité" est très rapide, mais la "comparaison de plage" est beaucoup plus lente. Par conséquent, la valeur de l'index de hachage est adaptée à une utilisation dans les opérateurs "=" et "", mais ne convient pas à une utilisation dans les opérateurs "". utiliser dans la clause orderby à l’intérieur. Si vous souhaitez vraiment utiliser les opérateurs "" ou entre, vous pouvez utiliser un index btree pour accélérer les choses.

Les lignes de données stockées dans la table de données MEMORY utilisent un format de longueur constante, accélérant ainsi le traitement. Cela signifie que les types de données de longueur variable tels que BLOB et TEXT ne peuvent pas être utilisés. VARCHAR est un type de longueur variable, mais comme il est traité en interne par MySQL comme un type CHAR de longueur fixe, il peut être utilisé.

Createtabletab_memoryengine=memoryselectid,name,age,addrfrommanorderbyid;

Utilisez USINGHASH/BTREE pour spécifier l'index.

Createindexmem_hashusinghashontab_memory(city_id);

Utilisez l'option --init-file lors du démarrage du service MySQL et placez des instructions telles que insertinto...select ou loaddatainfile dans ce fichier. Les tables peuvent être chargées. à partir d’une source de données durable au démarrage du service.

Le serveur a besoin de suffisamment de mémoire pour maintenir la table MEMORY qui est utilisée en même temps. Lorsque la table MEMORY n'est plus utilisée, pour libérer la mémoire occupée par la table MEMORY, vous devez exécuter DELETEFROM ou troncatetable ou. supprimer tout le tableau.

La quantité de données placées dans chaque table MEMORY est limitée par la variable système max_heap_table_size. La valeur initiale de cette variable système est 16M. En même temps, lors de la création d'une table MEMORY, vous pouvez utiliser la clause MAX_ROWS pour spécifier la. nombre maximum de lignes dans le tableau.

Le point de départ pour utiliser le moteur de stockage MySQL Memory est la vitesse. Pour obtenir le temps de réponse le plus rapide, le support de stockage logique utilisé est la mémoire système. Bien que le stockage des données de la table en mémoire offre des performances élevées, lorsque le démon mysqld plante, toutes les données de la mémoire sont perdues. La vitesse gagnée entraîne certains inconvénients. Cela nécessite que les données stockées dans la table de données mémoire utilisent un format de longueur constante, ce qui signifie que les types de données de longueur variable tels que BLOB et TEXT ne peuvent pas être utilisés. Il s'agit d'un type de longueur variable, mais parce qu'il est traité en interne par MySQL. comme un type CHAR avec une longueur fixe, il peut donc être utilisé.

　 (4) MERGE

Le moteur de stockage de fusion est une combinaison d'un ensemble de tables MyISAM. Les structures de ces tables MyISAM doivent être exactement les mêmes. Il n'y a aucune donnée dans la table MERGE. , et les tables de type MERGE peuvent être interrogées, mises à jour et supprimées. Ces opérations sont en fait des opérations sur la table interne MyISAM. Pour l'opération d'insertion dans la table MERGE, la table insérée est définie selon la clause INSERT_METHOD. Elle peut avoir trois valeurs différentes pour que l'opération d'insertion agisse sur la première ou la dernière table en conséquence. cette clause ou définissez-la sur NO, indiquant que la table MERGE ne peut pas être insérée. Vous pouvez effectuer une opération de suppression sur la table MERGE. Cette opération supprime uniquement la définition de la table MERGE et n'a aucun impact sur les tables internes. MERGE conserve deux fichiers commençant par le nom de la table MERGE sur le disque : le fichier .frm stocke la définition de la table ; le fichier .MRG contient des informations sur la table combinée, y compris les tables dont la table MERGE est composée et la base de celle-ci. insertion de données. La table MERGE peut être modifiée en modifiant le fichier .MRG, mais elle doit être actualisée via flushtable après modification.

createtableman_all(idint,namevarchar(20))engine=mergeunion=(man1,man2)insert_methos=last;

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!