划线反馈

OceanBase 数据库 V4.x 版本中 select count(*) from table_A 的统计原理

更新时间：2026-05-14 07:41

适用版本：V4.0.x、V4.1.x、V4.2.x、V4.3.x内容类型：TechNote

OceanBase 数据库 V4.x 版本对 select count(*) from table_A 执行语句做了下压优化, 从而可以减少扫描的行数，更快速地返回执行结果。但是，同样是做了下压的场景，对不同表的 coun(*) 统计，执行时间有时候会差别比较大。OceanBase 数据库 V4.x 版本对于 count(*) 查询，是如何实现的？ OceanBase 数据库 V4.x 对于 count(*) 查询，对于基线数据和增量数据没有主键交叉的部分可以利用存储 block 块上 row count 统计快速计算。没有增量数据交叉的时候是不需要实际读行数据的 row count 就是 0，有增量数据的时候就需要读行，row count 就多。

详细说明

在下面的例子中，OceanBase 数据库 V4.x 对 select count(*) 的下压，可以体现 T_FUN_COUNT(*) 下压执行在 5 号基表扫描算子中。

对于基线数据和增量数据没有主键交叉的部分可以利用存储 block 块上 row count 统计快速计算。下面的图表中，介绍了宏块的 block header 及每项存储的对应内容。其中 macro block header 中记录了对应的行数（row count）。

对于基线数据和增量数据有交叉的场景，就不能利用上宏块上的 rownum 来加速 count(*) 的计算，这种情况下，就需要去表中实际访问满足条件的数据之后，再执行 count(*)，这样的话就会慢很多。可以通过查看 sql audit，对应的 sstore_read_row_count 来粗略地判断是否利用上了宏块上的 rownum。

进一步验证的话，需要根据所查询 SQL 走的主键/索引，查看增量数据和基线数据实际情况。现在行存判断交叉的粒度是微块，增量里范围内有 1 行也会导致基线一个微块没法走快速执行。下面是一个如何查看是否有交叉的例子。

原始 SQL 如下，查询表中符合条件的 2025-05-11 的数据，该 SQL 在走了 count(*) 下压的情况下，执行耗时 4s。
```
select count(*) FROM `pay_info_merged` WHERE `gmt_create` >= '2025-05-11' and `gmt_create` < '2025-05-12';
```

获得对应表的 tablet_id。

operator@[oceanbase]>select table_id, table_name from __all_table where table_name = 'pay_info_merged';
+----------+-----------------+
| table_id | table_name      |
+----------+-----------------+
|   504616 | pay_info_merged |
+----------+-----------------+
1 row in set (0.015 sec)

因为 SQL 走的是 gmt_create 索引，需要查到索引的 tablet_id，如果是走的主表，查看主表的 tablet_id（TABLET_ID: 1152921504606850802）。

ocp_monitor@[oceanbase]>select * from CDB_OB_TABLE_LOCATIONS where data_table_id = '504616' and role ='leader' and tenant_id = '1002' and INDEX_NAME = 'gmt_create' and partition_name='p202506'\G;
*************************** 1. row ***************************
     TENANT_ID: 1002
 DATABASE_NAME: amap_car_pay_ob
    TABLE_NAME: __idx_504616_gmt_create
      TABLE_ID: 505955
    TABLE_TYPE: INDEX
 PARTITION_NAME: p202506
 SUBPARTITION_NAME: NULL
    INDEX_NAME: gmt_create
 DATA_TABLE_ID: 504616
     TABLET_ID: 1152921504606850802
         LS_ID: 1001
          ZONE: cn-zhangjiakou-a-z0
        SVR_IP: 10.101.0.76
      SVR_PORT: 2882
          ROLE: LEADER
 REPLICA_TYPE: FULL
 DUPLICATE_SCOPE: NONE
     OBJECT_ID: 505888
 TABLEGROUP_NAME: NULL
 TABLEGROUP_ID: NULL
      SHARDING: NULL
 1 row in set (0.244 sec)

 ERROR:
 No query specified

获取增量数据，对应查询 2025-05-11 的数据的存在增量数据。

ocp_monitor@[oceanbase]>select tablet_id,macro_range from __all_virtual_tablet_sstable_macro_info where tenant_id = 1002 and tablet_id = 1152921504606850802 and row_store_type = 'flat_row_store' order by macro_range;

