Quelles sont les situations dans lesquelles MySQL provoque un échec d’index ?

1. PRÉPARATION

Préparez deux tableaux pour la démonstration:

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

rreerE

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

2. Règles d'invalidation d'index

1. son efficacité de requête en construisant des index Il existe plusieurs options comme suit :

①Construire un index normal :

#准备数据
select count(*) from student_info;#1000000
select count(*) from course;      #100

②Sur la base de l'index normal, ajouter un index conjoint :

#平均耗时291毫秒
select * from student_info where name='123' and course_id=1 and class_id=1;

Vous pouvez voir. que

lorsque plusieurs index peuvent être utilisés, le système donne généralement la priorité à l'utilisation d'index conjoints plus longs, car les index conjoints sont plus rapides en comparaison. Cela devrait être facile à comprendre,

Le principe est que l'index conjoint doit être observé La correspondance la plus à gauche. principe d'index

. Si vous créez un index commun composé de name, course_id, class_id, alors l'instruction SQL ci-dessus utilisera cet index commun avec un key_len plus long comme prévu (la surprise est que l'optimiseur peut choisir d'autres meilleures solutions, s'il s'agit de mots plus rapides ).

La vitesse de l'index conjoint n'est pas nécessairement meilleure que celle de l'index ordinaire

Par exemple, si la première condition filtre tous les enregistrements, alors il n'est pas nécessaire d'utiliser un index suivant.

2. Principe de correspondance le plus à gauche

#建立普通索引
create index idx_name on student_info(name);
#平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

① Situation dans laquelle l'index conjoint correspond à tous :

#name，course_id组成的联合索引
create index idx_name_courseId on student_info(name,course_id);
#该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策
#平均耗时20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

Chaque condition de champ correspond à l'index conjoint, donc l'instruction SQL suit la règle de préfixe la plus à gauche. L'utilisation d'un index conjoint permet des recherches rapides et évite des requêtes supplémentaires, c'est donc la situation optimale.

②La situation où la partie la plus à droite de l'index conjoint est manquante :

#删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id
create index idx_name_cou_cls on student_info(name,course_id,class_id);

Les conditions de cette instruction SQL ne contiennent pas toutes les conditions de l'index conjoint, mais la moitié droite est effacée. L'index utilisé. par cette instruction est toujours Cette requête de corrélation n'en utilise qu'une partie. En regardant key_len, on peut savoir qu'il manque 5 octets. Ces 5 octets correspondent à class_id, ce qui prouve que class_id n'est pas efficace (il n'est pas là, donc). bien sûr, il n'est pas utilisé).

De même, si vous supprimez le champ course_id dans Where, l'index conjoint prendra toujours effet, mais le key_len sera réduit.

③La situation de manque dans l'index conjoint :

#关联字段的索引比较完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

L'instruction SQL ci-dessus utilise toujours l'index conjoint, mais son key_len devient plus petit. Seul le champ nom utilise l'index, et bien que le champ class_id soit présent. l'index conjoint, mais c'est GG car il ne répond pas au principe de correspondance le plus à gauche.

Le processus d'exécution de l'intégralité de l'instruction SQL est le suivant : recherchez d'abord tous les enregistrements portant le nom 11111 dans l'arbre B de l'index conjoint, puis filtrez le texte intégral de ces enregistrements dont l'identifiant de classe n'est pas 10154. Avec une étape supplémentaire de recherche en texte intégral, les performances seront pires qu'en ① et ②.

④Le cas où le champ le plus à gauche de l'index conjoint est manquant :

explain select * from student_info where name='11111' and course_id=10068;

Cette situation est un cas particulier de la situation précédente. Le champ le plus à gauche de l'index conjoint n'est pas trouvé, donc bien qu'il en existe d'autres. parties, elles sont toutes invalides. La méthode est la recherche en texte intégral.

Conclusion : le principe de correspondance le plus à gauche signifie que la requête commence à partir de la colonne la plus à gauche de l'index et ne peut pas sauter de colonnes dans l'index. Si une colonne est ignorée, l'index sera partiellement invalide (tous les index de champ suivants seront invalides). .

Remarque : lors de la création d'un index conjoint, l'ordre des champs est fixe et la correspondance la plus à gauche est comparée selon cet ordre, mais dans l'instruction de requête, l'ordre des champs dans la condition Where est variable, ce qui Cela signifie qu'il n'est pas nécessaire de suivre l'ordre des champs d'index associés. Il suffit d'inclure la condition Where.

3. L'index de colonne sur le côté droit de la condition de plage n'est pas valide Reprenez l'index conjoint ci-dessus et utilisez la requête SQL suivante :

#联合索引中间的字段未使用，而左边和右边的都存在
explain select * from student_info where name='11111' and class_id=10154;;

Le key_len n'est que de 68, ce qui signifie que le class_id dans l'index associé n'est pas utilisé, bien qu'il soit conforme au principe de correspondance le plus à gauche, mais parce que le symbole

> rend invalide l'index à droite du champ de condition dans l'index associé .

Mais si vous utilisez le signe >= :

explain select * from student_info where class_id=10154 and course_id=10068;

L'index de droite n'est pas invalide, key_len vaut 73 et les index de tous les champs sont utilisés.

结论：为了充分利用索引，我们有时候可以将>、=、的条件的字段尽量放在关联索引靠后的位置。

4.计算、函数导致索引失效

#删除前面的索引，新创建name字段的索引，方便演示
create index idx_name on student_info(name);

现有一个需求，找出name为li开头的学生信息：

#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';

上面的两条sql语句都可以满足需求，然而第一条语句用了索引，第二条没有，一点点的改变真是天差地别。

结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。字段能不用就不用函数。

类似：

#也不会使用索引
explain select * from student_info where name+''='lisi';

类似的对字段的运算也会导致索引失效。

5.类型转换导致索引失效

#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。

6.不等于(!= 或者)索引失效

#创建索引
create index idx_name on student_info(name);
#索引失效
explain select * from student_info where name<>&#39;zhangsan&#39;;
explain select * from student_info where name!=&#39;zhangsan&#39;;

不等于的情况是不会使用索引的。因为!=代表着要进行全文的查找，用不上索引。

7.is null可以使用索引，is not null无法使用索引

#可以使用索引
explain select * from student_info where name is null;
#索引失效
explain select * from student_info where name is not null;

和前一个规则类似的，!=null。同理not like也无法使用索引。

最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。

8.like以%开头，索引失效

#使用到了索引
explain select * from student_info where name like 'li%';
#索引失效
explain select * from student_info where name like '%li';

只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。

9.OR前后存在非索引的列，索引失效

#创建好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果or前后都是索引：

#使用索引
explain select * from student_info where name like 'li%' or course_id=200;

 如果其中一个没有索引：

explain select * from student_info where name like 'li%' or class_id=1;

那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。

10.字符集不统一

字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。

三、建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引

• 在选择组合索引时，query过滤性最好的字段应该越靠前越好

• 在选择组合索引时，尽量选择能包含当前query中where子句中更多字段的索引

• 在选择组合索引时，如果某个字段可能出现范围查询，尽量将它往后放

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!