Quelle est la syntaxe de l'index MySQL

Définition de l'index

Un index est une structure de données ordonnée qui aide MySQL à obtenir des données efficacement. Il s'agit de la définition officielle d'un index par MySQL. Afin d'améliorer l'efficacité des requêtes, les index sont un mécanisme ajouté aux champs des tables de base de données. En plus des données, le système de base de données conserve également des structures de données qui satisfont à des algorithmes de recherche spécifiques. Ces structures de données font référence (pointent vers) les données d'une manière ou d'une autre, de sorte que des algorithmes de recherche avancés puissent être implémentés sur ces structures de données. indice. Comme le montre le schéma ci-dessous :

En fait, pour parler simplement, un index est une structure de données triée

Le côté gauche est la table de données, avec un total de deux colonnes et sept enregistrements, et le côté le plus à gauche l'une est la structure physique de l'adresse de l'enregistrement de données (notez que les enregistrements logiquement adjacents ne sont pas nécessairement physiquement adjacents sur le disque). Afin d'accélérer la recherche de Col2, vous pouvez maintenir un arbre de recherche binaire comme indiqué à droite. Chaque nœud contient valeur de clé d'index et un pointeur vers l'adresse physique de l'enregistrement de données correspondant, afin que vous puissiez. utilisez la recherche binaire pour obtenir rapidement les données correspondantes.

Avantages de l'index

• Accélérez la vitesse de recherche et de tri, réduisez le coût d'E/S de la base de données et la consommation du processeur

• En créant un index unique, vous pouvez garantir le caractère unique de chacun ligne de données dans la table de base de données.

Inconvénients de l'index

1. L'index est en fait une table, qui enregistre la clé primaire et le champ d'index et pointe vers les enregistrements de la classe d'entité. Il doit lui-même prendre de la place

2. Bien que. cela augmente l'efficacité des requêtes, c'est Ajout, suppression et modification. Chaque fois que la table est modifiée, l'index doit être mis à jour. Nouveau : Naturellement, de nouveaux nœuds doivent être ajoutés dans l'arborescence d'index. l'arbre d'index peut devenir invalide, ce qui signifie que de nombreux nœuds dans cet arbre d'index sont invalides. Changement : Le pointeur du nœud dans l'arbre d'index doit peut-être être modifié

Mais en fait, nous n'utilisons pas. arbre de recherche binaire pour le stocker dans MySQL Pourquoi ?

Vous devez savoir que dans un arbre de recherche binaire, un nœud ici ne peut stocker qu'une seule donnée, et un nœud correspond à un bloc de disque dans MySQL. De cette façon, chaque fois que nous lisons un bloc de disque, nous ne pouvons que le faire. obtenir une donnée, l'efficacité est particulièrement faible, on pensera donc à utiliser une structure B-tree pour la stocker.

Structure de l'index

Les index sont implémentés dans la couche moteur de stockage de MySQL, pas dans la couche serveur. Par conséquent, les index peuvent différer selon les moteurs de stockage, et tous les moteurs ne prennent pas en charge tous les types d'index.

• Indice BTREE : Le type d'index le plus courant, la plupart des index prennent en charge les index B-tree.

• HASH Index : Uniquement pris en charge par le moteur de mémoire, le scénario d'utilisation est simple.

• Indice R-tree (index spatial) : L'index spatial est un type d'index spécial du moteur MyISAM. Il est principalement utilisé pour les types de données géospatiales. Il est généralement moins utilisé et ne sera pas spécialement introduit.

• Texte intégral (index de texte intégral) : L'index de texte intégral est également un type d'index spécial de MyISAM, principalement utilisé pour l'index de texte intégral. InnoDB prend en charge l'index de texte intégral à partir de la version Mysql5.6.

MyISAM, InnoDB et Memory trois moteurs de stockage prennent en charge différents types d'index

