一、分区概念

概念

数据库分区是将数据库的表分割成更小的、更易管理的部分的过程，分区表逻辑上看起来仍然是一个统一的表，但是数据存储在不同的物理位置。尽管数据分散到不同分区中，但数据库表依然保持一致性和事务完整性

分区优点

1、提高查询性能：通过将数据分解到不同的分区，可以仅针对相关分区进行查询，减少了需要扫描的数据量，从而加快了查询速度

2、支持高并发：在并发场景中，可以提供多个物理设备进行数据分区来分担负载，从而提高数据库的吞吐量和响应能力

3、数据的维护效率提升：备份、恢复、归档单个分区的效率更高

4、成本效益：数据分区使得对硬件的整体需求减少，从而降低了数据库的总体成本

分区缺点

1、复杂性增加：数据分区让数据库的设计变得更复杂，需要选择合适的分区键，且数据一致性更复杂，更难以维护

2、性能考量：虽然分区本身是为了提升查询性能，但是不当的分区策略反而会降低性能。且在进行DML操作时，如果涉及到多个区也可能会降低效率

3、空间利用率、数据倾斜：对于某些数据量较小的分区，存储空间的利用会不充分。且当数据整体出现分布不均时，可能会出现热点问题

数据分区的依据（分区键）

选择分区键应该考虑数据的查询模式、数据的增长趋势、数据的冗余和一致性、数据的维护和可扩展性、性能改进。分区键建议非null。这里列出一些场景，如下
1、时间条件，适用于日志数据、交易记录、财务报告等
2、数值范围，适用于年龄、金额等
3、区间哈希，适合随机写入的场景
4、具有高聚合属性的列，比如用户id
5、复合键，根据业务场景来说，有时候可能需要多个列的值作为分区

二、分区类型

MySQL支持：范围分区RANGE、列表分区LIST、散列分区HASH、键值分区KEY

针对每种分区方式，可以通过show VARIABLES like 'datadir';找到表文件的具体位置来查看实际情况

也可以用通过select * from information_schema.PARTITIONS where table_name = 'table_name';查看分区的具体情况

范围分区（RANGE）

RANGE分区按区间来划分数据区域，根据所选列的值范围将数据分配到对应的分区中。是一种分析顺序插入和查询模式选择的分区类型，对于具有清晰界限的数据集非常适用。

假设有一个sales表，按月份存储销售数据，现在对每一年的每个月份进行分区，sql如下

CREATE TABLE sales (
   sale_id INT NOT NULL COMMENT '销售id',
   sale_date DATE NOT NULL COMMENT '销售日期',
   amount DECIMAL(10,2) NOT NULL COMMENT '销售金额',
   PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE ( YEAR(sale_date) ) (
   PARTITION p0 VALUES LESS THAN (2010),  -- 2010年之前
   PARTITION p1 VALUES LESS THAN (2011),  -- 2010年
   PARTITION p2 VALUES LESS THAN (2012),  -- 2011年
   -- 以此类推，每增加一个PARTITION可以覆盖一个额外的年份
   PARTITION pmax VALUES LESS THAN (MAXVALUE)  -- MAXVALUE用于存储剩余的分区
);

需要注意： 主键 key 需要包含分区键，否则会报如下错
    "1503 - A PRIMARY KEY must include all columns in the table's partitioning function"

在对表建立分区后，插入的数据的值应该严格遵守分区的定义，避免当插入一个不属于任何分区的值时，MySQL数据库会报错 Table has no partition for value XXX，可以添加 PARTITION pmax VALUES LESS THAN (MAXVALUE)，或者明确插入的值在分区范围内

1、新增数据

INSERT INTO sales (sale_id, sale_date, amount) VALUES (1, '2023-04-01', 100.00);

2、删除范围数据

DELETE FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

3、修改数据

UPDATE sales SET amount = 105.00 WHERE sale_date = '2023-04-01';

如果更新操作导致数据不符合当前分区的界限条件，也会报错

4、查询数据

- 可以在WHERE子句中指定分区键的范围来查询特定分区的数据，如下
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

- 也可以使用PARTITION子句直接对特定的分区或多个分区执行查询操作
SELECT * FROM sales PARTITION (p0, p1) WHERE sale_date 
BETWEEN '2020-01-01' AND '2021-12-31';

5、对分区进行操作

-1- 删除指定分区
ALTER TABLE sales DROP PARTITION p0;

需要注意：这种方式会将分区数据也删除。不过这种方式删除数据比delete会更快

-2- 合并分区
ALTER TABLE sales REORGANIZE PARTITION p1, p2 into (
     PARTITION p1_2 VALUES LESS THAN (2012)
)

需要注意：
    1）、需要合并的range分区必须是连续顺序的分区，否则会报如下错：
        "1519 - When reorganizing a set of partitions they must be in consecutive order"
    2）、合并range分区时，合并后的范围需要大于等于需要合并的范围中的最大值，否则会报如下错：
        "1520 - Reorganize of range partitions cannot change total ranges except for last 
        partition where it can extend the range"

-3- 拆分分区
ALTER TABLE sales REORGANIZE PARTITION p1_2 into (
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2012)
)

