一道面試的問(wèn)題，當(dāng)MySQL表中有數(shù)據(jù)量很大的時(shí)候如何做分頁(yè)。。。。當(dāng)時(shí)只知道在數(shù)據(jù)量很大的時(shí)候可以分表，但不知道不分表時(shí)可以怎么做。。。。唉，誰(shuí)讓代理商就那么幾條數(shù)據(jù)，一個(gè)簡(jiǎn)單的limit，offset就完全hold住了(捂臉)。。。
很多應(yīng)用往往只展示最新或最熱門(mén)的幾條記錄，但為了舊記錄仍然可訪(fǎng)問(wèn)，所以就需要個(gè)分頁(yè)的導(dǎo)航欄。然而，如何通過(guò)MySQL更好的實(shí)現(xiàn)分頁(yè)，始終是比較令人頭疼的問(wèn)題。雖然沒(méi)有拿來(lái)就能用的解決辦法，但了解數(shù)據(jù)庫(kù)的底層或多或少有助于優(yōu)化分頁(yè)查詢(xún)。
我們先從一個(gè)常用但性能很差的查詢(xún)來(lái)看一看。
SELECT *FROM cityORDER BY id DESCLIMIT 0, 15
這個(gè)查詢(xún)耗時(shí)0.00sec。So，這個(gè)查詢(xún)有什么問(wèn)題呢？實(shí)際上，這個(gè)查詢(xún)語(yǔ)句和參數(shù)都沒(méi)有問(wèn)題，因?yàn)樗玫搅讼旅姹淼闹麈I，而且只讀取15條記錄。
CREATE TABLE city ( id int(10) unsigned NOT NULL AUTO_INCREMENT, city varchar(128) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;
真正的問(wèn)題在于offset(分頁(yè)偏移量)很大的時(shí)候，像下面這樣：
SELECT *FROM cityORDER BY id DESCLIMIT 100000, 15;
上面的查詢(xún)?cè)谟?M行記錄時(shí)需要0.22sec，通過(guò)EXPLAIN查看SQL的執(zhí)行計(jì)劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁(yè)偏移量會(huì)增加使用的數(shù)據(jù)，MySQL會(huì)將大量最終不會(huì)使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設(shè)大部分網(wǎng)站的用戶(hù)只訪(fǎng)問(wèn)前幾頁(yè)數(shù)據(jù)，但少量的大的分頁(yè)偏移量的請(qǐng)求也會(huì)對(duì)整個(gè)系統(tǒng)造成危害。Facebook意識(shí)到了這一點(diǎn)，但Facebook并沒(méi)有為了每秒可以處理更多的請(qǐng)求而去優(yōu)化數(shù)據(jù)庫(kù)，而是將重心放在將請(qǐng)求響應(yīng)時(shí)間的方差變小。
對(duì)于分頁(yè)請(qǐng)求，還有一個(gè)信息也很重要，就是總共的記錄數(shù)。我們可以通過(guò)下面的查詢(xún)很容易的獲取總的記錄數(shù)。
SELECT COUNT(*)FROM city;
然而，上面的SQL在采用InnoDB為存儲(chǔ)引擎時(shí)需要耗費(fèi)9.28sec。一個(gè)不正確的優(yōu)化是采用SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS可以在能夠在分頁(yè)查詢(xún)時(shí)事先準(zhǔn)備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢(xún)語(yǔ)句簡(jiǎn)短并不意味著性能的提高。不幸的是，這種分頁(yè)查詢(xún)方式在許多主流框架中都有用到，下面看看這個(gè)語(yǔ)句的查詢(xún)性能。
SELECT SQL_CALC_FOUND_ROWS *FROM cityORDER BY id DESCLIMIT 100000, 15;
這個(gè)語(yǔ)句耗時(shí)20.02sec，是上一個(gè)的兩倍。事實(shí)證明使用SQL_CALC_FOUND_ROWS做分頁(yè)是很糟糕的想法。
下面來(lái)看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。
如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會(huì)將行數(shù)存儲(chǔ)到表的元信息中。但如果引擎是InnoDB情況就會(huì)復(fù)雜一些，因?yàn)镮nnoDB不保存表的具體行數(shù)。
我們可以將行數(shù)緩存起來(lái)，然后可以通過(guò)一個(gè)守護(hù)進(jìn)程定期更新或者用戶(hù)的某些操作導(dǎo)致緩存失效時(shí)，執(zhí)行下面的語(yǔ)句：
SELECT COUNT(*)FROM cityUSE INDEX(PRIMARY);
下面進(jìn)入這篇文章最重要的部分，獲取分頁(yè)要展示的記錄。上面已經(jīng)說(shuō)過(guò)了，大的偏移量會(huì)影響性能，所以我們要重寫(xiě)查詢(xún)語(yǔ)句。為了演示，我們創(chuàng)建一個(gè)新的表“news”，按照時(shí)事性排序(最新發(fā)布的在最前面)，實(shí)現(xiàn)一個(gè)高性能的分頁(yè)。為了簡(jiǎn)單，我們就假設(shè)最新發(fā)布的新聞的Id也是最大的。
CREATE TABLE news( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, title VARCHAR(128) NOT NULL) ENGINE=InnoDB;
一個(gè)比較高效的方式是基于用戶(hù)展示的最后一個(gè)新聞Id。查詢(xún)下一頁(yè)的語(yǔ)句如下，需要傳入當(dāng)前頁(yè)面展示的最后一個(gè)Id。
SELECT *FROM news WHERE id < $last_idORDER BY id DESCLIMIT $perpage
查詢(xún)上一頁(yè)的語(yǔ)句類(lèi)似，只不過(guò)需要傳入當(dāng)前頁(yè)的第一個(gè)Id，并且要逆序。
SELECT *FROM news WHERE id > $last_idORDER BY id ASCLIMIT $perpage
上面的查詢(xún)方式適合實(shí)現(xiàn)簡(jiǎn)易的分頁(yè)，即不顯示具體的頁(yè)數(shù)導(dǎo)航，只顯示“上一頁(yè)”和“下一頁(yè)”，例如博客中頁(yè)腳顯示“上一頁(yè)”，“下一頁(yè)”的按鈕。但如果要實(shí)現(xiàn)真正的頁(yè)面導(dǎo)航還是很難的,下面看看另一種方式。
SELECT idFROM ( SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt FROM news JOIN (SELECT @cnt:= 0)T WHERE id < $last_id ORDER BY id DESC LIMIT $perpage * $buttons)CWHERE cnt = 0;
通過(guò)上面的語(yǔ)句可以為每一個(gè)分頁(yè)的按鈕計(jì)算出一個(gè)offset對(duì)應(yīng)的id。這種方法還有一個(gè)好處。假設(shè)，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會(huì)往后移一位，所以如果用戶(hù)在發(fā)布文章時(shí)換頁(yè)，那么他會(huì)看見(jiàn)一篇文章兩次。如果固定了每個(gè)按鈕的offset Id，這個(gè)問(wèn)題就迎刃而解了。Mark Callaghan發(fā)表過(guò)一篇類(lèi)似的博客，利用了組合索引和兩個(gè)位置變量，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對(duì)應(yīng)的頁(yè)碼存儲(chǔ)到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當(dāng)新增一個(gè)記錄的時(shí)候，需要執(zhí)行下面的查詢(xún)重新生成對(duì)應(yīng)的頁(yè)號(hào)。
SET p:= 0;UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當(dāng)然，也可以新增一個(gè)專(zhuān)用于分頁(yè)的表，可以用個(gè)后臺(tái)程序來(lái)維護(hù)。
UPDATE pagination TJOIN ( SELECT id, CEIL((p:= p + 1) / $perpage) page FROM news ORDER BY id)CON C.id = T.idSET T.page = C.page;
現(xiàn)在想獲取任意一頁(yè)的元素就很簡(jiǎn)單了：
SELECT *FROM news AJOIN pagination B ON A.id=B.IDWHERE page=$offset;
還有另外一種與上種方法比較相似的方法來(lái)做分頁(yè)，這種方式比較試用于數(shù)據(jù)集相對(duì)小，并且沒(méi)有可用的索引的情況下—比如處理搜索結(jié)果時(shí)。在一個(gè)普通的服務(wù)器上執(zhí)行下面的查詢(xún)，當(dāng)有2M條記錄時(shí)，要耗費(fèi)2sec左右。這種方式比較簡(jiǎn)單，創(chuàng)建一個(gè)用來(lái)存儲(chǔ)所有Id的臨時(shí)表即可(這也是最耗費(fèi)性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))SELECT id, FLOOR(RAND() * 0x8000000) randomFROM city;ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT, ORDER BY random;
接下來(lái)就可以向下面一樣執(zhí)行分頁(yè)查詢(xún)了。
SELECT *FROM _tmpWHERE OFFSET >= $offsetORDER BY OFFSETLIMIT $perpage;
簡(jiǎn)單來(lái)說(shuō)，對(duì)于分頁(yè)的優(yōu)化就是。。。避免數(shù)據(jù)量大時(shí)掃描過(guò)多的記錄。
博客比較長(zhǎng)，所以翻譯的有些粗糙。。。，之后會(huì)在好好檢查一遍的。在自己做測(cè)試時(shí)，有些查詢(xún)時(shí)間與作者有點(diǎn)不一致，不過(guò)作者這篇博客是寫(xiě)于2011年的，so~不要在意具體數(shù)據(jù)，領(lǐng)會(huì)精神吧~~
聯(lián)系客服