输出结果如下：

 +---------------------+-----------------------------------------------------------------------------------+
 | tablet_id           | macro_range                                                                       |
 +---------------------+-----------------------------------------------------------------------------------+
 | 1152921504606850802 | (2025-05-06 11:27:07.629000,12392859676 ; 2025-05-10 10:01:53.013000,12470184299] |
 | 1152921504606850802 | (2025-05-06 16:06:35.667000,12418174187 ; 2025-05-10 15:59:36.955000,12442011101] |
 | 1152921504606850802 | (2025-05-10 10:01:53.013000,12470184299 ; 2025-05-12 12:02:44.421000,12471117538] |
 | 1152921504606850802 | (2025-05-10 15:59:36.955000,12442011101 ; 2025-05-12 16:41:49.887000,12489638436] |
 | 1152921504606850802 | (2025-05-12 12:02:44.421000,12471117538 ; 2025-05-13 14:59:19.657000,12475995233] |
 | 1152921504606850802 | (2025-05-12 16:41:49.887000,12489638436 ; 2025-05-13 17:22:08.144000,12500114102] |

获取基线数据，对应查询 2025-05-11 的数据的存在基线数据。

ocp_monitor@[oceanbase]>select tablet_id,macro_range from __all_virtual_tablet_sstable_macro_info where tenant_id = 1002 and tablet_id = 1152921504606850802 and row_store_type = 'encoding_row_store' order by macro_range;

