当前位置: > 数据库 > MySQL >

实战mysql分区(PARTITION)

时间:2016-05-25 23:18来源:linux.it.net.cn 作者:IT

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

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

 

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


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

 

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

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

新建一个表:

 

 
  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())

 

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


 

 

维护命令:


添加分区

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

 

 

删除分区

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

 

 

删除分区数据

 
  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');  

 

 

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

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

 
rebuild重建分区

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

 

 

优化表

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

 

analzye表

 
  1. alter  table xxxxxx analyze partition pm1/all;  

 

check表

 
  1. alter  table xxxxxx check partition pm1/all;  

 
 

 
  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使用  



(责任编辑:IT)
------分隔线----------------------------
栏目列表
推荐内容