MySQL避免索引失效

墨蓝 2022-05-30 00:52 256阅读 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';

![这里写图片描述][70]

可以看到在有带头大哥的情况下，第一条记录用到了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';

![这里写图片描述][70 1]

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

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

![这里写图片描述][70 2]

在有带头大哥的情况下，中间断了。可以看到只用到了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达到的效果是一样的，但第二条用到了函数进行计算，导致了索引失效

![这里写图片描述][70 3]

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

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

![这里写图片描述][70 4]  
可以发现其中name和age的索引用到了，但pos的索引没有用到。这里type为range。

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

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

![这里写图片描述][70 5]

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

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

![这里写图片描述][70 6]

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

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

![这里写图片描述][70 7]

这里的Extra中多了Using index

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

![这里写图片描述][70 8]

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

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

![这里写图片描述][70 9]  
而执行

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)

<table> 
 <thead> 
  <tr> 
   <th align="left">where语句</th> 
   <th>索引是否被使用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">where a=3</td> 
   <td>使用到a</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b=5</td> 
   <td>使用到a和b</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b=5 and c=4</td> 
   <td>使用到a,b,c</td> 
  </tr> 
  <tr> 
   <td align="left">where b=3 或者 where b=3 and c=4 或者where c=4</td> 
   <td>没有使用到</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and c=5</td> 
   <td>使用到a，但是c不可以，b中间断了</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b&gt;4 and c=5</td> 
   <td>使用到a和b，c不能用在范围之后</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b like ‘kk%’ and c=4</td> 
   <td>使用到a和b和c，这里like为范围但和大于号的那个范围不一样</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b like ‘%kk’ and c=4</td> 
   <td>使用到a</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b like ‘%kk%’ and c=4</td> 
   <td>使用到a</td> 
  </tr> 
  <tr> 
   <td align="left">where a=3 and b like ‘k%kk%’ and c=4</td> 
   <td>使用到a和b和c</td> 
  </tr> 
 </tbody> 
</table>

# 一般性建议 #

*  对于单键索引，尽量选择针对当前query过滤性更好的索引
 *  在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
 *  在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
 *  尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

# 口诀 #

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

[70]: /images/20220530/2eb96e3a42dc4fd6b98cfc6bdd313581.png
[70 1]: /images/20220530/d68c08fb7e1e4574bfed33f28df0fba4.png
[70 2]: /images/20220530/adc6b3130a154494a69ef2c2d0daecb7.png
[70 3]: /images/20220530/ff5fb02304484ec5ba57351f8a64f249.png
[70 4]: /images/20220530/0a5a02549ae1417aa2b203cf563a68d6.png
[70 5]: /images/20220530/a62f242ef284493996228018256f8e97.png
[70 6]: /images/20220530/dfb5de881a5c490cbfeb430dc6199510.png
[70 7]: /images/20220530/040deff382fb4aa4b393c9c4682516d2.png
[70 8]: /images/20220530/f10e2114028848eca9e46daf1621ef2e.png
[70 9]: /images/20220530/3b2ac61792c64f7bbb9acb87146aaa87.png