MySQL 千万级数据表 partition 实战应用

栏目: 数据库 · 发布时间: 5年前

内容简介:目前系统的 Stat 表以每天 20W 条的数据量增加,尽管已经把超过3个月的数据 dump 到其他地方,但表中仍然有接近 2KW 条数据,容量接近 2GB。Stat 表已经加上索引,直接 select … where … limit 的话,速度还是很快的,但一旦涉及到 group by 分页,就会变得很慢。据观察,7天内的 group by 需要 35~50s 左右。运营反映体验极其不友好。 于是上网搜索 MySQL 分区方案。发现网上的基本上都是在系统性地讲解 partition 的概念和种类,以及一些

目前系统的 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
复制代码

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Android编程权威指南(第3版)

Android编程权威指南(第3版)

比尔·菲利普斯 (Bill Phillips)、克里斯·斯图尔特 (Chris Stewart)、克莉丝汀·马西卡诺 (Kristin Marsicano) / 王明发 / 人民邮电出版社 / 2017-6 / 129.00元

Big Nerd Ranch 是美国一家专业的移动开发技术培训机构。本书主要以其Android 训练营教学课程为基础,融合了几位作者多年的心得体会,是一本完全面向实战的Android 编程权威指南。全书共36 章,详细介绍了8 个Android 应用的开发过程。通过这些精心设计的应用,读者可掌握很多重要的理论知识和开发技巧,获得宝贵的开发经验。 第3 版较之前版本增加了对数据绑定等新工具的介......一起来看看 《Android编程权威指南(第3版)》 这本书的介绍吧!

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具

html转js在线工具
html转js在线工具

html转js在线工具

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具