数据库分区、分表、分库

XTY00

已于 2022-12-07 17:08:49 修改

阅读量5.7k

点赞数 3

分类专栏： mysql 文章标签：数据库

于 2022-12-07 16:46:39 首次发布

原文链接：https://blog.csdn.net/abc_hom/article/details/122088852

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、数据库分区

1. 定义
-数据库分区是将数据量大的表的数据均摊到不同的硬盘、系统或不同服务器存储介质中（将数据从物理上分成若干个小表存储），实际上还是一张表。
-将表的数据均衡到不同的地方，为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。提升查询效率。

2. 优点
1、相对于单个文件系统或是硬盘，分区可以存储更多的数据；
2、数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可；
3、精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率；
4、可跨多个分区磁盘查询，来提高查询的吞吐量；
5、在涉及聚合函数查询时，可以很容易进行数据的合并；

3. 分类
3.1水平分区
-背景：数据量庞大
-介绍：对表的行进行分区，不同物理不同分组里面的物理分割数据集得以组合，从而进行个体分割或集体分割。所有在表中定义的列在每个数据中都能找到。所以表的特性依然得以保持。（每行数据的字段不减少）

3.2垂直分区
-背景：每行数据字段多，但有些字段包含大text且不经常被访问，这些字段就需要被切割出去。
-介绍：对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个列都包含了其中的列所对应的行。

4. 使用场景
* 一张表的查询速度已经慢到影响使用；
* SQL经过优化还是很慢；
* 数据量大；
* 表中的数据是分段的；
* 对数据的操作往往只涉及一部分，而不是所有的数据。

二、分表

1. 定义
就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

2. 作用
* 减少查询时间
* 磁盘I/O性能提高（数据分布在不同的文件）
* 读写锁影响的数据量小
* 插入数据库需要建立索引的数据减少

3. 使用场景
* 一张表的查询速度慢到影响使用时；
* SQL经过优化；
* 数据量大；
* 当插入数据或联合查询速度变慢时。

4. 分区和分表的区别
* 目的都是减少数据库的负担，提高表的增删改查效率。
* 分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。
* 当访问量大且表数据比较大时，两种方式可以互相配合使用。
* 当访问量不大，但表数据比较多时，可以只进行分区。

5. 常见分区分表的规则策略
* Range(区域、距离等)
* Hash
* 按照时间拆分
* Hash之后按照分表个数取模

三、分库
——一旦分表，一个库中的表就会越来越多。

1. 目的
为突破单节点数据库服务器的I/O能力限制，解决数据库扩展性问题。

2. 水平拆分
-将一张表分成多张表并存放在不同的数据库中。
-问题：
1.影响主键自增；
2.单表查询会变成多表（如count函数操作）

3. 垂直拆分
-将系统中不存在关联关系或需要join的表可以放在不同数据库不同的服务器中。
-问题：
1.ACID被打破；
2.Join操作困难；
3.外键约束受到影响。

4. 应用背景
* 单台DB的存储空间不够
* 随着查询量的增加，单台数据库服务器已经没法支撑
垂直分库-->水平分库-->读写分离

总结：优先考虑分区，当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。

参考：数据库分区、分表、分库_VickW的博客-CSDN博客_数据库分区表

数据库分区_T_karine的博客-CSDN博客_数据库分区

对应的SQL语句：参考MySQL创建分区方法，及分区中常用SQL示例_abc_hom的博客-CSDN博客_mysql添加分区语句

