阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

线上MySQL某个历史数据表的分区笔记

175次阅读
没有评论

共计 3014 个字符,预计需要花费 8 分钟才能阅读完成。

背景:

    线上的一个历史数据库,业务方反馈经常遇到一个范围查询就导致 CPU 迅速飙升的情况。拿到他们提供的 SQL 后,SQL 类似下面这种:

select * from `order_his` where `xxxx` = ‘222’ AND `XXXX` <> 1 AND order_time > ‘2016-11-01 00:00:00’ AND order_time < ‘2017-06-01 00:00:00’ \G

explain 看了下发现基本上是全表扫描了,效率太低了,并且他们都是按月查询的,因此我们就对这张表按月进行分区,就能大大减少扫描的行数。

注意:TIMESTAMP 类型的列,只能基于 UNIX_TIMESTAMP 函数进行分区,切记!

### 原始 order_his 表类似如下这种结构:

CREATE TABLE `order_his` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `order_time` timestamp NULL DEFAULT NULL,

  `pay_time` timestamp NULL DEFAULT NULL,

  `create_time` timestamp NULL DEFAULT NULL,

  `update_time` timestamp NULL DEFAULT NULL,

  PRIMARY KEY (`id`),

) ENGINE=InnoDB AUTO_INCREMENT=47603581 DEFAULT CHARSET=utf8;

step0 创建一个表结构和原先的表一样的 tmp 表

create table `order_his_tmp` like `order_his`;

step1  修改原有的主键,将分区键添加到主键里。

alter table `order_his_tmp` drop primary key,add primary key(id,order_time);

必须把分区键加到主键里面,不然 step2 也会报错提醒你这样做的。

step2 分区操作

ALTER TABLE `order_his_tmp` PARTITION BY RANGE (UNIX_TIMESTAMP (order_time))  

(

PARTITION  P201601  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-02-01’)) ,

PARTITION  P201602  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-03-01’)) ,

PARTITION  P201603  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-04-01’)) ,

PARTITION  P201604  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-05-01’)) ,

PARTITION  P201605  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-06-01’)) ,

PARTITION  P201606  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-07-01’)) ,

PARTITION  P201607  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-08-01’)) ,

PARTITION  P201608  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-09-01’)) ,

PARTITION  P201609  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-10-01’)) ,

PARTITION  P201610  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-11-01’)) ,

PARTITION  P201611  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2016-12-01’)) ,

PARTITION  P201612  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-01-01’)) ,

PARTITION  P201701  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-02-01’)) ,

PARTITION  P201702  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-03-01’)) ,

PARTITION  P201703  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-04-01’)) ,

PARTITION  P201704  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-05-01’)) ,

PARTITION  P201705  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-06-01’)) ,

PARTITION  P201706  VALUES LESS THAN  (UNIX_TIMESTAMP(‘2017-07-01’))

); 

step3、将原先表的数据灌入新的 tmp 表

insert into `order_his_tmp` select * from `order_his`;

step4、查询验证

explain partitions select * from `order_his_tmp` where `xxxx` = ‘222’ AND `XXXX` <> 1 AND order_time > ‘2015-11-01 00:00:00’ AND order_time < ‘2015-12-21 00:00:00’ \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: order_his

   partitions: p201511,p201512   ### 可以看到这里走的是 2015 年 11 和 12 月,这 2 个分区

……….. 部分内容省略 ………….

注意:当时在线上操作的时候,发现即使做了分区,执行计划里面显示的还是 ALL 全表扫描了,于是根据这个 SELECT 加了个索引解决了这个问题。这里没有真实环境不好贴图出来。

step5、替换原先的表

通知开发同学当前不要对 `order_his` 表执行查询操作。

然后我们执行:

rename table `order_his` to `order_his_nopart`;

rename table `order_his_tmp` to `order_his`;

这样的话,新的 `order_his` 表就是分区表啦。

step6、添加分区表

后期如果需要加分区的话,只要执行如下这种操作就可以添加一个新的分区

ALTER TABLE `order_his` ADD PARTITION (PARTITION P201707 VALUES LESS THAN (UNIX_TIMESTAMP(‘2017-08-01’))) ;

当然,如果我们想省事的话,就在 step2 的时候,一次性多创建很多分区(我当时是按月建分区,一直创建到 2019 年)

此外,也可以写个存储过程配合 event_schedule 每月自动创建一个新的分区。

使用存储过程的方法这里先略过,后期补充。

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-04/143262.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3014字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中