文档章节

实战mysql分区(PARTITION)

源远流长-泉
 源远流长-泉
发布于 2017/06/06 08:08
字数 948
阅读 21
收藏 1

前些天拿到一个表,将近有4000w数据,没有任何索引,主键。(建这表的绝对是个人才)

这是一个日志表,记录了游戏中物品的产出与消耗,原先有一个后台对这个表进行统计。。。。。(这要用超级计算机才能统计得出来吧),只能帮前人填坑了。。。。

 

数据太大,决定用分区来重构。


如果你发现是empty,说明你的mysql版本不够,分区至少要5.1

 

下面针对业务查询,决定用时间来做range分区(还有list,hash等类型),一个月一个区.

按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。

新建一个表:

 

Sql代码  收藏代码

  1. CREATE TABLE `xxxxxxxx` (     
  2. `crttm` int(11) NOT NULL,     
  3. `srvid` int(11) NOT NULL,     
  4. `evtid` int(11) NOT NULL,     
  5. `aid` int(11) NOT NULL,     
  6. `rid` int(11) NOT NULL,     
  7. `itmid` int(11) NOT NULL,     
  8. `itmnum` int(11) NOT NULL,     
  9. `gdtype` int(11) NOT NULL,     
  10. `gdnum` int(11) NOT NULL,     
  11. `islmt` int(11) NOT NULL,  
  12. KEY `crttm` (`crttm`),  
  13.   KEY `itemid` (`itmid`),  
  14.   KEY `srvid` (`srvid`),  
  15.   KEY `gdtype` (`gdtype`)  
  16. ) ENGINE=myisam DEFAULT CHARSET=utf8  
  17. PARTITION BY RANGE (crttm)   
  18. (  
  19. PARTITION p201303 VALUES LESS THAN (unix_timestamp('2013-04-01')),  
  20. PARTITION p201304 VALUES LESS THAN (unix_timestamp('2013-05-01')),  
  21. PARTITION p201305 VALUES LESS THAN (unix_timestamp('2013-06-01')),  
  22. PARTITION p201306 VALUES LESS THAN (unix_timestamp('2013-07-01')),  
  23. PARTITION p201307 VALUES LESS THAN (unix_timestamp('2013-08-01')),  
  24. PARTITION p201308 VALUES LESS THAN (unix_timestamp('2013-09-01')),  
  25. PARTITION p201309 VALUES LESS THAN (unix_timestamp('2013-10-01')),  
  26. PARTITION p201310 VALUES LESS THAN (unix_timestamp('2013-11-01')),  
  27. PARTITION p201311 VALUES LESS THAN (unix_timestamp('2013-12-01')),  
  28. PARTITION p201312 VALUES LESS THAN (unix_timestamp('2014-01-01')),  
  29. PARTITION p201401 VALUES LESS THAN (unix_timestamp('2014-02-01'))  
  30. );  

 

注意: 

 

 

1. primary key和unique key必须包含在分区key的一部分,否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“

mysql> create unique index idx_employees1_job_code on employees1(job_code);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

mysql> ALTER TABLE `skate`.`employees1` ADD PRIMARY KEY (`id`) ;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

2. 范围分区添加分区只能在最大值后面追加分区
3. 所有分区的engine必须一样
4. 范围分区分区字段:integer、数值表达式、日期列,日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())

 

将旧的表数据导入到新表后,看到新表的数据都分布到不同的区了!


 

 

维护命令:


添加分区

Sql代码  收藏代码

  1. alter table xxxxxxx add partition (partition p0 values less than(1991));  //只能添加大于分区键的分区  

 

 

删除分区

Sql代码  收藏代码

  1. alter table xxxxxxx drop partition p0; //可以删除任意分区  

 

 

删除分区数据

Sql代码  收藏代码

  1. alter table xxxxxx  truncate partition p1,p2;  
  2. alter table xxxxxx  truncate partition all;  
  3. 或  
  4. delete from xxxxxx where separated < '2006-01-01' or (separated >= '2006-01-01' and separated<'2011-01-01');  

 

 

重定义分区(包括重命名分区,伴随移动数据;合并分区)

Sql代码  收藏代码

  1. alter table xxxxx reorganize partition p1,p3,p4 into (partition pm1 values less than(2006),  
  2. partition pm2 values less than(2011));  

 
rebuild重建分区

Sql代码  收藏代码

  1. alter  table xxxxxx rebuild partition pm1/all; //相当于drop所有记录,然后再reinsert;可以解决磁盘碎片  

 

 

优化表

