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
iyaozhen
V2EX  ›  MySQL

MySQL 如何按天动态表分区?

  •  
  •   iyaozhen ·
    iyaozhen · 2019-02-20 21:33:16 +08:00 · 8439 次点击
    这是一个创建于 1863 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目前有个表的数据比较大( 1T 左右,3 亿多行)使用了表分区

      /*!50100 PARTITION BY RANGE (DAY(`date_id`))
    (PARTITION day1 VALUES LESS THAN (2)
      ENGINE = InnoDB,
    PARTITION day2 VALUES LESS THAN (3)
      ENGINE = InnoDB,
    ……
    

    预先分了 31 个区,数据按天落到分区内,查询效率还行

    目前有个问题,因为单机存不下了,每天会删 15 天前的数据,但数据量比较大,删除需要执行 3 小时(已经是按 date_id 字段去删了),很是影响业务,还容易造成主从挂掉

    想过的方案: MySQL 按分区删数据倒是很快,想过每天创建明天的分区,但还是因为数据量大 ALTER TABLE 会卡死


    其它问题:

    1.为什么不分库、分表

    因为主要是 OLAP 操作,需要各种 group by/count,分库分表不合适

    2.为什么要用 MySQL,不使用 PostgreSQL 或者 TiDB

    首先不知道目前这个量级和问题,其它数据库是否能解决,再者是很多上下游、周边系统只支持 MySQL 不过也存了 ES 一份(其实 ES 运维压力并不比 MySQL 小)

    19 条回复    2021-04-28 16:38:33 +08:00
    wweir
        1
    wweir  
       2019-02-21 08:35:26 +08:00 via Android
    我猜一下,楼主记录的是日志类数据。

    可以考虑换用时序数据库,你说到的这几个功能都是可以实现的。
    非要用 MySQL 的话,不妨换 TokuDB 存储引擎
    realpg
        2
    realpg  
       2019-02-21 09:56:11 +08:00
    我怎么感觉,你这个数据库 alter table 都会卡死的话,感觉 group_by count 更是会死翘翘
    chennqqi
        3
    chennqqi  
       2019-02-21 10:14:29 +08:00
    换时序数据库
    glacer
        4
    glacer  
       2019-02-21 10:34:32 +08:00
    分区键应该设计成 date 的整数类型,如 20190221。一次性创建几年的分区,可以保证自己在职期间不需要在操作增加分区...
    两个问题:
    1. 不太想得通为什么数据量大的情况下会使得增加分区卡死,MySQL 的分区表也是一个独立的物理表,和其他分区应该是没有关系的。也许是自己没有经历过这个数据量级的操作吧。
    2. 楼主说删除数据需要 3 个小时以上,但是下面又说直接删除分区数据很快。那么之前楼主删除数据是直接使用 delete 删除的?这样删除大量数据不仅慢,还无法释放原数据占用的硬盘空间。必须在执行`optimize table`语句后才能释放。
    iyaozhen
        5
    iyaozhen  
    OP
       2019-02-21 11:13:07 +08:00 via Android
    @wweir
    @chennqqi

    嗯,确实大部分是日志。时序数据库也能方便的 group by count 吗?
    因为周边系统太多,实在不想换数据库,而且已经用了 es 了
    iyaozhen
        6
    iyaozhen  
    OP
       2019-02-21 11:14:46 +08:00 via Android
    @realpg 查询确实很快,得带上分区主键和索引字段。秒级
    iyaozhen
        7
    iyaozhen  
    OP
       2019-02-21 11:25:14 +08:00 via Android
    @glacer 嗯,一次创建几年的也是好办法
    1.理论上是这样,但实际不是。个人猜测是新增分区也是修改表结构,MySQL 会产生一个和之前差不多大的中间表,而且还会锁表,影响写入
    2. 嗯,删数据是 delete 方式,估计删分区的命令更粗暴所以比较快吧。你说的空间占用也是个问题,但实际还好,innodb 引擎只是占着不释放,但是内部分配的,如果数据没有增长,既有空间还是够用的。其实数据量大了,optimize table 也运行不了了
    iyaozhen
        8
    iyaozhen  
    OP
       2019-02-21 12:24:33 +08:00
    @wweir TokuDB 看了下,比较契合需求,但如果要表分区的话,也有分区的问题
    wweir
        9
    wweir  
       2019-02-21 12:52:01 +08:00 via Android
    @iyaozhen 真正用起来未必需要分区哦。
    tokudb 的性能我自己没测过,不过公司一个大佬在一天几十个 G 的场景下用,据说没问题。然后天天给我们安利 tokudb 😂
    iyaozhen
        10
    iyaozhen  
    OP
       2019-02-21 19:49:44 +08:00 via Android
    @wweir 哈哈哈,我试试。😬
    chennqqi
        11
    chennqqi  
       2019-02-22 11:06:01 +08:00
    @iyaozhen Elasticsearch 了解一下
    chennqqi
        12
    chennqqi  
       2019-02-22 11:06:59 +08:00
    @iyaozhen Elasticsearch 水平扩展,再多数据都能 hold 住
    iyaozhen
        13
    iyaozhen  
    OP
       2019-02-22 11:17:27 +08:00
    @chennqqi 正文说了呀,也用了 Elasticsearch。

    其实当你数据太多的时候你就会发现 hold 不住的,太耗机器资源了,扩展到一定程度,运维工作量成倍增长
    chennqqi
        14
    chennqqi  
       2019-02-22 15:22:05 +08:00
    @iyaozhen 运维没有多少工作量,硬件成本是主要问题了。看你有多少日志了。ES 你如果分析的不多的情况下,只存储和写入不用多大规模。硬盘大一点就好。ES 可以说特别适合存储和分析日志了,把副本关了,启用压缩,建好 mapping 就好。
    iyaozhen
        15
    iyaozhen  
    OP
       2019-02-22 18:54:02 +08:00
    @chennqqi 我们 128GB 内存 + 3T SSD 的机器用了 6 台了

    副本为 1,还是需要的。目前遇到的情况是某些节点,磁盘超过阈值了,ES 会自动分片均衡,抖动比较严重
    linoder
        16
    linoder  
       2019-10-21 15:40:00 +08:00
    1 楼上换解决方案的都是扯淡
    2 写个存储过程即可
    iyaozhen
        17
    iyaozhen  
    OP
       2019-10-22 11:22:42 +08:00 via Android
    @linoder 感谢,存储过程没怎么用过,我看看
    v2exblog
        18
    v2exblog  
       2021-04-28 13:55:57 +08:00
    后来解决了吗,怎么解决的
    iyaozhen
        19
    iyaozhen  
    OP
       2021-04-28 16:38:33 +08:00
    @v2exblog 最终选择了 hash 成 30 个表
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   980 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 20:10 · PVG 04:10 · LAX 13:10 · JFK 16:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.