MySQL data table range partition example

With the rapid expansion of data volume in the database, the data volume of some industries is growing rapidly, and the efficiency of database inserting and querying is getting lower and smaller. At this point, in addition to the program code and query statements, but also to make some changes in the structure of the database, in a primary read-write database, when the data table data more than 1000w rows, the query efficiency is really very maddening. Even if the index was built earlier, it is difficult to meet the user experience of system query efficiency.

The optimization scheme is a table or partition. As for the principle of partitioning and the difference between partition and table, search, are introduced in detail, here is not redundant introduction. Simply put, the sub-table is designed to improve the concurrency of the database, the partition is designed to optimize the disk's IO and data read and write, so what to use, but also based on business to reconsider. Because of the low concurrency requirements of our system, partitioning is used.

Partitions are implemented after MySQL5.1. The partition type has a range partition, a list partition, a hash partition, and a key partition. We use the Range section to explain this.

One thing to note about partitioning is that either you do not define a primary key or you add a partition field to the primary key. and the partition field cannot be null, otherwise it is difficult to determine the partition range. So set to not NULL.

First execute the show plugins; View partition Whether this column is active or not, indicates that the database supports partitions.

1. Create a data table and partition:

CREATE TABLE ' table_name ' (

' ID ' INT (one) not NULL auto_increment,

' UID ' VARCHAR (+) DEFAULT NULL,

' Action ' VARCHAR DEFAULT NULL,

' Channel ' VARCHAR DEFAULT NULL,

' Count_left ' INT (one) DEFAULT NULL,

' End_time ' INT (one) DEFAULT ' 0 ',

PRIMARY KEY (' id ', ' end_time '),

KEY ' time ' (' End_time ')

) Engine=myisam DEFAULT Charset=utf8

PARTITION by RANGE (' End_time ') (

PARTITION p161130 VALUES less THAN (1480550399),

PARTITION p161231 VALUES less THAN (1483228799),

PARTITION p170131 VALUES less THAN (1485907199),

PARTITION p170228 VALUES less THAN (1488326399),

PARTITION p170331 VALUES less THAN (1491004799),

PARTITION p170430 VALUES less THAN (1493596799),

PARTITION p170531 VALUES less THAN (1496275199),

PARTITION p170631 VALUES less THAN (1498867199),

PARTITION pnow VALUES less THAN MAXVALUE

);

2. Modify a data table partition:

ALTER TABLE ' table_name '

PARTITION by RANGE (' End_time ') (

PARTITION p161130 VALUES less THAN (1480550399),

PARTITION p161231 VALUES less THAN (1483228799),

PARTITION p170131 VALUES less THAN (1485907199),

PARTITION p170228 VALUES less THAN (1488326399),

PARTITION p170331 VALUES less THAN (1491004799),

PARTITION p170430 VALUES less THAN (1493596799),

PARTITION p170531 VALUES less THAN (1496275199),

PARTITION p170631 VALUES less THAN (1498867199),

PARTITION pnow VALUES less THAN MAXVALUE

);

Note: 1, 2 uses End_time (time is recorded as a timestamp) to partition the table as a partition field. The partition's distinguished value is the timestamp of the time in the partition name, such as 2016/11/30 23:59:59 to 1480550399 seconds. In the above code, I divided the data table into 9 districts, from November 30, 16 to June 31, 17 a total of 8 districts plus pnow this area to store after June 31, 17 data, as shown above, before the November 30, 16 data, The data that will be stored in the p161130 partition will be stored in the p161231 partition from December 01, 16 to December 31, 16, etc.

After partitioning, you can perform the following statements to see the effect (you can also use it later to see how much data is in each partition):

SELECT partition_name,table_rows from INFORMATION_SCHEMA. partitions WHERE table_name = ' table_name ';

3. Delete a partition:

Execute statement: ALTER TABLE table_name DROP PARTITION p_name;

Note: when a partition is deleted, all data within that partition is also deleted;

If you use this to delete data, use the DELETE from table_name where ... To be much more effective;

4. Add a partition:

Execute statement: ALTER TABLE table_name ADD PARTITION (PARTITION p_name VALUES less THAN (xxxxxxxxx));

Note: If the original last partition is partition Pnow VALUES less THAN MAXVALUE; Then you should delete the partition first, then execute the new partition statement, and then add it back to the partition;

MySQL data table range partition example