[Reprinted] mysql PARTITION and mysqlpartition

[Reprinted] mysql PARTITION and mysqlpartition

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

The previous day, we got a table with nearly million data records and no indexes or primary keys. (This table is definitely a talent)

This is a log table that records the output and consumption of items in the game. A background originally used to collect statistics on this table ..... (This can only be obtained through the supercomputer.) It can only help the predecessors to fill in the traps ....

If the data is too big, it is decided to use partitions for reconstruction.

If you find it is empty, it indicates that your mysql version is not enough and the partition must be at least 5.1.

For the business query, we decided to use time for range partitioning (as well as list, hash, and Other types), one partition per month.

A table partitioned by RANGE is partitioned by the following method. Each partition contains rows whose partition expression values are located in a given continuous interval. These intervals must be continuous and do not overlap with each other, and are defined using the values less than operator.

Create a table:

SQL code

1. Create table 'xxxxxxxxxx '(
2. 'Crttm 'int (11) not null,
3. 'Srvid' int (11) not null,
4. 'Evtid' int (11) not null,
5. 'Id' 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 ('2017-04-01 ')),
20. PARTITION p201304 values less than (unix_timestamp ('2017-05-01 ')),
21. PARTITION p201305 values less than (unix_timestamp ('2017-06-01 ')),
22. PARTITION p201306 values less than (unix_timestamp ('2017-07-01 ')),
23. PARTITION p201307 values less than (unix_timestamp ('2017-08-01 ')),
24. PARTITION p201308 values less than (unix_timestamp ('2017-09-01 ')),
25. PARTITION p201309 values less than (unix_timestamp ('2017-10-01 ')),
26. PARTITION p201310 values less than (unix_timestamp ('2017-11-01 ')),
27. PARTITION p201311 values less than (unix_timestamp ('2017-12-01 ')),
28. PARTITION p201312 values less than (unix_timestamp ('2017-01-01 ')),
29. PARTITION p201401 values less than (unix_timestamp ('2017-02-01 '))
30. );

 

Note: 

 

 

1. primary key and unique key must be included in part of the partition key. Otherwise, an ERROR 1503 (HY000) "ERROR will be reported when the primary key and unique index are created.

Mysql> create unique index idx_employees‑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'. 'ployees1 'add primary key ('id ');
ERROR 1503 (HY000): a primary key must include all columns in the table's partitioning function

2. You can only append a partition to a range partition after the maximum value.
3. the engine of all partitions must be the same
4. Range partition fields: integer, value expression, date column, date function expression (such as year (), to_days (), to_seconds (), unix_timestamp ())

 

After importing the old table data to the new table, we can see that the data in the new table is distributed to different zones!

 

Maintenance command:

Add Partition

SQL code

1. Alter table xxxxxxx add partition (partition p0 values less than (1991); // only partitions greater than the partition key can be added

 

 

Delete Partition

SQL code

1. Alter table xxxxxxx drop partition p0; // you 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 <'2017-01-01 'or (separated> = '2017-01-01' and separated <'2017-01-01 ');

 

 

Redefinition of partitions (including renaming a partition, moving data along with it, and 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 and re-partition

SQL code

1. Alter table xxxxxx rebuild partition pm1/all; // equivalent to dropping all records, and then reinsert; can solve disk fragments

Optimization table

SQL code

1. Alter table tt2 optimize partition pm1; // after a large number of delete table data, space and fragment can be reclaimed. However, it is supported after 5.5.30. You can use recreate + analyze Before 5.5.30.

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

1. Show create table employees2; // view Partition table Definitions
2. Show table status like 'ployees2' \ G; // when viewing a table, the partition table is shown as "Create_options: partitioned"
3. Select * from information_schema.KEY_COLUMN_USAGE where table_name = 'ployees2'; // view the index
4. SELECT * FROM information_schema.partitions WHERE table_name = 'ployees2' // view the Partition Table
5. Explain partitions select * from employees2 where separated <'2017-01-01 'or separated> '2017-01-01'; // check whether the partition is used by the select statement.