GaussDB数据库开发设计建议

1.1  总结

本文档围绕 GaussDB 数据库开发设计展开，核心涵盖数据库对象命名和设计建议、表设计最佳实践、SQL 查询最佳实践三大模块：命名层面建议避免关键字、统一风格（如表名用下划线分隔，临时表前缀tmp_），Schema&Database 设计推荐用 Schema 做业务隔离、创建 Database 时选 UTF-8 编码及关注 4 种兼容模式；表设计需合理选择Hash/Replication/Range/List 分布策略（如大表用 Hash、小表用 Replication）、优化分布列（避免数据倾斜，差异超 10% 需调整）及使用分区表；SQL 查询则需优化 WHERE 子句（避免隐式类型转换、不用函数操作索引列）、SELECT/INSERT/UPDATE/DELETE 等子句（如批量 INSERT 用多值语法，DELETE 避免全表扫描），同时控制事务和子查询复杂度，确保开发设计高效、合规。

1.2   思维导图（mindmap）

1.3   详细总结

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

1. 数据库对象命名建议

2. Schema&Database 设计建议

（1）Database 设计

（2）Schema 设计

             权限控制：非 sysadmin / 非 owner 用户访问 Schema 下对象，需同时拥有 Schema 的usage权限和对象的对应操作权限；创建对象需create权限。

             权限风险：Schema 的 owner 默认拥有该 Schema 下所有对象的删除权限，需严格管控。

3. 表、字段、约束及视图设计建议

（1）表设计核心关注

             数据均匀分布：通过合理选择分布列，避免数据集中在部分 DN（存储倾斜导致集群容量下降）。

             扫描压力分散：避免等值过滤集中扫描部分 DN，产生性能瓶颈。

             减少扫描量：用分区表剪枝机制，大幅降低数据扫描范围。

             减少随机 I/O：通过聚簇 / 局部聚簇（PCK）将热数据连续存储，转为连续 I/O。

             避免数据 shuffle：优化关联 / 分组条件，减少节点间数据传输（节省网络资源，提升性能）。

（2）字段设计建议

             高效类型优先：满足业务精度下，选择优先级为整数 > 浮点数 > NUMERIC（如 numeric 列改 int 后，点查询时间从 10+s 降至 1.8s）。

             关联字段统一：多表中同一含义的字段，需用相同数据类型（避免隐式转换消耗性能）。

             字符串类型：优先用变长类型（如 VARCHAR）并指定最大长度（需大于实际最大字符数，避免截断）；禁选 CHAR (n) 等固定长度类型（除非明确数据长度固定）。

（3）约束设计建议

（4）视图和关联表设计

             视图：避免嵌套（除非强依赖）、少用排序操作。

             关联表：关联字段少、类型一致、命名体现关联关系（如同名）。

二、表设计最佳实践

1. 分布策略选择

2. 分布列选择（Hash 表核心）

             选择原则：1. 列值离散（如主键，确保数据均匀）；2. 优先选查询中的连接条件（减少 DN 间通信）。

             倾斜检查：执行select xc_node_id, count(1) from tablename group by xc_node_id order by xc_node_id desc;，不同 DN 数据量差超 5% 视为倾斜，超 10% 必须调整分布列。

             特性支持：GaussDB Kernel 支持多分布列，提升数据分布均匀性。

3. 分区表使用

             定义：逻辑表分物理块存储，数据存于分区，逻辑表不存数据。

             支持类型：仅范围分区表（按分区键范围映射，如按月份分销售数据）。

             优点：1. 提升查询性能（仅扫目标分区）；2. 增强可用性（单个分区故障不影响其他）；3. 简化维护（仅修复故障分区）。

             缺点：索引扫描代价较高。

             建议：业务表优先按时间或地区做 Range 分区。

4. 数据类型选择（高效原则）

5. 分布式事务设计

             核心原则：分布式数据库≠业务必走分布式，业务改动量小时，优先设计单机事务（提升性能、并发，降低复杂度）。

             示例：user_account（用户 ID、余额）和log_account（用户 ID、操作日志）均以用户 ID 为分布列，产生单机事务；禁user_account按用户 ID、log_account按 action 分布（跨节点事务）。

三、SQL 查询最佳实践

1. WHERE 子句最佳实践

2. SELECT 子句最佳实践

             禁通配符*：仅查询需用字段，减少数据传输。

             少用count()查大表行数：避免全表扫描。

             不用子查询在目标列：可能导致计划无法下推，影响性能。

             统计表行数用count(*)：不用count(col)（col 含 NULL 时结果不准确）。

             避免大字段排序：如ORDER BY/GROUP BY操作大字段（如 TEXT），消耗资源。

3. INSERT 子句最佳实践

             批量插入用多值语法：INSERT INTO TABLE1 VALUES (),(),()效率高于多条INSERT INTO VALUES()。

               INSERT ON DUPLICATE KEY UPDATE限制：1. 不更新主键 / 唯一约束列；2. 批量插入数据间无主键 / 唯一约束冲突；3. 禁用于多唯一约束表。

