ページネーションクエリ|V4.3.5|OceanBaseデータベース|OceanBaseデータベースドキュメント|分散データベース

適用対象

この内容はOceanBaseデータベースのMySQLモードにのみ適用されます。Oracleモードのページネーションクエリに関する情報については、SIMPLE SELECTを参照してください。

ページネーションシナリオの最適化

データベース内のクエリ結果が多すぎる場合は、ページネーションクエリを実行することを推奨します。

一意キーによるページネーション

例：ページネーションクエリを実行する際には、一意キーに基づいてページングできます。

obclient> select * from tb where c1 = 'xxx' and c2 = 'xxx' and id > 'xxx' limit 100;

100件ごとの最後のIDを次の100件の条件値として保持します（c1、c2、idには複合インデックスを作成）。

注意

データの分布が均等な場合に適用されます。1ページ100件のデータを照会するコストは数万または数十万件をスキャンするコストと同じであり、パフォーマンスは理想的ではありません。
他の条件を追加せず、IDに基づいてテーブル全体を取得する select * from tb where id > '' limit 100; のシナリオに適用されます。

遅延結合またはサブクエリによるページネーション

OBはoffset行をスキップするのではなく、offset+N行を取得し、そのうち最初のoffset行を諦めてN行を返します。そのため、offsetが特に大きい場合、効率は非常に低くなります。業務上このようなシナリオに遭遇した場合は、返す総ページ数を制御するか、特定のしきい値を超えるページ数についてSQLを書き換えることを検討できます。

返す総ページ数を制御します。

コード内でページネーションクエリロジックを記述する際、countが0の場合は直接返却し、後続のページネーションステートメントの実行を避けるべきです。

反例："サービスマーケット"のある取引のページが1000ページを超える場合、ユーザーが最終ページをクリックすると、データベースはほぼ機能停止状態になります。
特定のしきい値を超えるページ数について書き換えます。

例：まず取得するID範囲を迅速に特定し、その後結合します：
```
obclient> select a.name from a,(select id from a where id LIMIT 100000,20) b where a.id=b.id;
```

ケーススタディ

テーブル作成ステートメントは以下のとおりです：

