求关注

求转发

一、什么情况会不走索引

1、模糊查询

在字段开头模糊时：

select * from teacher where name like '%臻大虾'

在字段后面使用模糊查询

select * from teacher where name like '臻大虾%'

结论：

可以在字段后面使用模糊查询

2、使用了in或not in

使用explain看了下你会发现，当in或not in的条件比较少时，会用到索引

当条件增加，使用的索引性能越来越差，直到全表扫描，not in同理

结论

• 根据实际情况，控制in的范围，in 的条件过多，会导致索引失效，走索引扫描

• 如果是连续的值，可以使用between...and

select * from teacher where id BETWEEN 1 and 1000;

• 子查询：

select * from teacher t where t.id in (SELECT t2.id FROM teacher t2 WHERE t2.id >1);

• 有时候可以使用exists替代in

in原理：mysql中的 in语句 是把 外表 和 内表 作hash 连接

exist原理：exists语句是对外表作loop循环，每次loop循环再对内表进行查询

一直大家都认为 exists 比 in 语句的效率要高，这种说法其实是不准确的。这个是要区分环境的

查询1:exist

select * from `subject` where exists (select * from teacher t where t.id = subject.teacher_id);

相当于如下，可以看到，用到了内表teacher的索引有，跟subject的关系不大

for (int i = 0; i < count(subject); i++) {
  a = get_record(subject, i);    # 从subject表逐条获取记录
  if (teacher.tacher_ide = subject[id]){       # 如果子条件成立
    result[] = subject;
    }
}
return result;

查询2:in

select * from `subject` where subject.teacher_id in (select id from teacher);

比如teacher的id有1，2，3，相当于如下，用到了外表subject的索引，跟teacher的关系不大

select * from `subject` where subject.teacher_id=1 or subject.teacher_id=2 or subject.teacher_id=3;

结论

外表小，内表大时，exists 效率高，前提是使用了列的索引。

外表大，内表小时，in效率高

3、使用了or，可能会导致全表扫描

• name有索引，ni_name没有索引，则不走索引

select * from teacher t where `name`='' OR ni_name='分享';

• name有索引，teacher_no有索引，则走索引

select * from teacher t where `name`='关注' OR teacher_no='在看';

结论：

or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的

4、在where的等号左边使用公式

where等号左边计算

select * from student_score t where t.score/2=10;

where等号右边计算

select * from student_score t where t.score=10*2;

结论

在where等号右边计算才能走索引

5、where条件，使用了<>或者！=

select * from A where name!='kangkang's

select * from A where name!='kangkang'

首先不等于是否走索引，是不确定的

比如：A表，90%是不等于kangkang的，那查询时间跟全表扫描会差不多(数据量跟全表比为9:10)，而走索引还需要第一个步读取索引的时间，这样一来走索引反而比全表用时要长，mysql就会选择最优的方案，即全表扫描

那什么时候会走索引呢？

比如下面，name有索引，查询的也是name，可以走索引

select name from A where name!='kangkang'

结论

• 不等于，可以走索引，也可以不走索引

• 查询的字段是索引字段，就会走索引，联合索引也一样（"not in"同理）

6、where条件，包含复合索引，但不是前置列

比如复合为name、age、score，以下sql没有包含前置列name字段

select * from A where age=11 and score=80

按照mysql最左匹配原则，不走索引

7、隐式类型转换

select * from A where name=1

name字段类型为varchar，也就是字符串，而sql中用name=1来查询，sql内部自动进行了类型转换，不走索引

二、select 查询优化

1、避免使用select *

select * 取出全部列，会导致无法完成索引覆盖扫描的优化，增加网络带宽消耗和额外的I/O消耗

根据业务，取出实际需要的列

2、针对主从复制场景，避免使用会出现不确定结果的函数

比如now(),rand(),sysdate(),current_user()

3、多表关联，小表在前，大表在后

from后的表关联，第一张表会全表扫描，所以小表在前，能提高扫描效率

4、连接多个表时，使用表的命名

可以减少解析的时间并减少列名歧义引起的语法问题

5、用where替换having

因为having只会在检索出所有记录后，才进行过滤；而where，是在聚合前，筛选记录

6、调整where 字句的顺序

mysql执行顺序，从左往右，自上而下，所以能过滤更多记录的放在前面，最快缩小查询范围

三、其他优化

1、批量插入

insert into A values(1,2)
insert into A values(3,4)

这种多条插入语句，会建立多次的数据库连接

insert into A values(1,2),(3,4)

写成一条sql，只需要解析一次，建立一次连接，减少网络传输IO

2、复杂的查询，可以使用中间临时表，暂存数据

3、group by优化

Mysql会对group by分组的所有值进行排序，比如group by a,b,c；那就会默认进行order by a,b,c

所以，如果不需要进行排序，那可以禁用排序，如下

select a,b,c count(1) from A group by a,b,c order by null

4、使用join替代in

join之所以效率高一些，是因为msyql不需要在内存创建临时表

5、union优化

如果不需要去除重复行，建议使用union all，因为union会给临时表加上distinct，去重，所以效率低

6、大sql拆为小sql

7、建立索引，优先考虑where、order by使用到的字段

8、字段尽量使用数字类型

9、用varchar替代char

比如char(10)，在字段建立时，空间就固定了，varchar，变长字段，可以节省存储空间