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

生产环境sql语句调优实战第四篇

2017-12-04 904
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

生产中有一条sql语句消耗了大量的cpu资源，执行时间在18秒左右，

Session

:

PRODBUSER (1560:61133)

SQL ID

:

1hg2wcuapy3y3

SQL Execution ID

:

16871963

Execution Started

:

07/21/2014 12:30:20

First Refresh Time

:

07/21/2014 12:30:24

Last Refresh Time

:

07/21/2014 12:30:37

Duration

:

18s

Module/Action

:

JDBC Thin Client/-

Service

:

PRODB

Program

:

JDBC Thin Client

Fetch Calls

:

771

sql语句如下，略微做了删改。

select t_run_request.run_mode,
       t_cycle_groups.flow_id,
       t_cycle_groups.request_id,
       t_cycle_groups.dynamic_attributes,
       t_cycle_groups.sys_creation_date,
       t_cycle_groups.sys_update_date,
       t_cycle_control.cycle_code
  from (select t_cycle_groups.*, a.request_id
          from (select t_cycle_groups.group_id,
                       t_cycle_groups.flow_id,
                       t_cycle_groups.cycle_seq_no,
                       t_cycle_groups.route,
                       t_group_status.request_id
                  from t_cycle_groups, --8000多条数据
                       t_group_status  --100多万条数据
                 where t_cycle_groups.group_id = t_group_status.group_id
                   AND t_cycle_groups.flow_id = t_group_status.flow_id
                   AND t_cycle_groups.cycle_seq_no =
                       t_group_status.cycle_seq_no
                   AND t_cycle_groups.route = t_group_status.route
                 group by t_cycle_groups.group_id,
                          t_cycle_groups.flow_id,
                          t_cycle_groups.cycle_seq_no,
                          t_cycle_groups.route,
                          t_group_status.request_id) a,
               t_cycle_groups
         where t_cycle_groups.group_id = a.group_id
           AND t_cycle_groups.flow_id = a.flow_id
           AND t_cycle_groups.cycle_seq_no = a.cycle_seq_no
           AND t_cycle_groups.route = a.route) t_cycle_groups,
       t_cycle_control, --2121多条数据
       t_run_request   --6000多条数据
 where t_cycle_groups.status = 'FIN'
   AND t_cycle_groups.request_id = t_run_request.request_id
   AND t_cycle_control.cycle_seq_no = t_cycle_groups.cycle_seq_no
   AND (t_run_request.population_type = 'CYC' OR
       t_run_request.population_type = 'CCD')
   AND t_run_request.population_id = t_cycle_control.cycle_seq_no
 order by t_cycle_groups.request_id

执行计划如下：

Plan hash value: 2458454793


------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |  4271K|   822M|       |   201K  (1)| 00:40:23 |       |       |
|   1 |  SORT ORDER BY               |                       |  4271K|   822M|   855M|   201K  (1)| 00:40:23 |       |       |
|*  2 |   HASH JOIN                  |                       |  4271K|   822M|       | 13779   (1)| 00:02:46 |       |       |
|   3 |    VIEW                      | VW_NSO_1              |   423 | 13959 |       |    27   (8)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE              |                       |   423 | 12690 |       |    27   (8)| 00:00:01 |       |       |
|*  5 |      HASH JOIN               |                       |  5876 |   172K|       |    26   (4)| 00:00:01 |       |       |
|   6 |       VIEW                   | index$_join$_009      |  2119 | 16952 |       |     3  (34)| 00:00:01 |       |       |
|*  7 |        HASH JOIN             |                       |       |       |       |            |          |       |       |
|   8 |         INDEX FAST FULL SCAN | T_CYCLE_CONTROL_PK    |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|   9 |         INDEX FAST FULL SCAN | T_CYCLE_CONTROL_1IX   |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       TABLE ACCESS FULL      | T_RUN_REQUEST         |  6038 |   129K|       |    23   (0)| 00:00:01 |       |       |
|* 11 |    HASH JOIN                 |                       |  1009K|   162M|       | 13735   (1)| 00:02:45 |       |       |
|  12 |     TABLE ACCESS FULL        | T_RUN_REQUEST         |  6110 | 67210 |       |    23   (0)| 00:00:01 |       |       |
|* 13 |     HASH JOIN                |                       |  1009K|   152M|       | 13708   (1)| 00:02:45 |       |       |
|  14 |      JOIN FILTER CREATE      | :BF0000               |  8824 |  1128K|       |    48   (3)| 00:00:01 |       |       |
|* 15 |       HASH JOIN              |                       |  8824 |  1128K|       |    48   (3)| 00:00:01 |       |       |
|  16 |        VIEW                  | index$_join$_006      |  2119 | 16952 |       |     3  (34)| 00:00:01 |       |       |
|* 17 |         HASH JOIN            |                       |       |       |       |            |          |       |       |
|  18 |          INDEX FAST FULL SCAN| T_CYCLE_CONTROL_PK    |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|  19 |          INDEX FAST FULL SCAN| T_CYCLE_CONTROL_1IX   |  2119 | 16952 |       |     1   (0)| 00:00:01 |       |       |
|* 20 |        TABLE ACCESS FULL     | T_CYCLE_GROUPS        |  8862 |  1064K|       |    45   (0)| 00:00:01 |       |       |
|  21 |      VIEW                    |                       |  1014K|    26M|       | 13656   (1)| 00:02:44 |       |       |
|  22 |       HASH GROUP BY          |                       |  1014K|    48M|    62M| 13656   (1)| 00:02:44 |       |       |
|  23 |        JOIN FILTER USE       | :BF0000               |  1014K|    48M|       |  1170   (1)| 00:00:15 |       |       |
|  24 |         NESTED LOOPS         |                       |  1014K|    48M|       |  1170   (1)| 00:00:15 |       |       |
|  25 |          PARTITION RANGE ALL |                       |  1014K|    26M|       |  1165   (0)| 00:00:14 |     1 |    19 |
|  26 |           INDEX FULL SCAN    | T_GROUP_STATUS_PK     |  1014K|    26M|       |  1165   (0)| 00:00:14 |     1 |    19 |
|* 27 |          INDEX UNIQUE SCAN   | T_CYCLE_GROUPS_PK     |     1 |    23 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

