Sql优化首先需要找到需要优化的sql,也就是执行比较慢的sql语句,我们在项目中主要用mysql数据库较多,以mysql数据库为例,可以采用开启mysql慢日志,通过set global slow_query_log=1语句开启慢查询日志,通过show variables like '%slow_query_log%' 查看慢查询日志开启状态和存储位置,通过设置long_query_time的时间,来界定执行时间超过多久的sql为慢查询sql,设置log-queries-not-using-indexes可以把未使用索引的sql语句也输出到慢查询中。也可以使用第三方数据库连接池比如德鲁伊(druid),设置logSlowSql属性为true,通过slowSqlMillis设置慢sql的界定时间。查找到执行较慢的sql语句之后,根据具体问题对sql语句进行分析,导致sql语句执行较慢的因素主要有
1. 过多的表联查
在拿到执行较慢的sql语句之后,分析其有无冗余的表联查,若存在,则去掉,使用嵌套查询解决多表直接联查问题,可以把多个表的查询结果分开执行,然后再把结果合并到一块。若无法解决表联查问题,可以把表的联查结果写入mongoDB这样的noSQl数据库中,查询时直接走NoSql数据库获取表联查结果,这种适用于查询较多,写入请求较少的情况。
2. 数据量较大的表查询未使用索引,或使用了索引,但是索引未生效。
首先使用sql的执行计划查看当前sql语句有无使用索引,使用索引查询的执行类型是什么,使用EXPLAIN关键字对sql语句进行分析,返回结果中的TYPE字段表示索引的使用情况,ALL代表全表扫描,也就是未使用索引,const表示主键索引或唯一索引,查询效率最高,ref引用查找,查找非唯一索引匹配项,range索引范围查找。Key代表当前查询所使用的实际索引。根据sql执行计划分析索引的使用情况,对于有条件的表(数据量超过10万条以上,查询较频繁)添加索引,对于查询添加了所有,应注意一下几点,a.添加索引的字段必须为where或group by中使用的字段,否则sql语句不会执行索引,多个字段同时查询可以考虑组合索引。b.添加索引的字段不能进行运算操作,因为索引的使用是在sql编译器编译时选择的,若对索引字段进行运算,其值只有在sql语句执行时才能确定,所以会导致索引失效。c使用like模糊查询时前后都有%或前面有%时则索引失效,使用组合索引时第一个字段匹配则走索引,第一个字段不匹配则索引失效,各个字段都匹配索引效率最高。字段顺序尽量与索引顺序保持一致d.使用in或not in 也会使索引失效,若非必须使用,可以考虑使用其它方式替代,例如between或exists。E 加索引的字段不能为空值。F 一个表的索引尽量不要超过6个,否则会降低写入表的性能。
3. 一次查询返回的数据量较大
考虑需求的合理性,采用分页减少数据量的返回
4. 不同数据库之间Sql语句解析器解析方式不同
Mysql查询采用自上而下查询,过滤掉最大数据量的条件必须紧跟where子句之后,缩小查询的范围,例如查询北京市有多少男性用户,则要先查询地址为北京,再查询性别。Oralce采用自下而上的查询方式,过滤最大数据量的条件必须放在where子句的末尾。