KingBaseES SQL 编写规范

SQL 编写规范是数据库开发和运维的重要组成部分，良好的 SQL 编写规范可以提高查询性能、减少错误、增强可读性和可维护性。本文将详细介绍 KingBaseES SQL 编写的规范和最佳实践。

基本规范

命名规范

• 表名：使用小写字母，单词之间用下划线分隔，如 user_info
• 列名：使用小写字母，单词之间用下划线分隔，如 user_id
• 索引名：使用 idx_表名_列名 格式，如 idx_user_info_user_id
• 视图名：使用 v_表名 格式，如 v_user_info
• 存储过程名：使用 sp_功能描述 格式，如 sp_get_user_info
• 函数名：使用 fn_功能描述 格式，如 fn_calculate_age

格式规范

• 缩进：使用 4 个空格进行缩进
• 换行：关键字（如 SELECT、FROM、WHERE 等）单独占一行
• 逗号：逗号放在行末，便于添加和删除列
• 空格：关键字和表名/列名之间使用空格分隔
• 大小写：关键字使用大写，表名、列名使用小写
• 长度限制：SQL 语句长度不宜过长，建议不超过 5000 字符

注释规范

• 单行注释：使用 -- 进行单行注释
• 多行注释：使用 /* */ 进行多行注释
• 注释内容：注释应清晰、简洁，说明 SQL 的功能和目的
• 复杂 SQL：复杂 SQL 语句应添加详细注释

查询规范

SELECT 语句规范

• **避免 SELECT ***：只查询需要的列，避免查询不必要的列
• 使用列别名：为复杂表达式或函数结果添加别名
• 排序：如果需要排序，使用 ORDER BY 子句，并考虑添加索引
• 分页：使用 LIMIT 子句进行分页，避免返回过多数据
• 去重：如果需要去重，使用 DISTINCT 关键字，但要注意性能影响

-- 推荐
SELECT user_id, user_name, email
FROM user_info
WHERE status = 1
ORDER BY create_time DESC
LIMIT 10 OFFSET 0;

-- 不推荐
SELECT *
FROM user_info
WHERE status = 1
ORDER BY create_time DESC;

WHERE 子句规范

• 避免使用函数：避免在 WHERE 子句中对列使用函数，如 WHERE DATE(create_time) = '2023-01-01'
• 使用索引列：WHERE 子句中的条件应尽量使用索引列
• 避免使用 OR：尽量使用 IN 替代 OR，如 WHERE status IN (1, 2) 替代 WHERE status = 1 OR status = 2
• 避免使用 NOT：尽量避免使用 NOT，如 WHERE status != 0
• 使用参数化查询：避免使用字符串拼接，使用参数化查询防止 SQL 注入

-- 推荐
SELECT *
FROM user_info
WHERE create_time >= '2023-01-01' AND create_time < '2023-02-01'
  AND status IN (1, 2)
  AND user_name LIKE '张%';

-- 不推荐
SELECT *
FROM user_info
WHERE DATE(create_time) = '2023-01-01'
  AND (status = 1 OR status = 2)
  AND NOT status = 0
  AND user_name LIKE '%张%';

JOIN 语句规范

• 使用显式 JOIN：使用 INNER JOIN、LEFT JOIN 等显式 JOIN 语法，避免隐式 JOIN
• JOIN 顺序：将数据量小的表放在前面
• JOIN 条件：JOIN 条件应使用主键或索引列
• 避免过多 JOIN：尽量减少 JOIN 的表数量，建议不超过 5 个

-- 推荐
SELECT u.user_id, u.user_name, o.order_id, o.order_amount
FROM user_info u
INNER JOIN order_info o ON u.user_id = o.user_id
WHERE u.status = 1;

-- 不推荐
SELECT u.user_id, u.user_name, o.order_id, o.order_amount
FROM user_info u, order_info o
WHERE u.user_id = o.user_id AND u.status = 1;

GROUP BY 和 HAVING 规范

• GROUP BY 列：GROUP BY 子句中的列应包含 SELECT 子句中所有非聚合列
• HAVING 条件：HAVING 条件用于过滤聚合结果，WHERE 条件用于过滤行
• 聚合函数：使用合适的聚合函数，如 COUNT、SUM、AVG 等
• 避免使用 HAVING：尽量使用 WHERE 替代 HAVING，减少聚合的数据量

-- 推荐
SELECT user_id, COUNT(*) AS order_count
FROM order_info
WHERE create_time >= '2023-01-01'
GROUP BY user_id
HAVING COUNT(*) > 10;

