MySQLCOLUMNS分區(qū)-創(chuàng)新互聯(lián)

介紹

COLUMN分區(qū)是5.5開始引入的分區(qū)功能，只有RANGE COLUMN和LIST COLUMN這兩種分區(qū)；支持×××、日期、字符串；RANGE和LIST的分區(qū)方式非常的相似。

成都創(chuàng)新互聯(lián)公司技術(shù)團隊10年來致力于為客戶提供網(wǎng)站設(shè)計制作、成都網(wǎng)站設(shè)計、成都品牌網(wǎng)站建設(shè)、成都全網(wǎng)營銷、搜索引擎SEO優(yōu)化等服務(wù)。經(jīng)過多年發(fā)展，公司擁有經(jīng)驗豐富的技術(shù)團隊，先后服務(wù)、推廣了上千家網(wǎng)站，包括各類中小企業(yè)、企事單位、高校等機構(gòu)單位。

COLUMNS和RANGE和LIST分區(qū)的區(qū)別

1.針對日期字段的分區(qū)就不需要再使用函數(shù)進行轉(zhuǎn)換了，例如針對date字段進行分區(qū)不需要再使用YEAR()表達式進行轉(zhuǎn)換。

2.COLUMN分區(qū)支持多個字段作為分區(qū)鍵但是不支持表達式作為分區(qū)鍵。

COLUMNS支持的類型

×××支持：tinyint,smallint,mediumint,int,bigint;不支持decimal和float

時間類型支持：date,datetime

字符類型支持：char,varchar,binary,varbinary;不支持text,blob

一、RANGE COLUMNS分區(qū)

1.日期字段分區(qū)

CREATE TABLE members (
    id INT,
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION a VALUES LESS THAN ('1960-01-01'),
    PARTITION b VALUES LESS THAN ('1970-01-01'),
    PARTITION c VALUES LESS THAN ('1980-01-01'),
    PARTITION d VALUES LESS THAN ('1990-01-01'),
    PARTITION e VALUES LESS THAN MAXVALUE
);

1.插入測試數(shù)據(jù)

insert into members(id,joined) values(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');

2.查詢分區(qū)數(shù)據(jù)分布

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='members';

 當前有5個分區(qū)只插入了4條記錄，其中C分區(qū)是沒有記錄的，結(jié)果和實際一樣。

3.分析執(zhí)行計劃

explain select id,joined from tb_partition.members where joined=YEAR(now());
explain select id,joined from tb_partition.members where joined='1963-01-01';

第一條查詢使用了函數(shù)導(dǎo)致查詢沒有走具體的分區(qū)而是掃描的所有的分區(qū)，而第二條查詢執(zhí)行語句查找具體的分區(qū)。

2.多個字段組合分區(qū)

CREATE TABLE rcx (
    a INT,
    b INT
    )
PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),
     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (15,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

注意：多字段的分區(qū)鍵比較是基于數(shù)組的比較。它先用插入的數(shù)據(jù)的第一個字段值和分區(qū)的第一個值進行比較，如果插入的第一個值小于分區(qū)的第一個值那么就不需要比較第二個值就屬于該分區(qū)；如果第一個值等于分區(qū)的第一個值，開始比較第二個值同樣如果第二個值小于分區(qū)的第二個值那么就屬于該分區(qū)。

例如：

insert into rcx(a,b)values(1,20),(10,15),(10,30);

第一組值：(1,20);1<5所以不需要再比較20了，該記錄屬于p0分區(qū)。

第二組值:(10,15)，10>5,10=10且15<20，所以該記錄屬于P1分區(qū)

第三組值:(10,30),10=10但是30>20，所以它不屬于p1,它滿足10<15所以它屬于p2

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='rcx';

 注意：RANGE COLUMN的多列分區(qū)第一列的分區(qū)值一定是順序增長的，不能出現(xiàn)交叉值，第二列的值隨便，例如以下分區(qū)就會報錯

PARTITION BY RANGE COLUMNS(a,b) (
     PARTITION p0 VALUES LESS THAN (5,10),     PARTITION p1 VALUES LESS THAN (10,20),
     PARTITION p2 VALUES LESS THAN (8,30),
     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

由于分區(qū)P2的第一列比P1的第一列要小，所以報錯，后面的分區(qū)第一列的值一定要比前面分區(qū)值要大，第二列沒規(guī)定。

二、LIST COLUMNS分區(qū)

1.非×××字段分區(qū)

CREATE TABLE listvar (
    id INT NOT NULL,
    hired DATETIME NOT NULL)
PARTITION BY LIST COLUMNS(hired) 
(
    PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),
    PARTITION b VALUES IN ('1992-01-01 10:00:00'),
    PARTITION c VALUES IN ('1993-01-01 10:00:00'),
    PARTITION d VALUES IN ('1994-01-01 10:00:00')
);ALTER TABLE listvar ADD INDEX ix_hired(hired);INSERT INTO listvar() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00'),(1,'1993-01-01 10:00:00');

LIST COLUMNS分區(qū)對分×××字段進行分區(qū)就無需使用函數(shù)對字段處理成×××，所以對非×××字段進行分區(qū)建議選擇COLUMNS分區(qū)。

EXPLAIN SELECT * FROM listvar WHERE hired='1990-01-01 10:00:00';

 2.多字段分區(qū)

CREATE TABLE listvardou (
    id INT NOT NULL,
    hired DATETIME NOT NULL)
PARTITION BY LIST COLUMNS(id,hired) 
(
    PARTITION a VALUES IN ( (1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00') ),
    PARTITION b VALUES IN ( (2,'1992-01-01 10:00:00') ),
    PARTITION c VALUES IN ( (3,'1993-01-01 10:00:00') ),
    PARTITION d VALUES IN ( (4,'1994-01-01 10:00:00') )
);ALTER TABLE listvardou ADD INDEX ix_hired(hired);INSERT INTO listvardou() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(2,'1992-01-01 10:00:00'),(3,'1993-01-01 10:00:00');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='listvardou';

EXPLAIN SELECT * FROM listvardou WHERE id=1 and hired='1990-01-01 10:00:00';

由于分區(qū)是組合字段，filtered只有50%，對于組合分區(qū)索引也最好是建組合索引，其實如果能通過id字段刷選出數(shù)據(jù)，單獨建id字段的索引也是有效果的，但是組合索引的效果是最好的，其實和非分區(qū)鍵索引的概念差不多。

ALTER TABLE listvardou ADD INDEX ix_hired1(id,hired);

備注：文章中的示例摘自mysql官方參考手冊

三、移除表的分區(qū)

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分區(qū)是僅僅移除分區(qū)的定義，并不會刪除數(shù)據(jù)和drop PARTITION不一樣，后者會連同數(shù)據(jù)一起刪除

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機、免備案服務(wù)器”等云主機租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

新聞標題：MySQLCOLUMNS分區(qū)-創(chuàng)新互聯(lián)
當前URL：http://bm7419.com/article8/gdcip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、網(wǎng)站營銷、動態(tài)網(wǎng)站、建站公司、App開發(fā)、網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)