数据库性能优化之分表

```markdown
# 1 背景

在生产站点，我们发现 MySQL 任务表的数据超过了 1700 万行，占用了高达 23G 的空间，导致数据库性能急剧下降，并出现了大量的 504 错误。分析数据后发现，有两个客户疯狂地创建任务，其中一个客户每月超过 150 万条。

我们面临一个问题：只要有 10 个客户每天生成 150 万条任务，在线表的容量就会达到极限。未来，如何处理 1500 万条数据？

# 2 表结构分析

```sql
CREATE TABLE
    IF NOT EXISTS `task`
(
    `customer_id`             CHAR(36) COLLATE `ascii_bin`                NOT NULL,
    `created_time`            INT UNSIGNED                                NOT NULL,
    `root_task_id`            CHAR(17) COLLATE `ascii_bin`                NOT NULL,
    `sub_task_sequence`       SMALLINT UNSIGNED                           NOT NULL,
    `sub_task_id`             CHAR(36) COLLATE `ascii_bin`                NOT NULL,
    `action`                  TINYINT                          DEFAULT -1 NOT NULL,
    
    `task_status`             TINYINT                          DEFAULT -1 NOT NULL,
    `ui_root_task_status`     TINYINT                          DEFAULT -1 NOT NULL,
    xxxx
    PRIMARY KEY (
                 `customer_id`,`created_time` DESC, `root_task_id` DESC,`sub_task_sequence`
        )
) COMMENT 'Response Task Table';
```

分析上述表结构，表中的字段数已达到 实际40 多个，未来可能会进一步增加。

常见的处理方法是分库分表。分表有两种类型：水平分表和垂直分表。

# 3 第一步

## 3.1 分离读写服务

创建两个连接池，一个用于写操作（主库），另一个用于读操作（从库）。根据操作类型选择相应的连接池。

## 3.2 清理 180 天的数据

用户数据保留时间能否缩短？90 天？60 天？（审计日志可追溯）

## 3.3 添加 UDSO 限制 1 万（保存 2 万历史任务）(1 天)

发现大多数客户调用添加 UDSO。

公共接口每天仅触发 1 万次

添加 UDSO 仅保存 2 万行历史数据，因为每天仅有 1 万次

限制每日公共 API 调用次数

## 3.4 监控任务数量

如果超过 100 万，将显示错误日志（同步任务或清理任务）(0.5 天)

# 4 第二步

## 4.1 按业务垂直分表

### 4.1.1 按 rootTask 和 subTask 分表

根据 rootTask 和 subTask 分表的主要原因是将获取 subTask 转变为异步请求。这样，用户请求任务接口将大大减少 504 错误，并进一步提高响应速度。

### 4.1.2 将 subTable 分为主表和次表

当前的 subTask 表太大，未来字段可能会继续增加。将其拆分为常用字段和不常用字段，或需要更改的字段等，以减少每次读取数据的压力。

## 4.2 水平分表

### 4.2.1 按时间范围分表

响应业务的特点是具有时间概念。可以使用 MySQL 分区表。每个月的数据存储在一个独立的分区中，后续删除数据时只需删除整个分区。大多数业务查询只需查询当前和最近的分区数据。

分区表实际上有一个独立的物理表，但在逻辑上看起来像一个表。这一特性意味着在业务层面不需要进行重大更改。

### 4.2.2 按 customer_id 分表

目前，仅有部分客户的数据量很大。如果未来监控到某些客户的数据量较大，可以将这些客户的数据提取出来放在一个独立的表中。然而，如果未来超过 10 个客户的数据量很大，一个月的数据量将达到 1500 万。

## 4.3 水平分表 + 垂直分表

假设我们当前 MySQL 表能够承载的数据量为 1000 万，约 12GB 数据

如果表水平分表，rootTask 分为三个表，单表容量为 5GB。如果这样，单表可以承载 2000 万数据

如果按时间分表，每个月创建一个表，用 6 个表（180 天）来分担压力。这样，20 个客户每个月可以生成 100 万数据。

如果每天有超过 20 个客户这样做，可能需要考虑按客户分表。

具体分析：

1 按照时间分表

# 1 创建分区表
CREATE TABLE task_partitioned (
    `customer_id`             CHAR(36) COLLATE `ascii_bin`                NOT NULL,
    `created_time`            INT UNSIGNED                                NOT NULL,
     .......
     .......
    PRIMARY KEY (`customer_id`, `created_time` DESC, `root_task_id` DESC, `sub_task_sequence`)
) PARTITION BY LIST RANGE (created_time) (
      PARTITION sp0 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01')),
      PARTITION sp1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01')),
      PARTITION sp2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-09-01'))
);

创建触发器，Event,每个月自动建立表

2 按照customer_id分表

1. 创建新分区表 or 针对某些customer新建一张表

# 1 创建分区表
CREATE TABLE task_partitioned (
    `customer_id`             CHAR(36) COLLATE `ascii_bin`                NOT NULL,
    `created_time`            INT UNSIGNED                                NOT NULL,
     .......
     .......
    PRIMARY KEY (`customer_id`, `created_time` DESC, `root_task_id` DESC, `sub_task_sequence`)
) PARTITION BY LIST COLUMNS (customer_id) (
    PARTITION p0 VALUES IN ('68960c94-9be6-4343-a4ca-6408de7aa331', '64d5ff66-4157-4a93-bd27-a991277d809b'),
);

2.数据迁移

• 先将这部分customer 某时间T1之前的数据全部迁移过去

INSERT INTO `task_partitioned` SELECT * FROM `task` WHERE customer_id in ('68960c94-9be6-4343-a4ca-6408de7aa331', '64d5ff66-4157-4a93-bd27-a991277d809b') and created_time <1719195232;

• 数据库表双写(T2)

代码层修改，写task表时候，需要同时写task和task_partitioned表

• 数据(T1-T2数据迁移)

• 数据检查

• 数据切读，某部分customer读新表

• 切换只写新表

1. 后期维护

目前只有EU环境中两个customer数据比较多，未来如果有超过几十甚至上百个customer，可能需要，将customer都迁移出来，那本次的设计考虑到未来的迁移，需要有一定扩展性。

• 新增分区（又有customer数据非常多）

ALTER TABLE task_partitioned
ADD PARTITION (PARTITION p_new VALUES IN ('new_customer_id'));

• 再次进行数据平滑迁移

优化方案-----无数据迁移

1 plan overview

• 考虑task本身的特性（存在有效期，90天）

• build上线后用户数据全部保存至新表中，旧表的历史数据不迁移仅更新

• 共存阶段同时查询新表和旧表数据做数据聚合，运行3个月后（旧库数据已全部失效）

• 用户请求的读写全部切到新库中，旧库部分可以删除。

2 details

1. 创建新表

新表可以采用分区表（按照customer和时间都可以），表结构与原来的保持一致

# 1 创建分区表 CREATE TABLE task_partitioned ( `customer_id` CHAR(36) COLLATE `ascii_bin` NOT NULL, `created_time` INT UNSIGNED NOT NULL, .......... ......... PRIMARY KEY (`customer_id`, `created_time` DESC, `root_task_id` DESC, `sub_task_sequence`) ) PARTITION BY LIST COLUMNS (customer_id) ( PARTITION p0 VALUES IN ('68960c94-9be6-4343-a4ca-6408de7aa331', '64d5ff66-4157-4a93-bd27-a991277d809b'), PARTITION p_default VALUES IN ('default_customer') );

2.业务层修改

（1）读的时候，读的是新表和旧表，然后进行数据耦合

（2）写:写的时候只写新表

但是后续有其他的用户数据增多了，可能还需要增加配置写入新表中