優(yōu)化慢SQL技巧

閱讀大概3分鐘

• 前言

• 1. OFFSET和LIMIT有什么問題

• 2. 有什么解決方案

• 再優(yōu)化

前言

不再需要擔(dān)心數(shù)據(jù)庫性能優(yōu)化的日子已經(jīng)一去不復(fù)返了。

隨著時代的發(fā)展，每個新企業(yè)家都希望建立下一個Facebook，并結(jié)合收集每個可能的數(shù)據(jù)點以提供更好的機(jī)器學(xué)習(xí)預(yù)測的心態(tài)，作為開發(fā)人員，我們需要比以往更好地準(zhǔn)備我們的API，以提供可靠，高效的端點，應(yīng)該能夠毫不費力地瀏覽大量數(shù)據(jù)。

如果您已經(jīng)進(jìn)行了一段時間的后端或數(shù)據(jù)庫體系結(jié)構(gòu)，則可能已經(jīng)完成了分頁查詢，如下所示：

但是，如果你確實建立了這樣的分頁，很遺憾地說，這樣做是錯的。

你不以為然？沒關(guān)系。Slack、Shopify 和 Mixmax 這些公司都在用我們今天將要討論的方式進(jìn)行分頁。

我想你很難找出一個不使用 OFFSET 和 LIMIT 進(jìn)行數(shù)據(jù)庫分頁的人。對于簡單的小型應(yīng)用程序和數(shù)據(jù)量不是很大的場景，這種方式還是能夠“應(yīng)付”的。

如果你想從頭開始構(gòu)建一個可靠且高效的系統(tǒng)，在一開始就要把它做好。

今天我們將探討已經(jīng)被廣泛使用的分頁方式存在的問題，以及如何實現(xiàn)高性能分頁。

1. OFFSET和LIMIT有什么問題

正如我們在上幾段中簡要探討的那樣，OFFSET和LIMIT非常適用于數(shù)據(jù)使用量很少的項目。

但是，當(dāng)數(shù)據(jù)庫里的數(shù)據(jù)量超過服務(wù)器內(nèi)存能夠存儲的能力，并且需要對所有數(shù)據(jù)進(jìn)行分頁，問題就會出現(xiàn)。

為了實現(xiàn)分頁，每次收到分頁請求時，數(shù)據(jù)庫都需要進(jìn)行低效的全表掃描。

全表掃描

什么是全表掃描？全表掃描（也稱為順序掃描）是在數(shù)據(jù)庫中進(jìn)行的掃描，其中順序讀取表中的每一行，然后檢查遇到的列是否符合條件。由于從磁盤進(jìn)行大量的?I/O?讀?。òǘ啻嗡褜ぃ┮约鞍嘿F的磁盤到內(nèi)存?zhèn)鬏?，這種類型的掃描被認(rèn)為是最慢的。


這意味著，如果你有 1 億個用戶，OFFSET 是 5 千萬，那么它需要獲取所有這些記錄 (包括那么多根本不需要的數(shù)據(jù))，將它們放入內(nèi)存，然后獲取 LIMIT 指定的 20 條結(jié)果。

也就是說，為了獲取一頁的數(shù)據(jù)：

10萬行中的第5萬行到第5萬零20行


需要先獲取 5 萬行。這么做是多么低效？

如果你不相信，可以看看這個例子：

https://www.db-fiddle.com/f/3JSpBxVgcqL3W2AzfRNCyq/1?ref=hackernoon.com

在左側(cè)面板中，您有一個基本架構(gòu)，該架構(gòu)將為我們的測試插入100.000行，而在右側(cè)，則是有問題的查詢和我們的解決方案。只需單擊頂部的“運行”，然后比較每個執(zhí)行時間。第一個查詢：1秒；（問題查詢）至少需要30秒鐘的時間才能運行。

數(shù)據(jù)越多，情況就越糟?？纯次覍?10 萬行數(shù)據(jù)進(jìn)行的 PoC。

https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com

現(xiàn)在你應(yīng)該知道這背后都發(fā)生了什么：OFFSET 越高，查詢時間就越長。

2. 有什么解決方案

這是你應(yīng)該使用的：

這是基于游標(biāo)的分頁。

你應(yīng)該存儲最后收到的主鍵（通常是一個ID）和LIMIT，而不是在本地存儲當(dāng)前的OFFSET和LIMIT并隨每個請求傳遞它，因此查詢最終可能與此類似。

為什么？因為通過顯式傳遞最新的讀取行，你可以根據(jù)有效的索引鍵告訴數(shù)據(jù)庫確切從哪里開始搜索，而不必考慮該范圍之外的任何行。

以下面的比較為例：

針對我們的優(yōu)化版本：

接收到的記錄完全相同，但是第一個查詢花費了12.80秒，第二個查詢花費了0.01秒。你能體會到差異嗎？

要使用這種基于游標(biāo)的分頁，需要有一個惟一的序列字段 (或多個)，比如惟一的整數(shù) ID 或時間戳，但是在某些特定情況下，這可能不符合我們的需求。

我的建議是始終考慮每種表體系結(jié)構(gòu)的優(yōu)缺點以及在每種表體系結(jié)構(gòu)中需要執(zhí)行哪種查詢。

如果您需要在查詢中處理大量相關(guān)數(shù)據(jù)，Rick James 的文章提供了更深入的指導(dǎo)。 http://mysql.rjweb.org/doc.php/lists

結(jié)論

這樣做的主要要點是始終檢查查詢的性能（無論是1k行還是1M行）。可伸縮性至關(guān)重要，如果從一開始就正確實施，肯定會避免將來出現(xiàn)許多麻煩。

再優(yōu)化

類似于查詢 SELECT * FROM table_name WHERE id > 8000000 LIMIT 10; ，這樣的效率非?？?因為主鍵上是有索引的,但是這樣有個缺點,就是ID必須是連續(xù)的,并且查詢不能有where語句,因為where語句會造成過濾數(shù)據(jù).

SELECT * FROM table_name WHERE id > 8000000 LIMIT 10;

覆蓋索引優(yōu)化

mysql 的查詢完全命中索引的時候，稱為覆蓋索引,是非常快的，因為查詢只需要在索引上進(jìn)行查找，之后可以直接返回，而不用再回數(shù)據(jù)表拿數(shù)據(jù)。因此我們可以先查出索引的 ID，然后根據(jù) Id 拿數(shù)據(jù)。

select * from (select id from table_name limit 1000000,100) a left join table_name b on a.id = b.id;

參考來源：https://hackernoon.com/please-dont-use-offset-and-limit-for-your-pagination-8ux3u4y

pub哥 2020 年終知識清單：