1、查询慢的原因
2、优化数据访问
3、执行过程优化
4、优化特定类型的查询
1、查询慢的原因
网络,cpu,io,上下文切换,系统调用,生成统计信息,锁等待时间
2、优化数据访问
查询性能低下的主要原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,可以通过减少访问数据量的方式进行优化
是否向数据库请求了不需要的数据:查询不需要的记录:常常会误以为mysql会只返回需要的数据,实际上mysql却是先返回全部结果再进行计算,在日常的开发习惯中,经常是先用select语句查询大量的结果,然后获取前面的N行后关闭结果集。优化方式是在查询后面添加limit,而limit不是分页,limit是限制输出,分页只是限制输出的一个方向。而limit 20000,5 这种写法也是不要写,容易全表扫描,当limit后面第一个数字比较大的时候,可以考虑改写子查询
多表关联时返回全部列
总是取出全部列
重复查询相同的数据:可以把数据缓存起来
3、执行过程优化
一把来说关闭缓存,这不多说。在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据,如果查询恰好命中了查询缓存,那么会在返回结果之前会检查用户权限,如果权限没有问题,那么mysql会跳过所有的阶段,就直接从缓存中拿到结果并返回给客户端。但是最好关掉。
将left join ,right join 转化为 inner join 。 内连接的效率比外连接效率高。但是也是看具体的需求和写的sql语句,并不是一定内连接的效率比外连接效率高。
4、优化特定类型的查询
关联查询的时候 on 的条件最好是索引列。
group by 、 order by 的列如果能用索引列的话,尽量用索引列,效率会高。
子查询的优化最重要的优化建议是尽可能使用关联查询代替,为什么不推荐使用子查询,因为临时表,子查询的结果会放到临时表里,临时表里也是io,不如直接join。但是还是看具体情况,别都把子查询改成关联查询。
group by 后的列,一般是select的字段除了聚合函数之外都应写在group by 之后,但是group by 后还可以只写标识列,也就是id,没想到吧,写id比写其他列效率高。但是还是看具体的需求来写sql。但是话说回来用id分组有什么意义?id一般来说本来就不重复。所以知道这个意思就行了。
优化limit
select * from table limit 100000,5
优化为
select * from table t1 inner join (select id from table limit 1000000,5) t2 on t1.id=t2.id
不要给我 where id>100000 这种优化,再去inner join 一般id都是uuid,uuid就不是这种id数字连续的了。就用不了where id >这种sql了
因为select * limit 100000,5是全表扫描,改成子查询和关联查询后,select id limit 100000,5 查询的是id,是主键索引,所以快,最后再关联查询这五条的select *
优化此类查询的最简单的办法就是尽可能地使用覆盖索引,而不是查询所有的列,上面还是select * 可以适当的改一下。