MySql indexe les choses

Aujourd'hui, nous parlerons des index MySql. Dans cet article, je parlerai principalement de la structure des données des index sous InnoDB, du fonctionnement des index et de la manière de mieux utiliser les index pour améliorer l'efficacité.

1. Qu'est-ce qu'un index

Un index de base de données est une structure de données triée dans un système de gestion de base de données pour aider à interroger et mettre à jour rapidement les données dans la base de données. des tableaux. Tout comme la table des matières du dictionnaire Xinhua que nous utilisions auparavant, elle peut nous aider à rechercher rapidement un certain mot.

2. Classification de l'index

3. Analyse des exemples d'index (en prenant InnoDB comme exemple)

3.1 La structure de l'index sous InnoDB

Sous InnoDB, les tables sont stockées sous forme d'index selon les ordre des clés primaires.Cette méthode de stockage des données est également appelée index clusterisé.Le "clustering" signifie que les lignes de données et les valeurs de clé adjacentes sont stockées ensemble de manière compacte, c'est-à-dire que les lignes de données sont réellement stockées dans la feuille. pages de l'index. Nous créons une table pour illustrer réellement la structure de l'index sous InnoDB. L'instruction de création de table est la suivante :

create table person(id int primary key, 
age int not nullindex (age)engine=InnoDB;

Ensuite, nous insérons cinq éléments de données : (1,15), (2,17), (6,20),(10,18),(19,21), la structure arborescente de l'index est la suivante :

La figure ci-dessus montre deux parties du contenu , le premier chiffre Pour le contenu de l'index clusterisé (index de clé primaire), vous pouvez voir que les données sont triées en fonction de la taille de l'Id, et l'index correspondant contiendra toute la ligne de données de l'index.

La deuxième image montre le diagramme de structure de l'index utilisant l'âge comme index, qui est un index non clusterisé (index de clé non primaire). Vous pouvez voir que l'index est trié par âge, mais contrairement au primaire. key index, age L'index correspond à l'Id, nous pouvons donc savoir que le contenu de l'enregistrement d'index de clé non primaire est la valeur de l'index de clé primaire.

Certains étudiants ici peuvent avoir des questions. Si je ne précise pas la clé primaire lors de la création de la table, quelle est la structure de l'index ? En fait, dans InnoDB, si la clé primaire n'est pas définie, alors il choisira à la place un index unique non vide. S'il n'existe pas un tel index, il définira implicitement une clé primaire comme un index clusterisé. Ainsi, peu importe que vous définissiez une clé primaire ou non, InnoDB vous aidera toujours à indexer les données sous la forme de la figure ci-dessus. Ensuite, nous analysons le processus de requête d’index.

3.2 Analyse des requêtes d'index

Supposons que nous exécutions une instruction de requête select * from person où ID = 6. Étant donné que la requête d'ID de clé primaire est directement utilisée, l'index de clé primaire sera utilisé depuis. l'index de clé primaire Toutes les données de la ligne entière sont directement liées, le moteur n'a donc besoin de l'exécuter qu'une seule fois pour interroger les résultats.

Si l'instruction SQL exécutée est un index de clé non primaire

select * from person where age = 18

Le processus de recherche de l'index de clé primaire à partir de l'index ordinaire, puis d'interrogation des données est appelé retour de table. Étant donné que le renvoi de la table nécessite une requête supplémentaire, c'est pourquoi les index de clé primaire sont plus rapides que les index ordinaires. Par conséquent, nous devrions essayer d'utiliser autant que possible les requêtes de clé primaire. L'instruction ci-dessus utilisera l'index d'âge ordinaire. L'index recherche d'abord les enregistrements d'index égaux à 18 en fonction de l'âge, trouve l'enregistrement avec ID = 10, puis recherche une fois l'index de clé primaire, puis supprime le données qui doivent être interrogées.

3.3 Index de couverture

Nous créons généralement des index basés sur la condition Where de la requête, mais ce n'est que notre pratique habituelle. Sur la base de l'analyse ci-dessus, nous pouvons savoir que si. nous voulons Si vous souhaitez avoir une efficacité de requête élevée, utilisez d'abord l'index de clé primaire et, deuxièmement, évitez de renvoyer la table, c'est-à-dire que vous pouvez obtenir autant que possible les données souhaitées dans l'index. Si un index contient les champs qui doivent être interrogés, nous l'appelons alors un « index de couverture ».

Alors comment créer un index de couverture ? La réponse est d'y parvenir grâce à un index conjoint. Les champs à interroger sont couverts par les champs de l'index conjoint, afin d'obtenir l'effet de couverture de l'index.

我们把上面的建表语句改造下，来分析下如何实现覆盖索引。

 CREATE TABLE `person` (  `id` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  `sex` varchar(1) DEFAULT NULL,

上面我创建了一个name和age的联合索引，索引结构图表示如下：

我们根据图可以知道，联合索引是和创建索引字段顺序有关的，上面这个例子就是先以name排序，然后name相同再以age为标准排序。那么我们建表后该如何达到覆盖索引的效果呢？相信有些同学已经知道了怎么写sql可以达到覆盖索引效果，sql如下：

select name,age from person where name = "Barry"

因为我们需要查询的字段name和age，都在索引中可以直接查询到了，所以不需要查找到主键ID，然后再回表了。

看到这里，肯定有同学会说，既然这样的话，我把所有需要查询的字段组合都建上联合索引不就行了吗？答案是：不行。因为索引也是需要消耗空间的，而且维护索引也是需要成本的，这一点我会在后面的优缺点中提到。那么有没有别的方式可以尽可能的实现不回表的效果呢？这里我们就要引入MySql的最左前缀原则了。

什么叫最左前缀原则呢？就是在索引的匹配中，可以以索引的最左N个字段,也可以是字符串索引的最左N个字符。比如在上图中，要查询以A开头的名字，查询语句就是

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select name from person where name like &#39;A%&#39;</span><br/>

这个时候就可以满足最左前缀规则来使用索引查询了，这里就会依赖索引查询到第一个首字母是A的名字，然后向后遍历，直到不满足条件为止。

那么最左N个字段是什么意思呢？意思就是索引（name,age),可以直接利用 name来当做单独索引使用，可以只使用联合索引的部分字段，但是必须是顺序一致，比如索引（a,b,c)，如果要想使用最左前缀规则，可以使用索引a,ab。

