Une brève analyse des index dans le moteur de stockage MySQL

Cet article vous expliquera comment implémenter des index dans le moteur de stockage MySQL. J'espère qu'il vous sera utile !

Nous savons que les différents fichiers du moteur de stockage sont différents. Nous pouvons vérifier le répertoire des fichiers de données :

show VARIABLES LIKE 'datadir';

Chaque table InnoDB a deux fichiers (.frm et .ibd), et la table MyISAM a trois fichiers (.frm). , .MYD, .MYI) . [Recommandation associée : Tutoriel vidéo mysql]

Il existe un fichier identique, .frm. .frm est un fichier qui définit la structure de la table dans MySQL. Il sera généré quel que soit le moteur de stockage que vous choisissez lors de la création de la table, nous ne l'examinerons donc pas. .frm。 .frm是 MySQL 里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成，我们就不看了。

我们主要看一下其他两个文件是怎么实现 MySQL 不同的存储引擎的索引的。

我们先来看下 MyISAM。

MyISAM

在 MyISAM 里面，另外有两个文件：

一个是.MYD文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我们的 user_myisam 表的所有的表数据。

一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在 id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。

也就是说，在 MyISAM 里面，索引和数据是两个独立的文件。那我们怎么根据索引找到数据呢？

MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。

这里画的是主键索引，如果是辅助索引，有什么不一样呢？

在 MyISAM 里面，辅助索引也在这个.MYI 文件里面。 辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

InnoDB

InnoDB 只有一个文件（.ibd 文件），那索引放在哪里呢？

在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd

Nous examinons principalement comment les deux autres fichiers implémentent l'indexation de différents moteurs de stockage MySQL.

Jetons d'abord un coup d'œil à MyISAM.

MyISAM

Dans MyISAM, il y a deux autres fichiers :

L'un est le fichier .MYD, D signifie Data, qui est Les fichiers de données MyISAM stockent les enregistrements de données, tels que toutes les données de notre table user_myisam.

L'un est le fichier .MYI. I signifie Index, qui est le fichier d'index de MyISAM. Il stocke l'index. Par exemple, si nous créons un index de clé primaire sur le champ id, alors. l'index de clé primaire se trouve dans ce fichier d'index.

En d'autres termes, dans MyISAM, l'index et les données sont deux fichiers indépendants. Alors, comment pouvons-nous trouver les données basées sur l’index ?

Dans le B+Tree de MyISAM, les nœuds feuilles stockent les adresses disques correspondant aux fichiers de données. Par conséquent, après avoir trouvé la valeur clé dans le fichier d'index .MYI, l'enregistrement de données correspondant sera obtenu à partir du fichier de données .MYD.

Ce qui est affiché ici est l'index de clé primaire. S'il s'agit d'un index auxiliaire, quelle est la différence ?

Dans MyISAM, l'index auxiliaire se trouve également dans ce fichier .MYI. Il n'y a aucune différence entre l'index auxiliaire et l'index de clé primaire dans la manière dont ils stockent et récupèrent les données. Ils trouvent également l'adresse du disque dans le fichier d'index, puis obtiennent les données dans le fichier de données.

InnoDB

InnoDB n'a qu'un seul fichier (fichier .ibd), alors où est l'index ?

Dans InnoDB, il utilise la clé primaire comme index pour organiser le stockage des données, donc le fichier d'index et le fichier de données sont le même fichier, tous deux dans le fichier .ibd.

Sur le nœud feuille de l'index de clé primaire d'InnoDB, il stocke directement nos données.

Qu'est-ce qu'un index clusterisé (index clusterisé) ?

signifie que l'ordre logique des valeurs des clés d'index est cohérent avec l'ordre de stockage physique des lignes de données de la table. (Par exemple, le répertoire du dictionnaire est trié par Pinyin, et le contenu est également trié par Pinyin. Ce répertoire trié par Pinyin est appelé un index clusterisé).

🎜Dans InnoDB, la façon dont il organise les données est appelée (table d'organisation d'index cluster), donc l'index de clé primaire est un index cluster et les clés non primaires sont des index non clusterisés. 🎜🎜🎜Comment les index autres que les clés primaires, tels que l'index ordinaire que nous construisons sur le champ de nom, stockent-ils et récupèrent-ils les données ? 🎜🎜🎜🎜🎜🎜Dans InnoDB, il existe une distinction primaire et secondaire entre l'index de clé primaire et l'index auxiliaire. 🎜🎜L'index auxiliaire stocke l'index auxiliaire et les valeurs de la clé primaire. Si vous utilisez l'index auxiliaire pour interroger, l'index de clé primaire sera interrogé en fonction de la valeur de la clé primaire et les données seront finalement obtenues. 🎜🎜Par exemple, si nous utilisons l'index de nom pour interroger name= '青山', il trouvera la valeur de la clé primaire dans le nœud feuille, c'est-à-dire id=1, puis ira au nœud feuille de l'index de clé primaire pour obtenir les données. 🎜🎜🎜Une autre question, que se passe-t-il si une table n'a pas de clé primaire ? 🎜🎜🎜1. Si nous définissons une clé primaire (PRIMARY KEY), alors InnoDB sélectionnera la clé primaire comme index clusterisé. 🎜🎜2. Si la clé primaire n'est pas explicitement définie, InnoDB sélectionnera le premier index unique qui ne contient pas de valeur NULL comme index de clé primaire. 🎜🎜3. S'il n'existe pas d'index unique, InnoDB choisira le ROWID intégré de 6 octets comme index cluster caché, qui incrémentera la clé primaire au fur et à mesure que les enregistrements de ligne sont écrits. 🎜

select _rowid name from t2;

🎜🎜Et alors ? Il ne peut y avoir de table sans clé primaire. 🎜🎜🎜🎜Résumé🎜🎜🎜Grâce à l'analyse ci-dessus, nous savons quelle est la forme spécifique d'implémentation des index dans les deux principaux moteurs de stockage, MyISAM et InnoDB. 🎜

Pour plus de connaissances sur la programmation, veuillez visiter : Introduction à la programmation ! !

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!