mysql中分區(qū)怎么刪 mysql 刪除分區(qū)

mysql之表分區(qū)

分區(qū)介紹：

創(chuàng)新互聯(lián)建站是一家專(zhuān)業(yè)提供龍安企業(yè)網(wǎng)站建設(shè),專(zhuān)注與成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)、HTML5建站、小程序制作等業(yè)務(wù)。10年已為龍安眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專(zhuān)業(yè)網(wǎng)站建設(shè)公司優(yōu)惠進(jìn)行中。

一、什么是分區(qū)？

所謂分區(qū)，就是將一個(gè)表分成多個(gè)區(qū)塊進(jìn)行操作和保存，從而降低每次操作的數(shù)據(jù)，提高性能。而對(duì)于應(yīng)用來(lái)說(shuō)則是透明的，從邏輯上看只有一張表，但在物理上這個(gè)表可能是由多個(gè)物理分區(qū)組成的，每個(gè)分區(qū)都是獨(dú)立的對(duì)象，可以進(jìn)行獨(dú)立處理。

二、分區(qū)作用

1.可以邏輯數(shù)據(jù)分割，分割數(shù)據(jù)能夠有多個(gè)不同的物理文件路徑。

2.可以存儲(chǔ)更多的數(shù)據(jù)，突破系統(tǒng)單個(gè)文件最大限制。

3.提升性能，提高每個(gè)分區(qū)的讀寫(xiě)速度，提高分區(qū)范圍查詢(xún)的速度。

4.可以通過(guò)刪除相關(guān)分區(qū)來(lái)快速刪除數(shù)據(jù)

5.通過(guò)跨多個(gè)磁盤(pán)來(lái)分散數(shù)據(jù)查詢(xún)，從而提高磁盤(pán)I/O的性能。

6.涉及到例如SUM()、COUNT()這樣聚合函數(shù)的查詢(xún)，可以很容易的進(jìn)行并行處理。

7.可以備份和恢復(fù)獨(dú)立的分區(qū)，這對(duì)大數(shù)據(jù)量很有好處。

三、分區(qū)能支持的引擎

MySQL支持大部分引擎創(chuàng)建分區(qū)，入MyISAM、InnoDB等；不支持MERGE和CSV等來(lái)創(chuàng)建分區(qū)。同一個(gè)分區(qū)表中的所有分區(qū)必須是同一個(gè)存儲(chǔ)引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分區(qū)。

四、確認(rèn)MySQL支持分區(qū)

從MySQL5.1開(kāi)始引入分區(qū)功能，可以如下方式查看是否支持：

老版本用：SHOW VARIABLES LIKE '%partition%';

新版本用：show plugins;

五、分區(qū)類(lèi)型

1. RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

例如，可以將一個(gè)表通過(guò)年份劃分成兩個(gè)分區(qū)，2001 -2010年、2011-2020。

2. LIST分區(qū)：類(lèi)似于RANGE分區(qū)，LIST是列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。

比如 根據(jù)字段 把值為1、3、5的放到一起，2、4、6的另外放到一起 等等...

3. HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值來(lái)進(jìn)行計(jì)算，這個(gè)函數(shù)必須產(chǎn)生非負(fù)整數(shù)值。

通過(guò)HASH運(yùn)算來(lái)進(jìn)行分區(qū)，分布的比較均勻

4. KEY分區(qū)：類(lèi)似于按HASH分區(qū)，由MySQL服務(wù)器提供其自身的哈希函數(shù)。

按照KEY進(jìn)行分區(qū)類(lèi)似于按照HASH分區(qū)

六、使用分區(qū)注意事項(xiàng)

1. 如果表中存在primary key 或者 unique key 時(shí)，分區(qū)的列必須是paimary key或者unique key的一個(gè)組成部分，也就是說(shuō)，分區(qū)函數(shù)的列只能從pk或者uk這些key中取子集

2. 如果表中不存在任何的paimary key或者unique key，則可以指定任何一個(gè)列作為分區(qū)列

3. 5.5版本前的RANGE、LIST、HASH分區(qū)要求分區(qū)鍵必須是int；MySQL5.5及以上，支持非整形的RANGE和LIST分區(qū)，即：range columns 和 list columns (可以用字符串來(lái)進(jìn)行分區(qū))。

