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

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

2014-12-12 853
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 在上一篇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
目录
相关文章
g洋洋洋洋洋-22223
|
应用服务中间件 Shell nginx
nginx离线部署
nginx离线部署
g洋洋洋洋洋-22223
544 0
喜欢猪猪
|
11月前
|
设计模式 Java API
微服务架构演变与架构设计深度解析
【11月更文挑战第14天】在当今的IT行业中，微服务架构已经成为构建大型、复杂系统的重要范式。本文将从微服务架构的背景、业务场景、功能点、底层原理、实战、设计模式等多个方面进行深度解析，并结合京东电商的案例，探讨微服务架构在实际应用中的实施与效果。
喜欢猪猪
571 6
以山向海
|
SQL 运维 监控
关系型数据库性能监控工具
【5月更文挑战第21天】
以山向海
273 2
游客cxtb2yf2r55as
|
存储
公司网站图片如何修改
公司网站图片如何修改
游客cxtb2yf2r55as
255 4
Java开发者
|
Java 大数据 API
Java 流(Stream)、文件(File)和IO的区别
Java中的流（Stream）、文件（File）和输入/输出（I/O）是处理数据的关键概念。`File`类用于基本文件操作，如创建、删除和检查文件；流则提供了数据读写的抽象机制，适用于文件、内存和网络等多种数据源；I/O涵盖更广泛的输入输出操作，包括文件I/O、网络通信等，并支持异常处理和缓冲等功能。实际开发中，这三者常结合使用，以实现高效的数据处理。例如，`File`用于管理文件路径，`Stream`用于读写数据，I/O则处理复杂的输入输出需求。
Java开发者
621 12
理想shi做条咸鱼
|
XML Java 数据库连接
Spring Boot集成MyBatis
主要系统的讲解了 Spring Boot 集成 MyBatis 的过程，分为基于 xml 形式和基于注解的形式来讲解，通过实际配置手把手讲解了 Spring Boot 中 MyBatis 的使用方式，并针对注解方式，讲解了常见的问题已经解决方式，有很强的实战意义。在实际项目中，建议根据实际情况来确定使用哪种方式，一般 xml 和注解都在用。
理想shi做条咸鱼
600 7
糜终
|
存储 关系型数据库 MySQL
深入理解MySQL：查询表的历史操作记录
深入理解MySQL：查询表的历史操作记录
糜终
1412 0
游客cxtb2yf2r55as
|
应用服务中间件 PHP nginx
访问网站500 Internal Server Error怎么办
访问网站500 Internal Server Error怎么办
游客cxtb2yf2r55as
1214 0
长梦
|
监控 大数据 数据处理
大数据组件之Storm简介
【5月更文挑战第2天】Apache Storm是用于实时大数据处理的分布式系统，提供容错和高可用的实时计算。核心概念包括Topology（由Spouts和Bolts构成的DAG）、Spouts（数据源）和Bolts（数据处理器）。Storm通过acker机制确保数据完整性。常见问题包括数据丢失、性能瓶颈和容错理解不足。避免这些问题的方法包括深入学习架构、监控日志、性能调优和编写健壮逻辑。示例展示了实现单词计数的简单Topology。进阶话题涵盖数据延迟、倾斜的处理，以及Trident状态管理和高级实践，强调调试、性能优化和数据安全性。
长梦
754 4
didiplus
|
Kubernetes 固态存储 调度
从NodeSelector到NodeAffinity：探索Kubernetes节点亲和性的进化之路
从NodeSelector到NodeAffinity：探索Kubernetes节点亲和性的进化之路
didiplus
810 0

热门文章

最新文章

  • 1
    【MCP教程系列】搭建基于 Spring AI 的 SSE 模式 MCP 服务并自定义部署至阿里云百炼
  • 2
    成功解决：was not registered for synchronization because synchronization is not active
  • 3
    《树莓派Python编程入门与实战》——1.6　让你的树莓派正常工作
  • 4
    gitlab合代码规范流程（一）
  • 5
    【教程】将自己的APP部署到阿里云
  • 6
    阿里云服务器ECS试用攻略及常见问题参考
  • 7
    CocosCreator3.8研究笔记（八）CocosCreator 节点和组件的使用
  • 8
    数据绑定（Data Binding）：实现实时数据交互的前端神器
  • 9
    中国计算机学会推荐国际学术会议和期刊目录(2)
  • 10
    实现多种方式对MYSQL进行备份
  • 1
    新手指南：通过 Playwright MCP Server 为 AI Agent 实现浏览器自动化能力
    48
  • 2
    系统里数据又“打架”了？让“少数服从多数”来终结这场混乱！
    29
  • 3
    基于springboot的医院陪诊预约挂号系统
    51
  • 4
    基于Android的智慧旅游系统的设计与实现
    26
  • 5
    java操作hbase报错：KeeperErrorCode=NoNode for /hbase-unsecure/master
    34
  • 6
    AI + 热成像技术在动火作业风险防控中的实现路径
    36
  • 7
    LBA-ECO ND-01 巴西朗多尼亚地理配准陆地卫星影像：1975-2000
    22
  • 8
    《3D山地场景渲染进阶：GPU驱动架构下细节与性能平衡的6大技术实践》
    37
  • 9
    《3D草原场景技术拆解：植被物理碰撞与多系统协同的6个实战方案》
    18
  • 10
    Dataphin功能Tips系列（73）Dataphin行级权限：构建灵活高效的权限管理体系
    21

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    阿里云无影云电脑免费使用_免费申请教程