Compréhension approfondie des index dans MySQL (utilisation, classification, méthodes de correspondance)

Cet article vous donnera une compréhension approfondie de l'index dans MySQL et présentera les avantages, les utilisations, les classifications, les termes techniques et les méthodes de correspondance de l'index. J'espère qu'il vous sera utile !

Pour un développement avancé, nous devons souvent écrire du SQL complexe, donc pour éviter d'écrire du SQL inefficace, nous devons comprendre certaines connaissances de base en indexation. Grâce à ces connaissances de base, nous pouvons écrire du SQL plus efficace. [Recommandations associées : Tutoriel vidéo mysql]

01 Avantages des index

• Réduisez considérablement la quantité de données que le serveur doit analyser, c'est-à-dire la quantité d'E/S
• Aidez le serveur à éviter tri et tables temporaires (essayez d'éviter le tri des fichiers, utilisez plutôt le tri par index)
• Transformez les E/S aléatoires en E/S séquentielles

02 L'utilité des index

• Trouvez rapidement les lignes qui correspondent à une clause Where
• Si vous pouvez choisir parmi plusieurs index, mysql utilisera généralement l'index qui trouve le moins de lignes
• Si la table a un index multi-colonnes, l'optimiseur peut utiliser n'importe quel préfixe le plus à gauche de l'index pour trouver la ligne
• Quand il y a sont des jointures de table, les données de ligne sont récupérées à partir d'autres tables
• Trouver les valeurs de min et max d'une colonne d'index spécifique
• Trier et regrouper la table si le tri ou le regroupement peut être fait avec le préfixe le plus à gauche de l'index
• Dans certains cas, la requête peut être optimisée pour récupérer des valeurs de données sans Rechercher des lignes de données

03 Classification des index

L'index par défaut créé par la base de données est destiné aux clés uniques

• Index de clé primaire (unique et non vide)
• Index unique (unique et peut être vide)
• Index ordinaire (index des champs ordinaires)
• Index de texte intégral (généralement construit sur les types varchar, char, texte, mais rarement utilisé)
• Index combiné (index construit sur plusieurs mots)

04 indexé Terme technique

1. Retour de table

Le champ de nom est un index ordinaire Recherchez la clé primaire de l'arborescence B+. de la colonne de nom, puis recherchez les données finales de l'arborescence B+ de la clé primaire. Il s'agit d'un retour de table. (Les nœuds feuilles de l'index de clé primaire enregistrent toutes les données de la colonne, mais généralement tous les nœuds feuilles enregistrent l'ID de clé primaire correspondant)

Comme le montre la figure : la structure d'index sql établie par nom dans une table d'utilisation est select * from usewhere name='sun' Tout d'abord, la clé primaire Id=2 correspondant au soleil sera trouvée via le nom d'index de clé non primaire, puis toutes les données de la ligne seront trouvées dans l'index de clé primaire via id=2, et renvoyé C'est la surface de retour. select * from use where name='sun'首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据，并返回，这个就是回表。

2. 覆盖索引

在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。

如上图name索引，sql是 select id,name from user where name ="1" ，id的值在第一步非主键索引就已经有了，就不需要根据ID到主键索引中查询行数据了。

3. 最左匹配

组合索引中 先匹配左边，再继续向后匹配；比如user表中有name+age组成的联合索引，select * from user where name="纪先生" and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。

扩展；

如果是下面两个sql怎么建立索引

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

由于最左匹配原则：只需要建立一个组合索引age+name即可

如果是下面三个sql呢

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

建立name+age和age索引，或者建立age+name和name索引,看着两个都可以。

其实name+age和age更好，因为索引也是需要持久化存储的，占用磁盘空间，读取的时候也是占用内存的，name+age和age+name这两个占用是一样的，但是name和age单独比较，肯定age占用空间更少，name更长（索引越大，IO次数可能更多）

注意！注意！注意！：

在看很多文章的时候，经常看到一些对于最左匹配错误的举例：

如果索引是name+age的组合索引，sql是select * from user where age = 18 and name="纪先生"

2. Index de couverture

Les champs requis peuvent être interrogés sur l'index de clé non primaire sans qu'il soit nécessaire de revenir à la table pour interroger à nouveau, ce qu'on appelle un index de couverture.

🎜🎜Comme le montre l'index de nom dans l'image ci-dessus, le sql est select id,name from user which name = "1" La valeur de id est déjà disponible dans la clé non primaire. index dans la première étape, il n'est donc pas nécessaire de le baser sur l'ID. Accédez à l'index de clé primaire pour interroger les données de ligne. 🎜🎜🎜🎜3. Correspondance la plus à gauche 🎜🎜🎜🎜🎜 Dans l'index combiné, faites d'abord correspondre le côté gauche, puis continuez à faire correspondre vers l'arrière, par exemple, il y a un index conjoint composé de nom+âge dans la table utilisateur, sélectionnez ; * de l'utilisateur où name="Mr. Ji " et age = 18 correspondront à la correspondance la plus à gauche et peuvent être utilisés comme index. Cependant, sélectionnez * auprès de l'utilisateur où age = 18 n'est pas conforme et cet index ne peut pas être utilisé. 🎜🎜🎜Extension ; 🎜🎜Comment créer un index s'il s'agit des deux sql suivants🎜

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

Copier après la connexion

Copier après la connexion

🎜En raison du principe de correspondance le plus à gauche : 🎜Il vous suffit de créer un index combiné âge+nom🎜🎜🎜Et si c'est le suivant trois sql🎜

mysql> alter table staff add index index_n1(first_name,last_name,username);

Copier après la connexion

Copier après la connexion

🎜 Créez des index nom+âge et âge, ou créez des index âge+nom et nom, les deux conviennent. 🎜🎜En fait, 🎜nom+âge et âge sont meilleurs🎜, car l'index doit également être stocké de manière persistante, occupant de l'espace disque et de la mémoire lors de la lecture. Nom+âge et âge+nom occupent la même quantité. séparément, l'âge prend définitivement moins de place et le nom est plus long (plus l'index est grand, plus il y a de fois IO) 🎜🎜🎜Attention ! Avis! Avis! :🎜🎜🎜🎜Lorsque je lis de nombreux articles, je vois souvent quelques exemples d'erreurs de correspondance les plus à gauche : 🎜🎜Si l'index est un index combiné de nom+âge, le sql est select * from user où age = 18 et name ="M. Ji"Beaucoup de gens pensent que cela ne peut pas être indexé, mais en fait c'est possible. L'optimiseur de MySQL optimisera la séquence d'ajustement et l'ajustera à name="Mr. Ji" et age = 18🎜🎜🎜🎜🎜4 Index pushdown🎜🎜🎜🎜🎜Utilisez autant que possible les informations d'index dans l'index combiné pour minimiser le nombre. de fois pour revenir à table🎜

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用，如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说，只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对应很小的表，大部分情况下没有索引，全表扫描更高效；对应中大型表，索引时非常有效的；但是对于超大的表，索引的建立和使用代价也就非常高，一般需要单独处理特大型的表，例如分区，分库，分表等。

更多编程相关知识，请访问：编程视频！！

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!