mysql 表分區 按天分區，mysql天分區

mysql 表分區 按天分區，mysql天分區
MySQL表分區就是把一張表根據設定好的條件下把表切分成若干個小表相互之間，在MySQL的5.1版本以後就開始支援表分區的功能，在使用表的分區後會使MySQL中大表在平時查詢統計時效能提升。使用MySQL的表分區有以下這些優點：

 

1.與單個磁碟或檔案系統分區相比，可以儲存更多的資料

2.很容易就能刪除不用或者過時的資料

3.一些查詢可以得到極大的最佳化

4.涉及到 SUM()/COUNT() 等彙總函式時，可以並行進行

5.IO輸送量更大

在早期的MySQL版本中可以查詢SHOW VARIABLES LIKE 'have_partitioning';變數參數來得知系統中是否會支援表分區而在MySQL5.6的版本後就不在使用該參數變數，預設都是支援資料分割函數，但是並不是所有的資料引擎都支援表分區的，其中目前可以使用表發資料分割函數引擎有：InnoDB、MyISAM、MEMORY另外還有BLACKHOLE引擎也是支援分區，但是BLACKHOLE引擎中的資料一切為空白所以在使用分區並沒有實際的意義，而使用較為多的就是InnoDB和MyISAM的引擎表上做分區，在這裡需要注意的是在使用的時候需要注意在原先是什麼表引擎分區的時候就必須是用什麼表引擎，其次在使用InnoDB引擎表時建議開啟innodb_file_per_table（獨立資料表空間）這樣資料表空間檔案也是獨立的便於管理，如果是在建立表分區時沒有指定分區所在的路徑預設是在datedir目錄下的庫檔案夾下把表的資料檔案下切分建立出許多小檔案，當然在建表或修改表的時候如果有指定表檔案的資料目錄那麼還可以使一張表分區後把資料放在不同的磁碟中，最後在使用的做分區的欄位上需要定義成主鍵，如果原先有一個主鍵那麼該處就會結合之前的主鍵形成聯合主鍵，同時在該欄位上因為主鍵原因該欄位應該不為NULL，建議在建立表後再ALTER表添加修改表分區，這樣可以確保修改建立表分區時不容易出錯，因為在MySQL中表分區不同於Oracle中可以在建表後分區，分區時就會按照所給出的條件把資料劃分至各個分區檔案中，當然在MySQL中分區後還可以在分區的基礎上再進行子分區，但是一般情況下一般很少使用，最後需要注意的一點如果刪除分區時，也會遺失資料的，所以在刪除表分區時需要謹慎。其中在MySQL下的分區類型有以下四種：

 

RANGE分區：基於屬於一個給定連續區間的列值，把多行分配給分區。

LIST分區：類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇。

HASH分區：基於使用者定義的運算式的返回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效、產生非負整數值的任何錶達式。

KEY分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含整數值。

其中我們比較常用的就是分區方式就是按欄位中的時間來分區那麼就選用RANGE分區方式來進行分區在這裡就說一說最常用的使用時間條件來進行表分區，按時間分區可以按照年月日等條件用來分區，因為是按時間條件分區所以在這裡就選用RANGE分區，在這裡分區的切條件判斷有三種：

 

LESS THAN：如果是數值就是小於等於，時間則是小於

LESS THAN MAX:不等於

IN:包含於某某區間

而切分時的條件可以用day()、to_days()等相關時間函數都可以，需要注意的是必須是返回值是整形的。如下有一張表

 

其中按時間欄位date按天分區，首先需要把date欄位改成主鍵，後添加分區切分規則

 

ALTER TABLE `employee_tbl`

DROP PRIMARY KEY,

ADD PRIMARY KEY (`id`, `date`); -- 之前id是主鍵，所以這裡第2主鍵為聯合主鍵

 

ALTER TABLE `employee_tbl` ADD PRIMARY KEY (`date`);

-- 這裡為了便於管理分區名都已“p時間”來命名

ALTER TABLE `employee_tbl` PARTITION BY RANGE (to_days(date)) (

    PARTITION `p20171101` VALUES LESS THAN (to_days('20171101')),

    PARTITION `p20171102` VALUES LESS THAN (to_days('20171102')),

    PARTITION `p20171103` VALUES LESS THAN (to_days('20171103')),

    PARTITION `p20171104` VALUES LESS THAN (to_days('20171104')),

    PARTITION `p20171105` VALUES LESS THAN (to_days('20171105')),

    PARTITION `p20171106` VALUES LESS THAN (to_days('20171106')),

    PARTITION `p20171107` VALUES LESS THAN (to_days('20171107')),

    PARTITION `p20171108` VALUES LESS THAN (to_days('20171108')),

    PARTITION `p20171109` VALUES LESS THAN (to_days('20171109')),

    PARTITION `p20171110` VALUES LESS THAN (to_days('20171110'))

);

這樣按天進行分區就創完畢，當然如果感覺不夠滿意還可以重新分區,後期添加分區也很簡單：

 

ALTER TABLE employee_tbl ADD PARTITION (PARTITION p20171111 VALUES LESS THAN (TO_DAYS ('2017-11-11')));

刪除分區：

 

ALTER TABLE employee_tbl DROP PARTITION p20171101;

最後我們可以通過查詢MySQL的系統字典庫得知所有的分區詳情資訊

 

SELECT

    *

FROM

    information_schema. PARTITIONS t

WHERE

    t.PARTITION_NAME IS NOT NULL

在分區建立後可以通過過程和事件控制自動增加表分區。

頂

0

踩

0

    查看評論