微学网

热门关键词:   as  thinkphp  ecshop  xxx  商务通  www.ymwears.cn
热门: ASP.NET PHP编程 ASP编程 JSP编程 Python

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

发布时间:2021-04-10 来源:未知 点击:
目前系统的 Stat 表以每天 20W 条的数据量增加,尽管已经把超过3个月的数据 dump 到其他地方,但表中仍然有接近 2KW 条数据,容量接近 2GB。
 
Stat 表已经加上索引,直接 select … where … limit 的话,速度还是很快的,但一旦涉及到 group by 分页,就会变得很慢。
 
据观察,7天内的 group by 需要 35~50s 左右。运营反映体验极其不友好。
 
于是上网搜索 MySQL 分区方案。发现网上的基本上都是在系统性地讲解 partition 的概念和种类,以及一些实验性质的效果,并不贴近实战。
 
通过参考 MySQL手册以及自己的摸索,最终在当前系统中实现了分区,因为记录一下。

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

这就是微学网-程序员之家为你提供的"mysql优化案例:千万级数据表 partition 实战应用案例"希望对你有所帮助.本文来自网络,转载请注明出处:http://www.weixuecn.cn/article/14150.html
网络编程 | 前端制作 | 数据库 | CMS教程 | 脚本编程 | 框架 | 服务器 | 微信开发 | APP开发 | 学习教程 |

凡本网站转载的文章、图片等资料的版权归版权所有人所有,因无法和版权所有者一一联系,如果本网站选取的文/图威胁到您的权益,请您及时和本网站联系。 我们会在第一时间内采取措施,避免给双方造 成不必要的损失。
© 2015-2021 微学网 版权所有,并保留所有权利。 备案号:粤ICP备09051474号-1

菜鸟学习网,提供各类程序编程入门,技巧