mysql partition(mysql range partition，對曆史資料建分區)

標籤：http   io   ar   for   資料   2014   div   art   sp   

官方文檔：http://www.w3resource.com/mysql/mysql-partition.php對於分區的類型，以及建立表時就寫好分區的話，按照例子做就好了。這邊文章主要是為了處理曆史資料，也就是表裡已經有海量的資料，比如兩年的，一天的資料就是G+的，像這樣的話，我們必須及早處理這些沒有提前分區的表。按照range 分區，主要是按照月和天來分區。e.g.1:（1）CREATE TABLE sale_mast (bill_no INT NOT NULL, bill_date date NOT NULL, cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL);（2）INSERT INTO sale_mast VALUES (1, ‘2013-01-02‘, ‘C001‘, 125.56), (2, ‘2013-01-25‘, ‘C003‘, 456.50), (3, ‘2013-02-15‘, ‘C012‘, 365.00), (4, ‘2013-03-26‘, ‘C345‘, 785.00), (5, ‘2013-04-19‘, ‘C234‘, 656.00), (6, ‘2013-05-31‘, ‘C743‘, 854.00), (7, ‘2013-06-11‘, ‘C234‘, 542.00), (8, ‘2013-07-24‘, ‘C003‘, 300.00), (8, ‘2013-08-02‘, ‘C456‘, 475.20);注意：這個表sale_mast是已經有資料的表，稱為曆史資料;查看分區前表的資訊：mysql> select partition_name,table_rows from information_schema.partitions where table_name=‘sale_mast‘;+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| NULL | 9 |
+----------------+------------+
1 row in set (0.00 sec)注意：range 分區的 values必須是int類型.第一種分區：然後對sale_mast 進行按月分區:ALTER TABLE sale_mast PARTITION BY RANGE (year(bill_date)*100+month(bill_date)) ( PARTITION p0 VALUES LESS THAN ((201304)), PARTITION p1 VALUES LESS THAN ((201307)), PARTITION p2 VALUES LESS THAN ((201310)), PARTITION p3 VALUES LESS THAN ((201401)));查看分區後的資訊：mysql> select partition_name,table_rows from information_schema.partitions where table_name=‘sale_mast‘;+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0 | 4 |
| p1 | 3 |
| p2 | 2 |
| p3 | 0 |
+----------------+------------+
4 rows in set (0.01 sec)第二種分區：然後對sale_mast 進行按天分區:刪除表，重新建立表和inset into後，進行按天分區：ALTER TABLE sale_mast PARTITION BY RANGE (to_days(bill_date)) ( PARTITION p0 VALUES LESS THAN (to_days(‘2013-04-01‘)), PARTITION p1 VALUES LESS THAN (to_days(‘2013-07-01‘)), PARTITION p2 VALUES LESS THAN (to_days(‘2013-10-01‘)), PARTITION p3 VALUES LESS THAN (to_days(‘2014-01-01‘)));mysql> select partition_name,table_rows from information_schema.partitions where table_name=‘sale_mast‘;+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0 | 4 |
| p1 | 3 |
| p2 | 2 |
| p3 | 0 |
+----------------+------------+
4 rows in set (0.00 sec)。還可以建立子分區，這個下次再更新。 

mysql partition(mysql range partition，對曆史資料建分區)