MYSQL--表分区、查看分区(转)_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分区表常用操作示例
以部门员工表为例子：

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

分解分区:

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 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分区表的局限性】

 在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列就正常。

```
 MySQL分区处理NULL值的方式
```

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

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

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

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

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

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

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

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

 只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

```
 临时表不能被分区。
```

四、获取mysql分区表信息的几种方法

```
show create table 表名
```

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

```
show table status
```

可以查看表是不是分区表

```
查看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’;
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

```
explain partitions select语句
```

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

五、分区表性能比较

创建两张表: 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)；

```
用存储过程插入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;

```
测试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%。

通过explain语句来分析执行情况

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

±—±------------±---------±-----±--------------±-----±--------±-----±--------±------------+

±—±------------±---------±-----±--------------±-----±--------±-----±--------±------------+

±—±------------±---------±-----±--------------±-----±--------±-----±--------±------------+

1 rowin set

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

±—±------------±------------±-----±--------------±-----±--------±-----±--------±------------+

±—±------------±------------±-----±--------------±-----±--------±-----±--------±------------+

±—±------------±------------±-----±--------------±-----±--------±-----±--------±------------+

1 rowin set

mysql >

分区表执行扫描了7980796行，而普通表则扫描了8000206行。

本文链接：https://blog.csdn.net/weixin_58286934/article/details/135417282

原作者删帖不实内容删帖广告或垃圾文章投诉

智能推荐

5.25Python基础语法2_type({100})-程序员宅基地

文章浏览阅读362次。一、类型相关操作1.type函数理解：type(数据)获取指定数据类型例如：type(100) #直接输入是不会打印,需要printprint(type(100)) #整型(int) #得出结果：100print(type(1.25)) #浮点型(float) #得出结果：1.25print(type('陈某某')) #字符串(str) #得出结果：陈某某print(type(10>20)) #布尔(bool) _type({100})

Unable to open debugger port错误，明明CMD查询端口没有被占用，但是idea一直提示端口占用_unable to open debugger port 12208-程序员宅基地

文章浏览阅读798次。在运行idea时常常提示端口被占用，在cmd查询该端口，但显示端口没有被占用怎么办？_unable to open debugger port 12208

爱上开源之一款查询docker容器启动命令的工具_docker joinsunsoft-程序员宅基地

文章浏览阅读312次。docker不容置疑，目前最为成熟最广泛的虚拟容器产品，虽然k8s在docker编排基础上，基于战略原因，协同google，ibm推出了CRI标准，兼容一切符合CRI标准的容器厂商，而带动了podman等其他容器产品的百花齐放，但是docker依然在诸多的容器产品里鹤立鸡群，强就是强，无惧大厂商的霸权，今天这里谈谈docker使用里查看容器启动命令的一个工具。runcommandruncommand是一款使用golang实现的基于容器管理的工具，市面上也有一些同类产品的实现，比如笔者我，在没有开发runco_docker joinsunsoft

深度解析ArrayList使用_arrylist-程序员宅基地

文章浏览阅读9.9k次，点赞115次，收藏108次。ArrayList 类是一个可以动态修改的数组，与普通数组的区别就是它是没有固定大小的限制，我们可以添加或删除元素。ArrayList 继承了 AbstractList ，并实现了 List 接口。_arrylist

面试题-分布式_分布式、缓存、消息等机制;-程序员宅基地

文章浏览阅读1.2k次，点赞12次，收藏73次。一、分布式知识点：分布式基础、缓存机制、消息机制、搜索机制、分布式ID、分布式锁、分布式SESSION、分布式事务、分布式分页、分布式部署、二、分布式基础知识软件架构设计的六大原则：https://blog.csdn.net/u012562943/article/details/76110761 软件架构需要考虑的基本原则：https://blog.csdn.net/zyhl..._分布式、缓存、消息等机制;

执行git命令报错：fatal: not a git repository (or any of the parent directories): .git-程序员宅基地

文章浏览阅读1.7w次，点赞2次，收藏8次。寞水_fatal: not a git repository (or any of the parent directories): .git

随便推点

通过Word或WLW离线发布程序员宅基地_新浪博客 wlw-程序员宅基地

文章浏览阅读802次。前言之前在网易和CSDN上零零散散写了一些博客，后来因为觉得写博客不方便。首先，是博客的编辑器功能不够强大；另外，我一般习惯在word上写文档，但从word文档转到博客时经常发生格式混乱的情况。由于当时觉得写博客费时、麻烦，就没有着手寻找相关的解决方法。但是，随着本地word文档越来越多，散步在硬盘的不同文件夹，对于自己管理博客和查看博客都造成了不便。首先，无法让自己清楚的从整体上查看_新浪博客 wlw

Java停车场智能化管理系统(毕设源码+mysql+lw)-程序员宅基地

文章浏览阅读513次，点赞18次，收藏8次。• 在这种开发技术模式下，系统的开发流程主要是前端专注于使用Vue.js构建动态和响应式的用户界面，同时通过Ajax技术与后端进行数据交换，实现了前后端的逻辑分离。传统的停车场管理方式已经无法满足现代社会的需求，因此，研究并开发一种智能化的停车场管理系统显得尤为重要。同时，系统还可以根据停车场的实际情况，自动调整收费标准，实现公平合理的收费。其次，智能化停车场管理系统可以提高停车场的管理效率。通过这种系统，可以实现车辆的自动识别、自动收费、自动导航等功能，大大提高了停车场的管理效率和服务质量。

以下11條小建議，幫助你們的異地戀一直保持活力-程序员宅基地

文章浏览阅读48次。多人認為遠距離關係（異地戀）幾乎都會無疾而終。家人不支持，甚至朋友也勸你不要全身心投入，以免未來傷心透頂。的確，維持一段異地戀並不容易——遙遠的距離令很多事變得難以實現。一切變得複雜起來，偶爾難過萬分，偶爾孤獨無助。不過，距離的遙遠卻也讓最簡單的小事做起來甜甜蜜蜜。例如，挽著對方的手，一起同桌吃飯，感受彼此的觸碰，一起散步，聞著彼此的發香...... 在異地戀中，如此渺小的願望瞬間顯得彌足珍貴。雖然異地戀很艱難，但也會帶來很多驚喜。如果真的不能避免異地戀，那我們也只能花心思去維持這段戀情了。