开发者社区> jeanron100> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

生产环境sql语句调优实战第十篇

简介: 陆陆续续写了九篇关于生产环境sql语句的调优案例,发现了不少问题,可能有些问题回头来看是比较低级的错误,稍加改动就能够运行在秒级,有些可能是在秒级到毫秒级的小步提升等等,不管调优的改进多大,从dba的角度来看,好多问题都是基于资源来调优的,比如添加索引,降低IO,降低CPU消耗,提高CPU利用率等等。
+关注继续查看
陆陆续续写了九篇关于生产环境sql语句的调优案例,发现了不少问题,可能有些问题回头来看是比较低级的错误,稍加改动就能够运行在秒级,有些可能是在秒级到毫秒级的小步提升等等,不管调优的改进多大,从dba的角度来看,好多问题都是基于资源来调优的,比如添加索引,降低IO,降低CPU消耗,提高CPU利用率等等。如果有时候从业务角度来下下功夫,可能某种程度上效果要更好于基于资源/代价的调优。
最近客户反馈有几条sql语句IO消耗很高,希望我们能够给提点建议。
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表走了全表扫描。

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 5,304,719 9,558.05 0.88
CPU Time (ms) 1,806,243 3,254.49 0.87
Executions 555    
Buffer Gets 158,171,280 284,993.30 0.95
Disk Reads 158,091,403 284,849.37 4.60
Parse Calls 555 1.00 0.00
Rows 5,612 10.11  
User I/O Wait Time (ms) 3,351,159    
Cluster Wait Time (ms) 0    
Application Wait Time (ms) 4,142    
Concurrency Wait Time (ms) 0    
Invalidations 0    
Version Count 35    
Sharable Mem(KB) 818    
    
执行计划如下:
Plan hash value: 2174296646
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |   126 |  4284 | 50533   (1)| 00:10:07 |
|   1 |  NESTED LOOPS                |                      |       |       |            |          |
|   2 |   NESTED LOOPS               |                      |   126 |  4284 | 50533   (1)| 00:10:07 |
|*  3 |    TABLE ACCESS FULL         | SUBSCRIBER           |    18 |   342 | 50517   (1)| 00:10:07 |
|*  4 |    INDEX RANGE SCAN          | SERVICE_DETAILS_PK   |     9 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| SERVICE_DETAILS      |     7 |   105 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
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')

如果从资源代价的角度来看,一种思路是添加对应的索引。因为这个表是产品线中统一规划的。所以要加入索引还是很不容易的。
如果没有其它的调优思路,可能并行就是一把双刃剑了,相对来说速度会高一些,但是IO和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' 

先来看看执行计划,表面来看所走的索引还是比较高效的。
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |     7 |   196 |     7  (15)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                            |                         |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                           |                         |     7 |   196 |     7  (15)| 00:00:01 |       |       |
|   3 |    VIEW                                  | VW_NSO_1                |     1 |    13 |     5   (0)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE                          |                         |     1 |    36 |            |          |       |       |
|   5 |      NESTED LOOPS                        |                         |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                       |                         |     1 |    36 |     5   (0)| 00:00:01 |       |       |
|   7 |        PARTITION RANGE ALL               |                         |     1 |    25 |     4   (0)| 00:00:01 |     1 |    11 |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| SUBSCRIBER_RESOURCE     |     1 |    25 |     4   (0)| 00:00:01 |     1 |    11 |
|*  9 |          INDEX RANGE SCAN                | SUBSCRIBER_RESOURCE_1IX |     1 |       |     3   (0)| 00:00:01 |     1 |    11 |
|* 10 |        INDEX UNIQUE SCAN                 | SUBSCRIBER_PK           |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |       TABLE ACCESS BY INDEX ROWID        | SUBSCRIBER              |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                      | SERVICE_DETAILS_PK      |     9 |       |     1   (0)| 00:00:01 |       |       |
|* 13 |   TABLE ACCESS BY INDEX ROWID            | SERVICE_DEAILS          |     7 |   105 |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   9 - access("RESOURCE_VALUE"=:1)
  10 - access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
  11 - filter("SUB_STATUS"='A' AND "PRIM_RESOURCE_TP"="RESOURCE_TYPE")
  12 - access("SUBSCRIBER_NO"="SUBSCRIBER_NO")
  13 - filter("PRODUCT_STATUS"='A')  

最后在备份库做相关的测试,执行时间都在毫秒级。
所以有些时候业务的角度来调优可能会有意向不到的收获。还有几个类似的语句,执行时间在分钟级,调整成类似的形式之后,都在毫秒级就完成了数据查询。






版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
835 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1486 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1667 0
实验一:SQL server 2005高可用性之----日志传送
如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/10/31/4751070.aspx           SQL server 2005高可用性之日志传送是在SQL server 2000 日志传送基础之上的延续,两者其本质上并没有太大的差异。
1002 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MaxCompute SQL计算成本调优以及优化方法
立即下载
Blink SQL关键技术及实现原理
立即下载
SQL Sever迁移PG经验
立即下载