Quels types d’index existe-t-il dans MySQL ?

Les types d'index incluent : 1. L'index B-tree, de sorte que chaque ligne de la table ait une valeur correspondante sur l'index ; 2. L'index de hachage, qui peut obtenir les lignes d'enregistrement de la table en fonction de la valeur de hachage correspondant à la colonne d'index. ; 3. Index ordinaire, qui permet d'insérer des valeurs en double et des valeurs nulles​​dans les colonnes qui définissent l'index ; 4. Index unique, qui peut éviter la duplication des données ; qui est un index créé pour le champ de clé primaire ; 6. Index spatial, qui est un index établi sur des champs de type de données spatiales ; 7. Index de texte intégral, utilisé pour rechercher des mots-clés dans le texte ; , l'index ne contient qu'une seule colonne de la table d'origine.

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

Explication détaillée des types d'index MySQL

Le type d'index est lié au moteur de stockage, et les types d'index pris en charge par chaque moteur de stockage ne sont pas nécessairement exactement les mêmes. Les index MySQL peuvent être classés selon une méthode de stockage, une perspective logique et une perspective d'utilisation pratique.

Différenciation des méthodes de stockage

Selon les différentes méthodes de stockage, les index couramment utilisés dans MySQL sont physiquement divisés en deux catégories : l'index B-tree et l'index HASH. Chacun des deux types d'index différents a son propre. propres différentes applications.

1) L'index B-tree

L'index B-tree est également appelé Indice BTREE Actuellement, la plupart des index sont stockés à l'aide d'index B-tree.

L'index B-tree est une structure de données typique, qui contient principalement les composants suivants :

• Nœuds feuilles : les entrées contenues pointent directement vers les lignes de données du tableau. Les nœuds feuilles sont connectés les uns aux autres et un nœud feuille possède un pointeur vers le nœud feuille suivant.
• Nœud de branche : contient des entrées pointant vers d'autres nœuds de branche ou nœuds feuilles dans l'index.
• Nœud racine : un index B-tree n'a qu'un seul nœud racine, qui est en fait le nœud de branche situé en haut de l'arbre.

Sur la base de cette structure de données arborescente, chaque ligne du tableau aura une valeur correspondante sur l'index. Par conséquent, lors de l'exécution d'une requête de données dans la table, la ligne où se trouvent les données peut être localisée étape par étape en fonction de la valeur de l'index.

L'index B-tree peut effectuer une requête complète sur la valeur de clé, la plage de valeurs de clé et le préfixe de valeur de clé, et peut également ORDER BY trier les résultats de la requête. Cependant, les index B-tree doivent suivre le principe du préfixe de gauche, et les contraintes suivantes doivent être prises en compte :

• La requête doit partir de la colonne la plus à gauche de l'index.
• La requête ne peut pas ignorer une certaine colonne d'index et doit être mise en correspondance dans l'ordre de gauche à droite.
• Le moteur de stockage ne peut pas utiliser les colonnes à droite de la condition de plage dans l'index.

2) Indice de hachage

Hash (Hash) est généralement traduit par « hachage », et certains sont directement translittérés en « hachage », qui consiste à convertir une entrée de n'importe quelle longueur (également appelé pré-mapping, pré- image) est convertie en une sortie de longueur fixe via un algorithme de hachage, et la sortie est la valeur de hachage.

Hash Index est également connu sous le nom de Hash Index ou HASH Index . MySQL ne prend actuellement en charge que ce type d'index pour le moteur de stockage MEMORY et le moteur de stockage HEAP. Parmi eux, le moteur de stockage MEMORY peut prendre en charge les index B-tree et les index HASH, et utilise HASH comme index par défaut.

L'index HASH ne recherche pas de données basées sur une structure de données arborescente, mais obtient les lignes d'enregistrement de la table en fonction de la valeur de hachage correspondant à la colonne d'index. La plus grande caractéristique de l'index de hachage est la vitesse d'accès rapide, mais il présente également les inconvénients suivants :

• MySQL doit lire la valeur de la colonne d'index dans la table pour participer au calcul du hachage, et le calcul du hachage prend relativement du temps. opération consommatrice. En d’autres termes, la création d’un index de hachage prendra plus de temps qu’un index B-tree.
• Impossible d'utiliser le tri d'index HASH.
• L'index HASH ne prend en charge que la comparaison d'égalité, telle que "=", "IN()" ou "96b4fef55684b9312718d5de63fb7121".
• L'index HASH ne prend pas en charge la correspondance partielle des clés, car la valeur HASH est calculée à travers la valeur entière de l'index.

Distinction logique

Selon l'objectif spécifique de l'index, les index dans MySQL sont logiquement divisés en 5 catégories suivantes :

1) Index ordinaire

L'index ordinaire est le plus basic dans le type MySQL Index n'a aucune restriction et sa seule tâche est d'accélérer l'accès du système aux données.

L'index normal permet d'insérer des valeurs en double et des valeurs nulles dans la colonne où l'index est défini.

Lors de la création d'un index normal, les mots-clés couramment utilisés sont INDEX ou KEY.

Exemple 1

Ce qui suit crée un index nommé index_id sur le champ id de la table tb_student.

CREATE INDEX index_id ON tb_student(id);

2) Index unique

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引通常使用 UNIQUE 关键字。

例 2

下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：

CREATE UNIQUE INDEX index_id ON tb_student(id);

其中，id 字段可以有唯一性约束，也可以没有。

3) 主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4) 空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

例 3

下面在 tb_student 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下：

CREATE SPATIAL INDEX index_line ON tb_student(line);

其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

5) 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

全文索引允许在索引列中插入重复值和空值。

不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。

例 4

在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

实际使用区分

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

1）单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

例 5

下面在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。SQL 语句如下：

CREATE INDEX index_addr ON tb_student(address(4));

这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

2）多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

例 6

下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

CREATE INDEX index_na ON tb_student(name,address);

该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

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