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’;
±—±------------±---------±-----±--------------±-----±--------±-----±--------±------------+
| id |select_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 set
mysql>explain select count(*) from no_part_tab where c3 > date '1995-01-01’and c3 < date ‘1995-12-31’;
±—±------------±------------±-----±--------------±-----±--------±-----±--------±------------+
| id |select_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 set
mysql >
分区表执行扫描了7980796行,而普通表则扫描了8000206行。
文章浏览阅读484次,点赞2次,收藏2次。管理员进入主界面,软件开始运行,提供用户登录功能,不同的用户登录操作的功 能不同,非管理员用户登录只能查看一些公告信息等,而管理员登录后,可以进行用户 管理丶部门管理、职位管理、员工管理、公告管理等功能。基于其他企业人事管理软件的不足,要求能够制作一个可以方便、快捷的对员工信 息进行添加、修改、删除的操作,为了能够更好的存储职工的信息,可以将职工的信息添 加到 Word 文档,这样,不但便于保存,还可以通过 Word 文档进行打印。员工信息的管理:维护员工的基本信息,用户可以进行员工档案信息的录入及更改,_基于ssm的企业人事管理系统的设计与实现参考文献
【C语言】手撕二叉树
无论是投影还是别的计算,表达式执行的入口和计算逻辑都是统一的,这里已投影为分析表达式执行的流程。
出现上面的问题执行以下代码。
DataGrip是由JetBrains公司推出的数据库管理软件,DataGrip支持几乎所有主流的关系数据库产品,如DB2、Derby、MySQL、Oracle、SQL Server等,也支持几乎所有主流的大数据生态圈SQL软件,并且提供了简单易用的界面,开发者上手几乎不会遇到任何困难。3、连接成功,在里面我们可以看到我们前面章节所创建的表,这样子就可以在里面操作我们的sql语句的。5、连接成功,在里面我们可以看到我们前面章节所创建的表,这样子就可以在里面操作我们的sql语句的。
文章浏览阅读1.2k次。我一直在Vaadin的GUI中工作,有一些来自我的IT主管的课程.这一切都很棒,但是,今天,我遇到过我不能在addListener方法类型中使用lambda表达式.此方法是自定义的,作为使用它的对象.这是实施:public class ResetButtonForTextField extends AbstractExtension {private final List listeners= n..._java: -source 1.5 中不支持 lambda 表达式
一款实时时钟(RTC)芯片,它采用SOP-8封装,这种封装形式使得芯片具有较小的体积和良好的引脚连接稳定性,便于集成到各种电子设备中。:该芯片采用低功耗技术,使得在待机状态下功耗极低,有助于延长电池寿命,特别适合用于便携式设备或长时间运行的系统。:FRTC8563基于稳定的晶振工作,能够提供准确的时钟和日历信息,包括年、月、日、星期、小时、分钟和秒等。:芯片支持较宽的电压范围,使其能够适应不同设备的电源需求。提供准确的时间戳和日历信息,支持设备的时间同步和事件记录。:在便携式仪器仪表中,由于其低功耗特性,
wetrtc简介
文章浏览阅读651次。利用I2C有线链路,任何NFC智能手机或NFC/RFID HF专业读卡器以及MCU均可以访问存储在这些标签中的数据,并且支持掉电保存。这些标签的集成度和性价比极高,可提供丰富的功能集,适用于各种应用。此外,开发人员可从评估板、软件工具、移动应用和其他资源构成的完整生态系统中受益,从而加快应用开发速度。在物联网产品的开发过程中,物联网设备非接触式向外界提供可变的交换信息非常有必要,比如路由器向手机提供可配置的WiFi信息,巡检点向手机提供动态的传感器数据等等。_st25dv能量采集
文章浏览阅读149次。前言妈蛋,自己搭建一个Linux服务器居然能遇到这么多坑。特此整理下,方便下次遇到同样的错误时能够回过头来快速定位问题并解决问题Number 1,服务器重启之后,Xshell 连接不上注:在服务器重启之前,我只安装了 jdk ,配置了 /etc/profile 环境变量,我一直以为是这个原因,后面把jdk 配置注释掉也没用正确的方向应该是先查看 ssh 服务有没有启动键入命令systemctl status sshd.service如果你的显示跟红框一样 【dead..._建立服务器踩过的坑
文章浏览阅读187次。通过控制20Vgs (±V)的门源电压,可以实现开关管的导通和截止,实现对电流的控制和开关状态的转换。MT4606详细参数说明 - 极性 N+P沟道- 额定电压 ±30V- 额定电流 9A (N沟道), -6A (P沟道)- 导通电阻 15mΩ @ 10V (N沟道), 42mΩ @ 10V (P沟道), 19mΩ @ 4.5V (N沟道), 50mΩ @ 4.5V (P沟道)- 门源电压 20Vgs (±V)- 阈值电压 ±1.65Vth (V)- 封装类型 SOP8。_mt4606
文章浏览阅读637次。启云平台部署hive_达梦sql中hiveing