注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

普通表转为分区表的步骤  

2017-04-26 15:50:43|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
普通表转为分区表的步骤:
1. 建立分区表
CREATE TABLE `dr_stats_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `views` int(10) NOT NULL DEFAULT '0' COMMENT '展示量',
  `num` mediumint(8) NOT NULL DEFAULT '0' COMMENT '计费量',
  `advnum` int(10) NOT NULL DEFAULT '0' COMMENT '广告商计费数',
  `clicks` mediumint(8) NOT NULL DEFAULT '0' COMMENT '点击量',
  `do2click` mediumint(8) NOT NULL DEFAULT '0' COMMENT '二次点击量',
  `day` date NOT NULL DEFAULT '0000-00-00' COMMENT '计费日期',
  `planid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '计划ID',
  `uid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '站长ID',
  `siteid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '站点ID',
  `zoneid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '广告位ID',
  `adstypeid` mediumint(8) NOT NULL COMMENT '广告类型ID',
  `deduction` mediumint(8) NOT NULL DEFAULT '0' COMMENT '扣量',
  `sumprofit` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '扣量金额',
  `sumpay` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '计费金额',
  `sumadvpay` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '计费总金额',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已结算',
  `dosage` mediumint(8) NOT NULL DEFAULT '0' COMMENT '补量',
  `sumdosage` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '补量金额',
  PRIMARY KEY (`id`,`day`),
  UNIQUE KEY `day` (`day`,`planid`,`uid`,`siteid`,`zoneid`,`adstypeid`)
) ENGINE=InnoDB AUTO_INCREMENT=1055328 DEFAULT CHARSET=utf8 COMMENT='站点结算'
 PARTITION BY RANGE  COLUMNS(`day`)
(PARTITION p_2016_06 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
 PARTITION p_2016_07 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB,
 PARTITION p_2016_08 VALUES LESS THAN ('2016-09-01') ENGINE = InnoDB,
 PARTITION p_2016_09 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,
 PARTITION p_2016_10 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB,
 PARTITION p_2016_11 VALUES LESS THAN ('2016-12-01') ENGINE = InnoDB,
 PARTITION p_2016_12 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p_2017_01 VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
 PARTITION p_2017_02 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB,
 PARTITION p_2017_03 VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
 PARTITION p_2017_04 VALUES LESS THAN ('2017-05-01') ENGINE = InnoDB,
 PARTITION p_2017_05 VALUES LESS THAN ('2017-06-01') ENGINE = InnoDB,
 PARTITION p_2017_06 VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
 PARTITION p_max VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
 Query OK, 0 rows affected (1.13 sec)
 
 
 
 2. 把历史数据插入到分区表
 insert into dr_stats_new select * from dr_stats where day < current_date();
Query OK, 15212736 rows affected (6 min 10.66 sec)
 
 
 
 3. 切换
 alter table dr_stats rename to dr_stats_old;
 alter table dr_stats_new rename to dr_stats;
 
 
 
 4. 插入剩余的数据
 insert into dr_stats select * from dr_stats_old where day >= current_date();
 
 
 
 5. 比较查询效率
 mysql> SELECT count(*) FROM `dr_stats` where 1 and uid='21123' and `day` BETWEEN '2017-03-01' and '2017-03-31';                                      
+----------+
| count(*) |
+----------+
|      925 |
+----------+
1 row in set (1.22 sec)

mysql> SELECT count(*) FROM `dr_stats_old` where 1 and uid='21123' and `day` BETWEEN '2017-03-01' and '2017-03-31';
+----------+
| count(*) |
+----------+
|      925 |
+----------+
1 row in set (4.33 sec)
 
第一次查询,分区表比普通表快3秒钟。
mysql> SELECT count(*) FROM `dr_stats_old` where 1 and uid='21123' and `day` BETWEEN '2017-03-01' and '2017-03-31';
+----------+
| count(*) |
+----------+
|      925 |
+----------+
1 row in set (0.97 sec)

mysql> SELECT count(*) FROM `dr_stats` where 1 and uid='21123' and `day` BETWEEN '2017-03-01' and '2017-03-31';
+----------+
| count(*) |
+----------+
|      925 |
+----------+
1 row in set (0.85 sec)
第二次查询,分区表比普通表快一点点。

回滚:
 1.停应用
 2.插回后插入的数据
 select max(id) from dr_stats_old; #old_id
 select max(id) from dr_stats;     #new_id
 alter table dr_stats rename to dr_stats_new;
 alter table dr_stats_old rename to dr_stats;
 insert into dr_stats select * from dr_stats_new where id>=old_id and id<=new_id;


分区拆分:
p_max分区拆成p_2017_06、p_2017_07和p_max
ALTER TABLE dr_stats
REORGANIZE PARTITION p_max INTO
(PARTITION p_2017_08 VALUES LESS THAN ('2017-09-01'),
PARTITION p_2017_09 VALUES LESS THAN ('2017-10-01'),
partition p_max values less than maxvalue); 

p_max分区拆成p_2017_10和p_max
ALTER TABLE dr_stats
REORGANIZE PARTITION p_max INTO
(PARTITION p_2017_10 VALUES LESS THAN ('2017-11-01'),
partition p_max values less than maxvalue); 

分区删除:
alter table dr_stats_part drop partition p_2016_06,p_2016_07,p_2016_08;
  评论这张
 
阅读(83)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017