可以看到，执行计划里面的预估，资源消耗是很严重的的，幸好涉及的几个表数据量都不算大。
根据数据量和执行计划可以得到。执行计划和实际的执行情况有很大的差别。实际执行时，返回的最终数据量只有9000多条，但是根据执行计划却有400多万。

其实问题可以归结为下面的一个子查询。
子查询中，因为t_cycle_groups含有clob字段且要作为最终的输出，clob字段不能做order by/group by的操作。所以开发最开始就先根据主键的情况和t_group_status来做关联(t_group_status中有100多万条数据),同时匹配t_group_status中的一个字段，过滤掉多余的数据记录，然后在子查询外再和表t_group_status做关联输出clob字段。
下面的例子中，子查询的别名为a,在子查询中根据主键和大表做关联，输出了小表的所有主键列和大表的一个列。大表的这个列和小表的主键列匹配会有很多冗余数据，需要做group by，在外层又重新和t_cycle_groups做关联。
这个操作可以打个比方，比如我有一张信用卡，在这个月的10,11,12,14号每天都刷了3次卡，现在就是想输出我在哪些天刷了卡，只需要输出10,11,12,14就可以，不需要输出每次刷卡的具体时间。

select t_cycle_groups.*, a.request_id
          from (select t_cycle_groups.group_id,
                       t_cycle_groups.flow_id,
                       t_cycle_groups.cycle_seq_no,
                       t_cycle_groups.route,
                       t_group_status.request_id
                  from t_cycle_groups, --8000多条数据
                       t_group_status  --100多万条数据
                 where t_cycle_groups.group_id = t_group_status.group_id
                   AND t_cycle_groups.flow_id = t_group_status.flow_id
                   AND t_cycle_groups.cycle_seq_no =
                       t_group_status.cycle_seq_no
                   AND t_cycle_groups.route = t_group_status.route
                 group by t_cycle_groups.group_id,
                          t_cycle_groups.flow_id,
                          t_cycle_groups.cycle_seq_no,
                          t_cycle_groups.route,
                          t_group_status.request_id) a,
               t_cycle_groups
         where t_cycle_groups.group_id = a.group_id
           AND t_cycle_groups.flow_id = a.flow_id
           AND t_cycle_groups.cycle_seq_no = a.cycle_seq_no
           AND t_cycle_groups.route = a.route

优化后的子查询如下：
因为主键列是固定的，我直接使用rowid来替代。这样过group by的时候会减少很多的数据过滤，在子查询过滤了最多的数据之后才和表重新关联，最后输出clob字段。
这样就避免了反复比较主键列，过多复杂的数据group by。

select T_cycle_groups.flow_id,
               temp.request_id,
               T_cycle_groups.dynamic_attributes,
               T_cycle_groups.sys_creation_date,
               T_cycle_groups.sys_update_date,
               T_cycle_groups.cycle_seq_no
          from (select T_cycle_groups.rowid temp_id,
                       T_group_status.request_id
                  from T_cycle_groups, T_group_status
                 where T_cycle_groups.group_id = T_group_status.group_id
                   AND T_cycle_groups.flow_id = T_group_status.flow_id
                   AND T_cycle_groups.cycle_seq_no =
                       T_group_status.cycle_seq_no
                   AND T_cycle_groups.route = T_group_status.route
                 group by T_cycle_groups.rowid, T_group_status.request_id) temp,
               T_cycle_groups
         where temp.temp_id = T_cycle_groups.rowid
           and T_cycle_groups.status = 'FIN'

