二、GaussDB数据库开发设计建议笔记

## 一、数据库对象与管理

### 1. 表空间（Tablespace）
- **定义**：表空间是操作系统中的一个目录，用于存储数据库的物理文件。
- **优点**：
  - 可在不同分区创建表空间以扩展存储。
  - 可控制磁盘占用，防止占用其他空间。
  - 磁盘使用率达90%时数据库转为只读，低于90%时恢复读写。
- **系统自带表空间**：
  - `pg_default`：存储系统目录、用户表、索引、临时表等。
  - `pg_global`：存储系统字典表。

### 2. 表空间管理命令
```sql
-- 创建
CREATE TABLESPACE tbs2 RELATIVE LOCATION 'tablespace/tbs2' MAXSIZE '100G';
CREATE TABLESPACE tbs3 OWNER jack LOCATION '/gauss/data/tbs3';

-- 查询
\db
SELECT * FROM pg_tablespace_location((SELECT oid FROM pg_tablespace WHERE spcname='tbs2'));
SELECT oid,* FROM pg_tablespace;

-- 修改
ALTER TABLESPACE tbs3 RENAME TO tbs4;
ALTER TABLESPACE tbs4 OWNER TO jack;
ALTER TABLESPACE tbs4 RESIZE MAXSIZE UNLIMITED;

-- 删除
DROP TABLESPACE tbs4;
```

---

### 3. 普通表管理
#### 创建表
```sql
CREATE TABLE emp1 AS SELECT * FROM emp WHERE sal<2000;
CREATE TABLE IF NOT EXISTS warehouse_t1 (
    W_WAREHOUSE_SK    INTEGER NOT NULL,
    W_WAREHOUSE_ID    CHAR(16) NOT NULL,
    W_WAREHOUSE_NAME  VARCHAR(20) UNIQUE DEFERRABLE,
    W_STATE           CHAR(2) DEFAULT 'GA'
) TABLESPACE tbs1;
```

#### 修改表
```sql
ALTER TABLE emp1 MODIFY sal NUMBER(10,2);
ALTER TABLE emp1 RENAME COLUMN ename TO name;
ALTER TABLE emp1 ADD PRIMARY KEY (empno);
ALTER TABLE emp1 ADD CONSTRAINT chk_dept CHECK (deptno IS NOT NULL);
ALTER TABLE emp1 ADD CONSTRAINT fk_dept FOREIGN KEY (deptno) REFERENCES dept(deptno);
ALTER TABLE emp1 RENAME CONSTRAINT chk_dept TO chk_deptno;
ALTER TABLE emp1 SET SCHEMA jack;
ALTER TABLE jack.emp1 RENAME TO emp2;
```

#### 行级访问控制（RLS）
```sql
CREATE USER alice PASSWORD 'gauss@123';
CREATE TABLE all_data(id INT, role VARCHAR(100), data VARCHAR(100));
ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;
CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
```

---

### 4. 分区表
#### 分区类型
- 范围分区（Range）
- 哈希分区（Hash）
- 列表分区（List）

#### 创建范围分区
```sql
CREATE TABLE part_tbl1 (a INT, b INT)
PARTITION BY RANGE(a) (
    PARTITION part1 VALUES LESS THAN (10),
    PARTITION part2 VALUES LESS THAN (100),
    PARTITION part3 VALUES LESS THAN (MAXVALUE)
);

-- 使用 START END
CREATE TABLE part_tbl2 (a INT, b INT)
PARTITION BY RANGE(a) (
    PARTITION part1 START(1) END(100) EVERY(50),
    PARTITION part2 END(200),
    PARTITION part3 START(200) END(300)
);
```

#### 分区管理
```sql
-- 查询分区
SELECT * FROM pt1 PARTITION (p3);

-- 删除分区
ALTER TABLE pt1 DROP PARTITION p3;

-- 增加分区
ALTER TABLE pt1 ADD PARTITION p3 VALUES LESS THAN (95);

-- 重命名分区
ALTER TABLE pt1 RENAME PARTITION p4 TO pmax;

-- 分离与合并分区
ALTER TABLE pt1 SPLIT PARTITION p3 AT (90) INTO (PARTITION p4, PARTITION p5);
ALTER TABLE pt1 MERGE PARTITIONS p4, p5 INTO PARTITION p3;
```

---

### 5. 索引
#### 索引类型
- B-Tree（默认，适合范围查询）
- GIN（倒排索引，适合数组）
- GiST（适合地理、几何类型）

#### 创建索引
```sql
CREATE UNIQUE INDEX t1_fn_idx ON t1(refillencode);
CREATE INDEX t1_owner_tbs_idx ON t1(relowner, retablespace);
CREATE INDEX t1_lttbs_idx ON t1(retablespace) WHERE retablespace < 20;
CREATE INDEX t1_upname_idx ON t1(UPPER(relname));
CREATE INDEX pt1_id_idx ON pt1(id) LOCAL;          -- 分区本地索引
CREATE INDEX pt1_score_idx ON pt1(score) GLOBAL TABLESPACE tbs1; -- 分区全局索引
```

