备案控制台
探索云世界
开发者社区 数据库 文章 正文

在平衡中追求性能极限【ACHIEVE MAX PERFORMANCE IN BALANCE VIEW】

2017-11-05 784
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
本文旨在通过一次对某个SQL优化的全过程，来说明在追求性能的同时要用更为全局的眼光来看待某些具体的优化操作。虽然BAD SQL是造成数据库性能低下的“元凶”之一，但是对于要处理成千上万个SQL的数据库来讲，“大数据库”观念还是非常必要的。
    同时本文还综合介绍了优化SQL的一些思想和ORACLE提供的一些实用优化功能。
    1.背景说明：
    数据库版本：ORACLE 10.2.0.1
    操作系统版本：HP-UX 11.23 IA64
    在数据库例行的检查过程中，发现一个SQL语句进行了大量的全表扫描！其中DISKREAD过十亿次。赶快找到这句SQL：
  
SQL> SELECT COUNT(DISTINCT(TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4)))
  2    FROM XXX_REPORT
  3   WHERE RECEIVER = '4444444444'
  4     AND STATUS_DETAIL = '6666'
  5     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) <
  6         TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4)
  7     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) >=
  8         TO_CHAR((TO_DATE(TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4),'yymmdd') - 2),'yymmdd');
     注：部分变量为绑定变量，这里填写了一个伪装值。表名也进行了适当处理。-;)
         XXX_REPORT是一张PARTION表，其数据量在15000000条左右。全表超过1.2G。
     2.
SQL> SELECT COUNT(DISTINCT(TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4)))
  2    FROM XXX_REPORT
  3   WHERE RECEIVER = '44444444444'
  4     AND STATUS_DETAIL = '6666'
  5     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) <
  6         TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4)
  7     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) >=
  8         TO_CHAR((TO_DATE(TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4),'yymmdd') - 2),'yymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 1107940823

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |    39 |  6198   (2)|  00:01:15 |       |       |        |      |            |
|   1 |  SORT GROUP BY             |               |     1 |    39 |            |          |       |       |        |      |            |
|*  2 |   PX COORDINATOR           |               |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001      |     1 |    39 |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT GROUP BY          |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE            |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH         | :TQ10000      |     1 |    39 |            |          |       |       |  Q1,00 | P->P | HASH       |
|   7 |        SORT GROUP BY       |               |     1 |    39 |            |          |       |       |  Q1,00 | PCWP |            |
|*  8 |         FILTER             |               |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|   9 |          PX BLOCK ITERATOR |               |     1 |    39 |  6198   (2)| 00:01:15 |     1 |     4 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| XXX_REPORT |     1 |    39 |  6198   (2)|  00:01:15 |     1 |     4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
   8 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
  10 - filter("RECEIVER"='44444444444' AND "STATUS_DETAIL"='6666' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)<TO_CHAR(SYSDATE@!,'yy')||'1105' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)>=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
      
    从执行计划上来看，即使使用了PARALLEL，但是全表的数据量太大。所以执行时间还是很长，应该超过1分15秒！
    寻找使用索引的可能。如果直接建立索引，尤其在生产库上其影响是非常巨大的。还好ORACLE提供了创建“虚拟索引”（VIRTUAL INDEX）的功能。(即使是这样也要慎重使用)
     建立VIRTUAL INDEX:
    
SQL> create index test_virtual on XXX_REPORT( SUBSTR(MESSAGEID, 1, 4)) nosegment;

Index created.
     注：该索引还是个基于函数的索引。
     接下来就是让ORACLE的优化器“知道”这个SESSION中可以考虑VIRTUAL INDEX了：
    
SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.
    OK!再来看看我的执行计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 378868617

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     1 |    39 |    11  (73)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                       |               |     1 |    39 |            |          |       |       |
|*  2 |   FILTER                             |               |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| XXX_REPORT |     1 |    39 |    11  (73)|  00:00:01 |     4 |     4 |
|*  4 |     INDEX RANGE SCAN                 |  TEST_VIRTUAL  |   310 |       |    10  (80)|  00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yym
              mdd'))
   3 - filter("RECEIVER"='44444444444' AND "MESSAGEID">='1217000000' AND "STATUS_DETAIL"='6666')
   4 - access(SUBSTR("MESSAGEID",1,4)>='1217')
       filter(TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)<TO_CHAR(SYSDATE@!,'yy')||'1105' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)>=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,
              'yymmdd'))
    预计的执行时间1秒！执行成本从6198下降到11！
    Great!
    完成？再想想，和研发同事确认，这个查询有个时限性即不能早于当天-1 ！别忘记这是个PARTITION的表，PARTITION的关键字段就是要求时间的字段（下面显示的SQL语句最后增加了一个 AND MESSAGEID>='1217000000' ）。改写这个SQL语句并查看执行计划：
  

