MySql第二章，数据库分区

最新推荐文章于 2024-04-30 18:45:35 发布

有一个小阿飞

最新推荐文章于 2024-04-30 18:45:35 发布

阅读量379

点赞数

分类专栏： MySQL 文章标签： mysql 分区 partition range list

本文链接：https://blog.csdn.net/LeeDemoOne/article/details/106292372

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySql第二章，数据库分区

一、分区概述

数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，实际上还是一张表。另外，分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值。

1、水平分区

这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份。

2、垂直分区

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

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。

3、分区的优点

1、数据分割，放置于不同的物理文件中

2、存储更多的数据，突破单个文件存储的最大限度

3、精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率

4、数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可

5、可跨磁盘分区查询，提高磁盘的I/O性能

6、SUM()和COUNT()等聚合函数查询，可以更容易的进行并行处理

7、可备份和恢复独立的分区

二、分区类型

1>、range  => 连续区间的列值：如0~99、100~199

2>、list   => 类似range,离散集合：如[1,3,5,7,9]、[2,4,6,8,10]

3>、hash   => 根据哈希函数分配，用户可自定义表达式。确保数据在预先确定数目的分区中平均分布

4>、key    => 类似hash，但是由MySQL自身提供的函数

注：

1、若表存在 primary key 【PK】或 unique key【UK】，分区函数列只能从PK或UK中取子集。

   若无PK和UK，则可随意指定列作为分区列。


2、MySQL-5.5version以前分区键必须为整型如int，5.5以后支持非整型


3、InnoDB支持分区。

   MyISAM mysql8以前支持，之后不再支持了。

查看数据库是否支持分区：

5.6版本以后使用如下：

show plugins;
结果：
partition ACTIVE  即支持分区

三、分区创建

1、range分区（常用）

