mysql之分区表

1.分区表概述：

1.分区表的主要意义在于，对于表结构进行划分，不同的数据进入不同的分区中，以便于在查询过程中，只查找指定分区的数据，减少数据库扫描的数据量。

2.虽然从逻辑上看分区表是一张表，但是底层却是有不同的物理分区构成，对应的底层就是不同的数据文件。

限制:唯一性索引,必须在分区列上.因为表在底层是分成的.每一段索引在每一段列上,不能跨分区进行唯一判断. 

 

2.分区处理NULL值的方式:

range分区表： null被保存在最小分区

list分区： null被保存在0分区

hash和key： 被当做0

为了避免这种情况的产生，建议分区键设置成NOT NULL。

 

3.range分区

range分区就是范围分区，根据被分区列的数据的范围，将数据路由到不同的分区中.

range:

优点:可以进行范围查询,并且筛选分区

缺点:特定分区可能数据量很大,或者具有比较多的热数据,不能平均分散数据.

关于范围分区,如果创建范围分区是为了解决单个文件的写压力问题,尽量不要使用递增的id作为分区列。

建range分区表语句:

create table emp_range

(empno varchar(20) not null,

empname varchar(20),

deptno int,

birthdate date,

salary int

)

partition by range(salary)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than maxvalue

);

查看表文件结构：

[root@master test1]# ls |grep emp

emp_range.frm

emp_range#P#p1.ibd

emp_range#P#p2.ibd

 

emp_range#P#p3.ibd

 

4.list 列表分区:

list就是列表分区，将固定的值，插入到固定的分区之中

建list分区表语句:

create table emp_list

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in (1),

partition p2 values in (2),

partition p3 values in (3)

);

表文件结构：

[root@master test1]# ls |grep list

emp_list.frm

emp_list#P#p1.ibd

emp_list#P#p2.ibd

emp_list#P#p3.ibd

 

5.hash分区:

先对列进行hash计算，然后使用取余算法对数据进行路由.

Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

hash优点:可以打散热数据         缺点:不能进行范围查询

建hash分区语句:

create table emp_hash

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

表文件结构：

[root@master test1]# ls |grep hash

emp_hash.frm

emp_hash#P#p0.ibd

emp_hash#P#p1.ibd

emp_hash#P#p2.ibd

emp_hash#P#p3.ibd

 

6.key分区:

hash分区只能支持数值的分区，key分区可以支持字符串分区

建key分区语句:

create table emp_key

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

表文件结构：

[root@master test1]# ls |grep key

emp_key.frm

emp_key#P#p0.ibd

emp_key#P#p1.ibd

emp_key#P#p2.ibd

emp_key#P#p3.ibd

 

7.查看分区表：

查看分区表的建表语句: show create table emp_list;
查看分区使用情况：从information_schema.partitions表中查询

mysql> select partition_name,partition_expressio-n,partition_description, table_rows  from  INFORMATION_SCHEMA.partitions  where  table_schema='test1'   and table_name = 'emp_list';

其中 table_schema为database,而table_name为分区表。expressio-n为敏感词（分开写了）

+----------------+----------------------+-----------------------+------------+

| partition_name | partition_expressio-n | partition_description | table_rows |

+----------------+----------------------+-----------------------+------------+

| p1             | deptno               | 1                     |          2 |

| p2             | deptno               | 2                     |          2 |

| p3             | deptno               | 3                     |          1 |

+----------------+----------------------+-----------------------+------------+

mysql> select count(1) from emp_list;

+----------+

| count(1) |

+----------+

|        5 |

+----------+

1 row in set (0.00 sec)