index	INNODB moteur	MYISAM moteur	Moteur MÉMOIRE
BTREE index	Supporté	Supporté	Supporté
Indice HASH	Non pris en charge	Non pris en charge	Supporté
R-tree index	Non pris en charge	Supporté	Non pris en charge
Texte intégral	Supporté après la version 5.6	Pris en charge	Non pris en charge

Les index auxquels nous faisons habituellement référence, sauf indication explicite, sont organisés selon une structure d'arbre B+ (un arbre de recherche multidirectionnel, pas nécessairement binaire). Les index clusterisés, les index composés, les index de préfixe et les index uniques appelés index utilisent tous des index d'arborescence B+ par défaut.

BTREE

Arbre de recherche équilibré multidirectionnel, un ordre m (m-fork) BTREE satisfait :

• Maximum de m enfants par nœud Nombre d'enfants : ceil(m/2) à m Nombre de mots-clés : ceil (m/2)-1 à m-1

ceil signifie arrondir, ceil(2.3)=3

insérer le mot-clé case

pour garantir que les propriétés de l'ordre m B- les arbres ne sont pas détruits

En raison du niveau 3, il ne peut y avoir que 2 nœuds au maximum, donc 26 et 30 sont ensemble au début, puis 85 commenceront à se diviser, 30 sera la position médiane supérieure, 26 restera, et 85 ira vers la droite
C'est-à-dire : La position médiane monte, puis le côté gauche reste à l'ancien nœud, et le côté droit va vers le nouveau nœud

Quand 70 est à nouveau inséré dans l'image, 70 se trouve être la position médiane, puis 62 reste et 85 est divisé en un nouveau nœud

Après avoir atteint le niveau supérieur, il doit être à nouveau divisé

Continuez simplement à se diviser vers le haut

.

Avantages comparatifs

Par rapport aux arbres de recherche binaires, la hauteur/profondeur est inférieure et l'efficacité naturelle des requêtes est plus élevée.

B+TREE

• L'arbre B+ a deux types de nœuds : les nœuds internes (également appelés nœuds d'index) et les nœuds feuilles. Les nœuds internes ne sont pas des nœuds feuilles. Les nœuds internes ne stockent pas de données, seuls des index et les données sont stockées dans des nœuds feuilles.

• Les clés du nœud interne sont classées dans l'ordre de petite à grande Pour une clé dans le nœud interne, toutes les clés de l'arborescence de gauche sont inférieures à elle et toutes les clés du sous-arbre de droite sont supérieures à. ou égal à celui-ci. Les enregistrements dans les nœuds feuilles sont également classés en fonction de la taille de la clé.

• Chaque nœud feuille stocke des pointeurs vers les nœuds feuilles adjacents. Les nœuds feuilles eux-mêmes sont connectés dans l'ordre du petit au grand en fonction de la taille des mots-clés.

• Le nœud parent stocke l'index

  du premier élément du bon enfant.

Par rapport aux avantages

• L'efficacité des requêtes de B+Tree

  est plus stable. Étant donné que seuls les nœuds feuilles de B+Tree stockent les informations clés, l’interrogation de n’importe quelle clé nécessite de passer de la racine aux feuilles, ce qui est donc plus stable.

• Il vous suffit de parcourir les nœuds feuilles pour parcourir l'arbre entier.

B+Tree dans MySQL

La structure de données d'index MySql optimise le B+Tree classique. Sur la base du B+Tree original, un pointeur de liste chaînée pointant vers le nœud feuille adjacent (la structure globale est similaire à une liste doublement chaînée) est ajouté pour former un B+Tree avec un pointeur séquentiel pour améliorer les performances de l'intervalle. accéder.

Les étudiants attentifs peuvent voir quelle est la plus grande différence entre cette image et notre arbre de recherche binaire ?

Transition de

