前言

在各類系統(tǒng)的表格類信息展示的功能中，經(jīng)常會(huì)用到“翻頁”這個(gè)操作，在頁面上每次只展示有限的數(shù)據(jù)，需要看其他數(shù)據(jù)的時(shí)候則像翻書一樣翻到后面的“頁”。在 MySQL 支持的 SQL 語法中對(duì)此有特殊的支持，開發(fā)人員在實(shí)現(xiàn)這類功能的時(shí)候很方便：

這兩類語法代表的意思是一樣的：返回從第 M 開始（不包括這一行）之后的 N 行數(shù)據(jù)。雖然使用起來很方便，但是這類語句存在查詢性能上的陷阱，需要特別注意一下。 

原理簡介

在解釋原理之前，先看一下實(shí)際的效果，看看這個(gè)“性能的陷阱”是什么。

兩個(gè)語句的內(nèi)容都非常簡單，差別只在 limit 的部分，第一個(gè)語句跳過的行數(shù)很少，第二個(gè)語句跳過的行數(shù)很多，結(jié)果是兩個(gè)語句的執(zhí)行時(shí)間差了至少 200 倍。PS：limit 配合 order by 使用是一個(gè)好習(xí)慣，確保結(jié)果數(shù)據(jù)是穩(wěn)定的。

可以看到跳過的行數(shù)大幅度增長時(shí)，SQL 語句的執(zhí)行時(shí)間也會(huì)快速增長，原因其實(shí)比較簡單：在處理 limit M,N 的時(shí)候，MySQL 會(huì)先拿到 M+N 行結(jié)果數(shù)據(jù)，然后再丟棄 M 行數(shù)據(jù)，展示之后剩下的 N 行數(shù)據(jù)。所以上圖的第二個(gè)語句實(shí)際上掃描了 800 多萬行數(shù)據(jù)，然后丟棄了 800 萬行數(shù)據(jù)，只展示之后的 1 行結(jié)果。

利用[慢查詢分析三部曲][Link 1]的方法嘗試排查一下，explain 和 optimizer_trace 都看不出來差別，但是 profile 里面能看出來兩者的差距：

雖然都只輸出一行結(jié)果，但是在 Sending data 階段花費(fèi)的時(shí)間差別很大，其實(shí)就是花在掃描 800 萬行數(shù)據(jù)上去了。 

優(yōu)化策略

針對(duì)這個(gè)問題，其實(shí)有一個(gè)比較通用的優(yōu)化思路：利用 join，先根據(jù)主鍵搜索到需要的數(shù)據(jù)，再通過主鍵關(guān)聯(lián)到原來的表輸出結(jié)果。SQL 可以改寫一下：

SQL 改寫的效果

可以看到查詢時(shí)間降到了 1.5s 左右，提升了約 37%，看起來還可以，那么還有其他的辦法么？

顯然還是有的，不過這會(huì)要求表有自增主鍵。在分頁查詢的時(shí)候，記錄上一次查詢結(jié)果中的主鍵，然后在 where 條件中添加主鍵的范圍約束。以上面的查詢?yōu)槔?，上次分頁查詢時(shí)的主鍵是 8000001，那么下次分頁的時(shí)候，where 條件中添加一個(gè)主鍵約束：id>8000001，再來看看查詢效果：

添加條件之后的效果

可以發(fā)現(xiàn)利用主鍵來篩選掉上一次分頁前的所有數(shù)據(jù)后再用 limit，查詢基本是馬上返回結(jié)果的。不過要特別注意，這種方法是根據(jù)主鍵的順序先做了一次篩選，不一定會(huì)適用于所有的業(yè)務(wù)場景，理論上 UUID 類的主鍵也可以用，但是改造 SQL 前務(wù)必確保查詢結(jié)果是符合預(yù)期的。