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

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

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

生产中有一条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语句
青夜之衫
目录
相关文章
最好zzz
|
6天前
|
SQL 缓存 监控
SQL性能提升指南：五大优化策略与十个实战案例
在数据库性能优化的世界里，SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载，提高应用响应速度，甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤，并结合十个实战案例，为你提供一份详尽的性能提升指南。
最好zzz
18 0
程序员小海绵
|
2月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
MySQL调优主要分为三个步骤：监控报警、排查慢SQL、MySQL调优。 排查慢SQL：开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优： 基础优化：缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表； 表设计优化：数据类型优化、冷热数据分表等。 索引优化：考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
程序员小海绵
558 15
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
何雨晨
|
1月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
55 1
游客qsxez56gggwqy
|
1月前
|
SQL 关系型数据库 MySQL
sql注入原理与实战（三）数据库操作
sql注入原理与实战（三）数据库操作
游客qsxez56gggwqy
13 0
sql注入原理与实战（三）数据库操作
1100237741946300
|
1月前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化：提升数据库性能的实战技巧
在数据库管理和应用中，SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
1100237741946300
190 0
1100237741946300
|
1月前
|
SQL 监控 关系型数据库
SQL语句性能分析：实战技巧与详细方法
在数据库管理中，分析SQL语句的性能是优化数据库查询、提升系统响应速度的重要步骤
1100237741946300
246 0
Z_sorrain
|
2月前
|
SQL 分布式计算 数据库
SQL调优总结
数据库表的规范化和反规范化设计，设计合适的字段数据类型……
Z_sorrain
45 8
游客qsxez56gggwqy
|
1月前
|
SQL 关系型数据库 Serverless
sql注入原理与实战（四）数据表操作
sql注入原理与实战（四）数据表操作
游客qsxez56gggwqy
12 0
游客qsxez56gggwqy
|
1月前
|
SQL 存储 Java
sql注入原理与实战（二）数据库原理
sql注入原理与实战（二）数据库原理
游客qsxez56gggwqy
17 0
游客qsxez56gggwqy
|
1月前
|
SQL 前端开发 安全
sql注入原理与实战（一）
sql注入原理与实战（一）
游客qsxez56gggwqy
64 0

热门文章

最新文章

  • 1
    SQL0286N. DB2表空间的pagesize问题
  • 2
    SQL Server 多表数据增量获取和发布 4
  • 3
    SQL Server 2008 存储结构之DCM、BCM
  • 4
    B+树查询上下级(sql实现）
  • 5
    SQL Server 存储过程的运用
  • 6
    计算日期段内有几个星期几(SQL, Delphi) ,日期段内有几个星期天
  • 7
    sql语句查询数据库表结构信息
  • 8
    SQL中几个快速定位语句
  • 9
    扩展Exception，增加判断Exception是否为SQL引用约束异常方法！
  • 10
    tempdb对SQL Server性能的影响 <转>
  • 1
    SQL脚本把多行SQL数据变成一条多列数据
    113
  • 2
    mybatis动态sql
    43
  • 3
    基于若依的ruoyi-nbcio流程管理系统修改代码生成的sql菜单id修改成递增id（谨慎修改，大并发分布式有弊端）
    99
  • 4
    SQL脚本字符串替换
    70
  • 5
    你写的每条SQL都是全表扫描吗
    179
  • 6
    数据库SQL语言实战（六）
    48
  • 7
    数据库SQL语言实战（五）(数据库系统概念第三章练习题）
    43
  • 8
    数据库SQL语言实战（四）(数据库系统概念第三章练习题）
    48
  • 9
    数据库SQL语言实战（三）
    47
  • 10
    Sql中如何添加数据
    70

    • 相关课程

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

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

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