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深分頁的資料請關注腳本之家其它相關文章!
相關文章
MySQL5.73?root用戶密碼修改方法及ERROR?1193、ERROR1819與ERROR1290報錯解決
這篇文章主要給大家介紹了關于MySQL5.73?root用戶密碼修改方法及ERROR?1193、ERROR1819與ERROR1290:...?running?with?--skip-...報錯的解決方法,文中通過圖文將解決的步驟介紹的非常詳細,需要的朋友可以參考下2023-02-02Navicat連接mysql報錯2003(10060)的解決方法
本來好好的navicat連接數據庫,突然間今天就打不開數據庫了,下面這篇文章主要給大家介紹了關于Navicat連接mysql報錯2003(10060)的解決方法,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2023-04-04IDEA鏈接MySQL報錯08001和連接成功后不顯示表的問題及解決方法
這篇文章主要介紹了IDEA鏈接MySQL報錯08001和連接成功后不顯示表的問題及解決方法,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-10-10mysql使用insert into select插入查出的數據
這篇文章主要介紹了mysql使用insert into select插入查出的數據方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12超詳細mysql left join,right join,inner join用法分析
比較詳細的mysql的幾種連接功能分析,只要你看完就能學會的好東西2008-08-08