MySQL百萬數(shù)據(jù)，你如何用分頁來查詢數(shù)據(jù)

在開發(fā)過程中我們經(jīng)常會(huì)使用分頁，核心技術(shù)是使用limit進(jìn)行數(shù)據(jù)的讀取，在使用limit進(jìn)行分頁的測(cè)試過程中，得到以下數(shù)據(jù)：

select?*?from?news?order?by?id?desc?limit?0,10
耗時(shí)0.003秒
select?*?from?news?order?by?id?desc?limit?10000,10
耗時(shí)0.058秒
select?*?from?news?order?by?id?desc?limit?100000,10?
耗時(shí)0.575秒
select?*?from?news?order?by?id?desc?limit?1000000,10
耗時(shí)7.28秒

我們驚訝的發(fā)現(xiàn)mysql在數(shù)據(jù)量大的情況下分頁起點(diǎn)越大查詢速度越慢，100萬條起的查詢速度已經(jīng)需要7秒鐘。這是一個(gè)我們無法接受的數(shù)值！

改進(jìn)方案 1

select?*?from?news?
where?id?>??(select?id?from?news?order?by?id?desc??limit?1000000,?1)
order?by?id?desc?
limit?0,10

查詢時(shí)間 0.365秒，提升效率是非常明顯的！！原理是什么呢？？？

我們使用條件對(duì)id進(jìn)行了篩選，在子查詢 (select id from news order by id desc limit 1000000, 1) 中我們只查詢了id這一個(gè)字段比起select * 或 select 多個(gè)字段 節(jié)省了大量的查詢開銷！

改進(jìn)方案2

適合id連續(xù)的系統(tǒng)，速度極快！

select?*?from?news?
where?id??between?1000000?and?1000010?
order?by?id?desc

不適合帶有條件的、id不連續(xù)的查詢。速度非常快！

百萬數(shù)據(jù)分頁的注意事項(xiàng)

接上一節(jié)，我們加上查詢條件：

select?id?from?news?
where?cate?=?1
order?by?id?desc?
limit?500000?,10?

查詢時(shí)間 20 秒

好恐怖的速度！！利用上面方案進(jìn)行優(yōu)化：

select?*?from?news
where?cate?=?1?and?id?>?(select?id?from?news?where?cate?=?1?order?by?id?desc?limit?500000,1?)?
order?by?id?desc?
limit?0,10?

查詢時(shí)間 15 秒

優(yōu)化效果不明顯，條件帶來的影響還是很大！在這樣的情況下無論我們?cè)趺慈?yōu)化sql語句就無法解決運(yùn)行效率問題。

那么換個(gè)思路：建立一個(gè)索引表，只記錄文章的id、分類信息，我們將文章內(nèi)容這個(gè)大字段分割出去。

表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ]

-------------------------------------------------

id?int?11?主鍵自動(dòng)增加

cate?int?11?索引

在寫入數(shù)據(jù)時(shí)將2張表同步，查詢是則可以使用news2 來進(jìn)行條件查詢：

select?*?from?news
where?cate?=?1?and?id?>?(select?id?from?news2?where?cate?=?1?order?by?id?desc?limit?500000,1?)?
order?by?id?desc?
limit?0,10

注意條件 id > 后面使用了news2 這張表！

運(yùn)行時(shí)間 1.23秒，我們可以看到運(yùn)行時(shí)間縮減了近20倍！！數(shù)據(jù)在10萬左右是查詢時(shí)間可以保持在0.5秒左右，是一個(gè)逐步接近我們能夠容忍的值！

但是1秒對(duì)于服務(wù)器來說依然是一個(gè)不能接受的值！！還有什么可以優(yōu)化的辦法嗎？？

我們嘗試了一個(gè)偉大的變化：

將 news2 的存儲(chǔ)引擎改變?yōu)閕nnodb，執(zhí)行結(jié)果是驚人的！

select?*?from?news
where?cate?=?1?and?id?>?(select?id?from?news2?where?cate?=?1?order?by?id?desc?limit?500000,1?)?
order?by?id?desc?
limit?0,10

只需要 0.2秒，非常棒的速度。

到了這一步，我們的分頁優(yōu)化完畢，顯然是有很大的效果的。你自己可以測(cè)試一下！