深入淺析MySQL COLUMNS分區(qū)
介紹
COLUMN分區(qū)是5.5開始引入的分區(qū)功能，只有RANGE COLUMN和LIST COLUMN這兩種分區(qū)；支持整形、日期、字符串；RANGE和LIST的分區(qū)方式非常的相似。
COLUMNS和RANGE和LIST分區(qū)的區(qū)別
1.針對日期字段的分區(qū)就不需要再使用函數(shù)進(jìn)行轉(zhuǎn)換了，例如針對date字段進(jìn)行分區(qū)不需要再使用YEAR()表達(dá)式進(jìn)行轉(zhuǎn)換。
2.COLUMN分區(qū)支持多個字段作為分區(qū)鍵但是不支持表達(dá)式作為分區(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';
當(dāng)前有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ū)的第一個值進(jìn)行比較，如果插入的第一個值小于分區(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ū)對分整形字段進(jìn)行分區(qū)就無需使用函數(shù)對字段處理成整形，所以對非整形字段進(jìn)行分區(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ù)一起刪除
總結(jié)
RANGE COLUMNS和LIST COLUMNS分區(qū)其實是RANG和LIST分區(qū)的升級，所以可以直接使用COLUMN分區(qū)。注意COLUMNS分區(qū)不支持timestamp字段類型。
以上所述是小編給大家介紹的MySQL COLUMNS分區(qū)，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！
相關(guān)文章
MYSQL(電話號碼,身份證)數(shù)據(jù)脫敏的實現(xiàn)
在日常開發(fā)需求中會經(jīng)常遇到數(shù)據(jù)脫敏處理，比如身份證號、手機(jī)號，需要使用*進(jìn)行部分替換顯示。這樣能使敏感隱私信息在一定程度上得到保護(hù)。本文就來介紹一下2021-05-05
在本篇文章里小編給大家整理的是一篇關(guān)于mysql中關(guān)于覆蓋索引的知識點總結(jié)內(nèi)容，有需要的朋友們學(xué)習(xí)參考下。2020-08-08
這篇文章主要為大家詳細(xì)介紹了Windows免安裝MySQL8.0.28版本圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-04-04
這篇文章主要介紹了MySQL ifnull的嵌套使用說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-06-06
這篇文章主要介紹了mysql如何將一個字段賦值給另一個字段，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-09-09
這篇文章主要介紹了vue?axios二次封裝的詳細(xì)解析，文章圍繞主題展開詳細(xì)的內(nèi)容戒殺，具有一定的參考價值，需要的小伙伴可以參考一下2022-09-09
這篇文章主要介紹了mysql視圖原理與用法,結(jié)合實例形式分析了mysql視圖的概念、原理、創(chuàng)建、使用方法及相關(guān)注意事項,需要的朋友可以參考下2018-04-04
一文詳解MYSQL的多版本并發(fā)控制MVCC(Multi-Version Concurrency Co
MVCC是一種用于數(shù)據(jù)庫管理系統(tǒng)的并發(fā)控制技術(shù),允許多個事務(wù)同時訪問數(shù)據(jù)庫,而不會導(dǎo)致讀寫沖突,本文就詳細(xì)的介紹了MVCC的具體用法,具有一定的參考價值,感興趣的可以了解一下2023-10-10
最近在工作中遇到一個問題，現(xiàn)將解決的方法分享出來供大家參考學(xué)習(xí)，下面這篇文章主要給大家介紹了關(guān)于MySQL啟動時InnoDB引擎被禁用了的解決方法，需要的朋友們下面跟著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2017-08-08
這篇文章主要介紹了Windows7中配置安裝MySQL 5.6解壓縮版的方法以及安裝過程中遇到的問題及解決方法，這里推薦給有需要的小伙伴2014-12-12