数据库分区、分表、分库

一、数据库分区

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、查询分区信息。

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';
10、原表设置了MAXVALUE分区，若想添加新分区又不丢失数据，则需要重新分区。

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)