七、分區(qū)命名

1. 分區(qū)的名字基本上遵循其他MySQL 標(biāo)識(shí)符應(yīng)當(dāng)遵循的原則，例如用于表和數(shù)據(jù)庫(kù)名字的標(biāo)識(shí)符。應(yīng)當(dāng)注意的是，分區(qū)的名字是不區(qū)分大小寫(xiě)的。

2. 無(wú)論使用何種類(lèi)型的分區(qū)，分區(qū)總是在創(chuàng)建時(shí)就自動(dòng)的順序編號(hào)，且從0開(kāi)始記錄。

八、 創(chuàng)建分區(qū)

1. RANGE分區(qū)：

解讀：以上為 uuid小于5時(shí)放到p0分區(qū)下，uuid大于5且小于10放到p1分區(qū)下，uuid大于10且小于15放到p2分區(qū)下，uuid大于15 一直到最大值的存在p3分區(qū)下

2. LIST分區(qū)：

解讀：以上為uuid 等于1/2/3/5時(shí)放到p0分區(qū)，7/9/10放到p1分區(qū)，11/15放到p2分區(qū)。當(dāng)時(shí)用insert into時(shí) 如果uuid的值不存在p0/p1/p2分區(qū)時(shí)，則會(huì)插入失敗而報(bào)錯(cuò)。

3. HASH分區(qū):

HASH分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布。在RANGE分區(qū)和LIST分區(qū)中必須明確指定一個(gè)指定的列值或列值集合以指定應(yīng)該保存在哪個(gè)分區(qū)中。而在HASH分區(qū)中，MySQL會(huì)自動(dòng)完成這些工作，要做的只是基于將要被哈希的列值指定一個(gè)表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量，如：

解讀：MySQL自動(dòng)創(chuàng)建3個(gè)分區(qū)，在執(zhí)行insert into時(shí)，根據(jù)插入的uuid通過(guò)算法來(lái)自動(dòng)分配區(qū)間。

注意：

(1) 由于每次插入、更新、刪除一行，這個(gè)表達(dá)式都要計(jì)算一次，這意味著非常復(fù)雜的表達(dá)式可能會(huì)引起性能問(wèn)題，尤其是在執(zhí)行同時(shí)影響大量行的運(yùn)算（例如批量插入）的時(shí)候。

(2) 最有效率的哈希函數(shù)是只對(duì)單個(gè)表列進(jìn)行計(jì)算，并且它的值隨列值進(jìn)行一致的增大或減小，因?yàn)檫@考慮了在分區(qū)范圍上的“修剪”。也就是說(shuō)，表達(dá)式值和它所基于的列的值變化越接近，就越能有效地使用該表達(dá)式來(lái)進(jìn)行HASH分區(qū)。

3.1：線性HASH分區(qū)

線性HASH分區(qū)在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字。

線性HASH分區(qū)的有點(diǎn)在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有及其大量數(shù)據(jù)的表。它的缺點(diǎn)在于各個(gè)分區(qū)間數(shù)據(jù)的分布不大可能均衡。

4. KEY分區(qū)

類(lèi)似于HASH分區(qū)，HASH分區(qū)允許用戶自定義的表達(dá)式，而KEY分區(qū)則不允許使用用戶自定義的表達(dá)式；HASH分區(qū)只支持整數(shù)分區(qū)，KEY分區(qū)支持除了blob和text類(lèi)型之外的其他數(shù)據(jù)類(lèi)型分區(qū)。

與HASH分區(qū)不同，創(chuàng)建KEY分區(qū)表的時(shí)候，可以不指定分區(qū)鍵，默認(rèn)會(huì)選擇使用主鍵或唯一鍵作為分區(qū)鍵，沒(méi)有主鍵或唯一鍵，就必須指定分區(qū)鍵。

解讀：根據(jù)分區(qū)鍵來(lái)進(jìn)行分區(qū)

5. 子分區(qū)

子分區(qū)是分區(qū)表中，每個(gè)分區(qū)的再次分割，適合保存非常大量的數(shù)據(jù)。

