MySQL 用 limit 爲什麼會影響性能？有什麼優化方案？

作者：Java 小陳

原文：https://www.toutiao.com/article/7279396210157371904

Limit 是一種常用的分頁查詢語句，它可以指定返回記錄行的偏移量和最大數目。例如，下面的語句表示從 test 表中查詢 val 等於 4 的記錄，並返回第 300001 到第 300005 條記錄：

select * from test where val=4 limit 300000,5;

這樣的語句看起來很簡單，但是在實際使用中，可能會出現性能問題。爲什麼呢？我們需要從 Mysql 的索引結構和查詢過程來分析。

Mysql 的索引結構

Mysql 支持多種類型的索引，其中最常用的是 B+ 樹索引。B+ 樹索引是一種平衡多路查找樹，它有以下特點：

下圖是一個 B+ 樹索引的示例：

在 Mysql 中，有兩種常見的 B+ 樹索引：聚簇索引和非聚簇索引。

聚簇索引是一種特殊的 B+ 樹索引，它將數據記錄和索引放在一起存儲，也就是說，葉子節點就是數據記錄。在 Mysql 中，每張表只能有一個聚簇索引，通常是主鍵或者唯一非空鍵。如果沒有定義這樣的鍵，Mysql 會自動生成一個隱藏的聚簇索引。

非聚簇索引是一種普通的 B+ 樹索引，它將數據記錄和索引分開存儲，也就是說，葉子節點只存儲鍵值和指向數據記錄地址的指針。在 Mysql 中，每張表可以有多個非聚簇索引，通常是普通鍵或者唯一鍵。

下圖是一個聚簇索引和非聚簇索引的對比：

聚簇索引非聚簇索引

Mysql 的查詢過程

當我們執行一個 SQL 查詢語句時，Mysql 會根據優化器的選擇，使用不同的執行計劃來執行。其中，最常見的執行計劃有以下幾種：

下圖是一個回表查詢的示例：

Mysql 的 Limit 性能問題

回到我們最開始的問題，Mysql 的 Limit 會影響性能嗎？爲什麼？

答案是：會影響性能，因爲 Limit 會導致 Mysql 掃描過多的數據記錄或者索引記錄，而且大部分掃描到的記錄都是無用的。

我們以一個非聚簇索引爲例，來分析一下 Limit 的影響。假設我們有一張表 test ，它有兩個字段 id 和 val ，其中 id 是主鍵，val 是非唯一非聚簇索引。表中有 500 萬條數據，val 的值從 1 到 10 隨機分佈。我們執行以下語句：

select * from test where val=4 limit 300000,5;

這條語句的意思是查詢 val 等於 4 的記錄，並返回第 300001 到第 300005 條記錄。Mysql 會怎麼執行呢？

首先，Mysql 會選擇 val 索引作爲執行計劃，因爲它可以縮小查詢範圍。然後，Mysql 會從 val 索引的根節點開始查找，沿着 B+ 樹向下搜索，直到找到第一個 val 等於 4 的葉子節點。接着，Mysql 會沿着葉子節點的指針向右移動，掃描所有 val 等於 4 的葉子節點，並記錄它們對應的 id 值和數據記錄地址。

由於我們要返回第 300001 到第 300005 條記錄，所以 Mysql 必須掃描至少 300005 個葉子節點，才能確定哪些是我們需要的。這就導致了大量的隨機 I/O 操作，在磁盤上讀取索引頁。

接下來，Mysql 還要根據葉子節點指向的數據記錄地址，去訪問數據頁，獲取查詢所需的所有字段。由於我們要返回所有字段（select *），所以 Mysql 必須訪問至少 300005 次數據頁，才能獲取到完整的數據記錄。這又導致了大量的隨機 I/O 操作，在磁盤上讀取數據頁。

最後，Mysql 還要對掃描到的數據記錄進行排序和過濾，拋棄前面 300000 條無用的記錄，只保留後面 5 條有用的記錄。這就導致了大量的 CPU 和內存消耗，在內存中進行排序和過濾。

綜上所述，Mysql 在執行這條語句時，需要做以下操作：

這些操作都是非常耗時和耗資源和時間的浪費。爲了返回 5 條有用的記錄，Mysql 不得不掃描和訪問大量的無用的記錄。這就是 Limit 會影響性能的原因。

那麼，有沒有辦法優化這個問題呢？

答案是：有，但是需要根據具體的情況來選擇合適的方法。下面，我們介紹幾種常見的優化方法：

使用索引覆蓋掃描。

如果我們只需要查詢部分字段，而不是所有字段，我們可以嘗試使用索引覆蓋掃描，也就是讓查詢所需的所有字段都在索引中，這樣就不需要再訪問數據頁，減少了隨機 I/O 操作。

例如，如果我們只需要查詢 id 和 val 字段，我們可以執行以下語句：

select id,val from test where val=4 limit 300000,5;

這樣，Mysql 只需要掃描索引頁，而不需要訪問數據頁，提高了查詢效率。

使用子查詢。

如果我們不能使用索引覆蓋掃描，或者查詢字段較多，我們可以嘗試使用子查詢，也就是先用一個子查詢找出我們需要的記錄的 id 值，然後再用一個主查詢根據 id 值獲取其他字段。

例如，我們可以執行以下語句：

select * from test where id in (select id from test where val=4 limit 300000,5);

這樣，Mysql 先執行子查詢，在 val 索引上進行範圍掃描，並返回 5 個 id 值。然後，Mysql 再執行主查詢，在 id 索引上進行點查找，並返回所有字段。這樣，Mysql 只需要掃描 5 個數據頁，而不是 300005 個數據頁，提高了查詢效率。

使用分區表。

如果我們的表非常大，或者數據分佈不均勻，我們可以嘗試使用分區表，也就是將一張大表分成多個小表，並按照某個字段或者範圍進行劃分。這樣，Mysql 可以根據條件只訪問部分分區表，而不是整張表，減少了掃描和訪問的數據量。

例如，如果我們按照 val 字段將 test 表分成 10 個分區表（test_1 到 test_10），每個分區表只存儲 val 等於某個值的記錄，我們可以執行以下語句：

select * from test_4 limit 300000,5;

這樣，Mysql 只需要訪問 test_4 這個分區表，而不需要訪問其他分區表，提高了查詢效率。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/7vMU4lZPDLxeukwOVkOyIg

猜你喜歡