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

james2013 · 12 小时 34 分钟前 · 1904 次点击

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

19 条回复 • 2021-11-18 18:07:31 +08:00

dilu 12 小时 34 分钟前

Exdui 12 小时 22 分钟前

sunjiayao 12 小时 20 分钟前

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

meeop 12 小时 16 分钟前

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

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

james2013 12 小时 0 分钟前

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

james2013 11 小时 53 分钟前

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

sunjiayao 11 小时 41 分钟前

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