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

关于索引扫描的极速调优实战（第二篇)

2014-12-12 794
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况，已经初步根据awr能够抓取到存在问题的sql语句。
在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况，已经初步根据awr能够抓取到存在问题的sql语句。
这条sql语句执行很频繁，目前平均执行时间在0.5秒。开发部门希望我们能不能做点优化，他们也在同时想办法从业务上来优化这个问题。从0.5秒的情况下，能够再提高很多，是得费很大力气的。
况且这个问题比较紧急，从拿到sql语句开始，就感觉到一种压力。
最开始的注意力都集中在cycle_month和cycle_year的处理上。
对于下面的部分，是这条sql语句的关键，cycle_year,cycle_month是在索引列中，但是根据业务逻辑，需要把cycle_year,cycle_month拼成一个数字，然后计算cycle_year+cycle_month最大的值。
目前的实现是把cycle_year准换成为字符型，然后使用这个字符串在子查询中匹配。这样的话，cycle_year,cycle_month作为索引列就不能直接使用索引了，还得依靠第一个索引列cycle_code.
   AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
       (SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
          FROM CRDT_LMT_NOTIFICATION

自己采用了如下的方式来改进，但是查看收效甚微，基本没有变化。
 AND (CYCLE_YEAR,CYCLE_MONTH) =
       (SELECT substr(MAX(cycle_year*100+cycle_month),0,4),substr(MAX(cycle_year*100+cycle_month),5,6)

所以看来需要索引扫描上多下点功夫。
根据sqlprofile中的提示，使用index skip scan效率最高。
但是使用index_ss却始终都是走index range scan.
SELECT /*+index_ss(CRDT_LMT_NOTIFICATION CRDT_LMT_NOTIFICATION_PK)*/LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
  FROM CRDT_LMT_NOTIFICATION
 WHERE CYCLE_CODE = 25
        AND ITEM_ID = 15131
       AND AGREEMENT_ID = 15997361
       AND OFFER_INSTANCE = 223499890
       AND CUSTOMER_ID = 10349451
   AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
       (SELECT  MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
          FROM PM9_CRDT_LMT_NOTIFICATION
         WHERE CYCLE_CODE = 25
        AND ITEM_ID = 15131
       AND AGREEMENT_ID = 15997361
       AND OFFER_INSTANCE = 223499890
       AND CUSTOMER_ID = 10349451)          
SQL> @plan
Plan hash value: 2310822947
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    37 |  4281   (1)| 00:00:52 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     1 |    37 |  2141   (1)| 00:00:26 |    13 |    25 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     1 |    37 |  2141   (1)| 00:00:26 |    13 |    25 |
|*  3 |    INDEX RANGE SCAN                |     CRDT_LMT_NOTIFICATION_PK |     1 |       |  2140   (1)| 00:00:26 |    13 |    25 |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     1 |    34 |  2140   (1)| 00:00:26 |    13 |    25 |
|*  6 |       INDEX RANGE SCAN             |     CRDT_LMT_NOTIFICATION_PK |     1 |    34 |  2140   (1)| 00:00:26 |    13 |    25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
              "ITEM_ID"=15131)
       filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
              TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
              MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "    CRDT_LMT_NOTIFICATION"
              "    CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
              "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
   6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
              "ITEM_ID"=15131)
       filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)

最后发现主要的原因是因为隐含参数 _optimizer_skip_scan_enabled 值为"false"导致的。
SQL> show parameter skip_scan
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_skip_scan_enabled         boolean     FALSE
 
对于这个参数，我使用alter sessison在session级做了改动。
alter session set " _optimizer_skip_scan_enabled"=true;
然后查看执行计划。效率极大的提高了。
SQL> @plan
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    37 |     3  (34)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     1 |    37 |     2  (50)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     1 |    37 |     2  (50)| 00:00:01 |   KEY |   KEY |
|*  3 |     INDEX SKIP SCAN                 |     CRDT_LMT_NOTIFICATION_PK |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     1 |    34 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |       INDEX SKIP SCAN              |     CRDT_LMT_NOTIFICATION_PK |     1 |    34 |     1   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
              "OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
       filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
              "ITEM_ID"=TO_NUMBER(:A) AND TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
              MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"."    CRDT_LMT_NOTIFICATION"
              "    CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
              "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A)))
   6 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
              "OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
       filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
              "ITEM_ID"=TO_NUMBER(:A))