arbre de recherche binaire à B-tree
• , un changement important est qu'un nœud peut stocker plusieurs données, ce qui équivaut à un bloc de disque peut stocker plusieurs données, ce qui réduit considérablement notre fréquence d'E/S ! !
  Diagramme de structure d'index B+ dans MySQL :

Diagramme d'arbre de recherche binaire :

Principe de l'index

Indice BTree :

Introduction à l'initialisation

Le bleu clair est appelé comme un bloc de disque , vous pouvez voir que chaque bloc de disque contient plusieurs éléments de données (indiqués en bleu foncé) et des pointeurs (indiqués en jaune)

Par exemple, le bloc de disque 1 contient les éléments de données 17 et 35 et contient les pointeurs P1, P2 et

P1. représente les blocs de disque inférieurs à 17, P2 représente les blocs de disque entre 17 et 35 et P3 représente les blocs de disque supérieurs à 35.

Les données réelles existent dans les nœuds feuilles
• c'est-à-dire 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. `
  Les nœuds non-feuilles ne stockent pas de données réelles, seuls les
éléments de données qui guident la direction de recherche
• , tels que 17 et 35, n'existent pas réellement dans la table de données. `

Processus de recherche

Si vous souhaitez trouver l'élément de données 29, alors le bloc de disque 1 sera d'abord chargé du disque vers la mémoire, et une IO sera surviennent à ce moment. Utilisez une recherche binaire dans la mémoire pour déterminer que 29 est compris entre 17 et 35, et verrouillez le pointeur P2 du bloc disque 1. Le temps mémoire est négligeable car très court (par rapport aux IO du disque). L'adresse du pointeur P2 du bloc de disque 1 vers le bloc de disque 3 est chargée du disque dans la mémoire. La deuxième E/S se produit entre 26 et 30. Le pointeur P2 du bloc de disque 3 est verrouillé dans la mémoire. la mémoire via le pointeur. La troisième IO se produit. En même temps, la mémoire passe. La recherche binaire atteint 29 et termine la requête, avec un total de trois IO.

La situation réelle est qu'un arbre B+ à 3 couches peut représenter des millions de données si des millions de recherches de données ne nécessitent que trois IO, l'amélioration des performances sera énorme s'il n'y a pas d'index, chaque. Un élément de données nécessite une IO, donc un total de millions d'IO sont nécessaires. Évidemment, le coût est très, très élevé.

Classification d'index

Une table organisée en index est une table stockée dans l'ordre des clés primaires en tant qu'index. Cette méthode convient au moteur InnoDB. Étant donné qu'InnoDB utilise le modèle d'index d'arbre B+, les données sont stockées dans l'arborescence B+.

Chaque index correspond à un arbre B+ dans InnoDB.
Supposons que nous ayons une table avec la colonne de clé primaire comme ID, qu'il y ait un champ k dans la table et qu'il y ait un index sur k.
L'instruction de création de table de cette table est :

mysql> create table T( 
  id int primary key, 
  k int not null,  
  name varchar(16), 
  index (k))engine=InnoDB; 
复制代码

Les valeurs (ID,k)​​de R1~R5 dans la table sont (100,1), (200, 2), (300, 3), (500,5) et (600,6), l'exemple de diagramme schématique des deux arbres est le suivant :

# 🎜🎜#

D'après l'image, il n'est pas difficile de voir qu'en fonction du contenu des nœuds feuilles, le type d'index est divisé en index de clé primaire et index de clé non primaire.

Index de clé primaire

La colonne de clé primaire de la table de données utilise l'index de clé primaire et sera créée par défaut. C'est pourquoi, lorsque nous n'avons pas appris l'indexation. pourtant, le professeur nous dit souvent qu'on dit que la recherche basée sur la clé primaire sera plus rapide. Il s'avère que la clé primaire elle-même a un index construit. Le nœud feuille de

index de clé primaire stocke la ligne entière de données . Dans InnoDB, l'index de clé primaire est également appelé index clusterisé (index clusterisé).

Index auxiliaire

