3 亿 mysql 分表数据根据条件分页查询

[james2013]

考勤数据有 3 亿条, 根据公司 id 在 mysql 已经进行分表,分了 100 张表,以后还在增加不少数据 现在有 1 个需求:根据公司所在的省份 /城市,公司名称关键字,打卡人姓名关键字进行分页查询考勤,3 个条件可以同时存在,有没有好的解决方法?

[dilu]

es

[Exdui]

es

[sunjiayao]

分库如果用的中间件。「根据公司所在的省份 /城市,公司名称关键字」这两个条件可以转成公司 id 。然后用 in 保证分片命中率。「打卡人姓名关键字」这个目前看没什么特别好的办法，不过也可以先根据姓名模糊匹配到所有公司 id 。具体得看匹配到的公司 id 多不多，要是太多的话也没啥提速。
（题外话：考勤数据是不是按时间分库比较好，查询时把时间作为必选值）

[meeop]

最简单粗暴的办法就是 3 个字段排列组合加 7 个索引
应该是最经济最简单最省事的方法了

考勤表反正也不关心什么高并发高性能

[james2013]

@dilu @Exdui 谢谢,我准备自己试下 es 效果

[james2013]

@sunjiayao 分表用的是中间件查询,这两个条件可以转成公司 id,我也有这个想法,试过了,发现 100 张表全中了,聚合返回结果时卡住了.由于根据公司 id 查询考勤记录的语句比较多,所以只能按公司 id 分表.
可能还得用 es 等其它的才好解决

[sunjiayao]

@james2013 感觉 es 唯一的问题就是多了套运维成本，如果考勤数据涉及更新的话也要做两套数据维护。「由于根据公司 id 查询考勤记录的语句比较多」这个条件我觉得可以看时间维度，比如说按月分表，月表里在按公司 id 查询。可以根据你们的表结构和常用 sql 做下单表压测。来确定按时间分表的颗粒度。我理解考勤这种数据时间跨度最长不会超过一年，也就是说如果按月分表的话最多也就广播 12 个库。理论上是可以接受的

[Saxton]

放过 mysql 吧 他已经超出他能承受的范围了 直接全部同步到 es 做成宽表都比这个来得快

[liuhan907]

我觉得最经济的按你的需求，应该是换分布式库了。

[Erroad]

感觉这个分表好像按日期分合理些啊

[lavanil]

1.mysql 冷热分离
2.搜索用 es
3.统计用 clickhouse

[guanhui07]

es

[yRebelHero]

@Saxton 你说的这个放过 MySQL 戳我笑点了，大哥是个幽默人。

[chenbojian]

@Saxton mysql 同步到 es 的方案有哪些呢？

[Saxton]

@chenbojian 1. canal 2. Debezium

[c88155745]

建统计库？

[chogath]

binlog
@chenbojian

[nekoneko]

trino,presto

[icchux]

es 搜索 clickhouse 做聚合统计 同步有 flink cdc canal 等

[mestrace]

首先看下考勤数据量能不能减少吧。考勤数据本身是像 log 一样的东西，可以定期归档一些不用的老数据，这样能有效降低表的大小，提升查询效率。
另一方面，如果直接设定可以任意模糊查询的话，可能出现的查询条件的数量是所有条件的笛卡尔积，因此可以考虑从业务规则上入手，看看用户的具体查询场景是啥。
比如我能想到的可以做的是，创建一个关键词列，把公司名称 用户 姓名都写进去，这样可以有效减少索引数量。
（关键词列，地区，时间）
（地区，时间）
在查询条件不复杂的情况下，不是特别推荐上 es ，除非你司已有非常成熟的中间件做导数据 /数据检查 /数据修复，而且无需你们运维 es 集群。