分区:
分区的功能不是在存储引擎层实现的。因此不只是InnoDB才支持分区。MyISAM、NDB都支持分区操作。
分区的过程是将一个表或者索引分解为多个更小、更可管理的部分。从逻辑上将,只有一个表或者索引,但是在物理上这个表或索引可能由数十个物理分区组成。
每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。
MySQL只支持水平分区,不支持垂直分区。
水平分区:将同一表中不同行的记录分配到不同的物理文件中。
垂直分区:将同一表中不同列的记录分配到不同的物理文件中。
MySQL数据库的分区是局部分区索引。一个分区中既存放了数据又存放了索引。而全局分区索引指的是数据存放在各个分区中,但是所有数据的索引放在一个对象中。MySQL暂时还不支持全局分区索引。
查看当前数据库是否启用了分区功能:
> show plugins\G partition状态是ACTIVE表示可以支持分区。
查看目前MySQL上有哪些分区表:
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,PARTITION_METHOD,CREATE_TIME from `PARTITIONS` where PARTITION_NAME is not null ;
MySQL数据库支持以下几种类型的分区:
RANGE分区
LIST分区
HASH分区
KEY分区
如下就是创建分区的表的方式:
> create table t4 (
col1 int null,
col2 date null,
col3 int null,
col4 int null,
key (col3)
) engine=InnoDB
partition BY HASH (col3)
partitions 4; -- 划分成4个分区
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p0.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p1.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p2.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p3.ibd
分区类型:
1 RANGE分区:
> create table t1 (id int)
partition by range(id)(
partition p0 values less than (10),
partition p1 values less than (20));
当数据小于10的时候,插入p0分区。大于等于10小于20时候,插入p1分区。
> INSERT INTO t1 SELECT 12;
> INSERT INTO t1 SELECT 2;
表物理文件变成了下面这种:
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:14 t1#P#p0.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:17 t1#P#p1.ibd
从表面上,看不出来到底插入到什么分区中了,可以用下面的命令查看:
> SELECT * from information_schema.PARTITIONS where table_schema=database() and table_name='t1'\G
***************************[ 1. row ]***************************
TABLE_CATALOG | def
TABLE_SCHEMA | hellodb
TABLE_NAME | t1
PARTITION_NAME | p0 # 这里能看到是插入到p0分区的
SUBPARTITION_NAME | None
PARTITION_ORDINAL_POSITION | 1
SUBPARTITION_ORDINAL_POSITION | None
PARTITION_METHOD | RANGE # 这里看得出是range分区类型
SUBPARTITION_METHOD | None
PARTITION_EXPRESSION | id
SUBPARTITION_EXPRESSION | None
PARTITION_DESCRIPTION | 10
TABLE_ROWS | 1 # 这个反映了该分区(这里是p0)记录的行数量。
AVG_ROW_LENGTH | 16384
DATA_LENGTH | 16384
MAX_DATA_LENGTH | None
INDEX_LENGTH | 0
DATA_FREE | 0
CREATE_TIME | None
UPDATE_TIME | None
CHECK_TIME | None
CHECKSUM | None
PARTITION_COMMENT |
NODEGROUP | default
TABLESPACE_NAME | None
***************************[ 2. row ]***************************
TABLE_CATALOG | def
TABLE_SCHEMA | hellodb
TABLE_NAME | t1
PARTITION_NAME | p1 # 这里能看到是插入到p1分区的
SUBPARTITION_NAME | None
PARTITION_ORDINAL_POSITION | 2
SUBPARTITION_ORDINAL_POSITION | None
PARTITION_METHOD | RANGE
SUBPARTITION_METHOD | None
PARTITION_EXPRESSION | id
SUBPARTITION_EXPRESSION | None
PARTITION_DESCRIPTION | 20
TABLE_ROWS | 1
AVG_ROW_LENGTH | 16384
DATA_LENGTH | 16384
MAX_DATA_LENGTH | None
INDEX_LENGTH | 0
DATA_FREE | 0
CREATE_TIME | None
UPDATE_TIME | None
CHECK_TIME | None
CHECKSUM | None
PARTITION_COMMENT |
NODEGROUP | default
TABLESPACE_NAME | None
INSERT INTO t1 SELECT 32; # 这个插入会报错,因为我们上面定义的分区,并不包含这个区间。
对此,要允许插入大数的话,可以修改下表:
ALTER TABLE t1 add partition( partition p2 values less than (30)); 或者 ALTER TABLE t1 add partition( partition p2 values less than maxvalue );
这下我们插入大数