1、在全值匹配中遵循最佳左前缀原则

有一个 role 表,表字段如下:

create table role(
id int(10) not null primary key auto_increment,
user_id int(10),
dept_id int(10),
role_name varchar(30) not null
) default charset=utf8;

插入数据如下:
全职匹配案例

在表 role 中的 user_id,dept_id,role_name 三个字段建立了名为 index_userid_deptid_rolename 的符合索引。

alter table role add index index_userid_deptid_rolename(user_id,dept_id,role_name);

索引失效的情况:
1)、复合索引的所有列都失效:

explain select * from role where role_name='老师';

explain select * from role where dept_id=1;

explain select * from role where dept_id=1 and role_name='老师' ;

这几种情况的结果都是差不多的,以第三种为例,其结果为:
全职匹配全部索引失效

2)、复合索引的部分列生效部分列不生效:

explain select * from role where user_id=1 and role_name='老师';

复合索引的部分列生效

由此可以得出最佳左前缀法则
如果索引有多个列,要遵循最左前缀法则。即查询条件从最左前列开始并且不跳过索引中的列。

2、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)

这样做会导致索引失效而转向全表扫描。
在索引列上使用函数

3、不要在作为范围条件的索引列的右边列使用索引

因为如果在 where 查询条件中使用到了所有的复合索引的列,其中中间列是范围匹配,则会导致该列的右边的所有列都不会生效。
例如:

 explain select * from role where user_id=1 and dept_id>1 and role_name='老师';

使用复合索引的中间列作为范围条件

4、尽量使用覆盖索引(只访问索引的查询即索引列和查询列一致),减少使用 select *(5.7 版本以后的不会导致索引失效)

explain select * from role where user_id=1 and dept_id=1 and role_name='老师';

explain select user_id,dept_id from role where user_id=1 and dept_id=1 and role_name='老师';

explain select user_id,dept_id,role_name from role where user_id=1 and dept_id=1 and role_name='老师';

使用覆盖索引

5、不要使用 != 或者 <> ,会导致全表扫描( 5.7 版本以后的只会降低效率,不会导致索引失效)

 explain select user_id,dept_id,role_name from role where role_name!='老师';
 explain select user_id,dept_id,role_name from role where role_name<>'老师';

 explain select * from role where role_name<>'老师';
 explain select * from role where role_name!='老师';

复合索引的部分列作为条件时使用到了不等于(!=或&lt;&gt;)
我这里无论是查询字段还是 select * 最终还是使用到了复合索引,由此可得出该条结论不适用于所有的版本。(我的版本是 5.7)

6、在不允许为 null 的索引字段上,不能使用 is null 或者 is not null 的索引列作为查询条件。(5.7 版本以后的 is not null 只会降低效率,不会导致索引失效)

is null ,is not null 会导致索引失效从而导致全表扫描。

explain select * from role where role_name is null;

explain select * from role where role_name is  not null;

explain select * from role where user_id is null;

explain select * from role where user_id is not null;

is null ,is not null 对索引的影响

7、不使用 like以通配符开头(如:'%abc..')

因为这样会导致索引失效,从而转变为全表扫描。
例如 tbl_user 表的字段如下:

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

测试案例:

explain select * from tbl_user where name like '%aa%';

explain select * from tbl_user where name like '%aa';

explain select * from tbl_user where name like 'aa%';

like 以通配符开头

如何解决 like 以通配符开头导致索引失效?

使用覆盖索引(查询的列是复合索引的部分列或者全部列,主键索引可添加到查询列中)

explain select id from tbl_user where name like '%aa%';

explain select name,age 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,age from tbl_user where name like '%aa%';

// 由于查询列包含了 复合索引所不包含的列导致索引失效
explain select id,name,age,email from tbl_user where name like '%aa%';

explain select name,age,email from tbl_user where name like '%aa%';

使用覆盖索引避免 like 以通配符开头导致的索引失效

8、字符串不要忘记加单引号,不然会导致索引失效

在表 tbl_user 中插入以下数据:

insert into tbl_user(name,age,email) values('2000',10,'e@163.com');

字符串未加单引号

9、少用 or ,用它来连接时会索引失效

explain select * from tbl_user where name='1aa1' or name='2aa2';

查询条件使用 or 连接

最后修改:2021 年 10 月 26 日 04 : 48 PM
如果觉得我的文章对你有用,请随意赞赏