目前系统的 Stat 表以每天 20W 条的数据量增加, 尽管已经把超过 3 个月的数据 dump 到其他地方, 但表中仍然有接近 2KW 条数据, 容量接近 2GB.
Stat 表已经加上索引, 直接 select ... where ... limit 的话, 速度还是很快的, 但一旦涉及到 group by 分页, 就会变得很慢.
据观察, 7 天内的 group by 需要 35~50s 左右. 运营反映体验极其不友好. 于是上网搜索 MySQL 分区方案. 发现网上的基本上都是在系统性地讲解 partition 的概念和种类, 以及一些实验性质的效果, 并不贴近实战.
通过参考 MySQL 手册以及自己的摸索, 最终在当前系统中实现了分区, 因为记录一下.
分区类型的选择
Stat 表本身是一个统计报表, 所以它的数据都是按日期来存放的, 并且热数据一般只限于当天, 以及 7 天内. 所以我选择了 Range 类型来进行分区.
为当前表创建分区
因为是对已有表进行改造, 所以只能用 alter 的方式:
- ALTER TABLE stat
- PARTITION BY RANGE(TO_DAYS(dt)) (
- PARTITION p0 VALUES Less THAN(0),
- PARTITION p190214 VALUES Less THAN(TO_DAYS('2019-02-14')),
- PARTITION pm VALUES Less THAN(MAXVALUE)
- );
这里有 2 点要注意:
一是 p0 分区, 这是因为 MySQL(我是 5.7 版) 有个 bug, 就是不管你查的数据在哪个区, 它都会扫一下第一个区, 我们每个区的数据都有几十万条, 扫一下很是肉疼啊, 所以为了避免不必要的扫描, 直接弄个 0 数据分区就行了.
二是 pm 分区, 这个是最大分区. 假如不要 pm, 那你存 2019-02-15 的数据就会报错. 所以 pm 实际上是给未来的数据一个预留的分区.
定期扩展分区
由于 MySQL 的分区并不能自己动态扩容, 所以我们要写个代码为它动态的增加分区.
增加分区需要用到 REORGANIZE 命令, 它的作用是对某个分区重新分配. 比如明天是 15 号, 那我们要给 15 号也增加个分区, 实际上就是把 pm 分区拆分成 2 个分区:
- ALTER TABLE stat
- REORGANIZE PARTITION pm INTO (
- PARTITION p190215 VALUES Less THAN(TO_DAYS('2019-02-15')),
- PARTITION pm VALUES Less THAN(MAXVALUE)
- );
这里就涉及到一个问题, 即如何获得当前表的所有分区? 网上有挺多方法, 但我试了下感觉还是先 show create table stat 然后用正则匹配出所有分区更方便一点.
定期删除分区
随着数据库越来越大, 我们肯定是要清除旧的数据, 同时也要清除旧的分区. 这个也比较简单:
ALTER TABLE stat DROP PARTITION p190214, p190215
来源: https://juejin.im/post/5c6e1cd7f265da2d8c7dc6a9