Actual combat MySQL partition (PARTITION)

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

Get a table some days ago, nearly 4000w data, no index, primary key. (It's definitely a talent to build this watch.)

This is a log table, recorded in the game the output and consumption of items, there was a background to the table statistics .... (This is to use the super computer can be counted out), can only help the predecessors pits ....

The data is too large to be reconstructed by partitioning.

If you find it is empty, it means that your MySQL version is not enough, the partition must be at least 5.1

The following for business queries, decided to use time to do range partition (and List,hash and other types), one months a district.

Tables that are partitioned by range are partitioned in one of the following ways, with each partition containing rows whose values are in a given contiguous interval. These intervals are contiguous and cannot overlap each other, and are defined using the values less than operator.

Create a new table:

SQL code

2. CREATE TABLE ' xxxxxxxx ' (
4. ' Crttm ' int (one) is not NULL,
6. ' Srvid ' int (one) is not NULL,
8. ' EvtID ' int (one) is not NULL,
10. ' Aid ' int (one) is not NULL,
12. ' RID ' int (one) is not NULL,
14. ' Itmid ' int (one) is not NULL,
16. ' Itmnum ' int (one) is not NULL,
18. ' Gdtype ' int (one) is not NULL,
20. ' Gdnum ' int (one) is not NULL,
22. ' ISLMT ' int (one) is not NULL,
24. KEY ' Crttm ' (' Crttm '),
26. KEY ' itemid ' (' itmid '),
28. KEY ' srvid ' (' srvid '),
30. KEY ' gdtype ' (' Gdtype ')
32. ) Engine=myisam DEFAULT Charset=utf8
34. PARTITION by RANGE (Crttm)
36. (
38. PARTITION p201303 VALUES less THAN (Unix_timestamp (' 2013-04-01 ')),
40. PARTITION p201304 VALUES less THAN (Unix_timestamp (' 2013-05-01 ')),
42. PARTITION p201305 VALUES less THAN (Unix_timestamp (' 2013-06-01 ')),
44. PARTITION p201306 VALUES less THAN (Unix_timestamp (' 2013-07-01 ')),
46. PARTITION p201307 VALUES less THAN (Unix_timestamp (' 2013-08-01 ')),
48. PARTITION p201308 VALUES less THAN (Unix_timestamp (' 2013-09-01 ')),
50. PARTITION p201309 VALUES less THAN (Unix_timestamp (' 2013-10-01 ')),
52. PARTITION p201310 VALUES less THAN (Unix_timestamp (' 2013-11-01 ')),
54. PARTITION p201311 VALUES less THAN (Unix_timestamp (' 2013-12-01 ')),
56. PARTITION p201312 VALUES less THAN (Unix_timestamp (' 2014-01-01 ')),
58. PARTITION p201401 VALUES less THAN (Unix_timestamp (' 2014-02-01 '))
60. );

Note:

1. Primary key and unique key must be included as part of the partition key, or "ERROR 1503 (HY000)" will be reported when creating primary key and unique index

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
Or
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. Range partition Add partition can only append partition after maximum value
3. All partitions must have the same engine
4. Range partition field: integer, numeric expression, date column, date function expression (such as year (), To_days (), To_seconds (), Unix_timestamp ())

When you import old table data into a new table, you see that the data for the new table is distributed across different extents!

Maintenance Commands:

adding partitions

SQL code

1. Alter table xxxxxxx Add Partition (partition P0 values less Than (1991)); Only partitions larger than the partition key can be added

Delete Partition

SQL code

1. Alter table xxxxxxx Drop Partition P0;//can delete any partition

Delete partition data

SQL code

1. Alter table XXXXXX truncate partition p1,p2;
2. Alter table XXXXXX truncate partition all ;
3. Or
4. Delete from xxxxxx where separated < ' 2006-01-01 ' or (separated >= ' 2006-01-01 ' and Sep  arated<' 2011-01-01 ');

Redefine partitions (including renaming partitions, accompanying moving data; merging partitions)

SQL code

1. Alter table XXXXX REORGANIZE partition P1,P3,P4 into (partition PM1 values less Than (2006),
2. Partition PM2 values less Than (2011));

Rebuild Rebuilding partitions

SQL code

1. Alter table XXXXXX rebuild partition pm1/all;//equivalent to drop all records and then reinsert; can resolve disk fragmentation

Optimizing tables

SQL code

1. Alter table TT2 optimize partition PM1;//After extensive delete table data, you can reclaim space and defragmentation. But after the 5.5.30 support. Before 5.5.30 can be replaced by Recreate+analyze, if with rebuild+analyze speed slow

Analzye table

SQL code

1. Alter table XXXXXX analyze partition pm1/all;

Check table

SQL code

1. Alter table xxxxxx Check partition pm1/all;

SQL code

2. Show create table employees2; To view the definition of a partitioned table
4. Show table status like ' Employees2 ' \g; When viewing a table, it is a partitioned table such as "create_options:partitioned"
6. SELECT * FROM information_schema.   Key_column_usage where table_name=' employees2 '; View Index
8. SELECT * from information_schema.partitions WHERE table_name=' employees2 '//view partition table
10. Explain partitions select * from employees2 where separated < ' 1990-01-01 ' or separated > ' 2   016-01-01 '; To see if a partition is being used by select

Actual combat MySQL partition (PARTITION)