Mysql peut compter sur l'indexation, mais je ne peux compter que sur un travail à temps partiel....

La colonne

tutoriel mysql présente les index associés.

1. Structure des données de l'index

Cette question sera certainement posée lors de l'entretien Pourquoi MySQL choisit-il l'arbre b+ comme index ? Au lieu de choisir un autre index, comme un b-tree ? hacher?

Le disque IO mentionné ci-dessous fait référence à l'opération de chargement des données du disque dur dans la mémoire

• Hash index, ne prend pas en charge la requête par plage, car le hachage est une clé correspondant à une valeur, il n'y a aucun moyen de requête par plage

• Pour un arbre binaire , sa caractéristique est que le sous-arbre gauche est plus petit que le nœud racine et plus petit que le sous-arbre droit s'il y en a un. problème avec la valeur du nœud racine, il est possible qu'il dégénère en une liste chaînée, c'est-à-dire que l'arbre ne bifurquera pas, et l'arbre ira toujours vers la gauche ou la droite, de sorte qu'il ne puisse pas chercher en deux pour réduire le nombre d'E/S. Les requêtes par plage ne sont pas prises en charge. Si des requêtes par plage sont utilisées, elles doivent être parcourues à partir de la racine à chaque fois. Plus l'arborescence est haute, plus les opérations d'E/S sont fréquentes, ce qui gaspille des ressources. 🎜>

• Si vous équilibrez l'arbre binaire, il n'y aura pas d'arbre binaire. Cela a l'inconvénient de dégénérer en liste chaînée, car le. la différence entre ses nœuds enfants gauche et droit est d'au plus 1 niveau, mais il ne prend pas en charge la recherche par plage, ce qui est le même que le problème des arbres binaires

• B-tree Comparé à un arbre binaire, l'arbre est très court et gros, et les opérations d'E/S sont réduites. Il s'agit d'un arbre multi-fork, et chaque nœud stocke la ligne de données correspondante. si les données de cette ligne Au fur et à mesure que les colonnes continuent d'augmenter, le nombre de nœuds stockés sur cette page diminuera, car l'espace occupé continuera d'augmenter, l'arborescence deviendra de plus en plus haute et le nombre d'opérations d'E/S augmentera. Dans le même temps, les recherches par plage ne sont pas prises en charge. Ce serait mieux si la même taille d'espace pouvait stocker beaucoup de données de nœuds, il y avait donc l'arbre b+ suivant

• arbre b+ Ses nœuds non-feuilles stockent uniquement les données d'index, pas la ligne entière de données, mais les nœuds feuilles sont des nœuds non-feuilles redondants et redondants, et les nœuds feuilles sont également liés à une liste doublement chaînée, ce qui facilite la recherche séquentielle, b+ Comparé au b-tree, l'arbre est plus volumineux et a moins de temps d'E/S disque

2. Types d'index dans MySQL

• Index clusterisé et index non clusterisé

On peut simplement comprendre comme L'index clusterisé est l'index de clé primaire et l'index non clusterisé est l'index ordinaire

La différence essentielle est

Index clusterisé Les nœuds feuilles stockent la ligne entière de données

innodb implémente l'index clusterisé via la clé primaire. S'il n'y a pas de clé primaire, alors il choisira un index unique non vide à implémenter. S'il n'y a pas de clé primaire, il le générera implicitement. Une clé primaire pour implémenter un index clusterisé

L'index non clusterisé stocke la valeur de l'index et la valeur de la clé primaire

• Index ordinaireUne table peut avoir plusieurs index ordinaires, et un index peut être établi sur n'importe quel champ. La plupart des index que nous créons habituellement sont des index ordinaires

• Index communUn index créé en combinant plusieurs champs

• Index unique Les champs uniques de l'entreprise sont adaptés à l'établissement d'index uniques. Il peut y avoir plusieurs index uniques dans une table

