在我们平时的工作项目中,会经常性的跟数据库打交道,如何写出一条优雅有高效的sql脚本,提高我们业务查询的效率是我们每一个程序员都必须要考虑的问题。博主在上家公司接手了一个外派项目,光是做sql优化就已经搞死了我们多少小伙伴。之后的几次面试经历也发现,对于数据库调优确实非常的重要,于是找了时间查了一些网上提供的方案,以及博主个人实际项目的总结,最终给出以下几条调优建议。
Mysql调优总结
0、开启慢查询输出
指定慢查询的时间,将多余此时间的慢查询sql输出到log里
Show variables like ‘long_query_time’; 显示慢查询时间
Set long_query_time=2; 设置慢查询时间
1、使用EXPLAN查看执行计划
关注type和key type:至少达到range级别,避免all key:索引名
2、能用between就不用了in
3、select 后面指明查询的字段名 禁止 * 会造成不必要的CPU/IO/内存/带宽
4、如果排序字段没有用到索引,就尽量少排序
5、如果限制条件中其他字段没有索引,尽量少用or,用union all或union代替
6、尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,
增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。
7、in和exists 、not in和not exists
in适合于外表大而内表小的情况
exists适合外表小内表大的情况
推荐使用not exists
8、分页查询建议先取上一页最大行数的ID来限制下一页的起点
9、避免在where子句中对字段进行值判断 会导致索引失效
10、不建议使用%前缀模糊查询 会导致索引失效
11、避免在where子句中对字段进行表达式操作 会导致索引失效
12、避免隐式类型转换
where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。
13、对于联合索引来说,要遵守最左前缀匹配规则
14、必要时可以使用force index来强制查询走某个索引
15、注意范围查询语句
对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。
16、关于JOIN优化
尽量使用inner join,避免left join
合理利用索引:被驱动表的索引字段作为on的限制字段
利用小表去驱动大表
17、高频率的字段加上索引,提高查询效率
18、根据业务需要做相关字段的冗余
持续更新中…