备案控制台
探索云世界
开发者社区 云计算 文章 正文

不走索引场景的一次分析优化

2015-10-21 847
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：   一般一个SQL查询数据量很大，且优化余地不大时，我们必然想开启并行，用并行的方式提高数据的查询速度，然后不是任何情况下开启并行都可以达到最佳运行效果， 有时原本使用索引的执行计划，因为使用并行反而该走全表扫描，因此必须通过hint方式引导优化器...

 

一般一个SQL查询数据量很大，且优化余地不大时，我们必然想开启并行，用并行的方式提高数据的查询速度，然后不是任何情况下开启并行都可以达到最佳运行效果，
有时原本使用索引的执行计划，因为使用并行反而该走全表扫描，因此必须通过hint方式引导优化器采取正确的执行计划，对于如下SQL
SELECT   
                     T1.RPO_NO
                    ,T_LGIN.LGIN_DT
                    ,T_LGIN.USER_ID  
                    ,T_LGIN.USER_IP
                    ,T_LGIN.CLNT_IP
                    ,T_LGIN.MAC_ADDR
                    ,T_LGIN.MENU_SYS_CD
                    ,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK
                    FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1
                            ,MCS_HQ.HI_USER_LGIN T_LGIN
                    WHERE   T_LGIN.USER_ID = T1.REQ_ID
                    AND     T_LGIN.LGIN_DT >= TRUNC(T1.REQ_DT)
                    AND     T_LGIN.LGIN_DT <= T1.REQ_DT;
     
     
不采取任何方式人工干预，优化器生成的执行计划将按索引查询表HI_USER_LGIN，但因HI_USER_LGIN表内数据量很大，查询非常消耗资源，因此开启并行，提高查询速度。
开始引入并行的hint如下所示

SQL_ID  7f2gdrbqzv7d8, child number 0
-------------------------------------
SELECT    /*+ PARALLEL(T_LGIN,8) PARALLEL(T1,8) ALL_ROWS */            
                 T1.RPO_NO /*-testNL0000001*/                    
,T_LGIN.LGIN_DT                     ,T_LGIN.USER_ID                    
,T_LGIN.USER_IP                     ,T_LGIN.CLNT_IP                    
,T_LGIN.MAC_ADDR                     ,T_LGIN.MENU_SYS_CD               
           ,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY
T_LGIN.LGIN_DT DESC) RNK                     FROM   
MCS_HQ_READ.UP_RPO_TRACE_0602 T1                            
,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE   T_LGIN.USER_ID
= T1.REQ_ID                     AND     T_LGIN.LGIN_DT >=
TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <= T1.REQ_DT
 
Plan hash value: 3061441924
 