• Index de clé primaire et unique Tout comme l'index, l'index de clé primaire est également unique. La différence est qu'une table ne peut avoir qu'un seul index de clé primaire

. 3. À propos des index sql

Créer un index de clé primaire

ALTER TABLE test add  PRIMARY  KEY (id)复制代码

Créer un index unique

ALTER TABLE test add UNIQUE idx_id_card(id_card)复制代码

Créer un index normal

ALTER TABLE test add INDEX idx_name(name)复制代码

Créer un index commun

ALTER TABLE test add INDEX idx_age_name(age,name)复制代码

Modifier le nom de l'index : supprimer d'abord puis ajouter

Supprimer l'index (deux méthodes)

ALTER TABLE test DROP INDEX idx_id_cardDROP INDEX idx_id_card on test --删除主键索引DROP PRIMARY key on test  ALTER TABLE test DROP  PRIMARY key复制代码

Afficher l'index dans le tableau

SHOW INDEX FROM test复制代码

Indice d'analyse

EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码

我们先给name字段添加一个索引,索引名字叫做idx_name

ALTER TABLE test add INDEX idx_name(name)复制代码

查看test表中的索引

SHOW INDEX FROM test复制代码

其中的属性

• table: 表名

• Non_unique: 能重复的话为1，不能重复的话为0，我们主键的那里是0，而name那里是1，因为name可以重复，而主键不能重复

• Key_name: 索引名称

• Seq_in_index:索引中列的顺序

• Column_name:列名称

• Collation:列以什么方式存储的，A升序，null无序

• Cardinality:数目越大，则使用该索引的可能性越大

• Sub_part:如果列只是部分的编入索引，则被编入索引的字符数目，如果整列被编入索引，则为null

• Packed：关键字是否被压缩，null表示没有被压缩

• Null:如果该列含有null,则为yes,如果没有null,则为no

• Index_type:索引数据结构

• Comment:多种评注

四、回表查询

select * from test where  name = "xhJaver"复制代码

假如说我们name字段建立了索引，然后当我们运行这一句sql语句的时候，因为建立的是普通索引，所以我们的b+树的叶子节点存储的数据是id，我们会找到name是xhJaver的这条记录的id，再根据这个id,去主键索引的那棵b+树去查询，查询到叶子节点时即查询出这条记录，可见这个过程中，我们从一棵树跑到了另一棵树继续查，这样就叫做“回表查询”，那有没有办法只查一棵树就可以查询出结果呢？

五、覆盖索引

办法当然是有的啦，那就是覆盖索引，我们注意到，刚才这个sql语句时查询出来了所有元素，假如说我们这样写的话

select address from test where  name = "xhJaver"复制代码

假如说我们建立的索引是（name,address）那么这个时候（name,address）这棵b+树的叶子节点存储的数据就包括address了，此时就不需要再根据name = "xhJaver"的id去第二棵树查了，这样就避免了回表查询

六、最左匹配原则

假如说现在我们写一个这样的sql语句

select *  from test where  name = "xhJaver" and age =23  and address="京东"复制代码

并且我们建立的索引是（name,address,age）这样是会用到（name,address,age）索引的，可是如果要这样写的话

select *  from test where  name = "xhJaver" and age >23  and address="京东"复制代码

这样只会用到（name，age）这两个索引，从左边开始匹配，如果要是遇到范围查询的话，则不继续往右匹配索引

七、explain分析索引语句

我们用explain语句解析一下下面这条sql语句

EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码

它的属性有

id: 执行的顺序

• id相同时，顺序从上到下执行
• id不同时，id大的先执行

select_type: 查询的类型

• primary: 最外层的查询被标记为primary
• simple: 简单查询，没有关联其他表，就一张表
• subquery: 在where或者select中的子查询
• derived: 衍生虚拟表 例如from(子查询) t,这个子查询的结果就被放在虚拟表t中

table: 关于哪张表的

partitions: 分区相关（还没搞懂呜呜呜）

