云服务器内容精选

  • 选择分布方式 GaussDB(DWS)支持的分布方式有复制表(Replication)、哈希表(Hash)和轮询表(Roundrobin)。 轮询表(Roundrobin)8.1.2及以上集群版支持。 策略 描述 适用场景 优势与劣势 复制表(Replication) 集群中每一个DN实例上都有一份全量表数据。 小表、维度表。 Replication优点是每个DN上都有此表的全量数据,在join操作中可以避免数据重分布操作,从而减小网络开销,同时减少了plan segment(每个plan segment都会起对应的线程)。 Replication缺点是每个DN都保留了表的完整数据,造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。 哈希表(Hash) 表数据通过hash方式散列到集群中的所有DN实例上。 数据量较大的事实表。 在读/写数据时可以利用各个节点的IO资源,大大提升表的读/写速度。 一般情况下大表(1000000条记录以上)定义为Hash表。 轮询表(Roundrobin) 表的每一行被轮番地发送给各个DN,数据会被均匀地分布在各个DN中。 数据量较大的事实表,且使用Hash分布时找不到合适的分布列。 Roundrobin优点是保证了数据不会发生倾斜,从而提高了集群的空间利用率。 Roundrobin缺点是无法像Hash表一样进行DN本地化优化,查询性能通常不如Hash表。 一般在大表无法找到合适的分布列时,定义为Roundrobin表,若大表能够找到合适的分布列,优先选择性能更好的Hash分布。
  • 选择数据类型 高效数据类型,主要包括以下三方面: 尽量使用执行效率比较高的数据类型 一般来说整型数据运算(包括=、>、<、≧、≦、≠等常规的比较运算,以及group by)的效率比字符串、浮点数要高。比如某客户场景中对列存表进行点查询,filter条件在一个numeric列上,执行时间为10+s;修改numeric为int类型之后,执行时间缩短为1.8s左右。 尽量使用短字段的数据类型 长度较短的数据类型不仅可以减小数据文件的大小,提升IO性能;同时也可以减小相关计算时的内存消耗,提升计算性能。比如对于整型数据,如果可以用smallint就尽量不用int,如果可以用int就尽量不用bigint。 使用一致的数据类型 表关联列尽量使用相同的数据类型。如果表关联列数据类型不同,数据库必须动态地转化为相同的数据类型进行比较,这种转换会带来一定的性能开销。
  • 使用分区表 分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表,物理块称之为分区。分区表是一张逻辑表,不存储数据,数据实际是存储在分区上的。分区表和普通表相比具有以下优点: 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索效率。 增强可用性:如果分区表的某个分区出现故障,表在其他分区的数据仍然可用。 方便维护:如果分区表的某个分区出现故障,需要修复数据,只修复该分区即可。 GaussDB(DWS)支持的分区表为范围分区表和列表分区(列表分区8.1.3集群版本支持)。
  • 选择分布列 采用Hash分布方式,需要为用户表指定一个分布列(distribute key)。当插入一条记录时,系统会根据分布列的值进行hash运算后,将数据存储在对应的DN中。 所以Hash分布列选取至关重要,需要满足以下原则: 列值应比较离散,以便数据能够均匀分布到各个DN。例如,考虑选择表的主键为分布列,如在人员信息表中选择身份证号码为分布列。 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如,表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’),那么就应当尽量不用zqdh做分布列。 在满足前两条原则的情况,考虑选择查询中的连接条件为分布列,以便Join任务能够下推到DN中执行,且减少DN之间的通信数据量。 对于Hash分表策略,如果分布列选择不当,可能导致数据倾斜,查询时出现部分DN的I/O短板,从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查,以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性: 1 2 3 4 5 SELECT xc_node_id, count(1) FROM tablename GROUP BY xc_node_id ORDER BY xc_node_id desc; 其中xc_node_id对应DN,一般来说,不同DN的数据量相差5%以上即可视为倾斜,如果相差10%以上就必须要调整分布列。 一般不建议用户新增一列专门用作分布列,尤其不建议用户新增一列,然后用SEQUENCE的值来填充作为分布列。因为SEQUENCE可能会带来性能瓶颈和不必要的维护成本。
  • 使用表压缩 表压缩可以在创建表时开启,压缩表能够使表中的数据以压缩格式存储,意味着占用相对少的内存。 对于I/O读写量大,CPU富足(计算相对小)的场景,选择高压缩比;反之选择低压缩比。建议依据此原则进行不同压缩下的测试和对比,以选择符合自身业务情况的最优压缩比。压缩比通过COMPRESSION参数指定,其支持的取值如下: 列存表为:YES/NO/LOW/MIDDLE/HIGH,默认值为LOW。 行存表为:YES/NO,默认值为NO。(行存表压缩功能暂未商用,如需使用请联系技术支持工程师) 各压缩级别所适用的业务场景说明如下: 压缩级别 所适用的业务场景 低级别压缩 系统CPU使用率高,存储磁盘空间充足。 中度压缩 系统CPU使用率适中,但存储磁盘空间不是特别充足。 高级别压缩 系统CPU使用率低,磁盘空间不充裕。
  • 选择存储方式 表的存储模型选择是表定义的第一步。业务属性是表的存储模型的决定性因素,根据下表选择适合当前业务的存储模型。 一般情况下,如果表的字段比较多(大宽表),查询中涉及到的列不多的情况下,适合列存储。如果表的字段个数比较少,查询大部分字段,那么选择行存储比较好。 存储模型 适用场景 行存 点查询(返回记录少,基于索引的简单查询)。 增删改比较多的场景。 列存 统计分析类查询。 group,join多的场景。 表的行/列存储通过表定义的orientation属性定义。当指定orientation属性为row时,表为行存储;当指定orientation属性为column时,表为列存储;如果不指定,默认为行存储。
  • 应用示例 连接数据库。 具体步骤参见使用gsql命令行客户端连接集群。 创建表student,有id、name和score三个字段。使用哈希函数加密保存name,使用对称密码算法保存score。 1 2 3 4 5 CREATE TABLE student (id int, name text, score text, subject text); INSERT INTO student VALUES (1, gs_hash('alice', 'sha256'), gs_encrypt('95', '12345', 'aes128', 'cbc', 'sha256'),gs_encrypt_aes128('math', '1234')); INSERT INTO student VALUES (2, gs_hash('bob', 'sha256'), gs_encrypt('92', '12345', 'aes128', 'cbc', 'sha256'),gs_encrypt_aes128('english', '1234')); INSERT INTO student VALUES (3, gs_hash('peter', 'sha256'), gs_encrypt('98', '12345', 'aes128', 'cbc', 'sha256'),gs_encrypt_aes128('science', '1234')); 不使用密钥查询表student,通过查询结果可知:没有密钥的用户即使拥有了SELECT权限也无法看到name和score这两列加密数据。 1 2 3 4 5 6 7 8 9 10 11 12 SELECT * FROM student; id | name | score | subject ----+------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+----------- ----------------------------------------------------------------------------------- 1 | 2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90 | AAAAAAAAAABAuUC3VQ+MvPCDAaTUySl1e2gGLr4/ATdCUjTEvova3cb/Ba3ZKqIn1yNVGEFBvJnTq/3sLF4//Gm8qG7AyfNbbqdW3aYErLVpbE/QWFX9Ig== | aFEWQR2gkj iu6sfsAad+dHzfFDHePZ6xd44zyekh+qVFlh9FODZ0DoaFAJXctwUsiqaiitTxW8cCSEaNjS/E7Ke1ruY= 2 | 81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9 | AAAAAAAAAABAuUC3VQ+MvPCDAaTUySl1taXxAoDqE793hgyCJvC0ESdAX5Mtgdq2LXI1f5ZxraQ73WIJVtIBX8oe3gTDxoXGlHbHht4kzM4U8dOwr5rjgg== | aFEWQR2gkj iu6sfsAad+dM8tPTDo/Pds6ZmqdmjGiKxf39+Wzx5NoQ6c8FrzihnRzgc0fycWSu5YGWNOKYWhRsE84Ac= 3 | 026ad9b14a7453b7488daa0c6acbc258b1506f52c441c7c465474c1a564394ff | AAAAAAAAAACnyusORPeApqMUgh56ucQu3uso/Llw5MbPFMkOXuspEzhhnc9vErwOFe6cuGtx8muEyHCX7V5yXs+8FxhNh3n5L3419LDWJJLY2O4merHpSg== | zomphRfHV4 H32hTtgkio1PyrobVO8N+hN7kAKwtygKP2E7Aaf1vsjmtLHcL88jyeJNe1lxe0fAvodzPJAxAuV3UJN4M= (3 rows) 使用密钥查询表student,通过查询结果可知:拥有密钥的用户通过使用gs_encrypt对应的解密函数gs_decrypt解密后,可以查看加密数据。 1 2 3 4 5 6 7 SELECT id, gs_decrypt(score, '12345', 'aes128', 'cbc', 'sha256'),gs_decrypt_aes128(subject, '1234') FROM student; id | gs_decrypt | gs_decrypt_aes128 ----+------------+------------------- 1 | 95 | math 2 | 92 | english 3 | 98 | science (3 rows)
  • 技术背景 哈希函数 哈希函数又称为摘要算法,对于数据data,Hash函数会生成固定长度的数据,即Hash(data)=result。这个过程是不可逆的,即Hash函数不存在反函数,无法由result得到data。在不应保存明文场景(比如口令password属于敏感信息),系统管理员用户也不应该知道用户的明文口令,就应该使用哈希算法存储口令的单向哈希值。 实际使用中会加入盐值和迭代次数,避免相同口令生成相同的哈希值,以防止彩虹表攻击。 图1 哈希函数 对称密码算法 对称密码算法使用相同的密钥来加密和解密数据。对称密码算法分为分组密码算法和流密码算法。 分组密码算法将明文分成固定长度的分组,用密钥对每个分组加密。由于分组长度固定,当明文长度不是分组长度的整数倍时,会对明文做填充处理。由于填充的存在,分组密码算法得到的密文长度会大于明文长度。 流加密算法是指加密和解密双方使用相同伪随机加密数据流作为密钥,明文数据依次与密钥数据流顺次对应加密,得到密文数据流。实践中数据通常是一个位(bit)并用异或(xor)操作加密。流密码算法不需要填充,得到的密文长度等于明文长度。 图2 对称密码算法
  • 技术实现 GaussDB(DWS)主要提供了哈希函数和对称密码算法来实现对数据列的加解密。哈希函数支持sha256,sha384,sha512和国密sm3。对称密码算法支持aes128,aes192,aes256和国密sm4。 哈希函数 md5(string) 将string使用MD5加密,并以16进制数作为返回值。MD5的安全性较低,不建议使用。 gs_hash(hashstr, hashmethod) 以hashmethod算法对hashstr字符串进行信息摘要,返回信息摘要字符串。支持的hashmethod:sha256,sha384,sha512,sm3。 对称密码算法 gs_encrypt(encryptstr, keystr, cryptotype, cryptomode, hashmethod) 采用cryptotype和cryptomode组成的加密算法以及hashmethod指定的HMAC算法,以keystr为密钥对encryptstr字符串进行加密,返回加密后的字符串。 gs_decrypt(decryptstr, keystr, cryptotype, cryptomode, hashmethod) 采用cryptotype和cryptomode组成的加密算法以及hashmethod指定的HMAC算法,以keystr为密钥对decryptstr字符串进行解密,返回解密后的字符串。解密使用的keystr必须保证与加密时使用的keystr一致才能正常解密。 gs_encrypt_aes128(encryptstr, keystr) 以keystr为密钥对encryptstr字符串进行加密,返回加密后的字符串。keystr的长度范围为1~16字节。 gs_decrypt_aes128(decryptstr, keystr) 以keystr为密钥对decryptstr字符串进行解密,返回解密后的字符串。解密使用的keystr必须保证与加密时使用的keystr一致才能正常解密。keystr不得为空。 有关函数的更多内容,请参见使用函数加解密。
  • 场景介绍 时序表继承普通表的行存和列存语法,降低了用户学习成本,易理解和使用。 时序表具备数据生命周期管理的能力,每天各种维度的数据爆炸式增长,需要定期给表增加新的分区,避免新数据无法存储。而对于很久之前的数据,其价值较低且不经常访问,可以定期删除无用的数据。因此时序表需要具备定时增加分区和定时删除分区的能力。 本实践主要讲解如何快速创建适合自己业务的时序表,并对时序表进行分区管理,从而真正发挥时序表的优势。将对应的列指定为合适的类型,能够帮助用户更好的提高导入、查询等场景的性能,让业务场景运行的更加高效。如下图所示,以发电机组数据采样为例: 图1 发电机组数据采样示意图
  • 创建时序表(手动设置分区边界) 手动指定分区边界的起始值。例如,手动设置默认的分区边界时间P1为“2022-05-30 16:32:45”、P2为“2022-05-31 16:56:12”,创建时序表GENERATOR1: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE IF NOT EXISTS GENERATOR1( genset text TSTag, manufacturer text TSTag, model text TSTag, location text TSTag, ID bigint TSTag, voltage numeric TSField, power bigint TSField, frequency numeric TSField, angle numeric TSField, time timestamptz TSTime) with (orientation=TIMESERIES, period='1 day') distribute by hash(model) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2022-05-30 16:32:45'), PARTITION P2 VALUES LESS THAN('2022-05-31 16:56:12') ); 查询当前时间: 1 2 3 4 select now(); now ------------------------------- 2022-05-31 20:36:09.700096+08(1 row) 查询分区以及分区边界: 1 2 3 4 5 6 7 8 SELECT relname, boundaries FROM pg_partition where parentid=(SELECT oid FROM pg_class where relname='generator1') order by boundaries ; relname | boundaries -------------+---------------------------- p1 | {"2022-05-30 16:32:45+08"} p2 | {"2022-05-31 16:56:12+08"} p1654073772 | {"2022-06-01 16:56:12+08"} p1654160172 | {"2022-06-02 16:56:12+08"} ......
  • 分区自动管理 分区管理功能是和表级参数period、ttl绑定的,只要成功设置了表级参数period,即开启了自动创建新分区功能;成功设置了表级参数ttl,即开启了自动删除过期分区功能。第一次自动创建分区或删除分区的时间为设置period或ttl后30秒。 有如下两种开启分区管理功能的方式: 建表时指定period、ttl。 该方式适用于新建分区管理表时使用。新建分区管理表有两种语法:一种是建表时指定分区,另一种是建表时不指定分区。 建分区管理表时如果指定分区,则语法规则和建普通分区表相同,唯一的区别就是会指定表级参数period、ttl。 示例:创建分区管理表CPU1,指定分区。 1 2 3 4 5 6 7 8 9 10 CREATE TABLE CPU1( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') ); 建分区管理表时可以只指定分区键不指定分区,此时将创建两个默认分区,这两个默认分区的分区时间范围均为period。其中,第一个默认分区的边界时间是大于当前时间的第一个整时/整天/整周/整月/整年的时间,具体选择哪种整点时间取决于period的最大单位;第二个默认分区的边界时间是第一个分区边界时间加period。假设当前时间是2023-02-17 16:32:45,各种情况的第一个默认分区的分区边界选择如下表: 表2 period参数说明 period period最大单位 第一个默认分区的分区边界 1hour Hour 2023-02-17 17:00:00 1day Day 2023-02-18 00:00:00 1month Month 2023-03-01 00:00:00 13months Year 2024-01-01 00:00:00 创建分区管理表CPU2,不指定分区: 1 2 3 4 5 6 CREATE TABLE CPU2( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time); 使用ALTER TABLE RESET的方式设置period、ttl。 该方式适用于给一张满足分区管理约束的普通分区表增加分区管理功能。 创建普通分区表CPU3: 1 2 3 4 5 6 7 8 9 10 CREATE TABLE CPU3( id integer, IP text, time timestamp ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-14 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:56:12') ); 同时开启自动创建和自动删除分区功能: 1 ALTER TABLE CPU3 SET (PERIOD='1 day',TTL='7 days'); 只开启自动创建分区功能: 1 ALTER TABLE CPU3 SET (PERIOD='1 day'); 只开启自动删除分区功能,如果没有提前开启自动创建分区功能,则开启失败: 1 ALTER TABLE CPU3 SET (TTL='7 days'); 通过修改period和ttl修改分区管理功能: 1 ALTER TABLE CPU3 SET (TTL='10 days',PERIOD='2 days'); 关闭分区管理功能。 使用ALTER TABLE RESET语句可以删除表级参数period、ttl,即可关闭相应的分区管理功能。 不能在存在ttl的情况下,单独删除period。 时序表不支持ALTER TABLE RESET。 同时关闭自动创建和自动删除分区功能: 1 ALTER TABLE CPU1 RESET (PERIOD,TTL); 只关闭自动删除分区功能: 1 ALTER TABLE CPU3 RESET (TTL); 只关闭自动创建分区功能,如果该表有ttl参数,则关闭失败: 1 ALTER TABLE CPU3 RESET (PERIOD);
  • 约束限制 在使用分区管理功能时,需要满足如下约束: 不支持在小型机、加速集群、单机集群上使用。 支持在8.1.3及以上集群版本中使用。 仅支持行存范围分区表、列存范围分区表、时序表以及冷热表。 分区键唯一且类型仅支持timestamp、timestamptz、date类型。 不支持存在maxvalue分区。 (nowTime - boundaryTime) / period需要小于分区个数上限,其中nowTime为当前时间,boundaryTime为现有分区中最早的分区边界时间。 period、ttl取值范围为1hour ~ 100years。另外,在兼容Teradata或MySQL的数据库中,分区键类型为date时,period不能小于1day。 表级参数ttl不支持单独存在,必须要提前或同时设置period,并且要大于或等于period。 集群在线扩容期间,自动增加分区会失败,但是由于每次增分区时,都预留了足够的分区,所以不影响使用。
  • 使用方法 在管理控制台上创建集群,具体操作步骤请参考创建集群。 打开GUC参数view_independent参数。 视图解耦功能由GUC参数view_independent进行控制,默认关闭。使用时需要用户手动打开,可登录管理控制台后,单击集群名称,进入“集群详情”页面,单击“参数修改”页签,并在“参数列表”模块搜索view_independent参数,修改后保存。 使用DAS连接集群。在集群列表中找到所需要的集群,单击“操作”栏中的“登录”按钮,跳转至数据库管理服务(DAS)页面,填写登录用户名、数据库名称、密码信息后测试连接,测试无误后登录集群。更多详细步骤请参考使用DAS连接集群。 创建示例表t1并插入数据。 1 2 3 SET current_schema='public'; CREATE TABLE t1 (a int, b int, c char(10)) DISTRIBUTE BY HASH (a); INSERT INTO t1 VALUES(1,1,'a'),(2,2,'b'); 创建视图v1依赖表t1,创建视图v11依赖视图v1。查询视图v11。 1 2 3 4 5 6 7 8 9 CREATE VIEW v1 AS SELECT a, b FROM t1; CREATE VIEW v11 AS SELECT a FROM v1; SELECT * FROM v11; a --- 1 2 (2 rows) 删除表t1后,查询视图v11会因表t1不存在而报错,但视图是依旧存在的。 GaussDB(DWS)提供GS_VIEW_INVALID视图查询当前用户可见的所有不可用的视图。如果该视图依赖的基础表或函数或同义词存在异常,该视图validtype列显示为“invalid”。 1 2 3 4 5 6 7 8 9 10 11 DROP TABLE t1; SELECT * FROM v11; ERROR: relation "public.t1" does not exist SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+-----------------------------+----------- 213563 | public | v1 | dbadmin | SELECT a, b FROM public.t1; | invalid 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (2 rows) 8.3.0之前的历史版本集群,重建表t1后,视图自动重建。视图只有使用才能自动刷新。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 CREATE TABLE t1 (a int, b int, c char(10)) DISTRIBUTE BY HASH (a); INSERT INTO t1 VALUES(1,1,'a'),(2,2,'b'); SELECT * from v1; a | b ---+--- 1 | 1 2 | 2 (2 rows) SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+--------------------------+----------- 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (1 row) SELECT * from v11; a --- 1 2 (2 rows) SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype -----+------------+----------+-----------+------------+----------- (0 rows) 8.3.0及以上版本集群,重建表t1后,视图不会自动重建,执行ALTER VIEW REBUILD操作后视图才能自动刷新。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 CREATE TABLE t1 (a int, b int, c char(10)) DISTRIBUTE BY HASH (a); INSERT INTO t1 VALUES(1,1,'a'),(2,2,'b'); SELECT * from v1; a | b ---+--- 1 | 1 2 | 2 (2 rows) SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+-----------------------------+----------- 213563 | public | v1 | dbadmin | SELECT a, b FROM public.t1; | invalid 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (1 row) ALTER VIEW ONLY v1 REBUILD; SELECT * FROM gs_view_invalid; oid | schemaname | viewname | viewowner | definition | validtype --------+------------+----------+-----------+--------------------------+----------- 213567 | public | v11 | dbadmin | SELECT a FROM public.v1; | invalid (1 rows)
  • 场景介绍 GaussDB(DWS)使用对象标识符(oid)来保存对象之间的引用关系,这使得视图在定义的时候就绑定了其依赖的数据库对象的oid,不管视图名称怎么改变,都不会改变这层依赖关系。如果要对基表进行一些字段修改,会因为与视图字段存在强绑定而报错。如果要删除某个表字段或整个表,就需要连同其关联的视图一起使用cascade关键字删除,表字段删除完成或表重建后再依次重建各级视图,给用户的使用增加了很大的工作量,导致易用性较差。 为了解决这一问题,GaussDB(DWS)在8.1.0集群版本实现了视图的解耦,使得存在视图依赖的基表或其他数据库对象(视图、同义词、函数、表字段)可以单独删除,而其对应对象上关联的依赖视图依然存在,而在基表重建后,可以通过ALTER VIEW REBUILD命令重建依赖关系。而8.1.1集群版本在此基础上又实现了自动重建,可以无感知自动重建依赖关系,开启自动重建后会有锁冲突,因此不建议用户开启自动重建。