“

終于要對mysql優(yōu)化下手了，本文將對分頁進(jìn)行優(yōu)化說明，希望可以得到一個合適你的方案

”

前言

分頁這個話題已經(jīng)是老生常談了，但是有多少小伙伴一邊是既希望優(yōu)化的自己的系統(tǒng)，另一邊在項目上還是保持自己獨有的個性。

優(yōu)化這件事是需要自己主動行動起來的，自己搞測試數(shù)據(jù)，只有在測試的路上才會發(fā)現(xiàn)更多你未知的事情。

本文咔咔也會針對分頁優(yōu)化這個話題進(jìn)行解讀。

一、表結(jié)構(gòu)

這個數(shù)據(jù)庫結(jié)構(gòu)就是咔咔目前線上項目的表，只不過咔咔將字段名改了而已，還有將時間字段取消了。

數(shù)據(jù)庫結(jié)構(gòu)如下

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CREATE</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">TABLE</span>?<span class="hljs-string" style="color: #98c379; line-height: 26px;">`tp_statistics`</span>?(<br>??<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span>?<span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">int</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>)?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span>?<span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span>?AUTO_INCREMENT,<br>??<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field1`</span>?<span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>)?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span>?<span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span>?<span class="hljs-string" style="color: #98c379; line-height: 26px;">'0.00'</span>,<br>??<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field2`</span>?<span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>)?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span>?<span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span>?<span class="hljs-string" style="color: #98c379; line-height: 26px;">'0.00'</span>,<br>??<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field3`</span>?<span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>)?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span>?<span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span>?<span class="hljs-string" style="color: #98c379; line-height: 26px;">'0.00'</span>,<br>??PRIMARY?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">KEY</span>?(<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span>)<br>)?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">ENGINE</span>=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">InnoDB</span>?AUTO_INCREMENT=<span class="hljs-number" style="color: #d19a66; line-height: 26px;">3499994</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CHARSET</span>=utf8?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COLLATE</span>=utf8mb4_general_ci?ROW_FORMAT=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COMPACT</span>;<br><br>

根據(jù)以上信息可以看到目前表里邊的數(shù)據(jù)有350萬記錄，接下來就針對這350W條記錄進(jìn)行查詢優(yōu)化。

二、初探查詢效率

先來寫一個查詢的SQL語句，先看一下查詢耗費的時間。

根據(jù)下圖可以看到查詢時間基本忽略不計，但是要注意的是limit的偏移量值。

于是我們要一步一步的加大這個偏移量然后進(jìn)行測試，先將偏移量改為10000

可以看到查詢時間還是非常理想的。

為了節(jié)省時間咔咔將這個偏移量的值直接調(diào)整到340W。

這個時候就可以看到非常明顯的變化了，查詢時間猛增到了0.79s。

出現(xiàn)了這樣的情況，那肯定就需要進(jìn)行優(yōu)化了，拿起鍵盤就是干。

三、分析查詢耗時的原因

提到分析SQL語句，必備的知識點就是explain，如果對這個工具不會使用的可以去看看MySQL的基礎(chǔ)部分。

根據(jù)下圖可以看到三條查詢語句都進(jìn)行了表掃描。

都知道只要有關(guān)于分頁就必存在排序，那么加一個排序再來看一下查詢效率。

然后在進(jìn)行對排序的語句進(jìn)行分析查看。

通過這里看到當(dāng)使用了排序時數(shù)據(jù)庫掃描的行數(shù)就是偏移量加上需要查詢的數(shù)量。

此時就可以知道的是，在偏移量非常大的時候，就像上圖案例中的limit ?3400000,12這樣的查詢。

此時MySQL就需要查詢3400012行數(shù)據(jù)，然后在返回最后12條數(shù)據(jù)。

前邊查詢的340W數(shù)據(jù)都將被拋棄，這樣的執(zhí)行結(jié)果可不是我們想要的。

咔咔之前看到相關(guān)文章說是解決這個問題的方案，要么直接限制分頁的數(shù)量，要么就優(yōu)化當(dāng)偏移量非常大的時候的性能。

