問(wèn)題描述

用傳統(tǒng)的LIMIT m, n做分頁(yè)查詢(xún)需要這么幾步：

用SELECT COUNT(*) FROM table WHERE condition ORDER BY ...查到總數(shù)并算出有多少頁(yè)；

用SELECT columns FROM table WHERE condition ORDER BY ... LIMIT 0, 100顯示第一頁(yè)(假設(shè)每頁(yè)有100行)，如果用SQL_CALC_FOUND_ROWS這個(gè)參數(shù)的話，可以跟前一條合并成一條SQL；

點(diǎn)“下一頁(yè)”時(shí)，用SELECT columns FROM table WHERE condition ORDER BY ... LIMIT 100, 100查；

...

這樣做往往花費(fèi)很大，因?yàn)閃HERE condition有可能是全表掃描。如果MySQL沒(méi)開(kāi)緩存的話，每翻一頁(yè)可能非常慢。

因此我就用一種新的辦法：

用SELECT id FROM table WHERE condition ORDER BY ...得到所有相符的ID，如果數(shù)據(jù)量太大(比如表中有1,000,000行)，我們就限制一下行數(shù)(比如限制最多查10,000，就用LIMIT 10000)，于是這些ID就通過(guò)動(dòng)態(tài)頁(yè)面或Ajax(以JS代碼或JSON的形式)被傳到了前端；

前端JS選取前100個(gè)ID作為第一頁(yè)，發(fā)送一個(gè)帶這100個(gè)ID的查詢(xún)請(qǐng)求，后端其實(shí)處理SELECT columns FROM table WHERE id IN (id_0, id_1, ..., id_99)這么一個(gè)查詢(xún)；

點(diǎn)“下一頁(yè)”時(shí)，查詢(xún)是SELECT columns FROM table WHERE id IN (id_100, id_101, ..., id_199)；

...

這種方法只需要做一次條件查詢(xún)(慢)，列表數(shù)據(jù)其實(shí)都是主鍵查詢(xún)(快)。

我在一個(gè)業(yè)余項(xiàng)目中用了這個(gè)辦法，詳見(jiàn)：(http://) www.chess-wizard.com/base/ (第一頁(yè)數(shù)據(jù)被寫(xiě)在JSP頁(yè)面里，有利于SEO).

我要求團(tuán)隊(duì)成員都用這種方式來(lái)處理分頁(yè)，他們卻并不認(rèn)同 :-(

難道LIMIT m, n是分頁(yè)查詢(xún)的標(biāo)準(zhǔn)做法或唯一途徑嗎？

問(wèn)題解答

一種id>$id limit $limit;傳遞參數(shù)$offset,$limit=100;

第一頁(yè):$offset = 0

select id ,name from table order by id limit $limit;

第二頁(yè):$offset為第一頁(yè)返回的id

select id ,name from table where id>$offset order by id limit $limit;

分頁(yè)比較慢的情況，主要是第一步慢（取出符合條件記錄、排序、選擇當(dāng)前頁(yè)的行），你說(shuō)的方法在這一步并沒(méi)有改進(jìn)。

另外一種情況，第一步取符合條件的記錄是可以使用少量的表，但取明細(xì)行數(shù)據(jù)需要關(guān)聯(lián)其他多張表，這時(shí)候如果數(shù)據(jù)庫(kù)選擇的執(zhí)行計(jì)劃不對(duì)，也會(huì)很慢。這個(gè)時(shí)候可以采用@abul的方法，先從小表取出符合條件的記錄id，然后再關(guān)聯(lián)其他表。

注意這些處理都是在數(shù)據(jù)庫(kù)內(nèi)部完成，不需要向前端傳遞數(shù)據(jù)，主要有幾個(gè)原因：1、如果符合條件的結(jié)果集數(shù)據(jù)量很大，數(shù)據(jù)庫(kù)全部查詢(xún)出ID和跨網(wǎng)絡(luò)傳輸代價(jià)很大，你說(shuō)的最多限制10000條不一定能滿(mǎn)足所有的場(chǎng)景。2、很多時(shí)候用戶(hù)只會(huì)看前幾頁(yè)的內(nèi)容，一次取出所有ID的消耗其實(shí)是浪費(fèi)了。3、如果在第一次和第二次查詢(xún)中間，數(shù)據(jù)發(fā)生了變化，用戶(hù)得到的結(jié)果集是不準(zhǔn)確的，需要根據(jù)對(duì)查詢(xún)結(jié)果的精確性要求判斷是否可行。另外，如果查詢(xún)出的數(shù)據(jù)公用性較高，可以考慮放到redis類(lèi)似的緩存中，降低系統(tǒng)的整體負(fù)載，只放在前端的話感覺(jué)重用率太低了。

如果非要說(shuō)一個(gè)絕對(duì)正確的原則，其實(shí)是正確的廢話：根據(jù)業(yè)務(wù)場(chǎng)景需求和各方案的優(yōu)缺點(diǎn)做判斷和取舍。

1.mysql 為什么不開(kāi)緩存呢

2.前端使用同步還是異步獲取頁(yè)面內(nèi)容？如果是同步的，那么你的方式無(wú)法滿(mǎn)足前端的需求;如果是異步的，你的查詢(xún)方式 初次查詢(xún)獲取全部符合條件的id(假設(shè)先獲取了10000條)，這10000個(gè)id如何讓前端獲取？假如都放頁(yè)面上，前端js可以直接使用這個(gè)數(shù)組來(lái)發(fā)起異步請(qǐng)求，但是如果跳轉(zhuǎn)頁(yè)碼超出了這個(gè)范圍怎么辦，前端肯定還需要請(qǐng)求頁(yè)碼id，這時(shí)候你的where查詢(xún)還是要用的

所以這個(gè)方案目前看效果不是那么明顯。我也不知道我分析的對(duì)不對(duì)，你做個(gè)參考吧。

既然你的思路已經(jīng)是前端做ID的緩存了，為什么不直接把ID以外的字段也一并在前端緩存了呢

比如你要取前10頁(yè)數(shù)據(jù)，每頁(yè)50條，那SQL語(yǔ)句就用LIMIT 500取前500條，在這10頁(yè)之內(nèi)翻頁(yè)就不需要任何請(qǐng)求；直到翻下一頁(yè)的時(shí)候，再用LIMIT 500,500的SQL語(yǔ)句去取后500條

是否可以嘗試兩者結(jié)合？limit的時(shí)候只是取出id，具體字段再關(guān)聯(lián)出來(lái)。

SELECT columns FROM table t1 inner join (SELECT id FROM table WHERE condition ORDER BY ... LIMIT m, n)t2 on t1.id=t2.id回答6：

這個(gè)真的要針對(duì)項(xiàng)目來(lái)看的：

1、如果數(shù)據(jù)量大且只有一個(gè)唯一索引ID，那用你后面提出的方法肯定是最快的（當(dāng)然條數(shù)不能太多）

2、如果有其它字段做了索引且百分百該字段必須作為條件，當(dāng)然是用普通的 ORDER BY ... LIMIT m, n 分頁(yè)查詢(xún)就很快

3、關(guān)于是否使用緩存，也是看應(yīng)用場(chǎng)景，如果你這個(gè)查詢(xún)不牽扯太多where條件，且數(shù)據(jù)不是實(shí)時(shí)更新，這是可以用的