索引优化原则——生效与失效

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(24) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `class` varchar(20) COLLATE utf8mb4_bin NOT NULL COMMENT '班级',
  `in_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入学时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_class` (`name`,`age`,`class`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


INSERT INTO test.student (id, name, age, class, in_date) VALUES (1, '张三', 8, '一年一班', '2022-09-19 21:42:14');
INSERT INTO test.student (id, name, age, class, in_date) VALUES (2, '李四', 9, '二年一班', '2022-09-19 21:42:16');
INSERT INTO test.student (id, name, age, class, in_date) VALUES (3, '王五', 10, '三年一班', '2022-09-19 21:42:29');

1.全值匹配：符合最左前缀原则，都会走索引，区别在于使用索引列个数，用key_len计算

explain select * from student where name = '张三';

只使用了name字段进行查询，类型为varchar(24)，COLLATE utf8mb4_bin，根据上一篇文章EXplain详解的key_len计算规则可以计算（n*4+2即24*4+2=98），即当前SQL就是使用了name一个字段的索引。 

explain select * from student where name = '张三' and age = '8';

使用了name+age字段进行查询，计算长度（n*4+2+4即24*4+2+4=102），即当前SQL使用了name+age索引 

explain select * from student where name = '张三' and age = '8' and class='一年一班';

使用了name+age+class，长度为（n*4+2+4+n*4+2即24*4+2+4+20*4+2=184），即当前SQL使用了name+age+class索引 

explain select * from student where name = '张三' and class='一年一班' and age = '8';

 将name/age/class的顺序打乱再次执行长度依然是184，说明走了name+age+class索引，原因是MySQL底层会对条件按照索引顺序进行优化。

优化建议：条件按照索引顺序写，不要让mysql在进行排序，减少mysql的负担。

2.最左前缀法则：查询从索引最左前列开始，并且不跳过索引中的列。

explain select * from student where name = '张三' and class='一年一班';

SQL使用name+class字段进行查询，计算两个字段长度（n*4+2+4+n*4+2+4即24*4+2+20*4+2=180）与执行计划中长度98不一致，98只是name的长度，所以可以看出，如果联合索引跳过某一字段，只有此字段之前的索引会生效。

explain select * from student where age = '8' and class='一年一班';

 SQL不包含name，则不走任何索引，进行全表扫描。

3.索引列不使用任何操作（计算、函数、类型转换），否则索引失效查询变成权标扫描

explain select * from student where name = '张三' and age+3 = '11';

 key_len长度是98，只有name字段走了索引，age字段索引失效了。

-- 增加in_date为普通索引
alter table student add index idx_in_date (in_date) using btree ;
explain select * from student where date(in_date) = '2022-09-19';

 查询时对in_date字段进行date类型转换，索引失效，我们可以优化一下使用范围查找，可能会使用到索引。

 在执行计划中给出possible_key：idx_in_date，说明mysql执行的时候可能会使用到这个索引，这个SQL为什么没有使用：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。当前表数据量少，做一次全表扫描也用不了太长时间，而且使用二级索引查找全字段（select *），并没有使用覆盖索引，会进行回表查询，mysql认为这个效率比全表扫描一次效率低，所以用全表扫描。

-- 删除in_date字段索引，还原student表原有索引
alter table student drop index idx_in_date;

4.存储引擎不能使用范围条件右边的列

explain select * from student where name = '张三' and age = '8' and class='一年一班';
explain select * from student where name = '张三' and age > '8' and class='一年一班';

 第一条key_len=184，使用name+age+class索引；第二条key_len=102，使用name+age索引。

 5.尽量使用覆盖索引（索引列包含查询列），减少select * 语句

使用的索引都一样，但是覆盖索引会少一次回表操作。

6.MySQL在使用不等于的时候可能会导致索引会失效，进行全表扫描 

 同3的第二种情形，mysql在执行前会计算是否走索引，如果全表扫描比走索引有效，就会进行全表扫描。

7.is null不会走索引，is not null可能会走索引

8.like以通配符开头，mysql索引会失效，查询变成全表扫描

以通配符开头

通配符不在开头 

 解决like '%字符串%' 索引不被使用方法：

① 使用覆盖索引，查询字段必须是建立联合索引的字段

 ② 如果不能使用覆盖索引，可以借助搜索引擎。

like 'AA%'相当于=常量，like '%AA%' 相当于范围查询

9.字符串不加单引号导致索引失效

10.少用or或者in，用它们查询时mysql不一定使用索引

 11.范围查找优化

-- 增加age字段索引
alter table student add index idx_age (age) using btree ;

这种情况是因为单次数据量查询比较大，导致mysql优化器最终选择不走索引。

优化方法：可以将大范围拆成多个小范围。

  总结：

index(name,age,class)
where语句	索引	索引字段
where name = '张三'	Y	name
where name = '张三' and age =8	Y	name,age
where name = '张三' and age =8 and class = '0101'	Y	name,age,class
where age =8 and class = '0101' where age =8 where class = '0101'	N
where name = '张三'  and class = '0101'	Y	name (class不能使用索引，age中间断了，class对于name是无序的)
where name = '张三' and age > 8 and class = '0101'	Y	name,age (class不能使用索引，age是范围查询，class在age范围后也是无序的)
where name = '张三' and age like '8%' and class = '0101'	Y	name,age,class
where name = '张三' and age like '%8' and class = '0101'	Y	name
where name = '张三' and age like '%8%' and class = '0101'	Y	name
where name = '张三' and age like '8%8%' and class = '0101'	Y	name,age,class

like AA%相当于=常量，%AA和%AA%相当于范围查询

索引下推：

5.6版本之前

1.使用二级联合索引最左前缀原则，先找到 like 'name%' 所对应的主键值；

2.使用主键值依次回表到主键索引树中，查询主键值对应的数据行；

3.再进行age和position值对比，得到想要的数据行。

5.6及之后版本

1.使用二级联合索引最左前缀原则，先找到 like 'name%' 所对应的主键值，索引下推去对比age和position的值，筛选出想要数据的主键值；

2.使用筛选后的主键值，回表查询，得到想要的数据行。

 总结：

1.使用了索引下推，在二级索引中获得了有效的主键值，减少了回表次数，提升了SQL查询效率。

2.二级索引使用索引下推，效果明显，主键索引使用索引下推意义不大。

主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果

强制走索引：force index(idx_name_age_class)

EXPLAIN SELECT * FROM student force index(idx_name_age_class) WHERE name > '张三' AND age = 22 AND class ='0101';

强制走索引的效率有时不一定会比扫描全表高，所以mysql会出现建议使用索引，实际上没有走索引的情况。