草庐IT

优化数仓业务视图：过滤条件传递

华为云开发者社区 2023-03-28 原文
摘要：在业务功能实现时，经常会用到视图简化查询SQL。但有时候会因为视图降低查询效率，本文主要分析在业务需求满足的情况下，将有效的过滤条件传递到基表，减少运算过程中数据库需要处理的数据量，提升SQL执行效率。

本文分享自华为云社区《GaussDB(DWS)业务视图优化-过滤条件传递》，作者：卫小毛 。

在业务功能实现时，经常会用到视图简化查询SQL。但有时候会因为视图降低查询效率，本文主要分析在业务需求满足的情况下，将有效的过滤条件传递到基表，减少运算过程中数据库需要处理的数据量，提升SQL执行效率。

SQL举例

SELECT
    count(1) AS have_done_num,
    t1.task_def_key_ AS menuguid
FROM
    vw_pay_voucher_bill t2
LEFT JOIN xact_hi_taskinst t1 ON t1.business_key_ = t2.id
AND t1.proc_def_key_ = 'pay_voucher_bill'
AND t1.operation_flag_ IN ('NORMAL', 'WITHDRAW')
AND t1.suspension_state_ = 1
AND t1.org_code_ = t2.mof_div_code
AND delete_reason_ = 'completed'
AND ext1_ IS NULL
WHERE
    t2.is_deleted = '2'
AND t2.fiscal_year = '2022'
AND t2.mof_div_code = 'xxxxxxxx0'
AND (
    agency_id = '5A1xxxxxxxxxxxxxxxxxxx4T5'
)
GROUP BY
    t1.task_def_key_
HAVING
    t1.task_def_key_ IS NOT NULL;

sql 分析：以上SQL vw_pay_voucher_bill t2 、xact_hi_taskinst t1 视图和表进行关联查询

根据业务特性分析过滤效果较好的字段为 agency_id

优化前耗时： 22s

分析执行计划：

时间主要耗时在 seq scan on pay_voucher_bill v 这一步

看到该表过滤条件仅有mof_div_code、fiscal_year、is_deleted 过滤效果差，几乎全表数据参与过程运算，执行代价高

视图及表结构分析：

视图中关联条件较为有效的过滤条件，bgt_id 字段查询时不会应用。分析视图中“v”和“t”表都存在agency_id 字段，当前t表过滤使用了agency_id字段，可以考虑视图定义中量表关联条件增加 agency_id 字段关联条件需要考虑业务需求。

同业务沟通后可进行优化

优化后耗时：0.4s

对比优化前后SQL查询结果一致

优化总结：

同业务侧研发沟通客户实际需要仅需要查询本单位 （agency_id） 下的数据，但因为SQL和视图设计时，并未将这一有效条件传递给每张表。导致数据库在针对 pay_voucher 进行数据过滤时需要将全表64万+ 数据筛选出来进行运算，仅仅这一步开销就占用了20s+。在优化后（视图中增加agency_id关联信息后，该操作可将agency_id 过滤条件传递给基表 pay_voucher），仅需从pay_voucher 表中获取738行数据进行运算，最终sql耗时降为 0.4s左右。

 

点击关注，第一时间了解华为云新鲜技术~

传递优化spancolorstyleSQL

