MySQL中limit最佳化

「

終於要對MySQL優化下手了，本文將對分頁進行最佳化說明，希望可以得到一個合適你的方案

」

前言

#分頁這個主題已經是老生常談了，但是有多少小夥伴一邊是既希望優化的自己的系統，另一邊在專案上還是保持自己獨有的個性。

有個性

優化這件事是需要自己主動行動起來的，自己搞測試數據，只有在測試的路上才會發現更多你未知的事。

本文咔咔也會針對分頁優化這個主題進行解讀。

一、表格結構

#這個資料庫結構就是喀喀目前線上項目的表，只不過咔咔將字段名改了而已，還有時間字段取消了。

資料庫結構如下

<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><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><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;">&#39;0.00&#39;</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;">&#39;0.00&#39;</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;">&#39;0.00&#39;</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/></code>

表結構

#根據上述資訊可以看到目前表裡邊的資料有350萬記錄，接下來就針對這350W筆記錄進行查詢最佳化。

二、初探查詢效率

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

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

初次查詢結果

於是我們要一步一步的加大這個偏移量然後進行測試，先將偏移量改為10000

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

偏移量10000查詢

為了節省時間咔咔將這個偏移量的值直接調整到340W。

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

偏移量340w查詢

出現了這樣的情況，那肯定就需要進行最佳化了，拿起鍵盤就是乾。

三、分析查詢耗時的原因

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

根據下圖可以看到三條查詢語句都進行了表格掃描。

explain分析語句

都知道只要有關於分頁就必存在排序，那麼加一個排序再來看查詢效率。

排序之後的查詢時間

然後在進行對排序的語句進行分析檢視。

透過這裡看到當使用了排序時資料庫掃描的行數就是偏移量加上需要查詢的數量。

分許排序語句

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

此時MySQL就需要查詢3400012行數據，然後在傳回最後12條數據。

前邊查詢的340W資料都會被拋棄，這樣的執行結果可不是我們想要的。

喀喀爾之前看到相關文章說是解決這個問題的方案，要嘛直接限制分頁的數量，要嘛就優化當偏移量非常大的時候的效能。

如果你都把本文看到了這裡，那怎麼會讓你失望，肯定是優化大偏移量的效能問題。

四、優化

#既然提到了最佳化，無非就那麼兩個點，加索引，使用其它的方案來代替這個方案。

咔咔提供的這條數據表結構信息，完全可以理解為就是圖書館的借閱記錄，字段的什麼都不要去關心就可以了。

對於排序來說，在這種場景下是不會給時間加排序的，而是給主鍵加排序，並且由於添加測試資料的原因將時間字段給取消了。

接下来使用覆盖索引加inner join的方式来进行优化。

<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><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><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/></code>

MySQL中limit最佳化

从上图可以看到查询时间从0.8s优化到了0.4s，但是这样的效果还是不尽人意。

于是只能更换一下思路再进行优化。

MySQL中limit最佳化

既然优化最大偏移量这条路有点坎坷，能不能从其它方面进行入手。

估计有很多同学已经知道咔咔将要抛出什么话题了。

没错，就是使用where > id  然后使用limit。

先来测试一波结果，在写具体实现方案。

MySQL中limit最佳化

根据上图可以看到这种方式是十分可行的，分页在300W条数据以后的查询时间也基本忽略不计。

那么这种方案要怎么实现呢！

五、方案落地

其实这个方案真的很简单，只需要简单的转换一下思路即可。

是時候做出改變了

當客戶端第一次取得資料的時候就正常傳遞offset、limit兩個參數。

首次傳回的資料就使用客戶端傳遞過來的offset、limit進行取得。

當第一次的資料回傳成功後。

客戶端第二次拉取資料時這個時候參數就改變了，就不能再是offset、limit了。

此時應該傳遞的參數就是第一次取得的資料最後一條資料的id。

此時的參數就為last_id、limit。

後台取得到last_id後就可以在sql語句中使用where條件

喀嚓這裡給的情況是資料在倒敘的情況下，如果正序就是大於last_id即可。

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

實戰案例

如下是將要實戰演示的案例，例如首次使用page、limit取得了資料。

傳回結果的最後一條資料的id就是3499984

#第一次取得資料

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

如下圖

此時就是使用的where條件來進行直接過濾數據，條件就是id小於上次數據的最後一條id即可。

取得第二個資料

時間比較

#假設現在要取得最後一個資料

#沒有優化之前

沒有優化之前

優化之後可以明顯的看到查詢時間的變化

優化之後的查詢

#六、總結

#關於limit優化簡單幾句話概述一下。

• 資料量大的時候不能使用offset、limit來分頁，因為offset越大，查詢時間越久。
• 當然不能說所有的分頁都不可以，如果你的資料就那麼幾千、幾萬條，那就很無所謂，隨便使用。
• 落地方案就是咔咔上邊的方案，首次使用offset、limit取得數據，第二次取得數據使用where條件到第一次數據最後一條id即可。

「

堅持學習、堅持寫博、堅持分享是咔咔從業以來一直所秉持的信念。希望在偌大互聯網中咔咔的文章能帶給你一絲絲幫助。我是咔咔，下期見。

」

##################################

以上是MySQL中limit最佳化的詳細內容。更多資訊請關注PHP中文網其他相關文章！