• [技术解读] GaussDB全密态数据库
    全密态数据库全密态数据库是指对应用能够提供透明的加解密能力,在数据库系统中将数据的全生命周期以密文形式进行处理,同时密钥掌握在授权用户手中的数据库管理系统。当数据拥有者在客户端完成数据加密并发送给服务端后,即使攻击者借助系统脆弱点窃取用户数据时仍然无法获得有效、有价值的数据信息,从而起到保护数据隐私的能力。由于整个业务数据流在数据处理过程中都是以密文形态存在,因此通过全密态数据库可以实现如下优势:数据安全:数据产生后即刻加密,保护数据在传输、处理、存储、同步和备份等过程中的全生命周期安全。管理可信:由授权用户掌握密钥,进行数据解密及验证,符合要求后,才可获得明文数据,而其他任何人员,包括操作系统、数据库的管理及运维人员都无法接触到明文数据。法律合规:随着数据安全法律法规相继发布,密评、等保、分类分级等要求出台,对数据全生命周期的保护有了更严格的要求。全密态数据库的总体架构示意图如图1 密态数据库总体架构所示,其完整形态包括纯软方案和软硬结合两种方案。纯软密态查询在数据库服务侧全程存储密文,通过密码学算法在密文空间直接查询运算,保障数据隐私不泄露。而软硬融合全密态则通过机密计算,借助可信执行环境(安全硬件隔离或逻辑隔离明文计算空间),通过访问控制,实现计算及数据对外“不可见”,防止数据泄露。两种方案都需要客户端加解密驱动的密钥管理、语法解析及加解密计算。
  • [技术解读] GaussDB默认权限机制
    数据库对象创建后,进行对象创建的用户就是该对象的所有者。集群安装后默认情况下,未开启三权分立,数据库系统管理员具有与对象所有者相同的权限。也就是说对象创建后,默认只有对象所有者或者系统管理员可以查询、修改和销毁对象,以及通过GRANT将对象的权限授予其他用户。为使其他用户能够使用对象,必须向用户或包含该用户的角色授予必要的权限。GaussDB支持以下的权限:SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE、USAGE、ALTER、DROP、COMMENT、INDEX和VACUUM。不同的权限与不同的对象类型关联。有关各权限的详细信息,请参见GRANT。要撤销已经授予的权限,请参见REVOKE。对象所有者的权限(例如ALTER、DROP、COMMENT、INDEX、VACUUM、GRANT和REVOKE)是隐式拥有的,即只要拥有对象就可以执行对象所有者的这些隐式权限。对象所有者可以撤销自己的普通权限(SELECT、INSERT、UPDATE、DELETE),例如,使表对自己以及其他人只读,系统管理员用户除外。系统表和系统视图要么只对系统管理员可见,要么对所有用户可见。标识了需要系统管理员权限的系统表和视图只有系统管理员可以查询。有关信息,请参见系统表和系统视图。数据库提供对象隔离的特性,对象隔离特性开启时,用户只能查看有权限访问的对象(表、视图、字段、函数),系统管理员不受影响。有关信息,请参见ALTER DATABASE。不建议用户修改系统表和系统视图的权限。创建和管理分区表GaussDB Kernel数据库支持的分区表为范围分区表,列表分区表,哈希分区表。范围分区表:将数据基于范围映射到每一个分区,这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期,例如将销售数据按照月份进行分区。列表分区表:将数据中包含的键值分别存储在不同的分区中,依次将数据映射到每一个分区,分区中包含的键值由创建分区表时指定。哈希分区表:将数据根据内部哈希算法依次映射到每一个分区中,包含的分区个数由创建分区表时指定。分区表和普通表相比具有以下优点:改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索效率。增强可用性:如果分区表的某个分区出现故障,表在其他分区的数据仍然可用。方便维护:如果分区表的某个分区出现故障,需要修复数据,只修复该分区即可。均衡I/O:可以把不同的分区映射到不同的磁盘以平衡I/O,改善整个系统性能。普通表若要转成分区表,需要新建分区表,然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时,请根据业务提前规划是否使用分区表创建和管理索引索引可以提高数据的访问速度,但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引,索引建立在哪些字段上,是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询的条件或者被要求排序的字段来确定是否建立索引。索引建立在数据库表中的某些列上。因此,在创建索引时,应该仔细考虑在哪些列上创建索引。在经常需要搜索查询的列上创建索引,可以加快搜索的速度。在作为主键的列上创建索引,强制该列的唯一性和组织表中数据的排列结构。在经常使用连接的列上创建索引,可以加快连接的速度。在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的。在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间。在经常使用WHERE子句的列上创建索引,加快条件的判断速度。为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。
  • GaussDB 嵌入式SQL:OPEN命令
    一、OPEN命令核心作用OPEN是嵌入式SQL中管理游标(Cursor)的核心指令,其核心价值包括:​结果集激活:执行查询语句并生成结果集​参数绑定:动态传递输入参数到预编译SQL​游标类型控制:支持只读、更新、滚动游标等模式典型语法:cEXEC SQL OPEN {cursor_name | ALL | NONE} [USING DESCRIPTOR descriptor_name]; 二、环境配置与基础用法开发环境要求组件 版本要求GaussDB C/C++驱动 8.3.0及以上编译器 GCC 9.3+ / Clang 12+ODBC驱动 unixODBC 2.3.7基础打开示例c#include <sqlca.h> EXEC SQL BEGIN DECLARE SECTION; char conn_str[] = "dbname=testdb user=admin password=SecurePass123!"; EXEC SQL END DECLARE SECTION; int main() { EXEC SQL DECLARE cur CURSOR FOR SELECT id, name FROM users WHERE status='ACTIVE'; EXEC SQL CONNECT :conn_str; // 打开游标并获取结果集 EXEC SQL OPEN cur; while(EXEC SQL FETCH NEXT FROM cur) { // 处理数据... } EXEC SQL CLOSE cur; return 0; } 三、高级应用模式动态参数绑定(C++示例)cppEXEC SQL DECLARE in_desc DESCRIPTOR WITH MAX 2; EXEC SQL DECLARE cur CURSOR FOR SELECT * FROM orders WHERE user_id=:1 AND amount>:2; void dynamic_open(int user_id, double min_amount) { EXEC SQL SET DESCRIPTOR in_desc VALUE 1 TYPE = 'INTEGER', DATA = &user_id; EXEC SQL SET DESCRIPTOR in_desc VALUE 2 TYPE = 'DECIMAL', DATA = &min_amount; EXEC SQL OPEN cur USING DESCRIPTOR in_desc; } 滚动游标操作cEXEC SQL DECLARE scroll_cur SCROLL CURSOR FOR SELECT id, value FROM sensor_data ORDER BY timestamp DESC; void process_scroll() { EXEC SQL OPEN scroll_cur; // 向前跳转3条记录 EXEC SQL FETCH PRIOR FROM scroll_cur; EXEC SQL FETCH RELATIVE 3 FROM scroll_cur; EXEC SQL CLOSE scroll_cur; } 四、最佳实践指南性能优化策略优化方向 实施方案游标复用 预打开高频游标并缓存批量处理 使用OPEN … FOR READ ONLY禁用更新锁参数化查询 启用预编译语句缓存(PREPARE模式)华为云增强功能sql-- 查看游标执行统计 SHOW STATUS LIKE 'cursor%'; -- 启用游标自动分析 SET autodiagnose = ON; CALL sys.dbms_cursor.analyze( '2023-10-01', '2023-10-02', 'CURSOR_OPEN_PATTERN' ); 五、典型问题排查游标未声明错误sqlSQLCODE=-204: Cursor 'invalid_cur' not found解决方案:c// 严格遵循声明顺序 EXEC SQL BEGIN DECLARE SECTION; ... // 变量声明 EXEC SQL DECLARE cur CURSOR FOR ...; EXEC SQL END DECLARE SECTION; 参数绑定错误sqlSQLCODE=-305: Invalid descriptor index修复策略:c// 检查描述符索引有效性 EXEC SQL SET DESCRIPTOR in_desc VALUE 1 ...; // 索引从1开始六、应用场景案例电商订单处理系统mermaidgraph TD A[用户查询订单] --> B{参数验证} B -->|有效| C[OPEN orders_cursor] B -->|无效| D[返回错误] C --> E[FETCH分页数据] E --> F[渲染页面] F --> G[CLOSE orders_cursor]工业物联网数据处理c// 实时设备监控 EXEC SQL DECLARE sensor_cursor SCROLL CURSOR FOR SELECT device_id, value FROM sensor_data WHERE timestamp > NOW() - INTERVAL 1 HOUR; void monitor_devices() { EXEC SQL OPEN sensor_cursor; while(EXEC SQL FETCH NEXT FROM sensor_cursor) { if(value > threshold) { trigger_alert(device_id); } } EXEC SQL CLOSE sensor_cursor; } 七、华为云特色功能​智能游标池管理sql-- 动态调整游标池大小 CALL sys.dbms_cursor.configure( 'CURSOR_POOL', 'MAX_SIZE=200', 'TTL=300' ); ​自动诊断报告sql-- 生成游标使用分析报告 CALL sys.dbms_diagnose.cursor_analysis( '2023-10-01 00:00:00', '2023-10-02 00:00:00' ); ​跨地域容灾支持c// 自动切换游标连接 EXEC SQL OPEN cur ON FAILOVER TO replica_db AT zone2; 八、总结与建议​关键原则遵循"打开即处理"原则,避免空游标占用资源在异常处理路径中强制关闭游标结合华为云监控工具实施游标级性能调优​性能优化组合拳预编译游标 + 参数绑定 + 池化管理 ≈ 性能提升200%​华为云实践建议启用__gaussdb_cursor_metrics诊断视图使用CloudDBA的自动优化建议结合GaussDB Star进行分布式游标管理通过合理运用OPEN命令及其关联功能,开发者可以显著提升GaussDB应用的响应速度和资源利用率。
  • [技术解读] GaussDB 实例 gsql 连接方式详解
    GaussDB 实例 gsql 连接方式详解GaussDB 是华为云推出的分布式关系型数据库服务,支持多种数据库引擎(如 MySQL、PostgreSQL、SQL Server 等)。gsql 是 GaussDB 提供的命令行客户端工具,用于连接和管理数据库实例。本文将详细介绍通过 gsql 连接 GaussDB 实例的多种方式,涵盖基础配置、安全连接及高级用法。一、环境准备在连接 GaussDB 实例前,需确保以下条件已满足:​​安装 gsql 客户端​​GaussDB 提供预装 gsql 的镜像,或通过华为云控制台下载客户端工具包。​​获取连接参数​​需准备以下信息:数据库实例的 ​​主机地址​​(公网 IP 或私有 IP)​​端口号​​(默认 3306/5432 等,依引擎类型而定)​​数据库名称​​、​​用户名​​、​​密码​​(可选)SSL 证书路径(如启用加密连接)二、基础连接方式1. 本地直接连接若客户端与数据库实例在同一服务器上,可直接使用本地回环地址(127.0.0.1)连接:gsql -h 127.0.0.1 -p 5432 -U gaussdb_user -d mydatabase-h: 数据库主机地址-p: 端口号-U: 用户名-d: 数据库名称输入密码后即可登录。2. 远程连接(通过公网 IP)若数据库实例开通了公网访问,可通过公网 IP 连接:gsql -h <公网IP> -p 5432 -U gaussdb_user -d mydatabase​​注意​​:需在华为云控制台配置安全组规则,允许公网访问对应端口。3. 使用连接字符串可将连接参数保存为配置文件(~/.pgpass)或使用 -c 参数指定连接字符串:gsql postgresql://gaussdb_user:password@host:5432/mydatabase?sslmode=require三、安全连接(SSL 加密)1. 启用 SSL 连接为保障数据传输安全,GaussDB 支持 SSL 加密。需执行以下步骤:​​下载 CA 证书​​从华为云控制台下载数据库实例的 CA 证书(如 root.crt)。​​使用 SSL 参数连接​​gsql -h <host> -p 5432 -U gaussdb_user -d mydatabase \ --sslmode=verify-full \ --sslrootcert=/path/to/root.crt --sslmode: 设置 SSL 验证级别(如 require、verify-ca、verify-full) --sslrootcert: 指定 CA 证书路径四、高级连接技巧持久化连接配置可将常用连接参数写入 ~/.pg_service.conf 文件,简化登录命令:[my_service] host=host port=5432 user=gaussdb_user dbname=mydatabase sslmode=require sslrootcert=/path/to/root.crt通过服务名连接:gsql service=my_service多连接管理使用 psql 风格的连接别名(需在 .pgpass 中配置):# 编辑 ~/.pgpass 文件 echo "host:5432:mydatabase:gaussdb_user:password" >> ~/.pgpass # 快速连接 gsql -W mydb_alias五、常见问题排查1. 连接超时或拒绝​​检查网络连通性​​:使用 telnet <host> <port> 验证端口可达性。​​确认安全组规则​​:确保云服务器的安全组放行了客户端 IP 和数据库端口。​​验证用户名/密码​​:通过控制台重置密码测试。2. SSL 证书错误若提示 SSL certificate problem,需检查:证书路径是否正确。证书是否过期。是否使用正确的 CA 证书。3. 权限不足确保用户拥有数据库的 CONNECT 和 USAGE 权限:GRANT CONNECT ON DATABASE mydatabase TO gaussdb_user;六、总结通过 gsql 连接 GaussDB 实例的方式灵活多样,可根据场景选择明文连接、SSL 加密或配置持久化参数。对于生产环境,强烈建议启用 SSL 加密以保障数据安全。掌握这些连接方法后,可高效完成数据库管理、开发和运维任务。
  • [技术解读] GaussDB ecpg常用示例代码
    GaussDB ecpg常用示例代码#include <locale.h> #include <string.h> #include <stdlib.h> exec sql whenever sqlerror sqlprint; exec sql include sqlca; int main(void) { EXEC SQL BEGIN DECLARE SECTION; char *temp_str = (char *)malloc(11); EXEC SQL END DECLARE SECTION; ECPGdebug(1, stderr); /* 提前创建testdb库 */ exec sql connect to testdb; /* 打开自动提交,以下执行exec sql时不用手动commit */ exec sql set autocommit = on; exec sql drop table if exists test_t; /* 建表,插入数据 */ exec sql create table test_t(f float, i int, a int[10], mstr char(10)); exec sql insert into test_t(f, i, a, mstr) values(1.01,1,'{0,1,2,3,4,5,6,7,8,9}', 'China'); /* 关闭自动提交,以下插入数据的sql语句需要手动commit才能提交 */ exec sql set autocommit = off; exec sql insert into test_t(f, i, a, mstr) values(2.01,2,'{0,1,2,3,4,5,6,7,8,9}', 'USA'); exec sql commit; exec sql insert into test_t(f, i, a, mstr) values(3.01,3,'{0,1,2,3,4,5,6,7,8,9}', 'AUS'); exec sql insert into test_t(f, i, a, mstr) values(4.01,4,'{0,1,2,3,4,5,6,7,8,9}', 'JAP'); exec sql commit; EXEC SQL BEGIN DECLARE SECTION; int a[10] = {9,8,7,6,5,4,3,2,1,0}; int id = 6; EXEC SQL END DECLARE SECTION; /* 从宿主变量取数据插入到表中,宿主变量的类型与表定义的类型一致 */ strcpy(temp_str, "RUS"); exec sql insert into test_t(f, i, a, mstr) values(5.01,5,:a,:temp_str); exec sql commit; exec sql set autocommit = on; exec sql begin; exec sql insert into test_t(f, i, a, mstr) values(6.01,:id,:a,'SIG'); exec sql commit; exec sql set autocommit = off; exec sql begin declare section; float ff; char tmp_text[25] = "klmnopqrst"; exec sql end declare section; exec sql set autocommit = on; exec sql begin work; printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text); /* 条件查询语句示例 */ exec sql select f, mstr into :ff,:tmp_text from test_t where f > (select f from test_t where i = 4 or i < 0) order by a limit 1; printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text); exec sql select f, mstr into :ff,:tmp_text from test_t where mstr = 'JAP' order by i; printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text); exec sql select f, mstr into :ff,:tmp_text from test_t order by i DESC limit 1; printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text); exec sql select f, mstr into :ff,:tmp_text from test_t order by mstr limit 1; printf("Found ff=%f tmp_text=%10.10s\n", ff, tmp_text); exec sql select count(f), a into :ff,:tmp_text from test_t where i > 2 group by a limit 1; printf("Found ff=%f tmp_text=%20.30s\n", ff, tmp_text); exec sql select count(f), a into :ff,:tmp_text from test_t where i > 3 group by a order by a limit 1; printf("Found ff=%f tmp_text=%20.30s\n", ff, tmp_text); exec sql select sum(f), a into :ff,:tmp_text from test_t where i > 2 group by a order by a limit 1; printf("Found ff=%f tmp_text=%20.30s\n", ff, tmp_text); exec sql select distinct a into :tmp_text from test_t order by a limit 1; exec sql drop table test_t; exec sql commit; /* 释放连接,释放为宿主变量分配的内存 */ exec sql disconnect; free(temp_str); return 0; } GaussDB pgtypes库函数示例代码示例一:使用库函数对时间和日期类型进行不同操作。具体使用方式请参见使用库函数章节。#include <stdio.h> #include <string.h> #include <stdlib.h> #include <limits.h> #include <pgtypes_date.h> #include <pgtypes_timestamp.h> char *dates[] = { "19990108foobar", "19990108 foobar", "1999-01-08 foobar", "January 8, 1999", "1999-01-08", "1/8/1999", "1/18/1999", "01/02/03", "1999-Jan-08", "Jan-08-1999", "08-Jan-1999", "99-Jan-08", "08-Jan-99", "08-Jan-06", "Jan-08-99", "19990108", "990108", "1999.008", "J2451187", "January 8, 99 BC", NULL }; /* 不可与libc的“times”冲突 */ static char *times[] = { "0:04", "1:59 PDT", "13:24:40 -8:00", "13:24:40.495+3", NULL }; char *intervals[] = { "1 minute", "1 12:59:10", "2 day 12 hour 59 minute 10 second", "1 days 12 hrs 59 mins 10 secs", "1 days 1 hours 1 minutes 1 seconds", "1 year 59 mins", "1 year 59 mins foobar", NULL }; int main(void) { exec sql begin declare section; date date1; timestamp ts1, ts2; char *text; interval *i1; date *dc; exec sql end declare section; int i, j; char *endptr; ECPGdebug(1, stderr); /* 从文本中解析一个时间戳并将日期转换成字符串 */ ts1 = PGTYPEStimestamp_from_asc("2003-12-04 17:34:29", NULL); text = PGTYPEStimestamp_to_asc(ts1); printf("timestamp: %s\n", text); free(text); /* 从时间戳中抽取日期部分 */ date1 = PGTYPESdate_from_timestamp(ts1); dc = PGTYPESdate_new(); *dc = date1; /* 返回一个日期变量的文本表达 */ text = PGTYPESdate_to_asc(*dc); printf("Date of timestamp: %s\n", text); free(text); PGTYPESdate_free(dc); for (i = 0; dates[i]; i++) { bool err = false; /* 从日期的文本表达解析一个日期 */ date1 = PGTYPESdate_from_asc(dates[i], &endptr); if (date1 == INT_MIN) { err = true; } /* 返回一个日期变量的文本表达 */ text = PGTYPESdate_to_asc(date1); printf("Date[%d]: %s (%c - %c)\n", i, err ? "-" : text, endptr ? 'N' : 'Y', err ? 'T' : 'F'); free(text); if (!err) { for (j = 0; times[j]; j++) { int length = strlen(dates[i])+ 1+ strlen(times[j])+ 1; char* t = (char *)malloc(length); sprintf(t, "%s %s", dates[i], times[j]); /* 从文本中解析一个时间戳并将日期转换成字符串 */ ts1 = PGTYPEStimestamp_from_asc(t, NULL); text = PGTYPEStimestamp_to_asc(ts1); if (i != 19 || j != 3) printf("TS[%d,%d]: %s\n",i, j, errno ? "-" : text); free(text); free(t); } } } /* 从文本中解析一个时间戳 */ ts1 = PGTYPEStimestamp_from_asc("2004-04-04 23:23:23", NULL); for (i = 0; intervals[i]; i++) { interval *ic; /* 从文本中解析一个区间 */ i1 = PGTYPESinterval_from_asc(intervals[i], &endptr); if (*endptr) printf("endptr set to %s\n", endptr); if (!i1) { printf("Error parsing interval %d\n", i); continue; } /* 把一个区间变量加到时间戳变量上 */ j = PGTYPEStimestamp_add_interval(&ts1, i1, &ts2); if (j < 0) continue; /* 将一个区间类型变量转换成文本格式 */ text = PGTYPESinterval_to_asc(i1); printf("interval[%d]: %s\n", i, text ? text : "-"); free(text); /* 返回一个已分配区间变量的指针 */ ic = PGTYPESinterval_new(); /* 复制一个区间类型的变量 */ PGTYPESinterval_copy(i1, ic); /* 将一个区间类型变量转换成文本格式 */ text = PGTYPESinterval_to_asc(i1); printf("interval_copy[%d]: %s\n", i, text ? text : "-"); free(text); /* 释放已经分配区间变量的内存 */ PGTYPESinterval_free(ic); PGTYPESinterval_free(i1); } return (0); } 示例二:使用pgtypes库函数对numeric类型进行不同操作。#include <stdio.h> #include <stdlib.h> #include <pgtypes_numeric.h> #include <pgtypes_error.h> #include <decimal.h> char* nums[] = { "2E394", "-2", ".794", "3.44", "592.49E21", "-32.84e4", "2E-394", ".1E-2", "+.0", "-592.49E-07", "+32.84e-4", ".500001", "-.5000001", "1234567890123456789012345678.91", /* 30个数位应转换为十进制 */ "1234567890123456789012345678.921", /* 31个数位的数字不应转为十进制 */ "not a number", NULL }; static void check_errno(void); int main(void) { char *text="error\n"; char *endptr; numeric *num, *nin; decimal *dec; long l; int i, j, k, q, r, count = 0; double d; numeric **numarr = (numeric **) calloc(1, sizeof(numeric)); ECPGdebug(1, stderr); for (i = 0; nums[i]; i++) { /* 返回由malloc分配的字符串的指针,它包含numeric类型nums[i]的字符串表达 */ num = PGTYPESnumeric_from_asc(nums[i], &endptr); if (!num) check_errno(); if (endptr != NULL) { printf("endptr of %d is not NULL\n", i); if (*endptr != '\0') printf("*endptr of %d is not \\0\n", i); } if (!num) continue; numarr = (numeric **)realloc(numarr, sizeof(numeric *) * (count + 1)); numarr[count++] = num; /* 返回由malloc分配的字符串的指针,它包含numeric类型num的字符串表达 */ text = PGTYPESnumeric_to_asc(num, -1); if (!text) check_errno(); printf("num[%d,1]: %s\n", i, text); free(text); text = PGTYPESnumeric_to_asc(num, 0); if (!text) check_errno(); printf("num[%d,2]: %s\n", i, text); free(text); text = PGTYPESnumeric_to_asc(num, 1); if (!text) check_errno(); printf("num[%d,3]: %s\n", i, text); free(text); text = PGTYPESnumeric_to_asc(num, 2); if (!text) check_errno(); printf("num[%d,4]: %s\n", i, text); free(text); /* 请求一个指向新分配的numeric变量的指针 */ nin = PGTYPESnumeric_new(); text = PGTYPESnumeric_to_asc(nin, 2); if (!text) check_errno(); printf("num[%d,5]: %s\n", i, text); free(text); /* 将一个numeric类型的变量转换为长整型 */ r = PGTYPESnumeric_to_long(num, &l); if (r) check_errno(); printf("num[%d,6]: %ld (r: %d)\n", i, r?0L:l, r); if (r == 0) { /* 把一个长整型变量转换为一个numeric变量 */ r = PGTYPESnumeric_from_long(l, nin); if (r) check_errno(); /* 返回由malloc分配的字符串的指针,它包含numeric类型nin的字符串表达 */ text = PGTYPESnumeric_to_asc(nin, 2); /* 比较两个numeric变量 */ q = PGTYPESnumeric_cmp(num, nin); printf("num[%d,7]: %s (r: %d - cmp: %d)\n", i, text, r, q); free(text); } /* 将一个numeric类型的变量转换成整数 */ r = PGTYPESnumeric_to_int(num, &k); if (r) check_errno(); printf("num[%d,8]: %d (r: %d)\n", i, r?0:k, r); if (r == 0) { /* 把一个整数变量转换成一个numeric变量 */ r = PGTYPESnumeric_from_int(k, nin); if (r) check_errno(); /* 返回由malloc分配的字符串的指针,它包含numeric类型nin的字符串表达 */ text = PGTYPESnumeric_to_asc(nin, 2); q = PGTYPESnumeric_cmp(num, nin); printf("num[%d,9]: %s (r: %d - cmp: %d)\n", i, text, r, q); free(text); } if (i != 6) { /* 将一个numeric类型的变量转换成双精度类型 */ r = PGTYPESnumeric_to_double(num, &d); if (r) check_errno(); printf("num[%d,10]: %g (r: %d)\n", i, r?0.0:d, r); } /* 请求一个指向新分配的numeric变量的指针*/ dec = PGTYPESdecimal_new(); /* 将一个decimal类型的变量转换成numeric */ r = PGTYPESnumeric_to_decimal(num, dec); if (r) check_errno(); printf("num[%d,11]: - (r: %d)\n", i, r); if (r == 0) { /* 将一个decimal类型的变量转换成numeric */ r = PGTYPESnumeric_from_decimal(dec, nin); if (r) check_errno(); /* 返回由malloc分配的字符串的指针,它包含numeric类型nin的字符串表达 */ text = PGTYPESnumeric_to_asc(nin, 2); /* 比较两个numeric变量 */ q = PGTYPESnumeric_cmp(num, nin); printf("num[%d,12]: %s (r: %d - cmp: %d)\n", i, text, r, q); free(text); } /* 释放numeric变量的内存 */ PGTYPESdecimal_free(dec); PGTYPESnumeric_free(nin); printf("\n"); } for (i = 0; i < count; i++) { for (j = 0; j < count; j++) { /* 请求一个指向新分配的numeric变量的指针 */ numeric* a = PGTYPESnumeric_new(); numeric* s = PGTYPESnumeric_new(); numeric* m = PGTYPESnumeric_new(); numeric* d = PGTYPESnumeric_new(); /* 把两个numeric变量相加放到第三个numeric变量中 */ r = PGTYPESnumeric_add(numarr[i], numarr[j], a); if (r) { check_errno(); printf("r: %d\n", r); } else { /* 返回由malloc分配的字符串的指针,它包含numeric类型a的字符串表达 */ text = PGTYPESnumeric_to_asc(a, 10); printf("num[a,%d,%d]: %s\n", i, j, text); free(text); } /* 把两个numeric变量相减并且把结果返回到第三个numeric变量 */ r = PGTYPESnumeric_sub(numarr[i], numarr[j], s); if (r) { check_errno(); printf("r: %d\n", r); } else { /* 返回由malloc分配的字符串的指针,它包含numeric类型s的字符串表达 */ text = PGTYPESnumeric_to_asc(s, 10); printf("num[s,%d,%d]: %s\n", i, j, text); free(text); } /* 把两个numeric变量相乘并且把结果返回到第三个numeric变量 */ r = PGTYPESnumeric_mul(numarr[i], numarr[j], m); if (r) { check_errno(); printf("r: %d\n", r); } else { /* 返回由malloc分配的字符串的指针,它包含numeric类型m的字符串表达 */ text = PGTYPESnumeric_to_asc(m, 10); printf("num[m,%d,%d]: %s\n", i, j, text); free(text); } /* 把两个numeric变量相除并且把结果返回到第三个numeric变量 */ r = PGTYPESnumeric_div(numarr[i], numarr[j], d); if (r) { check_errno(); printf("r: %d\n", r); } else { /* 返回由malloc分配的字符串的指针,它包含numeric类型d的字符串表达 */ text = PGTYPESnumeric_to_asc(d, 10); printf("num[d,%d,%d]: %s\n", i, j, text); free(text); } /* 释放一个numeric变量的内存 */ PGTYPESnumeric_free(a); PGTYPESnumeric_free(s); PGTYPESnumeric_free(m); PGTYPESnumeric_free(d); } } for (i = 0; i < count; i++) { /* 返回由malloc分配的字符串的指针,它包含numeric类型numarr[i]的字符串表达 */ text = PGTYPESnumeric_to_asc(numarr[i], -1); printf("%d: %s\n", i, text); free(text); /* 释放内存 */ PGTYPESnumeric_free(numarr[i]); } free(numarr); return (0); } /* 错误处理 */ static void check_errno(void) { switch(errno) { case 0: printf("(no errno set) - "); break; case PGTYPES_NUM_OVERFLOW: printf("(errno == PGTYPES_NUM_OVERFLOW) - "); break; case PGTYPES_NUM_UNDERFLOW: printf("(errno == PGTYPES_NUM_UNDERFLOW) - "); break; case PGTYPES_NUM_BAD_NUMERIC: printf("(errno == PGTYPES_NUM_BAD_NUMERIC) - "); break; case PGTYPES_NUM_DIVIDE_ZERO: printf("(errno == PGTYPES_NUM_DIVIDE_ZERO) - "); break; default: printf("(unknown errno (%d))\n", errno); printf("(libc: (%s)) ", strerror(errno)); break; } }
  • [技术解读] GaussDB统计信息更新
    GaussDB统计信息更新深度指南:从原理到生产实践一、统计信息核心价值关键统计维度统计项 存储位置 更新触发条件表行数估算 pg_class.reltuples 执行ANALYZE/VACUUM索引选择性 pg_stat_all_indexes 索引创建/数据变更列值分布直方图 pg_stats 数据分布显著变化最小/最大值 pg_class.reloptions 显式ANALYZE执行二、更新操作全解析基础更新命令sql-- 更新单表统计信息 ANALYZE VERBOSE orders; -- 更新整个数据库 ANALYZE VERBOSE; -- 更新指定模式 ANALYZE VERBOSE schema_name.*; -- 更新特定列统计 ANALYZE orders (create_time, status); 增量更新机制sql-- 启用自动增量统计 ALTER TABLE orders SET ( autovacuum_enabled = true, autovacuum_analyze_scale_factor = 0.05, -- 5%数据变更触发 autovacuum_analyze_threshold = 50 -- 50行变更绝对阈值 ); 三、生产环境实践定时维护方案bash# 每日凌晨执行全库分析 0 3 * * * psql -U postgres -c "ANALYZE VERBOSE;" # 每小时增量更新 0 * * * * psql -U postgres -c "ANALYZE VERBOSE orders, customers;" 监控告警配置sql-- 创建统计信息监控视图 CREATE VIEW stat_info_monitor AS SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); -- 设置异常阈值告警 SELECT * FROM stat_info_monitor WHERE n_dead_tup > 10000 OR age(last_autoanalyze) > interval '1 hour'; 四、性能优化案例案例:慢查询优化​​问题现象​​:sqlEXPLAIN ANALYZE SELECT * FROM sales WHERE product_id = 123 AND sale_date > '2023-01-01'; ​​执行计划分析​​:textSeq Scan on sales (cost=0.00..10000.00 rows=10000 width=128) Filter: (product_id = 123 AND sale_date > '2023-01-01'::date) ​​优化步骤​​:更新统计信息:sqlANALYZE VERBOSE sales (product_id, sale_date); 重新生成执行计划:sqlEXPLAIN ANALYZE SELECT * FROM sales WHERE product_id = 123 AND sale_date > '2023-01-01'; ​​优化效果​​:textIndex Scan using idx_sales_pid_sd on sales (cost=0.42..8.44 rows=1 width=128) Index Cond: ((product_id = 123) AND (sale_date > '2023-01-01'::date)) 指标 优化前 优化后 变化率执行时间 1200ms 15ms 98.75%↓索引使用率 0% 100% +100%↑扫描行数 10000 1 99.99%↓五、高级调优技巧并行统计收集sql-- 设置并行度 SET parallel_workers = 4; -- 执行并行分析 ANALYZE VERBOSE orders WITH (parallel_workers = 4); 统计信息导出/导入bash# 导出统计信息 pg_dump -Fc -d postgres -t public.orders > orders_stats.dump # 导入统计信息 pg_restore -d new_db orders_stats.dump六、常见问题处理统计信息不生效​​诊断步骤​​:sql-- 检查自动分析配置 SHOW autovacuum; -- 查看表最后分析时间 SELECT last_autoanalyze FROM pg_stat_all_tables WHERE relname = 'orders'; ​​解决方案​​:sql-- 手动触发立即分析 ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0); ANALYZE orders; 七、最佳实践总结​​更新策略​​:高频变更表:设置autovacuum_analyze_scale_factor=0.01静态数据表:禁用自动分析关键业务表:配置定时全量分析​​监控基线​​:text| 监控指标 | 正常阈值 | 告警阈值 | |-------------------------|---------------|---------------| | dead_tuple占比 | <5% | >10%触发告警 | | 分析延迟 | <1小时 | >2小时告警 | | 统计信息年龄 | <1天 | >3天告警 |通过科学的统计信息管理,某电商平台实现了:查询计划准确率提升至99%慢查询数量下降85%维护成本降低60%建议建立统计信息生命周期管理体系,结合业务数据变化特征实施精准调优。
  • [技术解读] GaussDB数据类型转换
    GaussDB数据类型转换实战指南:从原理到性能优化引言在数据架构演进与系统迁移过程中,数据类型转换是确保业务连续性的关键技术环节。GaussDB作为新一代分布式数据库,提供了灵活强大的类型转换能力,但也存在隐式转换性能优化空间。本文将深入解析GaussDB的类型转换机制,通过迁移案例揭示最佳实践,并针对高频问题给出解决方案,帮助开发者在复杂场景中实现安全高效的类型转换。一、类型转换核心机制显式转换语法体系sql-- 基础类型显式转换 SELECT CAST('123' AS INTEGER); -- 123 SELECT ::BIGINT '9223372036854775807'; -- 最大64位整数 -- 复杂类型转换 SELECT jsonb_build_object( 'id', id::TEXT, 'amount', amount::NUMERIC(18,2 FROM orders; -- 使用类型别名 SELECT CAST(current_timestamp AS timestamptz); 隐式转换规则矩阵sql-- 错误示例:隐式转换导致数据丢失 CREATE TABLE test_conversion ( id INTEGER, value VARCHAR(10) ); INSERT INTO test_conversion VALUES (1, '12345678901'); -- 超出INTEGER范围 -- 隐式转换报错 SELECT id, value::INTEGER FROM test_conversion; -- ERROR: invalid input syntax for type integer: "12345678901" 二、迁移场景实战MySQL到GaussDB迁移​​类型映射对照表​​:text| MySQL类型 | GaussDB推荐类型 | 转换方法 | |---------------|-----------------------|------------------------------| | TINYINT | SMALLINT | CAST(col AS SMALLINT) | | DATETIME | TIMESTAMPTZ | ::timestamptz | | TEXT | VARCHAR(65535) | CAST(col AS VARCHAR) | | ENUM('A','B') | VARCHAR(10) | CASE WHEN col='A' THEN... |​​迁移脚本示例​​:sql-- 处理自增主键差异 CREATE TABLE mysql_orders ( id INT AUTO_INCREMENT PRIMARY KEY, amount DECIMAL(10,2) ) ENGINE=InnoDB; -- GaussDB兼容转换 CREATE TABLE gauss_orders ( id SERIAL PRIMARY KEY, -- 自动生成序列 amount NUMERIC(10,2) -- 精确数值类型 ); -- 数据迁移时处理自增偏移 INSERT INTO gauss_orders (id, amount) SELECT id + 00000, amount::NUMERIC FROM mysql_orders; JSON数据迁移优化sql-- MongoDB JSON文档转换 db.users.find().forEach(function(user) { db.gauss_users.insert({ _id: user._id.str, -- ObjectId转字符串 name: user.name, meta: tojson(user.meta) -- 嵌套文档转换 }); }); -- GaussDB优化存储 ALTER TABLE gauss_users ALTER COLUMN meta TYPE JSONB USING meta::JSONB; -- 启用二进制存储与GIN索引三、性能优化策略批量转换优化sql-- 使用并行转换提升吞吐量 max_parallel_workers_per_gather = 4; ALTER TABLE large_table ALTER COLUMN old_col TYPE INTEGER USING old_col::INTEGER WITH (TYPE_CONVERT_PARALLEL_DEGREE = 8); -- 并行度控制存储空间优化sql-- 类型压缩方案对比 CREATE TABLE test_compression ( raw_data TEXT, compressed_data BYTEA GENERATED ALWAYS AS ( pg_column_size(raw_data)::BYTEA ) STORED AS TOAST ); 索引策略调整sql-- 转换后索引重建 CREATE INDEX CONCURRENTLY idx_converted_date ON sales USING btree (sale_date::date); -- 显式转换后创建索引 -- 函数索引应用 CREATE INDEX idx_lower_email ON users USING gin (lower(email::text) gin_trgm_ops); 四、异常处理与监控错误诊断模板sqlDO $$ BEGIN PERFORM 'invalid_data'::INTEGER; -- 故意触发异常 EXCEPTION WHEN others THEN RAISE NOTICE '错误代码: %, 消息: %', SQLSTATE, SQLERRM; -- 记录到日志表 INSERT INTO error_log (msg) VALUES (SQLERRM); END $$; 性能监控指标sql-- 类型转换性能分析 SELECT query, calls, total_time, rows, width FROM pg_stat_statements WHERE query ILIKE '%::%'; -- 过滤类型转换语句 -- 执行计划中的转换提示 EXPLAIN ANALYZE SELECT * FROM orders WHERE status::VARCHAR = 'PROCESSING'; -- 检查是否发生意外转换五、最佳实践总结​​迁移黄金法则​​:执行ANALYZE VERBOSE验证统计信息准确性使用pg_dump --column-inserts生成显式转换脚本对遗留系统实施pg_upgrade前进行类型兼容性检查​​运维监控基线​​:text| 监控项 | 阈值 | 响应措施 | |-----------------------|--------------|--------------------------| | 隐式转换错误率 | >0.1% | 立即修正应用程序代码 | | 类型转换耗时占比 | >15% | 优化SQL语句或调整数据模型| | TOAST存储膨胀率 | >200% | 修改列类型或启用压缩 |通过科学实施类型转换策略,某电商平台在迁移到GaussDB过程中实现了:数据迁移错误率降低至0.02%JSON文档查询性能提升3倍存储空间占用减少65%建议建立类型转换知识库,记录常见转换模式与性能参数,在DevOps流程中集成自动化验证工具,确保数据架构演进的平滑过渡。作者:兮酱
  • [技术解读] GaussDB数据类型深度解析
    GaussDB数据类型深度解析:从基础类型到高性能实践​​引言​​在数据库设计与开发中,数据类型的选择直接影响存储效率、查询性能和数据完整性。GaussDB作为新一代分布式关系型数据库,提供了丰富的数据类型体系,既兼容传统SQL标准,又针对现代业务场景进行了深度优化。本文将系统解析GaussDB的核心数据类型,结合实际应用场景揭示其设计原理,并通过性能优化案例展示如何通过数据类型选择实现效率跃升。无论是金融交易中的高精度数值处理,还是物联网场景下的时序数据存储,GaussDB的数据类型都能为业务提供精准支撑。一、核心数据类型体系数值类型矩阵sql-- 使用DECIMAL替代DOUBLE避免精度丢失 CREATE TABLE financial_data ( transaction_id INT PRIMARY KEY, amount DECIMAL(18,2) -- 精确存储货币值 ); 字符串类型选择​实战案例​​:sql-- 存储用户评论(混合类型) CREATE TABLE user_comments ( comment_id SERIAL PRIMARY KEY, content TEXT NOT NULL, metadata JSONB -- 存储{ "sentiment": "positive", "tags": ["tech", "news"] } ); 二、高级数据类型应用时空数据处理sql-- 时序数据存储模板 CREATE TABLE sensor_data ( device_id INT, reading_time TIMESTAMPTZ, -- 带时区时间戳 temperature FLOAT8, location GEOGRAPHY(POINT) -- 地理空间类型 ) PARTITION BY RANGE (reading_time); 枚举与自定义类型sql-- 创建业务状态枚举 CREATE TYPE order_status AS ENUM ('CREATED', 'PROCESSING', 'SHIPPED'); -- 使用复合类型 CREATE TYPE address AS ( street TEXT, city VARCHAR(50), zip_code VARCHAR(10) ); -- 表结构定义 CREATE TABLE orders ( SERIAL PRIMARY KEY, status order_status DEFAULT 'CREATED', shipping_address address ); 三、性能优化策略存储效率优化示例​​:sql-- 优化前 CREATE TABLE user_flags ( user_id INT PRIMARY KEY, flags INT -- 用整数位表示权限 ); -- 优化后 CREATE TABLE user_flags ( user_id INT PRIMARY KEY, permissions BIT(64) -- 直接存储位掩码 ); 索引效能提升sql-- 范围查询优化 CREATE INDEX idx_sensor_time ON sensor_data USING BRIN (reading_time) 块级索引 -- JSONB路径索引 CREATE INDEX idx_metadata_tags ON user_comments USING GIN ((metadata -> 'tags')); 四、兼容性与迁移指南迁移验证脚本sql-- 类型兼容性检查 DO $$ DECLARE col_record RECORD; BEGIN FOR col_record IN SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' LOOP IF col_record.data_type = 'INT' THEN RAISE NOTICE 'Check column % for 32/64位兼容性', col_record.column_name; END IF; END LOOP; END $$; 五、最佳实践总结​​选型原则​​:精确数值用DECIMAL替代FLOAT固定长度字符串优先用CHAR时空数据选用GEOGRAPHY类型​​监控指标​​:sql-- 数据类型相关性能监控 SELECT schemaname, tablename, attname, pg_column_size(attname) AS size_bytes, n_distinct FROM pg_stats WHERE schemaname NOT IN ('pg_catalog'); 通过合理运用GaussDB的数据类型体系,某物流企业实现了:时序数据存储压缩率提升70%地理空间查询响应时间缩短至毫秒级数据迁移兼容性问题减少90%建议建立数据类型选型规范,在架构设计阶段即考虑存储效率与业务需求的平衡,并通过定期执行ANALYZE VERBOSE保持统计信息准确性。数据类型作为数据库设计的基石,直接影响着系统性能、存储效率与业务连续性。通过深入理解GaussDB丰富的数据类型体系——从基础的数值、字符串时空地理、JSONB等高级类型,开发者能够精准匹配业务场景需求,在事务处理、时序数据分析、地理空间计算等领域实现效率跃升。本文通过性能优化案例与迁移实践验证了科学选型的核心价值:合理使用DECIMAL可规避金融计算误差,JSONB能高效支撑半结构化数据处理,而时空类型则赋能物联网场景的实时分析。作者:兮酱
  • [技术解读] GaussDB经验总结:SQL语句改写规则
    GaussDB经验总结:SQL语句改写规则根据数据库的SQL执行机制以及大量的实践,总结发现:通过一定的规则调整SQL语句,在保证结果正确的基础上,能够提高SQL执行效率。如果遵守这些规则,常常能够大幅度提升业务查询效率。使用union all代替unionunion在合并两个集合时会执行去重操作,而union all则直接将两个结果集合并,不执行去重。执行去重会消耗大量的时间,因此,在一些实际应用场景中,如果通过业务逻辑已确认两个集合不存在重叠,可用union all替代union以便提升性能。join列增加非空过滤条件若join列上的NULL值较多,则可以加上is not null过滤条件,以实现数据的提前过滤,提高join效率。not in转not existsnot in语句需要使用nestloop anti join来实现,而not exists则可以通过hash anti join来实现。在join列不存在NULL值的情况下,not exists和not in等价。因此在确保没有NULL值时,可以通过将not in转换为not exists,通过生成hash join来提升查询效率。如下所示,如果t2.d2字段中没有NULL值(t2.d2字段在表定义中not null)查询可以修改为:SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.c1=t2.d2); 选择hashagg查询中GROUP BY语句如果生成了groupagg+sort的plan性能会比较差,可以通过加大work_mem的方法生成hashagg的plan,因为不用排序而提高性能。尝试将函数替换为case语句GaussDB函数调用性能较低,如果出现过多的函数调用导致性能下降,可以根据情况把可下推的函数改成CASE表达式。避免对索引使用函数或表达式运算对索引使用函数或表达式运算会停止使用索引转而执行全表扫描。尽量避免在where子句中使用“!=”或“<”“>”操作符、NULL值判断、or连接、参数隐式转换。如果WHERE条件中出现了>=和<=同一个值,由于当前不支持范围等价类推导,尽量把条件改为=查询。SELECT * FROM t1 WHERE c1 >= 1 AND c1 <= 1修改为:SELECT * FROM t1 WHERE c1 = 1对于范围查询,优化器在计算选择率时误差相对等值查询较大,所以尽可能把范围查询改为等值查询。对复杂SQL语句进行拆分对于过于复杂并且不易通过以上方法调整性能的SQL可以考虑拆分的方法,把SQL中某一部分拆分成独立的SQL并把执行结果存入临时表,拆分常见的场景包括但不限于:作业中多个SQL有同样的子查询,并且子查询数据量较大。Plan cost计算不准,导致子查询hash bucket太小,比如实际数据1000W行,hash bucket只有1000。函数(如substr,to_number)导致大数据量子查询选择度计算不准。多DN环境下对大表做broadcast的子查询。
  • [技术解读] GaussDB性能调优:从根因分析到优化落地
    GaussDB性能调优:从根因分析到优化落地一、性能瓶颈诊断体系多维度监控指标矩阵-- 启用扩展事件监控 CREATE EVENT TRIGGER perf_monitor ON ddl_command_end EXECUTE FUNCTION pg_stat_statements; -- 关键性能视图查询 SELECT pid, now() - query_start AS duration, state, wait_event_type, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC LIMIT 5; 系统级性能剖析-- OS级资源监控脚本示例 while true; do echo "$(date) => CPU:$(top -bn1 | grep "Cpu(s)" | awk '{print $2}')% MEM:$(free -m | awk '/Mem:/ {print $3"M/"$2"M"}')" >> /var/log/gaussdb_perf.log sleep 60 done二、核心调优维度详解执行计划优化案例:关联查询性能提升8倍-- 原始低效执行计划 EXPLAIN ANALYZE SELECT a.*, b.* FROM orders a JOIN customers b ON a.customer_id = b.id WHERE a.create_time BETWEEN '2023-01-01' AND '2023-06-30'; -- 优化后执行策略 ALTER TABLE orders ADD INDEX idx_create_time (create_time); ALTER TABLE customers ADD INDEX idx_customer_id (id); -- 强制索引使用 SELECT /*+ index(a idx_create_time) index(b idx_customer_id) */ a.*, b.* FROM orders a JOIN customers b ON a.customer_id = b.id WHERE a.create_time BETWEEN '2023-01-01' AND '2023-06-30'; 参数动态调优关键参数配置包-- 并行查询优化配置 ALTER SYSTEM SET max_parallel_workers_per_gather = 8; ALTER SYSTEM SET parallel_setup_cost = 1000; ALTER SYSTEM SET parallel_tuple_cost = 0.001; -- 内存管理优化组合 ALTER SYSTEM SET work_mem = '256MB'; -- 排序/哈希操作内存 ALTER SYSTEM SET maintenance_work_mem = '4GB'; -- 维护操作内存 ALTER SYSTEM SET shared_buffers = '25%'; -- 数据缓存配置存储引擎调优-- 表空间配置优化 CREATE TABLESPACE fast_ssd LOCATION '/ssd1/gaussdb_data' WITH (INITIAL_EXTENT_SIZE = '128MB', AUTOEXTEND_ON); -- 列存储参数调整 CREATE TABLE fact_sales ( ... ) WITH ( orientation = column, compression = medium, max_partition = 1024 ); 三、高级优化技术矩阵自动调优系统构建s-- 创建自动调优任务 SELECT * FROM DBMS_AUTO_TRACE.CREATE_TASK( task_name => 'nightly_optimize', operation => DBMS_AUTO_TRACE.OPTIMIZE_SQL, scope => DBMS_AUTO_TRACE.SCOPE_ALL ); -- 查看优化建议 SELECT * FROM DBMS_AUTO_TRACE.REPORT_TASK('nightly_optimize'); 分布式查询优化跨节点查询优化公式最优分片数 = (总节点数 × 最佳并行度) / 数据倾斜系数分布式查询重写示例-- 原始跨分片查询 SELECT region, SUM(amount) FROM sales GROUP BY region; -- 优化后全局聚合方案 ALTER TABLE sales SET DISTRIBUTED BY HASH(region); -- 创建中间聚合表 CREATE MATERIALIZED VIEW region_sales_mv REFRESH FAST ON DEMAND AS SELECT region, SUM(amount) FROM sales GROUP BY region; 四、典型场景优化方案库场景1:大事务处理优化-- 分批次提交改造 DO $$ DECLARE batch_size INT := 10000; total_rows INT; BEGIN SELECT COUNT(*) INTO total_rows FROM orders_archive; FOR i IN 1..CEIL(total_rows/batch_size) LOOP UPDATE orders_archive SET status = 'processed' WHERE ctid BETWEEN ((i-1)*batch_size+1) AND (i*batch_size); COMMIT; PERFORM pg_sleep(0.1); -- 控制事务频率 END LOOP; END $$; 场景2:实时分析性能提升-- 实时数仓优化组合拳 CREATE EXTENSION citus; ALTER TABLE iot_metrics SET ( autovacuum_enabled = true, toast.autovacuum_enabled = true, autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.01 ); CREATE INDEX CONCURRENTLY idx_iot_time ON iot_metrics(time_bucket); 五、性能验证方法论基准测试模型-- 使用pgbench定制化压测 pgbench -c 64 -j 2 -T 300 -U postgres \ -f custom_test.sql \ -D scaling_factor=100 \ -g -l --report-latencies=histogram六、GaussDB性能调优的要点总结1、核心调优维度​执行计划优化使用EXPLAIN ANALYZE分析执行计划强制索引使用(/*+ index() */提示)避免全表扫描,优化关联查询顺序​参数动态调优内存参数:work_mem(排序/哈希)、shared_buffers(数据缓存)并行参数:max_parallel_workers_per_gather(并行度)成本参数:random_page_cost(磁盘I/O成本)​存储引擎优化列存储压缩(orientation=column)分区表设计(范围分区/列表分区)物化视图预计算(REFRESH FAST ON DEMAND)
  • [技术解读] GaussDB动态SQL执行深度解析:从原理到实战
    GaussDB动态SQL执行深度解析:从原理到实战一、动态SQL核心机制动态SQL执行原理sql-- 基础动态SQL模板 DO $$ DECLARE sql_text TEXT; result INT; BEGIN sql_text := 'SELECT COUNT(*) FROM orders WHERE create_time > $1'; EXECUTE sql_text USING '2023-01-01' INTO result; RAISE NOTICE 'Total orders: %', result; END $$; 执行流程解析:SQL文本动态拼接(sql_text变量)参数绑定(USING子句)执行计划生成(自动或强制重用)结果返回/存储二、高级应用场景实现分页查询优化方案sql-- 动态分页参数处理 CREATE OR REPLACE FUNCTION get_paginated_data( IN page_num INT, IN page_size INT, IN sort_column TEXT ) RETURNS TABLE(*) AS $$ DECLARE offset_val INT := (page_num-1)*page_size; sql_text TEXT; BEGIN sql_text := format( 'SELECT * FROM orders ORDER BY %I LIMIT %s OFFSET %s', sort_column, page_size::TEXT, offset_val::TEXT ); RETURN QUERY EXECUTE sql_text; END; $$ LANGUAGE plpgsql; 性能优化点:使用format()函数安全拼接标识符参数化分页参数防止SQL注入自动重用执行计划(设置plan_cache_mode)2. 条件过滤动态构建sql-- 动态WHERE条件生成 DO $$ DECLARE conditions TEXT[] := ARRAY['status = ''A''', 'amount > 100']; where_clause TEXT := ''; sql_text TEXT; BEGIN IF conditions IS NOT EMPTY THEN where_clause := 'WHERE ' || array_to_string(conditions, ' AND '); END IF; sql_text := 'SELECT * FROM orders ' || where_clause; EXECUTE sql_text; END $$; 三、性能优化关键技术执行计划缓存控制sql-- 设置执行计划缓存策略 SET plan_cache_mode = 'force_custom_plan'; -- 强制每次生成新计划 -- 查看缓存命中率 SELECT sum(case when usecnt > 0 then 1 else 0 end) * 100.0 / count(*) as cache_hit_ratio FROM pg_prepared_statements; 批量操作优化sql-- 动态批量插入优化 CREATE OR REPLACE FUNCTION batch_insert( IN data JSONB ) RETURNS VOID AS $$ DECLARE row_data JSONB; insert_sql TEXT := 'INSERT INTO target_table (col1, col2) VALUES ($1, $2)'; BEGIN FOREACH row_data IN SELECT * FROM jsonb_array_elements(data) LOOP EXECUTE insert_sql USING (row_data->>'field1')::INT, (row_data->>'field2')::TEXT; END LOOP; END; $$ LANGUAGE plpgsql; 性能对比:方式 执行时间 锁等待次数 WAL生成量逐行INSERT 12.3s 45 12MB动态批量 1.8s 2 0.8MB四、安全防护体系SQL注入防御矩阵sql-- 安全拼接实践对比 -- 危险写法 EXECUTE 'SELECT * FROM ' || user_input_table; -- 安全写法 EXECUTE format('SELECT * FROM %I', user_input_table); -- 标识符白名单校验函数 CREATE OR REPLACE FUNCTION validate_identifier( tbl_name TEXT ) RETURNS BOOLEAN AS $$ BEGIN RETURN tbl_name IN ('orders', 'customers', 'products'); END; $$ LANGUAGE plpgsql; 权限控制策略sql-- 最小权限动态执行 GRANT EXECUTE ON PROCEDURE dynamic_query() TO app_user; REVOKE ALL ON TABLE orders FROM app_user; 五、监控诊断工具链动态SQL追踪模板sql-- 启用动态SQL日志 ALTER SYSTEM SET log_statement = 'all'; ALTER SYSTEM SET log_duration = on; ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d '; -- 查看高频动态SQL SELECT query, calls, total_time, mean_time FROM pg_stat_statements WHERE query ILIKE 'EXECUTE%' ORDER BY total_time DESC LIMIT 10; 性能瓶颈定位流程mermaidgraph TD A[发现慢查询] --> B{是否动态SQL?} B -->|是| C[检查执行计划重用率] B -->|否| D[常规优化流程] C --> E[USING参数绑定率如何?] E -->|低| F[改用参数化查询] E -->|高| G[分析统计信息时效性]六、最佳实践指南开发规范建议​​参数化三原则​​:所有用户输入必须参数化动态标识符使用format() + %I数值类型强制显式转换​​代码复用规范​​:sql-- 推荐模式 EXECUTE sql_template USING param1, param2; -- 避免模式 EXECUTE 'SELECT * FROM table WHERE col = ''' || var || ''''; 运维监控基线监控指标 告警阈值 采集频率动态SQL平均执行时间 >500ms 1分钟执行计划重用率 <85% 5分钟动态SQL错误率 >0.5% 实时典型案例:电商实时推荐系统​​背景​​:某平台需要根据用户行为实时生成商品推荐列表​​动态SQL方案​​:sqlCREATE OR REPLACE FUNCTION get_recommendations( IN user_id INT, IN category_ids INT[], IN limit_count INT ) RETURNS TABLE(product_id INT, score FLOAT) AS $$ DECLARE base_sql TEXT := 'SELECT product_id, similarity_score FROM recommendation_engine WHERE '; filter_conditions TEXT; BEGIN -- 动态构建过滤条件 IF category_ids IS NOT EMPTY THEN filter_conditions := 'category_id = ANY($1)'; ELSE filter_conditions := 'user_behavior_pattern = $1'; END IF; RETURN QUERY EXECUTE base_sql || filter_conditions USING category_ids, user_id LIMIT limit_count; END; $$ LANGUAGE plpgsql; ​​优化成果​​:查询响应时间从3s降至180ms数据库连接池等待减少72%推荐结果更新延迟<500ms通过合理运用GaussDB的动态SQL能力,某金融机构实现了:运维配置变更自动化率提升90%报表生成速度提高15倍动态查询错误率下降至0.02%建议建立动态SQL开发规范,重点关注参数化设计和执行计划缓存管理,在灵活性与安全性之间取得平衡。
  • [技术解读] GaussDB Go驱动开发实战:从环境搭建到连接池优化
    GaussDB Go驱动开发实战:从环境搭建到连接池优化一、环境准备Go语言环境配置bash# 安装Go 1.21+ wget https://go.dev/dl/go1.21.linux-amd64.tar.gz sudo tar -C /usr/local -xzf go1.21.linux-amd64.tar.gz # 配置环境变量 echo 'export PATH=$PATH:/usr/local/go/bin' >> ~/.bashrc source ~/.bashrc # 验证安装 go version # 输出应为:go version go1.21 linux/amd64依赖管理工具bash# 安装Go Modules管理工具 go install golang.org/x/mod/cmd/mod@latest go install github.com/golangci/golangci-lint/cmd/golangci-lint@latest二、驱动安装与配置官方驱动安装bash# 安装GaussDB官方Go驱动 go get -u github.com/gaussdb/gaussdb-go@v1.2.0 # 验证驱动安装 ls $GOPATH/pkg/mod/github.com/gaussdb/gaussdb-go@v1.2.0认证配置创建~/.gaussdb/credentials文件:ini[default] user=your_username password=your_password host=your_host:5432 sslmode=require sslrootcert=root.crt三、基础连接示例简单连接测试gopackage main import ( "context" "fmt" "github.com/gaussdb/gaussdb-go" ) func main() { // 创建连接配置 config := &gaussdb.Config{ User: "your_username", Password: "your_password", Host: "your_host:5432", Database: "postgres", SSLMode: gaussdb.RequireSSL, } // 建立连接 conn, err := gaussdb.Connect(context.Background(), config) if err != nil { panic(fmt.Sprintf("连接失败: %v", err)) } defer conn.Close() // 执行简单查询 var now string err = conn.QueryRow(context.Background(), "SELECT NOW()").Scan(&now) if err != nil { panic(err) } fmt.Printf("当前时间: %s ", now) } 连接池配置go// 创建带连接池的配置 poolConfig := &gaussdb.PoolConfig{ MaxOpenConns: 50, // 最大打开连接数 MaxIdleConns: 10, // 最大空闲连接数 ConnMaxLifetime: 30 * time.Minute, // 连接最大存活时间 } // 使用连接池创建连接 connPool, err := gaussdb.NewConnectionPool(poolConfig) if err != nil { log.Fatalf("创建连接池失败: %v", err) } defer connPool.Close() // 从池中获取连接 conn, err := connPool.Acquire(context.Background()) if err != nil { log.Fatal(err) } defer conn.Release() // 执行批量操作...四、高级配置指南SSL/TLS加密配置goconfig := &gaussdb.Config{ Host: "your_host", Port: 5432, User: "admin", Password: "secure_password", SSLMode: gaussdb.VerifyFullSSL, SSLCert: "/path/to/client-cert.pem", SSLKey: "/path/to/client-key.pem", SSLRootCert: "/path/to/ca-cert.pem", } 负载均衡配置go// 配置多个节点实现负载均衡 clusterConfig := &gaussdb.ClusterConfig{ Nodes: []gaussdb.Node{ {Host: "node1.example.com", Port: 5432}, {Host: "node2.example.com", Port: 5432}, {Host: "node3.example.com", Port: 5432}, }, LoadBalanceStrategy: gaussdb.RoundRobin, // 轮询策略 } conn, err := gaussdb.ConnectWithCluster(context.Background(), clusterConfig) 五、错误处理与重试错误码处理goerr = conn.QueryRow("...").Scan(...) if err != nil { var pgErr *gaussdb.PGError if errors.As(err, &pgErr) { switch pgErr.Code { case "23505": // 唯一约束冲突 handleDuplicateKey() case "22P02": // 数据类型错误 handleInvalidType() default: log.Fatal(err) } } else { log.Fatal(err) } } 自动重试机制gofunc withRetry(ctx context.Context, maxRetries int, fn func() error) error { var err error for i := 0; i < maxRetries; i++ { if err = fn(); err == nil { return nil } // 检查是否可重试错误 var pgErr *gaussdb.PGError if !errors.As(err, &pgErr) || pgErr.Code == "57014" { // 事务超时可重试 return err } time.Sleep(time.Duration(i+1) * time.Second) } return fmt.Errorf("max retries reached: %w", err) } // 使用示例err := withRetry(context.Background(), 3, func() error { _, err := conn.Exec("INSERT INTO ...") return err }) 六、性能优化建议批量操作优化go// 使用COPY命令批量导入 func bulkInsert(rows [][]interface{}) error { tx, err := conn.Begin() if err != nil { return err } defer tx.Rollback() // 创建COPY writer writer := tx.CopyFrom( pgx.Identifier{"target_table"}, []string{"col1", "col2", "col3"}, pgx.CopyFromRows(rows), ) _, err = writer.WriteTo(context.Background()) if err != nil { return err } return tx.Commit() } 连接池调优参数参数 推荐值 说明MaxOpenConns CPU核数×2 避免过多连接消耗资源MaxIdleConns 5-10 保持足够空闲连接ConnMaxLifetime 15-30分钟 防止连接泄漏ConnMaxIdleTime 5分钟 及时回收闲置连接七、监控与诊断启用慢查询日志go// 设置查询超时 ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) defer cancel() _, err = conn.ExecContext(ctx, "SELECT * FROM large_table") 性能指标采集goimport ( "github.com/prometheus/client_golang/prometheus" ) var ( queryDuration = prometheus.NewHistogramVec(prometheus.HistogramOpts{ Name: "gaussdb_query_duration_seconds", Help: "Query execution time in seconds", Buckets: prometheus.DefBuckets, }, []string{"operation", "status"}) ) // 在查询前后记录时间 start := time.Now() _, err = conn.Exec("...") duration := time.Since(start).Seconds() queryDuration.With(prometheus.Labels{ "operation": "select", "status": "success", }).Observe(duration) 八、常见问题排查连接超时问题bash# 检查网络连通性 nc -zv your_host 5432 # 查看防火墙规则 sudo iptables -L -n | grep 5432认证失败处理text错误码: 28000可能原因:用户名/密码错误密码过期密钥文件权限错误(应设置为600)通过本指南,开发者可以快速搭建GaussDB Go开发环境,并掌握连接池管理、性能优化等核心技能。建议结合实际业务场景,逐步实施监控和调优措施,在保证稳定性的同时充分发挥GaussDB的高性能优势。
  • [技术解读] GaussDB运行倾斜的hint
    GaussDB运行倾斜的hint功能描述指明查询运行时重分布过程中存在倾斜的重分布键和倾斜值,针对Join和HashAgg运算中的重分布进行优化。语法格式指定单表倾斜:skew( [@queryblock] table (column) [(value)])指定中间结果倾斜:skew( [@queryblock] (join_rel) (column) [(value)])参数说明@queryblock 见指定Hint所处的查询块Queryblock章节,可省略,表示在当前查询块生效。table表示存在倾斜的单个表名。join_rel表示参与join的两个或多个表,如(t1 t2)表示t1和t2join后的结果存在倾斜。column表示倾斜表中存在倾斜的一个或多个列。value表示倾斜的列中存在倾斜的一个或多个值。说明:skew hint仅在需要重分布且指定的倾斜信息与查询执行过程中的重分布信息相匹配时才会被使用。skew hint受GUC参数skew_option限制,如果参数处于关闭状态,则无法进行skew hint倾斜调优。skew hint目前仅处理普通表和子查询类型的表关系,支持基表hint、子查询hint、with as子句hint。对于子查询,无论提升与否都支持在skew hint中使用,这点与其它hint不一样。对于倾斜表,如果定义了别名,则在hint中必须使用别名。对于倾斜列,在不产生歧义的情况下,可以使用原名也可以使用别名。skew hint的column不支持表达式,如果需要指定采用分布键为表达式的重分布存在倾斜,需要将重分布键指定为新的列,以新的列进行hint。对于倾斜值,个数需为列数的整数倍并按列的顺序进行组合,组合的个数不能超过10个。如果各倾斜列的倾斜值的个数不一样,为了满足按列组合,值可以重复指定。如,表t1的c1和c2存在倾斜,c1列的倾斜值只有a1,而c2列的倾斜有b1和b2,则skew hint如下:skew(t1 (c1 c2) ((a1 b1)(a1 b2)))。例中(a1 b1)为一个值组合,NULL可以作为倾斜值出现,每个hint中的值组合不超过十个, 且需为列的整数倍。在Join的重分布优化中,skew hint中的value不可缺省,在HashAgg中可以缺省。对于表、列、值中若指定多个,则同类间需以空格分离。对于倾斜值,不支持在hint中进行类型强转;对于string类型,需要使用单引号。例如:指定单表倾斜每一个skew hint用来表示一个表关系存在的倾斜信息,如果想要指定在查询中的多个表关系存在的倾斜信息,则通过指定多个skew hint实现。在指定skew时,包括以下四个场景的用法:单列单值: skew(t (c1) (v1))说明:表关系t的c1列中的v1值在查询执行中存在倾斜。单列多值:skew(t (c1) (v1 v2 v3 …))说明:表关系t的c1列中的v1、v2、v3…等值在查询执行中存在倾斜。多列单值:skew(t (c1 c2) (v1 v2))说明:表关系t的c1列的v1值和c2列的v2值在查询执行中存在倾斜。多列多值:skew(t (c1 c2) ((v1 v2) (v3 v4) (v5 v6) …))说明:表关系t的c1列的v1、v3、v5…值和c2列的v2、v4、v6…值在查询执行中存在倾斜。须知:多列多值时,各组倾斜值间也可以不使用括号,如:skew(t (c1 c2) (v1 v2 v3 v4 v5 v6 …))。是否使用括号必须统一,不可混合,如:skew(t (c1 c2) (v1 v2 v3 v4 (v5 v6) …)) 将会产生语法报错。指定中间结果倾斜如果基表不存在倾斜,而是查询执行中的中间结果出现倾斜,则需要通过指定中间结果倾斜的skew hint来进行倾斜的调优。skew((t1 t2) (c1) (v1))说明:表关系t1和t2 Join后的结果存在倾斜,倾斜的是t1表的c1列,c1列的倾斜值是v1。为了避免产生歧义,“c1”只能存在于join_rel的一个表关系中,如果存在同名列则通过别名进行规避。建议如果查询具有多层,则哪一层出现倾斜,则将hint写在哪一层中。对于提升的子查询,skew hint支持直接使用子查询名进行hint。如果明确子查询提升后的哪一个基表存在倾斜,则直接使用基表进行hint的可用性更高。无论对于表或列,若存在别名,则优先使用别名进行hint。
  • [技术解读] GaussDB提供了以下类型的系统表和系统视图
    GaussDB提供了以下类型的系统表和系统视图查看系统表除了创建的表以外,数据库还包含很多系统表。这些系统表包含集群安装信息以及GaussDB上运行的各种查询和进程的信息。可以通过查询系统表来获取有关数据库的信息。“系统表和系统视图”中每个表的说明指出了表是对所有用户可见还是只对初始化用户可见。以初始化用户身份登录才能查询只对初始化用户可见的表。GaussDB提供了以下类型的系统表和系统视图:兼容PostgreSQL的系统表和系统视图这类系统表和系统视图具有PG前缀。GaussDB内核新增的系统表和系统视图这类系统表和系统视图具有GS前缀。实现Oracle兼容的系统表和系统视图这类系统表和视图具有ALL、DBA、USER或PV前缀。查看数据库中包含的表在public Schema下新建以下表格。gaussdb=# CREATE TABLE public.search_table_t1(a int) distribute by hash(a); CREATE TABLE gaussdb=# CREATE TABLE public.search_table_t2(b int) distribute by hash(b); CREATE TABLE gaussdb=# CREATE TABLE public.search_table_t3(c int) distribute by hash(c); CREATE TABLE gaussdb=# CREATE TABLE public.search_table_t4(d int) distribute by hash(d); CREATE TABLE gaussdb=# CREATE TABLE public.search_table_t5(e int) distribute by hash(e); CREATE TABLE在PG_TABLES系统表中查看public Schema中包含的前缀为search_table的表。gaussdb=# SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = ‘public’ AND TABLENAME LIKE ‘search_table%’;结果如下: tablename ----------------- search_table_t1 search_table_t2 search_table_t3 search_table_t4 search_table_t5 (5 rows) 查看数据库用户通过PG_USER可以查看数据库中所有用户的列表,还可以查看用户ID(USESYSID)和用户权限。SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit ---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+------ -------+------------+-----------+-----------+----------------+----------------- roach | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | (1 row) 查看和停止正在运行的查询语句通过视图PG_STAT_ACTIVITY可以查看正在运行的查询语句。方法如下:设置参数track_activities为on。SET track_activities = on; 当此参数为on时,数据库系统才会获取当前活动查询的运行信息。查看正在运行的查询语句。以查看正在运行的查询语句所连接的数据库名、执行查询的用户、查询状态及查询对应的PID为例。SELECT datname, usename, state,pid FROM pg_stat_activity; datname | usename | state | pid ----------+---------+--------+----------------- testdb | Ruby | active | 140298793514752 testdb | Ruby | active | 140298718004992 testdb | Ruby | idle | 140298650908416 testdb | Ruby | idle | 140298625742592 testdb | omm | active | 140298575406848 (5 rows) 如果state字段显示为idle,则表明此连接处于空闲,等待用户输入命令。如果仅需要查看非空闲的查询语句,则使用如下命令查看:SELECT datname, usename, state, pid FROM pg_stat_activity WHERE state != ‘idle’;若需要取消运行时间过长的查询,通过PG_TERMINATE_BACKEND函数,根据线程ID(即2中查询结果的pid字段)结束会话,请执行如下命令。SELECT PG_TERMINATE_BACKEND(140298793514752); 显示如下信息,表示结束会话成功。PG_TERMINATE_BACKEND ---------------------- t (1 row) 显示如下信息,表示用户执行了结束当前会话的操作。FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command说明:gsql客户端使用PG_TERMINATE_BACKEND函数结束当前正在执行会话的后台线程时,如果当前的用户是初始用户,客户端不会退出而是自动重连,即返回“The connection to the server was lost. Attempting reset: Succeeded.”。否则客户端会重连失败,即返回“The connection to the server was lost. Attempting reset: Failed.”。这是因为只有初始用户可以免密登录,普通用户不能免密登录,从而重连失败。对于使用PG_TERMINATE_BACKEND函数结束非活跃的后台线程时。如果打开了线程池,此时空闲的会话没有线程ID,无法结束会话。非线程池模式下,结束的会话不会自动重连。
  • [技术解读] GaussDB数据库支持的分区表为范围分区表、列表分区表和哈希分区表
    创建和管理分区表背景信息GaussDB数据库支持的分区表为范围分区表、列表分区表和哈希分区表。范围分区表:将数据基于范围映射到每一个分区,这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期,例如将销售数据按照月份进行分区。列表分区表:将数据中包含的键值分别存储在不同的分区中,依次将数据映射到每一个分区,分区中包含的键值由创建分区表时指定。哈希分区表:将数据根据内部哈希算法依次映射到每一个分区中,包含的分区个数由创建分区表时指定。分区表和普通表相比具有以下优点:改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索效率。增强可用性:如果分区表的某个分区出现故障,表在其他分区的数据仍然可用。方便维护:如果分区表的某个分区出现故障,需要修复数据,只修复该分区即可。均衡I/O:可以把不同的分区映射到不同的磁盘以平衡I/O,改善整个系统性能。普通表若要转成分区表,需要新建分区表,然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时,请根据业务提前规划是否使用分区表。操作步骤示例一:使用默认表空间创建分区表(假设用户已创建tpcds schema)gaussdb=# CREATE TABLE tpcds.customer_address ( ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_street_number character(10) , ca_street_name character varying(60) , ca_street_type character(15) , ca_suite_number character(10) , ca_city character varying(60) , ca_county character varying(30) , ca_state character(2) , ca_zip character(10) , ca_country character varying(20) , ca_gmt_offset numeric(5,2) , ca_location_type character(20) ) DISTRIBUTE BY HASH (ca_address_sk) PARTITION BY RANGE (ca_address_sk) ( PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE) ) ENABLE ROW MOVEMENT; 当结果显示为如下信息,则表示创建成功。CREATE TABLE插入数据将表tpcds.customer_address的数据插入到表tpcds.web_returns_p2中。例如在数据库中创建了一个表tpcds.customer_address的备份表tpcds.web_returns_p2,现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中,则可以执行如下命令。gaussdb=# CREATE TABLE tpcds.web_returns_p2 ( ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_street_number character(10) , ca_street_name character varying(60) , ca_street_type character(15) , ca_suite_number character(10) , ca_city character varying(60) , ca_county character varying(30) , ca_state character(2) , ca_zip character(10) , ca_country character varying(20) , ca_gmt_offset numeric(5,2) , ca_location_type character(20) ) DISTRIBUTE BY HASH (ca_address_sk) PARTITION BY RANGE (ca_address_sk) ( PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE) ) ENABLE ROW MOVEMENT; CREATE TABLE gaussdb=# INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address; INSERT 0 0修改分区表行迁移属性gaussdb=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT; ALTER TABLE删除分区删除分区P8。gaussdb=# ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8; ALTER TABLE增加分区增加分区P8,范围为 40000<= P8<MAXVALUE。gaussdb=# ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE); ALTER TABLE重命名分区重命名分区P8为P_9。gaussdb=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9; ALTER TABLE重命名分区P_9为P8。gaussdb=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8; ALTER TABLE查询分区查询分区P6。gaussdb=# SELECT * FROM tpcds.web_returns_p2 PARTITION (P6); gaussdb=# SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888); 删除分区表和表空间gaussdb=# DROP TABLE tpcds.customer_address; DROP TABLE gaussdb=# DROP TABLE tpcds.web_returns_p2; DROP TABLE示例二:使用用户自定义表空间(假设用户已创建tpcds schema)按照以下方式对范围分区表进行操作(示例中的tpcds命名空间需提前创建)。创建表空间gaussdb=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1'; gaussdb=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2'; gaussdb=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3'; gaussdb=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4'; 当结果显示为如下信息,则表示创建成功。CREATE TABLESPACE创建分区表gaussdb=# CREATE TABLE tpcds.customer_address ( ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_street_number character(10) , ca_street_name character varying(60) , ca_street_type character(15) , ca_suite_number character(10) , ca_city character varying(60) , ca_county character varying(30) , ca_state character(2) , ca_zip character(10) , ca_country character varying(20) , ca_gmt_offset numeric(5,2) , ca_location_type character(20) ) TABLESPACE example1 DISTRIBUTE BY HASH (ca_address_sk) PARTITION BY RANGE (ca_address_sk) ( PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2 ) ENABLE ROW MOVEMENT; 当结果显示为如下信息,则表示创建成功。CREATE TABLE插入数据将表tpcds.customer_address的数据插入到表tpcds.web_returns_p2中。例如在数据库中创建了一个表tpcds.customer_address的备份表tpcds.web_returns_p2,现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中,则可以执行如下命令。gaussdb=# CREATE TABLE tpcds.web_returns_p2 ( ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_street_number character(10) , ca_street_name character varying(60) , ca_street_type character(15) , ca_suite_number character(10) , ca_city character varying(60) , ca_county character varying(30) , ca_state character(2) , ca_zip character(10) , ca_country character varying(20) , ca_gmt_offset numeric(5,2) , ca_location_type character(20) ) TABLESPACE example1 DISTRIBUTE BY HASH (ca_address_sk) PARTITION BY RANGE (ca_address_sk) ( PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2 ) ENABLE ROW MOVEMENT; CREATE TABLE gaussdb=# INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address; INSERT 0 0修改分区表行迁移属性gaussdb=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT; ALTER TABLE删除分区删除分区P8。gaussdb=# ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8; ALTER TABLE增加分区增加分区P8,范围为 40000<= P8<MAXVALUE。gaussdb=# ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE); ALTER TABLE重命名分区重命名分区P8为P_9。gaussdb=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9; ALTER TABLE重命名分区P_9为P8。gaussdb=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8; ALTER TABLE修改分区的表空间修改分区P6的表空间为example3。gaussdb=# ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P6 TABLESPACE example3; ALTER TABLE修改分区P4的表空间为example4。gaussdb=# ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P4 TABLESPACE example4; ALTER TABLE查询分区查询分区P6。gaussdb=# SELECT * FROM tpcds.web_returns_p2 PARTITION (P6); gaussdb=# SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888); 删除分区表和表空间gaussdb=# DROP TABLE tpcds.customer_address; DROP TABLE gaussdb=# DROP TABLE tpcds.web_returns_p2; DROP TABLE gaussdb=# DROP TABLESPACE example1; gaussdb=# DROP TABLESPACE example2; gaussdb=# DROP TABLESPACE example3; gaussdb=# DROP TABLESPACE example4; DROP TABLESPACE
总条数:1681 到第 页
上滑加载中