1,背景:普通的left join 两张表关联查询,短信信息表+短信类型表,使用oracle + mybatis 

PageHelper分页;短信信息表数据量比较大,50万+ 并且每天上千条的增加速度。并且其中两个字段长度为4000,存储短信发送内容及相应json(别的地方传来的数据),必须按发送时间排序。

2,情况:正常情况下查询速度还算可以,1-2秒左右。由于系统每天早上执行很多定时任务,会大量录入数据到短信信息表。所有早上经常遇到查询执行过慢,的情况

service代码:

记录一次分页优化_数据

 mapper.xml

记录一次分页优化_分页_02


记录一次分页优化_优化_03

 3,查看控制台sql,这是mybatis pageHaper 分页sql

记录一次分页优化_分页_04

 4,测试:单表查询 MSG_SM 还可以,加上order by 会稍慢一点,查资料有说排序数据量大排序,而排序空间小,不走排序内存而走磁盘。推荐改oracle排序内存。考虑到是生产环境,先考虑到代码层面解决,实在不行再动数据库配置。再次测试给排序字段增加索引,速度能快些。但是经过测试,还是在每天早上频繁写入的情况下查询分页很慢十几二十几秒,而不带分页查询的话会是比较正常1-2秒。所以决定修改代码,修改分页方式。

5,分析mybatis PageHelper分页,由于分页前会执行查询count  sql查询总数  所以一种情况,改掉count sql以优化PageHelper查询count这一部分的查询

        查看源码得知,PageHelper执行时pageInterceptor拦截器会判断在mapper里是否存在_COUNT的自定义查询。

记录一次分页优化_分页_05


记录一次分页优化_分页_06

那么在mapper里自定义一个查询方法_COUNT的接口。并在xml自定义查询总数的sql ,查询总数的部分会得到优化(干掉不必要的关联,只查总数)。

 6,本次使用优化:重写查询总数sql以及查询sql,不适用PageHelper自动分页。代码如下:

记录一次分页优化_数据_07

记录一次分页优化_sql_08


记录一次分页优化_sql_09


记录一次分页优化_优化_10

记录一次分页优化_优化_11

思路,避过大数据量、大字段情况下的查询,先查询满足数据的id,再关联查询其他数据。 经测试,无论是否再写入数据的高峰期还是平时查询速度都是不到一秒/一秒左右。

        问题解决,仅供参考,欢迎大佬们指教。