mysql优化案例：千万级数据表partition实战应用案例.docVIP

PAGE

PAGE1

mysql优化案例：千万级数据表partition实战应用案例

目前系统的Stat表以每天20W条的数据量增加，尽管已经把超过3个月的数据dump到其他地方，但表中仍然有接近2KW条数据，容量接近2GB。

Stat表已经加上索引，直接selectwherelimit的话，速度还是很快的，但一旦涉及到groupby分页，就会变得很慢。

据观察，7天内的groupby需要35~50s左右。运营反映体验极其不友好。

于是上网有哪些信誉好的足球投注网站MySQL分区方案。发现网上的基本上都是在系统性地讲解partition的概念和种类，以及一些实验性质的效果，并不贴近实战。

通过参考MySQL手册以及自己的摸索，最终在当前系统中实现了分区，因为记录一下。

分区类型的选择

Stat表本身是一个统计报表，所以它的数据都是按日期来存放的，并且热数据一般只限于当天，以及7天内。所以我选择了Range类型来进行分区。

为当前表创建分区

因为是对已有表进行改造，所以只能用alter的方式：

ALTERTABLEstat

PARTITIONBYRANGE(TO_DAYS(dt))(

PARTITIONp0VALUESLESSTHAN(0),

PARTITIONp190214VALUESLESSTHAN(TO_DAYS(#39;2019-02-14#39;)),

PARTITIONpmVALUESLESSTHAN(MAXVALUE)

);

这里有2点要注意：

一是p0分区，这是因为MySQL(我是5.7版)有个bug，就是不管你查的数据在哪个区，它都会扫一下第一个区，我们每个区的数据都有几十万条，扫一下很是肉疼啊，所以为了避免不必要的扫描，直接弄个0数据分区就行了。

二是pm分区，这个是最大分区。假如不要pm，那你存2019-02-15的数据就会报错。所以pm实际上是给未来的数据一个预留的分区。

定期扩展分区

由于MySQL的分区并不能自己动态扩容，所以我们要写个代码为它动态的增加分区。

增加分区需要用到REORGANIZE命令，它的作用是对某个分区重新分配。

比如明天是15号，那我们要给15号也增加个分区，实际上就是把pm分区拆分成2个分区：

ALTERTABLEstat

REORGANIZEPARTITIONpmINTO(

PARTITIONp190215VALUESLESSTHAN(TO_DAYS(#39;2019-02-15#39;)),

PARTITIONpmVALUESLESSTHAN(MAXVALUE)

);

这里就涉及到一个问题，即如何获得当前表的所有分区？网上有挺多方法，但我试了下感觉还是先showcreatetablestat然后用正则匹配出所有分区更方便一点。

定期删除分区

随着数据库越来越大，我们肯定是要清除旧的数据，同时也要清除旧的分区。

这个也比较简单：

ALTERTABLEstatDROPPARTITIONp190214,p190215