Sql代码  收藏代码

  1. alter  table tt2 optimize partition pm1; //在大量delete表数据后,可以回收空间和碎片整理。但在5.5.30后支持。在5.5.30之前可以通过recreate+analyze来替代,如果用rebuild+analyze速度慢  

 

analzye表

Sql代码  收藏代码

  1. alter  table xxxxxx analyze partition pm1/all;  

 

check表

Sql代码  收藏代码

  1. alter  table xxxxxx check partition pm1/all;  

 
 

Sql代码  收藏代码

  1. show create table employees2;  //查看分区表的定义  
  2. show table status like 'employees2'\G;    //查看表时候是分区表 如“Create_options: partitioned”  
  3. select * from information_schema.KEY_COLUMN_USAGE where table_name='employees2';   //查看索引  
  4. SELECT * FROM information_schema.partitions WHERE table_name='employees2'   //查看分区表  
  5. explain partitions select * from employees2 where separated < '1990-01-01' or separated > '2016-01-01';   //查看分区是否被select使用

本文转载自:http://lobert.iteye.com/blog/1955841

源远流长-泉
粉丝 0
博文 25
码字总数 3661
作品 0
私信 提问
摆脱垂直&水平拆分的窘境,这一招管用!

作者介绍 张秀云,网名飞鸿无痕,现任职于腾讯,负责腾讯金融数据库的运维和优化工作。2007年开始从事运维方面的工作,经历过网络管理员、Linux运维工程师、DBA、分布式存储运维等多个IT职位...

张秀云
2017/06/22
0
0
摆脱垂直&水平拆分的窘境,这一招管用!

作者介绍 张秀云,网名飞鸿无痕,现任职于腾讯,负责腾讯金融数据库的运维和优化工作。2007年开始从事运维方面的工作,经历过网络管理员、Linux运维工程师、DBA、分布式存储运维等多个IT职位...

张秀云
2017/06/22
0
0
mysql测试添加分区和删除分区

##mysql分区 测试添加分区和删除分区 //测试range分区 (1)创建range分区 mysql> CREATE TABLE titlesrange (empno INT NOT NULL, title VARCHAR(50) NOT NULL, fromdate DATE NOT NULL, tod......

浮云飘飘
2014/02/28
607
0
sqoop实战(五)

1 Importing Data Directly into Hive 关系型数据库-----hive sqoop import --connect jdbc:mysql://192.168.130.221/sqoop --username root --password root --table tblplace --hive-impor......

发光体
2016/02/26
176
0
MySQL表分区技术详细介绍

1、概述 数据库单表到达一定量后,性能会有衰减,像mysqlsql server等犹为明显,所以需要把这些数据进行分区处理。同时有时候可能出现数据剥离什么的,分区表就更有用处了! MySQL 5.1 中新增...

丢失的羊羔
2016/03/24
158
0

没有更多内容

加载失败,请刷新页面

加载更多

mysql-connector-java升级到8.0后保存时间到数据库出现了时差

在一个新项目中用到了新版的mysql jdbc 驱动 <dependency>     <groupId>mysql</groupId>     <artifactId>mysql-connector-java</artifactId>     <version>8.0.18</version> ......

ValSong
今天
5
0
Spring Boot 如何部署到 Linux 中的服务

打包完成后的 Spring Boot 程序如何部署到 Linux 上的服务? 你可以参考官方的有关部署 Spring Boot 为 Linux 服务的文档。 文档链接如下: https://docs.ossez.com/spring-boot-docs/docs/r...

honeymoose
今天
6
0
Spring Boot 2 实战:使用 Spring Boot Admin 监控你的应用

1. 前言 生产上对 Web 应用 的监控是十分必要的。我们可以近乎实时来对应用的健康、性能等其他指标进行监控来及时应对一些突发情况。避免一些故障的发生。对于 Spring Boot 应用来说我们可以...

码农小胖哥
今天
8
0
ZetCode 教程翻译计划正式启动 | ApacheCN

原文:ZetCode 协议:CC BY-NC-SA 4.0 欢迎任何人参与和完善:一个人可以走的很快,但是一群人却可以走的更远。 ApacheCN 学习资源 贡献指南 本项目需要校对,欢迎大家提交 Pull Request。 ...

ApacheCN_飞龙
今天
5
0
CSS定位

CSS定位 relative相对定位 absolute绝对定位 fixed和sticky及zIndex relative相对定位 position特性:css position属性用于指定一个元素在文档中的定位方式。top、right、bottom、left属性则...

studywin
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部