Moteur de stockage mysql : la différence entre myIsam et innodb

MySQL dispose de plusieurs moteurs de stockage, MyISAM et InnoDB sont deux moteurs couramment utilisés. Voici quelques concepts de base sur ces deux moteurs (pas une introduction approfondie).

MyISAM est le moteur de stockage par défaut pour MySQL. Il est basé sur le type ISAM traditionnel et prend en charge la recherche en texte intégral, mais il n'est pas sécurisé pour les transactions et ne prend pas en charge les clés étrangères. Chaque table MyISAM est stockée dans trois fichiers : le fichier frm stocke la définition de la table ; le fichier de données est MYD (MYData) ; le fichier d'index est MYI (MYIndex).

InnoDB est un moteur transactionnel qui prend en charge la restauration, la récupération après incident, le contrôle de concurrence multi-version, les transactions ACID et le verrouillage au niveau des lignes (le verrouillage des lignes de la table InnoDB n'est pas absolu. Si MySQL exécute un SQL Si la portée à analyser ne peut pas être déterminée, la table InnoDB verrouillera également la table entière, comme l'instruction SQL lors d'une opération similaire), et fournira une méthode de lecture sans verrouillage cohérente avec le type Oracle. InnoDB stocke ses tables et index dans un tablespace, qui peut contenir plusieurs fichiers.

Différence fondamentale

MyISAM n'est pas sécurisé sur le plan transactionnel, tandis qu'InnoDB est sécurisé sur le plan transactionnel.

La granularité du verrouillage MyISAM est au niveau de la table, tandis qu'InnoDB prend en charge le verrouillage au niveau de la ligne.

MyISAM prend en charge l'index de type texte intégral, mais InnoDB ne prend pas en charge l'index de texte intégral.

MyISAM est relativement simple, il est donc meilleur qu'InnoDB en termes d'efficacité. Les petites applications peuvent envisager d'utiliser MyISAM.

Les tables MyISAM sont enregistrées sous forme de fichiers. L'utilisation du stockage MyISAM dans le transfert de données multiplateforme évitera bien des problèmes.

Les tables InnoDB sont plus sécurisées que les tables MyISAM. Vous pouvez basculer les tables non transactionnelles vers les tables transactionnelles (alter table tablename type=innodb) tout en garantissant que les données ne seront pas perdues.

Scénarios d'application

MyISAM gère les tables de non-transaction. Il offre un stockage et une récupération à grande vitesse, ainsi que des capacités de recherche en texte intégral. Si votre application doit effectuer un grand nombre de requêtes SELECT, MyISAM est un meilleur choix.

InnoDB est utilisé pour les applications de traitement de transactions et possède de nombreuses fonctionnalités, notamment la prise en charge des transactions ACID. Si votre application doit effectuer un grand nombre d'opérations INSERT ou UPDATE, vous devez utiliser InnoDB, qui peut améliorer les performances des opérations simultanées multi-utilisateurs.

Moteur de stockage MySQL et index

La base de données doit avoir un index Sans index, le processus de récupération devient une recherche séquentielle La complexité temporelle de O(n). est presque intolérable. Il est très simple d'imaginer comment une table composée d'une seule clé peut être indexée à l'aide d'un arbre B+, à condition que la clé soit stockée dans le nœud de l'arbre. Lorsqu'un enregistrement de base de données contient plusieurs champs, une arborescence B+ ne peut stocker que la clé primaire. Si un champ de clé non primaire est récupéré, l'index de clé primaire perd sa fonction et redevient une recherche séquentielle. A ce moment, un deuxième ensemble d'index doit être établi sur la deuxième colonne à récupérer. Cet index est organisé par arbres B+ indépendants. Il existe deux méthodes courantes pour résoudre le problème de plusieurs arbres B+ accédant au même ensemble de données de table, l'une est appelée index clusterisé (index clusterisé) et l'autre est appelée index non clusterisé (index secondaire). Bien que ces deux noms soient tous deux appelés index, ils ne constituent pas un type d'index distinct, mais une méthode de stockage de données. Pour le stockage d'index clusterisé, les données de ligne et les arbres B+ de clé primaire sont stockés ensemble, et les arbres B+ de clé secondaire stockent uniquement les clés secondaires et les arbres B+ de clé primaire et de clé non primaire sont presque deux types d'arbres. Pour le stockage d'index non clusterisé, l'arborescence de clé primaire B+ stocke les pointeurs vers les lignes de données réelles dans les nœuds feuilles au lieu de la clé primaire.

InnoDB utilise un index clusterisé pour organiser la clé primaire dans un arbre B+, et les données de ligne sont stockées sur le nœud feuille. Si vous utilisez la condition "where id = 14" pour trouver la clé primaire, suivez. L'algorithme de récupération d'arbre B+ peut trouver le nœud feuille correspondant, puis obtenir les données de ligne. Si vous effectuez une recherche conditionnelle sur la colonne Nom, vous avez besoin de deux étapes : la première étape consiste à récupérer le Nom dans l'arborescence de l'index auxiliaire B+, et d'atteindre son nœud feuille pour obtenir la clé primaire correspondante. La deuxième étape utilise la clé primaire pour effectuer une autre opération de récupération d'arbre B+ sur l'espèce d'arbre d'index primaire B+, et atteint finalement le nœud feuille pour obtenir la ligne entière de données.

MyISM utilise un index non clusterisé. Les deux arbres B+ de l'index non clusterisé ne sont pas différents. La structure des nœuds est exactement la même mais le contenu stocké est différent. L'arborescence d'index B+ stocke la clé primaire. L'arborescence d'index de clé secondaire B+ stocke les clés secondaires. Les données de la table sont stockées dans un endroit séparé. Les nœuds feuilles des deux arbres B+ utilisent une adresse pour pointer vers les données réelles de la table, il n'y a pas de différence entre les deux clés. L’arborescence d’index étant indépendante, la récupération par clé secondaire ne nécessite pas d’accès à l’arborescence d’index pour la clé primaire.

Afin d'illustrer plus clairement la différence entre ces deux index, nous imaginons un tableau qui stocke 4 lignes de données comme indiqué ci-dessous. Parmi eux, Id sert d’index principal et Name sert d’index secondaire. Le diagramme montre clairement la différence entre un index clusterisé et un index non clusterisé.

Nous nous concentrons sur l'index clusterisé. Il semble que l'efficacité de l'index clusterisé soit évidemment inférieure à celle de l'index non clusterisé, car chaque récupération utilise l'index auxiliaire. doit être fait deux fois. La recherche arborescente B+, n'est-ce pas inutile ? Quels sont les avantages de l’index clusterisé ?

1 Étant donné que les données de ligne et les nœuds feuilles sont stockés ensemble, la clé primaire et les données de ligne sont chargées ensemble dans la mémoire lorsque le nœud feuille est trouvé, les données de ligne peuvent être renvoyées immédiatement si les données sont organisées selon. l'identifiant de la clé primaire, les données seront mises à jour rapidement.

2 L'index auxiliaire utilise la clé primaire comme "pointeur" au lieu d'utiliser la valeur d'adresse comme pointeur. L'avantage est qu'il réduit le travail de maintenance de l'index auxiliaire en cas de déplacement de ligne ou de division de page de données. , en utilisant la valeur de la clé primaire comme pointeur. Cela fera que l'index auxiliaire occupera plus d'espace. L'avantage est qu'InnoDB n'a pas besoin de mettre à jour le "pointeur" dans l'index auxiliaire lors du déplacement des lignes. C'est-à-dire que la position de la ligne (située dans la page de 16 Ko dans l'implémentation, qui sera abordée plus tard) changera à mesure que les données de la base de données seront modifiées (la division précédente du nœud de l'arborescence B+ et la division de la page), et vous pouvez utiliser un index clusterisé.Il est garanti que quelle que soit la façon dont les nœuds de l'arborescence de clé primaire B+ changent, l'arborescence d'index auxiliaire ne sera pas affectée.

Ainsi, dans le cas de millions de données et de données plus volumineuses, les performances d'indexation de mysql innoDB sont encore meilleures !

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!