Vous guide à travers l'index MySQL-tutoriel mysql-php.cn

Cet article vous apporte des connaissances pertinentes sur mysql Il présente principalement certains problèmes de la version avancée de MySQL, notamment ce qu'est un index, l'implémentation sous-jacente de l'index, etc. sera utile à tout le monde.

Vous guide à travers l'index MySQL

Apprentissage recommandé : Tutoriel vidéo mysql

MySQL, un terme familier et inconnu Dès notre apprentissage de Javaweb, nous utilisions la base de données MySQL. À ce stade, MySQL nous semblait C'est juste. un bon outil pour stocker des données lors du stockage, il est constamment rempli lors de l'interrogation, c'est également une requête de table complète aveugle (sans un peu d'optimisation).

Nous nous trompons toujours et pensons que nous pouvons optimiser par d'autres aspects. Nous sommes réticents à affronter MySQL Advanced, et à la place, apprenons quelque chose qui semble être plus "avancé", apprenons Redis, venez partager la pression de MySQL, apprenez les middlewares tels que MyCat et implémentez la réplication maître-esclave, la séparation lecture-écriture, sous-base de données et sous-table, etc. (Je parle de melo, c'est vrai)

Quand je me préparais pour l'interview, j'ai découvert que je ne connaissais pas toutes les questions sur MySQL dans les questions de l'interview~

Et à propos du middleware de pointe que j'ai appris, j'ai posé très peu de questions ! ! Je sais seulement comment l'utiliser. Lors de la rédaction de mon CV, je ne peux écrire que faiblement « comprendre » le middleware xxx...

Bien sûr, l'apprentissage du MySQL Advanced Chapter ne se limite pas aux entretiens. Dans les projets réels, cette partie. L'optimisation est très importante. Après avoir connu des temps d'arrêt du serveur, je ne peux qu'en silence...

Commencer à partir de maintenant, il est encore trop tard pour débarquer ! ! ! En profitant des trois or et des quatre argent, complétez les points de connaissances du MySQL Advanced Chapter et commencez le voyage du MySQL Advanced Chapter sous les aspects suivants

Il est recommandé de rechercher les parties qui vous sont utiles à travers le répertoire de la barre latérale, parmi lesquels sont Le préfixe emoji est une partie importante Si vous le trouvez utile, l'éditeur continuera à améliorer cet article et la colonne MySQL.

Définition de l'index

La définition officielle de l'index par MySQL est la suivante : L'index (index) est une structure de données (ordonnée) qui aide MySQL à obtenir des données efficacement. Des index sont ajoutés aux champs des tables de base de données afin d'améliorer l'efficacité des requêtes. 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, afin 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 recherche et le tritaux, réduisez les coûts d'E/S de la base de données et la consommation du processeur
En créant un index unique, vous pouvez garantir l'unicité de chaque ligne de données dans la table de la base de données.

Inconvénients de l'index

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
Bien qu'il augmente la requête. efficacité, pour les ajouts, suppressions et modifications, à chaque fois. Après avoir modifié la table, vous devez mettre à jour l'index. Nouveau : Naturellement, vous devez ajouter de nouveaux nœuds dans l'arborescence d'index. Supprimer : Les enregistrements pointés dans l'arborescence d'index peuvent devenir. invalide, ce qui signifie que de nombreux nœuds dans cet arbre d'index sont des modifications invalides : Arbre d'index Le pointage du nœud du milieu devra 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 de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes 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. Les trois moteurs de stockage MyISAM, InnoDB et Memory prennent en charge différents types d'index.

BTREE index Index R-treeTexte intégral

Supporté

Non pris en charge

Supporté

Non pris en charge

Supporté

Non pris en charge

Supporté après la version 5.6

Supporté

Non pris en charge

Ce que nous appelons habituellement index, sauf indication contraire, fait référence à des index organisés dans une structure arborescente B+ (arbre de recherche multidirectionnel, pas nécessairement binaire). Parmi eux, l'index clusterisé, l'index composé, l'index de préfixe et l'index unique utilisent tous l'index B+tree par défaut, collectivement appelés index.

BTREE

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

Maximum 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'arbre B d'ordre m sont pas détruit

Puisque le niveau 3 ne peut avoir que 2 nœuds au maximum, 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 à la position médiane supérieure. à droite
C'est-à-dire : La position médiane supérieure, puis le côté gauche reste à l'ancien nœud et le côté droit va au nouveau nœud

Lorsque 70 est à nouveau inséré dans l'image, 70 se trouve au milieu position, puis 62 est maintenu et 85 est divisé en un nouveau nœud

Après avoir monté, il doit être divisé

Continuez simplement à se diviser vers le haut De même,

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. L'arbre

B+TREE

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 petit à grand 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 égales. à cela. 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 à l'avantage

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'ensemble de l'arbre.

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 ?

En passant de

