Shulou(Shulou.com)06/01 Report--

This article is mainly about a historical data table partition of online MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on online MySQL of a historical data table partition can bring you some practical help.

Background:

In an online historical database, business feedback often encounters a scope query that leads to a rapid surge in CPU. After getting the SQL provided by them, the SQL looks like the following:

Select * from `order_ his` where `xxxx` = '222' AND `XXXX` 1 AND order_time >' 2016-11-01 00 select

< '2017-06-01 00:00:00' \G explain看了下发现基本上是全表扫描了，效率太低了，并且他们都是按月查询的，因此我们就对这张表按月进行分区，就能大大减少扫描的行数。 注意：TIMESTAMP类型的列，只能基于UNIX_TIMESTAMP函数进行分区，切记！ ### 原始order_his表类似如下这种结构： CREATE TABLE `order_his` ( `id` int(11) 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 创建一个表结构和原先的表一样的tmp表 create table `order_his_tmp` like `order_his`; step1 修改原有的主键，将分区键添加到主键里。 alter table `order_his_tmp` drop primary key,add primary key(id,order_time); 必须把分区键加到主键里面，不然step2也会报错提醒你这样做的。 step2 分区操作 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、将原先表的数据灌入新的tmp表 insert into `order_his_tmp` select * from `order_his`; step4、查询验证 explain partitions select * from `order_his_tmp` where `xxxx` = '222' AND `XXXX` 1 AND order_time >

'2015-11-01 00 AND order_time <' 2015-12-21 00 VR 00'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: order_his

Partitions: p201511 p201512 # # you can see that November and December 2015 are the two divisions here.

. Some of the contents are omitted.

Note: when operating online at that time, I found that even if the partition was done, the full table scan of ALL was shown in the execution plan, so an index was added to solve this problem according to this SELECT. There is no real environment where it is difficult to map.

Step5, replace the previous table

Inform the developer students not to query the `order_ his` table at this time.

Then we execute:

Rename table `order_ his` to `order_his_ nopart`

Rename table `order_his_ tmp` to `order_ his`

In that case, the new `order_ his` table is the partition table.

Step6, add Partition Table

If you need to add a partition later, you can add a new partition simply by doing the following

ALTER TABLE `order_ his` ADD PARTITION (PARTITION P201707 VALUES LESS THAN (UNIX_TIMESTAMP ('2017-08-01')

Of course, if we want to save trouble, we create a lot of partitions at a time during step2 (I built partitions on a monthly basis until 2019).

Online MySQL a historical data table partition will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.