3、表

# 概念

在关系数据库中，**数据库表**是由一系列**二维数组**的集合组成。

- **记录**：表中的每一行称为一个记录，也称为元组（tuple），由若干个字段组成。
- **字段**：也称为域或属性，表中的每一列称为一个字段。每个字段包含两个属性：列名和数据类型。

---

# 表字段类型

- **基本数据类型**：
  - 数值类型
  - 字符类型
  - 日期时间类型

- **用户自定义类型**：
  - 使用 `CREATE TYPE` 定义。

| 数据类型 | 说明 |
|----------|------|
| Smallint | 定长2字节整数，范围是 -32768 ~ +32767 |
| Char(n), Nchar(n) | 定长字符串，不足的部分补空格。n 是指字节长度，默认精度为1，n 小于 10485761。 |
| Int | 定长4字节整数，范围是 -2,147,483,648 ~ +2,147,483,647 |
| Varchar(n), Varchar2(n) | 变长字符串。n 是指字节长度，n 小于 10485761。 |
| Bigint | 定长8字节整数，范围是 -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 |
| Nvarchar2(n) | 变长字符串。n 是指字符长度，n 小于 10485761。 |
| NUMERIC[(p[,s])], DECIMAL[(p[,s])] | 精度 p 取值范围为 [1,1000]，标度 s 取值范围为 [0,p]，精确到小数点后 s 位。 |
| Date | 4字节，只存储日期，精度为天。 |
| Real | 4字节，浮点型数字。 |
| Double | 8字节，浮点型数字。 |
| Timestamp | 8字节，年月日时分秒，不含时区。 |
| Timestamptz | 8字节，年月日时分秒，含时区。 |
| Text | 变长字符串。 |
| Interval | 12字节，计算时间间隔。 |

---

# 表的分类 - 存储方式分类

- **按数据存储方式分类**：
  - **行存储表**
  - **列存储表**

```sql
CREATE TABLE author_row (
    author_id INTEGER,
    author_name CHAR(60),
    author_age INTEGER,
    author_address VARCHAR(255)
) WITH (ORIENTATION= ROW);

CREATE TABLE author_col (
    author_id INTEGER,
    author_name CHAR(60),
    author_age INTEGER,
    author_address VARCHAR(255)
) WITH (ORIENTATION= COLUMN);
```

![title](/api/file/getImage?fileId=682c3d782cbdfd19ee0006a1)

---

# 表 - HStore 表

- **HStore 表**是实时数仓中设计的一种表类型，用于将insert、upsert和update等操作实时快速入库。
- 可以支持单条或小批量的 IUD（Insert/Update/Delete）操作的高并发实时入库，也支持大批量的定期入库。
- HStore 表仅在 8.2.0.100 及以上集群版本支持。
- HStore 表支持冷热数据管理，面向对于实时入库和实时查询有较强诉求的场景，同时拥有处理传统 TP 场景的事务能力。

### HStore 表与列存表的辅助 Delta 表差异

| 数仓类型 | 表结构 | 功能 | 缺陷 |
|----------|--------|------|------|
| 列存的 Delta 表 | 与列存主表的表定义一致。 | 用于暂存小批量 insert 的数据，满阈值后再 merge 到主表，避免直接 insert 到主表产生大量的小 CU。 | 来不及 merge 导致 delta 表膨胀，影响查询性能，无法解决并发 update 的锁冲突问题，依赖后台常驻 autovacuum 来做 merge 操作。 |
| HStore 的 Delta 表 | 与主表表定义不一样。 | 用于持久化存储 update/delete/insert 信息。在故障拉起后用于恢复内存更新链等管理并发更新的内存结构。 | 来不及 merge 导致 delta 表膨胀，影响查询性能，无法解决并发 update 的锁冲突问题，依赖后台常驻 autovacuum 来做 merge 操作。 |

---

# 表的分类 - 各个存储模型下对比

| 存储模型 | 行存 | 列存 | HStore 表 |
|----------|------|------|-----------|
| 数据存储方式 | 以元组为单位，将每一条数据的所有属性值存储到临近的空间里。 | 以 CU（Compress Unit）为单位，将单个属性的所有值存储到临近的空间里。 | 数据主要以 CU 形式存储在列存主表上，对于被更新的列、小批量插入的数据将被序列化后存储到新设计的 Delta 表上。 |
| 数据写入 | 行存压缩暂未商用，数据按原始状态存储，磁盘空间占用较大。 | 按列存储时，由于属性值类型相同具有天然的压缩优势，能节省 IO 资源与磁盘空间占用。 | 被更新的列、小批量插入的数据会序列化后压缩，并定期 merge 到主表 CU。 |
| 数据更新 | 数据按行更新，没有 CU 锁问题，支持并发更新性能良好。 | 即使更新单条数据，也要获取整个 CU 的锁，基本无法支持并发更新。 | 彻底解决列存更新的 CU 锁问题，并发更新性能达到行存的 60%以上。 |
| 数据读取 | 按行读取，即使只需访问某一列的数据，也需要将一整行的数据取出，查询性能较差。 | 按列读取时只需访问该列的 CU，读取性能很好。 | 对于列存主表的数据按列读取，数据 merge 到主表后具有与列存一致的读取优势。 |
| 优点 | 并发更新性能好。 | 查询性能好，磁盘占用空间少。 | 并发更新性能好，数据 merge 后具有与列存一致的查询性能优势与压缩优势。 |
| 缺点 | 占用磁盘空间多，查询性能差。 | 基本无法支持并发更新。 | 需要后台常驻线程对 HStore 表进行 merge 清理操作。 |
| 适用场景 | 1. 更新删除操作频繁的 TP 事务场景。 2. 点查询（基于索引的、返回数据量小的简单查询）。 | 1. 查询分析为主的 AP 场景。 2. 数据量大，存入后的更新删除操作少。 | 1. 实时并发入库场景。 2. 需要支持高并发的更新入库操作以及高性能的查询效率。 |