Le contenu du nœud feuille de l'index auxiliaire est la valeur de clé primaire . Dans InnoDB, l'index auxiliaire est également appelé Secondary Index (index secondaire).

Comme indiqué ci-dessous :

• L'index de clé primaire stocke la

  ligne entière de données

• L'index auxiliaire ne se stocke que lui-même et la clé primaire id est utilisée pour la requête de table

# 🎜🎜##🎜 🎜#

Sur la base de la structure d'index ci-dessus, discutons d'une question : Quelle est la différence entre les requêtes basées sur des index de clé primaire et des index auxiliaires ?

Si l'instruction est select * from T où ID=500, qui est la méthode de requête de clé primaire, il vous suffit de rechercher l'arbre B+ de l'ID ;

• Si l'instruction est select * from T où k=5, qui est la méthode de requête d'index ordinaire, vous devez rechercher #🎜🎜 #k index tree
, la valeur de l'ID est 500,
• puis recherchez à nouveau dans l'arborescence d'index ID

  . Ce processus s'appelle Retour à table. En d'autres termes, les requêtes basées sur des index auxiliaires doivent analyser une arborescence d'index supplémentaire. Par conséquent, nous devrions essayer d’utiliser des requêtes par clé primaire dans nos applications.

À moins que les données que nous souhaitons interroger existent dans notre arbre d'index, nous l'appelons actuellement

index couvert
--c'est-à-dire l'index La colonne contient toutes les données que nous souhaitons interroger.

Dans le même temps, l'index secondaire est divisé selon les types suivants (ignorez-le pour l'instant, nous en apprendrons plus plus tard) :
#🎜🎜 #

Unique Key (Unique Key)

: Un index unique est aussi une contrainte.

Les données en double ne peuvent pas apparaître dans la colonne d'attribut d'un index unique, mais les données peuvent être NULL. Une table permet la création de plusieurs index uniques.
• La plupart du temps, l'établissement d'un index unique a pour but de garantir l'unicité des données dans la colonne d'attribut, plutôt que d'améliorer l'efficacité des requêtes.

  Ordinary Index (Index)

  :
La seule fonction d'un index ordinaire est d'interroger rapidement des données. Une table permet. la création de plusieurs index normaux, et permet la duplication de données et NULL.

• Index de préfixe (Prefix)

   : L'index de préfixe s'applique uniquement aux données de type chaîne. L'index de préfixe crée un index sur les premiers caractères du texte Par rapport à l'index ordinaire, les données créées sont plus petites car seuls les premiers caractères sont récupérés.

• Full Text Index (Full Text)

   : L'index de texte intégral est principalement utilisé pour récupérer des informations sur les mots-clés dans des données de texte volumineuses, et est le moteur de recherche actuel Une technologie utilisée par les moteurs de bases de données. Avant Mysql5.6, seul le moteur MYISAM prenait en charge l'indexation en texte intégral. Après la version 5.6, InnoDB prenait également en charge l'indexation en texte intégral
.

Extension - Index pushdown

Le soi-disant pushdown, comme son nom l'indique, en fait retarde notre opération de retour de table MySQL ne nous permettra pas de renvoyer facilement la table car c'est un gaspillage considérable. Qu'est-ce que ça veut dire? Considérez l'exemple suivant.

Nous avons établi un index composite (nom, statut, adresse), qui est également stocké selon ce champ, similaire à l'image :

Arbre d'index composé (stocke uniquement les colonnes d'index et les clés primaires pour le retour de la table)

我们执行这样一条语句：

SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ;
复制代码

1. 首先我们在复合索引树上，找到了第一个以小米开头的name -- 小米1

2. 此时我们不着急回表(回到主键索引树搜索的过程，我们称为回表)，而是先在复合索引树判断status是否=1，此时status=0，我们直接就不回表了，直接继续找下一个以小米开头的name

1. 找到第二个-- 小米2，判断status=1，则根据id=2去主键索引树上找，得到所有的数据

