MySQL數(shù)據(jù)庫分區(qū)實(shí)戰(zhàn)以及分區(qū)的管理

1、 MySQL數(shù)據(jù)庫分區(qū)實(shí)戰(zhàn)以及分區(qū)的管理 MySQL 分區(qū)和分表知識(shí)總結(jié)日常開發(fā)中我們經(jīng)常會(huì)遇到大表的情況，所謂的大表是指存儲(chǔ)了百萬級(jí)乃至千萬級(jí)條記錄的表。這樣的表過于龐大，導(dǎo)致數(shù)據(jù)庫在查詢和插入的時(shí)候耗時(shí)太長，性能低下，如果涉及聯(lián)合查詢的情況，性能會(huì)更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān)，提高數(shù)據(jù)庫的效率，通常點(diǎn)來講就是提高表的增刪改查效率。什么是分表？分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，我們可以稱為子表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機(jī)器上。app讀寫的時(shí)候根

2、據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的子表名，然后去操作它。什么是分區(qū)？分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，可以是同一塊磁盤也可以在不同的機(jī)器。分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個(gè)位置了。app讀寫的時(shí)候操作的還是大表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)。MySQL分表和分區(qū)有什么聯(lián)系呢？1.都能提高M(jìn)ySQL的性高，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。2.分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式（如果merge這種分表方式，不能和分區(qū)配合的話，可以用其他的分

3、表試），訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。3.分表技術(shù)是比較麻煩的，需要手動(dòng)去創(chuàng)建子表，app服務(wù)端讀寫時(shí)候需要計(jì)算子表名。采用merge好一些，但也要?jiǎng)?chuàng)建子表和配置子表間的union關(guān)系。4.表分區(qū)相對(duì)于分表，操作方便，不需要?jiǎng)?chuàng)建子表。分區(qū)又可以分為兩種：水平分區(qū)（Horizontal Partitioning）這種形式分區(qū)是對(duì)表的行進(jìn)行分區(qū)，通過這樣的方式不同分組里面的物理列分割的數(shù)據(jù)集得以組合，從而進(jìn)行個(gè)體分割（單分區(qū)）或集體分割（1個(gè)或多個(gè)分區(qū)）。所有在表中定義的列在每個(gè)數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。舉個(gè)簡(jiǎn)單例子：一個(gè)包含十年發(fā)票記錄的表可以被分區(qū)

4、為十個(gè)不同的分區(qū)，每個(gè)分區(qū)包含的是其中一年的記錄。（注：這里具體使用的分區(qū)方式我們后面再說，可以先說一點(diǎn)，一定要通過某個(gè)屬性列來分割，譬如這里使用的列就是年份）垂直分區(qū)（Vertical Partitioning）這種分區(qū)方式一般來說是通過對(duì)表的垂直劃分來減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。舉個(gè)簡(jiǎn)單例子：一個(gè)包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問，這時(shí)候就要把這些不經(jīng)常使用的text和BLOB了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性的同時(shí)還能提高訪問速度。分區(qū)表的原理分區(qū)表是由多個(gè)相關(guān)的底層表實(shí)現(xiàn)，這些底層表

5、也是由句柄對(duì)象表示，所以我們也可以直接訪問各個(gè)分區(qū)，存儲(chǔ)引擎管理分區(qū)的各個(gè)底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲(chǔ)引擎），分區(qū)表的索引只是在各個(gè)底層表上各自加上一個(gè)相同的索引，從存儲(chǔ)引擎的角度來看，底層表和一個(gè)普通表沒有任何不同，存儲(chǔ)引擎也無須知道這是一個(gè)普通表還是一個(gè)分區(qū)表的一部分。在分區(qū)表上的操作按照下面的操作邏輯進(jìn)行：select查詢：當(dāng)查詢一個(gè)分區(qū)表的時(shí)候，分區(qū)層先打開并鎖住所有的底層表，優(yōu)化器判斷是否可以過濾部分分區(qū)，然后再調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎接口訪問各個(gè)分區(qū)的數(shù)據(jù)insert操作：當(dāng)寫入一條記錄時(shí)，分區(qū)層打開并鎖住所有的底層表，然后確定哪個(gè)分區(qū)接受這條記錄，再將記錄寫