-- 不推荐
SELECT user_id, COUNT(*) AS order_count
FROM order_info
GROUP BY user_id
HAVING COUNT(*) > 10 AND create_time >= '2023-01-01';

数据操作规范

INSERT 语句规范

• 指定列名：INSERT 语句应明确指定列名，避免使用 INSERT INTO table_name VALUES (...) 语法
• 批量插入：使用批量插入替代单条插入，减少网络开销
• 默认值：使用默认值替代 NULL 值，如 DEFAULT
• 事务处理：批量插入应使用事务，确保数据一致性

-- 推荐
INSERT INTO user_info (user_name, email, status, create_time)
VALUES ('张三', 'zhangsan@example.com', 1, NOW()),
       ('李四', 'lisi@example.com', 1, NOW()),
       ('王五', 'wangwu@example.com', 1, NOW());

-- 不推荐
INSERT INTO user_info VALUES (NULL, '张三', 'zhangsan@example.com', 1, NOW());

UPDATE 语句规范

• 指定 WHERE 条件：UPDATE 语句必须指定 WHERE 条件，避免全表更新
• 使用索引列：WHERE 条件应使用主键或索引列
• 限制更新行数：使用 LIMIT 子句限制更新行数
• 批量更新：对于大量数据更新，使用批量更新，避免长事务

-- 推荐
UPDATE user_info
SET status = 0, update_time = NOW()
WHERE user_id = 1;

-- 不推荐
UPDATE user_info SET status = 0;

DELETE 语句规范

• 指定 WHERE 条件：DELETE 语句必须指定 WHERE 条件，避免全表删除
• 使用索引列：WHERE 条件应使用主键或索引列
• 限制删除行数：使用 LIMIT 子句限制删除行数
• 使用 TRUNCATE：如果需要删除表中所有数据，使用 TRUNCATE 替代 DELETE
• 批量删除：对于大量数据删除，使用批量删除，避免长事务

-- 推荐
DELETE FROM user_info
WHERE user_id = 1;

-- 删除所有数据，推荐使用 TRUNCATE
TRUNCATE TABLE user_info;

-- 不推荐
DELETE FROM user_info;

事务处理规范

事务控制

• 显式事务：使用 BEGIN、COMMIT、ROLLBACK 显式控制事务
• 事务长度：事务应尽量短，避免长事务
• 事务隔离级别：根据业务需求选择合适的事务隔离级别
• 错误处理：在事务中添加错误处理，确保事务正确回滚

-- 推荐
BEGIN;

UPDATE user_info
SET balance = balance - 100
WHERE user_id = 1;

UPDATE user_info
SET balance = balance + 100
WHERE user_id = 2;

COMMIT;
-- 或 ROLLBACK;

事务隔离级别

• READ COMMITTED：默认隔离级别，适合大多数场景
• REPEATABLE READ：适合需要重复读取同一数据的场景
• SERIALIZABLE：最高隔离级别，适合对数据一致性要求极高的场景
• READ UNCOMMITTED：最低隔离级别，不推荐使用

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

索引使用规范

索引创建

• 根据查询创建：只为频繁使用的查询创建索引
• 选择性原则：只为选择性高的列创建索引
• 最左前缀原则：复合索引应遵循最左前缀原则
• 覆盖索引：尽量使用覆盖索引，避免回表操作

索引使用

• 避免索引失效：避免在索引列上使用函数、表达式等
• 使用索引列：查询条件应使用索引列
• 避免全表扫描：尽量避免全表扫描，使用索引扫描
• 分析执行计划：使用 EXPLAIN 分析索引使用情况

性能优化规范

查询优化

• 避免全表扫描：使用索引扫描替代全表扫描
• 优化 JOIN 操作：减少 JOIN 的表数量，使用合适的 JOIN 类型
• 优化排序操作：使用索引避免排序
• 优化子查询：尽量使用 JOIN 替代子查询
• 使用 EXISTS 替代 IN：对于大表，使用 EXISTS 替代 IN

生产环境慢查询监控脚本：

#!/bin/bash
# KingBaseES 慢查询监控脚本

LOG_DIR="/opt/Kingbase/ES/V8/data/sys_log"
OUTPUT_FILE="/tmp/slow_query_analysis_$(date +%Y%m%d).txt"

# 统计慢查询数量
slow_query_count=$(grep -r "duration:" $LOG_DIR --include="*.log" | wc -l)
echo "慢查询总数: $slow_query_count" > $OUTPUT_FILE

# 统计执行时间最长的前10个慢查询
echo -e "\n执行时间最长的前10个慢查询:" >> $OUTPUT_FILE
grep -r "duration:" $LOG_DIR --include="*.log" | sort -nr | head -10 >> $OUTPUT_FILE

