SQL分頁查詢怎么寫 分頁查詢的3種實現方式

sql分頁查詢的3種實現方式為：1.limit offset（適用于mysql等），通過指定limit返回記錄數量和offset起始行數實現，如select from products limit 10 offset 20可獲取第21-30條記錄；2.row_number() over（適用于sql server等），使用窗口函數為每行分配唯一序號并基于該序號過濾，如通過rownum在21到30之間篩選；3.游標（不推薦），雖可用于分頁但效率低，不適合高并發或大數據場景。優化策略包括索引優化、避免select 、使用覆蓋索引、緩存、預編譯sql語句以及避免在where中使用函數。limit offset的性能瓶頸在于大offset值會導致數據庫掃描大量行后丟棄，解決方法有書簽法（利用上一頁最后記錄的排序值）、覆蓋索引和避免深度分頁。不同數據庫分頁語法存在差異：mysql/mariadb/postgresql使用limit offset，postgresql還支持fetch first n rows only；sql server支持row_number()或offset fetch（2012及以上版本）；oracle舊版用rownum，新版支持offset fetch；sqlite使用limit offset，實際應用時需考慮各數據庫的索引與查詢優化器特性以提升性能。

SQL分頁查詢，簡單來說，就是從數據庫中提取特定范圍內的數據記錄，而不是一次性加載整個數據集。這對于處理大型數據集至關重要，可以顯著提高查詢效率和用戶體驗。

分頁查詢的3種實現方式

LIMIT OFFSET (適用于MySQL等)

這是最常見且易于理解的分頁方法。LIMIT 指定要返回的記錄數量，OFFSET 指定從哪一行開始返回。

SELECT * FROM products LIMIT 10 OFFSET 20;

這條sql語句會返回 products 表中從第21條記錄開始的10條記錄（即第21-30條記錄）。OFFSET 從0開始計數。

使用ROW_NUMBER() OVER() (適用于SQL Server等)

這種方法利用窗口函數 ROW_NUMBER() 為結果集中的每一行分配一個唯一的序號。然后，你可以基于這個序號進行過濾。

WITH RankedProducts AS (     SELECT         *,         ROW_NUMBER() OVER (ORDER BY product_id) AS RowNum     FROM         products ) SELECT * FROM RankedProducts WHERE RowNum BETWEEN 21 AND 30;

這個查詢首先使用 ROW_NUMBER() 根據 product_id 對 products 表進行排序，并為每一行分配一個 RowNum。然后，它選擇 RowNum 在 21 到 30 之間的記錄。注意，ORDER BY 子句至關重要，因為它決定了行的排序方式，進而影響 ROW_NUMBER() 的結果。沒有 ORDER BY，結果將是不可預測的。

使用游標（不推薦，效率較低）

游標允許你逐行處理結果集。雖然可以用于分頁，但通常效率較低，不建議在高并發或大數據量的場景中使用。這里不提供具體代碼示例，因為更推薦使用前兩種方式。

SQL分頁查詢優化有哪些策略？

1. 索引優化： 確保用于排序和過濾的列（例如 product_id、RowNum）上建有索引。這可以顯著加快查詢速度。對于 LIMIT OFFSET 方法，如果 OFFSET 值很大，數據庫可能需要掃描大量的行才能找到起始位置。在這種情況下，可以考慮使用 “書簽” 或 “延遲連接” 技術。
2. *避免SELECT ：** 只選擇需要的列，減少數據傳輸量。
3. 使用覆蓋索引： 如果查詢只需要索引中的列，數據庫可以直接從索引中獲取數據，而無需訪問表本身。
4. 緩存： 對于頻繁訪問的分頁數據，可以考慮使用緩存來減少數據庫負載。
5. 預編譯SQL語句： 如果分頁查詢的結構不變，只是參數不同，可以使用預編譯的SQL語句來提高效率。
6. 避免在WHERE子句中使用函數： 在 WHERE 子句中使用函數會導致索引失效。

LIMIT OFFSET分頁的性能瓶頸是什么？

LIMIT OFFSET 的主要性能瓶頸在于 OFFSET。當 OFFSET 值很大時，數據庫需要掃描大量的行才能找到起始位置。例如，LIMIT 10 OFFSET 1000000 意味著數據庫需要掃描 1000010 行，然后丟棄前 1000000 行，只返回最后的 10 行。這顯然是非常低效的。

解決 OFFSET 性能瓶頸的一些方法：

• 使用書簽（也稱為 “延遲連接” 或 “Seek Method”）： 記錄上一頁的最后一條記錄的排序字段值，然后在下一頁查詢中使用 WHERE 子句來過濾掉之前的記錄。例如：

-- 假設上一頁的最后一條記錄的 product_id 是 100 SELECT * FROM products WHERE product_id > 100 ORDER BY product_id LIMIT 10;

這種方法避免了掃描大量的行，而是直接從指定的位置開始讀取。

• 使用覆蓋索引： 如果查詢只需要索引中的列，數據庫可以直接從索引中獲取數據，而無需訪問表本身。這可以顯著提高查詢速度。
• 避免深度分頁： 盡量避免用戶瀏覽到非常靠后的頁碼。可以考慮使用 “加載更多” 或 “無限滾動” 等技術來替代傳統的分頁。

不同數據庫系統在分頁查詢上的差異有哪些？

不同的數據庫系統在分頁查詢的語法和性能上存在一些差異。

• MySQL/MariaDB/PostgreSQL： 使用 LIMIT OFFSET 語法。PostgreSQL 還支持 FETCH FIRST n ROWS ONLY 語法，效果與 LIMIT n 相同。
• SQL Server： 使用 ROW_NUMBER() OVER() 窗口函數或者 OFFSET FETCH 語法（SQL Server 2012 及更高版本）。OFFSET FETCH 語法類似于 LIMIT OFFSET，但更加標準化。

SELECT * FROM products ORDER BY product_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

• oracle： 在舊版本中使用 ROWNUM 偽列，新版本（12c 及更高版本）也支持 OFFSET FETCH 語法。
• SQLite： 使用 LIMIT OFFSET 語法。

需要注意的是，不同數據庫系統在索引優化、查詢優化器等方面也存在差異，因此在實際應用中需要根據具體情況進行調整。例如，MySQL 的 LIMIT OFFSET 在大 OFFSET 值時性能較差，而 SQL Server 的 OFFSET FETCH 語法在某些情況下可能比 ROW_NUMBER() OVER() 更高效。