联系生活来简化sql(r3笔记第43天）
联系生活来简化sql(r3笔记第43天）
jeanron100
发布于 2018-03-14 18:08:45
发布于 2018-03-14 18:08:45
代码可运行
运行总次数：0
代码可运行
目前生产环境中有一条sql语句的CPU消耗很高。执行时间比较长。从awr中抓到的sql语句如下：
SELECT run_request.run_mode, cycle_groups.flow_id,
cycle_groups.request_id, cycle_groups.dynamic_attributes,
cycle_groups.sys_creation_date, cycle_groups.sys_update_date,
cycle_control.cycle_code
FROM (SELECT cycle_groups.*, a.request_id
FROM (SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --数据量在1万左右
group_status --数据量在百万
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --这些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --这些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_ghttp://blog.itpub.net/23718752/viewspace-1317283/roups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id) a,
cycle_groups
WHERE cycle_groups.GROUP_ID = a.GROUP_ID --这个地方又关联了一次
AND cycle_groups.flow_id = a.flow_id
AND cycle_groups.cycle_seq_no = a.cycle_seq_no
AND cycle_groups.route = a.route) cycle_groups,
cycle_control,
run_request
WHERE cycle_groups.status = 'FIN'
AND cycle_groups.request_id = run_request.request_id
AND cycle_control.cycle_seq_no = cycle_groups.cycle_seq_no
AND cycle_control.cycle_code
|| '_'
|| cycle_groups.flow_id
|| '_'
|| run_request.run_mode IN (
SELECT cycle_control.cycle_code
|| '_'
|| run_request.flow_id
|| '_'
|| run_request.run_mode
FROM run_request, cycle_control
WHERE ( run_request.population_type = 'CYC'
OR run_request.population_type = 'CCD'
)
AND run_request.population_id =cycle_control.cycle_seq_no)
ORDER BY cycle_groups.request_id从第一印象来看,sql语句有些臃肿，过滤条件也比较奇怪。 首先是标黄的部分，关联的连接条件都是索引列. 执行计划如下，可以看到还是消耗很大的。
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 273K(100)| | | |
| 1 | SORT ORDER BY | | 5714K| 1111M| 1174M| 273K (1)| 00:54:46 | | |
|* 2 | HASH JOIN | | 5714K| 1111M| | 19993 (1)| 00:04:00 | | |
| 3 | VIEW | VW_NSO_1 | 423 | 13959 | | 28 (11)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 423 | 12690 | | 28 (11)| 00:00:01 | | |
|* 5 | HASH JOIN | | 6992 | 204K| | 26 (4)| 00:00:01 | | |
| 6 | VIEW | index$_join$_009 | 2157 | 17256 | | 3 (34)| 00:00:01 | | |
|* 7 | HASH JOIN | | | | | | | | |
| 8 | INDEX FAST FULL SCAN | CYCLE_CONTROL_PK | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
| 9 | INDEX FAST FULL SCAN | CYCLE_CONTROL_1IX | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | RUN_REQUEST | 7641 | 164K| | 23 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 1350K| 220M| | 19944 (1)| 00:04:00 | | |
| 12 | TABLE ACCESS FULL | RUN_REQUEST | 7735 | 92820 | | 23 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 1350K| 204M| | 19916 (1)| 00:03:59 | | |
| 14 | JOIN FILTER CREATE | :BF0000 | 12436 | 1590K| | 70 (2)| 00:00:01 | | |
|* 15 | HASH JOIN | | 12436 | 1590K| | 70 (2)| 00:00:01 | | |
| 16 | VIEW | index$_join$_006 | 2157 | 17256 | | 3 (34)| 00:00:01 | | |
|* 17 | HASH JOIN | | | | | | | | |
| 18 | INDEX FAST FULL SCAN| CYCLE_CONTROL_PK | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
| 19 | INDEX FAST FULL SCAN| CYCLE_CONTROL_1IX | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS FULL | CYCLE_GROUPS | 13072 | 1570K| | 67 (0)| 00:00:01 | | |
| 21 | VIEW | | 1426K| 38M| | 19840 (1)| 00:03:59 | | |
| 22 | HASH GROUP BY | | 1426K| 69M| 87M| 19840 (1)| 00:03:59 | | |
| 23 | JOIN FILTER USE | :BF0000 | 1426K| 69M| | 1746 (1)| 00:00:21 | | |
| 24 | NESTED LOOPS | | 1426K| 69M| | 1746 (1)| 00:00:21 | | |
| 25 | PARTITION RANGE ALL | | 1426K| 38M| | 1740 (1)| 00:00:21 | 1 | 19 |
| 26 | INDEX FULL SCAN | GROUP_STATUS_PK | 1426K| 38M| | 1740 (1)| 00:00:21 | 1 | 19 |
|* 27 | INDEX UNIQUE SCAN | CYCLE_GROUPS_PK | 1 | 23 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------这个调优可以从一个生活的例子来简单说明。 我平时早餐吃面包，就举吃面包的例子吧。 比如我在所住的校区门口有家面包店，每次我买面包都会得到一个收据，上面有所买的面包明细和卡号，还有一个流水号。 在公司楼下也有一个面包店，都可以用同一张卡来消费，也会得到一个流水号。 可以关联起来，表cycle_groups就类似一个客户清单，清单中的每一项就代表一个客户卡号。而表group_status就代表消费的明细，比如流水号，所卖的面包和所消费的金额时间等。 cycle_group里只有卡号等信息，在cycle_status中是消费的数据。 这个时候我们想得到某张卡号 消费的情况，比如卡号，年月日，消费的金额，如果流水号相同只输出一个流水号。 这个操作就类似下面的形式。
SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --数据量在1万左右
group_status --数据量在百万
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --这些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --这些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id而这个时候我们得到了一个卡号，消费年月日，消费流水号的清单，流水号相同的情况下，可以只得到50万条记录。 这个时候我们想给予目前的统计结果，得到卡号，消费的门店，消费金额，消费流水号时，这个时候就相当于根据流水号把表cycle_status里面的数据又重新查取了一遍。 这个时候你想想，还不如直接关联卡号和消费记录来查取一次呢。因为，在子查询里过滤了流水号，只显示一条不重复的流水号，但是外部查询中又需要得到更为详细的信息，导致过滤了一次数据，然后又重新还原了一遍。
SELECT cycle_groups.*, a.request_id
FROM (SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --数据量在1万左右
group_status --数据量在百万
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --这些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --这些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id) a,
cycle_groups
WHERE cycle_groups.GROUP_ID = a.GROUP_ID --这个地方又关联了一次
AND cycle_groups.flow_id = a.flow_id
AND cycle_groups.cycle_seq_no = a.cycle_seq_no
AND cycle_groups.route = a.route
所以可能自己想了很多的思路，但是最后还是又把问题回归到原点。本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2014-11-01，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
