mysql分区表

mysql5.7以后，查看是否支持分区表的命令： >show plugins; ---在mysql控制台中执行

常用的分区命令举例：

查看表分区信息：
SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='photo_info';

分解分区:
alter table photo_info reorganize partition pLast into(partition p7 values less than (5000000), partition p8 values less than (6000000), partition pLast values less than MAXVALUE);

删除表分区（不会丢失数据）：
alter table photo_info remove partitioning;

重新定义range分区表（不会丢失数据）:
alter table photo_info partition by range(id)
(
partition p1 values less than (2000000),
partition p2 values less than (4000000),
partition p3 values less than (6000000),
partition p4 values less than (8000000),
partition pLast values less than MAXVALUE
);

检查分区：
alter table photo_info check partition p1,p2;

新增分区：
alter table photo_info add partition (partition pLast values less than MAXVALUE);
alter table photo_info add partition (partition p4 values less than (2500000));

mysql分区表的原理和优缺点 http://www.cnblogs.com/xiaoboluo768/p/5191964.html
mysql 表分区查看表分区修改表分区 http://blog.csdn.net/wilsonke/article/details/50109153
MySQL分区表 http://www.cnblogs.com/freeton/p/4265228.html
升级到MySQL 5.7 解决分区问题 http://www.linuxidc.com/Linux/2016-02/128060.htm

mysql分区表的原理和优缺点
1.分区表的原理

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上的操作按照下面的操作逻辑进行：

select查询：

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作：

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

delete操作：

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作：

当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作

虽然每个操作都会打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，如：innodb，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通Innodb上的查询类似。

2.在下面的场景中，分区可以起到非常大的作用：

A：表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据

B：分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作

C：分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

D：可以使用分区表来避免某些特殊的瓶颈，如：innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等

E：如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好

F：优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

3.分区本身也有一些限制：

A：一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）

B：在mysql5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区（使用varchar字符串类型列时，一般还是字符串的日期作为分区）。

C：如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引

D：分区表中无法使用外键约束

E：mysql数据库支持的分区类型为水平分区，并不支持垂直分区，因此，mysql数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中

F：目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

4.子分区的建立需要注意以下几个问题：

A：每个子分区的数量必须相同

B：只要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须在所有分区上定义子分区，不能漏掉一些分区不进行子分区。

C：每个subpartition子句必须包括子分区的一个名字

D：子分区的名字必须是唯一的，不能在一张表中出现重名的子分区

E：mysql数据库的分区总是把null当作比任何非null更小的值，这和数据库中处理null值的order by操作是一样的，升序排序时null总是在最前面，因此对于不同的分区类型，mysql数据库对于null的处理也各不相同。对于range分区，如果向分区列插入了null，则mysql数据库会将该值放入最左边的分区，注意，如果删除分区，分区下的所有内容都从磁盘中删掉了，null所在分区被删除，null值也就跟着被删除了。在list分区下要使用null，则必须显式地定义在分区的散列值中，否则插入null时会报错。hash和key分区对于null的处理方式和range,list分区不一样，任何分区函数都会将null返回为0.

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分区表常用操作示例

以部门员工表为例子：

```
  创建range分区
```

create table emp
(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
);
以员工工资为依据做范围分区。

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)表达式（计算员工的出生日期）作为范围分区依据。这里最值得注意的是表达式必须有返回值。

```
  创建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)
);
以部门作为分区依据，每个部门做一分区。

```
  创建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;

```
  创建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;

```
  创建复合分区
```

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

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

分区表的管理操作

删除分区:
alter table emp drop partition p1;
不可以删除hash或者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));

分解分区:
Reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。
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 partition by range(salary)
(
partition p1 values less than (2000),
partition p2 values less than (4000)
); ----不会丢失数据

删除表的所有分区:
Alter table emp remove partitioning;--不会丢失数据

重建分区：
这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。
ALTER TABLE emp rebuild partitionp1,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 Database 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_schema.partitions表
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='test';
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
4.explain partitions select语句
通过此语句来显示扫描哪些分区，及他们是如何使用的.

五、分区表性能比较

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 THAN (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
insert into part_tab
values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
set v = v + 1;
end while;
end;
insert into no_part_tab select * frompart_tab;

3.测试sql性能
查询分区表：
selectcount() from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
+----------+
| count() |
+----------+
| 795181 |
+----------+
1 row in set (2.62 sec)
查询普通表：
selectcount() from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
+----------+
| count() |
+----------+
| 795181 |
+----------+
1 row in set (7.33 sec)
分区表的执行时间比普通表少70%。

posted @ 2018-12-21 13:50 Jonky 阅读(863) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Jonky