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

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

場景模擬

建表語句

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

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ù)填充

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

ps:這個(gè)函數(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í)行時(shí)間

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

原因分析

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

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

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

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

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

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

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

優(yōu)化方案

覆蓋索引

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

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

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

果然，執(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)建時(shí)間等其他字段來代替主鍵id，但是前提是這個(gè)字段是建立了索引的。

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

小結(jié)

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

希望能幫助到大家。

以上就是MySQL limit分頁大偏移量慢的原因及優(yōu)化方案的詳細(xì)內(nèi)容，更多關(guān)于MySQL limit 分頁的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論