SQL> SELECT /*test1*/ COUNT(DISTINCT(TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4)))
  2    FROM XXX_REPORT
  3   WHERE RECEIVER = '44444444444'
  4     AND STATUS_DETAIL = '6666'
  5     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) <
  6         TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4)
  7     AND TO_CHAR(SYSDATE, 'yy') || SUBSTR(MESSAGEID, 1, 4) >=
  8         TO_CHAR((TO_DATE(TO_CHAR(SYSDATE, 'yy') || SUBSTR('110520230792500100071', 1, 4),'yymmdd') - 2),'yymmdd')
  9      AND MESSAGEID>='1217000000';



Execution Plan
----------------------------------------------------------
Plan hash value: 378868617

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     1 |    39 |    11  (73)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                       |               |     1 |    39 |            |          |       |       |
|*  2 |   FILTER                             |               |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| XXX_REPORT |     1 |    39 |    11  (73)| 00:00:01 |     4 |     4 |
|*  4 |     INDEX RANGE SCAN                 |  TEST_VIRTUAL  |   310 |       |    10  (80)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------
   看！ORACLE经过再三思考还是决定用这个VIRTUAL INDEX(TEST_VIRTUAL)，成本低呗！不行去掉这个VIRTUAL INDEX再试试：
 
  
SQL> drop index test_virtual;

Index dropped.
   再看看执行计划

Execution Plan
----------------------------------------------------------
Plan hash value: 2704931136

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |    39 |    95   (2)| 00:00:02 |       |       |        |      |            |
|   1 |  SORT GROUP BY             |               |     1 |    39 |            |          |       |       |        |      |            |
|*  2 |   PX COORDINATOR           |               |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001      |     1 |    39 |            |          |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT GROUP BY          |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE            |               |     1 |    39 |            |          |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH         | :TQ10000      |     1 |    39 |            |          |       |       |  Q1,00 | P->P | HASH       |
|   7 |        SORT GROUP BY       |               |     1 |    39 |            |          |       |       |  Q1,00 | PCWP |            |
|*  8 |         FILTER             |               |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|   9 |          PX BLOCK ITERATOR |               |     1 |    39 |    95   (2)| 00:00:02 |     4 |     4 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| XXX_REPORT |     1 |    39 |    95   (2)| 00:00:02 |     4 |     4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

   2 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
   8 - filter(TO_CHAR(SYSDATE@!,'yy')||'1105'>TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))
  10 - filter("STATUS_DETAIL"='4442' AND "RECEIVER"='44444444444' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)<TO_CHAR(SYSDATE@!,'yy')||'1105' AND
              TO_CHAR(SYSDATE@!,'yy')||SUBSTR("MESSAGEID",1,4)>=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!,'yy')||'1105','yymmdd')-2,'yymmdd'))

    
    看！执行该计划虽然不如执行使用索引的好，但是95的执行成本不比11的高多少，执行的预计时间两秒！谁又会去感觉1秒和2秒的差别呢？除非我执行了几百上千次。好就是用这个方法。在实际的执行中看，确实得到了很好的效果。
    这里，为什么我在看到索引的“优秀”执行计划时还要再想想其他办法呢？原因很简单，这张表记录很多，且还会不断有大量的数据进入，那么就会面临着为维护一个索引而需要高额的数据库资源耗费！索引是好东西，但同时也是个“双刃剑”。
    通过改写该SQL后，虽然SQL还是执行了全表扫描，但是新增的条件，让ORACLE大幅降低了全扫数据的数量！即很有可能为原来的1/N(N为PARTITION的数量)。这种方法虽然不是最快的方法，但它并没有增加数据库额外的负担！所以综合来看，它是最好的方法。
    BALANCE!
    通过更为全局的观点，来达到整个数据库性能的综合提升，而不是在某一点性能的突出表现，来导致数据库整体性能的下降。
    另外，本文中提到的VIRTUAL INDEX技术，为DBA们提供了一个低成本优化SQL的办法，否则真是建立了一个INDEX而发现ORACLE并没有使用，那成本就太大了，如果在生产库上将是个灾难呀。
   
    我们需要在“平衡”中追求性能，而追求性能的脚步应该是不会停止的......
    
    附：
    很抱歉，因为没有控制好显示的宽度，所以执行计划稍显凌乱。

    真心的欢迎大家指正！-:)

本文转自Be the miracle!博客51CTO博客，原文链接http://blog.51cto.com/miracle/55870如需转载请自行联系原作者


Larry.Yue