解讀：主分區(qū)使用RANGE按照年來(lái)進(jìn)行分區(qū)，有3個(gè)RANGE分區(qū)。這3個(gè)分區(qū)中又被進(jìn)一步分成了2個(gè)子分區(qū)，實(shí)際上，整個(gè)表被分成了3 * 2 = 6個(gè)分區(qū)。每個(gè)子分區(qū)按照天進(jìn)行HASH分區(qū)。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

(1) 在MySQL5.1中，對(duì)于已經(jīng)通過(guò)RANGE或LIST分區(qū)了的表在進(jìn)行子分區(qū)是可能的。子分區(qū)既可以使用HASH分區(qū)，也可以使用KEY分區(qū)。這也被稱(chēng)為復(fù)合分區(qū)。

(2) 每個(gè)分區(qū)必須有相同數(shù)量的子分區(qū)。

(3) 如果在一個(gè)分區(qū)表上的任何分區(qū)上使用SUBPARTITION來(lái)明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)。

(4) 每個(gè)SUBPARTITION子句必須包含（至少）子分區(qū)的一個(gè)名字。

(5) 在每個(gè)子分區(qū)內(nèi)，子分區(qū)的名字必須是惟一的，目前在整個(gè)表中，也要保持唯一。例如：

子分區(qū)可以用于特別大的表，可以在多個(gè)磁盤(pán)間分配數(shù)據(jù)和索引。例如：

九、MySQL分區(qū)處理NULL值的方式

十、分區(qū)管理概述

可以對(duì)分區(qū)進(jìn)行添加、刪除、重新定義、合并或拆分等管理操作。

① RANGE和LIST分區(qū)的管理

1. 刪除分區(qū)語(yǔ)句如：alter table tbl_test drop partition p0;

注意：

(1) 當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。

(2) 可以通過(guò)show create table tbl_test;來(lái)查看新的創(chuàng)建表的語(yǔ)句。

(3) 如果是LIST分區(qū)的話，刪除的數(shù)據(jù)不能新增進(jìn)來(lái)，因?yàn)檫@些行的列值包含在已經(jīng)刪除了的分區(qū)的值列表中。

2. 添加分區(qū)語(yǔ)句如：alter table tbl_test add partition(partition p3 values less than(50));

注意：

(1) 對(duì)于RANGE分區(qū)的表，只可以添加新的分區(qū)到分區(qū)列表的最高端。

(2) 對(duì)于LIST分區(qū)的表，不能添加已經(jīng)包含在現(xiàn)有分區(qū)值列表中的任意值。

3. 如果希望能不丟失數(shù)據(jù)的條件下重新定義分區(qū)，可以使用如下語(yǔ)句：

REORGANIZE會(huì)對(duì)分區(qū)的數(shù)據(jù)進(jìn)行重構(gòu)。

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)

(1) 拆分分區(qū)如：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10));

或者如：

ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5));

(2) 合并分區(qū)如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

4. 刪除所有分區(qū)，但保留數(shù)據(jù)，形式：ALTER TABLE tbl_name remove partitioning;

② HASH和KEY分區(qū)的管理

1. 減少分區(qū)數(shù)量語(yǔ)句如：ALTER TABLE tbl_name COALESCE PARTITION 2;

2. 添加分區(qū)數(shù)量語(yǔ)句如：ALTER TABLE tbl_name add PARTITION partitions 2;

③ 其他分區(qū)管理語(yǔ)句

1. 重建分區(qū)：類(lèi)似于先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，可用于整理分區(qū)碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

2. 優(yōu)化分區(qū)：如果從分區(qū)中刪除了大量的行，或者對(duì)一個(gè)帶有可變長(zhǎng)度的行（也就是說(shuō)，有VARCHAR，BLOB或TEXT類(lèi)型的列）做了許多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION來(lái)收回沒(méi)有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

3. 分析分區(qū)：讀取并保存分區(qū)的鍵分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

4. 檢查分區(qū)：檢查分區(qū)中的數(shù)據(jù)或索引是否已經(jīng)被破壞，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

5. 修補(bǔ)分區(qū)：修補(bǔ)被破壞的分區(qū)，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;

