快捷導(dǎo)航

Advanced Pagination for MySQL(mysql高級(jí)分頁(yè))

更新時(shí)間：2016年08月31日 12:02:20 投稿：mdxy-dxy

看到葉金榮的一篇關(guān)于mysql分頁(yè)的文章,結(jié)合雅虎之前發(fā)的一篇PDF 談?wù)勛约旱目捶?/div>

在葉子的文章里談到了使用inner join 從而減少了對(duì)page的掃描也就是減少了所謂的回表例如：

SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)

通過(guò)直接對(duì)id的操作而不是整張表的掃描通過(guò)id 的join 抓出符合條件id 然后通過(guò)ID 再去做數(shù)據(jù)的抓取。這樣就避免了對(duì)不需要的頁(yè)面的掃描。

不過(guò)這樣也不是最佳的方法還可以通過(guò)對(duì)id 的 range更加縮小范圍例如：

我們要分100條記錄分一頁(yè) 可以寫成

復(fù)制代碼代碼如下:

$page_size=100 select * from t where id > 99 order by id asc limit $page_size ; select * from  t where id >199 order by id asc limit $page_size;

盡量避免limit M,N 這種寫法 mysql在對(duì)M值很大而offset很小的時(shí)候的處理方式很不人性化 ,所以盡量不要使用offset來(lái)取得特定行數(shù)。

在這里有一個(gè)問(wèn)題比如根據(jù)不是唯一索引的column分頁(yè) 那么可能存在一個(gè)問(wèn)題,例如一個(gè)列column1存在11個(gè)key=100的值那么你使用limit N 之后取到的min value還是同一個(gè)值
這種情況如何處理？給個(gè)例子：

比如要每10條記錄分一頁(yè)

復(fù)制代碼代碼如下:

select * from t order by column1 desc limit 10

注意這里取到的min value還是100 (11個(gè)連續(xù)的100) 對(duì)下面的分頁(yè)會(huì)產(chǎn)生影響，如何處理？

雅虎給出的方案非常好取一個(gè)extra的column 例如PK 或者unique index key 例如：

復(fù)制代碼代碼如下:

select * from t  order by column1 desc, id desc   limit 10 -- 第一個(gè)頁(yè)

select * from t  where column1 <=minvalue_col1 and (id < minvalue_id or column1 < minvalue_col1) limit 10  ---第二個(gè)頁(yè)

這樣就確保了唯一性保證了每頁(yè)的數(shù)據(jù)不會(huì)重復(fù) 思想就是通過(guò)add一個(gè)唯一的extra 取得這個(gè)extra的邊界值結(jié)合range column來(lái)進(jìn)行分頁(yè)。

這個(gè)SQL 還能被優(yōu)化成：

復(fù)制代碼代碼如下:

SELECT m2.* FROM t m1, t m2  WHERE m1.id = m2.id  AND m1.column1 <= minvalue_col1

AND (m1.id < minvalue_id OR m1.column1 < minvalue_col1)  ORDER BY m1.column1 DESC, m1.id DESC  LIMIT 10;

核心思想: 通過(guò)extra過(guò)濾配合ID掃描避免大量的回表操作這樣就達(dá)到了要取多少條就掃描多少條 (in page)