obclient> CREATE TABLE `business_case_analysis_effect_receive_catalog_details` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `province_code` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '330000' COMMENT '省コード',
  `province_name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '浙江省' COMMENT '省名',
  `area_code` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '地市コード',
  `area_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '地市名',
  `region_code` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '区県コード',
  `region_name` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '区県名',
  `dept_code` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '部門コード',
  `dept_name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '部門名',
  `catalog_id` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'カタログコード',
  `catalog_name` varchar(500) CHARACTER SET utf8 DEFAULT NULL COMMENT 'カタログ名',
  `catalog_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'カタログ基本コード',
  `business_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '項目コード',
  `business_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '項目名',
  `received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '受信数',
  `app_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'APP受信数',
  `pc_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'PC受信数',
  `hall_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'ホール受信数',
  `window_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '専用窓口受信数',
  `two_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '2.0受信数',
  `two_app_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '2.0APP受信数',
  `two_pc_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '2.0PC受信数',
  `two_hall_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '2.0ホール受信数',
  `two_window_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '2.0専用窓口受信数',
  `one_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '1.0受信数',
  `one_app_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '1.0APP受信数',
  `one_pc_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '1.0PC受信数',
  `one_hall_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '1.0ホール受信数',
  `item_flag` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT '2.0事項か（0-いいえ、1-はい）',
  `stat_date` datetime NOT NULL COMMENT '統計時間（データ生成時間、通常は前日）',
  `delete_flag` int(1) NOT NULL DEFAULT '0' COMMENT '削除されているか（0-未削除、1-削除済み）',
  `gmt_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時間',
  `gmt_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `not_case_assigned_code_num` bigint(20) NOT NULL DEFAULT '0' COMMENT 'コード割り当て未集計数',
  `item_type_code` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '権限事項タイプID',
  `item_dock_type` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT '事項対応方式（0-すべて、1-全体対応、2-2.0フォーム対応）',
  `apply_person_type` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '申請者タイプ(0-すべて、1-個人、2-法人)',
  `two_not_case_assigned_code_num` bigint(20) NOT NULL DEFAULT '0' COMMENT '2.0コード割り当て未集計数',
  `one_not_case_assigned_code_num` bigint(20) NOT NULL DEFAULT '0' COMMENT '1.0コード割り当て未集計数',
  `self_service_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'セルフサービス機件数',
  `two_self_service_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '2.0セルフサービス機件数',
  `one_self_service_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '1.0セルフサービス機件数',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `query_catalog_index` (`catalog_id`,`catalog_code`,`stat_date`,`area_code`,`region_code`,`dept_code`,`business_code`,`business_name`) USING BTREE GLOBAL,
  KEY `idx_area_region` (`area_code`,`region_code`,`item_flag`,`item_type_code`) USING BTREE GLOBAL,
  KEY `idx_statDate` (`stat_date`,`area_code`,`region_code`) USING BTREE GLOBAL,
  KEY `idx_catalog_code` (`catalog_code`,`catalog_id`) USING BTREE GLOBAL,
  KEY `idx_item_type_code` (`item_type_code`) USING BTREE GLOBAL,
  KEY `idx_catalog_business` (`catalog_code`,`catalog_id`,`business_code`) USING BTREE GLOBAL
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4  COMMENT='業務ダッシュボード-件数-件数効果-受信-カタログ分布';

元のクエリSQLは以下のとおりです：

SELECT
  catalog_id AS catalogId,
  catalog_name AS catalogName,
  catalog_code AS catalogCode,
  business_code AS businessCode,
  business_name AS businessName,
  IFNULL(
    CAST(
      SUM(pc_received_num) / SUM(received_num) AS DECIMAL(14, 4)
    ),
    0
  ) AS networkHandlePercent,
  IFNULL(
    CAST(
      SUM(app_received_num) / SUM(received_num) AS DECIMAL(14, 4)
    ),
    0
  ) AS palmtopHandlePercent,
  IFNULL(
    CAST(
      SUM(two_received_num) /(
        SUM(received_num) + SUM(not_case_assigned_code_num)
      ) AS DECIMAL(14, 4)
    ),
    0
  ) AS netHandleRate,
  IFNULL(SUM(not_case_assigned_code_num), 0) AS notCaseAssignedCodeNum,
  IFNULL(SUM(received_num), 0) AS receivedNum,
  IFNULL(SUM(two_received_num), 0) AS twoReceivedNum,
  IFNULL(SUM(pc_received_num), 0) AS pcReceivedNum,
  IFNULL(SUM(app_received_num), 0) AS appReceivedNum,
  IFNULL(SUM(hall_received_num), 0) AS hallReceivedNum,
  IFNULL(SUM(two_window_received_num), 0) AS windowReceivedNum,
  IFNULL(SUM(self_service_num), 0) AS selfServiceNum
FROM
  business_case_analysis_effect_receive_catalog_details
WHERE
  stat_date <= 'xxxx-xx-xx'
  AND stat_date >= 'xxxx-xx-xx'
  AND item_type_code in ("xx", "xx", "xx", "xx", "xx", "xx", "xx")
GROUP BY
  catalog_code,
  catalog_id
LIMIT
  offset, 15;

このテーブルのデータ量は500万です。

オフセット量offsetを変更することで、得られるクエリ時間はそれぞれ以下のとおりです：

offsetオフセット量	0	100	300	500	1000	2000
実行時間（秒）	0.07	3.96	6.83	8.67	14.44	18.04

このテーブルにはインデックスがないため、フィルター条件にインデックスを追加した後も実行時間の変化は大きくありません。この場合、パラレル実行を検討し、SQLステートメントの最適化は以下のとおりです：

SELECT
  /*+ PARALLEL(5),USE_HASH_AGGREGATION*/
  catalog_id AS catalogId,
  catalog_name AS catalogName,
  catalog_code AS catalogCode,
  business_code AS businessCode,
  business_name AS businessName,
  IFNULL(
    CAST(
      SUM(pc_received_num) / SUM(received_num) AS DECIMAL(14, 4)
    ),
    0
  ) AS networkHandlePercent,
  IFNULL(
    CAST(
      SUM(app_received_num) / SUM(received_num) AS DECIMAL(14, 4)
    ),
    0
  ) AS palmtopHandlePercent,
  IFNULL(
    CAST(
      SUM(two_received_num) /(
        SUM(received_num) + SUM(not_case_assigned_code_num)
      ) AS DECIMAL(14, 4)
    ),
    0
  ) AS netHandleRate,
  IFNULL(SUM(not_case_assigned_code_num), 0) AS notCaseAssignedCodeNum,
  IFNULL(SUM(received_num), 0) AS receivedNum,
  IFNULL(SUM(two_received_num), 0) AS twoReceivedNum,
  IFNULL(SUM(pc_received_num), 0) AS pcReceivedNum,
  IFNULL(SUM(app_received_num), 0) AS appReceivedNum,
  IFNULL(SUM(hall_received_num), 0) AS hallReceivedNum,
  IFNULL(SUM(two_window_received_num), 0) AS windowReceivedNum,
  IFNULL(SUM(self_service_num), 0) AS selfServiceNum
FROM
  business_case_analysis_effect_receive_catalog_details
WHERE
  stat_date <= 'xxxx-xx-xx'
  AND stat_date >= 'xxxx-xx-xx'
  AND item_type_code in ("xx", "xx", "xx", "xx", "xx", "xx", "xx")
GROUP BY
  catalog_code,
  catalog_id
LIMIT
  offset, 15;

この時点で上記のオフセット量を再実行すると、返却時間はすべて約5秒であり、SQLの最適化が実現されました。

注意

このケーススタディの結果、以下の内容が明らかになりました：

OBクラスタでは、offsetオフセット量が増加するにつれて、クエリの返却時間も増加します。
並列数を設定する際、テストの結果、最適な並列数はこのテーブルのパーティション数であることがわかりました。

ページネーションによる順序の維持

ページネーションによる順序の維持とは、クエリ結果のページネーション処理後も順序が安定していることを保証するための仕組みです。具体的には、クエリ結果をソートした上でページネーション処理を行うことで、結果の順序の一貫性を確保します。

ページネーションの順序保持機能のスイッチ

OceanBaseデータベースのMySQLモードでは、隠れた構成パラメータ_preserve_order_for_paginationとクエリヒントPRESERVE_ORDER_FOR_PAGINATIONの2つの方法で、クエリにページネーションの順序保持機能を使用するかどうかを制御できます。

上記の2つの制御方法について、OceanBaseデータベースはヒントを優先的に使用します。例えば、テナントが隠れた構成パラメータを有効に設定している場合でも、クエリ内で/*+OPT_PARAM('PRESERVE_ORDER_FOR_PAGINATION', 'FALSE')*/を使用すると、そのクエリではページネーションの順序保持機能は有効になりません。

隠れた構成パラメータによるページネーションの順序保持機能の制御

_preserve_order_for_paginationはテナントレベルの隠れた構成パラメータであり、テナントのページネーションの順序保持機能を有効または無効にすることができます。現在のデフォルト動作は無効です。この構成パラメータを有効にすると、オプティマイザーはページネーションクエリに対して自動的にORDER BYを追加し、ページネーションクエリのデータが順序付けられて出力されるようにします。

プロパティ	説明
パラメータタイプ	ブール型
デフォルト値	False。デフォルトでは、ページネーションの順序保持機能は無効です。
値の範囲	FalseまたはTrue。 True：ページネーションの順序保持機能を有効にします。 False：ページネーションの順序保持機能を無効にします。
発効モード	即時発効
レベル	テナントレベル

例：

テナントレベルのページネーションの順序保持機能を有効にするには、次のコマンドを使用できます：

ALTER SYSTEM SET _preserve_order_for_pagination = true;

クエリヒントによるページネーションの順序保持機能の制御

より細かい粒度での制御手段を提供するために、OceanBaseデータベースはクエリレベルの制御方法としてヒントPRESERVE_ORDER_FOR_PAGINATIONも提供しています。

例：

特定のクエリに対してページネーションの順序保持機能を有効にする必要がある場合。

SELECT /*+OPT_PARAM('PRESERVE_ORDER_FOR_PAGINATION', 'TRUE')*/ * FROM test_tbl1
LIMIT 10;

注意

このヒントは特定のクエリブロックに対してページネーションの順序保持機能を有効にするかどうかを制御するためには使用できず、クエリ全体に対してページネーションの順序保持機能を有効にするかどうかを制御するためにのみ使用できます。

ページネーションの順序保持機能

ページネーションの順序保持機能を有効にすると、OceanBaseデータベースはそのクエリを書き換えてORDER BY句を追加し、ページネーションの順序を保証します。

例えば、

SELECT t1.c1, t1.c2 FROM t1, t2 WHERE t1.c1 = t2.c1 LIMIT 10,10;

上記のようなクエリに対して、OceanBaseデータベースはまずクエリ結果をソートし、その後ソートされた結果をページ処理します。書き換え後のクエリは以下のとおりです：

SELECT t1.c1, t1.c2 FROM t1, t2 WHERE t1.c1 = t2.c1
ORDER BY t1.c1, t2.c2 LIMIT 10,10;

注意

ページネーションの順序保持機能を有効にすると、追加のソート計算が導入されるため、一部のクエリでパフォーマンスの低下が発生する可能性があります。

ページネーションの順序保持機能を有効にする際の注意点

ページネーションの順序保持機能を有効にすると、ソートフィールドが追加されるため、クエリの出力結果は以前と異なる場合があります。

他のシナリオでは、期待される結果の順序が変更される可能性があります。例えば：

SELECT t2.c1, t2.c2
FROM (SELECT * from t1 ORDER BY t1.c1, t1.c2) V
  LEFT JOIN t2 ON V.c1 = t2.c1
LIMIT 10,10;

このクエリでは、ユーザーはt1テーブルでソートした後にページネーション結果を取得することを期待している可能性がありますが、実際には任意のSQLセマンティクスでは、クエリデータが必ずしもt1テーブルの結果に従ってソートされた後にページネーションされるとは限りません。OceanBaseデータベースは最外層にソートフィールドt2.c1、t2.c2を追加し、実行結果は出力結果（つまりt2テーブルのフィールド）に従ってソートされた後にページネーションされます。これに相当するクエリは以下のとおりです：

SELECT t2.c1, t2.c2
FROM t1
  LEFT JOIN t2 ON t1.c1 = t2.c1
ORDER BY t2.c1, t2.c2
LIMIT 10,10;