詳細介紹線上MySQL某個歷史數據表分區

本文主要給大家簡單講講線上MySQL某個歷史數據表分區，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望線上MySQL某個歷史數據表分區這篇文章可以給大家帶來一些實際幫助。

背景：

    線上的一個歷史數據庫，業務方反饋經常遇到一個范圍查詢就導致CPU迅速飆升的情況。拿到他們提供的SQL后，SQL類似下面這種：

select * from `order_his` where `xxxx` = '222' AND `XXXX` <> 1 AND order_time > '2016-11-01 00:00:00' AND order_time < '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: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   ### 可以看到這里走的是2015年11和12月，這2個分區

...........部分內容省略.............

注意： 當時在線上操作的時候，發現即使做了分區，執行計劃里面顯示的還是ALL全表掃描了，于是根據這個SELECT 加了個索引解決了這個問題。這里沒有真實環境不好貼圖出來。

step5、替換原先的表

通知開發同學當前不要對`order_his`表執行查詢操作。

然后我們執行：

rename table `order_his` to `order_his_nopart`;

rename table `order_his_tmp` to `order_his`;

這樣的話，新的`order_his`表就是分區表啦。

step6、添加分區表

后期如果需要加分區的話，只要執行如下這種操作就可以添加一個新的分區

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

當然，如果我們想省事的話，就在step2的時候，一次性多創建很多分區（我當時是按月建分區，一直創建到2019年）

線上MySQL某個歷史數據表分區就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。