目錄

• 1 深分頁問題
• 1.1 創(chuàng)建表
• 1.2 新增100萬條數(shù)據(jù)
• 1.3 深分頁語句
• 1.4 結(jié)果分析
• 2 深分頁優(yōu)化方案
• 2.1 方案一
• 2.2 方案二
• 2.2.1 優(yōu)化語句
• 2.2.2 執(zhí)行計(jì)劃
• 2.2.3 結(jié)果分析
• 2.3 方案三
• 2.3.1 優(yōu)化語句
• 2.3.2 執(zhí)行計(jì)劃
• 3 MyBatis
• 4 文章總結(jié)

1 深分頁問題

1.1 創(chuàng)建表

CREATE TABLE `player` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "主鍵",  `player_id` varchar(256) NOT NULL COMMENT "運(yùn)動(dòng)員編號",  `player_name` varchar(256) NOT NULL COMMENT "運(yùn)動(dòng)員名稱",  `height` int(11) NOT NULL COMMENT "身高",  `weight` int(11) NOT NULL COMMENT "體重",  `game_performance` text COMMENT "最近一場比賽表現(xiàn)",  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

1.2 新增100萬條數(shù)據(jù)

@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 結(jié)果分析

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

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

2.1 方案一

我們可以從業(yè)務(wù)形態(tài)維度去解決，可以參考搜索引擎解決方案。因?yàn)镋S也存在深分頁問題，搜索引擎解決方案是在業(yè)務(wù)上會限制查詢頁數(shù)。因?yàn)轫摂?shù)越大，內(nèi)容相關(guān)度越低，所以頁數(shù)太大對業(yè)務(wù)價(jià)值不高。MySQL可以類比處理：

• 限制查詢頁數(shù)
• 限制全量導(dǎo)出
• 查詢時(shí)要求帶必要條件（時(shí)間范圍、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í)行計(jì)劃

(1) 查看計(jì)劃

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相同則按照行數(shù)從上到下執(zhí)行
• 本語句執(zhí)行順序如下圖：

• 第一步和第二步表示執(zhí)行子查詢
• 第三步表示player表與子查詢關(guān)聯(lián)

(3) explain type

訪問類型是重要分析指標(biāo)：

(4) explain Extra

Extra表示執(zhí)行計(jì)劃擴(kuò)展信息重點(diǎn)關(guān)注三個(gè)：

2.2.3 結(jié)果分析

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

2.3 方案三

2.3.1 優(yōu)化語句

select * from player where id > 990000 LIMIT 5

2.3.2 執(zhí)行計(jì)劃

(1) 查看計(jì)劃

explain select * from player where id > 990000 LIMIT 5

(2) 結(jié)果分析

• 查詢耗時(shí)：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>	<!-- 查詢條數(shù) -->	<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 文章總結(jié)

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

以上就是MySQL深分頁問題原理與三種解決方案的詳細(xì)內(nèi)容，更多關(guān)于MySQL深分頁的資料請關(guān)注其它相關(guān)文章！