正文

分库分表，相信大家多少都有了解过其中的一些方案设计与原理。

而今天讲的是分区，对于分区又有多少小伙伴了解过呢？

小年最近是碰到这么一个业务背景：在用户进入购买主页面，需要展示用户最近几个月购买记录的商品列表。

实现的方案有很多，这里简单列举几个常见的：

直接查询用户交易订单表，获取近几个月内的交易记录（订单表记录商品）
增加一个用户购买商品流水表，这个表只需记录用户最近几个月的流水

（当然，大家有更好的方案欢迎留言）

方案1，这是大家最为容易想到和实现简单的一种。但是考虑到订单表的数据量会越来越多，而且用户购买相同的商品会重复，查询需要基于商品、时间等过滤，SQL 层面会稍微复杂一些。当然重要的一点还是会对订单表有影响，如果出现性能问题的话。

方案2，新增一个用户购买商品流水表，只需要记录近几个月的数据，在流程是实现上，只需要在写入订单表后，同时记录一条商品流水。数据量相比订单表来说肯定是少了不少，流水表的字段也只保留商品信息，关键是读写都不会影响订单表的性能。

经过一番对比，最终小年选择了方案2。

既然是保留用户最近近个月的数据，那么怎么实现呢？

定时删除数据？
分区表？

定时删除，可以通过应用程序的定时任务，或者数据库的脚本比如存储过程之类。

分区表，通过数据库物理存储层的实现，按照时间条件将数据存储于不同的分区，把不需要的分区数据删掉。

什么是表分区？

简单概述一下分库/分表/分区这几个概念：

分库：把一个数据库拆分成多个库，也就是数据分散在多个数据库实例上。
分表：把一张表按照一定的规则，分成多个独立存储空间的实体表。对数据的读写都必须按照规则，路由到相应的实体表操作。
分区：把一张表的数据分成多个区块，在逻辑上看还是一张表，但是在物理存储上，数据是存储在不同的区块。

假设有一张订单记录表（简略版），用于记录用户的商品购买历史。

CREATE TABLE `order_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '订单号',
  `member_id` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '用户ID',
  `product_id` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '商品ID',
  `create_time` datetime DEFAULT NULL COMMENT '订单创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

在未分区之前，所有的数据都是存储在一个 .ibd 文件

改成分区表之后（按照时间做分区），就会生成不同的时间分区，数据是分别存储在不同的 .ibd 文件。

现在应该可以更好理解，分区，在物理存储上，就是将同一个表中的行数据存储到不同的物理文件中，有几个分区就有几个 .ibd 文件。

当然，在逻辑层面上我们操作的还是同一个表。

分区类型

1. RANGE

基于行数据的列值，划分不同的范围分区。主要用于日期列的分区，可以根据年月来存放数据，比如交易表，流水表等。

像上面的例子中的 order_history 表，用的就是 RANGE 分区，按照年月来做分区。把数据按年月分区，如果只想保留近几个月的数据，可以直接删掉历史月份的数据文件即可。

CREATE TABLE `order_history` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `member_id` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (to_days(create_time))
(
     PARTITION p202301 VALUES LESS THAN (to_days('2023-02-01')),
     PARTITION p202302 VALUES LESS THAN (to_days('2023-03-01')),
     PARTITION p202303 VALUES LESS THAN (to_days('2023-04-01')),
  	 PARTITION pmax VALUES LESS THAN MAXVALUE
);

关键语法：

`PARTITION BY RANGE``
``VALUES LESS THAN`

这里是根据字段 create_time 按照年月来做分区，写入的每一条数据，都会预先判断要分配哪个分区，如果不存在 MySQL 会直接报错。比如插入一条 2023-06-01 的数据，在这里找不到匹配的分区，会直接报错。

所以有一点麻烦的是，需要手动预先分配分区。当然你也可以像上面一样，增加一个 PARTITION pmax VALUES LESS THAN MAXVALUE，在找不到匹配不到具体的分区时，都放到 pmax 的分区中，不过不建议这么用，这样的话分区就没什么意义了。

# 增加分区
alter table `tr` add partition(PARTITION p6 VALUES LESS THAN (2020));

#删除分区
alter table `tr` drop partition p1;

#查看分区
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tr';

# 查看某个分区的数据
SELECT * FROM `tr` PARTITION (p2);

# 清空某分区的数据
alter table `tr` truncate partition p0;

2. LIST

LIST 分区与 RANGE 分区有点相似，不过 LIST 分区字段的是基于明确的枚举范围。

CREATE TABLE product (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `product_name` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '商品名称',
    `type` INT(2) NOT NULL COMMENT '1-数码 2-零食 3-日用',
    PRIMARY KEY (`id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY LIST(type) 
(
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3)
);

关键语法：

PARTITION BY LIST
IN()

适用于分区字段是固定范围的枚举值。

3. HSAH

这个就更容易理解，就是对指定的分区字段，做哈希处理，从而判断是分配到哪个分区中。

CREATE TABLE product (
    id INT NOT NULL,
    product_name VARCHAR(64),
    type INT COMMENT '1-数码 2-零食 3-日用',
)
PARTITION BY HASH(id)
PARTITIONS 4；

关键语法：

PARTITION BY HASH
PARTITIONS

这里的 PARTITIONS4 指的是分区数为4

常见的分区类型就大概就这3种，还用子分区的嵌套组合用法，可能就比较稍微复杂，这里就不展开了。

聪明的同学可能会发现，在分区表的 DDL 中，主见索引是有稍微不一样的。比如例子中的 order_history 表，分区后主键变为 PRIMARY KEY (id,create_time)。

这是因为 MySQL 的分区字段，必须包含在主键字段或者唯一索引列中，也就是分区的字段必须全部都属于主键或者唯一索引列的一部分或者全部，否则创建分区将直接报错。

Pros VS Cons

Pros

单表存储更多的数据。因为将数据分摊到不同的物理文件中，所以对比未分区表来说，分区表的物理文件会更小。
查询性能高，提高检索速度。可以指定必要的分区来提高查询效率。
便于维护，可用性高
- 比如历史数据需要定时清理，通过清除整个分区快速实现。也可以增加新的分区来支持新插入的数据。
- 如果表的某个分区出现故障，需要修复数据，只修复该分区即可，不影响其他分区，可用性相对较高。
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

PS：分区也是大表优化的一个不错方案哦。

Cons

分区数限制，一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）
分区的字段，必须是表上所有的唯一索引（或者主键索引）包含的字段的子集
分区表中无法使用外键约束
维护分区的成本可能很高。如果数据更新频率比较高，这个会导致行数据的重新分配分区。

在什么场景中会适合用分区呢？

业务简单，单表查询，且都跟时间范围查询相关
数据需要定期清理数据，无需保留全部数据
数据更新频率较低，只有写入操作。