sql server数据一对多合并_数据库SQL优化技巧多，结合业务优化是王道

最新推荐文章于 2024-03-29 21:22:47 发布

weixin_39866867

最新推荐文章于 2024-03-29 21:22:47 发布

阅读量153

点赞数

文章标签： sql server数据一对多合并 sql 一对多获得一条数据

对于很多DBA而言，SQL的优化可以从系统层面、数据库层面、SQL语句层面进行改进。但是这些都是技术层面的改进，在具体的业务场景中，联系业务来优化也是一种非常值得推崇的方法。有一种说法是调需重于一切，具体问题具体对待，联系业务使得问题处理更有针对性，这样技术落地更有意义。

　　总结了不少的调优案例，也发现了不少问题，有些SQL问题回头来看是比较低级的错误，稍加改动就能够运行在秒级，有些甚至是秒级到毫秒级的大幅提升等；不管调优的改进多大，从DBA的角度来看，很多问题都是基于资源来调优的，比如添加索引、降低I/O、降低CPU消耗以及提高CPU利用率等。如果有时候从业务角度来下功夫，可能某种程度上效果要更好于基于资源/代价的调优。

　　最近客户反馈有几条SQL语句的I/O消耗很高，希望我们能够提点建议。

　　SQL语句很短，运行时间在9秒左右，运行频率也是挺高的，平均下来一个小时100次。

　　语句如下：

　　select product_name from service_details where subscriber_no in (select subscriber_no from subscriber where prim_resource_val = :1 and sub_status = 'A') and product_status = 'A' ;

　　其中service_details是一个亿级的大表，subscriber是百万级的表，但是prim_resource_val字段不是索引列，所以导致subscriber表走了全表扫描，见下表：

　　执行计划如下：

　　Predicate Information (identified by operation id):

　　---------------------------------------------------

　　3 - filter(''PRIM_RESOURCE_VAL''=:1 AND ''SUB_STATUS''='A')

　　4 - access(''SUBSCRIBER_NO''=''SUBSCRIBER_NO'')

　　5 - filter(''PRODUCT_STATUS''='A')

　　如果从资源/代价的角度来看，思路便是添加对应的索引。但这个表是产品线中统一规划的，要加入索引还是很不容易的。

　　如果没有其他的调优思路，并行也可以，但是它是一把双刃剑，相对来说速度会高一些，但是I/O和CPU的消耗会比较高，对于执行如此频繁的语句来说，使用多个并行对于系统负载是很高的。

　　看着SQL语句比较简单，但是无从下手也有些让人着急。数据库的角度所做的一些调整可能奏效不大，于是就想看看从业务角度能做点什么。

　　查看SQL语句。

　　select product_name from service_details where subscriber_no in (select subscriber_ no from subscriber where prim_resource_val = :1 and sub_status = 'A') and product_status = 'A' ;

　　SQL语句中prim_resource_val与我们使用的手机号码有些类似，这样一个号码为什么没有加入索引，从业务的角度来考虑，可能是有做号码变更之类操作时这个号码列变化比较频繁。而保持不变的就是subscriber_no。类似我们去银行办理业务时显示的客户号，这个字段就是主键列。

　　可能有的人有多个资源号，比如有机顶盒号、手机号等，在这个时候手机号就是主要的资源号。

　　下面来分析为什么产品线中没有规划给resource_value作为索引列，也是考虑了后期的一些变更。这个列变化还是比较频繁，这样考虑也就有一定的道理。

　　因为对这部分的业务比较熟悉，所以发现所需要的资源号，完全可以从一个独立的表subscriber_resource中得到更完整的信息。

　　这个表也是亿级的表，但是根据资源号来查找subscriber可以走index range scan，得到数据也要快很多。

　　subscriber_resource中存放着用户所使用的各类资源信息，从这个表里直接映射resource_value得到的用户信息很有限。因为根据条件只需要激活状态的用户，那么我们完全可以在得到一个很简单的用户列表后直接过滤用户状态，这样就能得到所需要的信息。最后做了如下的改动，把资源表关联进来。

　　select product_name

　　from service_details ser

　　where subscriber_no in (select subscriber_no

　　from subscriber

　　where sub_status = 'A'

　　and (subscriber_no, prim_resource_tp) in (select subscriber_no, resource_type from subcriber_resource where resource_value=:1)

　　)

　　and soc_status = 'A'

　　查看如下执行计划，执行的索引还是比较高效的。