记一次sql优化
先看表的基本信息:
表中的数据大概330w,项目用的是spring jpa,查询的sql语句为:
@Query(value="select * from message_data md where 1=1 "
+ "and if(\"\" = :startDate,1=1,md.create_date >= :startDate) "
+ "and if(\"\" = :endDate,1=1,md.create_date <= :endDate) "
+ "and if(\"\" = :messageType,1=1,md.message_type = :messageType) "
+ "and if(\"\" = :messageChildrenType,1=1,md.message_children_type = :messageChildrenType) "
+ "and if(\"\" = :keyword,1=1,md.task_name like :keyword) "
+ "and if(\"成功\" = :successedStatus,md.successed = 'Y',1=1) "
+ "and if(\"失败\" = :successedStatus,md.successed = 'N',1=1) "
+ "and if(\"已读\" = :read,md.message_read = 'Y',if(\"未读\" = :read,md.message_read = 'N',1=1)) "
+ "order by md.message_read asc, md.create_date desc "
keyword的为 xxx%, 以下结果 测试语句中 startDate和endDate都为“”,若有这两个参数只需下面的第一个优化语句即可。
由于所有的参数都是动态的,所以要进行if判断,执行时间为3.353s,看一下上面sql的执行计划:
没有命中任何索引,因为where中if 属于表达式,所以导致全表扫描,优化后的sql:
@Query(value="select * from message_data md where 1=1 "
+ "and ((\"\" != :startDate and md.create_date >= :startDate) or \"\"= :startDate) "
+ "and ((\"\" != :endDate and md.create_date <= :endDate) or \"\"= :endDate) "
+ "and ((\"\" != :messageType AND md.message_type = :messageType) or \"\"= :messageType) "
+ "and ("
+"(\"\" != :messageChildrenType and md.message_children_type = :messageChildrenType) "
+"or \"\"= :messageChildrenType"
+") "
+ "and ((\"\" != :keyword and md.task_name like :keyword) or \"\" = :keyword )"
+ "and ("
+"(\"成功\" = :successedStatus and md.successed = 'Y') "
+"or (\"失败\" = :successedStatus and md.successed = 'N')"
+" or \"\" =:successedStatus"
+")"
+ "and ("
+"(\"已读\" = :read and md.message_read = 'Y')"
+"or (\"未读\" = :read and md.message_read = 'N')"
+ "or \"\" = :read "
"order by md.create_date desc "
+ "limit :pageNo, :pageSize", nativeQuery=true)
再查看一下执行计划:
可以看到使用的索引为createdate,执行时间9.479s,比没有优化的时间还长6s !!!,经过分析了解到用task_name索引性能要高一些,这是sql优化器选错索引导致,继续优化sql为:
@Query(value="select * from message_data mess JOIN " +
"(select md.id from message_data md where 1=1 "
+ "and ((\"\" != :startDate and md.create_date >= :startDate) or \"\"= :startDate) "
+ "and ((\"\" != :endDate and md.create_date <= :endDate) or \"\"= :endDate) "
+ "and ((\"\" != :messageType AND md.message_type = :messageType) or \"\"= :messageType) "
+ "and ("
+"(\"\" != :messageChildrenType and md.message_children_type = :messageChildrenType) "
+"or \"\"= :messageChildrenType"
+") "
+ "and ((\"\" != :keyword and md.task_name like :keyword) or \"\" = :keyword )"
+ "and ("
+"(\"成功\" = :successedStatus and md.successed = 'Y') "
+"or (\"失败\" = :successedStatus and md.successed = 'N')"
+" or \"\" =:successedStatus"
+")"
+ "and ("
+"(\"已读\" = :read and md.message_read = 'Y')"
+"or (\"未读\" = :read and md.message_read = 'N')"
+ "or \"\" = :read "
+")"
+") temp on mess.id=temp.id "
+ "order by mess.create_date desc "
+ "limit :pageNo, :pageSize", nativeQuery=true)
查看执行计划:
执行时间:0.010s ,至此优化完毕。