十、查看分區(qū)信息

1. 查看分區(qū)信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G;

2. 查看分區(qū)上的數(shù)據(jù)：select * from tbl_test partition(p0);

3. 查看MySQL會(huì)操作的分區(qū)：explain partitions select * from tbl_test where uuid = 2;

十一、 局限性

1. 最大分區(qū)數(shù)目不能超過(guò)1024，一般建議對(duì)單表的分區(qū)數(shù)不要超過(guò)50個(gè)。

2. 如果含有唯一索引或者主鍵，則分區(qū)列必須包含在所有的唯一索引或者主鍵在內(nèi)。

3. 不支持外鍵。

4. 不支持全文索引，對(duì)分區(qū)表的分區(qū)鍵創(chuàng)建索引，那么這個(gè)索引也將被分區(qū)。

5. 按日期進(jìn)行分區(qū)很合適，因?yàn)楹芏嗳掌诤瘮?shù)可以用。但是對(duì)字符串來(lái)說(shuō)合適的分區(qū)函數(shù)不太多。

6. 只有RANGE和LIST分區(qū)能進(jìn)行子分區(qū)，HASH和KEY分區(qū)不能進(jìn)行子分區(qū)。

7. 臨時(shí)表不能被分區(qū)。

8. 分區(qū)表對(duì)于單條記錄的查詢(xún)沒(méi)有優(yōu)勢(shì)。

9. 要注意選擇分區(qū)的成本，沒(méi)插入一行數(shù)據(jù)都需要按照表達(dá)式篩選插入的分區(qū)。

10. 分區(qū)字段盡量不要可以為null

Mysql分區(qū)表Partition

一、背景

話說(shuō)風(fēng)和日麗的一天，為提高隨著業(yè)務(wù)增長(zhǎng)的大表（3510449行吧）的訪問(wèn)效率，于是決定對(duì)表分區(qū)，記錄如下。

二、實(shí)操

結(jié)合業(yè)務(wù)，若干條記錄會(huì)集中在一個(gè)日期，查詢(xún)時(shí)也往往只查詢(xún)一個(gè)日期內(nèi)的數(shù)據(jù)，于是選取分區(qū)字段為時(shí)間。

創(chuàng)建分區(qū) 比如

CREATE TABLE message_all (

id int(10) NOT NULL AUTO_INCREMENT,

......

createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間'

PRIMARY KEY ( id , createtime )

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(createtime))

(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,

PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

不過(guò)我們表已經(jīng)有了當(dāng)然不能這么建，除非你想導(dǎo)一次數(shù)據(jù)。

如下操作 ：

1、

ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

(

PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),

PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

);

或者

2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))

(

PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))

);

然后追加。

ALTER TABLE message_all ADD PARTITION

(

PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

);

這里會(huì)有幾種錯(cuò)誤情況：

1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime)) ;

[Err] 1492 - For RANGE partitions each partition must be defined

解釋?zhuān)罕仨氈付ㄖ辽僖粋€(gè)分區(qū)。

2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function

解釋?zhuān)悍謪^(qū)字段必須是主鍵之一。

3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

解釋?zhuān)悍謪^(qū)字段為timestamp，換成datetime。

4、[Err] 1526 - Table has no partition for value xxxx

解釋?zhuān)河米芳臃绞降谝淮伪仨毟采w目前所有數(shù)據(jù)。

總結(jié)：

1、創(chuàng)建時(shí)必須指定至少一個(gè)分區(qū)。

2、key必須為主鍵之一。

3、RANGE處必須為INT型，時(shí)間字段用函數(shù)轉(zhuǎn)——YEAR()、YEARWEEK()、TO_DAYS()。

4、THAN處必須為INT型，時(shí)間字段用函數(shù)轉(zhuǎn)——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。

5、它就是以?xún)蓚€(gè)INT比大小劃分的文件。

6、所有ENGINE必須一樣。

7、范圍分區(qū)添加只能在最大值后面追加。

8、分區(qū)是有上限的貌似1024個(gè)。

用到的其他操作

1、刪除分區(qū)（直接扔掉分區(qū)文件，數(shù)據(jù)也沒(méi)了）

ALTER TABLE message_all DROP PARTITION p2016;

