介绍远比mybatis强大的sqltoy-orm框架中的:最强分页(没有之一)

开源项目

github地址： https://github.com/sagframe/sagacity-sqltoy

gitee地址： https://gitee.com/sagacity/sagacity-sqltoy

在开场之前，先简单说一下sqltoy是有基于对象的crud的，通过quickvo产生pojo通过annotation对应表和字段。但这里不作介绍。

一说最强大，很多人带着质疑进来了，我想说不会让你失望的，会给你带来不一样的视角和对问题处理的策略！

 

我们假设一个场景：查询员工信息表

1. 显示信息： 工号、姓名、岗位名称、部门名称、入职日期、手机号码、邮箱
2. 查询条件： 入职日期在2019年1月到2019年12月，姓陈的员工
3. 排序规则： 按照入职日期逆序

分析：涉及员工表、机构表(取机构名称)、数据字典表（取岗位名称）

代码：

select t1.STAFF_ID,
       t1.STAFF_NAME,
       t1.ORGAN_ID,
	   -- 子查询岗位名称,应该还有性别等,这里简化一下
	   (select dict_name 、
        from sys_dict_detail 
        where dict_type='POST_TYPE' 
              and dict_key=t1.POST_CODE) postName,
	   t2.ORGAN_NAME,
	   t1.DUTY_DATE,
	   t1.EMAIL,
	   t1.MOBILE_NO
from  sys_staff_info t1 
      left join sys_organ_info t2 
	  on t1.ORGAN_ID=t2.ORGAN_ID
where t1.name like '陈%'
	  and t1.DUTY_DATE>='2019-01-01' 
      and t1.DUTY_DATE<='2019-12-31'
order by t1.DUTY_DATE desc

• 普通级别的分页：

1. select count(1) from (上述sql)   
2. 上述sql 结尾加上 limit ? offset ?

• 稍微优化一点的：看到count语句有优化的空间，没有必要中间有计算或子查询，排序也没有必要

1. select count  form （上述sql  from 后面的语句，并剔除order by）
2. 上述sql 结尾加上limit ? offset ?

• 更加科学的优化(快速分页)：发现查询条件在员工上，应该考虑先将员工符合条件的记录提取出一页(10条)，再跟机构表、数据字典表关联将大幅提升性能。

1. @fast() 最终效果就是:select t1.*,t2.xxx from (select xxx from table1 where xx=:xx1 limit ? offset ? ) t1 left join table2 t2 on t1.xxx=t2.xxx
2. 也就是先实现数据最小化，然后用最少的数据跟其他表进行关联，实现效率最优。

 <sql id="sqltoy_fastPage">
		<value>
			<![CDATA[
			select  t1.*,
			        (select dict_name 
			        from sys_dict_detail 
                    where dict_type='POST_TYPE' 
                          and dict_key=t1.POST_CODE) postName,
			        t2.ORGAN_NAME
		    -- @fast 实现先分页提取最小范围数据，再跟其他表关联查询 
			from  @fast(select STAFF_ID,
                              STAFF_NAME,
                              ORGAN_ID,
                              POST_CODE,
                              DUTY_DATE,
                              EMAIL,
                              MOBILE_NO
                       from  sys_staff_info 
                       where #[name like :staffName]
                             #[and  DUTY_DATE>=:beginDate]
                             #[and  DUTY_DATE<=:endDate]
                       order by t1.DUTY_DATE desc
                 ) t1 
                 left join sys_organ_info t2 
			     on t1.ORGAN_ID=t2.ORGAN_ID
				]]>
		</value>
</sql>

• 更进一步：分页优化；到上述一步的时候，很多人说，你该结束了吧？我告诉你还可以优化，分析一下：

1. 我们发现分页查询一般都是2次数据查询，第一次：取count记录数；第二次：取单页结果数据。
2. 优化的重点就在于是否每次都需要2次查询。
3. 策略：利用缓存在一定时效内保存相同条件查询的count记录数，可以避免2次查询
4. 请参看：```<page-optimize alive-max="100" alive-seconds="120" />```  分页优化设置

<sql id="sqltoy_fastPage">
    	<!--alive-max:表示存放100条不同条件的count结果; alive-seconds:表示每个条件组合的结果只在120秒内有效 -->
    	<page-optimize alive-max="100" alive-seconds="120" />
		<value>
			<![CDATA[
			select  t1.*,
			        (select dict_name 
			        from sys_dict_detail 
                    where dict_type='POST_TYPE' 
                          and dict_key=t1.POST_CODE) postName,
			        t2.ORGAN_NAME
		    -- @fast 实现先分页提取最小范围数据，再跟其他表关联查询 
			from  @fast(select STAFF_ID,
                              STAFF_NAME,
                              ORGAN_ID,
                              POST_CODE,
                              DUTY_DATE,
                              EMAIL,
                              MOBILE_NO
                       from  sys_staff_info 
                       where #[name like :staffName]
                             #[and  DUTY_DATE>=:beginDate]
                             #[and  DUTY_DATE<=:endDate]
                       order by t1.DUTY_DATE desc
                 ) t1 
                 left join sys_organ_info t2 
			     on t1.ORGAN_ID=t2.ORGAN_ID
				]]>
		</value>
</sql>

• 还有优化的空间吗?我告诉你另类思维：降维打击，不走寻常路！

1. 我们分析一下，上面所有的一切都从分页的视角在优化，但其实我们还可跳出圈圈，祭出大招：缓存翻译！
2. 看一下下面的sql编写，变成了单表查询！动态条件还很雅观！

<sql id="sqltoy_fastPage">
		<!-- alive-max:不同查询条件的总记录量; alive-seconds:查询条件记录量存活时长-->
		<page-optimize alive-max="100" alive-seconds="120" />
        <!-- 通过缓存对代码直接翻译，根本上去除掉一些不必要的关联查询 -->
		<translate cache="dictKeyName" cache-type="POST_TYPE" columns="postName"/>
		<translate cache="organIdName" columns="ORGAN_NAME"/>
		<!-- 对电话号码做一个安全脱敏 -->
		<secure-mask columns="MOBILE_NO" type="tel"/>
		<value>
			<![CDATA[
			select   t1.STAFF_ID,
			         t1.STAFF_NAME,
			         t1.ORGAN_ID,
			         t1.DUTY_DATE,
			         t1.POST_CODE postName,
			         t1.ORGAN_ID ORGAN_NAME,
			         t1.EMAIL,
			         t1.MOBILE_NO
            from   sys_staff_info 
            where  #[name like :staffName]
                   #[and  DUTY_DATE>=:beginDate]
                   #[and  DUTY_DATE<=:endDate]
            order by t1.DUTY_DATE desc
				]]>
	   </value>
</sql>