6、入對(duì)應(yīng)的底層表delete操作：當(dāng)刪除一條記錄時(shí)，分區(qū)層先打開并鎖住所有的底層表，然后確定數(shù)據(jù)對(duì)應(yīng)的分區(qū)，最后對(duì)相應(yīng)底層表進(jìn)行刪除操作update操作：當(dāng)更新一條數(shù)據(jù)時(shí)，分區(qū)層先打開并鎖住所有的底層表，mysql先確定需要更新的記錄在哪個(gè)分區(qū)，然后取出數(shù)據(jù)并更新，再判斷更新后的數(shù)據(jù)應(yīng)該放在哪個(gè)分區(qū)，然后對(duì)底層表進(jìn)行寫入操作，并對(duì)原數(shù)據(jù)所在的底層表進(jìn)行刪除操作雖然每個(gè)操作都會(huì)打開并鎖住所有的底層表，但這并不是說分區(qū)表在處理過程中是鎖住全表的，如果存儲(chǔ)引擎能夠自己實(shí)現(xiàn)行級(jí)鎖，如：innodb，則會(huì)在分區(qū)層釋放對(duì)應(yīng)的表鎖，這個(gè)加鎖和解鎖過程與普通Innodb上的查詢類似。在下面的場(chǎng)景中，分區(qū)可以起到

7、非常大的作用：A：表非常大以至于無法全部都放在內(nèi)存中，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)，其他都是歷史數(shù)據(jù)B：分區(qū)表的數(shù)據(jù)更容易維護(hù)，如：想批量刪除大量數(shù)據(jù)可以使用清除整個(gè)分區(qū)的方式。另外，還可以對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作C：分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上，從而高效地利用多個(gè)硬件設(shè)備D：可以使用分區(qū)表來避免某些特殊的瓶頸，如：innodb的單個(gè)索引的互斥訪問，ext3文件系統(tǒng)的inode鎖競(jìng)爭(zhēng)等E：如果需要，還可以備份和恢復(fù)獨(dú)立的分區(qū)，這在非常大的數(shù)據(jù)集的場(chǎng)景下效果非常好F：優(yōu)化查詢，在where字句中包含分區(qū)列時(shí)，可以只使用必要的分區(qū)來提高查詢效率，同時(shí)在涉及sum()和c

8、ount()這類聚合函數(shù)的查詢時(shí)，可以在每個(gè)分區(qū)上面并行處理，最終只需要匯總所有分區(qū)得到的結(jié)果。MySQL數(shù)據(jù)庫的分區(qū)總是把null當(dāng)作比任何非null更小的值，這和數(shù)據(jù)庫中處理null值的order by操作是一樣的，升序排序時(shí)null總是在最前面，因此對(duì)于不同的分區(qū)類型，mysql數(shù)據(jù)庫對(duì)于null的處理也各不相同。對(duì)于range分區(qū)，如果向分區(qū)列插入了null，則mysql數(shù)據(jù)庫會(huì)將該值放入最左邊的分區(qū)，注意，如果刪除分區(qū)，分區(qū)下的所有內(nèi)容都從磁盤中刪掉了，null所在分區(qū)被刪除，null值也就跟著被刪除了。在list分區(qū)下要使用null，則必須顯式地定義在分區(qū)的散列值中，否則插入nul

9、l時(shí)會(huì)報(bào)錯(cuò)。hash和key分區(qū)對(duì)于null的處理方式和range,list分區(qū)不一樣，任何分區(qū)函數(shù)都會(huì)將null返回為0.分區(qū)分區(qū)就是將數(shù)據(jù)庫或其構(gòu)成元素劃分為不同的獨(dú)立部分是一種預(yù)先組織表存儲(chǔ)的方法mysql支持水平分區(qū)將特定表行分配為行的子集分區(qū)的分布是跨物理存儲(chǔ)進(jìn)行的根據(jù)用戶在需要時(shí)設(shè)置的指定規(guī)則每個(gè)分區(qū)存儲(chǔ)為其自己的單元數(shù)據(jù)的劃分根據(jù)分區(qū)功能將數(shù)據(jù)劃分為子集分區(qū)類型和表達(dá)式是表定義的一部分表達(dá)式可以是整數(shù)或返回整數(shù)值的函數(shù)。此值根據(jù)定義確定將每條記錄存儲(chǔ)在哪個(gè)分區(qū)中1.primary key和unique key必須包含在分區(qū)key的一部分，否則在創(chuàng)建primary key和uniq