为什么skip scan效率这么高，但是使用隐含参数禁用了它呢。
产品部门的解释是对于skip scan在大多数的场景中，效率不是很理想，基本跟index full scan的效果一样，所以从优化器内部使用隐含参数禁用，就使用了range scan.
所以这个问题的处理就比较纠结，想得到立竿见影的效果吧，使用index_ss不起作用，做全局变更吧，这样可能会影响其它的sql运行。使用alter session处理，在程序中实现又不现实。

最后使用另外一个hint解决上面的顾虑。opt_param，这个hint是在10gR2之后引进的，要解决的问题就是可以避免系统级的db参数变更。
尝试的hint格式如下。
SELECT  /*+opt_param('_optimizer_skip_scan_enabled',true)*/  ....
但是执行计划中缺还是走了range scan。资源消耗跟没加hint一个样。
最后发现对于这个hint需要写为：
SELECT  /*+opt_param('_optimizer_skip_scan_enabled', 'true')*/  ....
这样就能够达到预期的目标了。从0.5秒到0.01秒，绝对是性能的极大提升。
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    37 |     6  ( 67)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                              |     1 |    37 |     3  (67)| 00:00:01 |    13 |    25 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     1 |    37 |     3  (67)| 00:00:01 |    13 |    25 |
|*  3 |     INDEX SKIP SCAN                 |     CRDT_LMT_NOTIFICATION_PK |     1 |       |     3  (67)| 00:00:01 |    13 |    25 |
|   4 |     SORT AGGREGATE                 |                              |     1 |    34 |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR      |                              |     1 |    34 |     3  (67)| 00:00:01 |    13 |    25 |
|*  6 |       INDEX SKIP SCAN              |     CRDT_LMT_NOTIFICATION_PK |     1 |    34 |     3  (67)| 00:00:01 |    13 |    25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
              "ITEM_ID"=15131)
       filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
              TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
              MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"."    CRDT_LMT_NOTIFICATION"
              "    CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
              "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
   6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
              "ITEM_ID"=15131)
       filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
可能这个问题到此就告一段落了，我在得到了一个初步的结论之后和开发部门进行协调，他们也试图从业务上进行简化。
最后他们把纠结的cycle_month和cycle_year的拼接去除了。改为在程序中处理。
与其说是改进不是直接说是简化。
SELECT  
LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
  FROM PM9_CRDT_LMT_NOTIFICATION
 WHERE ITEM_ID = :a
   AND AGREEMENT_ID = :a
   AND CYCLE_CODE = :a
   AND OFFER_INSTANCE = :a
   AND CUSTOMER_ID = :a
   AND CYCLE_YEAR=:a
   AND CYCLE_MONTH=:a
这条sql语句直观来看肯定是走唯一性扫描，但是效果有多好呢。可以看看几个指标，都已经达到了最低。
Plan hash value: 404442430
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    37 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                              |     1 |    37 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID|     CRDT_LMT_NOTIFICATION    |     1 |    37 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX UNIQUE SCAN               |     CRDT_LMT_NOTIFICATION_PK |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CYCLE_MONTH"=TO_NUMBER(:A) AND "CYCLE_YEAR"=TO_NUMBER(:A) AND
              "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
              "ITEM_ID"=TO_NUMBER(:A))

从开发得到的反馈是这个逻辑的修改也不复杂，最后他们决定使用简化后的sql。
在协调部署之后。速度有了极大的提升。
处理的事务数有了近10倍的提升。从十万事务到近百万事务 ，处理的速度还是提升了很多。
以下是事务处理的一些反馈数据。可以看到效果还是很明显的。

TIME

COUNT

20141212 00

119844

20141212 01

57357

20141212 02

23153

20141212 03

20610

20141212 04

111148

20141212 05

102540

20141212 06

59834

20141212 07

213985

20141212 08

69733

20141212 09

137163

20141212 10

163106

20141212 11

87091

20141212 12

89880

20141212 13

841172

20141212 14

960209

