Comment créer un index MySql

1. Index d'arbre B+

Comme son nom l'indique, un index dont la structure est un arbre B+ est un index d'arbre B+ Dans des circonstances normales, les index réguliers créés dans le moteur InnoDb ont une structure B+.

L'index de l'arbre B+ est le suivant.

1.1. Index clusterisé/Index clusterisé

Lors de la définition de la clé primaire, l'index automatiquement ajouté à la clé primaire est l'index clusterisé, également appelé index clusterisé.

Dans Mysql, les composants sont utilisés pour construire une arborescence B+ Comme le montre la figure, chaque nœud feuille correspond à une clé primaire et à d'autres données associées.

Si nous ne définissons pas de clé primaire lors de la création de la table, Mysql créera automatiquement une clé primaire et l'index correspondant. Le nom de la clé primaire est rowIdrowId

1.2、辅助索引/二级索引

辅助索引，也称为二级索引，是指对于非主键列column创建的索引。同样的，Mysql会为这个索引创建一个B+树，树的叶子节点除了包含这个列column的值以外，就只包含这个列所在行的主键值，这样通过列的索引就可以查到叶子节点，然后叶子节点中的主键信息再从主键的索引中搜索，最终得到一整行的数据。

通过二级索引找到主键，再从主键得到一整行数据的行为叫做回表。

1.3、联合索引/复合索引

1.3.1、什么是复合索引

聚合索引可以说是二级索引的一种特殊情况。一般二级索引都是只对一个非主键的列添加索引，而聚合索引则是一次性对多个列同时添加索引。

一般的二级索引用这样的语句创建：

CREATE INDEX  order_name_index on t_order(order_name);

复合索引则是这样创建：

CREATE INDEX  order_name_and_order_type_index on t_order(order_name, order_type);

对于复合索引，Mysql会也会创建一个B+树，但因为是多个列的索引，所以B+树的排序规则比较特殊，是遵循最左原则。下面会讲到什么是最左原则。

之后叶子节点包含的信息有多个，一个是作为索引的各个列的值，另一个就是主键的值。

1.3.2、最左原则

所谓的最左原则是，B+树的排序规则是根据索引定义时，定义的语句中的列名从左到右进行排序。

比如定义语句如下：

CREATE INDEX  joint_index on t_order(order_name, order_type, submit_time);

那排序规则是先排order_name，如果order_name相同，再排order_type，最后排submit_time。

那当我们查询时，根据定义时列的顺序从左至右，where子句或者order by等子句应该尽量先从order_name开始，然后以此类推。

比如说，我们已经定义了上面的三个列组成的复合索引，那查询或者排序的时候尽量先order_name，再order_type，最后submit_time。

select * from t_order where order_name = 'order1'
and order_type = 1
and submit_time = str_to_date('2022-08-02 00:52:26', '%Y-%m-%d %T')

原因很简单，因为联合索引的排序规则是先排order_name，如果order_name相同，再排order_type，最后排submit_time。所以只有查询排序时也遵循这个规则，我们才能用上索引。

如果我们不完全遵守最左原则，比如查询排序只排两个列，忽略中间那个order by order_name, submit_time。那这个时候Mysql会有智能化的处理，他会自己判断是用索引快还是不用索引快。

1.3.3、联合索引的查询优化

尽量使用到组成联合索引的列，并且保证顺序。可以通过查询索引查看列的顺序。查看sql_in_index

show index from t_order;

查询返回的字段尽量就只返回组成联合索引的列和主键，不要返回其它的列，以免造成回表。
这应该容易理解，因为联合索引的B+树的叶子节点就只包含主键和组成联合索引的列的值，如果返回的字段就这几列，那在一个B+树种查询就完事了。如果还要返回其它的列的话，就又要去主键的索引中查找，有回表操作。

2、哈希索引

一般数据库都会用B+树索引查询数据，但是当数据库使用一段时间后，InnoDB 会记录一些使用频率较高的热数据，然后为这些热数据建立哈希结构的索引，这就是哈希索引的应用场景。

这个索引在Mysql 5.7开始默认开启。

2.1、查看哈希索引的命中率等信息

使用语句：

show engine innodb status;

其中的status

1.2, index auxiliaire/secondaire. index🎜🎜Les index auxiliaires, également appelés index secondaires, font référence aux index créés pour les colonnes de clé non primaire. De même, Mysql créera un arbre B+ pour cet index En plus de la valeur de la colonne, les nœuds feuilles de l'arborescence ne contiennent que la valeur de clé primaire de la ligne où se trouve la colonne. via l'index de colonne.Ensuite, les informations de clé primaire dans le nœud feuille sont recherchées à partir de l'index de clé primaire, et enfin une ligne entière de données est obtenue. 🎜🎜Le fait de trouver la clé primaire via l'index secondaire, puis d'obtenir une ligne entière de données à partir de la clé primaire est appelé retour de table. 🎜🎜🎜🎜1.3, index conjoint/ index composite 🎜

