使用 OFFSET/LIMIT 會導致 MySQL 查詢速度變慢?
「為什麼都做分頁了，還會這麼慢？」
在後端開發中，很多時候我們需要做分頁來進行資料庫的查詢。在 MySQL 中，最常見的方法之一就是使用 OFFSET 和 LIMIT。但你知道嗎？當資料量變得非常龐大時，這種方式可能會讓查詢變得超慢！今天我們就來做幾個實驗，看看為什麼會這樣，並且找找看有沒有更好的解決方法。
前置作業
首先，我們需要建立一個測試用的資料表並插入大量假資料。剛好我近一年有協助公司開發潛在客戶管理的系統，那就用潛在客戶來做範例吧！
建立測試資料表
已複製！CREATE TABLE tbl_customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
安裝套件
已複製！npm install @faker-js/faker mysql2/promise moment
插入 10,000,000 筆假資料
可以使用 Node.js 撰寫 Script 批次插入資料 (其他語言亦可，在此以 Node.js 做範例)：
已複製！import { faker } from "@faker-js/faker"; import mysql from "mysql2/promise"; import moment from "moment"; const pool = mysql.createPool({ host: [[Your Host]], user: [[Your User]], password: [[Your Password]], database: "test_db", waitForConnections: true, connectionLimit: 150, queueLimit: 0, }); function createFakeCustomerData() { return { firstName: faker.person.firstName(), lastName: faker.person.lastName(), email: faker.internet.email(), }; } const generateTestData = async () => { let count = 0; const batchSize = 100000; const totalRows = 1000000; try { while (count < totalRows) { const dataBatch = []; const createdTime = moment().unix(); for (let i = 0; i < batchSize && count < totalRows; i++) { const { firstName, lastName, email } = createFakeCustomerData(); dataBatch.push([firstName, lastName, email, createdTime]); count++; } const insertQuery = "INSERT INTO tbl_customers (first_name, last_name, email, created_time) VALUES ?"; await pool.query(insertQuery, [dataBatch]); console.log(`Inserted ${batchSize} rows, total: ${count}`); } console.log("Finished inserting data"); } catch (err) { console.error("Error during data insertion:", err); } finally { pool.end(); } }; generateTestData();
現在，我們有一個名為 tbl_customers 的 Table，裡面有我們建立好的 1000000 筆假資料，其中 Primary Key 為 id。
測試開始
首先，在此先定義一頁只撈取 10 筆資料，撈出來的資料照 id 進行排序。
已複製！| id | first_name | last_name | email | created_time | |-----|------------|-------------|----------------------------|--------------| | 1 | Timmy | Ernser | Aileen.Harvey@yahoo.com | 1729566959 | | 2 | Bette | Wintheiser | Murl.Jones@yahoo.com | 1729566959 | | 3 | Montana | Cassin | Emelia.Jaskoli@hotmail.com | 1729566959 | | 4 | Madaline | Heller | Izabella31@gmail.com | 1729566959 | | 5 | Jennings | Little | Tyrese.Grimes@yahoo.com | 1729566959 |
用 Offset ＋ limit 進行分頁
- 第 1 頁（Offset 0）
已複製！SELECT * FROM test_db.tbl_customers order by id limit 10 offset 0;
執行結果：
已複製！| Query | Rows Returned | Execution Time (sec) | |---------------------------------------------------------------------|---------------|----------------------| | `SELECT * FROM test_db.tbl_customers order by id limit 10 offset 0` | 10 | 0.0023 sec |
- 第 100,000 頁（Offset 999,990）
已複製！SELECT * FROM test_db.tbl_customers order by id limit 10 offset 999990;
執行結果：
已複製！| Query | Rows Returned | Execution Time (sec) | |--------------------------------------------------------------------------------|---------------|----------------------| | `SELECT * FROM test_db.tbl_customers order by id limit limit 10 offset 999990` | 10 | 2.4567 sec |
可以看到，當我們在第一頁查詢時，執行時間非常快，僅需約 0.0023 秒。但當我們查詢到第 100,000 頁時，執行時間竟然增加到約 2.45 秒！這說明了當使用 OFFSET 進行大偏移量的查詢時，性能會明顯下降，且隨著 OFFSET 數量的增加，查詢變得越來越慢。
為什麼會這樣？
我們可以使用 EXPLAIN 來檢查我們剛剛的 SQL 語句：
已複製！EXPLAIN ANALYZE SELECT * FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990;
已複製！| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |----|-------------|----------------------|--------|---------------|---------|---------|------|--------|-------------| | 1 | SIMPLE | tbl_customers | index | NULL | PRIMARY | 4 | NULL | 1000000| NULL |
解釋：
從 EXPLAIN 結果可以看出，MySQL 在查詢時沒有利用任何條件篩選 (where)，這意味著 MySQL 必須遍歷所有的資料直到達到指定的 OFFSET。當 OFFSET 數值非常大時，這樣的操作耗費大量資源，導致查詢速度變慢。
到底影響有多大呢？若我們把 OFFSET 對執行時間做圖，會得到以下圖表：
原因在於 OFFSET 的工作方式。當你執行一個帶有大 OFFSET 的查詢時，資料庫必須掃描並丟棄 OFFSET 指定的行數，然後再返回接下來的結果。也就是說，對於 OFFSET 999990，資料庫需要掃描 999,990 行資料但不返回，這對於大型資料集來說，代價非常高昂。
已複製！| OFFSET | Execution Time (sec) | |-----------|----------------------| | 1,000 | 0.00059500 | | 2,000 | 0.00097400 | | 5,000 | 0.00445300 | | 10,000 | 0.00392400 | | 20,000 | 0.00949200 | | 50,000 | 0.01687200 | | 100,000 | 0.03510600 | | 200,000 | 0.10199900 | | 500,000 | 0.17357600 | | 1,000,000 | 0.32615300 | | 2,000,000 | 0.91823100 |
有什麼解決方法？
要解決查詢效能低落的問題，有以下幾種解法：
- 使用索引並避免 OFFSET
- 延遲關聯（Deferred Join）
- 使用 JOIN 進行最佳化
避免 OFFSET
透過使用 Index 並改寫查詢，我們可以大幅提升查詢的效能。例如，使用 Primary Key 或 Index 欄位作為篩選條件：
已複製！SELECT * FROM test_db.tbl_customers WHERE id > 999990 ORDER BY id LIMIT 10;
這樣，資料庫可以直接從指定的 id 開始讀取資料，而不需要掃描和丟棄大量的 Rows 。
執行結果：
已複製！| Query | Rows Returned | Execution Time (sec) | |------------------------------------------------------------------------------|---------------|----------------------| | `SELECT * FROM test_db.tbl_customers WHERE id > 999990 ORDER BY id LIMIT 10` | 10 | 0.0015 sec |
讓我們再次使用 EXPLAIN 來檢查我們改寫後的 SQL 語句：
已複製！EXPLAIN SELECT * FROM test_db.tbl_customers WHERE id > 999990 ORDER BY id LIMIT 10;
已複製！| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |----|-------------|----------------------|--------|---------------|---------|---------|------|--------|-------------| | 1 | SIMPLE | tbl_customers | range | PRIMARY | PRIMARY | 4 | NULL | 993399 | Using where |
解釋：
這次的查詢 MySQL 可以直接利用 id > 999990 的條件快速定位到資料，避免了之前需要掃描大量資料的問題。這樣的查詢能夠顯著降低檢索時間，特別適合在大型資料集上進行分頁查詢。
延遲關聯
延遲關聯（Deferred Join）是一種最佳化查詢的方法，特別適用於需要從多個表中查詢資料的情況。其核心思想是先查詢 Primary Key 或 Index 列，取得所需的 id，然後再根據這些 id 去查詢完整的資料。這樣可以減少資料庫需要處理的資料量，提高查詢性能。
實際操作
步驟一：先查詢需要的主鍵列表
已複製！SELECT id FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990;
執行結果：
已複製！| Query | Rows Returned | Execution Time (sec) | |------------------------------------------------------------------------------|---------------|----------------------| | `SELECT id FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990` | 10 | 0.0015 sec |
步驟二：使用這些主鍵查詢完整的資料
已複製！SELECT * FROM test_db.tbl_customers WHERE id IN (/* 步驟一獲取的 id 列表 */);
為了方便，我們可以將兩個步驟合併在一起，使用子查詢：
已複製！SELECT * FROM test_db.tbl_customers WHERE id IN ( SELECT id FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990 ) ORDER BY id;
執行結果：
已複製！| Query | Rows Returned | Execution Time (sec) | |-----------------------------------------------------------------------------------------------------------------------------------------------|---------------|----------------------| | `SELECT id FROM test_db.tbl_customers WHERE id IN (`SELECT id FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990`) ORDER BY id; | 10 | 0.0035 sec |
為什麼這樣會更快？
因為在第一個子查詢中，僅僅需要掃描 id 列，資料量非常小。接下來的主查詢中，使用 id 列表進行篩選，可以有效利用索引，減少不必要的資料讀取。
使用 JOIN 進行最佳化
我們也可以使用 JOIN 來替代子查詢，進一步最佳化查詢性能。例如：
已複製！SELECT t1.* FROM test_db.tbl_customers t1 JOIN ( SELECT id FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990 ) t2 ON t1.id = t2.id ORDER BY t1.id;
執行結果：
已複製！| Query | Rows Returned | Execution Time (sec) | |------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------|----------------------| | SELECT t1.* FROM test_db.tbl_customers t1 JOIN (SELECT id FROM test_db.tbl_customers ORDER BY id LIMIT 10 OFFSET 999990) t2 ON t1.id = t2.id ORDER BY t1.id; | 10 | 0.0030 sec |
這種方式可以讓資料庫的查詢更有效率，進一步提升性能。
結論
使用 OFFSET 和 LIMIT 進行分頁時，當 OFFSET 值很大時，查詢性能會明顯下降。透過使用索引、避免大的 OFFSET 值，以及採用 延遲關聯（Deferred Join 或 使用 JOIN 的方式，我們可以大幅提升查詢性能。