记一次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 ,至此优化完毕。