# 统计出现频率最高的前10个慢查询
echo -e "\n出现频率最高的前10个慢查询:" >> $OUTPUT_FILE
grep -r "duration:" $LOG_DIR --include="*.log" | awk -F"statement:" '{print $2}' | sort | uniq -c | sort -nr | head -10 >> $OUTPUT_FILE

echo "慢查询分析报告已生成: $OUTPUT_FILE"

内存优化

• 限制结果集：使用 LIMIT 限制返回的行数
• 避免大结果集：尽量避免返回大量数据
• 使用游标：对于大数据量查询，使用游标进行分页处理
• 优化内存参数：调整 shared_buffers、work_mem 等参数

生产环境内存参数配置示例：

-- 根据服务器内存调整参数（假设服务器内存为 64GB）
ALTER SYSTEM SET shared_buffers = '16GB';          -- 系统内存的 25%
ALTER SYSTEM SET effective_cache_size = '48GB';   -- 系统内存的 75%
ALTER SYSTEM SET work_mem = '64MB';               -- 每个工作进程的内存
ALTER SYSTEM SET maintenance_work_mem = '2GB';     -- 维护操作的内存
ALTER SYSTEM SET temp_buffers = '256MB';          -- 临时表的内存

I/O 优化

• 减少磁盘 I/O：使用索引减少磁盘 I/O
• 优化表结构：使用合适的数据类型，减少数据占用空间
• 使用 SSD 存储：使用 SSD 存储提高 I/O 性能
• 合理配置表空间：将不同类型的数据放在不同的表空间

生产环境 I/O 优化脚本：

-- 查看表的 I/O 使用情况
SELECT 
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    seq_scan AS sequential_scans,
    seq_tup_read AS sequential_tuples_read,
    idx_scan AS index_scans,
    idx_tup_fetch AS index_tuples_fetched
FROM 
    sys_stat_user_tables
ORDER BY 
    sequential_tuples_read DESC
LIMIT 10;

-- 查看索引的 I/O 使用情况
SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS index_tuples_read,
    idx_tup_fetch AS index_tuples_fetched
FROM 
    sys_stat_user_indexes
ORDER BY 
    index_scans DESC
LIMIT 10;

安全规范

防止 SQL 注入

• 使用参数化查询：避免使用字符串拼接，使用参数化查询
• 验证输入：对用户输入进行验证和过滤
• 使用预处理语句：使用预处理语句执行 SQL
• 最小权限原则：为数据库用户分配最小权限

数据安全

• 加密敏感数据：对敏感数据进行加密存储
• 限制访问权限：限制数据库用户的访问权限
• 审计日志：启用审计日志，记录数据库操作
• 定期备份：定期备份数据库，确保数据安全

最佳实践

开发阶段

• 分析执行计划：使用 EXPLAIN 分析查询执行计划
• 测试性能：在开发阶段测试 SQL 性能
• 代码审查：进行 SQL 代码审查，确保符合规范
• 文档化：为复杂 SQL 添加文档说明

SQL 代码审查检查列表：

- [ ] 是否避免使用 SELECT *
- [ ] WHERE 子句是否使用了索引列
- [ ] 是否避免了在索引列上使用函数
- [ ] JOIN 表数量是否超过 5 个
- [ ] 是否使用了参数化查询防止 SQL 注入
- [ ] UPDATE/DELETE 语句是否有 WHERE 条件
- [ ] 是否使用了合适的索引
- [ ] 查询是否有 LIMIT 限制
- [ ] 是否使用了显式 JOIN 语法
- [ ] 复杂查询是否有注释说明

运维阶段

• 监控性能：监控 SQL 查询性能
• 分析慢查询：定期分析慢查询日志
• 优化索引：定期优化索引，删除不使用的索引
• 更新统计信息：定期更新表的统计信息

生产环境索引优化脚本：

-- 查找未使用的索引
SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scan_count,
    pg_size_pretty(pg_index_size(indexrelid)) AS index_size
FROM 
    sys_stat_user_indexes
WHERE 
    idx_scan = 0
ORDER BY 
    index_size DESC;

-- 查找索引碎片率超过 30% 的索引
SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_index_size(indexrelid)) AS index_size,
    round(100.0 * (pg_index_size(indexrelid) - pg_relation_size(indexrelid)) / pg_index_size(indexrelid), 2) AS frag_percent
FROM 
    sys_stat_user_indexes
    JOIN sys_indexes ON indexrelid = oid
    JOIN sys_class ON relid = indrelid
