CnosDB 2.3中7000万行数据count(*)耗时超1000秒如何解决？

针对7700万行数据执行select count(*) from readings;耗时超18分钟的问题，可从以下几个方向优化：

1. 创建物化视图实现预聚合 #

CnosDB支持物化视图，可提前预计算全表count值，后续查询直接读取预计算结果，彻底避免全表扫描：

• 创建物化视图语句示例：

  CREATE MATERIALIZED VIEW mv_readings_count
  AS SELECT count(*) AS total_rows
  FROM readings
  WITH (refresh_interval = '1h'); -- 按业务需求设置刷新间隔，比如1小时
  

• 查询时直接访问物化视图：

  SELECT total_rows FROM mv_readings_count;
  

如果需要实时精确值，可将刷新间隔设为更小（如'5m'），或手动触发刷新：REFRESH MATERIALIZED VIEW mv_readings_count;

2. 优化表的分区与存储策略 #

• 检查并调整分区键：时序数据库的核心优化是按时间分区，若readings表未配置时间分区，需重新建表并指定分区策略，示例：

  CREATE TABLE readings (
    time TIMESTAMP,
    -- 其他字段定义
  ) PARTITION BY TIME (INTERVAL '1 day'); -- 按天分区，可根据数据量调整为小时/周
  

  已有数据可通过数据迁移工具导入新分区表。
• 确认数据压缩配置：CnosDB默认开启LZ4压缩，可检查是否启用并确保压缩率正常，减少磁盘IO开销。

3. 优化查询语句 #

• 添加时间范围过滤：若业务不需要全历史数据的count，务必加上时间条件，缩小扫描范围：

  SELECT count(*) FROM readings WHERE time >= '2024-01-01 00:00:00' AND time <= '2024-06-01 00:00:00';
  

• 尝试并行分段统计：手动将时间范围拆分为多个子区间，并行查询后求和，利用多核CPU提升效率：

  SELECT sum(cnt) FROM (
    SELECT count(*) AS cnt FROM readings WHERE time >= '2024-01-01' AND time < '2024-02-01'
    UNION ALL
    SELECT count(*) AS cnt FROM readings WHERE time >= '2024-02-01' AND time < '2024-03-01'
    -- 继续拆分剩余时间区间
  ) t;
  

4. 调整数据库配置参数 #

修改CnosDB配置文件（如cnosdb.conf）后重启生效：

• 开启并行查询：

  parallel_query_enabled = true
  parallel_query_max_threads = 8 -- 根据CPU核心数设置，比如核心数的1-2倍
  

• 提升查询内存上限：

  query_max_memory = 16GB -- 根据服务器内存调整，比如总内存的1/4到1/2
  

• 优化缓存配置：

  data_cache_enabled = true
  metadata_cache_enabled = true
  

5. 硬件资源升级 #

• 若服务器使用机械硬盘（HDD），更换为固态硬盘（SSD），全表扫描的IO性能可提升数倍。
• 增加CPU核心数，并行查询和数据扫描能充分利用多核资源。
• 提升服务器内存容量，让更多数据块缓存到内存，减少磁盘读取次数。

内容的提问来源于stack exchange，提问作者learn_more