MYSQL高级篇-----索引优化分析（四）-阿里云开发者社区

2.6 索引失效

常见情况：

全值匹配我最爱。

最佳左前缀法则。

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

索引中范围条件右边的字段会全部失效。

尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。

MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。

is null、is not null也无法使用索引。

like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）。

字符串不加单引号索引失效。

少用or，用它来连接时会索引失效。

为了更好的演示效果，采用实战方式

建立一张表

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);

全值匹配，且从左到右，只不过长度变长了

/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';
/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;
/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

索引失效（应该避免）

从左开始且不跳列才不会失效

最佳左前缀法则 - 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。

/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';
/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';

索引列上不做额外操作才不会失效

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。*

由此可见，在索引列上进行计算，会使索引失效。

口诀：索引列上不计算。

范围之后全失效

/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name，age字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev';

少用>，<，between…and等结构

存储引擎不能使用索引中范围条件右边的列。（就是>，<，between…and）*

减少select *

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））

区别在于extra，索引的不同，速度不一样\

*在写SQL的不要使用SELECT ，用什么字段就查询什么字段。

/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

不用!=或者<>

不等有时会失效

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。\

不用 is null, is not null

is null, is not null 也无法使用索引。\

模糊查询

like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。

扩展：

详细理解可查看博客11节

#like 模糊查询 查询 相似值
#%% 代表不确定个数的字符  代表前后有若干个值可能是0 ，1，或者其他
#查询name中包含 a的值
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%';
#查询name中 a 开头的值
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE 'a%';
#查询name中 a 结尾的值
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a';
#查询包含a e的值
#写法1 
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2 
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';
#查询第二个字符是a的值
#_ 代表不确定的字符
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '_a%'

注意看上面模糊查询的细节，只有xx%前缀查询才不会失效

那如何改变%xx% 让其不失效呢\

引入一张表

CREATE TABLE `tbl_user`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `age`INT(11) DEFAULT NULL,
  `email` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');NSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

在没有索引的时候，怎么查询都是全表查询

增加一个索引

覆盖索引

全部覆盖

CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

之后的索引查询为字段值\

视频地址

如果使用下面这些，都是使用的覆盖索引，结果都是一样的

EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';

id之所以没加索引，但是可以加入使用不会失效，是因为他是主键

但是如果加入了没有主键又不是索引的东西，%xx%就会失效\

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';

类型要正确

即使类型不正确也可以查询，但是底层会帮你转换类型，在判断，但会浪费时间，索引直接失效，变成了全表查询

字符串不加单引号索引失效。\

不用or关键字就不会失效

少用or，用它来连接时会索引失效。

口诀：覆盖索引保两边。

视频地址

2.7 面试常考

为了更加贴切的展示，先建立一张表

create table test03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

建立一条组合索引 create index idx_test03_c1234 on test03(c1,c2,c3,c4);

以下都符合最左前缀原则，都会用到索引，只是估计用到的长度在变化而已

正常顺序

explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

乱序

即使更改一下顺序，mysql的最左前缀原则还是符合的，因为mysql有优化器会帮你查询是否匹配

explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';

限定范围

如果开始限定范围

第一条会用到3个索引，前两个用来查找，第三个用来排序

第二条用到了4个索引，前3个用来查找，第四个用来排序

explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

通过看其长度也可知道

范围之后（索引）全失效：在本文当中当我们使用c4的时候 c4后面已经没有查询了，但如果是c3的话 c3后面存在c4的；（可理解为在创建索引中的顺序为准；c4是最后的他后面失效没用了他后面就无数据）

使用order by

下面那个例子都是一样的

前两个都是在查找，第三个只是在排序，到了这里就已经断了，所以第四个可有可无

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;

explain select * from test03 where c1='a1' and c2='a2' order by c3;

如果把3换成4，会出现Using filesort。，因为优化器会给你文件排序（因为中间跳了一个）

![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zc7GaexN-1659805741463)(https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6e6b2a38b4d94e2dbb88f2827639bd47~tplv-k3u1fbpfcp-zoom-1.image)]](https://ucc.alicdn.com/images/user-upload-01/4249633b94cd45e98304c58c5c40627f.png)

如果使用这个不会出现Using filesort，因为是正常的索引顺序

而这个就会出现Using filesort，因为顺序颠倒了

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

下面这两个一样的功能

前两个是查找，第二三是排序

explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
 explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;

这个就不一样，如果确定了一个查找的顺序之后，即使排序乱序也不会出现filesort

但是如果不用的话就会出现

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-agY637dQ-1659805741464)(https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7809f38b02e64f2eacb4151d48109127~tplv-k3u1fbpfcp-zoom-1.image)]](https://ucc.alicdn.com/images/user-upload-01/989e92e35e7e448eb7fee4cc9fc7751b.png)

group by

如果乱序，还会多出现一个临时表

定值、范围还是排序，一般order by是给个范围

group by基本上都需要进行排序，会有临时表产生

explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
explain select * from test03 where c1='a1' and c4='a4' order by c2,c3;

模糊查询

explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';
explain select * from test03 where c1='a1' and c2 like '%kk' and c3='a3';
explain select * from test03 where c1='a1' and c2 like '%kk%' and c3='a3';
explain select * from test03 where c1='a1' and c2 like 'k%kk%' and c3='a3';

具体其执行结果如下

整合：

/* 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次 */
/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';
/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';
/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';
/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';
/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';
/* 
   6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;
/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;
/* 
   8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段 
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;
/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;
/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;
/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;
/* 
   13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
      因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
      所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;
/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */
/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;
/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

2.8 总结

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

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

假设index(a, b, c)

where语句	索引是否被使用
where a = 3	可以，使用到a
where a = 3 and b = 5	可以，使用到a，b
where a = 3 and b = 5 and c = 4	可以，使用到a，b，c

where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	不可
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了

where a = 3 and b > 4 and c = 5	使用到a和b，c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4	可以，使用到a，b，c。

where a = 3 and b like ‘%kk’ and c = 4	Y，使用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y，使用到a

where a= 3 and b like ‘k%kk%’ and c = 4

Y，使用到a，b，c

优化的口诀如下：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写 *；

不等空值还有 OR，索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

MYSQL高级篇-----索引优化分析（四）

2.6 索引失效

2.7 面试常考

2.8 总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MYSQL高级篇-----索引优化分析（四）

2.6 索引失效

2.7 面试常考

2.8 总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像