Exemple détaillé du principe de correspondance de l'index MySQL le plus à gauche

Cet article vous apporte des connaissances pertinentes sur mysql Le principe de correspondance le plus à gauche signifie que dans l'index conjoint, si votre instruction SQL utilise l'index le plus à gauche dans l'index conjoint, alors cette instruction SQL Vous pouvez utiliser cet index conjoint pour effectuer la correspondance. Ce qui suit présente des informations pertinentes sur le principe de correspondance le plus à gauche de l'index MySQL. J'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Préparation

Pour les instructions ultérieures, nous créons d'abord une table comme suit (MySQL5.7), avec un total de 5 champs dans la table (a, <code>b, c, d, e), où a is La clé primaire a un index commun composé de b, c et d Le moteur de stockage est InnoDB. Trois éléments de données de test sont. inséré. Il est fortement recommandé d'essayer toutes les instructions de cet article dans MySQL. a、b、c、d、e），其中a为主键，有一个由b，c，d组成的联合索引，存储引擎为InnoDB，插入三条测试数据。强烈建议自己在MySQL中尝试本文的所有语句。

CREATE TABLE `test` (
  `a` int NOT NULL AUTO_INCREMENT,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  `e` int DEFAULT NULL,
  PRIMARY KEY(`a`),
  KEY `idx_abc` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (1, 2, 3, 4, 5);
INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (2, 2, 3, 4, 5);
INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (3, 2, 3, 4, 5);

这时候，我们如果执行下面这个SQL语句，你觉得会走索引吗？

SELECT b, c, d FROM test WHERE d = 2;

如果你按照最左匹配原则（简述为在联合索引中，从最左边的字段开始匹配，若条件中字段在联合索引中符合从左到右的顺序则走索引，否则不走，可以简单理解为(a, b, c)的联合索引相当于创建了a索引、(a, b)索引和(a, b, c)索引），这句显然是不符合这个规则的，它走不了索引，但是我们用EXPLAIN语句分析，会发现一个很有趣的现象，它的输出如下是使用了索引的。

这就很奇怪了，最左匹配原则失效了吗？事实上，并没有，我们一步步来分析。

理论详解

由于现在基本上以InnoDB引擎为主，我们以InnoDB为例进行主要说明。

聚集索引和非聚集索引

MySQL底层使用B+树来存储索引，数据均存在叶子节点上。对于InnoDB而言，主键索引和行记录时存储在一起的，因此叫做聚集索引（clustered index）。除了聚集索引，其他所有都叫做非聚集索引（secondary index），包括普通索引、唯一索引等。

在InnoDB中，只存在一个聚集索引：

• 若表存在主键，则主键索引就是聚集索引；
• 若表不存在主键，则会把第一个非空的唯一索引作为聚集索引；
• 否则，会隐式定义一个rowid作为聚集索引。

我们以下图为例，假设现在有一个表，存在id、name、age三个字段，其中id为主键，因此id为聚集索引，name建立索引为非聚集索引。关于id和name的索引，有如下的B+树，可以看到，聚集索引的叶子节点存储的是主键和行记录，非聚集索引的叶子节点存储的是主键。

回表查询

从上面的索引存储结构来看，我们可以看到，在主键索引树上，通过主键就可以一次性查出我们所需要的数据，速度很快。这很直观，因为主键就和行记录存储在一起，定位到了主键就定位到了所要找的包含所有字段的记录。

但是对于非聚集索引，如上面的右图，我们可以看到，需要先根据name所在的索引树找到对应主键，然后通过主键索引树查询到所要的记录，这个过程叫做回表查询。

索引覆盖

上面的回表查询无疑会降低查询的效率，那么有没有办法让它不回表呢？这就是索引覆盖。所谓索引覆盖，就是说，在使用这个索引查询时，使它的索引树的叶子节点上的数据可以覆盖你查询的所有字段，就可以避免回表了。我们回到一开始的例子，我们建立的(b,c,d)

EXPLAIN SELECT * FROM test WHERE b = 1;

À ce stade, si nous exécutons l'instruction SQL suivante, pensez-vous qu'elle sera indexée ?

EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2;

Si vous suivez le principe de correspondance le plus à gauche (simplement énoncé comme dans l'index conjoint, commencez la correspondance à partir du champ le plus à gauche, si les champs de la condition correspondent à l'ordre de gauche à droite dans l'index conjoint, puis accédez à l'index, sinon, on peut simplement comprendre que l'index conjoint de (a, b, c) équivaut à créer un index, (a, b) index et (a, b, c) index). respecter cette règle. Il n'y a pas d'index, mais si nous utilisons l'instruction EXPLAIN pour analyser, nous trouverons un phénomène très intéressant. La sortie suivante utilise l'index.

C'est très étrange, la correspondance la plus à gauche a le principe a expiré ? En fait non, analysons cela étape par étape.

Explication théorique détaillée

Étant donné que le moteur InnoDB est désormais fondamentalement le moteur principal, nous utilisons InnoDB comme exemple pour l'explication principale.

Index clusterisé et index non cluster🎜🎜La couche inférieure de MySQL utilise l'arborescence B+ pour stocker l'index, et les données sont stockées sur les nœuds feuilles. Pour InnoDB, l'index de clé primaire et les enregistrements de ligne sont stockés ensemble, c'est donc ce qu'on appelle un index clusterisé. À l'exception de l'index clusterisé, tous les autres sont appelés index non clusterisés (index secondaire), y compris les index ordinaires, les index uniques, etc. 🎜🎜Dans InnoDB, il n'y a qu'un seul index clusterisé : 🎜

• Si la table a une clé primaire, l'index de clé primaire est un index clusterisé
• Si la table n'a pas de clé primaire ; clé primaire, la première sera Un index unique non vide est utilisé comme index clusterisé ;
• Sinon, un rowid est implicitement défini comme un index clusterisé.

🎜 Prenons la figure suivante comme exemple. Supposons qu'il y ait une table avec trois champs : id, name et age. ID est la clé primaire, donc id est un index clusterisé et name est. indexé en tant qu’index non clusterisé. Concernant l'index de l'id et du nom, il existe l'arborescence B+ suivante. Vous pouvez voir que les nœuds feuilles de l'index clusterisé stockent la clé primaire et les enregistrements de ligne, et que les nœuds feuilles de l'index non cluster stockent la clé primaire. 🎜🎜🎜🎜Requête de retour à la table🎜🎜De ce qui précède index En regardant la structure de stockage, nous pouvons voir que sur l'arborescence d'index de clé primaire, les données dont nous avons besoin peuvent être trouvées en une seule fois via la clé primaire, ce qui est très rapide. C'est très intuitif, car la clé primaire est stockée avec l'enregistrement de ligne. Une fois la clé primaire localisée, l'enregistrement contenant tous les champs que vous recherchez est localisé. 🎜🎜Mais pour les index non clusterisés, comme le montre l'image de droite ci-dessus, nous pouvons voir que nous devons d'abord trouver la clé primaire correspondante en fonction de l'arborescence d'index où se trouve le nom, puis interroger l'enregistrement souhaité via la clé primaire. arbre d'indexation. Ce processus est appelé requête de table. 🎜🎜Couverture de l'index🎜🎜La requête de retour de table ci-dessus réduira sans aucun doute l'efficacité de la requête, existe-t-il donc un moyen de l'empêcher de renvoyer la table ? Il s’agit d’une couverture indicielle. Ce qu'on appelle la couverture d'index signifie que lorsque vous utilisez cet index pour interroger, les données sur les nœuds feuilles de son arbre d'index peuvent couvrir tous les champs que vous interrogez, afin que vous puissiez éviter de renvoyer la table. Revenons à l'exemple initial. Nous avons établi l'index conjoint de (b,c,d), donc lorsque les champs que nous interrogeons sont en b, c, d, la table ne sera pas renvoyée. , vous n'avez besoin de regarder l'arborescence d'index qu'une seule fois, c'est la couverture de l'index. 🎜🎜Le principe de correspondance le plus à gauche🎜🎜 fait référence à l'index de la colonne la plus à gauche dans l'index conjoint. Il en va de même pour les index conjoints sur plusieurs champs. Par exemple, l'index conjoint index(a,b,c) équivaut à créer un index à une seule colonne, un index conjoint (a,b) et un index conjoint (a,b,c). 🎜🎜Nous pouvons exécuter les instructions suivantes pour vérifier ce principe. 🎜

EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2 and d = 3;

🎜🎜🎜

EXPLAIN SELECT * FROM test WHERE d = 3;

🎜🎜🎜

EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2 and d = 3;

接着，我们尝试一条不符合最左原则的查询，它也如图预期一样，走了全表扫描。

EXPLAIN SELECT * FROM test WHERE d = 3;

详细规则

我们先来看下面两个语句，他们的输出如下。

EXPLAIN SELECT b, c from test WHERE b = 1 and c = 1;
EXPLAIN SELECT b, d from test WHERE d = 1;

id|select_type|table|partitions|type|possible_keys|key    |key_len|ref        |rows|filtered|Extra      |
--+-----------+-----+----------+----+-------------+-------+-------+-----------+----+--------+-----------+
 1|SIMPLE     |test |          |ref |idx_bcd      |idx_bcd|10     |const,const|   1|   100.0|Using index|
i
d|select_type|table|partitions|type |possible_keys|key    |key_len|ref|rows|filtered|Extra                   |
--+-----------+-----+----------+-----+-------------+-------+-------+---+----+--------+------------------------+
 1|SIMPLE     |test |          |index|idx_bcd      |idx_bcd|15     |   |   3|   33.33|Using where; Using index|

显然第一条语句是符合最左匹配的，因此type为ref，但是第二条并不符合最左匹配，但是也不是全表扫描，这是因为此时这表示扫描整个索引树。

具体来看，index 代表的是会对整个索引树进行扫描，如例子中的，列 d，就会导致扫描整个索引树。ref 代表 mysql 会根据特定的算法查找索引，这样的效率比 index 全扫描要高一些。但是，它对索引结构有一定的要求，索引字段必须是有序的。而联合索引就符合这样的要求，联合索引内部就是有序的，你可以理解为order by b,c,d这种排序规则，先根据字段b排序，再根据字段c排序，以此类推。这也解释了，为什么需要遵守最左匹配原则，当最左列有序才能保证右边的索引列有序。

因此，我们总结最后的原则为，若符合最左覆盖原则，则走ref这种索引；若不符合最左匹配原则，但是符合覆盖索引（index），就可以扫描整个索引树，从而找到覆盖索引对应的列，避免回表；若不符合最左匹配原则，也不符合覆盖索引（如本例的select *），则需要扫描整个索引树，并且回表查询行记录，此时，查询优化器认为这样两次查找索引树，还不如全表扫描来得快（因为联合索引此时不符合最左匹配原则，要不普通索引查询慢得多），因此，此时会走全表扫描。

补充：为什么要使用联合索引

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

推荐学习：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!