Différences entre les moteurs de stockage InnoDB et MyISAM dans MySQL

MySQLUne caractéristique très importante qui distingue la base de données des autres bases de données est son moteur de stockage de tables de plug-in, qui est basé sur des tables plutôt que sur des bases de données. Chaque moteur de stockage ayant ses propres caractéristiques, nous pouvons choisir le moteur de stockage le plus adapté à chaque table.

En tant que DBA, nous devrions avoir une compréhension approfondie des moteurs de stockage. Aujourd'hui, nous présentons les deux moteurs de stockage les plus courants et leurs différences : InnoDB et MyISAM.

InnoDB Moteur de stockage

InnoDB Le moteur de stockage prend en charge les transactions et son objectif de conception est principalement destiné aux OLTP（On Line Transaction Processing 在线事务处理） applications. Les fonctionnalités incluent la conception de verrouillage de ligne, la prise en charge des clés étrangères et la prise en charge des lectures non verrouillables. À partir de la version 5.5.8, InnoDB devient le moteur de stockage par défaut de MySQL.

InnoDBLe moteur de stockage utilise un index clusterisé pour stocker les données, donc chaque table est stockée dans l'ordre de la clé primaire si la clé primaire n'est pas spécifiée, InnoDB sera automatiquement généré pour chacune. la ligne A 6 d'octets sert de clé primaire. ROWID

Moteur de stockageMyISAM

Le moteur de stockage ne prend pas en charge la conception de transactions et de verrouillage de table, mais prend en charge l'index en texte intégral , principalement pour MyISAMApplication, adapté aux scénarios avec des requêtes fréquentes telles que les entrepôts de données. Avant la version OLAP（On Line Analytical Processing 联机分析处理）, 5.5.8 était le moteur de stockage par défaut pour MyISAM. Ce moteur représente le besoin d’interroger et d’analyser d’énormes quantités de données. Il met l'accent sur les performances, de sorte que les requêtes s'exécutent plus rapidement que MySQL. InnoDB

La différence entre et InnoDBMyISAM

Transactions

Pour les opérations de base de données Atomicité, nous avons besoin de transactions. Assurez-vous qu’un ensemble d’opérations réussissent ou échouent, comme la fonction de transfert d’argent. Nous plaçons généralement plusieurs déclarations

entre SQL et begin pour former une transaction. commit

est pris en charge, InnoDB n'est pas pris en charge. MyISAM

Clé primaire

En raison de l'index clusterisé de

, si la clé primaire n'est pas spécifiée, la clé primaire sera automatiquement générée. InnoDB
Prend en charge l'existence d'une table sans clé primaire. MyISAM

Clés étrangères

Afin de résoudre les dépendances d'une logique complexe, nous avons besoin de clés étrangères. Par exemple, la saisie des résultats de l'examen d'entrée à l'université doit appartenir à un certain étudiant, nous avons donc besoin d'une clé étrangère vers la base de données des résultats de l'examen d'entrée à l'université avec le numéro du billet d'admission.

est pris en charge, InnoDB n'est pas pris en charge. MyISAM

Index

Afin d'optimiser la vitesse des requêtes, de trier et de faire correspondre les recherches, nous avons besoin d'index. Par exemple, le nom de chacun est stocké séquentiellement à partir de la première lettre de

Lorsque nous recherchons a-z ou la position zhangsan, nous pouvons localiser rapidement l'emplacement que nous souhaitons rechercher. 44

est un index clusterisé. Les données sont liées à l'index clusterisé de la clé primaire. L'indexation via la clé primaire est très efficace. Si vous effectuez une recherche dans l'index auxiliaire d'autres colonnes, vous devez d'abord trouver l'index clusterisé, puis interroger toutes les données, ce qui nécessite deux requêtes. InnoDB

est un index non clusterisé, les fichiers de données sont séparés et l'index enregistre le pointeur des données. MyISAM

À partir de la version

, après la version InnoDB 1.2.x, les deux prennent en charge l'indexation en texte intégral. MySQL5.6

Incrémentation automatiqueauto_increment

Pour les champs à incrémentation automatique,

exige que la colonne soit un index et soit la première colonne de l'index . Sinon, une erreur sera signalée : InnoDB

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Remplacez simplement la séquence

par (b,a). (a,b)

Et

peut combiner ce champ avec d'autres champs dans n'importe quel ordre pour former un index commun. MyISAM

Nombre de lignes du tableau

Une exigence très courante est de voir combien de données il y a dans le tableau, auquel cas nous avons besoin de

. select count(*) from table_name

Le nombre de lignes du tableau n'est pas enregistré et une analyse complète du tableau est requise. InnoDBEnregistrez-le dans une variable et lisez directement la valeur, ce qui est plus rapide. A cette époque, lors de l'interrogation avec MyISAM, les deux étaient identiques. where

