遊標分頁 - Lindorm

如果常規分頁查詢無法滿足您的業務需求，您可以在查詢時設定遊標分頁。遊標分頁通過記錄分頁位置的方式，可以在海量資料查詢情境中快速定位目標資料，減少大規模掃描帶來的效能開銷。本文介紹遊標分頁特性的使用方法和相關注意事項。

前提條件

寬表引擎為2.7.8及以上版本。您可以在控制台查看寬表引擎版本並升級小版本至2.7.8及以上版本。

注意事項

遊標分頁特性僅支援簡單查詢，不支援SUM、COUNT等彙總查詢，也不支援包含ORDER BY、GROUP BY等子句的查詢。
遊標分頁特性必須與OFFSET、LIMIT子句同時使用。

功能簡介

常規分頁查詢基於OFFSET和LIMIT條件在記憶體中過濾資料。當資料量較大時，深度分頁會導致每次查詢都要掃描並跳過大量資料，效率較低。

Lindorm提供了遊標分頁特性，支援在查詢語句中指定OFFSET、LIMIT子句來實現分頁邏輯。在執行遊標分頁查詢時，系統可以直接根據遊標快速定位到要查詢資料的位置，避免掃描大量資料並跳過，從而提升大量資料情境下的查詢效率。

使用方法

顯式投影（推薦）

在原先分頁查詢的語句中添加_l_next_cursor_ 參數，即可返回下一個分頁的遊標。樣本如下：

SELECT <column_identifier1>, <column_identifier2>, _l_next_cursor_ FROM <table_identifier> LIMIT 0, 5;

使用遊標分頁的查詢結果會多出一列名為_l_next_cursor_的列，列類型為VARCHAR，該列的值為下一頁的遊標。

需要查詢下一頁時，您可以在WHERE條件中使用_l_current_cursor_將上一頁返回的遊標值與原先的過濾條件（例如c1=1）通過AND帶回。樣本如下：

SELECT <column_identifier1>, <column_identifier2>, _l_next_cursor_ FROM <table_identifier> WHERE _l_current_cursor_ = '<遊標值>' AND <過濾條件>1 LIMIT 5, 5;

隱式投影

說明

如果您無法預先確定待查詢的列名，建議您使用隱式投影。例如動態列查詢情境。

當查詢投影列表為*時，預設只返回表預先定義的列，不會返回遊標列。如果需要在查詢時返回遊標列，在SELECT關鍵字後面添加HINT參數 /*+ _l_allow_cursor_ */ 即可返回下一個分頁的遊標。樣本如下：

SELECT /*+ _l_allow_cursor_ */ * FROM <table_identifier> LIMIT 0, 5;

使用遊標分頁的查詢結果會多出_l_next_cursor_列，列類型為VARCHAR，該列的值為下一頁的遊標。需要查詢下一頁時，在WHERE條件中使用_l_current_cursor_將上一頁返回的遊標值與原先的過濾條件（例如c1=1）通過AND帶回。樣本如下：

SELECT /*+ _l_allow_cursor_ */ * FROM <table_identifier> WHERE _l_current_cursor_ = '<遊標值>' AND <原先的過濾條件> LIMIT 5, 5;

重要

遊標的值會隨著查詢結果動態變化，因此下一頁的遊標以當前查詢結果的最後一行為準。

樣本

假設樣本表tb_cursor_test的結構與表中資料如下：

-- 建立樣本表
CREATE TABLE tb_cursor_test(c1 INT , c2 INT, c3 VARCHAR(50), PRIMARY KEY(c1));

-- 插入資料
UPSERT INTO tb_cursor_test(c1, c3) VALUES (1, 'c3_1'),(2, 'c3_2'),(3, 'c3_3'),(4, 'c3_4'),(5, 'c3_5');
UPSERT INTO tb_cursor_test(c1, c3) VALUES (6, 'c3_6'),(7, 'c3_7'),(8, 'c3_8'),(9, 'c3_9'),(10, 'c3_10');
UPSERT INTO tb_cursor_test(c1, c3) VALUES (11, 'c3_11'),(12, 'c3_12'),(13, 'c3_13'),(14, 'c3_14'),(15, 'c3_15');

首次執行遊標分頁查詢。

重要

首次執行遊標分頁查詢時，必須在查詢語句中添加_l_next_cursor_參數。

SELECT c1, c2, c3,  _l_next_cursor_ FROM tb_cursor_test LIMIT 0, 5;

返回結果：

+----+------+------+------------------------------+
| c1 | c2   | c3   | _l_next_cursor_              |
+----+------+------+------------------------------+
| 1  | null | c3_1 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 2  | null | c3_2 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 3  | null | c3_3 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 4  | null | c3_4 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 5  | null | c3_5 | AAAABIAAAAYFAAAABk5PUk1BTA== |
+----+------+------+------------------------------+

其中，_l_next_cursor_列的結果AAAABIAAAAYFAAAABk5PUk1BTA==為下一頁的遊標。

查詢第二頁，需要在WHERE條件中指定第一頁返回的遊標。樣本如下：

SELECT c1, c2, c3,  _l_next_cursor_ FROM tb_cursor_test WHERE  _l_current_cursor_ = 'AAAABIAAAAYFAAAABk5PUk1BTA==' LIMIT 5, 5;

返回結果：

+----+------+-------+------------------------------+
| c1 | c2   | c3    | _l_next_cursor_              |
+----+------+-------+------------------------------+
| 6  | null | c3_6  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 7  | null | c3_7  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 8  | null | c3_8  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 9  | null | c3_9  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 10 | null | c3_10 | AAAABIAAAAsKAAAABk5PUk1BTA== |
+----+------+-------+------------------------------+

查詢第三頁。樣本如下：

SELECT c1, c2, c3,  _l_next_cursor_ FROM tb_cursor_test WHERE  _l_current_cursor_ = 'AAAABIAAAAsKAAAABk5PUk1BTA==' LIMIT 10, 5;

返回結果：

+----+------+-------+-----------------+
| c1 | c2   | c3    | _l_next_cursor_ |
+----+------+-------+-----------------+
| 11 | null | c3_11 | null            |
| 12 | null | c3_12 | null            |
| 13 | null | c3_13 | null            |
| 14 | null | c3_14 | null            |
| 15 | null | c3_15 | null            |
+----+------+-------+-----------------+

由於僅寫入了三頁資料，即沒有第四頁資料，因此查詢第三頁返回的遊標值為空白。