MySQL避免索引失效

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

select * from staffs;

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

这里我建立索引的顺序是name,age,pos。name是大哥，在最前边

• 全值匹配我最爱
• 最佳左前缀法则，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断）

-- 有带头大哥
EXPLAIN select * from staffs where name='July';
EXPLAIN select * from staffs where name='July' AND age=25;
EXPLAIN select * from staffs where name='July' AND age=25 and pos='dev';

可以看到在有带头大哥的情况下，第一条记录用到了name索引，第二条记录用到了name和age索引，第三条记录用到了name,age,pos索引。可以看到key_len的长度越来越长。

-- 没有带头大哥
EXPLAIN SELECT * FROM staffs WHERE age=23 AND pos='dev';
EXPLAIN SELECT * FROM staffs WHERE pos='dev';

在没有带头大哥的情况下，索引都没有用到。

-- 有带头大哥但中间断了
EXPLAIN select * from staffs where name='July' AND pos='dev';

在有带头大哥的情况下，中间断了。可以看到只用到了name索引。因为如果name和pos索引都用到了的话，key_len应该比74大。

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

15-20k的程序员可以做出符合需求的功能。20k以上的程序员要考虑性能。

EXPLAIN select * from staffs where name='July';
-- 其中left(name,4)这个函数的意思是找name字段中从左数4个字符的名字
EXPLAIN select * from staffs where left(name,4)='July';

其实这两条SQL达到的效果是一样的，但第二条用到了函数进行计算，导致了索引失效

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

EXPLAIN select * from staffs where name='July' AND age>25 and pos='dev';

可以发现其中name和age的索引用到了，但pos的索引没有用到。这里type为range。

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

EXPLAIN select name,age,pos from staffs where name='July' AND age=25 and pos='dev';

这里我没有用select *，而是改成了具体的字段，可以发现Extra中多了个Using index，有using index是比较好的。

EXPLAIN select name,age,pos from staffs where name='July' AND age>25 and pos='dev';

可以发现这里的type由range变成了ref性能更好了。

EXPLAIN select name,age,pos from staffs where name='July' AND age=25;

这里的Extra中多了Using index

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

• is null,is not null也无法使用索引

• like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作。问题：解决like‘%字符串%’时索引不被使用的方法？

EXPLAIN select * from staffs where name like '%July%';
EXPLAIN select * from staffs where name like '%July';
EXPLAIN select * from staffs where name like 'July%';

结论：这三条语句中前两条的索引都会失效。而第三条在name上建立的索引不会失效，为range类型的索引。索引使用like的时候%分号要加在右边，不要加在左边。

但如果我非要在左边加%号呢？怎么解决？

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;

#drop table tbl_user

insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
insert into tbl_user(name,age,email) values('2aa2',222,'a@163.com');
insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');
insert into tbl_user(name,age,email) values('aa',121,'e@163.com');

用覆盖索引来解决

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

执行这些语句都不会全表扫描，因为要查询的字段都在索引里边

而执行

EXPLAIN SELECT * from tbl_user WHERE name like '%aa%';
EXPLAIN SELECT id,name,age,email from tbl_user WHERE name LIKE '%aa%';

就会全表扫描，因为email字段不在索引里边

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

SELECT * from staffs where name='2000';
SELECT * from staffs where name=2000;

这两条语句都会查询出正确结果，但第二条没有用到索引

因为mysql会在底层对其进行隐式的类型转换

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

EXPLAIN SELECT * FROM staffs WHERE name='July' or name='z3';

这条语句不会使用索引，会全表扫描

练习

假设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不能用在范围之后
where a=3 and b like ‘kk%’ and c=4	使用到a和b和c，这里like为范围但和大于号的那个范围不一样
where a=3 and b like ‘%kk’ and c=4	使用到a
where a=3 and b like ‘%kk%’ and c=4	使用到a
where a=3 and b like ‘k%kk%’ and c=4	使用到a和b和c

一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
• mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引

口诀

• 全职匹配我最爱，最左前缀要遵守；
• 带头大哥不能死，中间兄弟不能断
• 索引列上少计算，范围之后全失效
• like百分写最右，覆盖索引不写星
• 不等空值还有or，索引失效要少用
• var引号不可丢，SQL高级也不难