MYSQL--表分区、查看分区

MYSQL--表分区、查看分区

⼀、 mysql分区简介

数据库分区

数据库分区是⼀种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要⽬的是为了在特定的SQL操作中减少数据读写的总量以

缩减sql语句的响应时间，同时对于应⽤来说分区完全是透明的。

MYSQL的分区主要有两种形式：⽔平分区和垂直分区

⽔平分区（HorizontalPartitioning）

这种形式的分区是对根据表的⾏进⾏分区，通过这样的⽅式不同分组⾥⾯的物理列分割的数据集得以组合，从⽽进⾏个体分割（单分区）或

集体分割（1个或多个分区）。

所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。⽔平分区⼀定要通过某个属性列来分割。常见的⽐如年份，⽇

期等。

垂直分区（VerticalPartitioning）

这种分区⽅式⼀般来说是通过对表的垂直划分来减少⽬标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对

应所有⾏。

可以⽤ showvariables like '%partition%';

命令查询当前的mysql数据库版本是否⽀持分区。

分区的作⽤：数据库性能的提升和简化数据管理

在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得印度的神 性能的提⾼。

分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不⽤⼿⼯⼲预。

mysql从5.1版本开始⽀持分区。每个分区的名称是不区分⼤⼩写。同个表中的分区表名称要唯⼀。

⼆、 mysql分区类型

根据所使⽤的不同分区规则可以分成⼏⼤分区类型。

RANGE 分区：

基于属于⼀个给定连续区间的列值，把多⾏分配给分区。

LIST 分区：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配⼀个离散值集合中的某个值来进⾏选择。

HASH分区：

基于⽤户定义的表达式的返回值来进⾏选择的分区，该表达式使⽤将要插⼊到表中的这些⾏的列值进⾏计算。这个函数可以包含MySQL中

有效的、产⽣⾮负整数值的任何表达式。

KEY

分区：类似于按HASH分区，区别在于KEY分区只⽀持计算⼀列或多列，且MySQL服务器提供其⾃⾝的哈希函数。必须有⼀列或多列包含整

数值。

复合分区：

基于RANGE/LIST 类型的分区表中每个分区的再次分割。⼦分区可以是 HASH/KEY 等类型。

三、 mysql分区表常⽤操作⽰例

以部门员⼯表为例⼦：

1) 创建range分区

create table emp

(empno varchar(20) not null ,

empname v气字成语 archar(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

);

以员⼯⼯资为依据做范围分区。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(year(birthdate))

(

partition p1 values less than (1980),

partition p2 values less than (1990),

partition p3 values less than maxvalue

);

以year(birthdate)表达式（计算员⼯的出⽣⽇期）作为范围分区依据。这⾥最值得注意的是表达式必须有返回值。

2) 创建list分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in (10),

partition p2 values in (20),

partition p3 values in (30)

);

以部门作为分区依据，每个部门做⼀分区。

3) 创建hash分区

HASH分区主要⽤来确保数据在预先确定数⽬的分区中平均分布。在RANGE和LIST分区中，必须明确指定⼀个给定的列值或列值集合应该

保存在哪 个分区中；⽽在HASH分区中，MySQL ⾃动完成这些⼯作，你所要做的只是基于将要被哈希的列值指定⼀个列值或表达式，以及

指定被分区的表将要被分割成的分区数量。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

4) 创建key分区

按照KEY进⾏分区类似于按照HASH分区，除了HASH分区使⽤的⽤户定义的表达式，⽽KEY分区的哈希函数是由MySQL 服务器提供，服

务器使⽤其⾃⼰内部的哈希函数，这些函数是基于与PASSWORD()⼀样的运算法则。“CREATE TABLE ...PARTITION BY KEY”的语法规则

类似于创建⼀个通过HASH分区的表的规则。它们唯⼀的区别在于使⽤的关键字是KEY⽽不是HASH，并且KEY分区只采⽤⼀个或多个 列名

的⼀个列表。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

5) 创建复合分区

range - hash(范围哈希)复合分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salar介绍中国的作文 y int

)

partition by range(salary)

subpartition by hash(year(birthdate))

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

range- key复合分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by key(birthdate)

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

list - hash复合分区

CREATE TABLE emp (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by hash(year(birthdate))

subpartitions 3

(

PARTITION p1 VALUES in (10),

PARTITION p2 VALUES in (20)

)

;

list - key 复合分区

CREATE TABLE empk (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by key(birthdate)

subpartitions 3

(

PARTITION p1 VALUES in (10),

PARTITION p2 VALUES in (20)

)

;

6) 分区表的管理操作

删除分区:

alter table emp drop partition p1;

不可以删除has茶壶简笔画 h或者key分区。

⼀次性删除多个分区，alter table emp drop partition p1,p2;

增加分区:

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

分解分区:

Reorganizepartition关键字可以对表的部分分区或全部分区进⾏修改，并且不会丢失数据。分解前后分区的整体范围应该⼀致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); ----不会丢失数据

合并分区:

Merge分区：把2个分区合并为⼀个。

alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

----不会丢失数据

重新定义hash分区表:

Alter table emp partition by hash(salary)partitions 7;

----不会丢失数据

