分區(qū)是MySQL5.1版本時(shí)添加了對分區(qū)的支持。分區(qū)的過程是將一個(gè)表或者索引分解為多個(gè)更小，更可管理的部分，每個(gè)分區(qū)都是獨(dú)立的對象，可以獨(dú)立處理，也可以作為一個(gè)更大的對象的一部分進(jìn)行處理。
MySQL數(shù)據(jù)庫支持的分區(qū)類型為水平分區(qū)，并不支持垂直分區(qū)。此外MySQL數(shù)據(jù)庫的分區(qū)是局部分區(qū)索引，一個(gè)分區(qū)中既存放了數(shù)據(jù)有存放了索引。而全局分區(qū)是指數(shù)據(jù)存放在各個(gè)分區(qū)中，而索引存放在一個(gè)對象中。目前MySQL數(shù)據(jù)庫還不支持全局分區(qū)。MySQL5.6 ，讓分區(qū)交換成了現(xiàn)實(shí)。只需要通過ALTER TABLE ...EXCHANGE PARTITION語句即可，也就是說，可以在短暫的時(shí)間內(nèi)將某一個(gè)分區(qū)內(nèi)的數(shù)據(jù)移到其他表中。
實(shí)驗(yàn)一：交換主分區(qū)
備注：matchedVideo中分區(qū)viacom有2410806行記錄，如果要將這部分?jǐn)?shù)據(jù)備份出來，MySQL 5.1需要新建臨時(shí)表，然后把這部分?jǐn)?shù)據(jù)導(dǎo)出去；
1>備份某一分區(qū)數(shù)據(jù)mysql> insert into matchedVideo4 select * from matchedVideo where company_id = 14;Query OK, 2410806 rows affected (12 min 28.41 sec)Records: 2410806 Duplicates: 0 Warnings: 02>刪除某一個(gè)分區(qū)數(shù)據(jù)（分區(qū)保留）mysql> delete from matchedVideo4 where company_id = 14;Query OK, 2410806 rows affected (8 min 47.76 sec)
然而MySQL5.6在性能上有了很大的改進(jìn)，大大的縮短了時(shí)間。
mysql> create table matchedVideo2 like matchedVideo;Query OK, 0 rows affected (1 min 13.84 sec)mysql> ALTER TABLE matchedVideo2 REMOVE PARTITIONING;Query OK, 0 rows affected (16.88 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;Query OK, 0 rows affected (0.67 sec)mysql> select count(*) from matchedVideo2;+----------+| count(*) |+----------+| 2410806 |+----------+1 row in set (4.24 sec)
交換分區(qū)應(yīng)遵循以下原則：
1>被交換的表為沒有分區(qū)，但是有相同結(jié)構(gòu)的表
2>未分區(qū)表中的記錄必須要在另一表的分區(qū)或子分區(qū)范圍內(nèi)
mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;Query OK, 0 rows affected (0.58 sec)mysql> update matchedVideo2 set company_id = 10 where id = 75537347;Query OK, 1 row affected (0.23 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;ERROR 1737 (HY000): Found a row that does not match the partition
3>交換表中不允許有外鍵
分區(qū)在不同引擎中的情況：
分區(qū)功能并不是在存儲引擎曾完成，因此不是只有InnoDB存儲引擎支持分區(qū)，常見的存儲引擎MyISAM，NDB等都支持，但也不是所有的都支持，CSV，F(xiàn)EDORATED，MERGE等就不支持。下面我們來看下InnoDB，MyISAM的區(qū)別
不管是5.1還是5.6版本，引擎在處理數(shù)據(jù)不存在分區(qū)的情況是一樣的
Innodb
mysql> create table t ( -> id int) engine=innodb -> partition by range (id) ( -> partition p0 values less than (10), -> partition p1 values less than (20));Query OK, 0 rows affected (3.25 sec)root@test 11:30:42>insert into t(id) values(3),(4),(10),(13),(21);ERROR 1526 (HY000): Table has no partition for value 21root@test 11:32:18>select * from t;Empty set (0.00 sec)
MyISAM
mysql> create table tt ( -> id int) engine=myisam -> partition by range (id) ( -> partition p0 values less than (10), -> partition p1 values less than (20));Query OK, 0 rows affected (0.10 sec)root@test 11:31:10>insert into tt(id) values(3),(4),(10),(13),(21);ERROR 1526 (HY000): Table has no partition for value 21root@test 11:31:59>select * from tt;+------+| id |+------+| 3 || 4 || 10 || 13 |+------+4 rows in set (0.00 sec)
由此可見，當(dāng)插入多行記錄數(shù)據(jù)時(shí)不存在與分區(qū)中，InnoDB會(huì)全部回滾，而MyISAM會(huì)從出錯(cuò)的數(shù)據(jù)那行開始回滾，之前的都會(huì)插入。
分區(qū)分類：Range，List，Hash，Key分區(qū)四類
mysql5.5開始支持COLUMNS分區(qū) ,視為RANGE分區(qū)和LIST分區(qū)的一種進(jìn)化，columns分區(qū)可以直接使用非整型的數(shù)據(jù)進(jìn)行分區(qū)，分區(qū)根據(jù)類型直接比較而得，不需要轉(zhuǎn)化為整型，此外，columns分區(qū)可以對多個(gè)列的值進(jìn)行分區(qū)
支持INT,SMALLINT,TINYINT,BIGINT.DATE,DATETIME.CHAR,VARCHAR,BINARY,VARBINARY類型
MySQL5.1
root@test 11:57:19> CREATE TABLE t_columns_range51( -> a INT, -> b DATETIME) ENGINE=INNODB -> PARTITION BY RANGE (TO_DAYS(b)) -> (PARTITION p0 VALUES LESS THAN(TO_DAYS('2009-01-01')), -> PARTITION p1 VALUES LESS THAN(TO_DAYS('2010-01-01')));Query OK, 0 rows affected (0.42 sec)root@test 11:57:19>insert into t_columns_range51(a,b) values(1,'2009-02-01');Query OK, 1 row affected (0.00 sec)root@test 11:58:18>select table_name,partition_name,table_rows from information_schema.partitions where table_name ='t_columns_range51';+-------------------+----------------+------------+| table_name | partition_name | table_rows |+-------------------+----------------+------------+| t_columns_range51 | p0 | 0 || t_columns_range51 | p1 | 1 |+-------------------+----------------+------------+2 rows in set (0.00 sec)MySQL5.6
mysql> CREATE TABLE t_columns_range( -> a INT, -> b DATETIME) ENGINE=INNODB -> PARTITION BY RANGE COLUMNS (b) -> (PARTITION p0 VALUES LESS THAN('2009-01-01'), -> PARTITION p1 VALUES LESS THAN('2010-01-01'));Query OK, 0 rows affected (0.26 sec)mysql> insert into t_columns_range(a,b) values (1,'2009-02-01');Query OK, 1 row affected (0.05 sec)mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='t_columns_range';+-----------------+----------------+------------+| table_name | partition_name | table_rows |+-----------------+----------------+------------+| t_columns_range | p0 | 0 || t_columns_range | p1 | 1 |+-----------------+----------------+------------+2 rows in set (0.03 sec)mysql> create table c1( -> a varchar(25), -> b varchar(25), -> city varchar(15) -> ) -> partition by list columns(city)( -> partition p1 values in('xx','yy'), -> partition p2 values in('uu','vv'));Query OK, 0 rows affected (0.46 sec)mysql> insert into c1(a,b,city) values('a','a','xx');Query OK, 1 row affected (5.41 sec)mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c1';+------------+----------------+------------+| table_name | partition_name | table_rows |+------------+----------------+------------+| c1 | p1 | 1 || c1 | p2 | 0 |+------------+----------------+------------+2 rows in set (0.00 sec)對于range columns分區(qū)，可以使用多個(gè)列進(jìn)行分區(qū)，如mysql> create table c2( -> a int, -> b int, -> c varchar(15) -> ) -> partition by range columns(a,b,c)( -> partition p1 values less than (5,10,'xbb'), -> partition p2 values less than (10,20,'icey'), -> partition p5 values less than (maxvalue,maxvalue,maxvalue));Query OK, 0 rows affected (0.16 sec)mysql> insert into c2(a,b,c) values(3,20,'xbb');Query OK, 1 row affected (0.04 sec)mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c2';+------------+----------------+------------+| table_name | partition_name | table_rows |+------------+----------------+------------+| c2 | p1 | 1 || c2 | p2 | 0 || c2 | p5 | 0 |+------------+----------------+------------+3 rows in set (0.00 sec)mysql> insert into c2(a,b,c) values(12,22,'xbb');Query OK, 1 row affected (0.02 sec)mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c2';+------------+----------------+------------+| table_name | partition_name | table_rows |+------------+----------------+------------+| c2 | p1 | 1 || c2 | p2 | 0 || c2 | p5 | 1 |+------------+----------------+------------+3 rows in set (0.00 sec)當(dāng)行列中有一半以上滿足某一個(gè)分區(qū)，則存與那個(gè)分區(qū)中注意：
1.分區(qū)優(yōu)化器只對YEAR()，TO_DAYS()，TO_SECONDS(),UNIX_TIMESTAMP()這類函數(shù)進(jìn)行優(yōu)化選擇，如果人為的定義分區(qū)，如按照年月做分區(qū)（201102），year(date)+month(date),優(yōu)化器不會(huì)根據(jù)分區(qū)進(jìn)行選擇，會(huì)掃描所有的分區(qū)。正確的應(yīng)該用to_days來進(jìn)行分區(qū)。
2.分區(qū)中的null不同的類型處理也不同
1>對于Range分區(qū)，如果向分區(qū)列插入NULL值，則MySQL數(shù)據(jù)庫會(huì)將該值放入最左邊的分區(qū)。但刪除分區(qū)時(shí)，刪除的將是小于分區(qū)的值，并且還有Null的值。
2>對于List分區(qū)，如果要使用NULL值，則必須在List中標(biāo)明哪個(gè)分區(qū)中放入NULL值，否則插入報(bào)錯(cuò)。。
3>Hash和key分區(qū)對于NULL值的處理方法和list和range分區(qū)不一樣，任何函數(shù)都會(huì)將含有NUll值的記錄返回0.
