请教一些分库分表的问题

[jiobanma]

目前线上数据库中一个大表大概有 2k 万条数据。 这 2 千万数据大概是 1 年的量。之后业务量变大，可能会更高一点。 准备对这个表进行分表，有两点问题想咨询一下大佬们。

1. 大概分多少个表，能够支持未来几年的业务量。
2. 分表后，旧表的数据如何迁移到新表内（可停服，不用考虑过渡阶段数据）。 写代码/canal/LogStatsh/flink-cdc

鉴于大家都说要根据实际的业务场景来看，我描述一下我的业务场景。
1. 这个大表类似于一个任务表，每个用户每天可能有多个任务，每个任务每天可能有几十条或者上百条数据不等。
2. 大部分的查询场景是根据 id （雪花算法生成的），同时 id 也是任务号，进行查询，或者根据其他的一些限制条件来查询。
3. 因为每个用户可能会查看历史的数据报告，或者其他的一些报表查询，所以不太好按照时间进行分表。
4. 数据的增长频率为：去年上线，到现在已经 2 千万了，估计后续会按照每年 2-3 千万的速度增加。
5. 担心后面数据量越来越大影响增删改查的压力。所以现在想做分表，分库目前不需要，所以不涉及分布式事务的场景。
6. 大家不要提更换其他分布式数据库或者其他的数据处理方案了，因为代码包括数据的架构已经定了，包括一些历史原因，我没这个能力和精力去推动整个架构的改动，目前只考虑最方便的处理方案

我能想到的是根据 id 取模分表，但是不知道这样对不对，没什么经验对分库分表。
还有一点就是服务可以停机，所以不用考虑增量全量数据一致性的问题

[imokkkk]

感觉既然决定了分就一次性分到位 要不后面再扩 迁移数据头大

[cubecube]

分区表，分个毛线。现在全闪存，大内存服务器，非国内 top10 的公司，放弃分库分表吧

[flashBee233]

分布式数据库 OceanBase 咋样

[richangfan]

建新表，写 SQL 转移数据，再把新旧表的名字改了

[raphaell2e]

如果数据分布时间均匀的话，可以按年月分表，按你的数据量描述一个月 200 万左右的数据量，搓搓有余。数据迁移的话，提前写好 migrate 脚本，设定数据冻结时间，先跑脚本迁移数据，然后上线，上线后再同步冻结时间到上线时间之间产生的数据。

[Hurriance]

尝试看有没有可能从垂直增强（机器配置）的角度加强下？

因为我发现，引入分库分表带来的不止是开发过程中的心智成本的增加，还会需要运维团队的支持，不充分的考虑引入可能会带来更大的隐患和成本，如果你需要考虑这个的话。

[iyaozhen]

其实 mysql 的表分区还是很好用的，可以按一个字段，比如日期分表，对应用层还是一张表。预先把未来的分区都建好就行

[nash1000]

两千万一点数据一点都不多，找个字段建个分区，常用的一些字段加一些索引就行了

[opengps]

硬盘速度快，查询简单，那么 2k 万数据并不多，结合合理的索引就行
查询如果复杂，那么有必要提升下硬盘速度，或者表结构上拆分一下了

[NoobNoob030]

我这公司一张表 6e 数据，三个月 1e 条，频繁读写，索引建好查询很快，不太影响性能盒业务，根本没打算分库分表

[jiobanma]

@NoobNoob030 #10 这个好夸张
@nash1000 #8
@opengps #9 问题是这只是 1 年的数据量。之后逐年增加，明年可能就 5 千万了，这也不需要分吗

[RainCats]

讨论技术问题前先整明白业务要求。
- 如果这个业务相关的历史数据不怎么访问的，那直接按年或者年月去开新表存放旧数据得了。然后程序上稍作调整即可
- 如果必须要新旧数据都混在一块的，那再看看用啥技术，分库分表带来的问题可太多了

[ashe900501]

我觉得你可以详细描述一下业务场景. 不同场景方案不同.
分表后期查询非常麻烦.尤其是聚合查询.
通用的分表,可以翻倍分表.从一个翻倍成两个.从两个翻倍成 4 个.直接用主键区余数.
完全不停机方法:先把从库变成全变成主库,然后修改业务逻辑.然后再把所有的主库重新挂载新的从库.
当然也可以用中间件,我没用过

我觉得可以部分表.把事务操作留在数据库,然后把查询放到 es.感觉数据量再大,也不会有太大问题.数据库只做基于主键的增删改查.查询全都放 es.

[jiobanma]

@RainCats #12 目前是这样的，数据可能不太好按照时间做拆分，因为有业务需要访问整体的数据。因为我对分库分表的东西接触的不多，目前我们的业务其实不需要分库，只是分表。所以分库分表带来的分布式事务问题应该可以先按下不表。其他的我能想到问题好像也没其他的了。使用的是 shardingsphere 框架，代码上基本没改动，我感觉复杂程度也不高，框架都帮忙做好了。唯一不清楚的就是性能什么的，也许是我了解的坑比较少。还希望大佬们可以给点建议...

[opengps]

@jiobanma 我看到已经有人建议了，首先考虑下表分区，如果分区字段合理，表分区最大优势是本身用法等同于单表，几乎不对原有代码逻辑造成任何影响。
单表不行的时候再考虑分表-减少索引时间
单机 io 扛不住了在考虑分库-分不同服务器

[yc8332]

按用户分表。。不过感觉 N 年的也不需要了啊。

[jiobanma]

@ashe900501 #13 es 这个方式到是也不错，但是有一点我不太清楚，虽然查询走 es ，es 数据量大无所谓，但是基础数据依然在 mysql 中的单表存放，按照现在的数据增长率，数据量可能在两三年后就破亿了，真的不会影响数据库压力或者单表的增删改效率吗。

