關於MySQL Limit 的實作原理

2024.10.08

在實際工作中，我們經常使用MySQL 中的LIMIT子句來控制查詢傳回的資料大小，特別是在分頁、效能最佳化等場景中。這篇文章，我們將深入探討MySQL 中LIMIT的實作原理，以及如何在不同場景下有效運用此功能。

什麼是LIMIT？

LIMIT 是SQL 查詢語句中的子句，用來限制查詢結果的行數。在MySQL 中，LIMIT 子句也可以與offset結合使用，以實現更複雜的應用場景，例如分頁查詢。 LIMIT的語法如下：

SELECT column1, column2, ... FROM table_name LIMIT [offset,] row_count;

在上述語法中：row_count 表示傳回的記錄行數。 offset 表示要跳過的記錄數。它是可選的，如果不指定則預設為0。

如下範例：

SELECT * FROM order LIMIT 10; # 返回表中的前 10 行记录。
SELECT * FROM order LIMIT 10 10; # 从第 11 行开始返回接下来的 10 行记录

LIMIT 在MySQL 中的實現

MySQL 內部是如何實作LIMIT的呢？為了更了解其實作原理，我們需要先了解MySQL 的查詢執行過程。在MySQL 中，查詢執行過程主要由解析器、最佳化器和執行器三個部分組成：

解析器（Parser）：將SQL 語句解析成資料結構，通常是解析樹。
優化器（Optimizer）：對查詢進行最佳化，例如選擇最優的執行計劃。
執行器（Executor）：根據優化器提供的計畫逐步執行查詢。

而LIMIT子句的處理主要發生在最佳化器和執行器兩個階段。下面我們分別從這兩個階段來說明。

1.優化器階段

在最佳化器階段，MySQL 會考慮LIMIT和OFFSET來最佳化查詢計畫。查詢最佳化器透過考慮是否使用索引、何時應用排序、何時進行過濾、在何處應用LIMIT子句等來產生一個效率較高的執行計劃。

索引的利用：當查詢中涉及排序（ORDER BY）並且有可能利用索引時，優化器會嘗試在索引階段就套用 LIMIT，這可以避免全表掃描，提高查詢速度。
子查詢最佳化：在某些情況下，如果LIMIT出現在子查詢中，優化器可能會選擇透過推導LIMIT到上一層查詢，從而減少不必要的資料處理。

2.執行器階段

在執行器階段，MySQL 在逐行讀取資料時套用LIMIT子句。在資料讀取過程中，執行器會根據LIMIT和offset的值來控制需要傳回的行數。

資料截取：對於一個沒有offset的LIMIT子句，執行器會在讀取到 row_count 行之後立刻中斷讀取過程，這可以大大節省資源。
跳過記錄：在有offset的情況下，執行器會跳過前offset行數據，然後開始計數 row_count，直到滿足要求為止。

效能影響和優化

使用LIMIT進行分頁查詢時需注意效能問題。通常，OFFSET 較大的情況下可能會導致效能下降，因為MySQL 必須掃描和丟棄大量的記錄。這時可以考慮以下優化策略：

1.索引優化

透過合理設計索引可以減少全表掃描。例如，如果查詢中包含排序（ORDER BY）可以利用的索引，則使用索引可以更快速地找到所需的資料行，從而減少不必要的資料掃描。

如下範例：可以為created_at欄位建立一個索引

SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000, 10;

2.覆蓋索引

利用覆蓋索引來加速查詢。當索引本身就包含要查詢的資料列時，MySQL 可以直接從索引中取得數據，而無需存取表，這樣能夠提高效率。

如下範例：可以為user_id欄位建立一個idx_user_id索引，這樣user_id的值就可以直接從索引上取得。

SELECT user_id FROM user_actions WHERE user_id = ? LIMIT 10;

3.子查詢與連線優化

在某些情況下，可以透過使用偽列或輔助腳本為大量分頁提前計算出中間結果，減少offset帶來的影響。

-- 使用子查询减少偏移量
SELECT * FROM (SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000, 10) as temp;

4.其它技術

延遲關聯（Deferred Join）：延遲關聯的核心思想是先透過一個簡單且有效率的查詢來取得目標記錄的主鍵（或候選鍵），然後再利用這些主鍵進行進一步的複雜關聯查詢。這樣可以避免在初始階段處理大量不必要的數據，減少了I/O 和CPU 開銷。延遲關聯可以用於避免在分頁時對大表的多次存取。書籤（Bookmarking）：書籤方法旨在利用唯一且依序可比的欄位（通常是主鍵或時間戳記）來決定分頁資料起始點，而不是使用OFFSET。這樣，更大的偏移查詢也能保持較好的效能，因為查詢限制在會影響的較小資料集內。例如使用上一頁最後一行的唯一識別來作為下頁的查詢條件。

實踐建議

合理使用LIMIT：盡量避免過大的OFFSET 值。充分利用索引：在大量資料分頁場景中，設計良好的索引是至關重要的。使用快取：對於相同的查詢，可以使用快取來避免重複計算和資料存取。批次處理：對於可能的大數據處理任務，可以考慮以批次的形式進行處理，然後進行分頁顯示。

總結

本文，我們分析了MySQL 的 LIMIT執行原理，在實際使用中，當offset較大時，效能可能會下降，我們應該考慮透過索引最佳化、覆蓋索引、子查詢等方式來改善效能。

新聞