MySQL创建分区方法
通过sql语句创建
CREATE TABLE `database_test`.`table_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`create_time` datetime(0) NOT NULL,
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(create_time))(
PARTITION partition1 VALUES LESS THAN (2020),
PARTITION partition2 VALUES LESS THAN (2021)
);
通过navicat工具创建
1、选中表，右键->设计表，点击选项卡中的“选项”

2、设置好参数，点击确定

3、预览SQL，无误则保存，则完成分区创建

4、运行

SELECT PARTITION_NAME,PARTITION_METHOD,
PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='your_table';
查看分区情况

分区中常用SQL示例
1、查询指定分区内的数据：

select * from `your_table` partition(your_partition_name);

2、对已存在的表进行分区

ALTER TABLE `your_table` PARTITION by HASH(YEAR(`day`)) PARTITIONS 10;

3、删除表的所有分区

ALTER TABLE `your_table` REMOVE PARTITIONING;
4、删除表的特定分区

ALTER TABLE `your_table` DROP PARTITION your_partition_name;
5、删除表单个分区内的数据

ALTER TABLE `your_table` TRUNCATE PARTITION your_partition_name;

6、把分区内的数据复制到另一张表

INSERT INTO `your_new_table` SELECT * FROM `your_old_table` PARTITION(your_partition_name);
7、新增分区

alter table `your_table` add partition(partition your_partition_name values less than(2021));
8、原表已有主键，需要新增复合主键，则需要先丢弃再重新设置主键。

ALTER TABLE `your_database`.`your_table`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `login_time`) USING BTREE;
9、查询分区信息。

Alter table `your_table` partition by RANGE(YEAR(login_time))(
partition p1 values less than (2014),
partition p2 values less than (2015),
partition p3 values less than (2016),
partition p4 values less than (2019),
partition p5 values less than (MAXVALUE)
);
————————————————
版权声明：本文为CSDN博主「abc_hom」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/abc_hom/article/details/122088852

ALTER TABLE…ADD PARTITION

参考：如何使用ALTERTABLE…ADDPARTITION命令添加分区_云原生关系型数据库 PolarDB O引擎-阿里云帮助中心

ALTER TABLE…ADD PARTITION命令用于将分区添加到现有的分区表中。

语法介绍

使用ALTER TABLE…ADD PARTITION命令把分区添加到现有的分区表中。语法如下：

ALTER TABLE table_name ADD PARTITION partition_definition;

其中 partition_definition 是：

{list_partition | range_partition}

list_partition 是：

PARTITION [partition_name]
  VALUES (value[, value]...)
  [TABLESPACE tablespace_name]
  [(subpartition, ...)]

range partition 是：

PARTITION [partition_name]
  VALUES LESS THAN (value[, value]...)
  [TABLESPACE tablespace_name]
  [(subpartition, ...)]

其中 subpartition 是：

{list subpartition | range subpartition}

list subpartition 是：

SUBPARTITION [subpartition_name]
  VALUES (value[, value]...)
  [TABLESPACE tablespace_name]

range subpartition 是：

SUBPARTITION [subpartition_name ]
  VALUES LESS THAN (value[, value]...)
  [TABLESPACE tablespace_name]

描述

ALTER TABLE…ADD PARTITION命令用于将分区添加到现有的分区表中。在分区表中对于定义的分区数量没有上限。

新的分区必须与现有分区的类型（LIST or RANGE）相同。新分区规则必须引用和定义现有分区的分区规则中指定的相同列。

您不能使用ALTER TABLE…ADD PARTITION语句把分区添加到带有MAXVALUE或 DEFAULT规则的表中。需要注意的是，您可以交替使用ALTER TABLE。SPLIT PARTITION语句对现有分区进行划分，有效增加表中的分区数量。

RANGE分区必须以升序的方式指定。您不能把新分区添加在RANGE分区表中现有的分区之前。

包括TABLESPACE子句指定新分区要所属的表空间。如果您没有指定表空间，那么分区将所属于缺省表空间。

如果对表进行了索引设置，那么索引将创建在新分区上。要使用ALTER TABLE…ADD PARTITION命令，您必须是表的拥有者或有超级用户（或管理员）的权限。

参数


参数	参数说明
table name	要创建的表名称（可以采用模式限定的方式引用）。
partition name	要创建的分区名称。分区名称在所有分区和子分区中必须是唯一的，且必须遵循给对象标识符命名的惯例。
subpartition name	要创建的子分区名称。子分区名称在所有分区和子分区中必须是唯一的，且必须遵循给对象标识符命名的惯例。
(value[, value]...)	使用`value`来指定一个引用的文本值（或以逗号分隔的文本值列表）将表项目划分为不同的分区。每个分区规则必须至少指定一个值，但在规则中对于指定的值的数量没有上限要求。`Value`可能为`null`、 `default`（如果指定了一个`list`分区的话）或 `maxvalue`（如果指定了一个`range` 分区的话）。更多关于创建`default` 或 `maxvalue`分区的信息请参见在LIST 或 RANGE 分区表中处理偏离值。
tablespace name	分区或子分区所属的表空间名称。

添加分区到LIST 分区表示例

下列示例把分区添加到列表分区表sales中。通过使用下列命令创建表：

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

表包含三个分区（americas、asia 和 europe）：

acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 americas       | 'US', 'CANADA'      
 asia           | 'INDIA', 'PAKISTAN' 
 europe         | 'FRANCE', 'ITALY'   
(3 rows)

下述命令用于添加分区east_asia到表sales中：

ALTER TABLE sales ADD PARTITION east_asia 
  VALUES ('CHINA', 'KOREA');

在调用这个命令之后，表包括了east_asia分区。

acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 east_asia      | 'CHINA', 'KOREA'    
 americas       | 'US', 'CANADA'      
 asia           | 'INDIA', 'PAKISTAN' 
 europe         | 'FRANCE', 'ITALY'   
(4 rows)

添加分区到RANGE 分区表示例

下列示例添加了一个分区到范围分区表sales中：

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 
    VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012 
    VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012 
    VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012 
    VALUES LESS THAN('2013-Jan-01')
);

表包含四个分区（q1_2012、q2_2012、q3_2012 和 q4_2012）：

acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |  high_value   
----------------+---------------
 q4_2012        | '2013-Jan-01' 
 q3_2012        | '2012-Oct-01' 
 q2_2012        | '2012-Jul-01' 
 q1_2012        | '2012-Apr-01' 
(4 rows)

下列命令添加了一个名为q1_2013的分区到表sales 中：

ALTER TABLE sales ADD PARTITION q1_2013 
  VALUES LESS THAN('01-APR-2013');

在调用这个命令之后，表包括了分区q1_2013：

acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |  high_value   
----------------+---------------
 q1_2012        | '2012-Apr-01' 
 q2_2012        | '2012-Jul-01' 
 q3_2012        | '2012-Oct-01' 
 q4_2012        | '2013-Jan-01' 
 q1_2013        | '01-APR-2013' 
(5 rows)