2、清空分區(qū)數(shù)據(jù)

ALTER TABLE message_all TRUNCATE PARTITION p2017;

3、重定義（可實(shí)現(xiàn)：分區(qū)拆分、合并、重命名）

ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO

(

PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),

PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))

);

檢查/查看你的分區(qū)

1、SHOW TABLE STATUS LIKE 'message_all';

2、SELECT * FROM information_schema.partitions WHERE table_name='message_all';

3、SHOW CREATE TABLE message_all;

4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime= '2016-01-01' AND createtime '2016-12-30';如果用到了分區(qū)partitions里會(huì)有顯示。

5、指定分區(qū)查

SELECT COUNT(1) FROM message_all PARTITION (p2016) 表別名 WHERE ......;

到這里就結(jié)束啦，土豆白。

一些概念

水平分區(qū)Partition有以下幾種模式

十、MySQL表分區(qū)

??表分區(qū)是將?個(gè)表的數(shù)據(jù)按照?定的規(guī)則?平劃分為不同的邏輯塊，并分別進(jìn)?物理存儲(chǔ)，這個(gè)規(guī)則就叫做分區(qū)函數(shù)，可以有不同的分區(qū)規(guī)則。5.7可以通過(guò)show plugins語(yǔ)句查看當(dāng)前MySQL是否?持表分區(qū)功能。

??但當(dāng)表中含有主鍵或唯?鍵時(shí)，則每個(gè)被?作 分區(qū)函數(shù)的字段必須是表中唯?鍵和主鍵的全部或?部分 ，否則就?法創(chuàng)建分區(qū)表。?如下?的表由于唯?鍵和主鍵沒(méi)有相同的字段，所以?法創(chuàng)建表分區(qū)

上述例?中刪除唯?鍵，確保主鍵中的字段包含分區(qū)函數(shù)中的所有字段，創(chuàng)建成功

或者將主鍵擴(kuò)展為包含ref字段

表分區(qū)的主要優(yōu)勢(shì)在于：

??可以允許在?個(gè)表?存儲(chǔ)更多的數(shù)據(jù)，突破磁盤(pán)限制或者?件系統(tǒng)限制

??對(duì)于從表?將過(guò)期或歷史的數(shù)據(jù)移除在表分區(qū)很容易實(shí)現(xiàn)，只要將對(duì)應(yīng)的分區(qū)移除即可

??對(duì)某些查詢(xún)和修改語(yǔ)句來(lái)說(shuō)，可以 ?動(dòng) 將數(shù)據(jù)范圍縮?到?個(gè)或?個(gè)表分區(qū)上，優(yōu)化語(yǔ)句執(zhí)?效率。?且可以通過(guò) 顯示指定表分區(qū) 來(lái)執(zhí)?語(yǔ)句，?如 SELECT * FROM t PARTITION (p0,p1) WHERE c 5

表分區(qū)類(lèi)型分為：

范圍表分區(qū)，按照?定的范圍值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)，分區(qū)函數(shù)使?的字段必須只能是 整數(shù)類(lèi)型，分區(qū)的定義范圍必須是連續(xù)的，且不能有重疊部分，通過(guò)使?VALUES LESS THAN來(lái)定義分區(qū)范圍，表分區(qū)的范圍定義是從?到?定義的

?如：

??Store_id6的數(shù)據(jù)被放在p0分區(qū)?，6=store_id10之間的數(shù)據(jù)被放在p1分區(qū)?，以此類(lèi)推，當(dāng)新插?的數(shù)據(jù)為(72, ‘Mitchell’, ‘Wilson’, ‘1998-06-25’, NULL, 13) 時(shí)，則新數(shù)據(jù)被插?到p2分區(qū)?，但當(dāng)插?的數(shù)據(jù)的store_id為21時(shí)，由于沒(méi)有分區(qū)去容納此數(shù)據(jù)，所以會(huì)報(bào)錯(cuò)，我們需要修改?下表的定義

報(bào)錯(cuò)：

修改表的定義：

?? MAXVALUE關(guān)鍵詞的作?是表示可能的最?值，所以任何store_id=16的數(shù)據(jù)都會(huì)被寫(xiě)?到p3分區(qū)?。分區(qū)函數(shù)中也可以使?表達(dá)式 ，?如：

