CREATE TABLE `player` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `player_id` varchar(256) NOT NULL COMMENT '运动员编号',  `player_name` varchar(256) NOT NULL COMMENT '运动员名称',  `height` int(11) NOT NULL COMMENT '身高',  `weight` int(11) NOT NULL COMMENT '体重',  `game_performance` text COMMENT '最近一场比赛表现',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

@SpringBootTest(classes = TestApplication.class)@RunWith(SpringJUnit4ClassRunner.class)public class PlayerServiceTest {
    @Resource    private PlayerRepository playerRepository;
    @Test    public void initBigData() {        for (int i = 0; i < 1000000; i++) {            PlayerEntity entity = new PlayerEntity();            entity.setPlayerId(UUID.randomUUID().toString());            entity.setPlayerName("球员_" + System.currentTimeMillis());            entity.setWeight(150);            entity.setHeight(188);            entity.setGamePerformance("{\"runDistance\":8900.0,\"passSuccess\":80.12,\"scoreNum\":3}");            playerRepository.insert(entity);        }    }}

select * from player limit 990000,5

select * from player a, (select id as tmpId from player limit 990000,5) b WHERE a.id = b.tmpId

explain select * from player a, (select id as tmpId from player limit 990000,5) b WHERE a.id = b.tmpId

select * from player where id > 990000 LIMIT 5

explain select * from player where id > 990000 LIMIT 5

<mapper namespace="com.test.java.front.test.mysql.deep.page.repository.PlayerRepository">
 <resultMap id="BaseResultMap" type


    
="com.test.java.front.test.mysql.deep.page.entity.PlayerEntity">  <id column="id" jdbcType="BIGINT" property="id" />  <result column="player_id" jdbcType="VARCHAR" property="playerId" />  <result column="player_name" jdbcType="VARCHAR" property="playerName" />  <result column="height" jdbcType="INTEGER" property="height" />  <result column="weight" jdbcType="INTEGER" property="weight" />  <result column="game_performance" jdbcType="LONGVARCHAR" property="gamePerformance" /> resultMap>
 <sql id="Base_Column_List">  id, player_id, player_name, height, weight, game_performance sql>
 <sql id="conditions">  <where>   <if test="playerId != null">    and player_id = #{playerId,jdbcType=VARCHAR}   if>  where> sql>
 <sql id="pager">  <if test="skip != null and limit != null">   limit #{skip}, #{limit}  if> sql>
  <select id="selectPageCount" parameterType="com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam" resultType="java.lang.Long">  select count(*) from player  <include refid="conditions" /> select>
   <select id="selectPager1" parameterType="com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam" resultMap="BaseResultMap">  select  <include refid="Base_Column_List" />  from player  <include refid="conditions" />  <include refid="pager" /> select>
   <select id="selectPager2" parameterType="com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryParam" resultMap="BaseResultMap">  select  <include refid="Base_Column_List" />  from player a,


    
  (  select id as tmpId from player  <include refid="conditions" />  <include refid="pager" />  ) b  where a.id = b.tmpId select>
   <select id="selectPager3" parameterType="com.test.java.front.test.mysql.deep.page.param.biz.PlayerQueryIdParam" resultMap="BaseResultMap">  select  <include refid="Base_Column_List" />  <include refid="conditions" />  from player where id > #{startId} limit #{pageSize} select>mapper>