#### 修改与重建索引
```sql
ALTER INDEX t1_fn_idx RENAME TO t1_fn_idx2;
ALTER INDEX t1_fn_idx2 SET TABLESPACE tbs2;
ALTER INDEX t1_lttbs_idx UNUSABLE;
ALTER INDEX t1_lttbs_idx REBUILD;
REINDEX INDEX t1_lttbs_idx;
REINDEX TABLE t1;
```

---

### 6. 视图
#### 创建视图与物化视图
```sql
CREATE VIEW v1 AS SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
CREATE MATERIALIZED VIEW mv1 TABLESPACE tbs1 AS SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
```

#### 管理视图
```sql
ALTER VIEW v1 RENAME TO v2;
ALTER VIEW v2 OWNER TO jack;
ALTER VIEW v2 SET SCHEMA jack;
REFRESH MATERIALIZED VIEW mv1;
DROP VIEW jack.v2;
DROP MATERIALIZED VIEW mv1;
```

---

### 7. 序列（Sequence）
#### 创建与使用
```sql
CREATE SEQUENCE seq01;
CREATE SEQUENCE seq02 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CACHE 1 NOCYCLE;

SELECT NEXTVAL('seq01');
SELECT CURRVAL('seq01');
SELECT LASTVAL();
SELECT SETVAL('seq01', 100);

-- 应用为默认值
CREATE TABLE t2(id SERIAL, name VARCHAR(20), tag INT);
ALTER TABLE t2 ALTER tag SET DEFAULT NEXTVAL('seq01');
```

---

### 8. 同义词（Synonym）
```sql
CREATE SYNONYM syn_t1 FOR t1;
CREATE SYNONYM syn_emp FOR v_emp;
CREATE SYNONYM syn_add FOR func_add_sql;
CREATE SYNONYM syn_proc_emp FOR proc_emp;

SELECT * FROM syn_emp;
CALL syn_proc_emp(7566, name, job, sal);

DROP SYNONYM syn_add;
```

---

## 二、存储过程（Stored Procedure）

### 1. 概述
- 存储过程是预编译的 SQL 语句集合，存储在数据库中。
- 优点：封装性好、性能高、安全性强。
- 缺点：与数据库绑定度高，不易移植。

### 2. 匿名块与函数
- **匿名块**：临时执行，不存储。
- **函数**：必须返回单个值，存储过程可不返回。

### 3. PL/SQL 结构
```sql
DECLARE
    my_var VARCHAR2(30);
BEGIN
    my_var := 'world';
    dbe_output.print_line('hello' || my_var);
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;
/
```

### 4. 创建存储过程
```sql
CREATE OR REPLACE PROCEDURE prc_div (
    param1 IN INTEGER,
    param2 IN OUT INTEGER
) AS
BEGIN
    param2 := param1 / param2;
    dbe_output.print_line('result is: ' || TO_CHAR(param2));
EXCEPTION
    WHEN division_by_zero THEN
        dbe_output.print_line('caught division_by_zero');
END;
/
```

### 5. 控制语句
#### IF 条件
```sql
IF v_user_id <> 0 THEN
    RAISE INFO 'v_user_id is NOT 0';
ELSIF v_user_id < 0 THEN
    RAISE INFO 'v_user_id is negative';
ELSE
    RAISE INFO 'v_user_id is 0';
END IF;
```

#### 循环
```sql
-- LOOP
LOOP
    EXIT WHEN count > 10;
    count := count + 1;
END LOOP;

-- WHILE
WHILE i < maxval LOOP
    i := i + 1;
END LOOP;

-- FOR
FOR i IN 0..5 LOOP
    dbe_output.print_line('It is ' || i || ' time');
END LOOP;

-- FORALL（批量操作）
FORALL i IN 100..120
    UPDATE hdfs_t1 SET title = title + 100*i;
```

#### CASE 分支
```sql
CASE pl_result
    WHEN 1 THEN pl_return := 111;
    WHEN 2 THEN pl_return := 222;
    ELSE pl_return := 999;
END CASE;
```

### 6. 游标（Cursor）
#### 显式游标
```sql
DECLARE
    CURSOR c1 IS SELECT section_name, place_id FROM hr.sections WHERE section_id <= 50;
    dept_name VARCHAR(100);
    dept_loc INT;
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO dept_name, dept_loc;
        EXIT WHEN c1%NOTFOUND;
        dbe_output.print_line(dept_name || '---' || dept_loc);
    END LOOP;
    CLOSE c1;
END;
```