?? 對(duì)timestamp字段類(lèi)型可以使?的表達(dá)式?前僅有unix_timestamp ，其他的表達(dá)式都不允許

列表表分區(qū)，按照?個(gè)?個(gè)確定的值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)，通過(guò)PARTITION BY LIST(expr)分區(qū)函數(shù)表達(dá)式必須返回整數(shù)，取值范圍通過(guò)VALUES IN (value_list)定義

對(duì)List表分區(qū)來(lái)說(shuō)，沒(méi)有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有未定義的取值則會(huì)報(bào)錯(cuò)

同樣，當(dāng)有主鍵或者唯?鍵存在的情況下，分區(qū)函數(shù)字段需要包含在主鍵或唯?鍵中

對(duì)range和list表分區(qū)來(lái)說(shuō)，分區(qū)函數(shù)可以包含多個(gè)字段，分區(qū)多字段函數(shù)(column partition) 所涉及的字段類(lèi)型可以包括：

范圍多字段分區(qū)函數(shù)與普通的范圍分區(qū)函數(shù)的區(qū)別在于：

a) 字段類(lèi)型多樣化

b) 范圍多字段分區(qū)函數(shù) 不?持表達(dá)式，只能?字段名

c) 范圍多字段分區(qū)函數(shù)?持?個(gè)或多個(gè)字段

再?如創(chuàng)建如下的表分區(qū)：

對(duì)多列對(duì)?來(lái)說(shuō)：

當(dāng)然只要保證取值范圍是增?的，表分區(qū)就能創(chuàng)建成功，?如：

但如果 取值范圍不是增?的，就會(huì)返回錯(cuò)誤 ：

對(duì)其他數(shù)據(jù)類(lèi)型的?持：

list列表多字段表分區(qū)，例如：你有一個(gè)在12個(gè)城市客戶的業(yè)務(wù), 為了銷(xiāo)售和市場(chǎng)的目的, 你的組織每3個(gè)城市劃分為一個(gè)區(qū)域針對(duì)LIST COLUMNS分區(qū), 你可以基于城市的名稱(chēng)創(chuàng)建一個(gè)客戶數(shù)據(jù)表并聲明4個(gè)分區(qū)當(dāng)你的客戶在對(duì)應(yīng)的這個(gè)區(qū)域:

使用日期分區(qū)

但是這種情況在日期增長(zhǎng)到非常大的時(shí)候是很復(fù)雜的, 所以這種還是使用RANGE 分區(qū)方式比較好

??按照?個(gè)?定義的函數(shù)返回值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)，這個(gè) ?定義函數(shù)也可以?xún)H僅是?個(gè)字段名字

??通過(guò)PARTITION BY HASH (expr)?句來(lái)表達(dá)哈希表分區(qū)，其中的 expr表達(dá)式必須返回?個(gè)整數(shù)，基于分區(qū)個(gè)數(shù)的取模（%）運(yùn)算。根據(jù)余數(shù)插?到指定的分區(qū)

??對(duì)哈希表分區(qū)來(lái)說(shuō)只需要定義分區(qū)的個(gè)數(shù)，其他的事情由內(nèi)部完成

如果沒(méi)有寫(xiě)明PARTITIONS字段，則默認(rèn)為1，表達(dá)式可以是整數(shù)類(lèi)型字段，也可以是?個(gè)函數(shù)，?如

?如： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

如果插??條數(shù)據(jù)對(duì)應(yīng)的col3為‘2005-09-15’時(shí)，則插?數(shù)據(jù)的分區(qū)計(jì)算?法為：

??與哈希表分區(qū)類(lèi)似，只不過(guò)哈希表分區(qū)依賴(lài)于?定義的函數(shù)，?key表分區(qū)的哈希算法是依賴(lài)MySQL本身， CREATE TABLE ... PARTITION BY KEY () 創(chuàng)建key表分區(qū)， 括號(hào)??可以包含0個(gè)或者多個(gè)字段，所引?的字段必須是主鍵或者主鍵的?部分 ，如果括號(hào)??沒(méi)有字段，則代表使?主鍵

