数据分布不均匀走HASH JOIN导致的性能问题

这个案例是JAVA开发说一个存储过程跑的很慢,之后我跑这个过程,然后通过脚本抓出了慢的SQL

表大小

1
2
3
tb_user_channel --1W
tb_channel_info --1W
base_data_login_info 19W

就是这条SQL,跑完要7分钟。base_data_login_info本来是@db_link,但是我在本地建了一个同样的表发现还是7分钟左右,所以排除了可能是由于db_link造成问题的可能性

1
2
3
4
5
6
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

看一下这个sql返回多少行,结果秒杀,瞬间就出结果了

1
2
3
4
5
6
select
count(*)
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

45122行
之后单独跑

1
2
3
4
5
6
select
count(distinct a.user_name),count( a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform


1
2
3
4
5
6
select
count( a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

都是秒杀
单独count distinct user_name 或 invest_id 都很快 ,一起count distinct就很慢了
那么这时候其实已经可以通过改写SQL提示性能了,改写如下

阅读全文

视图合并、hash join连接列数据分布不均匀引发的惨案

表大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SQL> select count(*) from agent.TB_AGENT_INFO;
COUNT(*)
----------
1751
SQL> select count(*) from TB_CHANNEL_INFO ;
COUNT(*)
----------
1807
SQL> select count(*) from TB_USER_CHANNEL;
COUNT(*)
----------
7269
SQL> select count(*) from OSS_USER_STATION;
COUNT(*)
----------
2149
SQL> select count(*) from tb_user_zgy ;
COUNT(*)
----------
43
SQL> select count(*) from act.tb_user_agent_relat;
COUNT(*)
----------
29612
SQL> select count(*) from agent.base_data_user_info ;
COUNT(*)
----------
30005
SQL> select count(*) from agent.base_data_invest_info;
COUNT(*)
----------
3530163

慢的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
select a.city,
a.agent_id,
a.username,
a.real_name,
phone,
zgy_name,
login_count,
user_count,
count(distinct b.invest_id) user_invested,
sum(b.order_amount / 100) invest_amount
from (select a.city,
a.agent_id,
a.username,
a.real_name, -- 业主姓名
a.phone, -- 业主手机号
d.real_name zgy_name, -- 所属专管员
count(distinct case
when c.str_day <= '20160821' then
c.login_name
end) login_count,
count(distinct case
when c.str_day <= '20160821' then
decode(c.status, 1, c.invest_id, null)
end) user_count
from (select agent_id, city, username, real_name, phone
from agent.TB_AGENT_INFO
where agent_id in
(SELECT agent_id
FROM (SELECT distinct *
FROM TB_CHANNEL_INFO t
START WITH t.CHANNEL_ID in
(select CHANNEL_ID
from TB_USER_CHANNEL
where USER_ID = 596)
CONNECT BY PRIOR
t.CHANNEL_ID = t.PARENT_CHANNEL_ID)
WHERE agent_id IS NOT NULL)) a
left join oss_user_station e
on a.agent_id = e.agent_id
and e.user_type = 0
left join tb_user_zgy d
on e.username = d.username
left join act.tb_user_agent_relat c
on a.agent_id = c.agent_id
group by a.city,
a.username,
a.real_name,
a.phone,
d.real_name,
a.agent_id) a
left join (select invest_id, order_amount, agent_id, str_day
from agent.base_data_invest_info
where str_day >= '20150801' and str_day<='20160821') b
on a.agent_id = b.agent_id
group by a.city,
a.agent_id,
a.username,
a.real_name,
a.phone,
a.zgy_name,
a.login_count,
a.user_count
这个查询可以看成两部分,第一部分一堆小表关联的a和唯一的一个大表再做关联
man
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 1 | HASH GROUP BY | | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 2 | VIEW | VW_DAG_1 | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 3 | HASH GROUP BY | | 55M| 6301M| 7681M| 3934K (1)| 13:06:52 |
| 4 | VIEW | VM_NWVW_0 | 55M| 6301M| | 2456K (1)| 08:11:15 |
| 5 | SORT GROUP BY | | 55M| 10G| 11G| 2456K (1)| 08:11:15 |
|* 6 | HASH JOIN RIGHT OUTER | | 55M| 10G| | 21643 (2)| 00:04:20 |
| 7 | TABLE ACCESS FULL | TB_USER_AGENT_RELAT | 27937 | 1200K| | 102 (0)| 00:00:02 |
|* 8 | HASH JOIN OUTER | | 3374K| 511M| | 21392 (1)| 00:04:17 |
|* 9 | HASH JOIN SEMI | | 1712 | 188K| | 2007 (1)| 00:00:25 |
|* 10 | HASH JOIN RIGHT OUTER | | 1712 | 173K| | 32 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | TB_USER_ZGY | 43 | 903 | | 3 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 1712 | 138K| | 29 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | OSS_USER_STATION | 1075 | 25800 | | 6 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TB_AGENT_INFO | 1712 | 98K| | 23 (0)| 00:00:01 |
| 15 | VIEW | VW_NSO_1 | 16271 | 143K| | 1975 (1)| 00:00:24 |
|* 16 | VIEW | | 16271 | 143K| | 1975 (1)| 00:00:24 |
| 17 | HASH UNIQUE | | 16271 | 8882K| 10M| 1975 (1)| 00:00:24 |
|* 18 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | | |
|* 19 | HASH JOIN RIGHT SEMI | | 530 | 146K| | 29 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | TB_USER_CHANNEL | 600 | 7800 | | 7 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 1807 | 476K| | 22 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 1807 | 476K| | 22 (0)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | BASE_DATA_INVEST_INFO | 3374K| 148M| | 19375 (1)| 00:03:53 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AGENT_ID"="C"."AGENT_ID"(+))
8 - access("AGENT_ID"="AGENT_ID"(+))
9 - access("AGENT_ID"="AGENT_ID")
10 - access("C"."USERNAME"="D"."USERNAME"(+))
12 - access("AGENT_ID"="C"."AGENT_ID"(+))
13 - filter("C"."USER_TYPE"(+)=0)
16 - filter("AGENT_ID" IS NOT NULL)
18 - access("T"."PARENT_CHANNEL_ID"=PRIOR "T"."CHANNEL_ID")
19 - access("T"."CHANNEL_ID"="CHANNEL_ID")
20 - filter("USER_ID"=596)
23 - filter("STR_DAY"(+)>='20150801' AND "STR_DAY"(+)<='20160821')

阅读全文

抓跑得慢的SQL,查看正在跑的sql的等待事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select (sysdate-a.logon_time)*24*60 minutes,
a.username,
a.BLOCKING_INSTANCE,
a.BLOCKING_SESSION,
a.program,
a.machine,
a.osuser,
a.status,
a.sid,
a.serial#,
a.event,
a.p1,
a.p2,
a.p3,
a.sql_id,
a.sql_child_number,
b.sql_text
from v$session a, v$sql b
where
a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_child_number=b.child_number
and a.event not in ('SQL*Net message from client','Space Manager: slave idle wait')
-- and a.username like '%USERNAME%'
order by 1 desc;

在查执行计划

1
Select * from table(dbms_xplan.display_cursor('91prd0dh0bs8d',0,'ALL'));

Hexo博客配置

Hexo博客配置

基于hexo-theme-hiker主题

先看myblog下的_config.yml完整配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# Hexo Configuration
## Docs: https://hexo.io/docs/configuration.html
## Source: https://github.com/hexojs/hexo/
# Site
title: Fan()
subtitle:
description:
author: a dba's blog
language: zh-CN
timezone: Asia/Shanghai
# URL
## If your site is put in a subdirectory, set url as 'http://yoursite.com/child' and root as '/child/'
url: http://yoursite.com
root: /Fandb.github.io
permalink: :year/:month/:day/:title/
permalink_defaults:
# Directory
source_dir: source
public_dir: public
tag_dir: tags
archive_dir: archives
category_dir: categories
code_dir: downloads/code
i18n_dir: :lang
skip_render:
# Writing
new_post_name: :title.md # File name of new posts
default_layout: post
titlecase: false # Transform title into titlecase
external_link: true # Open external links in new tab
filename_case: 0
render_drafts: false
post_asset_folder: false
relative_link: false
future: true
highlight:
enable: true
line_number: true
auto_detect: true
tab_replace:
# Home page setting
# path: Root path for your blogs index page. (default = '')
# per_page: Posts displayed per page. (0 = disable pagination)
# order_by: Posts order. (Order by date descending by default)
index_generator:
path: ''
per_page: 6
order_by: -date
# Category & Tag
default_category: uncategorized
category_map:
tag_map:
# Date / Time format
## Hexo uses Moment.js to parse and display date
## You can customize the date format as defined in
## http://momentjs.com/docs/#/displaying/format/
date_format: YYYY-MM-DD
time_format: HH:mm:ss
# Pagination
## Set per_page to 0 to disable pagination
per_page: 10
pagination_dir: page
# Extensions
## Plugins: https://hexo.io/plugins/
## Themes: https://hexo.io/themes/
theme: hiker
# Deployment
## Docs: https://hexo.io/docs/deployment.html
deploy:
type: git
repository:
github: https://github.com/Fanduzi/Fandb.github.io.git
coding: https://git.coding.net/Fandb/Fandb.blog.git
branch: master

Note

冒号后面要空一格

阅读全文

使用Hexo + github快速搭建个人博客

使用Hexo + github快速搭建个人博客

安装依赖软件

安装git

1
sudo brew install git

安装Node.js

Mac下最简单的做法便是直接下载pkg文件进行安装,最新版本的下载地址如下,选择后缀为pkg的文件下载安装即可:
https://nodejs.org/download/release/latest/
安装完成后修改环境变量

1
2
3
vi ~/.bash_profile
添加
export PATH=/usr/local/bin:$PATH

将npm源替换成淘宝源

1
npm config set registry http://registry.npm.taobao.org/

安装Hexo

安装前先介绍几个hexo常用的命令

1
2
3
4
$ hexo g #完整命令为hexo generate,用于生成静态文件
$ hexo s #完整命令为hexo server,用于启动服务器,主要用来本地预览
$ hexo d #完整命令为hexo deploy,用于将本地文件发布到github上
$ hexo n #完整命令为hexo new,用于新建一篇文章

利用 npm 命令安装:

1
sudo npm install -g hexo

报错可尝试

1
npm install --unsafe-perm -g hexo

阅读全文

Percona Monitoring and Management架构

Percona Monitoring and Management架构

PMM基于简单的client-server模型,可实现高效的扩展性,它包含以下模块:

  • PMM Client,安装在任何你希望被监控的数据库服务器上.它会手机服务器指标和查询分析数据以提供一份完整的性能概览.数据被收集并发送到PMM Server
  • PMM Server是PMM的核心部分,它聚合手机的数据,并以Web界面的表格,仪表盘和图形的形式展现

这些模块被封装以提供简单的安装和使用给用户,无需关心它的内部实现方法.但是,如果要利用PMM的全部潜力,内部结构就很重要.

PMM是旨在无缝协同工作的工具集合。 一些是由Percona开发的,一些是第三方开源工具。

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2019 Fan() All Rights Reserved.

访客数 : | 访问量 :

#