有关优化数仓业务视图：过滤条件传递的更多相关文章

  1. ruby - 如何根据特征实现 FactoryGirl 的条件行为 - 2

    我有一个用户工厂。我希望默认情况下确认用户。但是鉴于unconfirmed特征，我不希望它们被确认。虽然我有一个基于实现细节而不是抽象的工作实现，但我想知道如何正确地做到这一点。factory:userdoafter(:create)do|user,evaluator|#unwantedimplementationdetailshereunlessFactoryGirl.factories[:user].defined_traits.map(&:name).include?(:unconfirmed)user.confirm!endendtrait:unconfirmeddoenden

  2. ruby - 在 Ruby 中有条件地定义函数 - 2

    我有一些代码在几个不同的位置之一运行:作为具有调试输出的命令行工具，作为不接受任何输出的更大程序的一部分，以及在Rails环境中。有时我需要根据代码的位置对代码进行细微的更改，我意识到以下样式似乎可行:print"Testingnestedfunctionsdefined\n"CLI=trueifCLIdeftest_printprint"CommandLineVersion\n"endelsedeftest_printprint"ReleaseVersion\n"endendtest_print()这导致:TestingnestedfunctionsdefinedCommandLin

  3. ruby - 定义方法参数的条件 - 2

    我有一个只接受一个参数的方法:defmy_method(number)end如果使用number调用方法，我该如何引发错误？？通常，我如何定义方法参数的条件？比如我想在调用的时候报错:my_method(1) 最佳答案 您可以添加guard在函数的开头，如果参数无效则引发异常。例如:defmy_method(number)failArgumentError,"Inputshouldbegreaterthanorequalto2"ifnumbereputse.messageend#=>Inputshouldbegreaterthano

  4. ruby - rails 3 redirect_to 将参数传递给命名路由 - 2

    我没有找到太多关于如何执行此操作的信息，尽管有很多关于如何使用像这样的redirect_to将参数传递给重定向的建议:action=>'something',:controller=>'something'在我的应用程序中，我在路由文件中有以下内容match'profile'=>'User#show'我的表演Action是这样的defshow@user=User.find(params[:user])@title=@user.first_nameend重定向发生在同一个用户Controller中，就像这样defregister@title="Registration"@user=Use

  5. ruby-on-rails - 如何生成传递一些自定义参数的 `link_to` URL？ - 2

    我正在使用RubyonRails3.0.9，我想生成一个传递一些自定义参数的link_toURL。也就是说，有一个articles_path(www.my_web_site_name.com/articles)我想生成如下内容:link_to'Samplelinktitle',...#HereIshouldimplementthecode#=>'http://www.my_web_site_name.com/articles?param1=value1¶m2=value2&...我如何编写link_to语句“alàRubyonRailsWay”以实现该目的？如果我想通过传递一些

  6. ruby-on-rails - 事件管理员日期过滤器日期格式自定义 - 2

    是否有简单的方法来更改默认ISO格式(yyyy-mm-dd)的ActiveAdmin日期过滤器显示格式？ 最佳答案 您可以像这样为日期选择器提供额外的选项，而不是覆盖js:=f.input:my_date,as::datepicker,datepicker_options:{dateFormat:"mm/dd/yy"} 关于ruby-on-rails-事件管理员日期过滤器日期格式自定义，我们在StackOverflow上找到一个类似的问题： https://s

  7. ruby - 在 Ruby 中按名称传递函数 - 2

    如何在Ruby中按名称传递函数？(我使用Ruby才几个小时，所以我还在想办法。)nums=[1,2,3,4]#Thisworks,butismoreverbosethanI'dlikenums.eachdo|i|putsiend#InJS,Icouldjustdosomethinglike:#nums.forEach(console.log)#InF#,itwouldbesomethinglike:#List.iternums(printf"%A")#InRuby,IwishIcoulddosomethinglike:nums.eachputs在Ruby中能不能做到类似的简洁？我可以只

  8. ruby-on-rails - 在 Controller 中干净地处理多个过滤器(参数) - 2

    我有一个名为Post的类，我需要能够适应以下场景:如果用户选择了一个类别，则只显示该类别的帖子如果用户选择了一种类型，则只显示该类型的帖子如果用户选择了一个类别和类型，则只显示该类别中该类型的帖子如果用户没有选择任何内容，则显示所有帖子我想知道我的Controller是否不可避免地会因大量条件语句而显得粗糙...这是我解决此问题的错误方法-有谁知道我如何才能做到这一点？classPostsController 最佳答案 您最好遵循“胖模型，瘦Controller”的惯例，这意味着您应该将这种逻辑放在模型本身中。Post类应该能够报告

  9. ruby-on-rails - 如何处理 Grape 中特定操作的过滤器之前？ - 2

    我正在我的Rails项目中安装Grape以构建RESTfulAPI。现在一些端点的操作需要身份验证，而另一些则不需要身份验证。例如，我有users端点，看起来像这样:moduleBackendmoduleV1classUsers现在如您所见，除了password/forget之外的所有操作都需要用户登录/验证。创建一个新的端点也没有意义，比如passwords并且只是删除password/forget从逻辑上讲，这个端点应该与用户资源。问题是Grapebefore过滤器没有像except,only这样的选项，我可以在其中说对某些操作应用过滤器。您通常如何干净利落地处理这种情况？

  10. ruby-on-rails - 使用包含多个关联和单独的条件 - 2

    我的Gallery模型中有以下查询:media_items.includes(:photo,:video).rank(:position_in_gallery)我的图库模型有_许多媒体项，每个都有一个照片或视频关联。到目前为止，一切正常。它返回所有media_items包括它们的photo或video关联，由media_item的position_in_gallery属性排序。但是我现在需要将此查询返回的照片限制为仅具有is_processing属性的照片，即nil。是否可以进行相同的查询，但条件是返回的照片等同于:.where(photo:'photo.is_processingIS

随机推荐