优化分页场景

对于数据库中查询结果过多的场景，建议进行分页查询。

根据唯一键分页

示例：在进行分页查询时可以根据唯一键进行分页。

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
执行时间（s）	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

此时重新执行上述偏移量，返回时间均在 5S 左右，实现了 SQL 优化。

注意

该案例分析后发现，以下内容：

在 OB 集群中，随着 offset 偏移量的增加，查询返回时间会随之增加。

在设置并行数的时候，经测试，最佳并行数为该表的分片数。OB 是系统自动分片的，无需开发接入，因此设置并行时，需要先查询该表的分片数。

ITPub博客

【手摸手玩转 OceanBase 143】分页查询

优化分页场景

根据唯一键分页

利用延迟关联或子查询分页

案例分析

最新文章