1.3.1. Qu'est-ce qu'un index composite ?

🎜L'index d'agrégation peut être considéré comme un cas particulier d'index secondaire. Généralement, les index secondaires ajoutent uniquement des index à une colonne de clé non primaire, tandis que les index agrégés ajoutent des index à plusieurs colonnes à la fois. 🎜🎜Un index secondaire général est créé avec cette instruction :🎜

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
-- 哈希索引的命中率，可根据这个来决定是否使用哈希索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

🎜Un index composite est créé comme ceci :🎜

select count(distinct id)/count(*) form t_table;

🎜Pour un index composite, Mysql en créera également un Arbre B+, mais comme il s'agit d'un index de plusieurs colonnes, la règle de tri de l'arbre B+ est spéciale et suit le principe le plus à gauche. Quel est le principe le plus à gauche sera discuté ci-dessous. 🎜🎜Après cela, les nœuds feuilles contiennent plusieurs informations. L'une est la valeur de chaque colonne utilisée comme index et l'autre est la valeur de la clé primaire. 🎜

1.3.2. Le principe le plus à gauche

🎜Le principe dit le plus à gauche est que la règle de tri de l'arbre B+ consiste à trier les noms de colonnes dans l'instruction définie de gauche à droite lorsque l'index est défini. . 🎜🎜Par exemple, l'énoncé de définition est le suivant : 🎜

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

🎜La règle de tri consiste à trier order_name en premier, et si order_name est le pareil, puis triez order_type, la dernière ligne est <code>submit_time. 🎜🎜Ensuite, lorsque nous interrogeons, selon l'ordre des colonnes de gauche à droite selon la définition, la clause where ou order by et les autres clauses devraient essayer de démarrer avec order_name Start, et ainsi de suite. 🎜🎜Par exemple, nous avons défini un index composite composé des trois colonnes ci-dessus lors d'une requête ou d'un tri, essayez d'abord order_name, puis order_type et enfin . submit_time. 🎜

alter table order_exp add key(order_note(13));

🎜La raison est très simple, car la règle de tri de l'index conjoint est de trier d'abord order_name, si order_name est le même, puis trier order_type, et enfin submit_time. Ainsi, ce n'est que si cette règle est suivie lors du tri des requêtes que nous pouvons utiliser l'index. 🎜🎜Si nous ne respectons pas entièrement le principe le plus à gauche, par exemple, le tri par requête ne trie que deux colonnes, ignorant celle du milieu order by order_name, submit_time. À ce stade, Mysql disposera d'un traitement intelligent et jugera s'il est plus rapide d'utiliser l'index ou non. 🎜

1.3.3. Optimisation des requêtes de l'index conjoint

🎜 Essayez d'utiliser les colonnes qui composent l'index conjoint et assurez-vous de l'ordre. L'ordre des colonnes peut être consulté en interrogeant l'index. Afficher sql_in_index🎜

CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

🎜🎜🎜Requête renvoyée field Essayez de renvoyer uniquement les colonnes et les clés primaires qui composent l'index conjoint, et ne renvoyez pas d'autres colonnes pour éviter les retours en arrière des tables.
Cela devrait être facile à comprendre, car les nœuds feuilles de l'arbre B+ de l'index conjoint ne contiennent que la clé primaire et les valeurs des colonnes qui composent l'index conjoint si les champs renvoyés ne sont que ceux-ci. colonnes, alors la requête dans un arbre B+ est terminée. Si vous souhaitez renvoyer d'autres colonnes, vous devez rechercher dans l'index de la clé primaire et effectuer une opération de retour de table. 🎜🎜2. Index de hachage🎜🎜Les bases de données générales utilisent des index arborescents B+ pour interroger les données, mais lorsque la base de données est utilisée pendant un certain temps, InnoDB enregistrera certaines données chaudes qui sont utilisées plus fréquemment, puis construira un index de structure de hachage pour ces données chaudes, c'est le scénario d'application de l'index de hachage. 🎜🎜Cet index est activé par défaut à partir de Mysql 5.7. 🎜🎜2.1. Afficher le taux de réussite et d'autres informations de l'index de hachage 🎜🎜Déclaration d'utilisation : 🎜

select cno,fname from customer where lname='xx' and city ='yy' order by fname;

🎜🎜🎜Le statut contient de nombreuses informations, notamment l'index de hachage. Nous copions les informations dans l'éditeur et les visualisons. Cette section contient les informations sur l'index de hachage. 🎜

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
-- 哈希索引的命中率，可根据这个来决定是否使用哈希索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

