MySQL避免索引失效

一时失言乱红尘 2022-01-21 08:09 468阅读 0赞

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的长度越来越长。

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

这里写图片描述

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

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

这里写图片描述

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

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

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

  1. EXPLAIN select * from staffs where name='July';
  2. -- 其中left(name,4)这个函数的意思是找name字段中从左数4个字符的名字
  3. 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是比较好的。

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

这里写图片描述

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

  1. 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的时候%分号要加在右边，不要加在左边。

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

  1. 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;
  3. #drop table tbl_user insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
  4. insert into tbl_user(name,age,email) values('2aa2',222,'a@163.com');
  5. insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
  6. insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');
  7. insert into tbl_user(name,age,email) values('aa',121,'e@163.com');

用覆盖索引来解决

我对name和age建立索引之后

  1. EXPLAIN SELECT id from tbl_user WHERE name LIKE '%aa%';
  2. EXPLAIN SELECT name from tbl_user WHERE name LIKE '%aa%';
  3. EXPLAIN SELECT age from tbl_user WHERE name LIKE '%aa%';
  4. EXPLAIN SELECT id,name from tbl_user WHERE name LIKE '%aa%';
  5. EXPLAIN SELECT id,name,age from tbl_user WHERE name LIKE '%aa%';
  6. EXPLAIN SELECT name,age from tbl_user WHERE name LIKE '%aa%';

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

这里写图片描述
而执行

  1. EXPLAIN SELECT * from tbl_user WHERE name like '%aa%';
  2. 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高级也不难

发表评论

表情：
评论列表 （有 0 条评论，468人围观）

还没有评论，来说两句吧...

相关阅读