"Reprint" The actual combat MySQL partition (PARTITION)

Reprint Address: 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

CREATE TABLE' xxxxxxxx ' (' Crttm ' )int( One) not NULL, ' Srvid 'int( One) not NULL, ' EvtID 'int( One) not NULL, ' aid 'int( One) not NULL, ' rid 'int( One) not NULL, ' Itmid 'int( One) not NULL, ' Itmnum 'int( One) not NULL, ' Gdtype 'int( One) not NULL, ' Gdnum 'int( One) not NULL, ' ISLMT 'int( One) not NULL,KEY' Crttm ' (' Crttm '),KEY' itemid ' (' itmid '),KEY' srvid ' (' srvid '),KEY' Gdtype ' (' Gdtype ')) ENGINE=MyISAMDEFAULTCHARSET=utf8partition byRANGE (Crttm) (PARTITION p201303VALUESLess THAN (Unix_timestamp ('2013-04-01')), PARTITION p201304VALUESLess THAN (Unix_timestamp ('2013-05-01')), PARTITION p201305VALUESLess THAN (Unix_timestamp ('2013-06-01')), PARTITION p201306VALUESLess THAN (Unix_timestamp ('2013-07-01')), PARTITION p201307VALUESLess THAN (Unix_timestamp ('2013-08-01')), PARTITION p201308VALUESLess THAN (Unix_timestamp ('2013-09-01')), PARTITION p201309VALUESLess THAN (Unix_timestamp ('2013-10-01')), PARTITION p201310VALUESLess THAN (Unix_timestamp ('2013-11-01')), PARTITION p201311VALUESLess THAN (Unix_timestamp ('2013-12-01')), PARTITION p201312VALUESLess THAN (Unix_timestamp ('2014-01-01')), PARTITION p201401VALUESLess THAN (Unix_timestamp ('2014-02-01')));

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

Alter Table Add values less than (1991));  // can only add partitions larger than the partition key

Delete Partition

SQL code

Alter Table Drop // can delete any partition

Delete partition data

Alter TableXxxxxxtruncatepartition p1,p2;Alter TableXxxxxxtruncatePartition All; orDelete  fromXxxxxxwhereSeparated< '2006-01-01' or(Separated>= '2006-01-01'  andSeparated<'2011-01-01');

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

Alter Table  into values less than (2006values less than);

Rebuild Rebuilding partitions

Alter  table xxxxxx rebuild partition PM1/all// equals drop all records, then reinsert; can resolve disk fragmentation

Optimizing tables

Alter  Table // after a large number of delete table data, you can reclaim space and defragmentation. But in 5. 5. Support After 30. In the 5. 5. 30 can be replaced by recreate+analyze, if with rebuild+analyze Slow

Analzye table

Alter  table xxxxxx analyze partition PM1/all;

Check table

Alter  Table Check partition PM1/all;

ShowCreate TableEmployees2;//view the definition of a partitioned table showTableStatus like 'Employees2'\g;//when viewing a table, it is a partitioned table such as "create_options:partitioned"Select *  fromInformation_schema. Key_column_usagewheretable_name='Employees2';//View IndexSELECT *  fromInformation_schema.partitionsWHEREtable_name='Employees2'   //view partition Table explain partitionsSelect *  fromEmployees2whereSeparated< '1990-01-01' orSeparated> '2016-01-01';//To see if a partition is being used by select
//view partition usage

Select A.partition_name part,a.partition_expression expr,a.partition_description descr,a.table_rows
From INFORMATION_SCHEMA. Partitions A
where Table_schema=schema ()
and Table_name= ' RC3 ';

"Reprint" The actual combat MySQL partition (PARTITION)