如果你都把本文看到了這里，那怎么會讓你失望，肯定是優(yōu)化大偏移量的性能問題。

四、優(yōu)化

既然提到了優(yōu)化，無非就那么倆點，加索引，使用其它的方案來代替這個方案。

咔咔提供的這條數(shù)據(jù)表結(jié)構(gòu)信息，完全可以理解為就是圖書館的借閱記錄，字段的什么都不要去關(guān)心就可以了。

對于排序來說，在這種場景下是不會給時間加排序的，而是給主鍵加排序，并且由于添加測試數(shù)據(jù)的原因?qū)r間字段給取消了。

接下來使用覆蓋索引加inner join的方式來進(jìn)行優(yōu)化。

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span>?ss_id,ss_field1,ss_field2,ss_field3?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span>?tp_statistics?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">inner</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">join</span>?(?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span>?ss_id?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span>?tp_statistics?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">order</span>?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">by</span>?ss_id?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">limit</span>?<span class="hljs-number" style="color: #d19a66; line-height: 26px;">3000000</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">10</span>)?b?<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">using</span>?(ss_id);<br>

從上圖可以看到查詢時間從0.8s優(yōu)化到了0.4s，但是這樣的效果還是不盡人意。

于是只能更換一下思路再進(jìn)行優(yōu)化。

既然優(yōu)化最大偏移量這條路有點坎坷，能不能從其它方面進(jìn)行入手。

估計有很多同學(xué)已經(jīng)知道咔咔將要拋出什么話題了。

沒錯，就是使用where > id ?然后使用limit。

先來測試一波結(jié)果，在寫具體實現(xiàn)方案。

根據(jù)上圖可以看到這種方式是十分可行的，分頁在300W條數(shù)據(jù)以后的查詢時間也基本忽略不計。

那么這種方案要怎么實現(xiàn)呢！

五、方案落地

其實這個方案真的很簡單，只需要簡單的轉(zhuǎn)換一下思路即可。

當(dāng)客戶端第一次獲取數(shù)據(jù)的時候就正常傳遞offset、limit倆個參數(shù)。

首次返回的數(shù)據(jù)就使用客戶端傳遞過來的offset、limit進(jìn)行獲取。

當(dāng)?shù)谝淮蔚臄?shù)據(jù)返回成功后。

客戶端第二次拉取數(shù)據(jù)時這個時候參數(shù)就發(fā)生改變了，就不能再是offset、limit了。

此時應(yīng)該傳遞的參數(shù)就是第一次獲取的數(shù)據(jù)最后一條數(shù)據(jù)的id。

此時的參數(shù)就為last_id、limit。

后臺獲取到last_id后就可以在sql語句中使用where條件

咔咔這里給的情況是數(shù)據(jù)在倒敘的情況下，如果正序就是大于last_id即可。

接下來咔咔使用一個案例給大家直接明了的說明。

實戰(zhàn)案例

如下就是將要實戰(zhàn)演示的案例，例如首次使用page、limit獲取到了數(shù)據(jù)。

返回結(jié)果的最后一條數(shù)據(jù)的id就是3499984

此時如果在獲取第二條記錄就不是使用offset、limit了，就是傳遞last_id和limit了。

如下圖

此時就是使用的where條件來進(jìn)行直接過濾數(shù)據(jù)，條件就是id小于上次數(shù)據(jù)的最后一條id即可。

時間對比

假設(shè)現(xiàn)在要獲取最后一條數(shù)據(jù)

沒有優(yōu)化之前

優(yōu)化之后可以明顯的看到查詢時間的變化

六、總結(jié)

關(guān)于limit優(yōu)化簡單幾句話概述一下。

數(shù)據(jù)量大的時候不能使用offset、limit來進(jìn)行分頁，因為offset越大，查詢時間越久。
當(dāng)然不能說所有的分頁都不可以，如果你的數(shù)據(jù)就那么幾千、幾萬條，那就很無所謂，隨便使用。
落地方案就是咔咔上邊的方案，首次使用offset、limit獲取數(shù)據(jù)，第二次獲取數(shù)據(jù)使用where條件到第一次數(shù)據(jù)最后一條id即可。