, un changement significatif est qu'un nœud peut stocker plusieurs données, ce qui équivaut à un bloc de disque pouvant stocker plusieurs données, ce qui réduit considérablement nos temps d'E/S ! !

Diagramme d'arbre de recherche binaire :

Principe de l'index

Indice BTree :

Introduction à l'initialisation

Le bleu clair s'appelle 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, y compris les pointeurs P1, P2, P3,

P1 représente le disque blocs 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 feuillesc'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 E/S se produira à ce moment-là. 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, ce qui donne 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 élément de données devra être recherché. . Une IO nécessite un total de millions d’IO. Évidemment, le coût est très, très élevé.

Classification des index

Dans InnoDB, les tables sont stockées sous forme d'index selon l'ordre des clés primaires. Les tables ainsi stockées sont appelées tables organisées en index. Et comme nous l'avons mentionné plus tôt, InnoDB utilise le modèle d'index d'arbre B+, donc 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; 
复制代码

Copier après la connexion

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 de deux arbres est le suivant :

Il est facile de voir sur la figure 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 est créée par défaut. C'est pourquoi, avant d'apprendre l'indexation, le professeur nous disait souvent que la recherche basée sur la clé primaire serait plus rapide. . Il s'avère que la clé primaire elle-même L'index est 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é). Le contenu du nœud feuille de

index auxiliaire

index auxiliaire est la valeur de la clé primaire. Dans InnoDB, l'index auxiliaire est également appelé index secondaire (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

Selon le au-dessus de la structure de l'index, discutons d'une question : Quelle est la différence entre les requêtes basées sur l'index de clé primaire et l'index secondaire ?

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 dans l'arborescence B+ de ID
Si l'instruction est select * from T où k= ; 5, qui est une méthode de requête d'index normale, vous devez d'abord rechercher dans l'arbre d'index k pour obtenir la valeur d'ID de 500, puis rechercher à nouveau dans l'arbre d'index d'ID. Ce processus est appelé 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 voulons interroger n'existent dans notre arbre d'index, nous l'appelons actuellement index de couverture, c'est-à-dire que la colonne d'index contient toutes les données que nous voulons interroger.

Dans le même temps, les index secondaires sont divisés selon les types suivants (sautez-le simplement brièvement, nous en apprendrons plus plus tard) :

Clé Unique : L'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'objectif de l'établissement d'un index unique est l'unicité des données dans la colonne d'attribut, plutôt que l'efficacité des requêtes.
Index ordinaire (Index) : La seule fonction d'un index ordinaire est d'interroger rapidement des données. Une table permet la création de plusieurs index ordinaires, et permet la duplication de données et NULL.
Prefix Index (Prefix) : l'index de préfixe n'est applicable qu'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.
Index de texte intégral (Texte intégral) : 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. Il s'agit d'une technologie actuellement utilisée par les bases de données des moteurs de recherche. 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, signifie en faitreport. notre opération de retour de table, MySQL ne le fera pas. Il est facile pour nous de renvoyer la table car c'est du gaspillage. 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' ;
复制代码

Copier après la connexion

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

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

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

最左前缀原则

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

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

直接第一层name都不走，当然都失效
走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有name成功）
同时，这个顺序并不是由我们where中的排列顺序决定，比如： where name='小米科技' and status='1' and address='北京市' where status='1' and name='小米科技' and address='北京市'

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

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

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

索引设计原则

针对表

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

针对字段

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

其他原则

最好用唯一索引，区分度越高，使用索引的效率越高
不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

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

比如：

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

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

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

使用短索引,索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀，比如有N个字段，我们不一定需要创建N个索引，可以用复合索引

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

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

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

Copier après la connexion

举个栗子

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

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

Copier après la connexion

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

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

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

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

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

考虑空间->短索引

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

我们有两种方案：

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

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

何时建立索引

where中的查询字段
查询中与其他表关联的字段，比如外键
排序的字段
统计或分组的字段

何时达咩索引

表中数据量很少
经常改动的表
频繁更新的字段
数据重复且分布均匀的表字段（比如包含了很多重复数据，那此时多叉树的二分查找，其实用处不大，可以理解为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]
复制代码

Copier après la connexion

查找索引

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

select index from tbl_name\G;
复制代码

Copier après la connexion

删除索引

drop INDEX index_name on tbl_name ;
复制代码

Copier après la connexion

变更索引

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， 用于全文索引
复制代码

Copier après la connexion

查看索引使用情况

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

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

Copier après la connexion

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

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

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

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

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

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

总结

索引简单来说就是一个排好序的数据结构，可以方便我们检索数据，而不需要盲目的进行全表扫描。
索引底层有很多种实现结构，这篇主要只是讲解了BTREE索引，如果对树这一数据结构还不太熟悉的小伙伴，可以关注我后续数据结构专栏，会整理关于普通树，二叉树，二叉排序树的文章。
索引分类：
1. 主键索引
2. 辅助索引