我们也可以利用该规则来少维护一个或多个索引，比如我们需要 a,ab,abc的查询，那就只需要(a,b,c)联合索引就满足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一个新特性，叫做“索引条件推送（index condition pushdown)”，这也称为索引下推。那么索引下推是这个什么东东呢？其实从“索引条件推送”这个名字就可以表明，这个特性是可以在索引中的字段进行条件判断，然后过滤不满足条件的记录，减少回表的次数。

比如以上图中的数据为准，sql如下：

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select * from person where name like &#39;A%&#39; and age =19;</span><br/>

那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。

在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。

3.5 索引的优点与缺点

说了这么多关于索引的内容，我们来谈谈索引的优缺点。

优点：

减少服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机IO变为顺序IO

缺点

索引会占用额外的存储空间索引的维护需要一定的成本，插入数据后需要保证原来的索引有序，所以也会影响一定的数据库性能。

五、总结

Dans cet article de blog, j'ai principalement parlé de la définition de l'index, de la classification des index et des types courants d'index selon différentes perspectives. Ensuite je me suis concentré sur la structure des données de l'index indexé sous InnoDB. La différence entre l'index de clé primaire et l'index de clé non primaire est que l'interrogation de l'index de clé primaire peut renvoyer directement des données. L'index de clé non primaire doit d'abord interroger l'ID de clé primaire, puis interroger les données. Ce processus est appelé retour de table. . Nous pouvons réduire le nombre de retours de table en couvrant les index, améliorant ainsi les performances. Après mysql5.6, InnoDB peut prendre en charge le pushdown d'index. Lors de l'utilisation d'un index conjoint, si les conditions peuvent être jugées dans l'index, les lignes qui ne remplissent pas les conditions seront filtrées dans l'index, réduisant ainsi le nombre de retours de table.

6. Référence

"High Performance MySql" 3ème édition

Colonne "MySql45 Lecture"

【 Recommandé cours : Tutoriel vidéo 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!