很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過MySQL更好的實現(xiàn)分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數(shù)據(jù)庫的底層或多或少有助于優(yōu)化分頁查詢。
創(chuàng)新互聯(lián)網(wǎng)站建設提供從項目策劃、軟件開發(fā)，軟件安全維護、網(wǎng)站優(yōu)化(SEO)、網(wǎng)站分析、效果評估等整套的建站服務，主營業(yè)務為成都網(wǎng)站設計、網(wǎng)站制作，成都App制作以傳統(tǒng)方式定制建設網(wǎng)站，并提供域名空間備案等一條龍服務，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務。創(chuàng)新互聯(lián)深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！
我們先從一個常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實際上，這個查詢語句和參數(shù)都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的問題在于offset(分頁偏移量)很大的時候，像下面這樣：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執(zhí)行計劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會增加使用的數(shù)據(jù)，MySQL會將大量最終不會使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設大部分網(wǎng)站的用戶只訪問前幾頁數(shù)據(jù)，但少量的大的分頁偏移量的請求也會對整個系統(tǒng)造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請求響應時間的方差變小。
對于分頁請求，還有一個信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句 select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢語句簡短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。
高效的計算行數(shù)
如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會將行數(shù)存儲到表的元信息中。但如果引擎是InnoDB情況就會復雜一些，因為InnoDB不保存表的具體行數(shù)。
我們可以將行數(shù)緩存起來，然后可以通過一個守護進程定期更新或者用戶的某些操作導致緩存失效時，執(zhí)行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經(jīng)說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創(chuàng)建一個新的表“news”，按照時事性排序(最新發(fā)布的在最前面)，實現(xiàn)一個高性能的分頁。為了簡單，我們就假設最新發(fā)布的新聞的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一個比較高效的方式是基于用戶展示的最后一個新聞Id。查詢下一頁的語句如下，需要傳入當前頁面展示的最后一個Id。
SELECT *
FROM news WHERE id $last_id
ORDER BY id DESC
LIMIT $perpage
查詢上一頁的語句類似，只不過需要傳入當前頁的第一個Id，并且要逆序。
SELECT *
FROM news WHERE id $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查詢方式適合實現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實現(xiàn)真正的頁面導航還是很難的,下面看看另一種方式。
SELECT id
FROM (
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
)C
WHERE cnt = 0;
通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應的id。這種方法還有一個好處。假設，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會往后移一位，所以如果用戶在發(fā)布文章時換頁，那么他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發(fā)表過一篇類似的博客，利用了組合索引和兩個位置變量，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對應的頁碼存儲到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當新增一個記錄的時候，需要執(zhí)行下面的查詢重新生成對應的頁號。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
現(xiàn)在想獲取任意一頁的元素就很簡單了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數(shù)據(jù)集相對小，并且沒有可用的索引的情況下—比如處理搜索結(jié)果時。在一個普通的服務器上執(zhí)行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創(chuàng)建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下來就可以向下面一樣執(zhí)行分頁查詢了。
SELECT *
FROM _tmp
WHERE OFFSET = $offset
ORDER BY OFFSET
LIMIT $perpage;
簡單來說，對于分頁的優(yōu)化就是。。。避免數(shù)據(jù)量大時掃描過多的記錄。
offset+limit方式的分頁查詢，當數(shù)據(jù)表超過100w條記錄，性能會很差。
主要原因是offset limit的分頁方式是從頭開始查詢，然后舍棄前offset個記錄，所以offset偏移量越大，查詢速度越慢。
比如: 讀第10000到10019行元素(pk是主鍵/唯一鍵).
使用order by id可以在查詢時使用主鍵索引。
但是這種方式在id為uuid的時候就會出現(xiàn)問題?？梢允褂脀here in的方式解決：
帶條件的查詢：
如果在分頁查詢中添加了where條件例如 type = 'a’這樣的條件，sql變成 ：
這種情況因為type沒有使用索引也會導致查詢速度變慢。但是只添加type為索引查詢速度還是很慢,是因為查詢的數(shù)據(jù)量太多了。這個時候考慮添加組合索引，組合索引的順序要where條件字段在前，id在后，如 (type,id)，因為組合索引查詢時用到了type索引，而type跟id是組合索引的關(guān)系，如果只select id ，那么直接就可以按組合索引返回id，而不需要再進行一次查詢?nèi)シ祷豬d
使用uuid作為主鍵不僅會帶來性能上的問題，在查詢時也會遇到問題。
因為在使用select id from table limit 10000,10 查詢id數(shù)據(jù)時，默認是對id進行排序，返回的是排序后的id結(jié)果，如果我們想按插入順序查詢結(jié)果，這樣查詢出來的結(jié)果就與我們的需求不相符。
聚集索引跟非聚集索引：聚集索引類似與新華字典的拼音，根據(jù)拼音搜索到的信息都是連續(xù)的，可以很快獲取到它前后的信息。非聚集索引類似于部首查詢，信息存放的位置可能不在一個區(qū)域。對經(jīng)常使用范圍查詢的字段考慮使用聚集索引。
InnoDB中索引分為聚簇索引（主鍵索引）和非聚簇索引（非主鍵索引），聚簇索引的葉子節(jié)點中保存的是整行記錄，而非聚簇索引的葉子節(jié)點中保存的是該行記錄的主鍵的值。
如果您的表上定義有主鍵，該主鍵索引是聚集索引。
如果你不定義為您的表的主鍵時，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚集索引。
如果沒有這樣的列，InnoDB就自己產(chǎn)生一個這樣的ID值，
優(yōu)先選index key_len小的索引進行count(*),盡量不使用聚簇索引
在沒有where條件的情況下，count(*)和count(常量)，如果有非聚簇索引，mysql會自動選擇非聚簇索引，因為非聚簇索引所占的空間小，如果沒有非聚簇索引會使用聚集索引。count(primary key)主鍵id為聚集索引，使用聚集索引。有where條件的情況下，是否使用索引會根據(jù)where條件判斷。
直接用limit start, count分頁語句， 也是我程序中用的方法：
select * from product limit start, count
當起始頁較小時，查詢沒有性能問題，我們分別看下從10， 100， 1000， 10000開始分頁的執(zhí)行時間（每頁取20條）， 如下：
select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒
我們已經(jīng)看出隨著起始記錄的增加，時間也隨著增大， 這說明分頁語句limit跟起始頁碼是有很大關(guān)系的，那么我們把起始記錄改為40w看下（也就是記錄的一般左右） select * from product limit 400000, 20 3.229秒
再看我們?nèi)∽詈笠豁撚涗浀臅r間
select * from product limit 866613, 20 37.44秒
難怪搜索引擎抓取我們頁面的時候經(jīng)常會報超時，像這種分頁最大的頁碼頁顯然這種時
間是無法忍受的。
從中我們也能總結(jié)出兩件事情：
1）limit語句的查詢時間與起始記錄的位置成正比
2）mysql的limit語句是很方便，但是對記錄很多的表并不適合直接使用。
1、首先我們建立一個表表的數(shù)據(jù)，這個表里有25條數(shù)據(jù)，id從1到25。（下圖是部分截圖）
2、要分頁數(shù)據(jù)，首先我們假設一頁有10條數(shù)據(jù)，我們可以用mysql的limit關(guān)鍵字來限定返回多少條數(shù)據(jù)。并且用orderby來排序數(shù)據(jù)，這里用id來排序。所以第一頁的sql可以如圖這樣寫。
3、執(zhí)行后得到的數(shù)據(jù)如圖，就是id從1到10的前10條數(shù)據(jù)，因為我們是按id升序來排序的。
4、上面第一頁的sql是簡化的寫法，完整的寫法如圖，得到的結(jié)果和上圖的一模一樣。代碼里limit0,10的意思是從第一條數(shù)據(jù)開始，取10條數(shù)據(jù)。（注意的是第一條數(shù)據(jù)是從0開始的）
5、那么第二頁的數(shù)據(jù)，關(guān)鍵是要知道是從哪一條數(shù)據(jù)開始，可以用這個公式得到：(頁碼-1)?*每頁顯示多少條，即（2-1)*10=10,所以sql語句如圖,limit10,10。
6、執(zhí)行后，結(jié)果正確，得到id從11到20的10條數(shù)據(jù)。
7、同理第三頁數(shù)據(jù)的sql如圖，br/就是limit20,10。
8、查詢的結(jié)果如圖，因為這頁只剩下5條數(shù)據(jù)了，所以只顯示5條數(shù)據(jù)。如果你有更多頁的數(shù)據(jù)，后面的數(shù)據(jù)只需要按上面的公式，得到從哪行開始，就可以寫對應的sql語句了。
重慶分公司
重慶分公司