如果表中沒(méi)有主鍵但有唯?鍵，則使?唯?鍵，但 唯?鍵字段必須定義為not null ，否則報(bào)錯(cuò)

所引?的字段未必必須是整數(shù)類(lèi)型，其他的類(lèi)型也可以使?，?如：

?表分區(qū)，是在表分區(qū)的基礎(chǔ)上再創(chuàng)建表分區(qū)的概念， 每個(gè)表分區(qū)下的?表分區(qū)個(gè)數(shù)必須?致 ，?如：

ts表?yè)碛腥齻€(gè)范圍分區(qū)，同時(shí)每個(gè)分區(qū)都各?有兩個(gè)?分區(qū)，所以總共有6個(gè)分區(qū)

?表分區(qū)必須是范圍/列表分區(qū)+哈希/key?表分區(qū)的組合

?表分區(qū)也可以顯示的指定?表分區(qū)的名字，?如：

不同的表分區(qū)對(duì)NULL值的處理?式不同

對(duì)范圍表分區(qū)來(lái)說(shuō)，如果插?的是NULL值，則將數(shù)據(jù)放到最?的分區(qū)表?

對(duì)list表分區(qū)來(lái)說(shuō)，?持NULL值的唯?情況就是某個(gè)分區(qū)的允許值中包含NULL

對(duì)哈希表分區(qū)和Key表分區(qū)來(lái)說(shuō)，NULL值會(huì)被當(dāng)成0值對(duì)待

通過(guò)alter table命令可以執(zhí)?增加，刪除，重新定義，合并或者拆分表分區(qū)的管理動(dòng)作

對(duì)范圍表分區(qū)和列表表分區(qū)來(lái)說(shuō)，刪除?個(gè)表分區(qū)命令如下：

刪除表分區(qū)的動(dòng)作不光會(huì)把分區(qū)刪掉，也會(huì)把表分區(qū)?原來(lái)的數(shù)據(jù)給刪除掉

在原分區(qū)上增加?個(gè)表分區(qū)可以通過(guò)alter table … add partition語(yǔ)句來(lái)完成

但對(duì)范圍表分區(qū)來(lái)說(shuō)，增加的表分區(qū)必須在尾部增加，在頭部或者在中間增加都會(huì)失敗：

為解決這個(gè)問(wèn)題，可以使? REORGANIZE 命令：

對(duì)列表表分區(qū)來(lái)說(shuō)，只要新增加的分區(qū)對(duì)應(yīng)的值在之前的表分區(qū)中沒(méi)有出現(xiàn)過(guò)，就可以通過(guò)alter table… add partition來(lái)增加

當(dāng)然， 也可以通過(guò)REORGANIZE命令將之前的多個(gè)分區(qū)合并成?個(gè)或?個(gè)分區(qū)，但要保持分區(qū)值?致：

更復(fù)雜的?如將多個(gè)分區(qū)重組成多個(gè)分區(qū)：

mysql 分區(qū)PARTITIONS之基本使用

當(dāng)數(shù)據(jù)表中的數(shù)據(jù)量很大時(shí)，分區(qū)帶來(lái)的效率提升才會(huì)顯現(xiàn)出來(lái)。

只有檢索字段為分區(qū)字段時(shí)，分區(qū)帶來(lái)的效率提升才會(huì)比較明顯。因此， 分區(qū)字段的選擇很重要 ，并且 業(yè)務(wù)邏輯要盡可能地根據(jù)分區(qū)字段做相應(yīng)調(diào)整 （盡量使用分區(qū)字段作為查詢(xún)條件）。

1、分區(qū)表對(duì)業(yè)務(wù)透明，只需要維護(hù)一個(gè)表的數(shù)據(jù)結(jié)構(gòu)。

2、DML操作加鎖僅影響操作的分區(qū)，不會(huì)影響未訪問(wèn)分區(qū)。

3、通過(guò)分區(qū)交換快速將數(shù)據(jù)換入和換出分區(qū)表。

4、通過(guò)TRUNCATE操作快速清理特定分區(qū)數(shù)據(jù)。

