Optimize pagination scenarios
For scenarios where the query result set in the database is too large, it is recommended to use pagination queries.
Paginate by unique key
Example: You can paginate by unique key when performing a pagination query.
obclient> select * from tb where c1 = 'xxx' and c2 = 'xxx' and id > 'xxx' limit 100;
Retain the last ID of every 100 rows as the condition value for the next 100 rows (create a composite index on c1, c2, and id).
Note
- Applicable when data distribution is relatively even. If querying 100 rows per page costs scanning tens of thousands or hundreds of thousands of rows, performance will not meet expectations.
- Applicable for scenarios where pagination is performed solely based on id to fetch the entire table without other conditions, such as
select * from tb where id > '' limit 100;.
Paginate using delayed joins or subqueries
OceanBase AI Database does not skip the specified number of rows (offset) but fetches rows starting from offset + N and then returns the first N rows, discarding the remaining rows. Therefore, efficiency degrades significantly when the offset is very large. When encountering such scenarios in your application, consider controlling the total number of pages returned or rewriting SQL statements for page counts exceeding a specific threshold.
Control the total number of pages returned.
When implementing pagination logic in code, if the count is 0, return directly to avoid executing the subsequent pagination statement.
Counterexample: In the "Service Market" application, a transaction could exceed 1,000 pages when paginated. When a user clicked the last page, the database would almost become paralyzed.
Rewrite SQL for page counts exceeding a specific threshold.
Example: First, quickly locate the range of IDs that need to be fetched, then perform the join:
obclient> select a.name from a,(select id from a where id LIMIT 100000,20) b where a.id=b.id;
Case analysis
The table creation statement is as follows:
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 code',
`province_name` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT 'Zhejiang Province' COMMENT 'Province name',
`area_code` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT 'City code',
`area_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'City name',
`region_code` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT 'District/county code',
`region_name` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT 'District/County name',
`dept_code` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT 'Department code',
`dept_name` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT 'Department name',
`catalog_id` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Catalog ID',
`catalog_name` varchar(500) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Directory name',
`catalog_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Basic code of the catalog',
`business_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Line code',
`business_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'Line name',
`received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of recipients',
`app_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received by the app',
`pc_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received by the PC',
`hall_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of items received in the hall',
`window_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received in the dedicated window',
`two_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of recipients in version 2.0',
`two_app_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received by the client of version 2.0',
`two_pc_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of 2.0 PC recipients',
`two_hall_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received in Hall 2.0',
`two_window_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received in the dedicated window of version 2.0',
`one_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received at version 1.0',
`one_app_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of messages received by the application',
`one_pc_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of 1.0 PC recipients',
`one_hall_received_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of items received in Hall 1.0',
`item_flag` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT 'Whether it is a 2.0 item (0-No, 1-Yes)',
`stat_date` datetime NOT NULL COMMENT 'Statistics time (the time when the data was generated, usually the previous day)',
`delete_flag` int(1) NOT NULL DEFAULT '0' COMMENT 'Whether deleted (0-not deleted, 1-deleted)',
`gmt_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
`gmt_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
`not_case_assigned_code_num` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Number of codes not collected',
`item_type_code` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT 'Type ID of the power item',
`item_dock_type` varchar(10) CHARACTER SET utf8 NOT NULL COMMENT 'Item docking method (0: all; 1: overall docking; 2: 2.0 form docking)',
`apply_person_type` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT 'The type of the applicant. Valid values: 0 (all), 1 (individual), and 2 (legal entity)',
`two_not_case_assigned_code_num` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Number of codes not assigned in version 2.0',
`one_not_case_assigned_code_num` bigint(20) NOT NULL DEFAULT '0' COMMENT '1.0 Number of codes not assigned',
`self_service_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of self-service transactions',
`two_self_service_num` bigint(11) NOT NULL DEFAULT '0' COMMENT 'Number of self-service transactions processed in version 2.0',
`one_self_service_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '1.0 Number of self-service transactions',
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='Business Dashboard > Case Management > Case Effectiveness > Recipients > Directory Distribution';
The original query SQL is as follows:
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;
The data volume in this table is 5 million records.
By modifying the offset offset, the query times obtained are as follows:
offset |
0 |
100 |
300 |
500 |
1000 |
2000 |
|---|---|---|---|---|---|---|
| Execution Time (s) | 0.07 | 3.96 | 6.83 | 8.67 | 14.44 | 18.04 |
Since this table has no indexes, adding indexes to the filter conditions did not significantly improve execution time. In this case, consider enabling parallel execution by optimizing the SQL statement as follows:
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;
At this point, when the offset is executed again, the return time is around 5 seconds, achieving SQL optimization.
Notice
After analyzing this case, the following findings were made:
- In an OceanBase cluster, as the offset increases, the query response time also increases.
- When setting the number of parallel threads, testing shows that the optimal value is typically equal to the number of partitions in the table.
Preserve order in pagination
Paginated order preservation ensures the stability of query pagination results. Specifically, it maintains a consistent order by first sorting the query results and then paginating them.
Switch for Preserving Order in Paging
OceanBase AI Database provides two methods to control whether a query uses the order-preserving pagination feature: the hidden parameter _preserve_order_forPagination and the query hint PRESERVE_ORDER_FOR_PAGINATION.
For the above two control methods, OceanBase AI Database prefers to use hints. For example, if a tenant has the hidden parameter set to enabled and the query uses /*+OPT_PARAM('PRESERVE_ORDER_FOR_PAGINATION', 'FALSE')*/, the query will not enable the preserve-order feature for pagination.
Hide the parameter to control the pagination order-preserving feature
The _preserve_order_for_pagination hidden parameter is set at the tenant level and controls whether to enable or disable order preservation for pagination queries. The default behavior is disabled. When this parameter is enabled, the optimizer will actively add an ORDER BY clause to pagination queries to ensure that the data is output in order.
Attribute |
Description |
|---|---|
| Parameter type | Boolean |
| Default Value | False indicates that the pagination-preserving order feature is disabled by default. |
| Value range | False or True.
|
| Effective Mode | Effective Immediately |
| Level | Tenant-level |
Example:
To enable the ordered paging feature at the tenant level, use the following command:
ALTER SYSTEM SET _preserve_order_for_pagination = true;
Query hint to control the order-preserving pagination feature
To provide more fine-grained control, OceanBase AI Database also supports the query-level hint PRESERVE_ORDER_FOR_PAGINATION.
Example:
If you need to enable the ordered paging feature for a specific query.
SELECT /*+OPT_PARAM('PRESERVE_ORDER_FOR_PAGINATION', 'TRUE')*/ * FROM test_tbl1
LIMIT 10;
Notice
This hint cannot be used to control whether the pagination-preserving order feature is enabled for a specific query block. It can only be used to control whether this feature is enabled for the entire query.
Paginated order preservation feature
After the order-preserving pagination feature is enabled, OceanBase AI Database will rewrite the query by adding an ORDER BY clause to ensure order preservation during pagination.
For example,
SELECT t1.c1, t1.c2 FROM t1, t2 WHERE t1.c1 = t2.c1 LIMIT 10,10;
For the query above, OceanBase AI Database first sorts the query results and then paginates the sorted results. The rewritten query is as follows:
SELECT t1.c1, t1.c2 FROM t1, t2 WHERE t1.c1 = t2.c1
ORDER BY t1.c1, t2.c2 LIMIT 10,10;
Notice
After enabling the Preserve Order feature for pagination, some queries may experience performance degradation due to the additional sorting computation introduced, which can reduce query execution efficiency.
Considerations on enabling the order-preserving pagination feature
After enabling the Preserve Order feature for pagination, the query output may differ from before because a sorting field has been added.
For other scenarios, the expected order of results may change. For example:
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;
The user might expect this query to sort the results by table t1 before paginating them. However, no SQL semantics guarantee that the data must be sorted according to the results of table t1 before pagination. OceanBase AI Database adds the sorting fields t2.c1 and t2.c2 in the outermost layer. The execution result is sorted by the output fields (i.e., the fields of table t2) before pagination. The equivalent query is as follows:
SELECT t2.c1, t2.c2
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1
ORDER BY t2.c1, t2.c2
LIMIT 10,10;