type:访问类型

性能由好至坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，好的sql查询至少达到range级别，最好能达到ref

• système : il n'y a qu'une seule ligne de données dans la table

• const : la requête constante est habituellement utilisé pour comparer la clé primaire est égale à Une constante, qui peut être trouvée dans une requête en utilisant l'index

• eq_ref : Index unique, chaque index correspond à un morceau de données, telles que l'index de clé primaire

• ref : index non unique, chaque index peut correspondre à plusieurs lignes de données, telles que l'index ordinaire

• range : requête de plage, utilisant >, <, dans, entre et d'autres requêtes

• index : analyse complète de la table, mais traverse toute l'arborescence d'index

• all : analyse complète de la table, aucun index n'est utilisé

possible_keys : requête S'il y a un index sur le champ, il sera affiché

key : L'index spécifique utilisé. index est utilisé, possible_keys est nul et ne sera affiché que dans la clé

key_len : le nombre d'octets utilisés dans l'index, la longueur maximale possible, pas la longueur réelle de key_len. calculé en fonction de la définition de la table, non récupéré de la table

ref : Indique quel champ est utilisé pour l'indexation

lignes : estime approximativement le nombre de lignes qui doivent être lues

filtrées : affiche l'estimation en pourcentage du nombre de lignes filtrées par la condition.

Extra :

• Utilisation du tri de fichiers : Le tri que MySQL ne peut pas effectuer à l'aide de l'index est appelé tri de fichiers

• Utilisation de temporaire : une table temporaire est utilisée pour stocker les résultats intermédiaires. MySQL utilise une table temporaire lors du tri des résultats de la requête, ce qui est courant dans l'ordre et le regroupement par

  .

• Using index：使用了覆盖索引，查询内容在索引内

  1. 如果出现了Using where，表示对查询出来的数据进行了过滤
  2. 如果没有出现Using where，表示对查询出来的数据没有进行过滤

• 只有Using where 查询内容不在索引内，且对查出来的数据进行了过滤

1. EXPLAIN SELECT (select student.id from student WHERE student.`name`="xhJaver") FROM teacher2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`="xhJaver") 
复制代码

我们写几个sql语句实际分析下 1.SELECT后面2.where后面

我们就拿后面这个图来实战分析一下,挑几个重要的属性说一下

select_type：

• 我们最外层的查询是 from teacher 所以table为teacher的那个表的select_type就是primary

• select/where后面的括号中的查询语句中的表是student，所以table为student的那个表的select_type就是subquery

table: 这条sql查询用到的表

type: 访问类型

• 第一行const : teacher.id =巴拉巴拉巴拉（这个是常数）主键和常数比较时，这个表最多有一个匹配数据，只读取一次

• 第二行ref：代表用到了普通索引，就是这个索引name和xhJaver匹配,可能匹配到很多相同的值

possible_key: 代表可能用到的索引，但是不一定会用到

key: 代表用到的索引， 用到了idx_name，PRIMARY索引

ref: 这一列显示了在key列记录的索引中，表查找值所用到的列或常量， 常见的有：const，字段名

extra：

• using index: 一般是使用了覆盖索引，看我们这个sql语句，

select student.id from student WHERE student.`name`="xhJaver"复制代码

name字段有索引，查询的是id，b+树叶子节点存的数据就是id，所以不需要回表查询了，用到了覆盖索引

八、索引失效原因

1. 遇到范围查询（>,<,like,beetwon），右边的索引列会失效

2. 索引字段不能有函数操作或者不能是表达式的一部分

3. 索引字段隐式类型转换 索引字段类型是string，我们传进来个int

4. 使用时or,is null ,is not null , !=, <>, like "%xxx" 索引会失效

但是用覆盖索引就可以解决 like左模糊查询走不到索引的情况 如果只select索引字段，或者select索引字段和主键，也会走索引的。

更多相关免费学习推荐：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!