SQL优化

1、考虑在where和order by涉及到的列建立合理的索引

2、避免where使用is null判断

select * from user where update_time is null;

可以在update_time字段上加上默认值

select * from user where update_time='0';

索引不会包含有null值的列

只要列中包含有nul值将不会被包含在索引中，符合索引中只要有一列含有null值，那么这一列对此复合索引都是无效的，所以我们在数据库设计时不要让字段的默认值为null

3、避免where条件中使用负向条件，比如!=、<>、not in、not exists

select * from user whrere sex!='男';

应该改为：

select * from user where sex='女';

4、避免where表达式中，列中使用表达式

select * from user where age/2=20;

将在每行上进行运算，索引实效，导致全表扫描

应改为

select * from user where age=40;

5、复合索引最左前缀，要保证第一列被使用，否则无法使用符合索引（注意： 并不是顺序一致）

比如(name,sex,age)是组合索引

select * from user where name='张三' and sex='男';
select * from user where age='24'and sex='男' and name='张三' ;
select * from user where age='24' and name='张三';

都含有name列，所以都能能命中索引，满足最左前缀的复合索引

select * from user where sex='男' and age='24';

不满足最左前缀的复合索引，不能命中索引

6、避免使用前模糊匹配

select * from user where name like '%%XX%%'

如果可以，尽量使用后模糊匹配

select * from user where name like 'XX%%'

尽量不要使用like，因为like '%%XX%%' 不会使用索引， like 'XX%%'使用索引

7、查询中某个列有范围查询，则右边所有的列都无法使用索引

mysql会按照联合索引从左往右进行匹配，直到遇到范围查询，例如遇到>、<、between、like等就停止匹配

比如index：（a,b,c,d）

d 不使用索引

b,c,d也不使用索引

a,d,c使用索引

a=1 and b=2 and c>7 and d=2    d不使用索引

但是如果索引是（a,b,d,c）   a=1 and b=2 and c>7 and d=2     a,b,d,c都使用索引

8、避免select *， 查询不必要的字段

9、索引数量尽量控制在，每张表不要超过6个

10、分页查询优化

数据量较大时，如果起始行较大，查询效率会很低

select * from user where create_time>'2017-01-01 00:00:00' limit 100000,100;

解决办法：

select *from user where id in(select id from user where create_time>'2017-01-01 00:00:00' limit 100000,100);

select * from user where id >= (select id from user where create_time>'2017-01-01 00:00:00' limit 100000,1) limit 100;

select * from user,(select id from user where create_time>'2017-01-01 00:00:00' limit 100000,100) tmp where user.id=tmp.id

11、索引列排序order by

MySQL查询只使用一个索引，所以如果where子句中已经使用索引的话，order by的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下，尽量不要使用排序操作。如果有多个列的排序，最好给这些列创建复合索引。

总结：

mysql只对以下操作符使用索引

<，<=，>，>=，between，in以及like（不以通配符%开头）