重新定义range分区表:

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); ----不会丢失数据

删除表的所有分区:

Alter table emp removepartitioning;--不会丢失数据

重建分区：

这和先删除保存在分区中的所有记录，然后重新插⼊它们，具有同样的效果。它可⽤于整理分区碎⽚。

ALTER TABLE emp rebuild partitio吃药能吃萝卜吗 np1,p2;

优化分区：

如果从分区中删除了⼤量的⾏，或者对⼀个带有可变长度的⾏（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可

以使⽤“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使⽤的空间，并整理分区数据⽂件的碎⽚。

ALTER TABLE emp optimize partition p1,p2;

分析分区：

读取并保存分区的键分布。

ALTER TABLE emp analyze partition p1,p2;

修补分区：

修补被破坏的分区。

ALTER TABLE emp repairpartition p1,p2;

检查分区：

可以使⽤⼏乎与对⾮分区表使⽤CHECK TABLE 相同的⽅式检查分区。

ALTER TABLE emp CHECK partition p1,p2;

这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发⽣了这种情况，使⽤“ALTER TABLE ... REPAIR

PARTITION”来修补该分区。

【mysql分区表的局限性】

1. 在5.1版本中分区表对唯⼀约束有明确的规定，每⼀个唯⼀约束必须包含在分区表的分区键（也包括主键约束）。

CREATE TABLE emptt (

empno varchar(20) NOT NULL ,

empname varchar(20)，

deptno int,

birthdate date NOT NULL,

salary int ,

primary key (empno)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

这样的语句会报错。MySQL Databa Error: A PRIMARY KEY must include allcolumns in the table's partitioning function；

CREATE TABLE emptt (

empno varchar(20) NOT NULL ,

empname varchar(20) ,

deptno int(11),

birthdate date NOT NULL,

salary int(11) ,

primary key (empno，salary)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

在主键中加⼊salary列就正常。

2.西游记感想 MySQL分区处理NULL值的⽅式

如果分区键所在列没有notnull约束。

如果是range分区表，那么null⾏将被保存在范围最⼩的分区。

如果是list分区表，那么null⾏将被保存到list为0的分区。

在按HASH和KEY分区的情况下，任何产⽣NULL值的表达式mysql都视同它的返回值为0。

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

3. 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯⼀的例外是当分

区类型为KEY分区的时候，可以使⽤其他类型的列作为分区键（ BLOB or TEXT 列除外）。

4. 对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引⼀说。

5. 只有RANG和LIST分区能进⾏⼦分区，HASH和KEY分区不能进⾏⼦分区。

6. 临时表不能被分区。

四、 获取mysql分区表信息的⼏种⽅法

1. show create table 表名

可以查看创建分区表的create语句

2. show table status

可以查看表是不是分区表

3. 查看information_ions表

lect

partition_name part,

partition_expression expr,

partition_description descr,

table_rows

from information_ions where

table_schema = schema()

and table_name='test';

可以查看表具有哪⼏个分区、分区的⽅法、分区中数据的记录数等信息

4. explain partitions lect语句

通过此语句来显⽰扫描哪些分区，及他们是如何使⽤的.

五、 分区表性能⽐较

1. 创建两张表: part_tab(分区表),no_part_tab(普通表)

CREATE TABLEpart_tab

( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)

PARTITION BYRANGE(year(c3))

(PARTITION p0VALUES LESS THAN (1995),

PARTITION p1 VALUESLESS THAN (1996) ,

PARTITION p2 VALUESLESS THAN (1997) ,

PARTITION p3 VALUESLESS THAN (1998) ,

PARTITION p4 VALUES LESS THAN (1999) ,

PARTITION p5 VALUESLESS THAN (2000) ,

PARTITION p6 VALUESLESS THAN (2001) ,

PARTITION p7 VALUESLESS THAN (2002) ,

PARTITION p8 VALUESLESS THAN (2003) ,

PARTITION p9 VALUESLESS T回味 HAN (2004) ,

PARTITION p10VALUES LESS THAN (2010)凉拌青木瓜 ,

PARTITION p11VALUES LESS THAN (MAXVALUE) );

CREATE TABLE no_part_tab

( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)；

2. ⽤存储过程插⼊800万条数据

CREATE PROCEDUREload_part_tab()

begin

declare v int default 0;

while v < 8000000

do

inrt into part_tab

values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));

t v = v + 1;

end while;

end;

inrt into no_part_tab lect * frompart_tab;

3. 测试sql性能

查询分区表：

lectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

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

| count(*) |

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

| 795181 |

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

1 row in t (2.62 c)

查询普通表：

lectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

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

| count(*) |

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

| 795181 |

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

1 row in t (7.33 c)

分区表的执⾏时间⽐普通表少70%。

4. 通过explain语句来分析执⾏情况

mysql>explain lect count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

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

| id |lect_type | table | type |possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE 裙带菜的做法 | part_tab | ALL | NULL | NULL | NULL | NULL | 7980796 | Using where |

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

1 rowin t

mysql>explain lect count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

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

| id |lect_type | table | type |possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000206 | Using where |

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

1 rowin t

mysql >

分区表执⾏扫描了7980796⾏，⽽普通表则扫描了8000206⾏。