[ashe900501]

@jiobanma 数据库只做基于主键的增删改查.我觉得你可以再测试环境弄几亿数据测试一下.这个我不敢保证.但是我感觉应该没啥问题.索引的效率还是很高的.

[wqhui]

shardingsphere 这个我有在用，也是单纯做了分表没分库
遇到比较麻烦的问题有两个
1.查询如果不带上分片键会导致所有分表执行一遍查询语句，对于频繁执行的查询必须带分片键，不然容易炸，跟其他表聚合、统计也会遇到这问题，有时候需要做些骚操作去维持性能，比如做个关系表让你通过查询条件获取分片键
2.这框架的文档跟实际代码有点对不上，应该是文档维护不及时，每个版本都会有些不兼容改动，升级版本、初次开发遇到问题都需要自己一边看文档一边看源码摸索

迁移数据的方法跟 5 楼讲的差不多，先把历史数据按规则刷进新表，这时新数据入的是旧表，然后代码切换到新版本，新数据进新表，上次还没迁的增量旧数据迁到新表

[yaodong0126]

2000w 根本不多，我觉得没必要分

[jiobanma]

@wqhui #19 你说的这个应该类似于分两个表，id 按照奇偶数分布，然后关联其他表查询的时候，条件中的 id 即使是单数，但也会把两个拆分出来的表都去关联另一个表然后聚合是吧。我记得文档里好像可以配置绑定键来解决笛卡尔积。不知道我说的是不是你说的第一点。第二点确实，文档有点怪，不过一个版本没问题的话，后续应该也不会更换了。

[token10086]

花点钱上分布式数据库吧，DRDS 什么的

[kanepan19]

分表分库是解决插入问题的。 插入到瓶颈了？

[seth19960929]

直接使用 MySQL 分区, 别分表了

[sadfQED2]

你们有 dba 吗？有 dba 让 dba 搞 dbproxy ，对业务透明，没 dba 就让公司加钱升配置，2000 万数据算个屁啊，我司评论表 20 多个字段的宽表，20 亿+数据，照样单表跑。

[wyx119911]

这种用户之间隔离的数据，按用户 id hash 分表就可以（比如预先分 1000 张表），将用户 id 编码到任务 id 中，无论是根据 id 查询，还是用户查历史报表都是没问题的。

[xyening]

2000w 数据不多，应该没有一点点压力

[dw2693734d]

不得不吹一波 Postgres, 配合 Citus 分表分库, 无缝扩展, 操作极其简单

[dw2693734d]

@dw2693734d

两句即可完成操作

create extension citus;
SELECT create_distributed_table('table', 'shard_column', shard_count := 256);

[dw2693734d]

添加新的机器节点:

SELECT * FROM citus_add_node('worker_node_address', '192.168.0.2');

rebalance:

SELECT rebalance_table_shards();

[dw2693734d]

我的服务器，1.2TB 的数据，2000 个分表，单机节点

[kuituosi]

分区表如果不小心跨表也是头疼。你对分库分表不熟悉的话有 2 个成熟方案，1 旧数据很少访问就移走大部分都是访问最近一个月的数据。2 用云上分布式数据库，完全兼容单节点数据库平滑迁移，几乎不用改动业务代码。如果这 2 个成熟方案条件不满足，那只能考虑自己实现分库分表，但是坑也比较多最好找人技术咨询。本人资深架构师，只要报酬丰富就能提供优质服务

[happy32199]

tidb 自动分区 有人用过吗？ 免费版的能用吗

[v2eb]

数据也不多吧, sql 慢的话要不贴下表结构？
历史数据提前统计好, 按年分表也可以吧

[512357301]

搞个 clickhouse ，用来读，MySQL 专门写

[lovelylain]

“2. 大部分的查询场景是根据 id （雪花算法生成的），同时 id 也是任务号
3. 因为每个用户可能会查看历史的数据报告
我能想到的是根据 id 取模分表”
你直接按 id 取模分表就没法实现你的第 3 点了。你这个按用户或者按时间分表都行，
按用户的话，uid 取模分表，任务 id 里加上这个模数，这样既能通过 uid 找到表，也能通过任务 id 找到表，方便查询，需要预先分好足够表和确定好 id 规则，不然后面再调整就比较麻烦；
按时间分表的话，每年一个表，用户查找历史数据时问题也不大，方便扩充新表和淘汰旧数据。
报表的话，看你需求了，一般都是离线做吧，不在乎速度。
历史数据的话，如果你没有根据旧 id 查询的场景或者有但影响不大，可以按新表规则生成新 id 迁移到新表，不能迁移的话就在查询上做兼容。

[PendingOni]

试试用 MyCat 做读写分离 读的表加上索引

[lvxiaomao]

1. 首先明确查询场景吧，如果是根据 userId 查询的，那就根据 userId 分表。但是需要考虑是否有 过热的 userId 的问题，即某个分表数据远超于其他分表，但是这种情况感觉不会存在

2. 每张表数据不建议超 1000w 吧；每年 2000w 的增量，用 8 张表或者 16 张表就可以了；能支持好几年呢。反正分表数要是 2 的 N 次幂

3. 是否可以考虑不分表，你们 2000w 数据是否有一部分可以归档处理呢？

[dailiha01sy]

现在的机器单表塞个几个亿轻轻松松, 没必要分

[crazyweeds]

@dailiha01sy 1 亿左右确实没必要，但是 10 亿的话，分区都不太够看，估计还得配合分表。当然，分布式数据库可能是更好的选择。不过，没实际调研过分布式数据库。