CREATE TABLE `shop_user_info` (
  `user_info_id` bigint(30) NOT NULL COMMENT '主键ID',
  `user_code` varchar(64) NOT NULL COMMENT '用户编码',
  `user_name` varchar(100) NOT NULL COMMENT '用户名称',
  `gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '信息创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'

PARTITION BY RANGE (user_info_id)(
  PARTITION P0 VALUES LESS THAN (5),
  PARTITION P1 VALUES LESS THAN (10),
  PARTITION P2 VALUES LESS THAN (15),
  PARTITION P3 VALUES LESS THAN MAXVALUE
);

分区后默认的文件存放路径：

在这里插入图片描述

【自定义存放路径后面有写】

1.1、查看分区是否成功建立

show create table shop_user_info;

1.2、查询分区中的数据

select * from shop_user_info partition (p0);

1.3、查看数据所在分区

explain partitions select * from shop_user_info where user_info_id=1;

2、list分区（常用）

CREATE TABLE `shop_user_info2` (
  `user_info_id` bigint(30) NOT NULL COMMENT '主键ID',
  `user_code` varchar(64) NOT NULL COMMENT '用户编码',
  `user_name` varchar(100) NOT NULL COMMENT '用户名称',
  `gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '信息创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'

PARTITION BY list (user_info_id)(
  PARTITION P0 VALUES in (1,3,5,7,9),
  PARTITION P1 VALUES in (2,4,6,8,10)
);

注：

因为在上面定义分区的时候，user_info_id设置了 1 3 5 7 9 2 4 6 8 10

所以user_info_id的值也只能在1 3 5 7 9 2 4 6 8 10中

##插入语句会操作失败
insert into shop_user_info2 value (66,'u66','name66',2,20,'2020-03-09 12:22:33','2020-03-09 12:25:55');

3、hash分区

【只支持整数分区】

【由于range和list分区无法保证数据的平均分布，所以有了hash分区和key分区】

CREATE TABLE `shop_user_info3` (
  `user_info_id` bigint(30) NOT NULL COMMENT '主键ID',
  `user_code` varchar(64) NOT NULL COMMENT '用户编码',
  `user_name` varchar(100) NOT NULL COMMENT '用户名称',
  `gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '信息创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'

PARTITION BY hash (user_info_id)
PARTITIONS 3;

##这里数据会按  user_info_id % 3进行分配

注：

hash分区，数据的插入、更新、删除都会根据hash表达式计算一次选择分区，因此会有性能问题。

所以自定义表达式的时候尽量不要太复杂，最好为单列

4、linear hash 线性分区

partition by linear hash (id)
partition 3;

注：

线性分区在插入、更新、删除时性能会更好更快，但是数据会分布不均，所以一般不使用

5、key分区

key分区类似hash分区，但不允许自定义表达式，默认使用mysql自身提供的函数

可以分区除了支持整数分区外，还支持除了blob、text之外的分区

partition by linear key (id)
partition 3;

四、MySQL指定分区路径

MySQL可以针对分区表的每个分区指定各自的存储路径。

1、对于innodb存储引擎的表只能指定数据路径，因为数据和索引是存储在一个文件当中。

2、对于MYISAM存储引擎可以分别指定数据文件和索引文件。

3、一般也只有RANGE、LIST分区、sub子分区才有可能需要单独指定各个分区的路径，HASH和KEY分区的所有分区的路径都是一样。RANGE分区指定路径和LIST分区是一样的。

1、MyISAM存储引擎

CREATE TABLE th (id INT, createDate DATE)
engine='MyISAM'

PARTITION BY LIST(YEAR(createDate))
(
  PARTITION p0 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p1 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p2 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx',
  PARTITION p3 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/data/data'
    INDEX DIRECTORY = '/data/idx'
);

2、INNODB存储引擎

CREATE TABLE thex (id INT, createDate DATE)
engine='InnoDB'
PARTITION BY LIST(YEAR(createDate))
(
  PARTITION p0 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/data/data',
    
  PARTITION p1 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/data/data',
   
  PARTITION p2 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/data/data',
    
  PARTITION p3 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/data/data'
  
);

五、子分区

子分区：分区表中可以对每个分区再次切割，适合保存大量的数据。

range和hash组合：

CREATE TABLE `shop_user_info4` (
  `user_info_id` bigint(30) NOT NULL COMMENT '主键ID',
  `user_code` varchar(64) NOT NULL COMMENT '用户编码',
  `user_name` varchar(100) NOT NULL COMMENT '用户名称',
  `gender` int(2) DEFAULT NULL COMMENT '性别 1:女 2:男 3:保密',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime NULL DEFAULT NULL COMMENT '信息创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '信息修改时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商用户信息表'

partition by range (YEAR(create_time))
subpartition by hash (TO_DAYS(create_time))
subpartitions 2
(
   partition p0 values less than (2015),
   partition p1 values less than (2020),
   partition p2 values less than MAXVALUE
);

①、如果创建子分区，每一个分区必须有相同数量的子分区

②、对于已经通过range和list分区了的表，可再进行子分区。

子分区既可以用hash分区，也可用key分区====> 我们称之为复合分区

③、若指定分区名称，每一个分区和子分区的名称必须全局唯一

partition by range (YEAR(create_time))
subpartition by hash (TO_DAYS(create_time))
(
   partition p0 values less than (2015)(
     subpartition s0,
     subpartition s1
   ),
   partition p1 values less than (2020)(
     subpartition s2,
     subpartition s3
   ),
   partition p2 values less than MAXVALUE(
     subpartition s4,
     subpartition s5
   )
);

六、分区管理

分区管理即：对分区进行添加、删除、重新定义、合并、拆分等操作

1、range和list分区

1.1、删除分区–数据丢失

alter table users drop partition p0;

##数据会直接丢失
##list分区drop删除后，对应的删除值就不可再添加了

1.2、添加分区

alter table users add partition (partition p4 values less than (100));

##range添加分区只能往高处添加，不能删除了p0再添加p0.
##有MAXVALUE的，先把MAXVALUE所在的分区删除后，再添加新的.

##list分区,不能添加已存在的值

1.3、重新定义–数据不丢失

拆分p1分区

alter table users reorganize partition p1 into(
	partition s0 values less than (5),
    partition s1 values less than (10)
);

合并s0、s1分区

alter table users reorganize partition s0,s1 into(
    partition p0 less than (10)
);

1.4、删除所有分区–保留数据

alter table users remove partitioning;

2、hash和key分区

【hash和key分区只能减少或增加分区数量，数据不会丢失】

减少分区数：

alter table users coalesce partition 1;  ##减少1个分区

添加分区数：

alter table users add partition partitions 2;  ##添加2个分区

3、重建分区

alter table users rebuild partition p2,p3;

##先删除分区中的记录，然后再重新插入。可用于整理分区碎片

4、分析分区

alter table users analyze partition p2,p3;

##读取并保存分区键分布

5、检查分区

alter table users check partition p2,p3;

##检查分区中的数据or索引是否被破坏

6、修补分区

alter table users repair partition p2,p3;

##修补被破坏的分区

7、优化分区 = 分析+检查+修补分区

alter table users optimize partition p2,p3;

##若删除大量行，优化后可收回没有使用的空间，并整理分区的碎片

七、注意事项

1、MySql分区处理NULL的方式

若分区键有的值为NULL，MySQL会默认将NULL视为0做处理，但这样数据就会分布不均匀，
所以我们在设计表和分区时尽量将分区列声明为 NOT NULL

2、注意

1、最大分区数不超过1024，一般建议不超过150个

2、若有PK或UK，必须包含在分区键内

3、不支持外键

4、不支持全文索引，对分区表的分区键创建索引，那么这个索引也将被分区

5、一般会按照 整数 or 日期 分区

6、只有range和list可进行子分区，hash和key不可以

7、临时表不能被分区

8、分区表对单条记录的查询无优势

9、注意分区成本（分区后数据的增删改性能会有影响）

10、分区字段尽量不要使用NULL

有一个小阿飞

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql第二章，数据库分区

MySql第二章，数据库分区一、分区概述数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，实际上还是一张表。另外，分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值。1、水平分区这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数
复制链接

扫一扫