MySQL分區(qū)之RANGE分區(qū)詳解
介紹
RANGE分區(qū)基于一個(gè)給定的連續(xù)區(qū)間范圍，早期版本RANGE主要是基于整數(shù)的分區(qū)。在5.7版本中DATE、DATETIME列也可以使用RANGE分區(qū)，同時(shí)在5.5以上的版本提供了基于非整形的RANGE COLUMN分區(qū)。RANGE分區(qū)必須的連續(xù)的且不能重疊。使用
“VALUES LESS THAN ()”來(lái)定義分區(qū)區(qū)間,非整形的范圍值需要使用單引號(hào)，并且可以使用MAXVALUE作為分區(qū)的最高值。
一、RANGE分區(qū)
1.創(chuàng)建分區(qū)
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
alter table employees add index ix_store_id(store_id) ;alter table employees add index ix_job_code(job_code) ;p0:指分區(qū)p0,這個(gè)分區(qū)名的取值可以隨便取值只要同一個(gè)表里面的每個(gè)分區(qū)名不重復(fù)即可，也不需要非得從0開(kāi)始，你也可以使用a、b、c、d。
THEN():分區(qū)的范圍值，這個(gè)值只能的連續(xù)不重疊的從小到大的值。
2.性能分析
插入測(cè)試數(shù)據(jù)
insert into employees(id,job_code,store_id) values(1,1001,1),(2,1002,2),(3,1003,3),(4,1004,4);
從執(zhí)行計(jì)劃可以看到兩個(gè)查詢都用到了分區(qū)的效果；如果細(xì)心估計(jì)會(huì)發(fā)現(xiàn)第二個(gè)查詢沒(méi)有走索引，并不是使用小于就不會(huì)走索引而且執(zhí)行計(jì)劃分析評(píng)估任務(wù)不走索引的效果會(huì)更好，事實(shí)卻是如果當(dāng)前查詢整個(gè)分區(qū)的數(shù)據(jù)時(shí)使用索引的話還需要去查詢其它的字段還不如直接掃描整個(gè)分區(qū)來(lái)的快。
3.增加分區(qū)
由于當(dāng)前分區(qū)值的范圍是小于21，當(dāng)向分區(qū)表中插入一個(gè)超過(guò)分區(qū)范圍的值時(shí)會(huì)報(bào)錯(cuò)。這個(gè)時(shí)候可以增加一個(gè)分區(qū)，當(dāng)你不確定需要給一個(gè)多大的上限值時(shí)可以使用MAXVALUE
alter table employees add PARTITION (PARTITION p4 VALUES LESS THAN MAXVALUE);
注意：增加分區(qū)只能在最大端增加
4.刪除分區(qū)
alter table employees drop PARTITION p4;
注意：通過(guò)這種刪除分區(qū)的方式會(huì)將分區(qū)中的數(shù)據(jù)也刪除，慎用?。。?！。但是通過(guò)刪除分區(qū)的方式刪除數(shù)據(jù)會(huì)比delete快很多，因?yàn)樗喈?dāng)于刪除一個(gè)數(shù)據(jù)庫(kù)一樣因?yàn)槊總€(gè)分區(qū)都是一個(gè)獨(dú)立的數(shù)據(jù)文件。用來(lái)刪除歷史分區(qū)數(shù)據(jù)是非常好的辦法。
5.拆分合并分區(qū)
拆分合并分區(qū)統(tǒng)稱為重新定義分區(qū)，拆分分為不會(huì)造成數(shù)據(jù)的丟失，只將會(huì)將數(shù)據(jù)從一個(gè)分區(qū)移動(dòng)到另一個(gè)分區(qū)。
例1：將P0拆分成s1,s2兩個(gè)分區(qū)
ALTER TABLE employees REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (3),
PARTITION s1 VALUES LESS THAN (6)
);注意：原來(lái)分區(qū)p0的范圍是[負(fù)無(wú)窮-6)，所以新拆分的分區(qū)也必須是這范圍，所以新的分區(qū)范圍值最大不能超過(guò)6。
分區(qū)由原來(lái)的p0[-6)變成了so[-3),s1[3-6)，整個(gè)分區(qū)的范圍還是不變。
例2：將s1,p1,p2合并為a,b兩個(gè)分區(qū)
ALTER TABLE employees REORGANIZE PARTITION s1,p1,p2 INTO (
PARTITION a VALUES LESS THAN (5),
PARTITION b VALUES LESS THAN (16)
);原本的s1,p1,p2分區(qū)范圍是：[3-16)所以新的分區(qū)也必須和原本的分區(qū)相同，所以新的分區(qū)的值不能低于3不能高于16即可。
分區(qū)由原來(lái)的s1[3-6),p1[6-11),p2[11-16)變成了現(xiàn)在的a[3-5),b[5-16),總的范圍沒(méi)有發(fā)生變化
注意：無(wú)論是拆分還是合并分區(qū)都不能改變分區(qū)原本的覆蓋范圍，并且合并分區(qū)只能合并連續(xù)的分區(qū)不能跳過(guò)分區(qū)合并；并且不能改變分區(qū)的類型，例如不能把range分區(qū)改成key分區(qū)等。
二、日期字段分區(qū)方法
注意：RANG分區(qū)針對(duì)日期字段進(jìn)行分區(qū)可以使用時(shí)間類型的函數(shù)進(jìn)行轉(zhuǎn)換成整形，但是如果你的查詢語(yǔ)句需要利用分區(qū)那么查詢語(yǔ)句也需要使用相同的時(shí)間函數(shù)進(jìn)行查詢。
1.使用YEAR()函數(shù)進(jìn)行分區(qū)
CREATE TABLE employees1 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);插入測(cè)試數(shù)據(jù)
insert into employees1(id,separated,job_code,store_id) values(1,'1990-03-04',1001,1),(2,'1995-03-04',1002,2),(3,'1998-03-04',1003,3),(4,'2016-03-04',1004,4);
對(duì)于日期字段分區(qū)，查詢條件使用> 、< 、betnwen、=都會(huì)利用分區(qū)查詢,如果條件使用函數(shù)轉(zhuǎn)換則不會(huì)走分區(qū)，比如使用YEAR()。
2.TIMESTAMP類型的列的分區(qū)方法
針對(duì)TIMESTAMP的日期類型的字段需要使用專門(mén)的UNIX_TIMESTAMP（）函數(shù)進(jìn)行轉(zhuǎn)換
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);三、null值處理
當(dāng)往分區(qū)列中插入null值RANG 分區(qū)會(huì)將其當(dāng)作最小值來(lái)處理即插入最小的分區(qū)中
CREATE TABLE test (
id INT NOT NULL,
store_id INT
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
insert into test(id,store_id) values(1,null);SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='test';
備注：文章中的示例摘自mysql官方參考手冊(cè)
四、移除表的分區(qū)
ALTER TABLE tablename REMOVE PARTITIONING ;
注意：使用remove移除分區(qū)是僅僅移除分區(qū)的定義，并不會(huì)刪除數(shù)據(jù)和drop PARTITION不一樣，后者會(huì)連同數(shù)據(jù)一起刪除
分區(qū)系列文章：
RANGE分區(qū)：http://chabaoo.cn/article/244269.htm
COLUMN分區(qū)：http://chabaoo.cn/article/96515.htm
LIST分區(qū)：http://chabaoo.cn/article/244256.htm
HASH分區(qū)：http://chabaoo.cn/article/244277.htm
KEY分區(qū)：http://chabaoo.cn/article/244282.htm
子分區(qū)：http://chabaoo.cn/article/244294.htm
指定各分區(qū)路徑：http://chabaoo.cn/article/244296.htm
分區(qū)索引以及分區(qū)介紹總結(jié)：http://chabaoo.cn/article/244300.htm
總結(jié)
有兩點(diǎn)非常重要需要注意，第一刪除分區(qū)時(shí)要慎重因?yàn)闀?huì)連同分區(qū)里的數(shù)據(jù)一并刪除，拆分合并分區(qū)新的分區(qū)一定要和原來(lái)的分區(qū)的范圍一致。RANGE COLUMN分區(qū)單獨(dú)用章節(jié)進(jìn)行講解，。
到此這篇關(guān)于MySQL分區(qū)之RANGE分區(qū)的文章就介紹到這了,更多相關(guān)MySQL RANGE分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
分享一下Mysql常見(jiàn)的幾個(gè)錯(cuò)誤問(wèn)題及解決方法
這篇文章主要為大家分享一下Mysql常見(jiàn)的幾個(gè)錯(cuò)誤問(wèn)題及解決方法,需要的朋友可以參考下2015-08-08
MySql中sql語(yǔ)句執(zhí)行過(guò)程詳細(xì)講解
天天和數(shù)據(jù)庫(kù)打交道,一天能寫(xiě)上幾十條 SQL 語(yǔ)句,但你知道我們的系統(tǒng)是如何和數(shù)據(jù)庫(kù)交互的嗎?下面這篇文章主要給大家介紹了關(guān)于MySql中sql語(yǔ)句執(zhí)行過(guò)程詳細(xì)講解的相關(guān)資料,需要的朋友可以參考下2023-02-02
這篇文章主要為大家詳細(xì)介紹了linux上mysql安裝詳細(xì)教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化
今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-01-01
MySQL數(shù)據(jù)庫(kù)分區(qū)功能的使用教程
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)分區(qū)功能的使用教程,文中特別講解了MySQL分表和分區(qū)的區(qū)別以及聯(lián)系,需要的朋友可以參考下2016-05-05
這篇文章主要給大家介紹了關(guān)于MySQL查看和修改時(shí)區(qū)的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08
這篇文章主要介紹了mysql請(qǐng)求阻塞問(wèn)題解析,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2023-10-10
Mysql查詢最近一條記錄的sql語(yǔ)句(優(yōu)化篇)
這篇文章主要介紹了Mysql查詢最近一條記錄的sql語(yǔ)句，非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友參考下吧2018-05-05
Linux手動(dòng)部署遠(yuǎn)程的mysql數(shù)據(jù)庫(kù)的方法詳解
這篇文章主要介紹了Linux手動(dòng)部署遠(yuǎn)程的mysql數(shù)據(jù)庫(kù)的方法詳解,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-11-11
MySQL中查看數(shù)據(jù)庫(kù)安裝路徑的方法
有時(shí)候在我們開(kāi)發(fā)的過(guò)程中并不一定記得數(shù)據(jù)庫(kù)的安裝路徑，比如要查看mysql 數(shù)據(jù)庫(kù)的安裝目錄在哪里，這里就為大家分享一下2021-03-03