-----------------------------------------------------------------
| Id  | Operation                  | Name              | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |        |
|   1 |  PX COORDINATOR            |                   |        |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002          |    307K|
|   3 |    WINDOW SORT             |                   |    307K|
|   4 |     PX RECEIVE             |                   |    307K|
|   5 |      PX SEND HASH          | :TQ10001          |    307K|
|*  6 |       HASH JOIN            |                   |    307K|
|   7 |        PX RECEIVE          |                   |    396K|
|   8 |         PX SEND BROADCAST  | :TQ10000          |    396K|
|   9 |          PX BLOCK ITERATOR |                   |    396K|
|* 10 |           TABLE ACCESS FULL| UP_RPO_TRACE_0602 |    396K|
|  11 |        PX BLOCK ITERATOR   |                   |     35M|
|* 12 |         TABLE ACCESS FULL  | HI_USER_LGIN      |     35M|
-----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"
              )) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
  10 - access(:Z>=:Z AND :Z<=:Z)
  12 - access(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - dynamic sampling used for this statement (level=5)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 

从执行计划看，说明优化器并没有按之前的计划采用索引方式扫描HI_USER_LOGIN，而是直接采用并行全表扫描的方式，说明并行不是在原来计划的基础上添加并行，但全表扫描如此大量的数据，
会降低查询速度，因此这里尝试人工干预执行计划，令其查询HI_USER_LOGIN时走索引，查看该表上对应的连接字段上有索引X_HI_USER_LGIN_2(LGIN_DT,USER_ID),因此引导优化器，
令连接的连个表采用嵌套循环连接方式，时扫描大表时，优化器能使用大表上的索引查询连接字段的值，修改hint后的执行计划


 SQL_ID  g1thg0qgnk745, child number 0
-------------------------------------
SELECT  /*+ LEADING(T1) USE_NL(T_LGIN) PARALLEL(T_LGIN,8)
PARALLEL(T1,8) ALL_ROWS */                              T1.RPO_NO
/*-testNL*/                     ,T_LGIN.LGIN_DT                    
,T_LGIN.USER_ID                     ,T_LGIN.USER_IP                    
,T_LGIN.CLNT_IP                     ,T_LGIN.MAC_ADDR                   
 ,T_LGIN.MENU_SYS_CD                           ,ROW_NUMBER()
OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK          
          FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1                     
       ,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE  
T_LGIN.USER_ID = T1.REQ_ID                     AND     T_LGIN.LGIN_DT
>= TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <=
T1.REQ_DT
 
Plan hash value: 3231175795
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   1 |  PX COORDINATOR                   |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   2 |   PX SEND QC (RANDOM)             | :TQ10001          |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   3 |    WINDOW SORT                    |                   |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   4 |     PX RECEIVE                    |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   5 |      PX SEND HASH                 | :TQ10000          |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   6 |       NESTED LOOPS                |                   |      1 |        |    185K|00:01:06.73 |     720K|  25072 |
|   7 |        NESTED LOOPS               |                   |      1 |    307K|    185K|00:00:44.02 |     538K|   6995 |
|   8 |         PX BLOCK ITERATOR         |                   |      1 |        |  67750 |00:00:01.00 |    2338 |   2129 |
|*  9 |          TABLE ACCESS FULL        | UP_RPO_TRACE_0602 |     15 |    396K|  67750 |00:00:00.97 |    2338 |   2129 |
|* 10 |         INDEX RANGE SCAN          | X_HI_USER_LGIN_2  |  67750 |     16 |    185K|00:00:42.87 |     536K|   4866 |
|  11 |        TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN      |    185K|      1 |    185K|00:00:22.49 |     181K|  18077 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - access(:Z>=:Z AND :Z<=:Z)
  10 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"))
              AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
 
Note
-----
   - dynamic sampling used for this statement (level=5)
 
 
 显然，使用并行后，需要认为使用hint干预执行计划，使其使用合理的索引，提高查询速度
 
 

SQL_ID  gyn8zxr8uchgs, child number 0
-------------------------------------
SELECT  /*+ LEADING(T1) USE_HASH(T_LGIN) PARALLEL(T_LGIN,8)
PARALLEL(T1,8) ALL_ROWS */                              T1.RPO_NO
/*-test01*/                     ,T_LGIN.LGIN_DT                    
,T_LGIN.USER_ID                     ,T_LGIN.USER_IP                    
,T_LGIN.CLNT_IP                     ,T_LGIN.MAC_ADDR                   
 ,T_LGIN.MENU_SYS_CD                           ,ROW_NUMBER()
OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK          
          FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1                     
       ,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE  
T_LGIN.USER_ID = T1.REQ_ID                     AND     T_LGIN.LGIN_DT
>= TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <=
T1.REQ_DT
 
Plan hash value: 3061441924
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   1 |  PX COORDINATOR            |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002          |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   3 |    WINDOW SORT             |                   |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   4 |     PX RECEIVE             |                   |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   5 |      PX SEND HASH          | :TQ10001          |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|*  6 |       HASH JOIN            |                   |      1 |    307K|    221K|00:00:46.56 |   66137 |  66027 |
|   7 |        PX RECEIVE          |                   |      1 |    396K|    513K|00:00:00.56 |       0 |      0 |
|   8 |         PX SEND BROADCAST  | :TQ10000          |      0 |    396K|      0 |00:00:00.01 |       0 |      0 |
|   9 |          PX BLOCK ITERATOR |                   |      0 |    396K|      0 |00:00:00.01 |       0 |      0 |
|* 10 |           TABLE ACCESS FULL| UP_RPO_TRACE_0602 |      0 |    396K|      0 |00:00:00.01 |       0 |      0 |
|  11 |        PX BLOCK ITERATOR   |                   |      1 |     35M|   4718K|00:00:06.03 |   66137 |  66027 |
|* 12 |         TABLE ACCESS FULL  | HI_USER_LGIN      |     22 |     35M|   4718K|00:00:04.62 |   66137 |  66027 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND
              "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
  10 - access(:Z>=:Z AND :Z<=:Z)
  12 - access(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - dynamic sampling used for this statement (level=5)
 

 
 
 
 SQL_ID  db4n20k7nt2bh, child number 0
-------------------------------------
SELECT  /*+ LEADING(T1) USE_NL(T_LGIN) */                             
T1.RPO_NO /*-testNL1*/                     ,T_LGIN.LGIN_DT             
       ,T_LGIN.USER_ID                     ,T_LGIN.USER_IP             
       ,T_LGIN.CLNT_IP                     ,T_LGIN.MAC_ADDR            
        ,T_LGIN.MENU_SYS_CD                           ,ROW_NUMBER()
OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK          
          FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1                     
       ,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE  
T_LGIN.USER_ID = T1.REQ_ID                     AND     T_LGIN.LGIN_DT
>= TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <=
T1.REQ_DT
 
Plan hash value: 1850855573
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |    100 |00:03:53.23 |    5480K|  16439 |  21677 |
|   1 |  WINDOW SORT                  |                   |      1 |    307K|    100 |00:03:53.23 |    5480K|  16439 |  21677 |
|   2 |   NESTED LOOPS                |                   |      1 |        |   1396K|00:03:50.52 |    5480K|  16397 |      0 |
|   3 |    NESTED LOOPS               |                   |      1 |    307K|   1396K|00:03:42.31 |    4111K|  16379 |      0 |
|   4 |     TABLE ACCESS FULL         | UP_RPO_TRACE_0602 |      1 |    396K|    513K|00:00:05.64 |   16479 |  16378 |      0 |
|*  5 |     INDEX RANGE SCAN          | X_HI_USER_LGIN_2  |    513K|      1 |   1396K|00:03:35.82 |    4095K|      1 |      0 |
|   6 |    TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN      |   1396K|      1 |   1396K|00:00:06.86 |    1368K|     18 |      0 |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND
              "T_LGIN"."LGIN_DT"<="T1"."REQ_DT")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 

文章标签：
索引
flzhang
目录
相关文章
技术蜜糖罐
|
22天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
MySQL 查询优化：提速查询效率的13大秘籍（索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化）（中）
技术蜜糖罐
37 0
最后一支迷迭香
|
17天前
|
存储 关系型数据库 MySQL
mysql索引优化，更好的创建和使用索引
mysql索引优化，更好的创建和使用索引
最后一支迷迭香
23 1
kc2u5456dvuc6
|
10月前
|
JavaScript 前端开发 API
【项目数据优化三】长列表数据优化
【项目数据优化三】长列表数据优化
kc2u5456dvuc6
91 0
q7s2kces74wvy
|
缓存 关系型数据库 MySQL
索引三表优化案例|学习笔记
快速学习索引三表优化案例
q7s2kces74wvy
64 0
索引三表优化案例|学习笔记
q7s2kces74wvy
|
SQL 关系型数据库 MySQL
索引单表优化案例|学习笔记
快速学习索引单表优化案例
q7s2kces74wvy
98 0
q7s2kces74wvy
|
关系型数据库 MySQL 开发者
索引两表优化案例|学习笔记
快速学习索引两表优化案例
q7s2kces74wvy
86 0
索引两表优化案例|学习笔记
游客ipr2b72wwzsnc
|
存储 机器学习/深度学习 缓存
索引为什么能提高查询性能....
B+ 树的古老数据结构
游客ipr2b72wwzsnc
68 0
索引为什么能提高查询性能....
Lateautumn4lin
|
SQL 存储 关系型数据库
MySQL索引优化案例
开发同学或多或少会遇到系统响应慢的问题,除了业务系统本身的问题外,常常会遇到SQL查询慢的问题,这篇文章结合实际案例分析MySQL InnoDB存储引擎的索引优化,这篇文章不会介绍B+树的知识点,如果需要了解聚集索引和辅助索引特点的同学可以参考这篇文章,这篇文章主要会介绍三星索引和ICP优化.
Lateautumn4lin
1200 0
德哥,digoal
|
Web App开发 关系型数据库 测试技术
PostgreSQL pageinspect 诊断与优化GIN (倒排) 索引合并延迟导致的查询性能下降问题
标签 PostgreSQL , brin索引 , gin索引 , 合并延迟 , gin_pending_list_limit , 查询性能下降 背景 GIN索引为PostgreSQL数据库多值类型的倒排索引，一条记录可能涉及到多个GIN索引中的KEY，所以如果写入时实时合并索引，会导致IO急剧增加，写入RT必然增加。
德哥,digoal
1804 0
嗯哼9925
|
数据库 索引 SQL
索引对查询效率的影响
嗯哼9925
1241 0

热门文章

最新文章

  • 1
    理解事务的4种隔离级别
  • 2
    疑犯追踪第一季/全集Person Of Interest迅雷下载
  • 3
    简单实用的数据建模工具PDManer
  • 4
    详细讲解！Canal+Kafka实现MySQL与Redis数据同步！
  • 5
    71.7. Script for automatic startup on boot
  • 6
    关于Oracle客户端显示乱码问题
  • 7
    PHPWAMP站点管理的“域名模式”和“端口模式”详解、均支持自定义
  • 8
    jsHelper
  • 9
    学校里学不到的东西(一)
  • 10
    Python 3 教程一:入门
  • 1
    Python的装饰器
    30
  • 2
    R语言用潜类别混合效应模型(Latent Class Mixed Model ,LCMM)分析老年痴呆年龄数据
    20
  • 3
    数据分享|R语言用logistic逻辑回归和AFRIMA、ARIMA时间序列模型预测世界人口
    14
  • 4
    【视频】马尔可夫链蒙特卡罗方法MCMC原理与R语言实现|数据分享（下）
    18
  • 5
    【视频】马尔可夫链蒙特卡罗方法MCMC原理与R语言实现|数据分享（上）
    23
  • 6
    数据分享|数据探索电商平台用户行为流失可视化分析
    25
  • 7
    电商平台数据可视化分析网红零食销量
    17
  • 8
    R语言线性回归模型拟合诊断异常值分析家庭燃气消耗量和卡路里实例带自测题
    15
  • 9
    数据分享|R语言GLM广义线性模型：逻辑回归、泊松回归拟合小鼠临床试验数据（剂量和反应）示例和自测题
    12
  • 10
    数据分享|R语言逻辑回归、线性判别分析LDA、GAM、MARS、KNN、QDA、决策树、随机森林、SVM分类葡萄酒交叉验证ROC（下）
    15

    • 相关电子书

    更多
  • RowKey与索引设计：技巧与案例分析
  • MySQL查询优化实战
  • 面向索引、模型、检索联合优化的下一代推荐技术

    • 相关实验场景

    更多
  • 使用检索分析服务Elasticsearch版实现基础检索
  • 基于工具的数据探查
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）