4、分区表

# 分区管理概述 – 分区表优势

- **分区表和普通表相比，有以下优势**：
  - **改善查询性能**：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
  - **增强可用性**：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
  - **方便维护**：如果分区表的某个分区出现故障，只需修复该分区即可。
  - **均衡 I/O**：可以将不同的分区映射到不同的磁盘，从而平衡 I/O，改善整个系统性能。

---

# 分区管理概述 – 分区策略选择依据

**分区策略选择依据**：

- **数据具有明显区间性的字段**：分区表需要根据有明显区间性字段进行表分区，例如按照日期、区域、数值等字段进行分区，时间字段是最常见的分区字段。
- **业务查询有明显的区间范围特征**：查询数据可落到区间范围指定的分区内，才能通过分区剪枝，仅扫描查询需要的分区，从而提升数据扫描效率，降低数据扫描的 IO 开销。
- **表数据量比较大**：小表扫描本身耗时不大，分区表的性能收益不明显，因此只建议对大表采取分区策略。列存储模式下，每个列是单独的文件存储，且最小存储单元 CU 可存储 6w 行数据，因此对于列存分区表，建议每个分区的数据不少于 DN 的数量乘以 6w。

---

# 分区类型

- **范围分区（Range Partitioning）**：
  - 基于数值型范围划分数据，例如按日期或价格区间定义。
  - 范围分区表将数据基于范围映射到每个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，分区键经常采用日期，例如将销售数据按照月份进行分区。
  - 当在范围分区表中插入不符合范围条件的记录时，会出错。
  - **间隔分区**：在发现记录映射不到任何分区时，会根据间隔条件（INTERVAL）自动创建分区。
  
- **列表分区（List Partitioning）**：
  - 基于一个值列表划分数据，例如按销售范围或产品属性定义。仅在 8.1.3 及以上集群版本支持。

---

# 管理分区表

**操作命令**：

- **创建分区表**：使用 `CREATE TABLE` 命令创建分区表。
- **修改分区表行迁移属性**：使用 `ALTER TABLE { ENABLE | DISABLE } ROW MOVEMENT` 命令修改分区表行迁移属性。
- **增加分区**：使用 `ALTER TABLE ADD PARTITION` 命令为分区表增加分区。
- **删除分区**：使用 `ALTER TABLE DROP PARTITION` 命令删除分区表的分区。
- **重命名分区**：使用 `ALTER TABLE RENAME PARTITION` 命令重命名分区。
- **清空分区**：使用 `ALTER TABLE TRUNCATE PARTITION` 命令清空分区。
- **分区合并**：使用 `ALTER TABLE MERGE PARTITIONS INTO` 命令合并多个分区为一个分区。
- **分区分裂**：使用 `ALTER TABLE SPLIT PARTITION` 命令将一个分区切割成多个分区。
- **修改分区的表空间**：使用 `ALTER TABLE MOVE PARTITION TABLESPACE` 命令修改分区的表空间。
- **查询分区**：使用 `SELECT` 命令查询分区。
- **创建分区表索引**：使用 `CREATE INDEX` 命令创建分区表索引。
- **重命名索引分区**：使用 `ALTER INDEX RENAME PARTITION` 命令重命名分区索引。
- **修改索引分区的表空间**：使用 `ALTER INDEX MOVE PARTITION TABLESPACE` 命令修改索引分区的表空间。

---

# 分区表基本操作 (1)

**创建示例**：
创建范围分区表 `student1`，含有三个分区，分区键为整数类型。三个分区的范围分别为：`score1 < 60`，`60 <= score2 < 80`，`80 <= score3 < 90`。

```sql
CREATE TABLE student1 (
    name VARCHAR(30),
    score INT,
    birthday TIMESTAMP
)
PARTITION BY RANGE(score) (
    PARTITION score1 VALUES LESS THAN (60),
    PARTITION score2 VALUES LESS THAN (80),
    PARTITION score3 VALUES LESS THAN (90)
);
```

---

# 分区表基本操作 (2)

**删除示例**：
删除分区 `score1`：

```sql
ALTER TABLE student1 DROP PARTITION score1;
```

**增加示例**：
增加分区 `score4`，范围为 `90 <= score4 <= 100`：

```sql
ALTER TABLE student1 ADD PARTITION score4 VALUES LESS THAN (101);
```

**重命名示例**：
重命名分区 `score2` 为 `score2_new`：

```sql
ALTER TABLE student1 RENAME PARTITION score2 TO score2_new;
```

---

# 分区表基本操作 (3)

**查询示例**：
查询分区 `score2`：

```sql
SELECT * FROM student1 PARTITION (score2_new);
```

**分区表索引**：
- 创建分区表索引 `student1_index1`，不指定索引分区的名字：

```sql
CREATE INDEX student1_index1 ON student1 (score) LOCAL;
```

- 创建分区索引 `student1_index2`，并指定索引分区的名字：

```sql
CREATE INDEX student1_index2 ON student1 (score) LOCAL (
    PARTITION score1_index,
    PARTITION score2_index TABLESPACE ts2,
    PARTITION score3_index TABLESPACE ts3
) TABLESPACE ts0;
```

---

# 分区表基本操作 (4)

**分区表索引**：
- 重命名索引分区：

```sql
ALTER INDEX student1_index2 RENAME PARTITION score1_index TO score1_index_new;
```

- 修改索引分区 `score1_index` 的表空间为 `ts1`：

```sql
ALTER INDEX student1_index2 MOVE PARTITION score1_index_new TABLESPACE ts1;
```

---

# 分区设计总结

- 针对已经存在的表进行分区，最好在数据迁移完成后再建立索引。
- 如果数据表已经存在，建议先建立分区表，然后使用非堵塞式的迁移接口。
- 为了充分利用分区表的查询优势，必须使用分区字段作为过滤条件。
- 需要注意分区后就没有全局唯一性，各个分区之间可以有重复的 UUID。
- 对于分区键条件查询，效率非常高。
- 分区的字段必须是非空，类似案件的立案日期和结案日期就不能用作分区字段。
- `VACUUM` 或 `ANALYZE` 只会对主表起作用，若要分析分区表，需要分别分析每个分区表。
- 分区的备份可以单独备份各个分区，但若要备份所有分区只能备份整个 schema。
- 数据迁移到分区表后，建议禁用主表；如果主表未执行 `VACUUM` 操作，执行计划可能会全表扫描主表，非常耗时。

DBLOG

导航1234

最近发表

友情链接