WHERE 
    pg_index_size(indexrelid) > 0
    AND (pg_index_size(indexrelid) - pg_relation_size(indexrelid)) / pg_index_size(indexrelid) > 0.3
ORDER BY 
    frag_percent DESC;

生产环境统计信息更新脚本：

#!/bin/bash
# KingBaseES 统计信息更新脚本

KB_BIN="/opt/Kingbase/Server/bin"
LOG_FILE="/opt/Kingbase/logs/update_stats_$(date +%Y%m%d).log"

# 日志函数
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> $LOG_FILE
}

# 确保日志目录存在
mkdir -p $(dirname $LOG_FILE)

log "开始更新统计信息"

# 更新所有数据库的统计信息
$KB_BIN/ksql -U sysdba -d template1 -c "DO \$DO\$ 
BEGIN 
  FOR db IN SELECT datname FROM sys_database WHERE datname NOT IN ('template0', 'template1') LOOP 
    EXECUTE '\c ' || quote_ident(db); 
    EXECUTE 'ANALYZE VERBOSE'; 
  END LOOP; 
END \$DO\$;"

log "统计信息更新完成"
echo "统计信息更新完成，日志文件：$LOG_FILE"

常见场景最佳实践

分页查询

-- 推荐
SELECT user_id, user_name, email
FROM user_info
WHERE status = 1
ORDER BY user_id
LIMIT 10 OFFSET 20;

-- 不推荐（大数据量时性能差）
SELECT user_id, user_name, email
FROM (
    SELECT user_id, user_name, email, ROW_NUMBER() OVER (ORDER BY user_id) AS rn
    FROM user_info
    WHERE status = 1
) t
WHERE rn BETWEEN 21 AND 30;

批量操作

-- 批量插入
INSERT INTO user_info (user_name, email, status, create_time)
VALUES ('张三', 'zhangsan@example.com', 1, NOW()),
       ('李四', 'lisi@example.com', 1, NOW()),
       ('王五', 'wangwu@example.com', 1, NOW());

-- 批量更新（使用 CASE 语句）
UPDATE user_info
SET status = CASE user_id
               WHEN 1 THEN 0
               WHEN 2 THEN 1
               WHEN 3 THEN 2
           END
WHERE user_id IN (1, 2, 3);

数据迁移

-- 使用 CREATE TABLE AS 进行数据迁移
CREATE TABLE new_table AS
SELECT user_id, user_name, email, status, create_time
FROM old_table
WHERE status = 1;

-- 使用 INSERT INTO SELECT 进行数据迁移
INSERT INTO new_table (user_id, user_name, email, status, create_time)
SELECT user_id, user_name, email, status, create_time
FROM old_table
WHERE status = 1;

版本差异 (V8 R6 vs V8 R7)

特性	V8 R6	V8 R7
SQL 语法支持	基本 SQL 语法和功能	支持更多高级 SQL 特性，如并行查询、JSON 支持、WITH 子句递归查询等
优化器	优化器选择逻辑相对简单	增强的自适应优化器，选择逻辑更加智能，支持执行计划缓存和管理
索引类型	基本索引类型（B-tree、Hash、GiST、GIN）	支持更多索引类型，如 SP-GiST、BRIN、表达式索引、部分索引等
性能优化选项	相对较少	更加丰富，支持自动统计信息收集、执行计划锁定、资源组管理等
数据类型	基本数据类型	支持更多数据类型，如 JSONB、UUID、数组类型增强等
函数支持	基本函数支持	支持更多内置函数，包括 JSON 函数、窗口函数增强、聚合函数增强等
并行查询	不支持	支持并行查询，可加速大型查询的执行
执行计划管理	基本支持	支持执行计划缓存、执行计划锁定、执行计划诊断等
统计信息收集	手动触发	支持自动统计信息收集和更新，可配置收集策略
慢查询日志	基本支持	增强的慢查询日志，包含更多上下文信息和执行计划
资源管理	基本支持	支持资源组管理，可按用户、按语句类型分配资源
监控视图	基本的系统视图	增强的监控视图，包含更多性能指标和统计信息
备份恢复	基本支持	增强的备份恢复功能，支持增量备份、并行备份等
高可用	基本的流复制	增强的高可用功能，支持更灵活的复制配置和故障切换

常见问题 (FAQ)

Q1: 如何优化慢查询？

A: 优化慢查询的方法：

• 分析执行计划，找出性能瓶颈
• 添加合适的索引
• 优化查询语句，避免全表扫描
• 减少 JOIN 的表数量
• 优化排序和分组操作
• 使用 EXISTS 替代 IN（对于大表）
• 优化子查询，尽量使用 JOIN 替代

