--- title: OceanBase 数据库 V4.x 版本中 select count(*) from table_A 的统计原理-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 OceanBase 数据库 V4.x 版本中 select count(*) from table_A 的统计原理相关的常见问题和使用技巧，帮助您快速解决 OceanBase 数据库 V4.x 版本中 select count(*) from table_A 的统计原理的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 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 号基表扫描算子中。 ![image01](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/performance/20250523ob4x-statistical-principle-select-count-from-table01.png) 对于基线数据和增量数据没有主键交叉的部分可以利用存储 block 块上 row count 统计快速计算。下面的图表中，介绍了宏块的 block header 及每项存储的对应内容。 其中 macro block header 中记录了对应的行数（row count）。 ![image02](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/performance/20250523ob4x-statistical-principle-select-count-from-table02.png) 对于基线数据和增量数据有交叉的场景，就不能利用上宏块上的 rownum 来加速 `count(*)` 的计算，这种情况下，就需要去表中实际访问满足条件的数据之后，再执行 `count(*)`，这样的话就会慢很多。 可以通过查看 sql audit，对应的 `sstore_read_row_count` 来粗略地判断是否利用上了宏块上的 rownum。 ![image03](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/performance/20250523ob4x-statistical-principle-select-count-from-table03.png) 进一步验证的话，需要根据所查询 SQL 走的主键/索引，查看增量数据和基线数据实际情况。现在行存判断交叉的粒度是微块，增量里范围内有 1 行也会导致基线一个微块没法走快速执行。 下面是一个如何查看是否有交叉的例子。 1. 原始 SQL 如下，查询表中符合条件的 `2025-05-11` 的数据，该 SQL 在走了 `count(*)` 下压的情况下，执行耗时 4s。 ```shell select count(*) FROM `pay_info_merged` WHERE `gmt_create` >= '2025-05-11' and `gmt_create` < '2025-05-12'; ``` 2. 获得对应表的 tablet_id。 ```shell 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) ``` 3. 因为 SQL 走的是 gmt_create 索引，需要查到索引的 tablet_id，如果是走的主表，查看主表的 tablet_id（TABLET_ID: 1152921504606850802）。 ```shell 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 ``` 4. 获取增量数据，对应查询 `2025-05-11` 的数据的存在增量数据。 ```shell 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; ``` 输出结果如下： ```shell +---------------------+-----------------------------------------------------------------------------------+ | 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] | ``` 5. 获取基线数据，对应查询 `2025-05-11` 的数据的存在基线数据。 ```shell 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; ``` 输出结果如下： ```shell | 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 版本。 上一篇 [如何排查常量约束导致的参数不匹配，计划不命中的问题](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005438095) 下一篇 [如何在行列混存的场景下，指定 Hint 走上预期的计划](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005412746) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线