改进后再次查询，整个查询的性能就好多了，从执行计划来说，资源的消耗就比较合理了。

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                       |     2 |   332 |  1277   (4)| 00:00:16 |       |       |
|   1 |  SORT ORDER BY             |                       |     2 |   332 |  1277   (4)| 00:00:16 |       |       |
|*  2 |   HASH JOIN                |                       |     2 |   332 |  1276   (4)| 00:00:16 |       |       |
|*  3 |    TABLE ACCESS FULL       | T_RUN_REQUEST         |  6038 |   112K|    23   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN               |                       |  3224 |   462K|  1253   (4)| 00:00:16 |       |       |
|   5 |     VIEW                   | index$_join$_006      |  2119 | 16952 |     3  (34)| 00:00:01 |       |       |
|*  6 |      HASH JOIN             |                       |       |       |            |          |       |       |
|   7 |       INDEX FAST FULL SCAN | T_CYCLE_CONTROL_PK    |  2119 | 16952 |     1   (0)| 00:00:01 |       |       |
|   8 |       INDEX FAST FULL SCAN | T_CYCLE_CONTROL_1IX   |  2119 | 16952 |     1   (0)| 00:00:01 |       |       |
|*  9 |     HASH JOIN              |                       |  3238 |   439K|  1249   (4)| 00:00:15 |       |       |
|  10 |      VIEW                  |                       |  3252 | 52032 |  1204   (4)| 00:00:15 |       |       |
|  11 |       HASH GROUP BY        |                       |  3252 |   196K|  1204   (4)| 00:00:15 |       |       |
|  12 |        NESTED LOOPS        |                       |  1014K|    59M|  1170   (1)| 00:00:15 |       |       |
|  13 |         PARTITION RANGE ALL|                       |  1014K|    26M|  1165   (0)| 00:00:14 |     1 |    19 |
|  14 |          INDEX FULL SCAN   | T_GROUP_STATUS_PK     |  1014K|    26M|  1165   (0)| 00:00:14 |     1 |    19 |
|* 15 |         INDEX UNIQUE SCAN  | T_CYCLE_GROUPS_PK     |     1 |    35 |     1   (0)| 00:00:01 |       |       |
|* 16 |      TABLE ACCESS FULL     | T_CYCLE_GROUPS        |  8862 |  1064K|    45   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

查询的执行时间也保持在5秒左右。

本文转自ICT时空dbasdk的博客，原文链接：生产环境sql语句调优实战第四篇 ，如需转载请自行联系原博主。

