# GaussDB 数据库开发设计建议（考试笔记）

---

## 一、总体设计原则

### 设计目标

* 保证 **高性能、高可用、可扩展性**
* 避免因设计缺陷导致 SQL 性能下降
* 充分利用 **GaussDB 分布式并行特性**

### 核心设计原则

1. **分而治之**：分区、分表、分布式设计
2. **局部优化优于全局调整**：SQL 优化应聚焦热点语句
3. **高并发优先**：减少全表锁与大事务
4. **存储与计算分离**：减轻 I/O 与网络瓶颈

---

## 二、数据建模设计

### 表设计建议

* 表字段总数控制在 **100个以内**
* 行长度不超过 **8KB**（防止行迁移）
* 避免使用大量 **NULLABLE** 字段
* 大表建议采用 **分区表（Partition Table）**

### 分区表设计

* 建议选择以下列类型：

* `DATE` / `NUMBER` / `VARCHAR2`
* 分区策略：

* RANGE（按时间分区）
  * LIST（按地区或业务类型分区）
  * HASH（均匀分布）
* 每个分区数据量建议 ≤ 1000万行

### 示例

```sql
CREATE TABLE sales_data (
  sale_id BIGINT,
  sale_date DATE,
  region VARCHAR(32),
  amount NUMERIC(12,2)
)
PARTITION BY RANGE (sale_date)
(
  PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2025 VALUES LESS THAN (MAXVALUE)
);
```

---

## 三、索引设计建议

### 索引设计原则

* **索引并非越多越好**：过多索引影响 DML 性能
* **高选择性列** 优先创建索引（选择性 > 0.3）
* 联合索引：根据 SQL 过滤条件列顺序匹配
* 避免在频繁更新字段上建索引
* 索引命名建议：`IDX_<表名>_<字段名>`

### 索引类型

| 类型             | 特点         | 场景         |
| -------------- | ---------- | ---------- |
| B-Tree         | 范围查找高效     | 主键、外键、唯一约束 |
| Bitmap         | 低基数字段（如性别） | 只读查询       |
| Function Index | 对表达式创建索引   | 常用函数过滤     |
| Full Text      | 模糊匹配与全文搜索  | 文本列        |

### 索引示例

```sql
CREATE INDEX idx_emp_dept ON emp(dept_id);
CREATE INDEX idx_order_expr ON orders(LOWER(customer_name));
```

---

## 四、SQL 设计与优化

### SQL 编写原则

* 避免使用 `SELECT *`
* 避免隐式类型转换，例如：

```sql
  WHERE col_num = '100'  -- 错误
  WHERE col_num = 100    -- 正确
  ```
* 尽量使用绑定变量，避免硬解析
* 合理使用 `JOIN`：

* 小表驱动大表
  * 尽量使用 `INNER JOIN` 替代 `OUTER JOIN`
* 尽量避免：

* `NOT IN`（改为 `NOT EXISTS`）
  * `UNION`（改为 `UNION ALL`）

### SQL 优化重点

| 类别       | 建议                             |
| -------- | ------------------------------ |
| 查询语句     | 尽量走索引扫描，避免全表扫描                 |
| 聚合查询     | 使用 `GROUP BY` 而非嵌套子查询          |
| 分页查询     | 优化 `LIMIT + OFFSET`，建议使用 ID 游标 |
| 子查询      | 尽量改写为 JOIN                     |
| COUNT 优化 | 对大表可使用统计表或缓存计数                 |

---

## 五、事务与锁设计

### 事务设计

* 保持事务简短，避免长事务占用资源
* 避免多表大事务（建议分解）
* 禁止显式控制锁，如 `LOCK TABLE`
* 合理控制事务隔离级别（默认 `READ COMMITTED`）

### 锁冲突优化

* 尽量使用批量提交（`BATCH INSERT/UPDATE`）
* 避免热点行竞争（可引入 HASH KEY）
* 对高并发更新场景可采用 **行版本并发控制（MVCC）**

---

## 六、存储与性能优化

### 表空间与数据文件

* 不同业务表建议使用独立 **表空间（Tablespace）**
* 数据文件分离日志与业务：

* 数据文件路径：`/data/gaussdb/data`
  * 日志路径：`/data/gaussdb/xlog`
  * 备份路径：`/data/gaussdb/backup`

### 常用参数优化

