機能の適用範囲
この内容は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。
|
| 適用モード | 即時に反映 |
| レベル | テナントレベル |
例：
テナントレベルのページネーションの順序保持機能を有効にするには、次のコマンドを使用できます：
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;