MySQL5.6 新效能之二（exchange partitions）

分割槽是MySQL5.1版本時新增了對分割槽的支援。分割槽的過程是將一個表或者索引分解為多個更小，更可管理的部分，每個分割槽都是獨立的物件，可以獨立處理，也可以作為一個更大的物件的一部分進行處理。


MySQL資料庫支援的分割槽型別為水平分割槽，並不支援垂直分割槽。此外MySQL資料庫的分割槽是區域性分割槽索引，一個分割槽中既存放了資料有存放了索引。而全域性 分割槽是指資料存放在各個分割槽中，而索引存放在一個物件中。目前MySQL資料庫還不支援全域性分割槽。MySQL5.6 ，讓分割槽交換成了現實。只需要透過ALTER TABLE ...EXCHANGE PARTITION語句即可，也就是說，可以在短暫的時間內將某一個分割槽內的資料移到其他表中。


實驗一：交換主分割槽


備註：matchedVideo中分割槽viacom有2410806行記錄，如果要將這部分資料備份出來，MySQL 5.1需要新建臨時表，然後把這部分資料匯出去；


1>備份某一分割槽資料
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: 0


2>刪除某一個分割槽資料（分割槽保留）
mysql> delete from matchedVideo4 where company_id = 14;


Query OK, 2410806 rows affected (8 min 47.76 sec)




然而MySQL5.6在效能上有了很大的改進，大大的縮短了時間。


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: 0


mysql> 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)


交換分割槽應遵循以下原則：


1>被交換的表為沒有分割槽，但是有相同結構的表


2>未分割槽表中的記錄必須要在另一表的分割槽或子分割槽範圍內


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: 0


mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2;
ERROR 1737 (HY000): Found a row that does not match the partition
3>交換表中不允許有外來鍵


分割槽在不同引擎中的情況：


分割槽功能並不是在儲存引擎曾完成，因此不是隻有InnoDB儲存引擎支援分割槽，常見的儲存引擎MyISAM，NDB等都支援，但也不是所有的都支援，CSV，FEDORATED，MERGE等就不支援。下面我們來看下InnoDB，MyISAM的區別


不管是5.1還是5.6版本，引擎在處理資料不存在分割槽的情況是一樣的


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 21
root@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 21
root@test 11:31:59>select * from tt;
+------+
| id   |
+------+
|    3 |
|    4 |
|   10 |
|   13 |
+------+
4 rows in set (0.00 sec)




由此可見，當插入多行記錄資料時不存在與分割槽中，InnoDB會全部回滾，而MyISAM會從出錯的資料那行開始回滾，之前的都會插入。


分割槽分類：Range，List，Hash，Key分割槽四類


mysql5.5開始支援COLUMNS分割槽 ,視為RANGE分割槽和LIST分割槽的一種進化，columns分割槽可以直接使用非整型的資料進行分割槽，分割槽根據型別直接比較而得，不需要轉化為整型，此外，columns分割槽可以對多個列的值進行分割槽


支援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分割槽，可以使用多個列進行分割槽，如


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)


當行列中有一半以上滿足某一個分割槽，則存與那個分割槽中




注意：


1.分割槽最佳化器只對YEAR()，TO_DAYS()，TO_SECONDS(),UNIX_TIMESTAMP()這類函式進行最佳化選擇，如果人為的定 義分割槽，如按照年月做分割槽（201102），year(date)+month(date),最佳化器不會根據分割槽進行選擇，會掃描所有的分割槽。正確的應該 用to_days來進行分割槽。


2.分割槽中的null不同的型別處理也不同


1>對於Range分割槽，如果向分割槽列插入NULL值，則MySQL資料庫會將該值放入最左邊的分割槽。但刪除分割槽時，刪除的將是小於分割槽的值，並且還有Null的值。


2>對於List分割槽，如果要使用NULL值，則必須在List中標明哪個分割槽中放入NULL值，否則插入報錯。。


3>Hash和key分割槽對於NULL值的處理方法和list和range分割槽不一樣，任何函式都會將含有NUll值的記錄返回0.