| 参数名                          | 含义       | 建议值      |
| ---------------------------- | -------- | -------- |
| `max_connections`            | 最大连接数    | 500–2000 |
| `work_mem`                   | 单查询可用内存  | 8MB–32MB |
| `maintenance_work_mem`       | 索引维护内存   | 256MB    |
| `shared_buffers`             | 共享内存池大小  | 总内存的25%  |
| `effective_cache_size`       | 操作系统缓存估算 | 总内存的75%  |
| `log_min_duration_statement` | 记录慢SQL阈值 | 500ms    |

配置示例：

```ini
max_connections = 1000
work_mem = 16MB
shared_buffers = 4GB
effective_cache_size = 12GB
log_min_duration_statement = 500
```

---

## 七、批量操作与数据导入

### 插入优化

* 使用 `COPY` 命令替代 `INSERT` 批量导入
* 建议关闭索引后再导入，再重建索引
* 大表分区并行导入（按时间分区）

示例：

```sql
COPY sales_data FROM '/data/sales.csv' WITH DELIMITER ',' CSV;
```

### 批量删除优化

* 避免大表 `DELETE` 全量操作
* 使用 `TRUNCATE` 或分批删除
* 分批删除示例：

```sql
DELETE FROM big_table WHERE id < 100000 LIMIT 5000;
```

---

## 八、统计信息与执行计划

### 自动收集统计信息

* 建议开启 `auto_analyze`
* 定期执行：

```sql
  ANALYZE sales_data;
  ```

### 执行计划查看

```sql
EXPLAIN ANALYZE SELECT * FROM sales_data WHERE region='BJ';
```

### 常见执行计划类型

| 操作类型              | 说明     |
| ----------------- | ------ |
| Seq Scan          | 全表扫描   |
| Index Scan        | 索引扫描   |
| Bitmap Index Scan | 位图扫描   |
| Hash Join         | 使用哈希连接 |
| Merge Join        | 有序连接   |
| Nested Loop       | 嵌套循环   |

---

## 九、数据安全与权限控制

### 用户与角色

```sql
CREATE ROLE app_user LOGIN PASSWORD 'Pwd@123';
GRANT CONNECT ON DATABASE gaussdb TO app_user;
GRANT SELECT, INSERT ON TABLE sales_data TO app_user;
```

### 安全设计建议

* 禁止使用 `superuser` 执行业务SQL
* 启用 SSL/TLS 通信
* 定期更换口令并使用复杂策略
* 敏感表数据建议使用列级加密或脱敏

---

## 十、开发规范与最佳实践

1. 所有 DDL/DML 语句必须审核
2. 业务表需预留扩展字段（如 `reserved1`, `reserved2`）
3. 所有表必须定义主键
4. 禁止在应用层直接拼接SQL
5. 执行前可使用：

```sql
   EXPLAIN SELECT ...
   ```
6. 批处理任务建议开启日志记录，便于排查性能瓶颈

---

# GaussDB 开发易错与高频考点速记表

| 分类   | 高频考点                      | 易错点提示                            |
| ---- | ------------------------- | -------------------------------- |
| 表设计  | 分区策略                      | 忘记指定分区键导致性能下降                    |
| 索引   | 高选择性字段                    | 误在低选择性列上建索引                      |
| SQL  | 类型转换                      | 隐式类型转换导致索引失效                     |
| SQL  | 子查询                       | `NOT IN` 导致全表扫描，应改为 `NOT EXISTS` |
| 事务   | 长事务                       | 持锁时间长造成死锁                        |
| 参数   | work_mem / shared_buffers | 未调整导致查询溢出磁盘                      |
| 批量导入 | COPY                      | 未关闭索引导入慢                         |
| 统计信息 | ANALYZE                   | 不更新统计信息导致优化器误判                   |
| 权限   | Superuser                 | 业务用户不应使用超级权限                     |
| 安全   | 密码策略                      | 口令简单或长期未更新                       |

---

## 记忆口诀

* **索引建表口诀**
  主外键先建，更新列慎建，选择性优先，命名需规范。

* **分区设计口诀**
  时空分表，热冷分区，千行分片，性能成倍。

* **SQL优化口诀**
  不星号，不隐转，小表驱，大表JOIN。

* **参数调优口诀**
  连接看 `max_connections`，
  内存调 `work_mem`、`shared_buffers`，
  慢SQL靠 `log_min_duration_statement`。

FYF

导航1234

最近发表

友情链接