MySQL 如何按天动态表分区？

[iyaozhen]

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

[wweir]

我猜一下，楼主记录的是日志类数据。

可以考虑换用时序数据库，你说到的这几个功能都是可以实现的。
非要用 MySQL 的话，不妨换 TokuDB 存储引擎

[realpg]

我怎么感觉，你这个数据库 alter table 都会卡死的话，感觉 group_by count 更是会死翘翘

[chennqqi]

换时序数据库

[glacer]

分区键应该设计成 date 的整数类型，如 20190221。一次性创建几年的分区，可以保证自己在职期间不需要在操作增加分区...
两个问题：
1. 不太想得通为什么数据量大的情况下会使得增加分区卡死，MySQL 的分区表也是一个独立的物理表，和其他分区应该是没有关系的。也许是自己没有经历过这个数据量级的操作吧。
2. 楼主说删除数据需要 3 个小时以上，但是下面又说直接删除分区数据很快。那么之前楼主删除数据是直接使用 delete 删除的？这样删除大量数据不仅慢，还无法释放原数据占用的硬盘空间。必须在执行`optimize table`语句后才能释放。

[iyaozhen]

@wweir
@chennqqi

嗯，确实大部分是日志。时序数据库也能方便的 group by count 吗？
因为周边系统太多，实在不想换数据库，而且已经用了 es 了

[iyaozhen]

@realpg 查询确实很快，得带上分区主键和索引字段。秒级

[iyaozhen]

@glacer 嗯，一次创建几年的也是好办法
1.理论上是这样，但实际不是。个人猜测是新增分区也是修改表结构，MySQL 会产生一个和之前差不多大的中间表，而且还会锁表，影响写入
2. 嗯，删数据是 delete 方式，估计删分区的命令更粗暴所以比较快吧。你说的空间占用也是个问题，但实际还好，innodb 引擎只是占着不释放，但是内部分配的，如果数据没有增长，既有空间还是够用的。其实数据量大了，optimize table 也运行不了了

[iyaozhen]

@wweir TokuDB 看了下，比较契合需求，但如果要表分区的话，也有分区的问题

[wweir]

@iyaozhen 真正用起来未必需要分区哦。
tokudb 的性能我自己没测过，不过公司一个大佬在一天几十个 G 的场景下用，据说没问题。然后天天给我们安利 tokudb 😂

[iyaozhen]

@wweir 哈哈哈，我试试。😬

[chennqqi]

@iyaozhen Elasticsearch 了解一下

[chennqqi]

@iyaozhen Elasticsearch 水平扩展，再多数据都能 hold 住

[iyaozhen]

@chennqqi 正文说了呀，也用了 Elasticsearch。

其实当你数据太多的时候你就会发现 hold 不住的，太耗机器资源了，扩展到一定程度，运维工作量成倍增长

[chennqqi]

@iyaozhen 运维没有多少工作量，硬件成本是主要问题了。看你有多少日志了。ES 你如果分析的不多的情况下，只存储和写入不用多大规模。硬盘大一点就好。ES 可以说特别适合存储和分析日志了，把副本关了，启用压缩，建好 mapping 就好。

[iyaozhen]

@chennqqi 我们 128GB 内存 + 3T SSD 的机器用了 6 台了

副本为 1，还是需要的。目前遇到的情况是某些节点，磁盘超过阈值了，ES 会自动分片均衡，抖动比较严重

[linoder]

1 楼上换解决方案的都是扯淡
2 写个存储过程即可

[iyaozhen]

@linoder 感谢，存储过程没怎么用过，我看看

[joApioVVx4M4X6Rf]

后来解决了吗，怎么解决的

[iyaozhen]

@v2exblog 最终选择了 hash 成 30 个表