20141212 15

948309

20141212 16

899030

20141212 17

870231

20141212 18

953362

通过这个实例，我们可以看到业务优化还是最好的优化，从数据库的角度来做优化，也需要考虑到影响范围，尽量是影响和变更最低，效率最高。
文章标签：
索引
SQL
数据库
jeanron100
目录
相关文章
季风泯灭的季节
|
2月前
|
SQL 存储 关系型数据库
Mysql索引优化实战一
Mysql索引优化实战一
季风泯灭的季节
37 0
季风泯灭的季节
|
2月前
|
SQL 算法 关系型数据库
MySQL索引优化实战二
MySQL索引优化实战二
季风泯灭的季节
28 0
码猿技术专栏
|
SQL 搜索推荐 关系型数据库
一文带你你搞懂索引如何优化！！！
一文带你你搞懂索引如何优化！！！
码猿技术专栏
61 0
肖健
|
存储 SQL NoSQL
二十、sql优化其他方式
二十、sql优化其他方式
肖健
148 0
ybbf7fwncy2w2
|
存储 SQL 自然语言处理
入门案例(索引过程)|学习笔记
快速学习入门案例(索引过程)
ybbf7fwncy2w2
69 0
入门案例(索引过程)|学习笔记
ybbf7fwncy2w2
|
存储 数据采集 自然语言处理
lucene 索引流程详细分析|学习笔记
快速学习 lucene 索引流程详细分析
ybbf7fwncy2w2
101 0
lucene 索引流程详细分析|学习笔记
浅羽技术
|
存储 SQL 缓存
干货！MySQL优化原理分析及优化方案总结
说起MySQL优化的话，想必大部分人都不陌生了。在我们的记忆储备里也早已记住了这些关键词：避免使用SELECT*、避免使用NULL值的判断、根据需求适当的建立索引、优化MySQL参数.....
浅羽技术
413 0
邴越
|
存储 SQL 算法
从原理到优化，深入浅出数据库索引
数据库查询是数据库的最主要功能之一，我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化，这篇文章对索引做一个系统的梳理，希望对大家有帮助。
邴越
2051 0
优惠码发放
|
关系型数据库 MySQL 索引
Mysql优化（出自官方文档） - 第八篇（索引优化系列）
Mysql优化（出自官方文档） - 第八篇（索引优化系列）目录 Mysql优化（出自官方文档） - 第八篇（索引优化系列）Optimization and Indexes1 Foreign Key Optimization2 Column Indexes3 Column Indexes && Mu...
优惠码发放
1100 0
孤独键客
|
关系型数据库 MySQL 程序员
MySQL索引优化看这篇文章就够了！
不容错过~
孤独键客
1140 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的装饰器
    32
  • 2
    R语言用潜类别混合效应模型(Latent Class Mixed Model ,LCMM)分析老年痴呆年龄数据
    20
  • 3
    数据分享|R语言用logistic逻辑回归和AFRIMA、ARIMA时间序列模型预测世界人口
    15
  • 4
    【视频】马尔可夫链蒙特卡罗方法MCMC原理与R语言实现|数据分享（下）
    20
  • 5
    【视频】马尔可夫链蒙特卡罗方法MCMC原理与R语言实现|数据分享（上）
    26
  • 6
    数据分享|数据探索电商平台用户行为流失可视化分析
    28
  • 7
    电商平台数据可视化分析网红零食销量
    18
  • 8
    R语言线性回归模型拟合诊断异常值分析家庭燃气消耗量和卡路里实例带自测题
    15
  • 9
    数据分享|R语言GLM广义线性模型：逻辑回归、泊松回归拟合小鼠临床试验数据（剂量和反应）示例和自测题
    12
  • 10
    数据分享|R语言逻辑回归、线性判别分析LDA、GAM、MARS、KNN、QDA、决策树、随机森林、SVM分类葡萄酒交叉验证ROC（下）
    16

    • 相关电子书

    更多
  • Phoenix 全局索引原理与实践
  • MySQL查询优化实战
  • O2O搜索优化实践之道

    • 相关实验场景

    更多
  • 使用检索分析服务Elasticsearch版实现基础检索
  • 函数计算进阶-IP查询工具开发
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）