【MySQL进阶】之如何避免索引失效

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

插入数据如下：

在表 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!='老师';

我这里无论是查询字段还是 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;

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 以通配符开头导致索引失效？

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

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%';

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