分区是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.