Sharding JDBC分页查询

概要：虽然数据分片解决了性能、可用性以及单点备份恢复等问题，但分布式的架构在获得了收益的同时，也引入了新的问题。

sharding JDBC 默认使用limit进行分页，在不指定路由字段时，分页将会全库全表全数据捞取，然后进行排序。

以MySQL为例：

SELECT * FROM user ORDER BY id LIMIT 1000000, 10

这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。 而在分库分表的情况下（假设分为2个库），为了保证数据的正确性，SQL会改写为：

SELECT * FROM user ORDER BY id LIMIT 0, 1000010

mybatis plus 分页插件测试

• UserMapper

package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {

    IPage<User> selectPageByEnable(Page<?> page, Integer enable);

}

• userMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.demo.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.entity.User">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="phone" property="phone" />
        <result column="create_time" property="createTime" />
        <result column="enable" property="enable" />
        <result column="version" property="version" />
    </resultMap>

    <select id="selectPageByEnable" resultMap="BaseResultMap">
        SELECT * FROM user WHERE enable=#{enable} order by create_time desc
    </select>
</mapper>

• 查看sql

2020-05-11 14:30:04.525 DEBUG 2956 --- [nio-8080-exec-2] c.e.d.m.UserMapper.selectPageByEnable    : ==>  Preparing: SELECT COUNT(1) FROM user WHERE enable = ? 
2020-05-11 14:30:04.539 DEBUG 2956 --- [nio-8080-exec-2] c.e.d.m.UserMapper.selectPageByEnable    : ==> Parameters: 1(Integer)
2020-05-11 14:30:04.882  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2020-05-11 14:30:04.882  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Logic SQL: SELECT COUNT(1) FROM user WHERE enable = ?
2020-05-11 14:30:04.883  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@6f15e3b2), containStar=false, firstSelectItemStartPosition=7, selectListLastPosition=16, groupByLastPosition=0, items=[AggregationSelectItem(type=COUNT, innerExpression=(1), alias=Optional.absent(), derivedAggregationSelectItems=[], index=-1)], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2020-05-11 14:30:04.883  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db0 ::: SELECT COUNT(1) FROM user0 WHERE enable = ? ::: [[1]]
2020-05-11 14:30:04.883  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db0 ::: SELECT COUNT(1) FROM user1 WHERE enable = ? ::: [[1]]
2020-05-11 14:30:04.883  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: SELECT COUNT(1) FROM user0 WHERE enable = ? ::: [[1]]
2020-05-11 14:30:04.884  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: SELECT COUNT(1) FROM user1 WHERE enable = ? ::: [[1]]
2020-05-11 14:30:05.210 DEBUG 2956 --- [nio-8080-exec-2] c.e.d.m.UserMapper.selectPageByEnable    : ==>  Preparing: SELECT * FROM user WHERE enable=? order by create_time desc LIMIT ?,? 
2020-05-11 14:30:05.210 DEBUG 2956 --- [nio-8080-exec-2] c.e.d.m.UserMapper.selectPageByEnable    : ==> Parameters: 1(Integer), 5(Long), 5(Long)
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Logic SQL: SELECT * FROM user WHERE enable=? order by create_time desc LIMIT ?,?
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=io.shardingsphere.core.parsing.parser.sql.dql.select.SelectStatement@26b49cd1), containStar=true, firstSelectItemStartPosition=7, selectListLastPosition=9, groupByLastPosition=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(create_time), orderDirection=DESC, nullOrderDirection=ASC, index=-1, expression=null, alias=Optional.absent())], limit=Limit(offset=LimitValue(value=5, index=1, boundOpened=false), rowCount=LimitValue(value=5, index=2, boundOpened=false)), subQueryStatement=null, subQueryStatements=[], subQueryConditions=[])
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db0 ::: SELECT * FROM user0 WHERE enable=? order by create_time desc LIMIT ?,? ::: [[1, 0, 10]]
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db0 ::: SELECT * FROM user1 WHERE enable=? order by create_time desc LIMIT ?,? ::: [[1, 0, 10]]
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: SELECT * FROM user0 WHERE enable=? order by create_time desc LIMIT ?,? ::: [[1, 0, 10]]
2020-05-11 14:30:05.223  INFO 2956 --- [nio-8080-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: SELECT * FROM user1 WHERE enable=? order by create_time desc LIMIT ?,? ::: [[1, 0, 10]]

查询偏移量过大的分页会导致数据库获取数据性能低下。

分页方案优化

由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案（无法跳页查询）：

SELECT * FROM user WHERE id > 100000 AND id <= 100010 ORDER BY id

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

SELECT * FROM user WHERE id > 100000 LIMIT 10