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
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(不以通配符%开头)