優(yōu)化sql分頁查詢性能的核心在于減少offset的使用，避免全表掃描。1.使用limit和offset適用于小數(shù)據(jù)量，但offset過大時會導致性能急劇下降；2.通過子查詢或join先找到起始id再進行范圍查詢，在一定程度上提升性能；3.書簽式分頁基于唯一遞增字段記錄上一頁最后一條數(shù)據(jù)id，直接從該位置開始查詢，適合大數(shù)據(jù)量場景，但不支持跳頁；4.選擇方案需綜合考慮數(shù)據(jù)量、是否需要跳頁、是否存在唯一遞增字段及數(shù)據(jù)庫類型；5.為避免高并發(fā)下的數(shù)據(jù)重復或遺漏，可使用事務、快照隔離、鎖定表或樂觀鎖等機制；6.orm框架如spring data jpa可通過pageable實現(xiàn)分頁，但仍需關注底層sql性能。合理選擇分頁策略是高效獲取數(shù)據(jù)的關鍵。
SQL分頁查詢，說白了，就是如何在數(shù)據(jù)庫中高效地獲取指定范圍的數(shù)據(jù)。核心在于優(yōu)化查詢語句，避免全表掃描，提升查詢效率。
分頁查詢是日常開發(fā)中高頻使用的功能，尤其是在數(shù)據(jù)量巨大的情況下，用戶不可能一次性瀏覽所有數(shù)據(jù)，所以分頁顯示是必須的。下面我們來聊聊SQL實現(xiàn)分頁查詢的幾種常見方案。
如何優(yōu)化SQL分頁查詢的性能？
分頁查詢的性能瓶頸通常在于offset的計算。比如，查詢第100頁，每頁10條數(shù)據(jù)，數(shù)據(jù)庫需要跳過前990條數(shù)據(jù)才能找到第100頁的數(shù)據(jù)。當offset非常大時，這個跳過的過程會消耗大量資源。
方案一：使用LIMIT和OFFSET
這是最基礎的分頁方式，幾乎所有數(shù)據(jù)庫都支持。
SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 990;
雖然簡單，但正如前面所說，當OFFSET很大時，性能會急劇下降。 數(shù)據(jù)庫需要先找到前990條數(shù)據(jù)，然后再丟棄，只返回后面的10條。
方案二：使用子查詢或連接
這種方式避免了OFFSET的大量跳過操作。 核心思想是先找到起始位置的ID，然后基于這個ID進行范圍查詢。
SELECT * FROM table_name WHERE id > (SELECT id FROM table_name ORDER BY id LIMIT 990, 1) ORDER BY id LIMIT 10;
或者使用JOIN：
SELECT t1.* FROM table_name t1 JOIN (SELECT id FROM table_name ORDER BY id LIMIT 990, 1) t2 ON t1.id > t2.id ORDER BY t1.id LIMIT 10;
這種方法在很多情況下比LIMIT和OFFSET更快，因為它只需要找到起始ID，然后直接從該ID開始查詢。
方案三：書簽式分頁（也稱為游標分頁）
書簽式分頁不使用OFFSET，而是記錄上一頁的最后一條數(shù)據(jù)的ID（或者其他唯一標識），然后在查詢下一頁時，直接從該ID之后開始查詢。
假設上一頁最后一條數(shù)據(jù)的ID是1000，查詢下一頁的SQL如下：
SELECT * FROM table_name WHERE id > 1000 ORDER BY id LIMIT 10;
這種方式避免了OFFSET的計算，性能非常高，尤其是在數(shù)據(jù)量非常大的情況下。 但是，書簽式分頁要求數(shù)據(jù)必須有一個唯一且遞增的字段，比如ID。同時，它也不支持跳頁查詢，只能一頁一頁地往下翻。
性能對比
簡單來說，LIMIT和OFFSET在數(shù)據(jù)量小的情況下夠用，但數(shù)據(jù)量一大，性能就急劇下降。子查詢或JOIN的方式在一定程度上可以緩解這個問題，但仍然存在一定的性能瓶頸。書簽式分頁在數(shù)據(jù)量非常大的情況下性能最好，但限制也比較多。
如何選擇合適的分頁方案？
選擇合適的分頁方案需要考慮以下幾個因素：
- 數(shù)據(jù)量： 數(shù)據(jù)量越大，越需要選擇性能更高的方案，比如書簽式分頁。
- 是否需要跳頁查詢： 如果需要跳頁查詢，書簽式分頁就無法滿足需求，只能選擇LIMIT和OFFSET或者子查詢/JOIN。
- 是否有唯一且遞增的字段： 書簽式分頁要求數(shù)據(jù)必須有一個唯一且遞增的字段。
- 數(shù)據(jù)庫類型： 不同的數(shù)據(jù)庫對分頁語法的支持可能略有不同。
分頁查詢時如何避免數(shù)據(jù)重復或遺漏？
這是一個很常見的問題，尤其是在高并發(fā)場景下。 比如，在兩次分頁查詢之間，有新的數(shù)據(jù)插入或者刪除，就可能導致數(shù)據(jù)重復或遺漏。
解決方案：
- 使用事務： 將分頁查詢放在一個事務中，確保在查詢過程中數(shù)據(jù)不會被修改。 但這種方式可能會影響并發(fā)性能。
- 使用快照隔離： 某些數(shù)據(jù)庫支持快照隔離，可以在查詢開始時創(chuàng)建一個數(shù)據(jù)快照，然后基于該快照進行查詢，避免數(shù)據(jù)被修改。
- 鎖定表： 在查詢期間鎖定表，阻止其他事務修改數(shù)據(jù)。 這種方式對并發(fā)性能影響最大，應該盡量避免。
- 使用樂觀鎖或悲觀鎖： 在數(shù)據(jù)表中增加一個版本號字段，每次修改數(shù)據(jù)時都更新版本號。 在查詢時，可以根據(jù)版本號來判斷數(shù)據(jù)是否被修改。
選擇哪種方案取決于具體的業(yè)務場景和性能要求。 一般來說，使用快照隔離或者樂觀鎖是比較好的選擇，既能保證數(shù)據(jù)的一致性，又能兼顧并發(fā)性能。
如何在ORM框架中使用分頁查詢？
現(xiàn)在很多項目都使用ORM框架，比如mybatis、hibernate、Spring Data JPA等。 這些框架都提供了分頁查詢的支持。
以Spring Data JPA為例：
Pageable pageable = PageRequest.of(pageNumber, pageSize, Sort.by("id").ascending()); Page<Entity> page = repository.findAll(pageable);
Pageable對象包含了分頁信息，比如頁碼、每頁大小、排序方式等。 repository.findAll(pageable)方法會自動生成分頁查詢的sql語句。
ORM框架通常會對分頁查詢進行一定的優(yōu)化，比如使用預編譯SQL、緩存等。 但最終的性能仍然取決于底層的SQL語句。 因此，在使用ORM框架進行分頁查詢時，仍然需要關注SQL語句的性能。
總而言之，SQL分頁查詢是一個看似簡單，實則需要深入理解的知識點。選擇合適的分頁方案，優(yōu)化SQL語句，才能在海量數(shù)據(jù)中高效地獲取所需信息。