4. UPDATE 子句最佳实践

             必须有 WHERE 子句：避免全表更新。

             结合主键 / 索引：WHERE 条件用主键或索引列，提升效率。

             禁多表更新：不支持多表关联更新。

             禁 LIMIT/ORDER BY/GROUP BY：避免不必要排序或目标行不明确。

             禁更新列作为更新源：如update t set a = a + 1, b = a（b 用更新后 a 的值，逻辑风险）。

5. DELETE 子句最佳实践

             必须有 WHERE 子句：避免全表删除。

             用 TRUNCATE 清表：比 DELETE 高效（TRUNCATE 直接删物理文件，DELETE 仅标记数据，需 VACCUUM FULL 清理）。

             禁 LIMIT / 多表删除：不支持 LIMIT，且不能单条 SQL 删多表。

             禁 ORDER BY/GROUP BY：避免不必要排序。

             GTM-FREE 模式限制：删 Hash 表需指定分布列等值条件（禁跨节点事务）。

6. 关联查询、子查询与事务最佳实践

（1）关联查询

             嵌套深度 < 8 层：避免复杂度过高。

             明确定义连接方式：用INNER JOIN/LEFT JOIN等，不用默认 JOIN（避免笛卡尔积）。

             表加别名：多表关联时用别名，逻辑清晰。

             关联列处理：1. 同数据类型；2. 含大量 NULL 时加IS NOT NULL过滤。

             少用嵌套子查询：优先表关联（子查询产生临时表，影响性能）。

（2）子查询

             禁重复子查询：同一条 SQL 中不出现相同子查询（重复计算，浪费资源）。

             少用标量子查询：如where id = (select id from t2 LIMIT 1)，建议拆分为两条 SQL。

             嵌套深度≤2 层：减少临时表开销，复杂查询从业务逻辑优化。

             禁子查询在 SELECT 目标列：影响计划下推，降低性能。

（3）事务

             GTM-FREE 模式限制：禁跨节点事务（如多 DN 操作），否则报错。

             大对象操作无事务：如CREATE/DROP DATABASE、ANALYZE、VACUUM不支持事务。

             JDBC 执行规范：不拼接多条 SQL 为一条执行（中间操作失败可能重新开事务，逻辑混乱）。

1.4   关键问题

问题 1：GaussDB 中 Hash 分布表的分布列该如何选择？若数据倾斜需如何判断和处理？

答案：

             分布列选择原则：1. 优先选列值离散度高的字段（如主键、唯一键，确保数据均匀分布到各 DN）；2. 其次选查询中频繁作为连接条件的字段（减少 DN 间数据 shuffle，降低网络开销）。

             数据倾斜判断：执行 SQL select xc_node_id, count(1) from tablename group by xc_node_id order by xc_node_id desc;，统计各 DN（xc_node_id 对应 DN）的数据量，不同 DN 数据量差异超 5% 视为轻微倾斜，超 10% 必须调整分布列。

             处理方式：1. 更换分布列（选更离散的字段，如将 “性别” 改为 “用户 ID”）；2. 使用 GaussDB Kernel 的多分布列特性（通过多个字段的 Hash 值共同映射 DN，提升分布均匀性）。

问题 2：在 GaussDB 的 SQL 开发中，WHERE 子句存在哪些常见性能风险？如何规避这些风险？

答案：

             常见性能风险：1. 隐式数据类型转换（导致索引失效、DN 无法裁剪）；2. 对索引列使用函数 / 表达式（如abs(income) > 1000，索引失效，全表扫描）；3. LIKE 查询左置 %（如like '%张'，无法走索引）；4. 缺少分布键等值条件（计划下发到所有 DN，资源浪费）；5. IN 候选子集超 500（执行效率骤降）；6. 用!= NULL判断非空（语法错误，且无法走索引）。

             规避措施：1. 确保字段类型与条件值一致（如 VARCHAR 字段用字符串条件，phonenumber = '13512345678'）；2. 索引列条件避免函数 / 表达式，改为字段直接比较（如income > 1000 or income < -1000替代abs(income) > 1000）；3. LIKE 查询 % 右置（like '张%'）；4. 必含分布键等值条件（如user_id = 123，user_id 为分布列）；5. IN 候选超 500 时拆分 SQL 或用其他过滤逻辑；6. 非空判断用IS NOT NULL。

问题 3：GaussDB 分区表的适用场景是什么？使用时需注意哪些要点？

答案：

             适用场景：1. 数据量较大且有明确分区维度（如按时间分区的销售表、按地区分区的用户表）；2. 查询常聚焦于部分数据（如仅查 “2024 年 10 月” 的销售数据，可通过分区剪枝减少扫描量）；3. 需提升可用性和维护效率（如单个分区故障不影响其他数据，修复仅针对故障分区）。

             使用要点：1. 仅支持范围分区：需指定分区键（如日期、数值范围），不支持哈希分区等其他类型；2. 分区键选择：优先按时间或地区（业务查询高频维度），确保分区剪枝效果；3. 索引注意事项：分区表索引扫描代价较高，查询若依赖索引需评估性能；4. 数据管理：分区表是逻辑表，数据实际存于物理分区，删除 / 备份可按分区操作（如删除 “2023 年” 的历史分区），简化维护。