文章标签：
SQL
关系型数据库
索引
数据库
Oracle
机器学习/深度学习
数据库管理
Unix
科技探索者
目录
相关文章
DrugAI
差异基因分析：fold change(差异倍数), P-value(差异的显著性)
差异基因分析：fold change(差异倍数), P-value(差异的显著性)
DrugAI
1947 0
差异基因分析：fold change(差异倍数), P-value(差异的显著性)
阿里开发者
|
15天前
|
缓存 并行计算 负载均衡
大模型推理优化实践：KV cache复用与投机采样
在本文中，我们将详细介绍两种在业务中实践的优化策略：多轮对话间的 KV cache 复用技术和投机采样方法。我们会细致探讨这些策略的应用场景、框架实现，并分享一些实现时的关键技巧。
阿里开发者
42 1
还是大剑师兰特
|
2月前
|
JavaScript 前端开发
v-if 和 v-show 的差异及最优使用场景
v-if和v-show都是Vue.js中的条件渲染指令，它们都可以根据表达式的值来决定是否渲染一个元素。但是它们的工作方式不同，因此在使用上也有一些区别。
还是大剑师兰特
13 0
Snu77
|
3月前
|
机器学习/深度学习
YOLOv8改进 | Conv篇 | 结合Dual思想利用HetConv创新一种全新轻量化结构CSPHet（参数量下降70W）
YOLOv8改进 | Conv篇 | 结合Dual思想利用HetConv创新一种全新轻量化结构CSPHet（参数量下降70W）
Snu77
49 0
算精通
|
7月前
|
存储
负载因子（Load Factor）
负载因子（Load Factor）是一个用于衡量散列表（如哈希表）填充程度的参数。它表示在散列表中，当插入一个新的键值对时，可以允许的最大填充程度。负载因子越大，
算精通
143 1
-编程工程师-
|
SQL 存储 缓存
原来count(*)就是我们系统的接口性能变差100倍的真凶…
原来count(*)就是我们系统的接口性能变差100倍的真凶…
-编程工程师-
102 0
陈宗志
|
11天前
|
关系型数据库 MySQL 测试技术
通过performance_schema定量分析系统瓶颈
目前在系统里面, 我们可以通过perf 或者 pt-pmp 汇总堆栈的方式来查看系统存在的热点, 但是我们仅仅能够知道哪些地方是热点, 却无法定量的说这个热点到底有多热, 这个热点占整个访问请求的百分比是多少? 是10%, 还是40%, 还是80%?所以我们需要一个定量分析系统瓶颈的方法以便于我们进...
陈宗志
21 0
CastenWang
|
索引
PAT甲级 1007. Maximum Subsequence Sum(25分) 复杂度优化到O(n)
PAT甲级 1007. Maximum Subsequence Sum(25分) 复杂度优化到O(n)
CastenWang
59 0
陈宗志
|
关系型数据库 MySQL 测试技术
通过performance_schema 定量分析系统瓶颈
目前在系统里面, 我们可以通过perf 或者 pt-pmp 汇总堆栈的方式来查看系统存在的热点, 但是我们仅仅能够知道哪些地方是热点, 却无法定量的说这个热点到底有多热, 这个热点占整个访问请求的百分比是多少? 是10%, 还是40%, 还是80%? 所以我们需要一个定量分析系统瓶颈的方法以便于我们进行系统优化. 本文通过Performance_schema 来进行定量的分析系统性能瓶颈
陈宗志
126 0
CV技术指南（公众号）
|
机器学习/深度学习 算法 计算机视觉
经典论文系列 | 缩小Anchor-based和Anchor-free检测之间差距的方法：自适应训练样本选择 ​
本文介绍一篇CVPR2020的论文，它在paperswithcode上获得了16887星，谷歌学术上有261的引用次数。 论文主要介绍了目标检测现有的研究进展、anchor-based和anchor-free的背景和各自的方法差异，并提出了一种新的正负样本选择方案，用于消除这两者之间的差距。
CV技术指南（公众号）
289 0
经典论文系列 | 缩小Anchor-based和Anchor-free检测之间差距的方法：自适应训练样本选择 ​

热门文章

最新文章

  • 1
    订票系统不再瘫痪 阿里云确认与12306合作
  • 2
    29.4. Maatkit Essential command-line utilities for MySQL
  • 3
    hdu 2051 Bitset （java）
  • 4
    sqlplus格式的使用
  • 5
    想上威胁情报？先搞明白这五个问题吧
  • 6
    kbmmw 中XML 操作入门
  • 7
    kotlin 语言入门指南(一)--基础语法
  • 8
    LINUX 硬连接和软连接
  • 9
    Android常用工具类封装---SharedPreferencesUtil
  • 10
    What are TCHAR, WCHAR, LPSTR, LPWSTR, LPCTSTR (etc.)?
  • 1
    如何本地安装AFFiNE并将服务发布至公网实现团队远程访问协作
    27
  • 2
    【Linux-14】进程地址空间＆虚拟空间＆页表——原理＆知识点详解
    18
  • 3
    如何在CentOS7一键安装宝塔面板并实现固定地址访问内网宝塔进行管理
    20
  • 4
    【Mysql】在多表查询过程中如果已经起了别名，就不能通过表名限定字段
    19
  • 5
    Windows文件搜索神器Everything安装配置结合内网穿透实现公网查询本地文件
    20
  • 6
    【Web 前端】 jQuery 里的 ID 选择器和 class 选择器有何不同？
    21
  • 7
    CentOS安装MeterSphere并实现无公网IP远程访问本地测试平台
    16
  • 8
    【Web 前端】网页上有 5 个div元素，如何使用JQ来选择它们？
    21
  • 9
    本地MinIO存储服务通过Java程序结合cpolar实现远程连接上传文件
    11
  • 10
    【Web 前端】jQuery 库中的 $() 是什么？
    16

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考