首页   注册   登录
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
Coding
V2EX  ›  MySQL

MySQL 如何按天动态表分区?

  •  
  •   iyaozhen · 295 天前 · 3687 次点击
    这是一个创建于 295 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目前有个表的数据比较大( 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 小)

    17 回复  |  直到 2019-10-22 11:22:42 +08:00
        1
    wweir   295 天前 via Android
    我猜一下,楼主记录的是日志类数据。

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

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

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

    副本为 1,还是需要的。目前遇到的情况是某些节点,磁盘超过阈值了,ES 会自动分片均衡,抖动比较严重
        16
    linoder   52 天前
    1 楼上换解决方案的都是扯淡
    2 写个存储过程即可
        17
    iyaozhen   51 天前 via Android
    @linoder 感谢,存储过程没怎么用过,我看看
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2624 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 30ms · UTC 00:46 · PVG 08:46 · LAX 16:46 · JFK 19:46
    ♥ Do have faith in what you're doing.