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的写法来达到选择合适索引的目的

口诀

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

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读