Stockage

Les fichiers de la base de données doivent être stockés sur le disque, puis lus en mémoire lorsque l'application en a besoin. Inclut généralement les fichiers de données et les fichiers d'index.

est divisé en : InnoDB

• Fichier de structure de table.frm
• Espace table partagé.ibdata1
• Table exclusif L'espace .ibd
• fichier journal.redo

est divisé en trois fichiers : MyISAM

• définition de la table de stockage.frm
• Données de la table de stockage.MYD
• Indice de la table de stockage.MYI

Vitesse d'exécution

Si votre opération comporte un grand nombre d'opérations de requête, telles que SELECT, l'utilisation de MyISAM offrira de meilleures performances.
Si la plupart des opérations sont des suppressions et des modifications, utilisez InnoDB. Les index de

InnoDB et MyISAM sont tous deux des index d'arbre B+. La clé primaire des données peut être interrogée via l'index. Ceux qui ne sont pas familiers avec l'arbre B+ peuvent vérifier. les principes et algorithmes de l'index MySQL InnoDB. La différence de performances entre les deux réside principalement dans les différentes méthodes de traitement après interrogation de la clé primaire des données.

InnoDB mettra en cache les fichiers d'index et de données. Généralement, 16KB est utilisé comme la plus petite unité (taille de la page de données) pour interagir avec le disque, InnoDB obtient en fait le. clé primaire. ID, il doit trouver toutes les données de la ligne dans la page de données dans la mémoire, mais si les données ne sont pas chargées de données chaudes, il doit également rechercher et remplacer la page de données, ce qui peut impliquer plusieurs fois I/OOpération et recherche de données en mémoire, ce qui entraîne une consommation de temps élevée.

Le MyISAM moteur de stockage met uniquement en cache les fichiers d'index et ne met pas en cache les fichiers de données. Le cache de ses fichiers de données utilise directement le cache du système d'exploitation, ce qui est tout à fait unique. À l'heure actuelle, le même espace peut charger plus d'index, donc lorsque l'espace de cache est limité, la page de données d'index de MyISAM sera remplacée moins souvent. D'après ce que nous savons plus tôt, les fichiers de MyISAM sont divisés en MYI et MYD. Lorsque nous trouvons la clé primaire MYI via ID, nous obtenons en fait le décalage MYD du fichier de données. Find Data est beaucoup plus rapide que le offset mappage adressé. InnoDB

Mais comme

est un verrou de table et que MyISAM prend en charge les verrous de ligne, lorsqu'un grand nombre d'opérations d'écriture sont impliquées, les performances de concurrence de InnoDB sont bien meilleures que celles de InnoDB. Dans le même temps, MyISAM améliore également les performances de lecture et d'écriture simultanées grâce à InnoDB le contrôle multi-version. MVVC

Supprimer les donnéesdelete

Lors de l'appel de

, delete from table reconstruira directement la table, et MyISAM supprimera ligne par ligne, mais vous pouvez utiliser InnoDB à la place. Référence : Deux manières et différences dans la suppression des données de table dans MySQL. truncate table

Verrouillage

Prend uniquement en charge les verrous de table et la table entière est verrouillée pour chaque opération. MyISAM
Prend en charge le verrouillage des lignes, verrouillant un nombre minimum de lignes de données pour chaque opération. InnoDB

Le verrouillage de table consomme moins de ressources que le verrouillage de ligne et ne provoque pas de blocage, mais en même temps, les performances de concurrence sont médiocres. Le verrouillage de ligne consomme plus de ressources, est plus lent et peut provoquer un blocage, mais comme la granularité du verrouillage est faible et que les données sont petites, les performances de concurrence sont bonnes. Si une déclaration de

ne peut pas déterminer la plage à analyser, la table entière sera également verrouillée. InnoDB

Lorsqu'un blocage de verrouillage de ligne se produit, le nombre de lignes affectées par chaque transaction sera calculé, puis la transaction avec un plus petit nombre de lignes sera annulée.

Récupération de données

Il n'existe pas de récupération rapide et sûre après un crash. MyISAMIl existe un mécanisme de récupération complet. InnoDB

Mise en cache des données

Mise en cache uniquement les données d'index et interroge les données via l'index. MyISAMNon seulement met en cache les données d'index, mais met également en cache les informations sur les données, lit les données dans le pool de cache par page et les met à jour selon l'algorithme InnoDB. LRU（Latest Rare Use 最近最少使用）

Comment choisir un moteur de stockage

Les instructions pour créer une table sont les mêmes, seule la dernière

précise le moteur de stockage . type

MyISAM

1. Un grand nombre de requêtes

count

2. 3, Aucune opération de transaction

InnoDB1 Une haute disponibilité est requise, ou des transactions sont requises

2. >

Apprentissage recommandé :

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!