输出结果如下：

 | 1152921504606850802 | (2025-05-10 23:45:42.306000,12461303121 ; 2025-05-11 00:36:33.914000,12470177100] |
 | 1152921504606850802 | (2025-05-10 23:45:42.306000,12461303121 ; 2025-05-11 00:36:33.914000,12470177100] |
 | 1152921504606850802 | (2025-05-11 00:36:33.914000,12470177100 ; 2025-05-11 01:49:45.561000,12459236861] |
 | 1152921504606850802 | (2025-05-11 00:36:33.914000,12470177100 ; 2025-05-11 01:49:45.561000,12459236861] |
 | 1152921504606850802 | (2025-05-11 01:49:45.561000,12459236861 ; 2025-05-11 03:51:39.998000,12470809799] |
 | 1152921504606850802 | (2025-05-11 01:49:45.561000,12459236861 ; 2025-05-11 03:51:39.998000,12470809799] |
 | 1152921504606850802 | (2025-05-11 03:51:39.998000,12470809799 ; 2025-05-11 06:43:00.778000,12448499013] |
 | 1152921504606850802 | (2025-05-11 03:51:39.998000,12470809799 ; 2025-05-11 06:43:00.778000,12448499013] |
 | 1152921504606850802 | (2025-05-11 06:43:00.778000,12448499013 ; 2025-05-11 07:52:53.554000,12472358205] |
 | 1152921504606850802 | (2025-05-11 06:43:00.778000,12448499013 ; 2025-05-11 07:52:53.554000,12472358205] |
 | 1152921504606850802 | (2025-05-11 07:52:53.554000,12472358205 ; 2025-05-11 08:33:05.042000,12472622109] |
 | 1152921504606850802 | (2025-05-11 07:52:53.554000,12472358205 ; 2025-05-11 08:33:05.042000,12472622109] |
 | 1152921504606850802 | (2025-05-11 08:33:05.042000,12472622109 ; 2025-05-11 09:09:41.471000,12482328920] |
 | 1152921504606850802 | (2025-05-11 08:33:05.042000,12472622109 ; 2025-05-11 09:09:41.471000,12482328920] |
 | 1152921504606850802 | (2025-05-11 09:09:41.471000,12482328920 ; 2025-05-11 09:47:21.649000,12472441715] |
 | 1152921504606850802 | (2025-05-11 09:09:41.471000,12482328920 ; 2025-05-11 09:47:21.649000,12472441715] |
 | 1152921504606850802 | (2025-05-11 09:47:21.649000,12472441715 ; 2025-05-11 10:21:40.447000,12450195703] |
 | 1152921504606850802 | (2025-05-11 09:47:21.649000,12472441715 ; 2025-05-11 10:21:40.447000,12450195703] |
 | 1152921504606850802 | (2025-05-11 10:21:40.447000,12450195703 ; 2025-05-11 10:54:54.696000,12462036344] |
 | 1152921504606850802 | (2025-05-11 10:21:40.447000,12450195703 ; 2025-05-11 10:54:54.696000,12462036344] |
 | 1152921504606850802 | (2025-05-11 10:54:54.696000,12462036344 ; 2025-05-11 11:26:50.714000,12458693766] |
 | 1152921504606850802 | (2025-05-11 10:54:54.696000,12462036344 ; 2025-05-11 11:26:50.714000,12458693766] |
 | 1152921504606850802 | (2025-05-11 11:26:50.714000,12458693766 ; 2025-05-11 11:56:00.989000,12465183907] |
 | 1152921504606850802 | (2025-05-11 11:26:50.714000,12458693766 ; 2025-05-11 11:56:00.989000,12465183907] |
 | 1152921504606850802 | (2025-05-11 11:56:00.989000,12465183907 ; 2025-05-11 12:25:49.166000,12474121647] |
 | 1152921504606850802 | (2025-05-11 11:56:00.989000,12465183907 ; 2025-05-11 12:25:49.166000,12474121647] |
 | 1152921504606850802 | (2025-05-11 12:25:49.166000,12474121647 ; 2025-05-11 12:55:27.609000,12463404074] |
 | 1152921504606850802 | (2025-05-11 12:25:49.166000,12474121647 ; 2025-05-11 12:55:27.609000,12463404074] |
 | 1152921504606850802 | (2025-05-11 12:55:27.609000,12463404074 ; 2025-05-11 13:24:42.758000,12452259840] |
 | 1152921504606850802 | (2025-05-11 12:55:27.609000,12463404074 ; 2025-05-11 13:24:42.758000,12452259840] |
 | 1152921504606850802 | (2025-05-11 13:24:42.758000,12452259840 ; 2025-05-11 13:53:12.730000,12485480126] |
 | 1152921504606850802 | (2025-05-11 13:24:42.758000,12452259840 ; 2025-05-11 13:53:12.730000,12485480126] |
 | 1152921504606850802 | (2025-05-11 13:53:12.730000,12485480126 ; 2025-05-11 14:19:56.718000,12476526274] |
 | 1152921504606850802 | (2025-05-11 13:53:12.730000,12485480126 ; 2025-05-11 14:19:56.718000,12476526274] |
 | 1152921504606850802 | (2025-05-11 14:19:56.718000,12476526274 ; 2025-05-11 14:46:53.595000,12476806756] |
 | 1152921504606850802 | (2025-05-11 14:19:56.718000,12476526274 ; 2025-05-11 14:46:53.595000,12476806756] |
 | 1152921504606850802 | (2025-05-11 14:46:53.595000,12476806756 ; 2025-05-11 15:13:56.730000,12477166278] |
 | 1152921504606850802 | (2025-05-11 14:46:53.595000,12476806756 ; 2025-05-11 15:13:56.730000,12477166278] |
 | 1152921504606850802 | (2025-05-11 15:13:56.730000,12477166278 ; 2025-05-11 15:41:07.012000,12477454139] |
 | 1152921504606850802 | (2025-05-11 15:13:56.730000,12477166278 ; 2025-05-11 15:41:07.012000,12477454139] |
 | 1152921504606850802 | (2025-05-11 15:41:07.012000,12477454139 ; 2025-05-11 16:08:09.060000,12461893882] |
 | 1152921504606850802 | (2025-05-11 15:41:07.012000,12477454139 ; 2025-05-11 16:08:09.060000,12461893882] |
 | 1152921504606850802 | (2025-05-11 16:08:09.060000,12461893882 ; 2025-05-11 16:22:59.081000,12478070537] |
 | 1152921504606850802 | (2025-05-11 16:08:09.060000,12461893882 ; 2025-05-11 16:22:59.081000,12478070537] |
 | 1152921504606850802 | (2025-05-11 16:22:59.081000,12478070537 ; 2025-05-11 16:49:47.558000,12487768740] |
 | 1152921504606850802 | (2025-05-11 16:22:59.081000,12478070537 ; 2025-05-11 16:49:47.558000,12487768740] |
 | 1152921504606850802 | (2025-05-11 16:49:47.558000,12487768740 ; 2025-05-11 17:16:09.546000,12478686633] |
 | 1152921504606850802 | (2025-05-11 16:49:47.558000,12487768740 ; 2025-05-11 17:16:09.546000,12478686633] |
 | 1152921504606850802 | (2025-05-11 17:16:09.546000,12478686633 ; 2025-05-11 17:41:58.342000,12479062881] |
 | 1152921504606850802 | (2025-05-11 17:16:09.546000,12478686633 ; 2025-05-11 17:41:58.342000,12479062881] |
 | 1152921504606850802 | (2025-05-11 17:41:58.342000,12479062881 ; 2025-05-11 18:07:39.063000,12455883964] |
 | 1152921504606850802 | (2025-05-11 17:41:58.342000,12479062881 ; 2025-05-11 18:07:39.063000,12455883964] |
 | 1152921504606850802 | (2025-05-11 18:07:39.063000,12455883964 ; 2025-05-11 18:33:52.471000,12489120273] |
 | 1152921504606850802 | (2025-05-11 18:07:39.063000,12455883964 ; 2025-05-11 18:33:52.471000,12489120273] |
 | 1152921504606850802 | (2025-05-11 18:33:52.471000,12489120273 ; 2025-05-11 19:01:19.622000,12464109018] |
 | 1152921504606850802 | (2025-05-11 18:33:52.471000,12489120273 ; 2025-05-11 19:01:19.622000,12464109018] |
 | 1152921504606850802 | (2025-05-11 19:01:19.622000,12464109018 ; 2025-05-11 19:30:51.773000,12457011902] |
 | 1152921504606850802 | (2025-05-11 19:01:19.622000,12464109018 ; 2025-05-11 19:30:51.773000,12457011902] |
 | 1152921504606850802 | (2025-05-11 19:30:51.773000,12457011902 ; 2025-05-11 20:01:31.681000,12479809941] |
 | 1152921504606850802 | (2025-05-11 19:30:51.773000,12457011902 ; 2025-05-11 20:01:31.681000,12479809941] |
 | 1152921504606850802 | (2025-05-11 20:01:31.681000,12479809941 ; 2025-05-11 20:32:58.903000,12490384316] |
 | 1152921504606850802 | (2025-05-11 20:01:31.681000,12479809941 ; 2025-05-11 20:32:58.903000,12490384316] |
 | 1152921504606850802 | (2025-05-11 20:32:58.903000,12490384316 ; 2025-05-11 21:04:05.634000,12469700475] |
 | 1152921504606850802 | (2025-05-11 20:32:58.903000,12490384316 ; 2025-05-11 21:04:05.634000,12469700475] |
 | 1152921504606850802 | (2025-05-11 21:04:05.634000,12469700475 ; 2025-05-11 21:35:46.498000,12465733905] |
 | 1152921504606850802 | (2025-05-11 21:04:05.634000,12469700475 ; 2025-05-11 21:35:46.498000,12465733905] |
 | 1152921504606850802 | (2025-05-11 21:35:46.498000,12465733905 ; 2025-05-11 22:08:57.086000,12458683634] |
 | 1152921504606850802 | (2025-05-11 21:35:46.498000,12465733905 ; 2025-05-11 22:08:57.086000,12458683634] |
 | 1152921504606850802 | (2025-05-11 22:08:57.086000,12458683634 ; 2025-05-11 22:44:56.140000,12481818433] |
 | 1152921504606850802 | (2025-05-11 22:08:57.086000,12458683634 ; 2025-05-11 22:44:56.140000,12481818433] |
 | 1152921504606850802 | (2025-05-11 22:44:56.140000,12481818433 ; 2025-05-11 23:29:38.279000,12482790421] |
 | 1152921504606850802 | (2025-05-11 22:44:56.140000,12481818433 ; 2025-05-11 23:29:38.279000,12482790421] |

适用版本

OceanBase 数据库 V4.x 版本。

如何排查常量约束导致的参数不匹配，计划不命中的问题

如何在行列混存的场景下，指定 Hint 走上预期的计划

OceanBase 数据库