實戰mysql分區(PARTITION)

標籤：

 http://lobert.iteye.com/blog/1955841

前些天拿到一個表，將近有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使用  

 

 

實戰mysql分區(PARTITION)