需要注意：
    拆分range分区，拆分后的分区不能跨越已有的分区范围，例如：已有分区1、2、4、7，对7分区拆分后
    只能是大于4的范围的分区，不能等于或小于4，否则会报如下错：
        "1493 - VALUES LESS THAN value must be strictly increasing for each partition"

-4- 新增分区 （这里需要分情况）
    情况1：当未指定maxvalue
          可以用如下方式来新增分区，但是只能新增大于现有分区值的分区 
          比如 已有最大分区年份是 2012，则新增分区不能小于等于2012
    
alter table sales add partition(partition p? values less than (?));
    
    情况2：已指定maxvalue
          需要重新定义表分区,如下。但是种方式非常耗时
          
ALTER TABLE sales
PARTITION BY RANGE (YEAR(sale_date)) (
   PARTITION p0 VALUES LESS THAN (2010),
   PARTITION p1 VALUES LESS THAN (2011),
   PARTITION p2 VALUES LESS THAN (2012),
   PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

DDL操作对性能有一定的影响，特别是这种方式新增分区会锁表的情况
对于大数据表来说，最好的方式是创建一个新表来替换旧表，不至于业务阻塞

使用RANGE分区添加和删除分区需要考虑事务安全和数据连续性。比如如果数据的日期字段修改可能会引起跨分区边界，需要做相应处理保证数据正确，如删除原分区数据，将更改后的数据加入到正确的分区等。数据分布应尽量均匀，且查询时应该尽量缩小查询范围，以此减少扫描范围来提高查询性能

应用场景举例：
1、时间序列数据：如日志数据、销售数据等，可以按照年份、月份或日期进行分区
2、逐渐增长的数据：随着时间的推移数据量逐渐增长，比如在线访问记录

列表分区（LIST）

LIST分区根据分区键的值列表来确定数据所在分区。无顺序性，更具灵活性，可以根据业务规则、数据特性或查询模式等进行更细致的区域划分。

假设有一个company表，包含公司的不同部门，并根据部门来对数据进行分区。sql如下

CREATE TABLE company (
   id INT AUTO_INCREMENT COMMENT '自增主键',
   department_id INT NOT NULL COMMENT '部门id',
   department_name VARCHAR(100) NOT NULL COMMENT '部门名称',
   employee_id INT COMMENT '员工id',
   employee_name VARCHAR(100) COMMENT '员工名称',
   PRIMARY KEY (id, department_id)
)
PARTITION BY LIST (department_id) (
   PARTITION p_hr VALUES IN (1),
   PARTITION p_it VALUES IN (2),
   PARTITION p_finance VALUES IN (3),
   PARTITION p_marketing VALUES IN (4)
);

需要注意： 主键 key 需要包含分区键，否则会报如下错 
    "1503 - A PRIMARY KEY must include all columns in the table's partitioning function"

1、新增数据

INSERT INTO company (department_id, department_name, employee_id, employee_name) 
VALUES (1, 'HR', 1, 'John Doe');

需要注意：LIST分区中插入数据时，需要确保插入的数据匹配分区键上列出的特定值，否则会报如下错
    "1526 - Table has no partition for value xxx"

2、删除数据

- 删除指定分区指定的数据
DELETE FROM company PARTITION (p_hr) WHERE id = 1;

3、修改数据

对于不影响分区键值的改变，直接update即可。
如果分区键字段的值需要变动，直接修改对应数据的分区键可能导致错误。
考虑到数据一致性、维护分区连续性以及分区边界限制等问题，可以先删除旧数据，再新增数据

DELETE FROM company PARTITION (p_hr) WHERE id = 2;

INSERT INTO company (department_id, department_name, employee_id, employee_name)
VALUES (2, 'it', 2, 'Jane Smith');

4、查询指定分区数据

SELECT * FROM company PARTITION (partition_name);

5、对分区进行操作

-1- 删除整个分区
ALTER TABLE company DROP PARTITION p_hr;

需要注意：这种方式会删除整个区的数据

-2- 新增分区
ALTER TABLE company ADD PARTITION (PARTITION p_hr VALUES IN (1));

需要注意：
    1）、新增分区的列表值不能重复，否则会报如下错：
        "1495 - Multiple definition of same constant in list partitioning"
    2）、DDL操作对性能有一定的影响，特别是这种方式新增分区会锁表的情况
        对于大数据表来说，最好的方式是创建一个新表来替换旧表，不至于业务阻塞
    
-3- 合并分区
ALTER TABLE company REORGANIZE PARTITION  p_hr, p_it INTO (PARTITION p_hi VALUES IN (1,2));

需要注意：和Range分区一样，list分区合并只能合并相邻的几个分区，不能跨分区合并，否则会报如下错
   "1519 - When reorganizing a set of partitions they must be in consecutive order" 

-4- 拆分分区
ALTER TABLE company REORGANIZE PARTITION  p_hi INTO ( 
    PARTITION p_hr VALUES IN (1),
    PARTITION p_it VALUES IN (2)
);

应用场景：
类型明确的数据如用户类型、产品类型、设备类型、交易类型等

"列分区（COLUMNS）"

实际上MySQL官方文档中并没有COLUMNS类型的分区，与其说是单独的一个分区类型，不如说是RANGE和LIST分区的功能增强。目前只有RANGE和LIST分区会有COLUMNS多列分区的增强功能。这里仅简单举例说明

1、RANGE COLUMNS

大多数用法与标准的RANGE分区用法相似，只是RANGE COLUMNS允许使用多个列的值来定义分区范围，但是不能使用函数处理这些列的值，如下sql

CREATE TABLE test (
   field_a INT NOT NULL,
   field_b INT NOT NULL,
   PRIMARY KEY (field_a, field_b)
) PARTITION BY RANGE COLUMNS ( field_a, field_b ) (
   PARTITION p0 VALUES LESS THAN (1, 10),  
   PARTITION p1 VALUES LESS THAN (10, 200), 
   PARTITION p2 VALUES LESS THAN (100, 500), 
   PARTITION pmax VALUES LESS THAN (MAXVALUE, MAXVALUE) 
);

需要注意：如果用函数处理分区键字段，会报语法错误

其次，对于分区键而言，第一个分区键字段必须按从小到大顺序增长，后面的分区字段则没有要求。新增数据sql如下

INSERT INTO sales (field_a, field_b) VALUES (1, 10);

对于多分区字段，当新增数据需要进行分区时，会先用插入的数据对应分区键第一个字段的值来与分区范围作比较，如果第一个分区字段值小于第一个分区范围的第一个字段值，则当前数据属于这个区，不用再比较后面的字段。如果说第一个分区字段值等于第一个分区范围的第一个字段值，则比较第二个分区字段，如果小于则属于第二个分区，否则就属于第三个分区，以此类推。伪代码如下

    假如此时有一条数据 a (x，y)
    if (x < 1 || (x == 1 && y < 10))
        than a belong to "LESS THAN (1, 10)";
    eles if (x < 10 || (x == 10 && y < 200)
        than a belong to "LESS THAN (10, 200)";
    eles if (x < 100 || (x == 100 && y < 500))
        than a belong to "LESS THAN (100, 500)";
    eles 
        than a belong to "LESS THAN (MAXVALUE, MAXVALUE)";

2、LIST COLUMNS

相比起LIST分区，LIST COLUMNS允许多列组合进行分区，sql如下

CREATE TABLE test (
   field_a INT NOT NULL,
   field_b INT NOT NULL,
   PRIMARY KEY (field_a, field_b)
) PARTITION BY LIST COLUMNS ( field_a, field_b ) (
   PARTITION p0 VALUES IN ((1, 10), (1, 100)),  
   PARTITION p1 VALUES IN ((10, 20), (30, 100)), 
   PARTITION p2 VALUES IN ((100, 50), (500, 200))
);

在插入数据的时候，需要两个分区键字段都匹配到，否则会报如下错：
     "1526 - Table has no partition for value from column_list"

LIST COLUMNS和LIST的用法基本差不多，这里就不再赘述。

散列分区（HASH）

HASH区通过将数据哈希后均匀分布到不同的分区中，以达到负载均衡和数据分散的效果。通过HASH分区后，将大型表拆分成多个较小的分区，将数据均匀分散到各个分区，不仅能有效地分散热点读，提高查询速度，也减少了IO操作，降低了查询延迟，提升整体性能

相比起好处，对应的难点是需要评估、分析数据的分布需求，以确保分区策略能有效提升性能。维护的成本也有增加

HASH分区分为常规HASH和(线性)LINEAR HASH。由于两者在使用方式并没有太大差异，所以这里就不再分开说明。

常规HASH是基于对分区键字段值取模运算，根据运算结果，将数据插入到对应的分区。如下sql

CREATE TABLE test_hash (
   field_a INT NOT NULL,
   field_b INT NOT NULL,
   PRIMARY KEY (field_a, field_b)
) PARTITION BY HASH ( field_b )
PARTITIONS 2;

其中，field_b 需要是一个整数类型的表达式，可以是列名，或是计算表达式，PARTITIONS 后面跟着的是分区的数量

需要注意：
    1、同其他分区一样，创表时，如果需要指定主键，则分区键需要被指定在主键中，否则会报如下错：
        "1503 - A PRIMARY KEY must include all columns in the table's partitioning function"
    2、HASH 后面需要确保是整数表达式，不管是列值还是计算后的返回结果，否则会报如下错：
        "1659 - Field 'field_b' is of a not allowed type for this type of partitioning" 
    3、选中列的值与分区表达值应该是尽量一致地增减，这样能更有效地进行散列分区。
        - 简单地说，假如有一个字段是a是DATE类型，函数to_days(a)是随字段a的值变化而一致变化，但是函数
        year()则大多数情况下都不会随字段a的值变化而变化。相比之下，to_days()会比year()更能较好地进
        行hash（注意，这里是说更好的hash，而不是更好的分区，分区应该根据自己的业务场景来选择合适的范
        围进行分区）。
        - 对比上述所说的更好的hash，这里举一个直接不好的分区情况，比如说有一个int字段，当分区表达式为
        pow(1 - a, 4)，这对散列函数是不友好的，你没办法保证a的变化会让表达式一定会有相应的变化。

线性HASH则是将By HASH替换为By LINEAR HASH即可。

对于HASH分区数量的调整，只能通过ALTER TABLE语句进行，sql如下

新增4个分区
ALTER TABLE test_hash ADD PARTITION PARTITIONs 4;

减少4个分区
ALTER TABLE test_hash COALESCE PARTITION 4;

对于两者数据的新增和删除，与其余分区新增删除数据差不多，这里不再赘述，主要是HASH和LINEAR HASH的区别以及与其他分区相比的优势。

1、相比其他分区方式，HASH分区的数据分布更均匀，有助于避免热点问题，更好的面对并发。再者，上面有提到查询数据时只涉及到对应的分区，从而减少IO，提升查询速度，而HASH分区的平均分布的特性会让分区查询性能的提升更明显。不过HASH更适合大型表，且对应的需要考虑硬件问题。

2、关于HASH和LINEAR HASH的区别，HASH是使用分区表达式取模后将数据映射到对应的分区，数据可以平均地分布到各个分区。但是当分区数量发生变化时，需要对全表进行扫描，并将数据根据新的分区规则重新分配，这个过程很消耗资源。然后分区数量的变化可能导致更多的哈希冲突，不仅会导影响数据的访问效率，也会导致资源浪费和性能下降。LINEAR HASH则是通过如下方式来计算分区

1、通过公式 POWER(2, CEILING(LOG(2, num))) 计算得到基数 v
    v = POWER(2, CEILING(LOG(2, num)));
    其中，power()函数为冥函数，CEILING()为向上取整，num为分区数量
2、计算过程变量N
    N = F(column_value) & (v - 1)
    其中，F(column_value)为分区表达式，v则是上面的v，通过这两者进行逻辑与运算得到N
3、对N进行判断获取最终分区号 part_num，伪代码如下
    while(N >= num) {
        N = N & ((v / 2) - 1);
    }  
    part_num = N;

LINEAR HASH相对于HASH来说，对分区的添加、删除、拆分合并操作速度更快（关于这一点，我在两张同样字段的表，均塞了两百万数据的情况下进行分区，在分区数量越多的情况下，时间差距会更明显）。缺点则是数据分布不够均匀。

应用场景：
对于大数据量、需快速访问、数据无明显逻辑关系等场景，HASH分区是不错的选择

键值分区（KEY）

KEY分区与HASH分区几乎相同，也是通过哈希函数来确定数据的分区，只是HASH分区允许用户自定义分区表达式，而KEY分区只能使用MySQL内置的哈希函数。此外，如果表中存在主键或者唯一键，如果创建分区时没有指定字段，即：PARTITION BY KEY()（使用KEY分区创表时，对应HASH分区，只需要将HASH改为KEY即可），首先会默认选择主键作为分区键，如果没有主键则选择非空唯一键（一定要非空）。如果没有主键和非空唯一键，也没有指定列，那么你会收到一条信息如下：

    1488 - Field in list of fields for partition function not found in table

相对于HASH而言，KEY支持的分区键字段类型不仅仅是整数，除了BLOB和TEXT以及没有索引前缀的列都可以是分区键字段。

KEY分区也支持线性规则，与LINEAR HASH相同。由于KEY和HASH用法基本相同，这里不再赘述

关于子分区

目前子分区只支持RANGE和LIST两种分区，或者说是这两种分区的增强功能。创建表的语法如下

CREATE TABLE ts (
    id INT, 
    purchased DATE
) PARTITION BY RANGE( YEAR(purchased) )
  SUBPARTITION BY HASH( TO_DAYS(purchased) )
  SUBPARTITIONS 2 (
      PARTITION p0 VALUES LESS THAN (1990),
      PARTITION p1 VALUES LESS THAN (2000),
      PARTITION p2 VALUES LESS THAN MAXVALUE
  );
  
上述sql，总共有3个分区，每个分区有2个子分区，所以总共有6个分区。这种语法注意的点和RANGE与LIST是差不多的
也可以如下这样

CREATE TABLE ts (
    id INT, 
    purchased DATE
) PARTITION BY RANGE( YEAR(purchased) )
  SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
      PARTITION p0 VALUES LESS THAN (1990) (
          SUBPARTITION s0,
          SUBPARTITION s1
      ),
      PARTITION p1 VALUES LESS THAN (2000) (
          SUBPARTITION s2,
          SUBPARTITION s3
      ),
      PARTITION p2 VALUES LESS THAN MAXVALUE (
          SUBPARTITION s4,
          SUBPARTITION s5
      )
  );
  
 这种方式则要求显示定义的子分区数量必须全部一样，否则就会创建失败。

鉴于子分区的复杂性、性能开销以及必要性，这里不再过多描述，有兴趣可自行去官网查看

Mysql 数据分区