Quel est le principe du préfixe le plus à gauche de l'index Mysql ?

Avant-propos

La raison pour laquelle il y a cet index de préfixe le plus à gauche

En dernière analyse, c'est la structure de base de données B+ arborescente de mysql

Dans des problèmes pratiques, par exemple,

index (a, b, c) a trois champs,

utilisez l'instruction de requête select * from table Where c = '1', l'instruction SQL n'utilisera pas l'index index select * from table where c = '1' ，sql语句不会走index索引的

select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引

1. 定义

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

为了更好辨别这种情况，通过建立表格以及索引的情况进行分析

2. 全索引顺序

建立一张表，建立一个联合索引，如果顺序颠倒，其实还是可以识别的，但是一定要有它的全部部分

建立表

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

建立索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

索引的顺序位name-age-pos

显示其索引有没有show index from staffs;

通过颠倒其左右顺序，其执行都是一样的

主要的语句是这三句

1. explain select *from staffs where name='z3'and age=22 and pos='manager';

2. explain select *from staffs where pos='manager' and name='z3'and age=22;

3. explain select *from staffs where age=22 and pos='manager' and name='z3';

以上三者的顺序颠倒，都使用到了联合索引

最主要是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划

不论以何种顺序都可使用到联合索引

3. 部分索引顺序

3.1 正序

如果是按照顺序（缺胳膊断腿的），都是一样的

1. explain select *from staffs where name=‘z3’;

2. explain select *from staffs where name='z3’and age=22;

3. explain select *from staffs where name='z3’and age=22;

其type都是ref类型，但是其字段长度会有微小变化，也就是它定义的字长长度变化而已

3.2 乱序

如果部分索引的顺序打乱

1. 只查第一个索引explain select *from staffs where name='z3';

2. 跳过中间的索引 explain select *from staffs where name='z3' and pos='manager';

3. 只查最后的索引 explain select *from staffs where pos='manager';

可以发现正序的时候

如果缺胳膊少腿，也是按照正常的索引

即使跳过了中间的索引，也是可以使用到索引去查询

但是如果只查最后的索引

type就是all类型，直接整个表的查询了（这是因为没有从name一开始匹配，直接匹配pos的话，会显示无序，）

有些时候type就是index类型，这是因为还是可以通过索引进行查询

index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描

4. 模糊索引

类似模糊索引就会使用到like的语句

所以下面的三条语句

如果复合最左前缀的话，会使用到range或者是index的类型进行索引

1. explain select *from staffs where name like '3%'; 最左前缀索引，类型为index或者range

2. explain select *from staffs where name like '%3%'; 类型为all，全表查询

3. explain select *from staffs where name like '%3%';

   select * from table Where b =‘1’ et c ='2' Cette instruction n'ira pas à l'index index

1 Définition

Le principe de correspondance des préfixes les plus à gauche : lorsque MySQL construit un index conjoint, il respectera le principe de correspondance des préfixes les plus à gauche, à savoir. est, la priorité la plus à gauche, lors de la récupération des données de l'index commun. La correspondance commence par le côté le plus à gauche

Afin de mieux identifier cette situation, analysez-la en créant une table et un index

2 Ordre d'index complet

Créez une table et créez. un index commun. Si l'ordre est inversé, c'est toujours possible Identifié, mais il doit y avoir toutes ses parties

🎜Créer une table🎜

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

🎜Créer un indexALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos); 🎜🎜L'ordre de l'index name-age-pos🎜🎜Afficher son index afficher l'index des portées ;🎜🎜🎜🎜En inversant l'ordre gauche et droite, son exécution est la pareil🎜🎜Les déclarations principales sont ces trois phrases🎜

