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

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《MySQL 千万级数据表 partition 实战应用》，文章讲解的知识点主要包括MySQL、MySQL优化，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

目前系统的 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 的分区并不能自己动态扩容，所以我们要写个代码为它动态的增加分区。

增加分区需要用到

ALTER TABLE stat
    REORGANIZE PARTITION pm INTO (
        PARTITION p190215 VALUES LESS THAN(TO_DAYS('2019-02-15')),
        PARTITION pm VALUES LESS THAN(MAXVALUE)
    );

这里就涉及到一个问题，即如何获得当前表的所有分区？网上有挺多方法，但我试了下感觉还是先

ALTER TABLE stat DROP PARTITION p190214, p190215

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。