A partitioned note on a historical data table on the line MySQL

Tag: A partitioned note for a historical data table on a line MySQL

Background:

A historical database on the line, business feedback often encounters a range of queries that cause the CPU to soar rapidly. After getting the SQL they provided, SQL resembles the following:

SELECT * from ' order_his ' where ' xxxx ' = ' 222 ' and ' xxxx ' <> 1 and order_time > ' 2016-11-01 00:00:00 ' and order_t IME < ' 2017-06-01 00:00:00 ' \g

Explain looked at the discovery is basically full table scan, the efficiency is too low, and they are all monthly query, so we have this table by month partition, we can greatly reduce the number of scanned rows.

Note: Timestamp types of columns can only be partitioned based on the Unix_timestamp function, remember!

The original Order_his table is similar to the following structure:

CREATE TABLE ' Order_his ' (

' id ' int (one) not NULL auto_increment,

' Order_time ' timestamp null DEFAULT NULL,

' Pay_time ' timestamp null DEFAULT NULL,

' Create_time ' timestamp null DEFAULT NULL,

' Update_time ' timestamp null DEFAULT NULL,

PRIMARY KEY (' id '),

) Engine=innodb auto_increment=47603581 DEFAULT Charset=utf8;

STEP0 Create a table structure with the same TMP table as the original table

CREATE table ' order_his_tmp ' like ' order_his ';

Step1 Modify the original primary key and add the partition key to the primary key.

ALTER TABLE ' order_his_tmp ' drop primary Key,add primary key (Id,order_time);

The partition key must be added to the primary key, or STEP2 will also give an error to remind you to do so.

Step2 Partitioning operations

ALTER TABLE ' order_his_tmp ' PARTITION by RANGE (Unix_timestamp (order_time))

(

PARTITION P201601 VALUES Less THAN (unix_timestamp (' 2016-02-01 ')),

PARTITION P201602 VALUES Less THAN (unix_timestamp (' 2016-03-01 ')),

PARTITION P201603 VALUES Less THAN (unix_timestamp (' 2016-04-01 ')),

PARTITION P201604 VALUES Less THAN (unix_timestamp (' 2016-05-01 ')),

PARTITION P201605 VALUES Less THAN (unix_timestamp (' 2016-06-01 ')),

PARTITION P201606 VALUES Less THAN (unix_timestamp (' 2016-07-01 ')),

PARTITION P201607 VALUES Less THAN (unix_timestamp (' 2016-08-01 ')),

PARTITION P201608 VALUES Less THAN (unix_timestamp (' 2016-09-01 ')),

PARTITION P201609 VALUES Less THAN (unix_timestamp (' 2016-10-01 ')),

PARTITION P201610 VALUES Less THAN (unix_timestamp (' 2016-11-01 ')),

PARTITION P201611 VALUES Less THAN (unix_timestamp (' 2016-12-01 ')),

PARTITION P201612 VALUES Less THAN (unix_timestamp (' 2017-01-01 ')),

PARTITION P201701 VALUES Less THAN (unix_timestamp (' 2017-02-01 ')),

PARTITION P201702 VALUES Less THAN (unix_timestamp (' 2017-03-01 ')),

PARTITION P201703 VALUES Less THAN (unix_timestamp (' 2017-04-01 ')),

PARTITION P201704 VALUES Less THAN (unix_timestamp (' 2017-05-01 ')),

PARTITION P201705 VALUES Less THAN (unix_timestamp (' 2017-06-01 ')),

PARTITION P201706 VALUES Less THAN (unix_timestamp (' 2017-07-01 '))

);

Step3, pouring data from the original table into the new TMP table

Insert INTO ' order_his_tmp ' select * from ' order_his ';

STEP4, query validation

Explain partitions select * from ' order_his_tmp ' where ' xxxx ' = ' 222 ' and ' xxxx ' <> 1 and order_time > ' 2015-11-01 00:00:00 ' and Order_time < ' 2015-12-21 00:00:00 ' \g

1. Row ***************************

Id:1

Select_type:simple

Table:order_his

partitions:p201511,p201512 # # # can be seen here walking is 2015 11 and December, these 2 partitions

........... Part of the content omitted .....

Note: At the time of the online operation, it was found that even if the partition was done, the execution plan showed that all the whole table was scanned, and then the index of this select was added to solve the problem. There is no real environment here to map out.

STEP5, replacing the original table

Notify development students not to perform query operations on the ' order_his ' table at this moment.

Then we execute:

Rename table ' Order_his ' Order_his_nopart ';

Rename table ' order_his_tmp ' order_his ';

In this case, the new 'order_his ' table is the partition table.

STEP6, adding a partition table

Later, if you need to add a partition, just perform the following operation

ALTER TABLE ' order_his ' ADD PARTITION (PARTITION P201707 VALUES less THAN (unix_timestamp (' 2017-08-01 '));

Of course, if we want to save time, we can create many more partitions at once in Step2 (I was built on a monthly basis and created until 2019)

。

In addition, you can also write a stored procedure with Event_schedule to automatically create a new partition each month.

The method of using stored procedures is omitted here first and later supplemented.

This article is from the "Vegetable Chicken" blog, please be sure to keep this source http://lee90.blog.51cto.com/10414478/1917933

A partitioned note on a historical data table on the line MySQL