一 背景

大部分開發(fā)和DBA同行都對(duì)分頁查詢非常非常了解，看帖子翻頁需要分頁查詢，搜索商品也需要分頁查詢。那么問題來了,遇到上千萬或者上億的數(shù)據(jù)量怎么快速的拉取全量，比如大商家拉取每月千萬級(jí)別的訂單數(shù)量到自己獨(dú)立的ISV做財(cái)務(wù)統(tǒng)計(jì);或者擁有百萬千萬粉絲的公眾大號(hào),給全部粉絲推送消息的場(chǎng)景。本文講講個(gè)人的優(yōu)化分頁查詢的經(jīng)驗(yàn)，拋磚引玉。

二 分析

在講如何優(yōu)化之前我們先來看看一個(gè)比較常見錯(cuò)誤的寫法

該SQL是一個(gè)非常典型的排序+分頁查詢:

order by col limit N,M

MySQL 執(zhí)行此類SQL時(shí)需要先掃描到N行，然后再去取M行。對(duì)于此類操作，獲取前面少數(shù)幾行數(shù)據(jù)會(huì)很快，但是隨著掃描的記錄數(shù)越多，SQL的性能就會(huì)越差，因?yàn)镹的值越大，MySQL需要掃描越多的數(shù)據(jù)來定位到具體的N行，這樣耗費(fèi)大量的 IO 成本和時(shí)間成本。一圖勝千言，我們使用簡單的圖來解釋為什么 上面的sql 的寫法掃描數(shù)據(jù)會(huì)慢。

t 表是一個(gè)索引組織表，key idxkidtype(kid,type) 。

符合kid=3 and type=1 的記錄有很多行,我們?nèi)〉?9，10行。

select * from t where kid =3 and type=1 order by id desc 8,2;

MySQL 是如何執(zhí)行上面的sql 的？對(duì)于Innodb表，系統(tǒng)是根據(jù) idxkidtype 二級(jí)索引里面包含的主鍵去查找對(duì)應(yīng)的行。對(duì)于百萬千萬級(jí)別的記錄而言，索引大小可能和數(shù)據(jù)大小相差無幾，cache在內(nèi)存中的索引數(shù)量有限，而且二級(jí)索引和數(shù)據(jù)葉子節(jié)點(diǎn)不在同一個(gè)物理塊兒上存儲(chǔ)，二級(jí)索引與主鍵的相對(duì)無序映射關(guān)系，也會(huì)帶來大量的隨機(jī)IO請(qǐng)求，N值越大越需要遍歷大量索引頁和數(shù)據(jù)葉，需要耗費(fèi)的時(shí)間就越久。

鑒于上面的大分頁查詢耗費(fèi)時(shí)間長的原因，我們思考一個(gè)問題，是否需要完全遍歷“無效的數(shù)據(jù)”？如果我們需要limit 8，2；我們跳過前面8行無關(guān)的數(shù)據(jù)頁遍歷，可以直接通過索引定位到第9，第10行，這樣操作是不是更快了？依然是一圖勝千言，通過這其實(shí)也是 延遲關(guān)聯(lián)的 核心思思:通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù),而不是通過二級(jí)索引獲取主鍵再通過主鍵去遍歷數(shù)據(jù)頁。

通過上面的原理分析，我們知道通過常規(guī)方式進(jìn)行大分頁查詢慢的原因，也知道了提高大分頁查詢的具體方法 ，下面我們討論一下在線上業(yè)務(wù)系統(tǒng)中常用的解決方法。

三 實(shí)踐出真知

針對(duì)limit 優(yōu)化有很多種方式: 1 前端加緩存、搜索，減少落到庫的查詢操作。比如海量商品可以放到搜索里面，使用瀑布流的方式展現(xiàn)數(shù)據(jù)，很多電商網(wǎng)站采用了這種方式。 2 優(yōu)化SQL 訪問數(shù)據(jù)的方式，直接快速定位到要訪問的數(shù)據(jù)行。 3 使用書簽方式 ，記錄上次查詢最新/大的id值，向后追溯 M行記錄。 對(duì)于第二種方式 我們推薦使用'延遲關(guān)聯(lián)'的方法來優(yōu)化排序操作，何謂'延遲關(guān)聯(lián)' ：通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。

3.1 延遲關(guān)聯(lián)

優(yōu)化前

root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
| id  | select_type | table           | type  | possible_keys | key            | key_len | ref  | rows   | Extra   |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
| 1  | SIMPLE      | relation    | range | ind_endtime   | ind_endtime | 9       | NULL | 349622 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
1 row in set (0.00 sec)

其執(zhí)行時(shí)間：

優(yōu)化后:

執(zhí)行時(shí)間：

優(yōu)化后 執(zhí)行時(shí)間 為原來的1/3 。

3.2 使用書簽的方式

首先要獲取復(fù)合條件的記錄的最大 id和最小id(默認(rèn)id是主鍵)

select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;

其次 根據(jù)id 大于最小值或者小于最大值 進(jìn)行遍歷。

select xx,xx from t where kid=2333 and type=1 and id >=min_id order by id asc limit 100;

select xx,xx from t where kid=2333 and type=1 and id <=max_id order by id desc limit 100;

案例

當(dāng)遇到延遲關(guān)聯(lián)也不能滿足查詢速度的要求時(shí)

SELECT a.id as id, clientid, adminid, kdtid, type, token, createdtime, updatetime, isvalid, version FROM t1 a, (SELECT id FROM t1 WHERE 1 and client_id = 'xxx' and is_valid = '1' order by kdt_id asc limit 267100,100 ) b WHERE a.id = b.id;

使用延遲關(guān)聯(lián)查詢數(shù)據(jù)510ms ，使用基于書簽?zāi)Ｊ降慕鉀Q方法減少到10ms以內(nèi) 絕對(duì)是一個(gè)質(zhì)的飛躍。

SELECT * FROM t1 where clientid='xxxxx' and isvalid=1 and id<47399727 order by id desc LIMIT 100;

四 小結(jié)

從我們的優(yōu)化經(jīng)驗(yàn)和案例上來講，根據(jù)主鍵定位數(shù)據(jù)的方式直接定位到主鍵起始位點(diǎn)，然后過濾所需要的數(shù)據(jù) 相對(duì)比延遲關(guān)聯(lián)的速度更快些，查找數(shù)據(jù)的時(shí)候少了二級(jí)索引掃描。但是 優(yōu)化方法沒有銀彈，沒有一勞永逸的方法。比如下面的例子

order by id desc 和 order by asc 的結(jié)果相差70ms ，生產(chǎn)上的案例有l(wèi)imit 100 相差1.3s ，這是為什么呢？留給大家去思考吧。