mysql千萬(wàn)數(shù)據(jù)表Limit分頁(yè)查詢?nèi)绾蝺?yōu)化

前言

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

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

原理簡(jiǎn)介

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

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

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

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

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

優(yōu)化策略

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

SQL 改寫(xiě)的效果

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

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

添加條件之后的效果

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