#### 隐式游标
```sql
DELETE FROM hr.staffs WHERE section_id = v_deptno;
IF SQL%NOTFOUND THEN
    DELETE FROM hr.department WHERE section_id = v_deptno;
END IF;
```

### 7. 异常处理
```sql
BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
    WHEN OTHERS THEN
        RAISE NOTICE 'unknown error';
END;
```

### 8. 动态 SQL
#### 执行动态查询
```sql
EXECUTE IMMEDIATE 'SELECT first_name, salary FROM hr.staffs WHERE staff_id = :1'
    INTO first_name, salary
    USING IN staff_id;
```

#### 执行动态 DML
```sql
EXECUTE IMMEDIATE 'INSERT INTO sections_t1 VALUES(:1, :2, :3, :4)'
    USING section, section_name, manager_id, place_id;
```

#### 动态调用存储过程
```sql
EXECUTE IMMEDIATE 'CALL proc_add(:1, :2, :3)'
    USING IN input1, OUT param2, IN input2;
```

---

## 三、JDBC 高可用

### 1. 负载均衡策略
| 策略 | 配置示例 | 说明 |
|------|----------|------|
| 轮询 | `autoBalance=true` | 轮流选择可用 CN |
| 优先级 | `autoBalance=priority2` | 优先前2个节点，不可用则随机 |
| 随机 | `autoBalance=shuffle` | 随机选择可用 CN |
| 关闭 | `autoBalance=false` | 默认关闭 |

### 2. 读写分离
```java
jdbc:gaussdb://host1:port1,host2:port2/database?targetServerType=master
```
- `master`：连接主节点
- `slave`：连接备节点
- `preferSlave`：优先备节点，不可用则主节点
- `any`：任意节点

### 3. 日志记录
```java
jdbc:gaussdb://host1:port1/database?logger=Slf4jLogger&loggerLevel=DEBUG
```
- 级别：OFF、INFO、DEBUG、TRACE
- 支持 Slf4j 框架

---

## 四、数据库对象命名和设计建议

### 1. 命名规则
- 长度 ≤ 63 字符
- 以字母或下划线开头
- 避免使用关键字
- 风格统一，避免大小写敏感（除非必要）

### 2. Database 与 Schema
- 使用 Schema 进行业务隔离
- 创建 Database 时注意编码（推荐 UTF-8）和兼容模式（A/B/C/M/PG）
- Schema 的 usage 权限需单独赋予

### 3. 字段设计
- 优先使用整型
- 短字段优先
- 关联字段类型一致

### 4. 约束设计
- 避免滥用 DEFAULT
- NOT NULL 约束可优化查询
- 显式命名约束（如 `PK_id`、`UN_phone`、`CK_gender`）

### 5. 视图与关联表
- 避免视图嵌套
- 避免视图排序
- 关联字段尽量少且类型一致

---

## 五、表设计最佳实践

### 1. 分布方式选择
| 策略 | 适用场景 |
|------|----------|
| Hash | 大表、事实表 |
| Replication | 小表、维度表 |
| Range/List | 自定义分布规则 |

### 2. 分布列选择原则
- 离散度高
- 常作为连接条件
- 避免数据倾斜（相差 >5% 需调整）

### 3. 分区表使用
- 范围分区适用于时间或地区
- 避免在分区键上使用索引扫描

### 4. 数据类型选择
- 整型优于字符串
- 短字段优于长字段
- 关联字段类型一致

### 5. 分布式事务设计
- 尽量设计为单机事务
- 避免跨节点关联

---

## 六、SQL 查询最佳实践

### 1. WHERE 子句优化
- 包含分布键等值条件
- 避免标量子查询，改为 JOIN
- 条件顺序按筛选率排列
- 避免隐式类型转换
- 避免对字段使用函数或表达式

### 2. SELECT 建议
- 避免使用 `*`
- 避免频繁 `COUNT(*)` 大表，可查 `pg_class.reltuples`
- 避免对大字段排序、去重、分组

### 3. UPDATE 建议
- 禁止同时更新多列为同一源
- 禁止使用 ORDER BY/GROUP BY

### 4. DELETE 建议
- 禁止使用 ORDER BY/GROUP BY
- 清空表使用 `TRUNCATE` 而非 `DELETE`

---

## 七、思考题答案（仅供参考）

1. ✅ 正确  
2. B（`col1 IS NOT NULL`）  
3. B、D（BEGIN 和 END 是必需的）  
4. A（`i=2>0`，输出 AAA）  
5. 
   - `TRUNCATE`：快速清空表，不可回滚，释放空间  
   - `DELETE`：逐行删除，可回滚，不立即释放空间  
   - `DROP`：删除表结构和数据  
6. B（`param2 = 2 * 1 = 2`）

---

## 八、缩略语
- SQL：结构化查询语言
- CN：协调节点
- DN：数据节点

FYF

导航1234

最近发表

友情链接