MySQL数据库分区（Database partition）

时间：2021-09-12 19:39:11

认识数据库分区

数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

数据库分区优势

性能的提升（Increased performance）

在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。需要举个例子？好啊，百万行的表划分为10个分区，每个分区就包含十万行数据，那么查询分区需要的时间仅仅是全表扫描的十分之一了，很明显的对比。同时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上，这时候的I/O读写速度就“不堪设想”（没用错词，真的太快了，理论上100倍的速度提升啊，这是多么快的响应速度啊，所以有点不堪设想了）了。

对数据管理的简化

分区技术可以让DBA对数据的管理能力提升。通过优良的分区，DBA可以简化特定数据操作的执行方式。例如：DBA在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性(这是跟对表的数据删除这种大动作做比较的)。
此外分区是由MySQL系统直接管理的，DBA不需要手工的去划分和维护。例如：这个例如没意思，不讲了，如果你是DBA，只要你划分了分区，以后你就不用管了就是了。

数据库分区形式

水平分区（HorizontalPartitioning）

水平分区是对表的行进行分区，每个分区的列是一样的，我们可以通过年龄、日期、自增主键……

垂直分区（VerticalPartitioning）

垂直分区是对表的列进行分区，减少表的宽度，使某些特定的列在特定的分区，提高查询效率,查看是否支持垂直分区。

show variables like '%partition%';

数据库分区形式

RANGE分区

基于连续区间的列值进行分区（日期、自增主键）

LIST分区

类似于RANGE分区，LIST分区是基于列值匹配一个离散值集合中的某个值来选择

HASH分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包括MySQL中有效的、产生非负整数值的任何表达式。

KEY分区

类似于HASH分区，KEY分区只支持计算一列或多列，且MySQL服务器支持自身的哈希函数。必须有一列或多列包含整数值。

复合分区

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

MySQL分区实例

RANGE分区

根据自增主键ID为依据做范围分区

create table user(
id int primary key auto_increment,
username varchar(20) ,
age int
)
partition by rang(id)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less maxvalue
);

LIST分区

以性别为分区依据，做LIST分区

create table user(
id int primary key auto_increment,
username varchar(20) ,
age int,
sex int, -- 0：女 1：男 2：保密
primary key(id,sex)
)
partition by list(sex)
(
partition p1 values in (0),
partition p2 values in (1),
partition p3 values in (2) 
)

HASH分区（只需要指定分区的列名、表达式和分区的数量）

以性别为分区依据，做HASH分区，分区数量为3

create table user(
id int primary key auto_increment,
username varchar(20) ,
age int,
sex int, -- 0：女 1：男 2：保密
primary key (id,sex)
)
partition by hash(sex)
partitions 3;

KEY分区（类似于HASH分区）

以性别为分区依据，做HASH分区，分区数量为3

create table user(
id int primary key auto_increment,
username varchar(20) ,
age int,
sex int, -- 0：女 1：男 2：保密
primary key(id,sex)
)
partition by key(sex)
partitions 3;

复合分区（RANGE/HASH）

create table user(
id int auto_increment,
username varchar(20) ,
age int,
sex int, -- 0：女 1：男 2：保密
primary key(id,sex)
)
partition by range(id)
subpartition by hash(sex)
subpartitions 3
(
partition p1 value less than (200),
partition p2 value less than maxvalue
);

数据库分区的操作

删除分区

alter table user drop partition p1;  --不能删除HASH或者KEY分区
alter table user drop partition p1,p2;

增加分区

alter table user add partition (partition p4 values less than (300));
alter table user add partition (partition p4 value in (3));

分解分区

alter table user reorganize partition p1 into
(
partition p1 values less than (50),
partition p4 values less than (100)
); --分区分解不会丢失数据

合并分区

alter table user reorganize partition p1,p4 into(partition p1 values less than (100));

删除表的所有分区

alter table user remove partitioing;

重建分区

alter table user rebuild partition p1,p2;

优化分区

 alter table user optimize partition p1,p2;

分析分区

alter table user analyze partition p1,p2;

修补分区

alter table user repair partition p1,p2;

检查分区

alter table user check partition p1,p2;

查看分区表信息

查看创建分区表的语句

show create table user;

查看表是不是分区表

show table status;

查看分区信息

SELECT
 partition_name part,
 partition_expression expr,
 partition_description descr,
 table_rows
FROM
 information_schema. PARTITIONS
WHERE
 table_schema = SCHEMA ()
AND table_name = 'user';
标签：

相关文章