GaussDB(DWS)数据仓库性能压测与调优实战：从0到1全记录

创建DWS集群登录华为云控制台 “数据仓库服务GaussDB(DWS)”点击“创建数据仓库集群”。关键配置参数分享参数 推荐值 说明计费模式 按需计费 演练完就释放省钱区域 华北-北京四 选离你最近的产品类型 云数仓 标准版够用节点规格 dwsx2.xlarge 最小规格即可热数据存储 20G 测试够用节点数量 3 分布式体验网络配置虚拟私有云新建一个VPC如vpc-dws子网选对应子网安全组自动创建公网访问现在购买带宽选1Mbit/s管理员密码设置一个密码大小写字母数字特殊字符一定要记住后面连数据库要用连接DWS数据库gsql-d gaussdb-h公网IP-U dbadmin-p8000-r-W你的密码成功连接后会看到postgres提示符构造测试数据创建测试表-- 创建订单表模拟业务场景CREATETABLEorders(order_idBIGINTPRIMARYKEY,user_idBIGINT,product_idBIGINT,order_amountDECIMAL(10,2),order_statusVARCHAR(20),created_atTIMESTAMP,updated_atTIMESTAMP)DISTRIBUTEBYHASH(order_id);-- 创建用户表CREATETABLEusers(user_idBIGINTPRIMARYKEY,user_nameVARCHAR(100),user_phoneVARCHAR(20),user_statusVARCHAR(20),register_timeTIMESTAMP)DISTRIBUTEBYHASH(user_id);DISTRIBUTE BY HASH是指定分布键数据会按这个字段的哈希值分散到不同节点。选错分布键会导致数据倾斜影响性能。插入测试数据-- 插入100万用户INSERTINTOusersSELECTgenerate_series(1,1000000),user_||generate_series(1,1000000),1||lpad(generate_series(1,1000000)::text,10,0),CASEWHENrandom()0.2THENactiveELSEblockedEND,now()-(random()*interval365 days);-- 插入1000万订单分批插入避免事务过大INSERTINTOordersSELECTgenerate_series(1,10000000),(random()*9999991)::BIGINT,(random()*99991)::BIGINT,(random()*10001)::DECIMAL(10,2),CASEWHENrandom()0.9THENpaidWHENrandom()0.7THENshippedWHENrandom()0.5THENpending_paymentELSEcompletedEND,now()-(random()*interval180 days),now()-(random()*interval30 days);插入1000万条数据可能需要几分钟到十几分钟收集统计信息这是最容易忽略但最重要的一步——没有统计信息优化器就不知道数据分布生成的执行计划可能很烂。经验表明10%左右的性能问题就是因为没收集统计信息。ANALYZEorders;ANALYZEusers;生成WDR性能报告WDRWorkload Diagnosis Report是DWS自带的性能诊断工具类似Oracle的AWR。它会定期采集数据库性能快照然后生成两份快照之间的性能对比报告。开启WDR快照-- 开启WDR功能默认开启SETenable_wdr_snapshoton;-- 手动创建快照管理员权限SELECTcreate_wdr_snapshot();查看已有快照SELECT*FROMdbms_om.snapshotORDERBYsnapshot_idDESCLIMIT5;生成WDR报告WDR报告包含的核心内容Top SQL by Elapsed Time最耗时的SQL排名等待事件分析数据库在等什么资源磁盘I/O、锁等慢SQL定位与分析直接查TOP慢查询SELECTquery_id,substring(query,1,100)ASquery_preview,calls,total_time/callsASavg_ms,rowsFROMdbe_perf.statement_historyWHEREstart_timenow()-interval1 hourORDERBYtotal_timeDESCLIMIT10;使用EXPLAIN分析执行计划-- 查看执行计划并实际执行推荐能看到真实耗时EXPLAINANALYZESELECT*FROMordersWHEREuser_id12345;实例对比-- 建表并插入数据CREATETABLEstore_sales_row(ss_sold_date_skINT,ss_item_skINT,ss_quantityINT);INSERTINTOstore_sales_rowSELECTgenerate_series(1,5000000),generate_series(1,5000000),1;-- 不加索引全表扫描3.6秒EXPLAINANALYZESELECT*FROMstore_sales_rowWHEREss_sold_date_sk2450944;-- 创建索引CREATEINDEXidx_sales_dateONstore_sales_row(ss_sold_date_sk);-- 加索引后走Index Scan13毫秒EXPLAINANALYZESELECT*FROMstore_sales_rowWHEREss_sold_date_sk2450944;SQL调优实战案例1索引优化——WHERE条件没走索引现象查询按某字段过滤但执行计划是Seq Scan全表扫描CREATEINDEXidx_orders_user_idONorders(user_id);CREATEINDEXidx_orders_created_atONorders(created_at);-- 复合索引多个条件同时过滤CREATEINDEXidx_orders_user_status_createdONorders(user_id,order_status,created_atDESC);-- 再次分析ANALYZEorders;提醒索引不是越多越好每个索引都会占用存储空间且写入时都要维护。一般建议在WHERE条件、JOIN关联列、ORDER BY排序列上建索引。案例2SQL改写——用EXISTS代替IN-- 慢子查询返回大量数据SELECT*FROMordersWHEREuser_idIN(SELECTuser_idFROMusersWHEREuser_statusblocked);-- 快EXISTS可以提前终止适合子表很大的情况SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.user_ido.user_idANDu.user_statusblocked);案例3避免WHERE条件中使用函数-- 慢对created_at做DATE函数运算无法走索引SELECT*FROMordersWHEREDATE(created_at)2025-06-28;-- 快用范围查询可以走索引SELECT*FROMordersWHEREcreated_at2025-06-28 00:00:00ANDcreated_at2025-06-29 00:00:00;**GUC参数调优内存相关参数-- work_mem排序和Hash操作的内存默认512MB-- 复杂查询5-10个关联建议50%内存/10-- 并发场景串行值/并发数SETwork_mem2GB;-- maintenance_work_mem维护操作VACUUM、CREATE INDEX内存-- 建议不小于work_memSETmaintenance_work_mem2GB;-- shared_buffers共享内存建议内存的40%以内-- 行存表设大列存表设小SETshared_buffers8GB;并发控制参数-- max_active_statements全局并发队列-- 分析类查询设为CPU核数/DN个数一般是4-8SETmax_active_statements8;-- enable_dynamic_workload开启动态负载管理默认打开SETenable_dynamic_workloadon;查询优化开关-- 关闭排序归并强制走HashJoinSETenable_mergejoinoff;-- 关闭NestLoop避免小表驱动大表的低效关联SETenable_nestloopoff;-- 开启并行执行SMPSETquery_dop4;-- 并行度根据CPU核数设置问题 原因 解决方案1 查询总是跑得很慢 没收集统计信息 执行ANALYZE 表名;2 索引建了但不走 数据分布不均匀优化器判断走全表更快 更新统计信息ANALYZE或临时SET enable_seqscanoff;3 数据倾斜导致某节点特别慢 分布键选得不合适 用SELECT table_skewness(‘表名’);检查重新选分布键4 WDR报告生成失败 enable_wdr_snapshot没开 SET enable_wdr_snapshoton;5 大表JOIN特别慢 关联列不是分布键大量数据重分布 改写成先做INNER JOIN再LEFT JOIN的模式6 索引推荐给的方案不合理 推荐合并了不该合并的索引