Quelle est la différence entre l'index clusterisé MySQL et l'index non clusterisé

Différence : 1. L'index clusterisé stocke les données dans la table au niveau des nœuds feuilles, tandis que l'index non clusterisé stocke la clé primaire et les colonnes d'index au niveau des nœuds feuilles. 2. L'ordre des enregistrements de la table dans l'index cluster est : Conformément à l'ordre de l'index. , l'ordre de tri des index non clusterisés est incohérent ; 3. Chaque table ne peut avoir qu'un seul index clusterisé, mais il peut y avoir plusieurs index non clusterisés.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Les index du moteur de stockage Innodb de MySQL sont divisés en deux catégories : les index clusterisés et les index non clusterisés. Vous pouvez comprendre les index clusterisés et les index non clusterisés en comparant les index du dictionnaire chinois. Le dictionnaire chinois propose deux manières de récupérer les caractères chinois. La première est la récupération du pinyin (à condition que la prononciation du caractère chinois soit connue. Par exemple, le caractère chinois avec le pinyin de cheng est classé après le caractère chinois avec le pinyin de). chang. Le numéro de page du caractère chinois correspondant est trouvé sur la base du pinyin (car en appuyant sur le tri Pinyin, la recherche binaire peut localiser rapidement), c'est ce que nous appelons habituellement l'ordre du dictionnaire ; caractères selon les traits, et trouvez le numéro de page correspondant au caractère chinois. La récupération Pinyin est un index clusterisé, car les enregistrements stockés (données de lignes dans la base de données, enregistrements détaillés des caractères chinois dans le dictionnaire) sont triés en fonction de cet index, bien que les mots avec les mêmes traits soient adjacents dans l'index des traits ; le stockage réel Les numéros de page ne sont pas adjacents, il s'agit d'un index non clusterisé.

Index clusterisé

L'ordre logique des valeurs clés dans l'index détermine l'ordre physique des lignes correspondantes dans le tableau.

Un index clusterisé détermine l'ordre physique des données dans une table. Un index clusterisé est similaire à un annuaire téléphonique dans le sens où les données sont classées par nom de famille. Les index clusterisés sont particulièrement efficaces pour les colonnes dans lesquelles les valeurs de plage sont souvent recherchées. Une fois que vous utilisez un index clusterisé pour rechercher la ligne contenant la première valeur, vous pouvez vous assurer que les lignes contenant les valeurs d'index suivantes sont physiquement adjacentes. Par exemple, si votre application exécute une requête qui récupère fréquemment des enregistrements dans une certaine plage de dates, vous pouvez utiliser un index clusterisé pour rechercher rapidement la ligne contenant la date de début, puis récupérer toutes les lignes adjacentes de la table jusqu'à ce que la date de fin soit atteinte. Cela contribue à améliorer les performances de ces requêtes. De même, si une colonne est fréquemment utilisée lors du tri des données extraites d'une table, la table peut être regroupée (triée physiquement) sur cette colonne pour réduire les coûts en n'ayant pas à la trier à chaque fois que cette colonne est interrogée.

Ce qui précède est la structure d'index b+tree d'innodb

Nous savons que b+tree évolue à partir d'un b-tree. Un B-Tree d'ordre m a les caractéristiques suivantes :

1. peut avoir au plus m nœuds enfants.
2. À l'exception du nœud racine et des nœuds feuilles, chaque nœud a au moins m/2 (arrondis au supérieur) de nœuds enfants.
3. Si le nœud racine n'est pas un nœud feuille, le nœud racine contient au moins deux nœuds enfants.
4. Tous les nœuds feuilles sont situés sur le même calque.
5. Chaque nœud contient k éléments (mots-clés), où m/2≤k6. Les éléments (mots-clés) de chaque nœud sont classés du plus petit au plus grand.
7. La valeur du nœud gauche de chaque élément (mot-clé) est inférieure ou égale à l'élément (mot-clé). Les valeurs du nœud droit sont supérieures ou égales à l'élément (mot-clé).

Les caractéristiques de b+tree sont :

1. Tous les nœuds non-feuilles stockent uniquement des informations sur les mots-clés.
2. Toutes les données satellite (données spécifiques) sont stockées dans des nœuds feuilles.
3. Tous les nœuds feuilles contiennent des informations sur tous les éléments.
4. Il existe un pointeur de lien entre tous les nœuds feuilles.

Nous avons constaté que b+trre présente les caractéristiques suivantes :

• est particulièrement efficace et rapide pour les requêtes dans une plage (via le pointeur de chaîne feuille)
• les requêtes pour des valeurs clés spécifiques ne sont que légèrement moins efficaces que b ; -tree (car au niveau feuille), mais peut également être ignoré

Index non clusterisé

　 L'ordre logique des index dans l'index est différent de l'ordre de stockage physique du disque en amont ;

En fait, par définition, les index autres que les index clusterisés sont des index non clusterisés, mais les gens souhaitent subdiviser les index non clusterisés en index ordinaires, index uniques et index de texte intégral. Si nous devons comparer l'index non clusterisé à quelque chose dans la vie réelle, alors l'index non clusterisé est comme le dictionnaire radical du dictionnaire Xinhua, et son ordre structurel n'est pas nécessairement cohérent avec l'ordre de stockage réel.

La structure de stockage de l'index non clusterisé est la même qu'auparavant. La différence est que la partie données du nœud feuille ne stocke plus des données spécifiques, mais la clé de l'index clusterisé des données. Par conséquent, le processus de recherche dans un index non clusterisé consiste à trouver d'abord la clé de l'index clusterisé correspondant à la clé d'index, puis à utiliser la clé de l'index clusterisé pour trouver les données correspondantes dans l'arborescence de l'index de clé primaire. le processus s'appelle retour de table !

Donnez-moi un exemple :

create table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`username` VARCHAR(255),
`score` INT,
PRIMARY KEY(`id`), KEY(`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Index clusterisé, index clusterisé (id), index non clusterisé (nom d'utilisateur).

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

聚集索引和非聚集索引区别

区别一：

聚集索引：就是以主键创建的索引，在叶子节点存储的是表中的数据

非聚集索引：就是以非主键创建的索引(也叫做二级索引)，在叶子节点存储的是主键和索引列。

区别二：

聚集索引中表记录的排列顺序和索引的排列顺序一致；所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引中表记录的排列顺序和索引的排列顺序不一致。

区别三：

聚集索引是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储不连续。

区别四：

聚集索引每张表只能有一个，非聚集索引可以有多个。

【相关推荐：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!