一、场景描述:springmvc项目,mybatis,oracle数据库,目标表中有大概三百万条数据。需求要能够分页展示。
二、问题描述:使用pageHelper分页插件,对查出来的list进行分页。结果是一直pending的状态,最后超时报错。
三、知识储备:逻辑分页和逻辑分页
逻辑分页只需要访问一次数据库,是把DB中数据一次性查出来,然后通过自己在代码中利用分页插件或其他方法实现分页。这种情况在数据量小的情况下还好,但是数据量大的话,会占用很多内存,并且查询比较耗时。
物理分页是每次查询都要连接数据库,查询的条数就是你要查的那一页的内容。所以数据量大也不会有太大影响。
四、优化思路:
1、加好各种限制条件,尽量让查询范围缩小。
2、采用物理分页。
五、代码示例:
SELECT
*
FROM
(
SELECT
ROWNUM RN,
A.*
FROM
(SELECT
UDISPLAY userName,
UCARDNO cardNo,
USTATUS cardStatus,
UTYPE cardType,
STARTDATE startDate,
count(*) over () totalCount
FROM
TABLE_NAME
WHERE AREA = 'shanghai'
<if test="userBO.userName != null and userBO.userName != ''">
AND UDISPLAY = #{userBO.userName}
</if>
<if test="userBO.cardNo != null and userBO.cardNo != ''" >
AND UCARDNO = #{userBO.cardNo}
</if>
<if test="userBO.cardStatus != null and userBO.cardStatus != ''">
AND USTATUS = #{userBO.cardStatus}
</if>
<if test="userBO.cardType != null and userBO.cardType != ''">
AND UTYPE = #{userBO.cardType}
</if>
) A
WHERE
ROWNUM <= #{userBO.endRowNum}
)
WHERE
RN >= #{userBO.startRowNum}
说明:上面的startRowNum和endRowNum是计算出来的:
startRowNum = (currentPage - 1) * pageSize + 1
endRowNum = currentPage * pageSize
其中currentPage代表当前页数,pageSize代表每页行数。
六、代码解析:
a) 最内层的where条件就是加上各种限制条件,使得查询范围尽量缩小。
b) 利用 count(*) over () 查询出总条数。
七、结果对比:
优化前:接口一直pending状态,sql查询大概要花费十几秒。
优化后:接口平均响应时间大概0.5秒左右。
以上仅为我本次优化中实际操作总结,如果有些地方过于主观或片面,欢迎指导,一起进步,加油!