AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-11-05 815
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
本文旨在通过一次对某个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
科技探索者
目录
相关文章
最好zzz
|
3天前
|
SQL 关系型数据库 MySQL
MySQL性能探究：count(*)与count(1)的性能对决
在MySQL数据库的性能优化中，对查询语句的细微差别有着深入的理解是非常重要的。`count(*)`和`count(1)`是两种常用的聚合函数，用于计算行数。在面试中，面试官经常会问到这两种函数的性能差异。本文将探讨`count(*)`与`count(1)`的性能对比，并整理十道经典的MySQL面试题，帮助你在面试中游刃有余。
最好zzz
15 3
郑小健
|
4月前
|
vr&ar 图形学 UED
优化图形渲染与物理模拟：减少Draw Calls与利用LOD技术提升性能
【7月更文第10天】在现代游戏开发和实时渲染应用中，性能优化是至关重要的环节，它直接关系到用户体验的流畅度和真实感。本文将深入探讨两种关键技术手段——减少Draw Calls和使用Level of Detail (LOD) 技术，来提升图形渲染与物理模拟的效率。
郑小健
208 2
还是大剑师兰特
|
6月前
|
JavaScript 前端开发
v-if 和 v-show 的差异及最优使用场景
v-if和v-show都是Vue.js中的条件渲染指令，它们都可以根据表达式的值来决定是否渲染一个元素。但是它们的工作方式不同，因此在使用上也有一些区别。
还是大剑师兰特
48 0
-编程工程师-
|
SQL 存储 缓存
原来count(*)就是我们系统的接口性能变差100倍的真凶…
原来count(*)就是我们系统的接口性能变差100倍的真凶…
-编程工程师-
139 0
玩SAP的苏州BOY
SAP WM高阶之下架策略M(Small Large Quantity)（2）
SAP WM高阶之下架策略M(Small Large Quantity)（2）
玩SAP的苏州BOY
144 0
SAP WM高阶之下架策略M(Small Large Quantity)（2）
干货满满张哈希
|
设计模式 存储 消息中间件
分布式系统设计模式 - 最低水位线（Low-Water Mark）
分布式系统设计模式 - 最低水位线（Low-Water Mark）
干货满满张哈希
253 0
学堂小助手
|
资源调度 分布式计算 算法
Max compute 计算长尾问题优化| 学习笔记
快速学习 Max compute 计算长尾问题优化
学堂小助手
470 0
skin778
|
存储 缓存 算法
深度 | X-Engine的In-Memory读性能优化
作者：顾绅/北楼
skin778
1279 0
深度 | X-Engine的In-Memory读性能优化
minlearn2016
|
分布式计算 监控 Unix
Plan9:一个从0开始考虑分布式，分布appmodel的os设计
本文关键字：plan9,Inferno,limbo,Plan 9 from User Space:plan9port
minlearn2016
585 0
Plan9:一个从0开始考虑分布式，分布appmodel的os设计
激情的涯海
|
Java Linux 芯片
系统稳定性——So Hot? 快给 CPU 降降温！
> 作者：夏明（涯海） > 创作日期：2019-08-14 > 专栏地址：[【稳定大于一切】](https://github.com/StabilityMan/StabilityGuide) CPU（Central Processing Unit）是计算机系统的运算和控制核心，是信息处理、程序运行的最终执行单元，相当于系统的“大脑”。当 CPU 过于繁忙，就像“人脑”并发处理
激情的涯海
1307 0