---

# 表的分类 - 分布方式分类

- **按数据分布方式分类**：
  - **复制（Replication）表**：每个数据节点都有完整的表数据。
  - **哈希（Hash）表**：对表中指定的列进行哈希，根据哈希值映射到指定的数据节点；应选择数据分布较均匀的列作为 hash 分布列。
  - **轮询（Roundrobin）表**：轮番选择数据节点保存一行数据；默认创建方式；可通过配置 GUC 参数 `default_distribution_mode` 修改默认分布方式。

![title](/api/file/getImage?fileId=682c3dcb2cbdfd19ee0006a2)

---

# 表的分类 - 分区方式分类

- **按表的分区方式分类**：
  - **Range 分区**：
    ```sql
    CREATE TABLE orders_p (
        order_id NUMBER(6) NOT NULL,
        order_name VARCHAR2(20),
        order_price DECIMAL(9,2),
        order_date DATE
    )
    PARTITION BY RANGE(order_date) (
        PARTITION P0 VALUES LESS THAN ('2017-01-01'),
        PARTITION P1 VALUES LESS THAN ('2018-01-01'),
        PARTITION P2 VALUES LESS THAN ('2019-01-01'),
        PARTITION P3 VALUES LESS THAN ('2020-01-01')
    );
    ```
  
  - **List 分区**：
    ```sql
    CREATE TABLE geographic_info (
        country text,
        remark VARCHAR2(100)
    )
    PARTITION BY LIST (country) (
        PARTITION asia VALUES ('CHINA', 'JAPAN'),
        PARTITION europe VALUES ('ITALY', 'SWITZERLAND'),
        PARTITION rest VALUES (DEFAULT)
    );
    ```

![title](/api/file/getImage?fileId=682c3df82cbdfd19ee0006a3)

---

# 表的分类 - 其他分类

- **其他分类**：
  - **UNLOGGED 表（非日志表）**：
    - 不记录 Redo 日志，减少日志量，提高数据写性能。
    - 没有 Redo 日志，出现故障后数据库重启无法恢复。
    - 适用于可靠性要求不高的非核心数据。

- **临时表**：
    - 用来保存一个会话或一个事务中需要的数据，数据是临时的，过程性的。
    - 表定义及数据仅当前会话可见。
    - 分为会话级临时表和事务级临时表。

![title](/api/file/getImage?fileId=682c3e182cbdfd19ee0006a5)

---

# 表的约束

- **定义约束方法**：
  - 列约束
  - 表约束

- **常见约束类型**：
  - 主键约束
  - 唯一约束
  - 检查约束（仅支持行存表）
  - 非空约束
  - DEFAULT 约束
  - PARTIAL CLUSTER KEY（仅支持列存表）

![title](/api/file/getImage?fileId=682c3e3e2cbdfd19ee0006a6)

---

# 表创建方式

- **CREATE TABLE AS**：
  - 根据查询结果创建表，字段和 SELECT 输出字段的名字及数据类型相关。
  - 默认用来自 SELECT 命令的结果填充该表。指定 `WITH NO DATA` 时，不填充表数据。
  - 分区表不能采用此方式进行创建。
  
  示例：
  ```sql
  CREATE TABLE test_tpcds2 AS SELECT * FROM test_tpcds WITH NO DATA;
  ```

- **CREATE TABLE LIKE**：
  - 自动从 LIKE 指定的源表中继承所有字段名及其数据类型和非空约束。
  - `INCLUDING/EXCLUDING` 可指定新表是否继承或不继承源表的某些属性。

示例：
  ```sql
  CREATE TABLE customer_t3 (LIKE customer_t1 INCLUDING DEFAULTS EXCLUDING DISTRIBUTION);
  ```

---

# 表的修改和删除

- **ALTER TABLE**：
  - 添加、删除、修改列信息、约束信息、重命名列、约束、修改分布方式。
  - 增加新字段：`ADD COLUMN column_name data_type`
  - 修改表已存字段的数据类型：`MODIFY column_name data_type`
  - 删除一列：`DROP COLUMN column_name`
  - 修改非空约束：`ALTER [COLUMN] column_name {SET | DROP} NOT NULL`
  - 修改分布方式：`DISTRIBUTE BY {REPLICATION | ROUNDROBIN | {HASH (column_name[,…])}}`

- **DROP TABLE**：
  - 类似用户处理，必要时添加 `CASCADE` 进行级联删除。

---

# 表信息查询

- **元命令**：`\d`
- **系统视图**：
  - `pg_tables`
  - `xxx_part_tables`
  - `xxx_tab_partitions`
- **系统表**：
  - `pg_class`
  - `pg_partition`
- **系统函数**：
  - `pg_get_tabledef`

![title](/api/file/getImage?fileId=682c3e8f2cbdfd19ee0006a7)

---

# 表的设计思路

表的设计要考虑以下几点：存储模型、分布方式、分布列、分区等。

![title](/api/file/getImage?fileId=682c3eb32cbdfd19ee0006a9)

DBLOG

导航1234

最近发表

友情链接