使用id限定優(yōu)化mysql分頁查詢limit偏移量大問題

2022-11-16 08:56 作者:密云榛子IT教育 0人讀過 | 我要投稿

在工作中可能偶爾會遇到，當(dāng)使用limit實現(xiàn)分頁查詢時，當(dāng)limit的偏移量越大時，sql語句的耗時也越大。

如圖：

偏移量為0時，sql語句耗時在35毫秒。

順便說下偏移量與頁碼、頁大小的關(guān)系：

比如頁的大小是每頁100行記錄，那么:

第一頁的偏移量就是? (1 - 1) x 100 = 0

第二頁的偏移量就是??(2 - 1) x 100 = 100

以此類推

當(dāng)加大偏移量時：

當(dāng)偏移量加到100000時，耗時也增大到228毫秒。

為什么偏移量會對性能有這么大影響呢？

上圖是mysql的系統(tǒng)結(jié)構(gòu)圖，客戶端程序發(fā)送sql語句查詢請求給服務(wù)層，服務(wù)層會解析、優(yōu)化sql語句，之后交給存儲引擎，也就是說，存儲引擎是真正完成查詢的(增加、刪除、修改也是由存儲引擎負(fù)責(zé)的)。

當(dāng)存儲引擎查詢數(shù)據(jù)庫文件后返回的不是一頁的數(shù)據(jù)(100行)，而是從第1行到第 (100000 + 100)行的數(shù)據(jù)一起返回給服務(wù)層。??服務(wù)層收到數(shù)據(jù)后會拋棄前面的100000行，只留下最后的100行返回給客戶端。

數(shù)據(jù)庫表中行數(shù)據(jù)、索引都是以文件的形式存儲到磁盤(硬盤)上的，而硬盤的速度相對來說要慢很多，存儲引擎運(yùn)行sql語句時，需要訪問硬盤查詢文件，然后返回數(shù)據(jù)給服務(wù)層。當(dāng)返回的數(shù)據(jù)越多時，訪問磁盤的次數(shù)就越多，就會越耗時。這就是為什么偏移量越大、返回的數(shù)據(jù)越多，越耗時的原因。

所以說，如果想優(yōu)化上面的sql時，必須要減少返回的數(shù)據(jù)。

當(dāng)表的主鍵是有序的，或者是自增的，可以使用id限定查詢，查詢過程是：

當(dāng)已經(jīng)查詢了某頁的數(shù)據(jù)后，記錄下該頁最后一行記錄的主鍵id值(本例中是dbid為主鍵)，查詢下一頁時就可以使用如下sql：

比如：