数仓实践丨表扫描时过滤行数过多引起的性能瓶颈问题
1.数仓实践丨主动预防-DWS关键工具安装确认2.一条SQL如何被MySQL架构中的各个组件操作执行的?3.GaussDB(DWS)网络流控与管控效果4.GaussDB(DWS)字符串处理函数返回错误结果集排查5.从缓存的本质说起,说服技术大佬用Redis6.这年头怕数据泄露?全密态数据库:无所谓,我会出手7.华为云新一代分布式数据库GaussDB,给世界一个更优选择8.GaussDB技术解读丨高级压缩9.掌数科技携手华为云GaussDB,助力金融科技创新,联合打造行业标杆10.一文带你全面了解openGemini11.GaussDB(for Redis)多租户:读写权限控制和数据库隔离的完美融合12.5分钟迁移关系型数据库到图数据库13.数仓现网案例丨超大结果集接收异常14.DWS轻量化更新黑科技:宽表加工优化15.数据库行业需要什么样的人才?高校老师这样说16.数仓性能优化:倾斜优化-表达式计算倾斜的hint优化17.GaussDB技术解读系列之SQL Audit,面向应用开发的SQL审核工具18.带你认识数仓的监控系统TopSQL19.带你走进数仓大集群内幕丨详解关于作业hang及残留问题定位20.实时入库不用愁,HStore帮分忧21.openGauss数据库在CentOS上的安装实践22.揭秘华为云GaussDB(for Redis)丨大key治理23.GaussDB(DWS)函数不同写法引发的结果差异24.数仓中典型的几种不下推语句整改案例25.GaussDB技术解读系列之应用无损透明(ALT)26.华为云GaussDB(for Influx)单机版上线,企业降本增效利器来了27.数仓备份经验分享丨详解roach备份原理及问题处理套路28.中国云数据仓库,双第一!29.华为云GaussDB打造最可信的数据库,给世界一个更优选择30.GaussDB技术解读系列:高级压缩之OLTP表压缩31.十年磨一剑的华为云GES,高明在哪32.使用DWS集群,用户被锁定如何解锁33.GaussDB技术解读系列:高安全之密态等值34.GaussDB技术解读:应用无损透明(ALT)35.数仓资源管控理论已掌握,是时候实战了36.row_number函数的不稳定性37.GaussDB技术解读丨数据库迁移创新实践38.聊聊GaussDB AP是如何执行SQL的39.Navicat 携手华为云GaussDB,联合打造便捷高效的数据库开发和建模工具方案40.GaussDB技术解读系列丨运维自动驾驶探索41.一次性全讲透GaussDB(DWS)锁的问题42.GaussDB(DWS)性能调优:Sort+Groupagg聚集引起的性能瓶颈案例43.多主架构:VLDB技术论文《Taurus MM: bringing multi-master to the cloud》解读44.GaussDB(for Redis)游戏实践:玩家下线行为上报45.一文详解数据仓库的物理细粒度备份恢复46.华为云HBase冷热分离最佳实践47.四问复合索引,让你的数据查询速度飞起48.GaussDB(DWS)案例丨MERGE场景下语句不下推引起的性能瓶颈问题49.如何强制SQL走性能更优的hash join50.如何使用GaussDB(DWS)的本地临时表进行数据处理51.华为云GaussDB亮相金融业数据库技术大会52.2个数仓中不等值关联优化案例53.数仓实时场景下表行数估算不准确引起的的性能瓶颈问题案例54.详解GuassDB数据库权限命令:GRANT和REVOKE55.DWS临时内存不可用报错: memory temporarily unavailable56.华为云GaussDB城市沙龙活动走进安徽,助力金融行业数字化转型57.理论+应用,带你了解数据库资源池58.人人用数不用愁,动态数据脱敏为您解忧59.实例讲解数据库的数据去重
60.数仓实践丨表扫描时过滤行数过多引起的性能瓶颈问题
61.实例详解构建数仓中的行列转换62.Proxy下的Prepare透传,让GaussDB(for MySQL)更稳固,性能更卓越63.浅析KV存储之长尾时延解决办法64.实例讲解数据库的定义重载函数65.详解数据库SQL中的三个语句:DROP、TRUNCATE 、DELETE66.华为云GaussDB助力工商银行、华夏银行斩获“十佳卓越实践奖”67.Navicat 基于 GaussDB 主备版的快速入门68.数仓实时算子难以观测,快来试试算子级监控吧69.列举数据库缓存使用场景实例和命令速查表70.带你认识多模数据库GeminiDB架构与应用实践71.3招解决时序数据高基数难题,性能多维度提升!72.数仓性能调优:row_number() over(p)-rn=1性能瓶颈发现和改写套路73.数仓实践丨常量标量子查询做全连接导致整体慢74.细说GaussDB(DWS)的2种查询优化技术75.细说SQL与ETL之间的小秘密76.从概念到实践,带你掌握层次递归查询77.GeminiDB Cassandra接口新特性PITR发布:支持任意时间点恢复78.你的JoinHint为什么不生效79.六步走向无忧,华为云数据库高可用的秘密武器80.数仓调优实践丨SQL改写消除相关子查询81.GaussDB(for MySQL)新特性TDE发布:支持透明数据加密82.详解GaussDB(DWS)通信安全的小妙招:连接认证机制83.GaussDB(for MySQL) RegionlessDB发布:全球数据库技术84.5分钟带您了解DRS录制回放85.ICDM'23 BICE论文解读:基于双向LSTM和集成学习的模型框架86.数仓如何递归查询视图依赖87.支撑核心系统分布式改造,GaussDB为江南农商银行筑稳根基88.近6成金融机构的选择!华为云GaussDB加快金融核心系统转型89.GaussDB(for MySQL)剪枝功能,让查询性能提升70倍!90.2023年度十佳课题公布:华为云GaussDB获权威认可91.详解如何在数仓中搭建细粒度容灾应用92.对话苏光牛:国内数据库市场已进入关键转折点,2024年或是分水岭93.GaussDB通信运维:详解stream连接池设计原理94.GaussDB(for MySQL) Serverless全面商用:无感弹性,极致性价比95.华为云GaussDB支撑农行超级网银业务,性能和稳定性备受认可96.实例详解数据库的游标管理97.数仓实践丨从CU入手优化HStore表98.数仓的等待视图中,为什么会有Hashjoin-nestloop99.如何基于Sharding-JDBC实现GaussDB在客户端应用的读写分离100.如何迅速并识别处理MDL锁阻塞问题本文分享自华为云社区《GaussDB(DWS)性能调优:表扫描时过滤行数过多引起的性能瓶颈问题案例》,作者: O泡果奶~ 。
1、【问题描述】
SQL语句执行过程中,对12亿数据量的大表进行扫描,过滤99%的数据仅留617行数据,性能瓶颈位于扫描该表这里。
2、【原始语句】
set search_path = 'bi_dashboard'; WITH F_SRV_DB_DIM_PRD_D AS (SELECT EXTERNAL_NAME FROM ( SELECT MKT_NAME EXTERNAL_NAME FROM BI_DASHBOARD.DM_MSS_ITEM_PRODUCT_D PRD WHERE PRD.COMPANY_BRAND =any(array[string_to_array('HUAWEI',',')]) AND PRD.MKT_NAME =any(array[string_to_array('畅享 60,畅享 50,畅享 60X,畅享 60 Pro,畅享 50 Pro,畅享 50z,nova 10z,畅享 20e,畅享20 Pro,畅享 10e,畅享10 Plus,畅享20 SE,畅享10,nova 11i,畅享20 Plus,畅享9 Plus,畅享20 5G,nova Y90,畅享 10S,nova Y70,畅享Z,畅享 9S,nova 8 SE 活力版,麦芒9 5G,Y9s,麦芒9 5G',',')]) ) WHERE EXTERNAL_NAME<>'SNULL' GROUP BY EXTERNAL_NAME), V_PERIOD AS ( SELECT PERIOD_ID AS PERIOD_ID_M, LEAST(TO_CHAR(PERIOD_END_DATE, 'YYYYMMDD'), '20230630') AS PERIOD_ID, PERIOD_ID AS DATES FROM BI_DASHBOARD.RPT_TML_ACCOUNT_PERIOD_D WHERE PERIOD_TYPE = 'M' AND PERIOD_ID BETWEEN 202207 AND 202306 ), V_DATA_BASE AS ( SELECT A.PERIOD_ID, IFNULL(A.CHANNEL_NAME, 'SNULL') AS DISTRIBUTOR_CHANNEL_NAME, SUM(A.SO_QTY_MTD) AS SO_QTY, SUM(DECODE(A.PERIOD_ID, 20230630, A.SO_QTY_MTD)) AS SO_QTY_ORDER select count(*) FROM DM_MSS_CN_PC_REP_RP_ST_D_F A INNER JOIN F_SRV_DB_DIM_PRD_D PRD ON A.EXTERNAL_NAME = PRD.EXTERNAL_NAME WHERE 1 = 1 AND A.CHANNEL_ID IN ('100013388802') AND A.ORG_KEY IN (10000651) AND A.SALES_FLAG IN ('1', '0') AND A.PERIOD_ID IN (20220731,20221031,20220930,20220831,20221130,20221231,20230131,20230228,20230430,20230331,20230531,20230630) AND (A.SO_QTY_MTD <> 0) -- 过滤所有日期SO_QTY为0的数据 GROUP BY A.PERIOD_ID, IFNULL(A.CHANNEL_NAME, 'SNULL') ), V_DATA AS ( SELECT PERIOD_ID, NVL(DISTRIBUTOR_CHANNEL_NAME, 'Total') AS DISTRIBUTOR_CHANNEL_NAME, SUM(SO_QTY) AS SO_QTY, SUM(SO_QTY_ORDER) AS SO_QTY_ORDER FROM V_DATA_BASE A GROUP BY GROUPING SETS ((PERIOD_ID), (PERIOD_ID, DISTRIBUTOR_CHANNEL_NAME)) ) SELECT STRING_AGG(P.DATES, ',' ORDER BY P.PERIOD_ID_M) AS PERIOD_LIST, B.DISTRIBUTOR_CHANNEL_NAME, STRING_AGG(NVL(TO_CHAR(ROUND(A.SO_QTY)), '0'), ',' ORDER BY P.PERIOD_ID_M) AS SO_QTY FROM V_PERIOD P FULL JOIN (SELECT DISTINCT DISTRIBUTOR_CHANNEL_NAME FROM V_DATA) B ON 1 = 1 LEFT JOIN V_DATA A ON A.PERIOD_ID = P.PERIOD_ID AND A.DISTRIBUTOR_CHANNEL_NAME = B.DISTRIBUTOR_CHANNEL_NAME GROUP BY B.DISTRIBUTOR_CHANNEL_NAME ORDER BY DECODE(B.DISTRIBUTOR_CHANNEL_NAME, 'Total', 0, 'SOURCE IS NULL', 2, '源为空', 3, 'SNULL', 4, 1), SUM(A.SO_QTY_ORDER) DESC NULLS LAST LIMIT 50 OFFSET 0
3、【性能分析】
从上图的performance执行计划中可以看出(完整执行计划放在附件一),该SQL语句慢在扫描表a(bi_dashboard.dm_mss_cn_pc_rep_rp_st_d_f_test)。扫描时过滤条件包括:sales_flag、so_qty_mtd、channel_id、org_key、period_id,该表上原本的局部聚簇键PCK只包含了period_id,并没有包括其余三个过滤条件之一,因此,可以调整PCK,以减少扫描表a的执行时间。
补充:局部聚簇键
局部聚簇 (Partial Cluster Key, 简称PCK),列存储下一种通过min/max稀疏索引实现基表快速扫描的索引技术。Partial Cluster Key可以指定多列,但是一般不建议超过2列。PCK适用于列存大表点查询加速。另外,查看语句中where条件中in值较多(12个),在DWS中,in后面的条件默认就只能是5个,超过6个就过滤不下推,此时,可以用or将12个值改写,
A.PERIOD_ID IN (20220731,20221031,20220930,20220831,20221130) or A.PERIOD_ID IN (20221231,20230131,20230228,20230430,20230331) or A.PERIOD_ID IN (20230531,20230630)
此时,SQL语句执行时间减少为487ms,完整performance计划如附件二所示。
- 附件:优化后—performance.txt 466.64KB
- 附件:优化前—performance.txt 449.47KB
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-11-08 动手实践丨使用华为云IoT边缘体验“边云协同”
2022-11-08 想会用synchronized锁,先掌握底层核心原理
2022-11-08 OpenHarmony移植案例: build lite源码分析之hb命令__entry__.py
2021-11-08 教你如何在Spark Scala/Java应用中调用Python脚本
2021-11-08 云上远程运维的最后那点担心,“云梯”帮你解决
2021-11-08 基于软件分析的智能化开发新型服务与技术
2021-11-08 Scrum Master们,难道每天都在摸鱼?