mysql 亿级数据优化 - 使用 分区

本文主要讲述 mysql range 分区 的使用,以及使用 range 分区需要注意的事项,所谓分区,是指 mysql 底层实现的水平分表功能,但是对程序却是透明的,通过使用分区你可以做到水平的拆分你的数据库,从而提高各种性能,比如插入速度、查询速度等。

至于 mysql 分区到底是啥 网上资料够多了,此处略去,我只补充几点,一是,如果数据量少于 1000 万条,那么不要分区了,分区也没啥用,你用牛刀杀鸡,不见得就能提高效率,少于 1000 万,建个索引就好了,查询速度不会差,二是,分区要求分区键要么是唯一主键,要么是联合主键里的一个,三是,mysql 最好不要用 timestamp 类型,网上有太多教程建议您使用 它取代 dateTime,但是它有范围限制,2038年啊,有生之年你的程序将不支持继续运行,想一下,如果一个普通公司,有个程序员设计的数据库用的是 timestamp,然后公司一直活的好好的,到了 2038-01-19 03:14:07 ,突然系统崩溃了,那会是多么惨烈的场景啊,一代大神,晚节不保啊!!!而且,这个错误你会很容易忽略它。关于 dateTime 和 timestamp 你可以参考:https://dev.mysql.com/doc/refman/8.0/en/datetime.html

好了,进入主题,mysql 分区可能有多种,比如 hash 分区,list 分区 等,本文主要说 range 分区,range 分区是指 将一定范围的数据存在同一个分区,这就非常有用了,比如按年将数据归档,这样子你可以非常容易实现 水平 拆分,而且查询时 还可以并发查询,不过 count 和 sum 这两个函数目前应该依旧不支持并发,并发也是底层实现,不需要你操作它。

我个人建议,最好按 时间去 水平拆分,不要按自增的id,或者说不要按 唯一索引去分区,在我的测试中,我发现用 唯一索引分区,查询时,即使已经指定了范围,但 explain partition 依旧显示是查找了所有分区,这点我后面会再验证,然后补充,如果真是这样,那么,你一定不能使用 唯一主键去分区,那样子查询等于没分区,只有插入速度优化了而已。

下面,我们看一个使用 range 分区的例子,我使用 id 和 create_time 做联合主键,并使用 create_time 做分区键,如果之前 id 是唯一主键,那么,你可以修改下:

ALTER TABLE xxx DROP PRIMARY KEY ,ADD PRIMARY KEY (`id`, `create_time`);

然后修改表,并添加分区:

ALTER TABLE xxx
PARTITION BY RANGE (to_days(create_time))
(
PARTITION p201901 VALUES LESS THAN (to_days('2000-01-01')),
PARTITION p201907 VALUES LESS THAN (to_days('2005-01-01')),
PARTITION p202001 VALUES LESS THAN (to_days('2010-01-01')),
PARTITION p202007 VALUES LESS THAN (to_days('2015-01-01')),
PARTITION p202101 VALUES LESS THAN (to_days('2020-01-01')),
PARTITION p202107 VALUES LESS THAN (to_days('2025-01-01')),
PARTITION p202201 VALUES LESS THAN (to_days('2030-01-01')),
PARTITION p202207 VALUES LESS THAN (to_days('2035-01-01')),
PARTITION p202301 VALUES LESS THAN (to_days('2040-01-01')),
PARTITION p202307 VALUES LESS THAN (to_days('2045-01-01')),
PARTITION p202401 VALUES LESS THAN (to_days('2050-01-01')),
PARTITION p202407 VALUES LESS THAN (to_days('2055-01-01')),
   PARTITION pmax VALUES LESS THAN MAXVALUE
);

这里,假设我们就以 5 年分一个区,且分到 2055 结束,那时候,我也差不多退休啦。。。哈哈哈哈哈哈。。。

就这样,这就好了,然后你就可以正常去使用了,但是查询的时候做好限定 create_time 这样子将在指定的分区查找。具体你可以通过 explain partition select * from xxx where create_time > xxx and create_time < xxx; 去验证。

本博客若无特殊说明则由 full-stack-trip 原创发布
转载请点名出处:全栈之旅 > mysql 亿级数据优化 - 使用 分区
本文地址:https://www.kpromise.top/mysql-optimize-use-partition/

发表评论

电子邮件地址不会被公开。 必填项已用*标注