求各位大佬给些报表优化的思路?

[sunmoon1983]

现在客户要求这么一张报表 横向为年龄 竖向为项目名称 有各种条件每次客户进入页面都要点击一下开始分析，报表生成起来很慢，现在要求优化，加上了缓存，但是第一次生成报表的时候还是需要等待，想用计划任务跑一下，但是想到了各种搜索条件组合起来有很多情况，没有实施，求优化思路！

当前表结构

CREATE TABLE `xx_table1` (
  `id` bigint NOT NULL,
  `code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '编号',
  `full_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '姓名',
  `gender` tinyint NOT NULL DEFAULT '0' COMMENT '性别',
  `age` smallint DEFAULT '0' COMMENT '年龄',
  `idcard` varchar(33) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '身份证号',
  `resident_address` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '常驻地址',
  `resident_address_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '常驻地址行政编码',
  `registered_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '户籍地址',
  `registered_address_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '户籍地址行政编码',
  `birthday` date DEFAULT '0000-00-00' COMMENT '生日',
  `dead_date` datetime DEFAULT NULL COMMENT '死亡日期',
  `dead_place_code` bigint NOT NULL DEFAULT '0' COMMENT '死亡地点',
  `family_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '家属姓名',
  `family_tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '家属联系电话',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `is_audit` tinyint NOT NULL DEFAULT '1' COMMENT '是否审核',
  `audit_by` bigint DEFAULT NULL COMMENT '审核人',
  `audit_at` datetime DEFAULT NULL COMMENT '审核时间',
  `year` int NOT NULL DEFAULT '0' COMMENT '年份',
  `item_id` int NOT NULL DEFAULT '0' COMMENT '项目 id',
  `area_code` bigint DEFAULT '0' COMMENT '地区代码',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `p_fi_idx` (`full_name`,`idcard`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='XX 表';

索引还没加

数据量大于 100W!

想到了把数据跑成单表，但是没做过，不知道要怎么写，不知道单表要怎么建，求大老指点

[Chad0000]

如果是我的话，我就让客户提前建立报表（即定义每个报表的条件），然后这个数据改变后，定时后台重新统计写入报表。这样客户再进来读的是已经统计好的表，虽然有一点延迟，但考虑到这个量级的数据，有延迟并不影响。

[Chad0000]

但考虑到非必要勿增实体，能加资源解决的事情就不要搞得太麻烦。先加索引试试呗。

[agui2200]

上 clickhouse

[sunmoon1983]

@Chad0000 现在的想法就是，先把各个条件组合一下，然后用定时任务去跑

[sunmoon1983]

@agui2200 没有接触过，不太敢上呀

[BugCry]

每个指标单独成表，最后写个 view 拼到一起

[c6h6benzene]

一般的报表工具的话，可能都是先把底层的数据全部缓存之后（ dataset ）然后再以这个为基础去做筛选之类。如果筛选条件不固定的话，大概只能缓存整个表？

[sunmoon1983]

@c6h6benzene 现在的方案就是先把报表生成出来，然后再把整个报表缓存，但是生成的过程有点慢，客户无法忍受

[Chad0000]

@sunmoon1983 #4
我之前优化的一种方案是将允许统计的条件提前算出来，形成中间表。比如：
主键：[性别]_[出生年月]_[省市]_[死亡年月]，再加一个数量字段。这样所有查询都可以在这个中间表进行，你再 Sum 一下。但这样做还是改逻辑，所以不如本地对比一下加索引后的影响，如果现有数据库资源可承受，就没必要改代码了。

[yasea]

这种就只有用空间换时间，每天、每小时跑批， 根据所有需要使用的的维度（过滤条件）分组将相应指标定时写入报表即可。如果数据量大， 可以再做索引、分区存储、分表、缓存等优化。

[liprais]

除了 mysql 一百万数据都不是啥问题

[colvv]

换 clickhouse

[sunmoon1983]

@colvv 没有接触过，不太敢换呀

[sunmoon1983]

@yasea 嗯，现在想的就是要空间换时间，但是我一直没想明白，要怎么跑数据，是要把我每种条件都组合一次，跑一次吗？比如，上面图中的条件有户籍，性别，年份，那个，我就要`户籍+男+2022`跑一次，下面再用`户籍+女+2022`跑一次，然后`户籍+性别（男、女）+每一年（从今年到 2010 年）`都跑一次存到库中，是这样吗？后面还要加上地区，好多条件呀，不敢想象

[wxf666]

@sunmoon1983 你查询的 SQL 是怎么写的呢？

[agui2200]

@sunmoon1983 ck 是最佳选择，多条件，而且支持亿级查询，而且轻量运维小，统计上比 mysql 强多了，你可以 写个 定时任务把所有表拍平扔 ck ，如果担心用不好，就做好回退，当 ck 不可用的时候用 mysql 强查就是了

[jones2000]

数据放入内存，自己统计计算不就可以。 才几百万的数据， 都是数值计算， 也不涉及到字符串检索。

[wxf666]

@yasea @agui2200 能不能抽出可能用到的字段，建个小小的覆盖索引呢？

比如，（行政编码, 性别, 年份, 年龄, 项目 ID ），看起来一行只需 5+10+1+2+1+4+8 = 31 字节，100W 行索引只需 30 MB

如果不指定『当前地区』，扫全部索引也能满足各种查询需求。
如果指定了『当前地区』，可能只需扫几百上千行，几百 KB 的索引即可统计出来。

[wxf666]

@jones2000 我也感觉是，就算一行需要 1KB ，全部放进内存也才不到 1GB ，for 一百万次实际就连 Python 都不会很慢

如果只挑可能用到的字段出来，一百万行可能也只用到几十 MB 内存即可

[14104chk]

@sunmoon1983 可以只用一个 select * from table ，然后用代码生成粒度比较细的统计数据存在缓存或者内存表里面，例如分成户籍、性别、年份。如果只查 2010 年的数据，没有户籍性别限制，就把 2010 年的各种户籍或者性别的数据加起来

[dode]

这是疫情的系统啊😯

[nandaye]

首先看你指标是可加指标还是不可加指标，不可加基本只能加资源缓存之类的方案；可加指标就按筛选条件最小粒度聚合一次，查询时用筛选条件基于聚合数据做二次聚合

[git00ll]

一百万而已，后期预期会增加到多少。 一百万的话遍历一遍在代码里也把报表弄出来了

[c6h6benzene]

@sunmoon1983 其实我想说的是你后端先把最小粒度的全部数据缓存起来，把汇总之类的维度计算全部放在前端完成。

报表的前端呈现是自己的写的吗，还是要利用什么其他现有的报表工具？

另外微软那一套 BI 架构的话，有个东西叫 Cube （多维数据集），就是用来处理这种会有多个维度数据需求的应用的。

[sunmoon1983]

@c6h6benzene 前端自己写的，`最小粒度的全部数据` 这个要怎么解释？现在就是这里不太明白，第一次做报表，不太懂

[jiangwentx]

1 楼说的的确是个思路，但客户想要看各种组合，显然穷举很慢很累，而且一直跟着屁股后面改代码不现实，elasticsearch 聚合出所有情况，至于客户要看完整报表，那就用定时程序查 es 的各种情况总量写入表中，然后只要查表供前端展示即可。（没人力客户没预算当我没说）

[yufeng0681]

100 万数据 想办法变成 1W 级别的数据（临时表方案） ，基于 1W 数据做查询就快了。
做好同步操作， 看甲方的要求，按天、按周同步一次； 也支持手动同步。

[jones2000]

@Chad0000 就这几百 W 的数据量， 买一台 20W 的物理机， 直接装 mysql ， 用到的数据索引建完基本就够了。要什么优化，加配置就够了。

[makdon]

之前做过类似的需求，用的 apache druid ，非常适合这种场景

[redbridge]

如果用户查询组合不复杂，而且重复性高的话，可以在用户每次调用时候把查询条件自动记下来，自动生成定时任务。当然如果用户查询情况太多了就不太适合。

[yasea]

@sunmoon1983
select
gender
,age
,year
,sum(xxx)xxx
,count(xxx)xxx
from xx_table1
group by gender,age,year
这个是一个底层的汇总表， 可以直接放到内存进行查询统计，如果这个表数据量太大，可以再做切割，然后在前端做查询缓存，在生成上面这个汇总的表时候，也可以直接根据可能的查询条件将把数据生成到前端进行缓存。

[xuanbg]

把完整的透视数据跑出来存起来，然后无非就是条件查询而已。

[NizumaEiji]

hbase ？

[sunmoon1983]

@xuanbg 现在我就是想不明白，这个完整的数据要怎么跑，是把每个条件都组合一次，去跑数据，还是要怎么跑呢？

[sunmoon1983]

@xuanbg 比如，上面图中的条件有户籍，性别，年份，那个，我就要`城市户籍+男+2022`跑一次，下面再用`城市户籍+女+2022`跑一次，然后`农村户籍+性别（男、女）+每一年（从今年到 2010 年）`都跑一次存到库中，是这样吗？后面还要加上地区，好多条件呀，不敢想象

[xuanbg]

@sunmoon1983 你先忘记条件，没有任何条件，不就是全部了吗？然后，根据条件分组，就是多维度数据。这个时候，可以直接出，也可以汇总出，具体看你条件分组的数据是否是正交的。总之，只要结果正确就行。

[7911364440]

先根据"最小统计粒度"计算下每个年龄段的数量，查询时根据用户选择的统计条件只需要单独计算下这几个条件的数量就好。
比如要查询 "城市户籍+男+2022" = 2022 总数 - 非城市户籍数量 - 非男性数量

[wxf666]

@sunmoon1983 你要多少秒内出结果呢？

@7911364440 比如，100W 数据里，只有 1 个城市户籍、1 个男性、1 个 2022 的，那算式是 1 - (100W - 1) - (100W - 1) ？还是咋算的？

[warcraft1236]

为什么不考虑中间夹一层呢？不要直接读 mysql ，可以搞一个比如 es 这种的东西，他的查询应该会快很多，100w 这个量级上应该比 mysql 快很多

[lazyfighter]

目测提前算就行， 比如今天算出来昨天的数据， 把结果直接存起来， 基本上 T-1 之前的数据不会变化的

[8355]

直接把年龄分段刷个数据存成字段 后面建好索引直接跑会慢?才 100 万而已 不就是秒秒钟的事...
我都不理解为啥会慢 秒级不够用嘛?

[iphoneXr]

要不试试阿里云的 ADB 直接用 dts 同步源库 mysql 到 ADB 也是兼容的 mysql 写法 你的程序也不用改
ADB 号称比 mysql 快 10 倍 ADB 每个字段都自带索引的

我觉得还是要先分析统计下目前你的数据库慢在哪些地方，先针对性优化索引 再考虑别的架构更改。

[agui2200]

@wxf666 主要是我也不清楚他们具体业务，怎么断定小索引就能解决他的业务问题，所以最好的方案就是轻量的 BI ，轻量 BI 里面 CK 又是最好处理的，so

[xyjincan]

单独建立一个计算表，精简一下字段，之类去掉，现在主要瓶颈应该是 IO ，如果精简一下+SSD ，感觉速度可以提升 30 倍

然后按最主要的条件分表，研究优化吧