V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
james2013
V2EX  ›  MySQL

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

  •  
  •   james2013 · 2021-11-18 10:22:31 +08:00 · 4028 次点击
    这是一个创建于 861 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    21 条回复    2021-11-19 14:58:40 +08:00
    dilu
        1
    dilu  
       2021-11-18 10:23:18 +08:00
    es
    Exdui
        2
    Exdui  
       2021-11-18 10:35:08 +08:00
    es
    sunjiayao
        3
    sunjiayao  
       2021-11-18 10:36:32 +08:00
    分库如果用的中间件。「根据公司所在的省份 /城市,公司名称关键字」这两个条件可以转成公司 id 。然后用 in 保证分片命中率。「打卡人姓名关键字」这个目前看没什么特别好的办法,不过也可以先根据姓名模糊匹配到所有公司 id 。具体得看匹配到的公司 id 多不多,要是太多的话也没啥提速。
    (题外话:考勤数据是不是按时间分库比较好,查询时把时间作为必选值)
    meeop
        4
    meeop  
       2021-11-18 10:40:41 +08:00
    最简单粗暴的办法就是 3 个字段排列组合加 7 个索引
    应该是最经济最简单最省事的方法了

    考勤表反正也不关心什么高并发高性能
    james2013
        5
    james2013  
    OP
       2021-11-18 10:56:37 +08:00
    @dilu @Exdui 谢谢,我准备自己试下 es 效果
    james2013
        6
    james2013  
    OP
       2021-11-18 11:04:23 +08:00
    @sunjiayao 分表用的是中间件查询,这两个条件可以转成公司 id,我也有这个想法,试过了,发现 100 张表全中了,聚合返回结果时卡住了.由于根据公司 id 查询考勤记录的语句比较多,所以只能按公司 id 分表.
    可能还得用 es 等其它的才好解决
    sunjiayao
        7
    sunjiayao  
       2021-11-18 11:15:56 +08:00
    @james2013 感觉 es 唯一的问题就是多了套运维成本,如果考勤数据涉及更新的话也要做两套数据维护。「由于根据公司 id 查询考勤记录的语句比较多」这个条件我觉得可以看时间维度,比如说按月分表,月表里在按公司 id 查询。可以根据你们的表结构和常用 sql 做下单表压测。来确定按时间分表的颗粒度。我理解考勤这种数据时间跨度最长不会超过一年,也就是说如果按月分表的话最多也就广播 12 个库。理论上是可以接受的
    Saxton
        8
    Saxton  
       2021-11-18 11:47:07 +08:00
    放过 mysql 吧 他已经超出他能承受的范围了 直接全部同步到 es 做成宽表都比这个来得快
    liuhan907
        9
    liuhan907  
       2021-11-18 11:49:00 +08:00
    我觉得最经济的按你的需求,应该是换分布式库了。
    Erroad
        10
    Erroad  
       2021-11-18 12:06:10 +08:00 via iPhone
    感觉这个分表好像按日期分合理些啊
    lavanil
        11
    lavanil  
       2021-11-18 12:07:59 +08:00
    1.mysql 冷热分离
    2.搜索用 es
    3.统计用 clickhouse
    guanhui07
        12
    guanhui07  
       2021-11-18 12:56:59 +08:00 via iPhone
    es
    yRebelHero
        13
    yRebelHero  
       2021-11-18 13:11:44 +08:00   ❤️ 2
    @Saxton 你说的这个放过 MySQL 戳我笑点了,大哥是个幽默人。
    chenbojian
        14
    chenbojian  
       2021-11-18 13:17:34 +08:00 via Android
    @Saxton mysql 同步到 es 的方案有哪些呢?
    Saxton
        15
    Saxton  
       2021-11-18 13:38:10 +08:00
    @chenbojian 1. canal 2. Debezium
    c88155745
        16
    c88155745  
       2021-11-18 16:00:03 +08:00
    建统计库?
    chogath
        17
    chogath  
       2021-11-18 17:14:00 +08:00
    binlog
    @chenbojian
    nekoneko
        18
    nekoneko  
       2021-11-18 17:47:13 +08:00
    trino,presto
    icchux
        19
    icchux  
       2021-11-18 18:07:31 +08:00
    es 搜索 clickhouse 做聚合统计 同步有 flink cdc canal 等
    mestrace
        20
    mestrace  
       2021-11-19 10:36:32 +08:00
    首先看下考勤数据量能不能减少吧。考勤数据本身是像 log 一样的东西,可以定期归档一些不用的老数据,这样能有效降低表的大小,提升查询效率。
    另一方面,如果直接设定可以任意模糊查询的话,可能出现的查询条件的数量是所有条件的笛卡尔积,因此可以考虑从业务规则上入手,看看用户的具体查询场景是啥。
    比如我能想到的可以做的是,创建一个关键词列,把公司名称 用户 姓名都写进去,这样可以有效减少索引数量。
    (关键词列,地区,时间)
    (地区,时间)
    在查询条件不复杂的情况下,不是特别推荐上 es ,除非你司已有非常成熟的中间件做导数据 /数据检查 /数据修复,而且无需你们运维 es 集群。
    777777
        21
    777777  
       2021-11-19 14:58:40 +08:00
    mysql cluster 不是自动分库分表吗?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3452 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 11:10 · PVG 19:10 · LAX 04:10 · JFK 07:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.