使用当当sharding-jdbc分表，Mysql LIMIT分页的优化方案

当当sharding-jdbc官方文档，分表分页的性能问题以及优化方案 http://shardingjdbc.io/1.x/docs/02-guide/subquery/

通过记录上次查询结果的最后一条记录的ID进行下一页的查询：

SELECT * FROM t_order WHERE id > 100000 LIMIT 10

因此，衍生出另一个问题，如何获取上次查询结果的最后一条记录的ID?

暂时的SQL写法有两种：

1）

	<select id="getPreviousSequenceID" resultType="java.lang.String">
		select max(temp.SequenceID) from (
			select e.SequenceID
			  from tableXXXXXX e
			 where e.UserID = #{userId}
				<if test="startDate != null" >
					<![CDATA[ AND e.Adddate >= #{startDate} ]]>
				</if>
				<if test="endDate != null" >
					<![CDATA[ AND e.Adddate <= #{endDate} ]]>
				</if>
			  ORDER BY SequenceID ASC
			  limit #{preTotalCount}
		) temp
	</select>

2)

        <select id="getPreviousSequenceID" resultType="java.lang.String">
		select SequenceID
		  from tableXXXXXX 
		 where UserID = #{userId}
			<if test="startDate != null" >
				<![CDATA[ AND Adddate >= #{startDate} ]]>
			</if>
			<if test="endDate != null" >
				<![CDATA[ AND Adddate <= #{endDate} ]]>
			</if>
   		 ORDER BY SequenceID ASC
		 limit #{preTotalCount}, 1
	</select>

参考下面文章，里面有详细分析max和limit 10000,1 两种方法的比较

http://database.51cto.com/art/201005/200395.htm

文章的结果如下：

第1句执行结果.100 rows in set (0.23) sec

第2句执行结果.100 rows in set (0.19) sec

自己也测试过，结果如下：

只是依据简单的测试结果，limit 10000,1的性能好于用max，MySQL5.0中Mysql limit性能应该作了优化。

下面是查询的SQL用于替代limit：

	<select id="queryUserCommisionList" resultType="com.tuandai.ms.aqs.domain.ExtendEarnRecord">
		select SequenceID, ID, UserID AS userId
		  from tableXXXX
		 where UserID = #{userId}
			<if test="startDate != null" >
				<![CDATA[ AND Adddate >= #{startDate} ]]>
			</if>
			<if test="endDate != null" >
				<![CDATA[ AND Adddate <= #{endDate} ]]>
			</if>

			<if test="beginSequenceID != null and beginSequenceID != '' "  >
				<![CDATA[ and SequenceID >= #{beginSequenceID} ]]>
			</if>
		<!--
			sharding-jdbc不支持这种类型的子查询 SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))
		-->
		<!--
			<if test="beginIndex != null and beginIndex != '' "  >
				<![CDATA[ and SequenceID >=  ]]> (select MAX(e.SequenceID) from tableXXXX e where e.UserID = #{userId} LIMIT #{beginIndex}, 1)
			</if>
		-->

		 ORDER BY SequenceID ASC
		 limit #{pageSize}
	</select>