数据库设计问题

[hfywy]

目前的数据库表结构（忽略了其他不重要字段）是：

create table if not exists tb_test(
    `id`	bigint unsigned auto_increment comment '主键 ID',
    `A1` 	VARCHAR(32) DEFAULT '',
    `B2`	INT UNSIGNED DEFAULT 0,
    PRIMARY KEY(`id`),
    UNIQUE KEY(`A1`),
    KEY (`B2`)
);

常用的 SQL 主要是写入、查询和统计B2字段的数量，如下：

insert ignore into tb_test(A1, B2) values('xxx', 123);

select * from tb_test where A1 = 'xxx';

select count(*) as cnt fro tb_test where B2 = 123;

现在有个问题，这个表现在破亿了，统计 B2 字段耗时有时比较长，将来肯定越来越长，暂时升级数据库配置把耗时降到可接受范围了，目前想的方案是考虑做分表。但是用A1或者B2字段做分表字段都不是很好。A1做分表的话，要统计B2的时候需要每个表都轮询一次。B2字段做分表字段的话，要保证A1字段的唯一性，需要插入前轮询查每一个表当前插入的值是否已经存在。

请教下各位彦祖、亦菲，有没有好的建议，不只是数据库方案，其他存储也可以接受。

[Karte]

1: 分表分为冷数据表和热数据表 (冷:不查询,堆就可以. 热:今日或当月插入数据表)
2: 创建一个 snapshot 表, 将冷数据时直接进行统计汇总并写入到 snapshot 表中 (标志当前已经有多少了).
3: 之后每月将热数据表统计好后在通过 snapshot 表查询 snapshot 数据, 然后汇总成最新的 (旧的留着无所谓, 如果 A1 字段多, 就删掉) 写入到 snapshot 表中.
4: 汇总完成后重新建立 snapshot 的索引 (如果做删除的情况), 减少页空隙所导致的性能问题.
5: 将热数据写入到冷数据表, 然后清空热数据接收新的数据.

这样之后要统计时可以直接将热数据表的数据做统计 (缓存也可以), 然后直接获取 snapshot 表中的计数即可知道有多少了.

[icql]

如果 b2 字段有索引的话 count 还慢说明 b2 索引的区分度不高，相同 b2 值的记录数就是很多，count 就得一条一条累加就是慢，即使分表也解决不了问题。如果 b2 是可枚举的值，直接用另外一个表存储统计 b2 值对应的统计数量，insert 的时候事务里边去更新累加值，或者数据准确性要求不高放在 redis 里边累加也行，定期再校验修正一下

[Karte]

像这种之后做缓存, 然后每次有数据来的时候直接将缓存的数值进行更新. 获取时直接从缓存中获取. 数据库更新可以交由 MQ 做异步解耦处理, 可以保证宕机恢复缓存时有数据可用.
宕机恢复时可能会存在队列有堆积的情况, 这时候就可以阻塞消息消费, 然后先建立本地缓存, 然后再恢复消息处理. 消息处理时同步更新缓存中的数据. 消息生产时可以对消息添加版本号等手段来避免双写缓存的情况.
如果 A1 distinct 之后的所建立的缓存超过了服务所设定内存占比的 25%, 就放弃使用本地缓存, 直接读取数据库. 通过 BufferPool 的机制快速获取数据.

[Karte]

哦 需求看错了. 方案不可行

[cowcomic]

同意 2 楼，建议 B2 字段的统计单独存，如果这种统计不需要太强的数据一致性，可以不用做业务级别的事务，做成异步的或者通过 binlog 触发，或者简单的主从分离，从表定期同步都可以

[v2eb]

update 不多可以参考
把前 1 亿条和后面的分开统计, 前面的加缓存

[lanlanye]

额外建一张表存 B2 和对应的统计值，每次插入在同一个事务里往主表插入并让副表统计值+1 。

[void1900]

统计的表，是不用 count sum 之类的，都是 按统计维度 直接计数

[evalcony]

如果不会删数据的话，那可以按天统计，把数据另外存一个快照表里。
原 sql 增加 create_time 查询条件。之后再查一下快照表，汇总一下数据。

[hfywy]

@icql 补充下：在业务场景下，B2 是批次 id ，每一批次允许运营同学配置 A1 的记录条数是 1000 万。事务更新累加值累加值可能是个可选的方案；定期校验修正应该还是要用到 SELECT COUONT ，还是会有慢查询。

[icql]

@hfywy 定期修正是放在 redis 里边累加需要，放在数据库表事务里边 update 累加是不用修正的

[8355]

联合唯一索引是做约束存在的 单查 b2 并没有用上索引
b2 本身并没有索引所以慢

亿级并没有问题，索引合理 10 亿都可以
分表对业务代码和后续业务发展的影响是不可逆的，除非原生这些劣势就不影响你的需求。

还有上面说需要增加快照表。。。甚至要加事务。。真没这个必要
可以接受小概率数据一致性问题还不如直接 redis incr 每天定时拿脚本读完拉齐。
如果是后台系统上述方案已经足够用，前台系统建议还是先把索引加上

[Leo666666]

你可以试试布隆过滤器

[Leo666666]

抱歉，开始没有看清楚问题，我觉得 2 楼的思路是没问题的，至于定期校验的慢的问题可以选择夜间无运营人员使用时候自动触发。

[yumubi]

懂了

[kuituosi]

2 楼第一个方案正确，count 慢是因为本来数量多当然就慢，添加的时候直接在另一个表中更新累计就行，非常优雅的方案。其他方案实现反而复杂化了

[hfywy]

@8355 我这里没有用到联合索引哈，B2 是有索引的。

[kuituosi]

补充一下，2 楼第一个方案不需要 b2 是枚举的

[hfywy]

@Karte 在当前业务下，冷热数据的划分可能不是这样子，不是写入之后就数据就算冷数据，就可以清理了。背景是业务在线时都是热数据，都有统计、查询和追加的需求，在线时长一个月到一年不等。

但是，你这个分表的思路感觉可以和分表结合起来，分表分成 2 类，一类是按 A1 字段分表，另一类是按 B2 字段分表，然后新增的时候双写，统计的时候就去 B2 字段分表对应的表里查。