这种先在自身索引树上判断是否满足其他的where条件，不满足则直接pass掉，不进行回表的操作，就叫做索引下推。

最左前缀原则

所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1. 按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效

   

2. 出现跳跃的情况

• 直接第一层name都不走，当然都失效

  

• 走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有name成功）

  

• 同时，这个顺序并不是由我们where中的排列顺序决定，比如： where name='小米科技' and status='1' and address='北京市' where status='1' and name='小米科技' and address='北京市'

这两个尽管where中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的

其实是因为我们MySQL有一个Optimizer（查询优化器），查询优化器会将SQL进行优化，选择最优的查询计划来执行。

• 关于这个查询优化器，后续文章我们也会谈谈MySQL的逻辑架构与存储引擎

索引设计原则

针对表

1. 查询频次高，且数据量多的表

针对字段

1. 最好从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

其他原则

1. 最好用唯一索引，区分度越高，使用索引的效率越高

2. 不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

比如：

我们创建了三个单列索引，name，status，address

当我们where中根据status和address两个字段来查询时，数据库只会选择最优的一个索引，不会所有单列索引都使用。

最优的索引：具体是指所查询表中，辨识度最高(所占比例最少)的索引列，比如此处address中有一个辨识度很高的 '西安市'数据；

1. 使用短索引,索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

2. 利用最左前缀，比如有N个字段，我们不一定需要创建N个索引，可以用复合索引

也就是说，我们尽量创建复合索引，而不是单列索引

创建复合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;
复制代码

举个栗子

假设我们有这么一个表，id为主键，没有创建索引：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB
复制代码

如果要在此处建立复合索引，我们要遵循什么原则呢？

通过调整顺序，可以少维护一个索引

• 比如我们的业务需求里边，有如下两种查询方式： 根据name查询 根据name和age查询

如果我们建立索引（age,name），由于最左前缀原则，我们这个索引能实现的是根据age，根据age和name查询，并不能单纯根据name查询（因为跳跃了），为了实现我们的需求，我们还得再建立一个name索引；

而如果我们通过调整顺序，改成（name，age），就能实现我们的需求了，无需再维护一个name索引，这就是通过调整顺序，可以少维护一个索引。

考虑空间->短索引

• 比如我们的业务需求里边，有以下两种查询方式： 根据name查询 根据age查询 根据name和age查询

我们有两种方案：

1. 建立联合索引(name,age)，建立单列索引：age索引。

2. 建立联合索引(age,name)，建立单列索引：name索引。

这两种方案都能实现我们的需求，这个时候我们就要考虑空间了，name字段是比age字段大的，显然方案1所耗费的空间是更小的，所以我们更倾向于方案1。

何时建立索引

1. where中的查询字段

2. 查询中与其他表关联的字段，比如外键

3. 排序的字段

4. 统计或分组的字段

何时达咩索引

1. 表中数据量很少

2. 经常改动的表

3. 频繁更新的字段

4. 数据重复且分布均匀的表字段（比如包含了很多重复数据，那此时多叉树的二分查找，其实用处不大，可以理解为O(logn)退化了）

索引相关语法

创建索引

默认会为主键创建索引--primary

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
复制代码

查找索引

结尾加上\G，可以变成竖屏显示

select index from tbl_name\G;
复制代码

删除索引

drop INDEX index_name on tbl_name ;
复制代码

变更索引

1). alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL	
	
2). alter  table  tb_name  add  unique index_name(column_list);
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	该语句指定了索引为FULLTEXT， 用于全文索引
复制代码

查看索引使用情况

show status like 'Handler_read%';	 -- 查看当前会话索引使用情况

show global status like 'Handler_read%';	-- 查看全局索引使用情况
复制代码

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

nom	statut	adresse	id (clé primaire)
Xiaomi 1	0	1	1
Xiaomi 2	1	1	2

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!