Q2: 如何防止 SQL 注入？

A: 防止 SQL 注入的方法：

• 使用参数化查询
• 对用户输入进行验证和过滤
• 使用预处理语句
• 分配最小权限
• 避免使用字符串拼接构建 SQL 语句
• 启用 SQL 注入检测工具

Q3: 何时使用索引？

A: 使用索引的场景：

• 查询条件中频繁使用的列
• 选择性高的列（区分度高）
• JOIN 条件中的列
• 排序和分组的列
• 作为外键的列
• 用于覆盖查询的列

Q4: 如何选择事务隔离级别？

A: 选择事务隔离级别的原则：

• READ COMMITTED：默认隔离级别，适合大多数场景，提供较好的并发性能
• REPEATABLE READ：适合需要重复读取同一数据的场景，避免不可重复读
• SERIALIZABLE：最高隔离级别，适合对数据一致性要求极高的场景，避免幻读
• READ UNCOMMITTED：最低隔离级别，不推荐使用，可能读取到未提交的数据

Q5: 如何优化批量操作？

A: 优化批量操作的方法：

• 使用批量插入替代单条插入
• 使用事务处理批量操作
• 限制批量操作的大小（建议每批次 1000-5000 行）
• 避免长事务
• 使用 COPY 命令导入大量数据
• 考虑在非高峰期执行批量操作

Q6: 如何处理由统计信息过时导致的性能问题？

A: 处理统计信息过时的方法：

• 手动更新统计信息：ANALYZE VERBOSE table_name;
• 为频繁更新的表配置自动统计信息收集
• 使用 ANALYZE 更新所有表的统计信息
• 对于重要表，可以增加统计信息收集的频率
• 使用 ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 1000; 提高统计信息精度

Q7: 如何优化 ORDER BY 语句？

A: 优化 ORDER BY 语句的方法：

• 为 ORDER BY 列创建索引，避免排序操作
• 确保 ORDER BY 列顺序与索引顺序一致
• 避免在 ORDER BY 列上使用函数
• 考虑使用覆盖索引，避免回表操作
• 对于大数据量排序，可以增加 work_mem 参数

Q8: 如何处理锁等待问题？

A: 处理锁等待问题的方法：

• 查看锁等待情况：SELECT * FROM sys_locks WHERE NOT granted;
• 终止阻塞进程：SELECT sys_terminate_backend(pid);
• 优化事务，减少锁持有时间
• 使用合适的事务隔离级别
• 避免长事务
• 考虑使用乐观锁或无锁设计

Q9: 如何优化 JOIN 查询？

A: 优化 JOIN 查询的方法：

• 限制 JOIN 的表数量（建议不超过 5 个）
• 为 JOIN 条件创建索引
• 将数据量小的表放在前面
• 使用合适的 JOIN 类型（INNER JOIN、LEFT JOIN 等）
• 避免在 JOIN 条件中使用函数
• 考虑使用子查询或 CTE 简化复杂 JOIN

Q10: 如何选择合适的索引类型？

A: 选择索引类型的原则：

• B-tree：适合大多数场景，包括等值查询、范围查询、排序等
• Hash：适合等值查询，不适合范围查询和排序
• GiST：适合地理数据、全文搜索等复杂数据类型
• GIN：适合数组、JSON 等多值数据类型
• SP-GiST：适合非平衡数据结构，如四叉树、k-d 树等
• BRIN：适合大型表的范围查询，如时间序列数据

Q11: 如何监控和分析慢查询？

A: 监控和分析慢查询的方法：

• 配置慢查询日志：ALTER SYSTEM SET log_min_duration_statement = 1000;
• 使用慢查询分析工具，如 ELK Stack、Graylog 等
• 定期分析慢查询日志，识别高频慢查询
• 使用 EXPLAIN ANALYZE 分析慢查询执行计划
• 使用系统视图监控当前慢查询：SELECT * FROM sys_stat_activity WHERE state = 'active' AND now() - query_start > interval '1 second';
• 设置慢查询告警，及时发现性能问题

总结

SQL 编写规范是数据库开发和运维的重要组成部分，良好的 SQL 编写规范可以提高查询性能、减少错误、增强可读性和可维护性。在编写 SQL 语句时，应遵循基本规范、查询规范、数据操作规范、事务处理规范、索引使用规范和性能优化规范，不断优化和改进 SQL 语句，提高数据库的性能和可靠性。

同时，还应关注数据库版本的差异，了解不同版本的特性和优化选项，根据实际业务需求选择合适的 SQL 语法和优化策略。定期分析慢查询日志，优化查询性能，确保数据库的高效运行。