5、通過(guò)強(qiáng)制分區(qū)僅訪問(wèn)特定分區(qū)數(shù)據(jù)，減少操作影響。

6、通過(guò)大數(shù)據(jù)量分區(qū)能有效降低索引層數(shù)，提高查詢(xún)性能。

1、創(chuàng)建表時(shí)指定分區(qū)

物理文件：

2、插入數(shù)據(jù)

3、查詢(xún) information_schema.partitions 表得到該表的分區(qū)信息

4、查詢(xún)計(jì)劃分析

5、添加分區(qū)

若報(bào)錯(cuò)：

6、修改/覆蓋/合并分區(qū)

報(bào)錯(cuò)

所以需要添加pmax 分區(qū)，一同修改

7、刪除分區(qū)

8、查詢(xún)具體分區(qū)的下數(shù)據(jù)

MySQL分區(qū)的限制

怎么循環(huán)創(chuàng)建mysql表分區(qū)和清空表分區(qū)

如果是mysql5.5還是可以做到的，5.1不行

CREATE TABLE part_date

( c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,

c2 varchar(40) not null default '',

c3 datetime not NULL,

PRIMARY KEY (c1,c3),

KEY partidx(c3)) ENGINE=InnoDB DEFAULT CHARSET=utf8

partition by range COLUMNS(c3)

(

PARTITION p201012 VALUES LESS THAN ('2011-01-01 06:00:00'),

PARTITION p201101 VALUES LESS THAN ('2011-01-01 12:00:00'),

PARTITION p201102 VALUES LESS THAN ('2011-01-01 18:00:00'),

PARTITION p201103 VALUES LESS THAN ('2011-01-01 23:59:59'),

PARTITION p201912 VALUES LESS THAN MAXVALUE );

然后用函數(shù)錄入數(shù)據(jù)

DELIMITER $$

DROP PROCEDURE IF EXISTS `load_data` $$

CREATE DEFINER=`root`@`%` PROCEDURE `load_data`()

BEGIN

declare v int default 0;

while v 10000

do

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 01:00:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 03:00:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 05:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 07:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 15:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 16:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 17:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 18:01:00');

insert into part_date(c2,c3)

values (uuid(),'2011-01-01 19:01:00');

set v = v + 1;

end while;

END $$

DELIMITER ;

分區(qū)

explain partitions select count(*) from part_date where c3 date '2011-01-01 06:02:00' and c3 date '2011-01-01 08:02:00'

看一下只走了p201101分區(qū)

Mysql表分區(qū)狀態(tài)查詢(xún)

一、查詢(xún)mysql表是否為分區(qū)表：可以查看表具有哪幾個(gè)分區(qū)、分區(qū)的方法、分區(qū)中數(shù)據(jù)的記錄數(shù)等信息

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

二、查詢(xún)表有多少個(gè)分區(qū)

SELECT TABLE_NAME, COUNT(*) AS CNT

FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL

GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;

三、分析執(zhí)行語(yǔ)句

explain partitions select * from range_datetime where hiredate = '20151207124503' and hiredate='20151210111230';

四、分區(qū)管理

常規(guī)HASH和線性HASH的增加收縮分區(qū)的原理是一樣的。增加和收縮分區(qū)后原來(lái)的數(shù)據(jù)會(huì)根據(jù)現(xiàn)有的分區(qū)數(shù)量重新分布。HASH分區(qū)不能刪除分區(qū)，所以不能使用DROP PARTITION操作進(jìn)行分區(qū)刪除操作；

只能通過(guò)ALTER TABLE ... COALESCE PARTITION num來(lái)合并分區(qū)，這里的num是減去的分區(qū)數(shù)量；

可以通過(guò)ALTER TABLE ... ADD PARTITION PARTITIONS num來(lái)增加分區(qū)，這里是null是在原先基礎(chǔ)上再增加的分區(qū)數(shù)量。

網(wǎng)站題目：mysql中分區(qū)怎么刪 mysql 刪除分區(qū)
轉(zhuǎn)載源于：http://muchs.cn/article28/dossicp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、外貿(mào)網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站建設(shè)、定制開(kāi)發(fā)、虛擬主機(jī)、網(wǎng)站排名

廣告

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