3、索引的创建策略

3.1、 单列索引的策略

3.1.1、列的类型占用的空间越小，越适合作为索引

因为B+树也是占用空间的，所以在固定空间中，如果列的类型占用的空间越小，那我们一次就能读取更多的B+树节点，这样自然就加快了效率。

3.1.2、根据列的值的离散性

离散性是指数据的值重复的程度高不高，假如有N条数据的话，那离散性就可以用数值表示，范围是1/N 到 1。

比如说某个列在数据库中有下面几条数据（1, 2, 3, 4, 5, 5, 3），其中5和3都有重复，去重后应该是（1, 2, 3, 4, 5）。我们将去重后的条数除以总条数就得到离散性。这里是5/7。列中重复数据较多时，对应的数值较小，而重复数据较少时，数值相应较大。

如果一个列的数据的重复性越低，那么这个列就越适合加索引。

因为索引是需要起到筛选的作用。比如我们有个where条件是where id = 1，如果数据重复性较高，那可能根据索引会返回100条数据，然后我们在根据其他where条件在100条数据中再筛选。

如果数据重复性较低，那可能就只返回1条数据，那之后的运算量明显小得多。

所以一个列的数据离散性越高，那这个列越适合添加索引。

我们可以用下面的语句得到某个列的离散性程度。

select count(distinct id)/count(*) form t_table;

3.1.3、前缀索引

前缀索引和后缀索引：

有些列的值比较长，比如一些备注日志信息也会记录在数据库当中，这类信息的长度往往比较长，如果我们需要对这类列加索引，那索引并不是索引字符串的全部长度。这时候我们就可以建立前缀索引，即对字符串的前面几位建立索引。

所以前缀索引就是建立范围更小索引，选择一个好前缀位数就能有一个更好的查询效率。

不过有一些缺点，就是这类索引无法应用到order by和group语句上。

Mysql没有后缀索引，如果非要实现后缀索引，那在数据存储时我们应该将数据反转，这样就能用前缀索引达到后缀索引的效果。后缀索引的一个经典应用就是邮箱，快速查询某种类型的邮箱。

选择前缀索引的位数：

这里的逻辑和列的离散性类似，我们需要看看字符串的前面几位的子字符串的离散性如何。比如对于下面的表，内容是电影票的相关信息，我们需要对order_note建立前缀索引。

来比较一下各个位的子字符串的离散性。

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

可以看出，前面几位的子字符串的离散程度较低，后面sel13开始就比较高，那我们可以根据实际情况，建立13~15位的前缀索引。

建立前缀索引SQL语句：

alter table order_exp add key(order_note(13));

3.1.2、只为搜索、排序和分组的列建索引

这个理由很简单，不解释了。

3.2、 多列索引的策略

3.2.1、离散性最高的列放前面

原因很简单，查询时根据定义复合索引时的列的顺序来查询的，离散性高的列放在前面的话，就能更早的将更多的数据排除在外。

3.2.2、三星索引

三星索引是一种策略。有三种条件，满足一条则索引获得一颗星，三颗星则是很好的索引。

三条策略分别是

索引将相关记录放在一起。

意思是查询需要的数据在索引树的叶子节点中连续或者足够靠近。举个例子，下面是某个索引的B+树。查询所需数据仅在叶节点的前两个范围内，即0000至a。这很明显，后面的片我们就没必要再去查询了，这无疑增加了效率。当所需数据分布在每个片上时，查询次数就会显著增加。

所以查询需要的数据在叶子节点上越连续，越窄就越好。

索引中的数据顺序与查找中的数据排序一致。

这容易理解，讲解联合索引中说过，B+树的排序顺序和索引中的数据一样，所以查询时的where的数据顺序越贴近索引中的顺序，就越能更好地利用B+树。

索引的列包含查询中的所有列。

这个可以避免回文操作，不多解释。

三星索引的权重：

一般来说第三个策略权重占到50%，之后是第一个策略27%， 第二个策略23%。

三星索引实例：

CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

我们创建以上的索引，那么对于下面的查询语句，这个索引就是三星索引。

select cno,fname from customer where lname='xx' and city ='yy' order by fname;

首先，查询条件中有lname=’xx’ and city =’yy’，这条件让我们这需要在lname=’xx’ and city =’yy’的那一片B+树的叶子节点中查询，让我们的查询变窄了很多，并且这部分的数据是连续的，因为B+树是先根据city排序，再根据lname查询。

另外，因为已经锁定lname=’xx’ and city =’yy’，所以这部分的数据是根据fname和cno排序。查询语句正好是根据`fname```排序，所以第二点也满足。

最后是查询的结果都包含正在索引中，不会有回文，第三点也满足，所以这个索引是三星索引。

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!