10、ue index時(shí)會(huì)報(bào)”ERROR 1503 (HY000)“2.范圍分區(qū)添加分區(qū)只能在最大值后面追加分區(qū)3.所有分區(qū)的engine必須一樣4.范圍分區(qū)分區(qū)字段：integer、數(shù)值表達(dá)式、日期列，日期函數(shù)表達(dá)式(如year(),to_days(),to_seconds(),unix_timestamp()分表的幾種方式：1、MySQL集群它并不是分表，但起到了和分表相同的作用。集群可分擔(dān)數(shù)據(jù)庫的操作次數(shù)，將任務(wù)分擔(dān)到多臺(tái)數(shù)據(jù)庫上。集群可以讀寫分離，減少讀寫壓力。從而提升數(shù)據(jù)庫性能。2、自定義規(guī)則分表分區(qū)的類型range 根據(jù)屬于指定范圍的列值將行分配到分區(qū)list 根據(jù)與離散值集之一匹配的列

11、將行分配到分區(qū)hash 基于由用戶定義的表達(dá)式返回的值而選擇的分區(qū)，對(duì)要插入表中的行的列值進(jìn)行操作key 與hash類似，不同之處在于僅提供要評(píng)估的一個(gè)或多個(gè)列Composite（復(fù)合模式） 以上模式的組合使用InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables wh

12、ich have or which are referenced by foreign keys cannot be partitioned.分區(qū)的幾種方式實(shí)踐：Range：create table range(id int(11),money int(11) unsigned not null,date datetime)partition by range(year(date)(partition p2007 values less than (2008),partition p2008 values less than (2009),partition p2009 values less

13、 than (2010)partition p2010 values less than maxvalue)；List：create table list(a int(11),b int(11)(partition by list (b)partition p0 values in (1,3,5,7,9),partition p1 values in (2,4,6,8,0);Hash：create table hash(a int(11),b datetime)partition by hash (YEAR(b)partitions 4;Key：create table t_key(a int

14、(11),b datetime)partition by key (b)partitions 4;分區(qū)管理新增分區(qū)ALTER TABLE sale_dataADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011);刪除分區(qū)-當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。ALTER TABLE sale_data DROP PARTITION p201010;分區(qū)的合并下面的SQL，將p201001 - p201009 合并為3個(gè)分區(qū)p2010Q1 - p2010Q3ALTER TABLE sale_dataREORGANIZE P

15、ARTITION p201001,p201002,p201003,p201004,p201005,p201006,p201007,p201008,p201009 INTO(PARTITION p2010Q1 VALUES LESS THAN (201004),PARTITION p2010Q2 VALUES LESS THAN (201007),PARTITION p2010Q3 VALUES LESS THAN (201010);MySQL分區(qū)實(shí)戰(zhàn)以及分區(qū)的管理CREATE TABLE part_tab( c1 int default NULL,c2 varchar(30) default

16、NULL,c3 date default NULL) engine=myisamPARTITION BY RANGE (year(c3) (PARTITION p0 VALUES LESS THAN (1995),PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,PARTITION p5 VALUES LESS THAN (2000) ,

17、PARTITION p6 VALUES LESS THAN (2001) ,PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),PARTITION p11 VALUES LESS THAN MAXVALUE );create table no_part_tab(c1 int(11) default NULL,c2 varchar(30) de

18、fault NULL,c3 date default NULL) engine=myisam;delimiter /CREATE PROCEDURE load_part_tab()begindeclare v int default 0;while v date 1995-01-01 and c3 date 1995-01-01 and c3 date 1995-01-01 and c3 date 1995-01-01 and c3 date 1995-12-31G垂直呢？alter table part_tab drop column c2;INNODB呢？CREATE TABLE part

19、_tab( c1 int default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisamPARTITION BY RANGE (year(c3) (PARTITION p0 VALUES LESS THAN (1995),PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THA

20、N (1999) ,PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),PARTITION p11 VALUES LESS THAN MAXVALUE );insert into part_

21、tab select * from no_part_tab;alter table part_tab engine=innodb;1.RANGE分區(qū)基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來進(jìn)行定義。以下是實(shí)例。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-1

22、2-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);ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitio

23、ning function按照這種分區(qū)方案，在商店1到5工作的雇員相對(duì)應(yīng)的所有行被保存在分區(qū)P0中，商店6到10的雇員保存在P1中，依次類推。注意，每個(gè)分區(qū)都是按順序進(jìn)行定義，從最低到最高。這是PARTITION BY RANGE 語法的要求；在這點(diǎn)上，它類似于C或Java中的“switch . case”語句。對(duì)于包含數(shù)據(jù)insert into employees values(72, Michael, Widenius, 1998-06-25, 9999-12-31, 13,7);的一個(gè)新行，可以很容易地確定它將插入到p2分區(qū)中，但是如果增加了一個(gè)編號(hào)為第21的商店，將會(huì)發(fā)生什么呢？在這種

24、方案下，由于沒有規(guī)則把store_id大于20的商店包含在內(nèi)，服務(wù)器將不知道把該行保存在何處，將會(huì)導(dǎo)致錯(cuò)誤。要避免這種錯(cuò)誤，insert into employees values(72, Michael, Widenius, 1998-06-25, 9999-12-31, 13,22);ERROR 1526 (HY000): Table has no partition for value 22可以通過在CREATE TABLE語句中使用一個(gè)“catchall” VALUES LESS THAN子句，該子句提供給所有大于明確指定的最高值的值：CREATE TABLE employees (i

25、d 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 LES

26、S THAN (16),PARTITION p3 VALUES LESS THAN MAXVALUE)；MAXVALUE 表示最大的可能的整數(shù)值?，F(xiàn)在，store_id 列值大于或等于16（定義了的最高值）的所有行都將保存在分區(qū)p3中。在將來的某個(gè)時(shí)候，當(dāng)商店數(shù)已經(jīng)增長到25, 30, 或更多 ，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區(qū)。在幾乎一樣的結(jié)構(gòu)中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code 列值的連續(xù)區(qū)間。例如假定2位數(shù)字的工作代碼用來表示普通（店內(nèi)的）工人，三個(gè)數(shù)字代碼表示辦公室和支持人員，四個(gè)數(shù)字代碼表示管理層，你

27、可以使用下面的語句創(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 (job_code) (PARTITION p0 VALUES LESS THAN (100),PARTITION p1 VA

28、LUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (10000);在這個(gè)例子中, 店內(nèi)工人相關(guān)的所有行將保存在分區(qū)p0中，辦公室和支持人員相關(guān)的所有行保存在分區(qū)p1中，管理層相關(guān)的所有行保存在分區(qū)p2中。在VALUES LESS THAN 子句中使用一個(gè)表達(dá)式也是可能的。這里最值得注意的限制是MySQL 必須能夠計(jì)算表達(dá)式的返回值作為LESS THAN ()比較的一部分；因此，表達(dá)式的值不能為NULL 。由于這個(gè)原因，雇員表的hired, separated, job_code,和store_id列已經(jīng)被定義為非空（NOT NULL）。除了

29、可以根據(jù)商店編號(hào)分割表數(shù)據(jù)外，你還可以使用一個(gè)基于兩個(gè)DATE （日期）中的一個(gè)的表達(dá)式來分割表數(shù)據(jù)。例如，假定你想基于每個(gè)雇員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實(shí)現(xiàn)這種分區(qū)模式的CREATE TABLE 語句的一個(gè)例子如下所示：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

30、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)；在這個(gè)方案中，在1991年前雇傭的所有雇員的記錄保存在分區(qū)p0中，1991年到1995年期間雇傭的所有雇員的記錄保存在分區(qū)p1中， 1996年到2000年期間雇傭的所有雇員的記錄保存在分區(qū)p

31、2中，2000年后雇傭的所有工人的信息保存在p3中。RANGE分區(qū)在如下場(chǎng)合特別有用：1）、 當(dāng)需要?jiǎng)h除一個(gè)分區(qū)上的“舊的”數(shù)據(jù)時(shí),只刪除分區(qū)即可。如果你使用上面最近的那個(gè)例子給出的分區(qū)方案，你只需簡(jiǎn)單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經(jīng)停止工作的雇員相對(duì)應(yīng)的所有行。對(duì)于有大量行的表，這比運(yùn)行一個(gè)如“DELETE FROM employees WHERE YEAR (separated) = num: 設(shè)置 V = CEIL(V / 2) 設(shè)置 N = N & (V - 1) 例如，假設(shè)表t1，使用線性哈希分

32、區(qū)且有4個(gè)分區(qū)，是通過下面的語句創(chuàng)建的：CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6; 現(xiàn)在假設(shè)要插入兩行記錄到表t1中，其中一條記錄col3列值為2003-04-14，另一條記錄col3列值為1998-10-19。第一條記錄將要保存到的分區(qū)確定如下：V = POWER(2, CEILING(LOG(2,7) = 8N = YEAR(2003-04-14) & (8 - 1)= 2003 & 7= 3(3 = 6 為假（FALSE）:

33、 記錄將被保存到#3號(hào)分區(qū)中)第二條記錄將要保存到的分區(qū)序號(hào)計(jì)算如下：V = 8N = YEAR(1998-10-19) & (8-1)= 1998 & 7= 6(6 = 4 為真（TRUE）: 還需要附加的步驟)N = 6 & CEILING(5 / 2)= 6 & 3= 2(2 = 4 為假（FALSE）: 記錄將被保存到#2分區(qū)中)按照線性哈希分區(qū)的優(yōu)點(diǎn)在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有極其大量（1000GA）數(shù)據(jù)的表。它的缺點(diǎn)在于，與使用常規(guī)HASH分區(qū)得到的數(shù)據(jù)分布相比，各個(gè)分區(qū)間數(shù)據(jù)的分布不大可能均衡。4.KSY分區(qū)類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)

34、只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。CREATE TABLE tk (col1 INT NOT NULL,col2 CHAR(5),col3 DATE)PARTITION BY LINEAR KEY (col1)PARTITIONS 3;在KEY分區(qū)中使用關(guān)鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號(hào)是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。分區(qū)表管理對(duì)指定表添加分區(qū)alter table user add partition(partition p4 values less than M

35、AXVALUE); /只能添加大于分區(qū)鍵的分區(qū)刪除指定表指定分區(qū)alter table student drop partition p1;創(chuàng)建子分區(qū)create table role_subp(id int(20) not null,name int(20) not null)partition by list(id)subpartition by hash(name)subpartitions 3(partition p1 values in(10),partition p2 values in(20)復(fù)合分區(qū)alter table userreorganize partition p1,

36、p3 into(partition p1 values less than (1000);rebuild重建分區(qū)alter table xxxxxx rebuild partition pm1/all; /相當(dāng)于drop所有記錄，然后再reinsert；可以解決磁盤碎片優(yōu)化表alter table tt2 optimize partition pm1; /在大量delete表數(shù)據(jù)后，可以回收空間和碎片整理。但在5.5.30后支持。在5.5.30之前可以通過recreate+analyze來替代，如果用rebuild+analyze速度慢analzye表alter table xxxxxx an

37、alyze partition pm1/all;show create table employees2; /查看分區(qū)表的定義show table status like employees2G; /查看表時(shí)候是分區(qū)表 如“Create_options: partitioned”selectfrom information_schema.KEY_COLUMN_USAGE where table_name=employees2; /查看索引SELECTFROM information_schema.partitions WHERE table_name=employees2 /查看分區(qū)表expl

38、ain partitions select * from employees2 where separated 2016-01-01; /查看分區(qū)是否被select使用分區(qū)管理和操作RANGE,LIST分區(qū)管理1:為未分區(qū)表創(chuàng)建分區(qū)ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;2:刪除某個(gè)分區(qū)的數(shù)據(jù)ALTER TABLE tr DROP PARTITION p2;3:為分區(qū)表添加一個(gè)分區(qū)ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000);ALTER

39、 TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21);ALTER TABLE employees ADD PARTITION (PARTITION p5 VALUES LESS THAN (2010),PARTITION p6 VALUES LESS THAN MAXVALUE);4:將分區(qū)表的第一個(gè)分區(qū)分為兩個(gè)新的分區(qū)ALTER TABLE membersREORGANIZE PARTITION p0 INTO (PARTITION n0 VALUES LESS THAN (1960),PARTITION n1 VALUES

40、LESS THAN (1970);5:也可以將兩個(gè)分區(qū)合并為一個(gè)分區(qū),也可以理解為重新組織分區(qū)ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (PARTITION p0 VALUES LESS THAN (1970);ALTER TABLE tbl_nameREORGANIZE PARTITION partition_listINTO (partition_definitions);ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (PARTITION m0 VALUES

41、LESS THAN (1980),PARTITION m1 VALUES LESS THAN (2000);ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8);ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (PARTITION p1 VALUES IN (6, 18),PARTITION np VALUES in (4, 8, 12);刪除分區(qū)表：alter table tb_user remove partitioning;HASH,KEY 分區(qū)管理1:創(chuàng)建一個(gè)hash分區(qū)表C

42、REATE TABLE clients (id INT,fname VARCHAR(30),lname VARCHAR(30),signed DATE)PARTITION BY HASH( MONTH(signed) )PARTITIONS 12;將分區(qū)表從12個(gè)分區(qū)變?yōu)?個(gè)分區(qū)ALTER TABLE clients COALESCE PARTITION 4;同樣的有以下的語句關(guān)于KEY分區(qū)的表:mysql CREATE TABLE clients_lk (- id INT,- fname VARCHAR(30),- lname VARCHAR(30),- signed DATE- )- PA

43、RTITION BY LINEAR KEY(signed)- PARTITIONS 12;Query OK, 0 rows affected (0.03 sec)mysql ALTER TABLE clients_lk COALESCE PARTITION 4;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0當(dāng)然還有有限制的mysql ALTER TABLE clients COALESCE PARTITION 18;ERROR 1478 (HY000): Cannot remove all par

44、titions, use DROP TABLE instead要是分區(qū)數(shù)比現(xiàn)有的分區(qū)數(shù)多的話,只能使用 ADD來添加分區(qū)數(shù).下面就表示增加了6個(gè)分區(qū)數(shù)ALTER TABLE clients ADD PARTITION PARTITIONS 6;交換分區(qū),子分區(qū)的管理交換分區(qū),ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE ntwhere pt is the partitioned table and p is the partition or subpartition of pt to be exchanged with unpartitioned

45、 table nt, provided that the following statements are true:要滿足以下的條件:1、PT是已經(jīng)分區(qū)表,nt不是臨時(shí)表2、兩張表的表結(jié)構(gòu)必須是一模一樣的3、nt不能有外鍵約束,也不能有關(guān)于其他表的外鍵約束.4、nt表中的數(shù)據(jù)沒有分區(qū)P以外的數(shù)據(jù).WITHOUT VALIDATION指定的時(shí)候這條就可以忽視掉另外很重要的一點(diǎn)就是想要擁有EXCHANGE的權(quán)限的話必須對(duì)全表有DROP的權(quán)限才可以執(zhí)行.alter table .EXCHANGE partition 將不會(huì)調(diào)用任何的觸發(fā)器,執(zhí)行完以后被EXCHANGE 的表的自增列就會(huì)重新賦初始值

46、.例如:ALTER TABLE ptEXCHANGE PARTITION pWITH TABLE nt with VALIDATION ;1:將分區(qū)和一個(gè)沒有分區(qū)的表EXCHANGE創(chuàng)建表插入語句:CREATE TABLE e (id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30)PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (50),PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (150),PA

47、RTITION p3 VALUES LESS THAN (MAXVALUE);INSERT INTO e VALUES(1669, Jim, Smith),(337, Mary, Jones),(16, Frank, White),(2005, Linda, Black);查看分區(qū)和分區(qū)的行數(shù)SELECT PARTITION_NAME, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_NAME = e;SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DA

48、TA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = p AND TABLE_NAME LIKE e;創(chuàng)建新表:CREATE TABLE e2 LIKE e;然后交換分區(qū)開始了:ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;這個(gè)語句是很奇怪的,如果e2里面沒有數(shù)據(jù)的話就是切出分區(qū),如果e2里面有數(shù)據(jù)的話就是相互交換但是如果e2里面的數(shù)據(jù)不能夠滿足分區(qū)p0的要求的話,切分區(qū)就會(huì)失敗.1737 - Found a row that does not match the

49、 partition只有再指定不驗(yàn)證的時(shí)候才不會(huì)報(bào)錯(cuò)ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;WITHOUT VALIDATION 指定的時(shí)候效率會(huì)更高,因?yàn)椴辉僮鲋鹦序?yàn)證了.子分區(qū)和沒分區(qū)的表進(jìn)行切換1:假設(shè)創(chuàng)建一個(gè)分區(qū)表,帶有子分區(qū)然后就可以切分區(qū)了,先查看一下分區(qū),SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_NAME = es;然后切出分區(qū):A

50、LTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;當(dāng)前執(zhí)行切出分區(qū)前,必須要對(duì)新表做以下的處理:ALTER TABLE es2 REMOVE PARTITIONING;修改表的默認(rèn)引擎:ALTER TABLE es3 ENGINE = MyISAM;維護(hù)表分區(qū)1:重建分區(qū)ALTER TABLE t1 REBUILD PARTITION p0, p1;2:重新組織分區(qū)ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;3:分析某個(gè)分區(qū),主要看行數(shù)和名稱以及狀態(tài)ALTER TABLE t1 ANALYZE PARTITION p3;4:修復(fù)分區(qū),有重復(fù)值的時(shí)候就會(huì)報(bào)錯(cuò).ALTER TABLE t1 REPAIR