国产成人精品久久免费动漫-国产成人精品天堂-国产成人精品区在线观看-国产成人精品日本-a级毛片无码免费真人-a级毛片毛片免费观看久潮喷

您的位置:首頁技術(shù)文章
文章詳情頁

MySQL limit分頁大偏移量慢的原因及優(yōu)化方案

瀏覽:4日期:2023-10-09 09:18:53

在 MySQL 中通常我們使用 limit 來完成頁面上的分頁功能,但是當(dāng)數(shù)據(jù)量達(dá)到一個很大的值之后,越往后翻頁,接口的響應(yīng)速度就越慢。

本文主要討論 limit 分頁大偏移量慢的原因及優(yōu)化方案,為了模擬這種情況,下面首先介紹表結(jié)構(gòu)和執(zhí)行的 SQL。

場景模擬

建表語句

user 表的結(jié)構(gòu)比較簡單,id、sex 和 name,為了讓 SQL 的執(zhí)行時間變化更加明顯,這里有9個姓名列。

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ’主鍵’, `sex` tinyint(4) NULL DEFAULT NULL COMMENT ’性別 0-男 1-女’, `name1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name6` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name7` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name8` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, `name9` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ’姓名’, PRIMARY KEY (`id`) USING BTREE, INDEX `sex`(`sex`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 9000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

數(shù)據(jù)填充

這里建立了一個存儲過程來進(jìn)行數(shù)據(jù)的填充,一共9000000條數(shù)據(jù),執(zhí)行完函數(shù)后再執(zhí)行一句SQL,修改性別字段。

ps:這個函數(shù)執(zhí)行的挺久的,我運(yùn)行了617.284秒。

CREATE DEFINER=`root`@`localhost` PROCEDURE `data`()begin declare i int; set i=1; while(i<=9000000)do insert into user values(i,0,i,i,i,i,i,i,i,i,i); set i=i+1; end while;end-- 將id為偶數(shù)的user設(shè)置性別為1-女update user set sex=1 where id%2=0;

SQL與執(zhí)行時間

SQL 執(zhí)行時間 select * from user where sex = 1 limit 100, 10; OK, Time: 0.005000s select * from user where sex = 1 limit 1000, 10; OK, Time: 0.007000s select * from user where sex = 1 limit 10000, 10; OK, Time: 0.016000s select * from user where sex = 1 limit 100000, 10; OK, Time: 0.169000s select * from user where sex = 1 limit 1000000, 10; OK, Time: 5.892000s select * from user where sex = 1 limit 10000000, 10; OK, Time: 33.465000s

可以看到,limit 的偏移量越大,執(zhí)行時間越長。

原因分析

首先來分析一下這句 SQL 執(zhí)行的過程,就拿上面表格中的第一行來舉例。

由于 sex 列是索引列,MySQL會走 sex 這棵索引樹,命中 sex=1 的數(shù)據(jù)。

然后又由于非聚簇索引中存儲的是主鍵 id 的值,且查詢語句要求查詢所有列,所以這里會發(fā)生一個回表的情況,在命中 sex 索引樹中值為1的數(shù)據(jù)后,拿著它葉子節(jié)點(diǎn)上的值也就是主鍵 id 的值去主鍵索引樹上查詢這一行其他列(name、sex)的值,最后返回到結(jié)果集中,這樣第一行數(shù)據(jù)就查詢成功了。

最后這句 SQL 要求limit 100, 10,也就是查詢第101到110個數(shù)據(jù),但是 MySQL 會查詢前110行,然后將前100行拋棄,最后結(jié)果集中就只剩下了第101到110行,執(zhí)行結(jié)束。

小結(jié)一下,在上述的執(zhí)行過程中,造成 limit 大偏移量執(zhí)行時間變久的原因有:

查詢所有列導(dǎo)致回表 limit a, b會查詢前a+b條數(shù)據(jù),然后丟棄前a條數(shù)據(jù)

綜合上述兩個原因,MySQL 花費(fèi)了大量時間在回表上,而其中a次回表的結(jié)果又不會出現(xiàn)在結(jié)果集中,這才導(dǎo)致查詢時間變得越來越長。

優(yōu)化方案

覆蓋索引

既然無效的回表是導(dǎo)致查詢變慢的主要原因,那么優(yōu)化方案就主要從減少回表次數(shù)方面入手,假設(shè)在limit a, b中我們首先得到了a+1到a+b條數(shù)據(jù)的id,然后再進(jìn)行回表獲取其他列數(shù)據(jù),那么就減少了a次回表操作,速度肯定會快上不少。

這里就涉及到覆蓋索引了,所謂的覆蓋索引就是從非主聚簇索引中就能查到的想要數(shù)據(jù),而不需要通過回表從主鍵索引中查詢其他列,能夠顯著提升性能。

基于這樣的思路,優(yōu)化方案就是先查詢得到主鍵id,然后再根據(jù)主鍵id查詢其他列數(shù)據(jù),優(yōu)化后的 SQL 以及執(zhí)行時間如下表。

優(yōu)化后的 SQL 執(zhí)行時間 select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id; OK, Time: 0.000000s select * from user a join (select id from user where sex = 1 limit 1000, 10) b on a.id=b.id; OK, Time: 0.00000s select * from user a join (select id from user where sex = 1 limit 10000, 10) b on a.id=b.id; OK, Time: 0.002000s select * from user a join (select id from user where sex = 1 limit 100000, 10) b on a.id=b.id; OK, Time: 0.015000s select * from user a join (select id from user where sex = 1 limit 1000000, 10) b on a.id=b.id; OK, Time: 0.151000s select * from user a join (select id from user where sex = 1 limit 10000000, 10) b on a.id=b.id; OK, Time: 1.161000s

果然,執(zhí)行效率得到了顯著提升。

條件過濾

當(dāng)然還有一種有缺陷的方法是基于排序做條件過濾。

比如像上面的示例 user 表,我要使用 limit 分頁得到1000001到1000010條數(shù)據(jù),可以這樣寫 SQL:

select * from user where sex = 1 and id > (select id from user where sex = 1 limit 1000000, 1) limit 10;

但是使用這樣的方式優(yōu)化是有條件的:主鍵id必須是有序的。在有序的條件下,也可以使用比如創(chuàng)建時間等其他字段來代替主鍵id,但是前提是這個字段是建立了索引的。

總之,使用條件過濾的方式來優(yōu)化 limit 是有諸多限制的,一般還是推薦使用覆蓋索引的方式來優(yōu)化。

小結(jié)

主要分析了 limit 分頁大偏移量慢的原因,同時也提出了響應(yīng)的優(yōu)化方案,推薦使用覆蓋索引的方式來優(yōu)化 limit 分頁大偏移執(zhí)行時間久的問題。

希望能幫助到大家。

以上就是MySQL limit分頁大偏移量慢的原因及優(yōu)化方案的詳細(xì)內(nèi)容,更多關(guān)于MySQL limit 分頁的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章!

標(biāo)簽: MySQL 數(shù)據(jù)庫
相關(guān)文章:
主站蜘蛛池模板: 九九99靖品| 久精品在线观看 | 求欧美精品网址 | 久草视频免费在线看 | 久久这里只有精品视频99 | 97capcom超频在线 | 亚洲精彩视频在线观看 | 中文字幕 亚洲精品 第1页 | 狠狠五月深爱婷婷网 | 在线高清一级欧美精品 | 日本a级片免费观看 | 99久久国产综合精品五月天 | 男女性高爱潮免费的国产 | 欧美一级毛片激情 | 国产成人麻豆tv在线观看 | 成人黄页网站 | 中文字幕av一区二区三区 | 久久国产精品久久久久久久久久 | 欧美一级特黄特色大片免费 | 9cao视频精品 | 日本韩国一级 | 97婷婷狠狠成人免费视频 | 久久久久欧美精品网站 | 99久久国产免费 - 99久久国产免费 | a毛片在线还看免费网站 | 成人97| 三上悠亚免费一区二区在线 | 日韩永久在线观看免费视频 | 中文字幕一区二区三区亚洲精品 | 日韩视频网 | 国产三级一区二区 | 国产精品怡红院在线观看 | 欧美三级做爰视频 | 手机看成人免费大片 | 久久精品成人免费看 | 亚洲一区二区精品推荐 | 亚洲精品久久久久久久久久久网站 | 亚洲一级片在线播放 | 成人精品一区二区不卡视频 | 久久精品亚洲精品国产欧美 | 一级做a爰片毛片 |