文章标签：
SQL
Java
数据库连接
关键词：
SQL调优
SQL实战
SQL生产环境
SQL语句
生产环境SQL语句调优实战
青夜之衫
目录
相关文章
程序员小海绵
|
20天前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
MySQL调优主要分为三个步骤：监控报警、排查慢SQL、MySQL调优。 排查慢SQL：开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优： 基础优化：缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表； 表设计优化：数据类型优化、冷热数据分表等。 索引优化：考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
程序员小海绵
167 15
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
Z_sorrain
|
22天前
|
SQL 分布式计算 数据库
SQL调优总结
数据库表的规范化和反规范化设计，设计合适的字段数据类型……
Z_sorrain
33 8
土木林森
|
2月前
|
SQL 存储 数据处理
"SQL触发器实战大揭秘：一键解锁数据自动化校验与更新魔法，让数据库管理从此告别繁琐，精准高效不再是梦！"
【8月更文挑战第31天】在数据库管理中，确保数据准确性和一致性至关重要。SQL触发器能自动执行数据校验与更新，显著提升工作效率。本文通过一个员工信息表的例子，详细介绍了如何利用触发器自动设定和校验薪资，确保其符合业务规则。提供的示例代码展示了在插入新记录时如何自动检查并调整薪资，以满足最低标准。这不仅减轻了数据库管理员的负担，还提高了数据处理的准确性和效率。触发器虽强大，但也需谨慎使用，以避免复杂性和性能问题。
土木林森
34 1
蓝易云
|
1月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能，它能够帮助数据库管理员在数据丢失或损坏的情况下，有效地恢复数据。通过定期备份数据库和事务日志，并在需要时按照正确的步骤恢复，可以最大限度地减少数据丢失的风险。需要注意的是，恢复数据是一个需要谨慎操作的过程，建议在执行恢复操作之前，详细了解相关的操作步骤和注意事项，以确保数据的安全和完整。
蓝易云
59 0
土木林森
|
2月前
|
测试技术 Java
全面保障Struts 2应用质量：掌握单元测试与集成测试的关键策略
【8月更文挑战第31天】Struts 2 的测试策略结合了单元测试与集成测试。单元测试聚焦于单个组件（如 Action 类）的功能验证，常用 Mockito 模拟依赖项；集成测试则关注组件间的交互，利用 Cactus 等框架确保框架拦截器和 Action 映射等按预期工作。通过确保高测试覆盖率并定期更新测试用例，可以提升应用的整体稳定性和质量。
土木林森
59 0
土木林森
|
2月前
|
数据库 Java 监控
Struts 2 日志管理化身神秘魔法师，洞察应用运行乾坤，演绎奇幻篇章！
【8月更文挑战第31天】在软件开发中，了解应用运行状况至关重要。日志管理作为 Struts 2 应用的关键组件，记录着每个动作和决策，如同监控摄像头，帮助我们迅速定位问题、分析性能和使用情况，为优化提供依据。Struts 2 支持多种日志框架（如 Log4j、Logback），便于配置日志级别、格式和输出位置。通过在 Action 类中添加日志记录，我们能在开发过程中获取详细信息，及时发现并解决问题。合理配置日志不仅有助于调试，还能分析用户行为，提升应用性能和稳定性。
土木林森
38 0
土木林森
|
2月前
|
前端开发 Java JSON
Struts 2携手AngularJS与React：探索企业级后端与现代前端框架的完美融合之道
【8月更文挑战第31天】随着Web应用复杂性的提升，前端技术日新月异。AngularJS和React作为主流前端框架，凭借强大的数据绑定和组件化能力，显著提升了开发动态及交互式Web应用的效率。同时，Struts 2 以其出色的性能和丰富的功能，成为众多Java开发者构建企业级应用的首选后端框架。本文探讨了如何将 Struts 2 与 AngularJS 和 React 整合，以充分发挥前后端各自优势，构建更强大、灵活的 Web 应用。
土木林森
41 0
土木林森
|
2月前
|
Java XML Maven
跨越时代的飞跃：Struts 2 升级秘籍——从旧版本无缝迁移到最新版，焕发应用新生！
【8月更文挑战第31天】随着软件技术的发展，Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本，并执行 `mvn clean install`。接着检查 `struts.xml` 配置，确保符合新版本要求，调整包扫描器等设置。审查 Action 类及其注解，检查配置文件中的弃用项及插件。更新自定义拦截器实现，并验证日志配置。最后，通过一系列测试确保升级后的系统正常运行。通过这些步骤，可以顺利完成 Struts 2 的版本升级，提升应用的安全性和性能。
土木林森
94 0
土木林森
|
2月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战，Web 开发的未来何去何从？
【8月更文挑战第31天】在Web应用开发中，Struts 2框架因强大功能和灵活配置备受青睐，但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案，如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`<s:checkbox>`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等，助您有效应对挑战。
土木林森
67 0
土木林森
|
2月前
|
SQL 监控 关系型数据库
SQL性能监控与调优工具的神奇之处：如何用最佳实践选择最适合你的那一个，让你的数据库飞起来？
【8月更文挑战第31天】在现代软件开发中，数据库性能监控与调优对应用稳定性至关重要。本文对比了数据库内置工具、第三方工具及云服务工具等几种常用SQL性能监控与调优工具，并通过示例代码展示了如何利用MySQL的EXPLAIN功能分析查询性能。选择最适合的工具需综合考虑功能需求、数据库类型及成本预算等因素。遵循了解工具功能、试用工具及定期维护工具等最佳实践，可帮助开发者更高效地管理和优化数据库性能，迎接未来软件开发中的挑战与机遇。
土木林森
35 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    SQL Server中使用数据库快照的方式来完成测试环境中数据库的轻量级备份还原操作
  • 3
    SQL Server 查看备份集元数据的 4 种方法
  • 4
    SQL点滴23—T-SQL中的除法
  • 5
    SQL Server 事务嵌套
  • 6
    笨办法学 Python · 续 练习 40：SQL 读取
  • 7
    SQL FOR XML
  • 8
    一次SQL_ID和HASH_VALUE转换尝试引发的误区
  • 9
    LINQ to SQL语句 Union/Intersect/Except
  • 10
    SQL Server 2008连接局域网内的SQL Server 2000数据库
  • 1
    SQL注入的实现原理以及防止
    85
  • 2
    docker如何进入镜像其他的SQL
    88
  • 3
    sql如何新建数据库实例
    113
  • 4
    [MySQL]SQL优化之索引的使用规则
    70
  • 5
    [MySQL] SQL优化之性能分析
    72
  • 6
    sql文件批处理程序-java桌面应用
    51
  • 7
    《牛客笔试选择题》sql错题集
    74
  • 8
    AI代码生成器——AI2sql
    464
  • 9
    10个高级的 SQL 查询技巧
    105
  • 10
    【问题】Cause: java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
    361

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    无影云桌面