1. 🎜explain select *from staffs which name='z3'and age=22 et pos='manager ';🎜🎜
2. 🎜expliquez sélectionner *from staffs which pos='manager' et name='z3'and age=22;🎜🎜
3. 🎜 expliquez, sélectionnez * parmi les équipes où age=22 et pos='manager' et name='z3' ;🎜🎜🎜🎜🎜🎜L'ordre des trois ci-dessus est inversé et des index conjoints sont utilisés. La raison principale est que il existe un optimiseur de requête expliqué dans MySQL, donc l'ordre des champs dans l'instruction SQL n'a pas besoin d'être combiné avec l'union. Les champs définis par l'index sont dans le même ordre. L'optimiseur de requête déterminera l'ordre dans lequel il faut les utiliser. corriger cette instruction SQL est le plus efficace, et enfin générer un véritable plan d'exécution🎜🎜L'index conjoint peut être utilisé quel que soit l'ordre🎜🎜3 Ordre d'index partiel🎜

   3.1 Ordre positif

   🎜S'il est dans. ordre (manque bras et jambes), c'est pareil🎜
   1. 🎜explain select *from staffswhere name=‘z3’;🎜 🎜
   2. 🎜expliquez la sélection *dans les équipes où nom='z3’et age=22;🎜🎜🎜expliquez sélectionnez *dans les équipes où nom='z3’et l'âge =22;🎜🎜🎜🎜🎜🎜Les types sont tous des types ref, mais la longueur du champ changera légèrement, c'est-à-dire la longueur du mot qu'il définit Juste un changement🎜

      3.2 Hors service

      🎜Si l'ordre de certains index sont en panne🎜
      1. 🎜Vérifiez uniquement le premier indexexpliquez select * from staffs which name='z3';🎜🎜🎜ignorer l'index du milieuexplain select *from staffs which name='z3' et pos='manager' ;🎜 🎜
      2. 🎜Vérifiez uniquement le dernier indexexplain select *from personnel où pos='manager'; 🎜🎜🎜🎜🎜🎜Vous pouvez trouver la séquence positive🎜🎜S'il vous manque des bras et des jambes, vous pouvez également suivre l'index normal🎜🎜 Même si l'index du milieu est ignoré, vous pouvez toujours utiliser le index à interroger🎜🎜Mais si vous interrogez uniquement le dernier index🎜🎜type est le type all, et la table entière sera interrogée directement (c'est parce qu'il n'y a pas de correspondance depuis le début du nom, correspondance directe Si pos, c'est sera affiché dans le désordre,)🎜🎜Parfois, le type est le type d'index, car il peut toujours être interrogé via l'index🎜🎜index analyse toutes les arborescences d'index, tandis que all analyse les données entières du disque dans une table complète 🎜🎜4 . Index flou🎜🎜Un index flou similaire utilisera l'instruction like🎜🎜Donc les trois instructions suivantes🎜🎜Si elles sont composées avec le préfixe le plus à gauche, la plage ou le type d'index sera utilisé pour l'indexation🎜
         1. 🎜expliquez sélectionner *à partir des portées dont le nom est comme '3%' ; L'index de préfixe le plus à gauche, le type est un index ou une plage🎜🎜
         2. 🎜expliquez sélectionner *à partir des portées où le nom comme '%3%' ; Le type est tout, requête de table complète🎜🎜
         3. 🎜expliquez sélectionner *à partir des portées où le nom comme '%3%' ;, le type est tout, requête de table complète🎜🎜🎜🎜🎜🎜🎜5. Index de plage🎜🎜Si lors de l'interrogation de plusieurs champs, il y a une plage au milieu, il est recommandé de supprimer l'index et d'éliminer l'index du milieu🎜 🎜Les idées spécifiques sont comme suit🎜<p>建立一张单表</p><pre class="brush:sql;">CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views INT(10) UNSIGNED NOT NULL, comments INT(10) UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content TEXT NOT NULL ); INSERT INTO article(author_id,category_id,views,comments,title,content) VALUES (1,1,1,1,&amp;#39;1&amp;#39;,&amp;#39;1&amp;#39;), (2,2,2,2,&amp;#39;2&amp;#39;,&amp;#39;2&amp;#39;), (1,1,3,3,&amp;#39;3&amp;#39;,&amp;#39;3&amp;#39;);</pre><p>经过如下查询：</p><pre class="brush:sql;">explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments &gt; 1 ORDER BY views DESC LIMIT 1;</pre><p><img src="https://img.php.cn/upload/article/000/000/164/168525475137271.png" alt="Quel est le principe du préfixe le plus à gauche de lindex Mysql ?"></p> <p>发现其上面的单表查询，不是索引的话，他是进行了全表查询，而且在extra还出现了Using filesort等问题</p> <p>所以思路可以有建立其复合索引</p> <p>具体建立复合索引有两种方式：</p> <ol class=" list-paddingleft-2"> <li><p><code>create index idx_article_ccv on article(category_id,comments,views);

         4. ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

         

         但这只是去除了它的范围，如果要去除Using filesort问题的话，还要将其中间的条件范围改为等于号才可满足

         发现其思路不行，所以删除其索引 DROP INDEX idx_article_ccv ON article;

         

         主要的原因是：

         这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

         当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

         所以建立复合索引是对的

         但是其思路要避开中间那个范围的索引进去

         只加入另外两个索引即可create index idx_article_cv on article(category_id, views);

         

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!