欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL深分頁問題原理與三種解決方案

 更新時間:2023年05月04日 08:44:40   作者:JAVA前線  
本文主要介紹了MySql深分頁問題原理與解決方案,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

1 深分頁問題

1.1 創(chuàng)建表

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

1.2 新增100萬條數據

@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);
        }
    }
}

1.3 深分頁語句

select * from player limit 990000,5

1.4 結果分析

  • 查詢耗時:1.233秒
  • 本語句目標查詢[990001-990005]五條數據
  • 但是執(zhí)行時需要排序[1-990005]數據
  • 最終丟棄[1-990000]只返回[990001-990005]數據

2 深分頁優(yōu)化方案

2.1 方案一

我們可以從業(yè)務形態(tài)維度去解決,可以參考搜索引擎解決方案。因為ES也存在深分頁問題,搜索引擎解決方案是在業(yè)務上會限制查詢頁數。因為頁數越大,內容相關度越低,所以頁數太大對業(yè)務價值不高。MySQL可以類比處理:

  • 限制查詢頁數
  • 限制全量導出
  • 查詢時要求帶必要條件(時間范圍、userId)

2.2 方案二

2.2.1 優(yōu)化語句

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

2.2.2 執(zhí)行計劃

(1) 查看計劃
explain select * from player a, (select id as tmpId from player limit 990000,5) b WHERE a.id = b.tmpId

(2) 執(zhí)行順序
  • id越大執(zhí)行順序越靠前
  • id相同則按照行數從上到下執(zhí)行
  • 本語句執(zhí)行順序如下圖:

  • 第一步和第二步表示執(zhí)行子查詢
  • 第三步表示player表與子查詢關聯
(3) explain type

訪問類型是重要分析指標:

(4) explain Extra

Extra表示執(zhí)行計劃擴展信息重點關注三個:

2.2.3 結果分析

  • 查詢耗時:0.5秒
  • 原因是覆蓋索引提升分頁查詢效率(只查詢ID列)
  • 覆蓋索引含義是查詢時索引列完全包含查詢列
  • using index表示使用覆蓋索引,性能提升

2.3 方案三

2.3.1 優(yōu)化語句

select * from player where id > 990000 LIMIT 5

2.3.2 執(zhí)行計劃

(1) 查看計劃
explain select * from player where id > 990000 LIMIT 5

(2) 結果分析
  • 查詢耗時:0.001秒
  • range表示索引范圍搜索性能尚可
(3) 適用場景
  • 不適用跳頁場景
  • 只適用【上一頁】【下一頁】場景

3 MyBatis

<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>

	<!-- 分頁方式1:普通分頁存在深分頁問題 -->
	<!-- select * from player limit 990000,5 -->
	<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>

	<!-- 分頁方式2:覆蓋索引優(yōu)化深分頁問題 -->
	<!-- select * from player a, (select id as tmpId from player limit 990000,5) b where a.id = b.tmpId -->
	<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>

	<!-- 分頁方式3:Id分頁不支持跳頁 -->
	<!-- select * from player where id > 990000 limit 5 -->
	<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>

4 文章總結

本文第一介紹深分頁問題表現和原因。第二介紹深分頁問題三種解決方法,方案一是從業(yè)務維度優(yōu)化,方案二是使用覆蓋索引進行優(yōu)化,方案三是使用Id分頁。第三展示MyBatis相關代碼。

以上就是MySQL深分頁問題原理與三種解決方案的詳細內容,更多關于MySQL深分頁的資料請關注腳本之家其它相關文章!

相關文章

最新評論