本文从BI生成 sql 的原理解析入手,结合实际项目中报表设计优化的典型案例,系统讲解了BI分析表设计中性能攸关的设计要素。
一张分析单主题表的典型sql语句如下:
SELECT id,avg(sal) avgsal
FROM table
WHERE id<>"0000"
GROUP BY id
Having avg(sal)>5000
ORDER BY avgsal
这段语句中的粗黑体是sql语句的保留关键字,他们是sql语句的主体骨架,蓝色部分是查询统计的具体数据库表、字段、过滤条件、分组和排序依据;将蓝色部分"翻译"成BI分析表中的设计元素后,sql语句如下:
SELECT 表元表达式
FROM 主题表
WHERE 过滤条件 (含表元、维表元、主题集过滤条件及数据期条件)
GROUPBY 维表元(带下钻级次)
HAVING 结果集过滤
ORDERBY 排序依据
可以很清楚地看到,报表工程师们在制作分析表,设计报表和表元的各种属性时,其实是在间接地编写着BI分析报表的sql;因此BI中sql语句性能的好坏,报表工程师有很大的决定权。
如果分析表的数据来源于多个主题,BI分析表的sql需要将多张主题表连接;典型的sql有两种:
......
(SELECT 表元表达式
FROM 主题表1
WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)
GROUPBY 维表元 (带下钻级次)
) T1
JOIN (支持INNER JOIN、RIGHT JOIN、LEFT JOIN、UNION ALL)
(SELECT 表元表达式
FROM 主题表2
WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)
GROUPBY 维表元(带下钻级次)
)T2
ON T1.维表元字段 = T2.维表元字段 ......
红色部分是相对单主题sql新增加的典型元素;其中join方式可以是内连接、左连接、右连接、全连接,具体是哪种取决于主题集属性中的OLAP连接计算规则,如图:
有些分析需要在两个主题的明细数据上进行运算和关联,此时不得不在sql中将两个主题表先连接再分组统计;其典型的sql如下:
......
SELECT主题表1. 表元表达式1 ,主题表1. 表元表达式2 ,主题表2. 表元表达式1
FROM 主题表1
JOIN(支持INNER JOIN、RIGHT JOIN、LEFT JOIN、UNION ALL)
主题表2
ON主题表1.关联字段1=主题表2.关联字段1...
WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)
GROUPBY 维表元(带下钻级次) ......
从表面上看,此处的红色部分新增元素好像和【先分组后连接】差不多,是不是两种sql性能相当呢?
让我们一起考虑一下这样的场景,主题表A和B均有10w行明细数据,需要按行业大类(20行)统计来自A和B的某些指标;
【先连接后分组】需要首先将两个10w行规模的明细表join起来,之后再按行业分组;
【先分组后连接】会分别在主题A和主题B上分组后再按行业大类join,此时连接的仅仅只是两个20行的子表;性能相比,孰优孰劣,一目了然!
【先连接后分组】方式下,主题明细数据表连接的方式由报表设计中的什么元素决定呢?请看下图,是主题集属性中的表关联设置:
查看分析表的【详细信息】,有时我们会发现为了计算一张分析表,BI生成了多个子sql嵌套和连接,或者干脆是多个独立的sql,为什么会这样呢?
要弄清这个问题,咱们得先回头再看看之前提到的sql语句:
SELECT表元表达式
FROM 主题表
WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)
GROUPBY 维表元(带下钻级次)
HAVING 结果集过滤
ORDERBY 排序依据
一个这样的Sql语句可以查询出满足where子句条件的一批指标,如果有指标虽然来自同一个主题,但过滤条件不一样,还能用这一个sql一并统计出来吗?
假设现在需要统计主题表1中的多个指标,对zb1,zb2zbm的统计sql如下:
SELECT zb1,zb2...zbm
FROM 主题表1
WHERE filter1
主题表1的【zbn】也需要统计分析,其过滤条件是filter2,和其他指标zb1,zb2都不一样,那么对zbn的统计能和zb1,zb2等指标一起放在一个sql中吗?
答案很显然,不能!否则算出来的zbn就是filter1过滤后的统计值,而不是分析需求filter2的。
因此,如果在BI分析表上我们为表元zbn设置了特殊的过滤条件,BI的sql一定需要单独为这个指标生成一个子sql,比如:
SELECT zbn
FROM 主题表1
WHERE filter2
在sql元素中,除了过滤条件之外,还有连接方式的不统一也会造成BI生成多个子sql。正常情况下,假设A和B主题表连接分析的sql如下:
SELECT主题表A.zb ...主题表B.zb1 ,主题表B.zb2
FROM 主题表A INNERJ OIN 主题表B
ON主题表A.关联字段1=主题表B.关联字段1
若将B的zb1,zb2设置为左连接,将B的zb3,设置成右连接,由于连接方式不同,无论是【先分组后连接】还是【先连接后分组】,这两张主题表的连接都必须按指标的连接方式拆分为两次join;如下:
(SELECT主题表A.zb ,主题表B.zb1 ,主题表B.zb2
FROM 主题表A LEFT JOIN 主题表B
ON 主题表A.关联字段1=主题表B.关联字段1 )
和
(SELECT主题表A.zb,... 主题表B.zb3
FROM 主题表A
RIGHTJOIN 主题表B
ON 主题表A.关联字段1=主题表B.关联字段1 )
如果在报表设计上设置了多个独立的分析区,BI会为每个分析区单独生成独立的sql语句。因此我们需要注意,多个分析区真有必要独立分开吗?如果可以合并到一起,BI会尽可能用一个sql提交数据库处理,在某些场景下,特别是对数据量巨大的主题表而言,两次查询统计变成一次,性能上将会有不小的提升。
2011-08-02 17:14:10执行查询耗时1秒203毫秒。(内存情况:FREE=134.9M TOTAL=213.9M MAX=910.2M) |
你一定很熟悉这个方框中的信息模式吧?这是BI中一张分析表的详细信息,但是==,为什么在一个分析报表的详细信息中会看到如此多不同报表的sql生成和计算日志? 原来这张分析表使用了跨表取数,为了计算这张报表,系统先触发执行了其他报表的sql,具体详情请参考《tips201106》;这种情形下,虽然本表没有产生多个sql,但是间接地通过计算其他表,也执行了多个独立的sql,造成本表计算时间超长。
作为一个报表工程师,在设计报表的过程中,有哪些和sql性能相关的要素是我们可以调控,需要特别关注的呢?基于以上对BI分析表sql的理解,报表工程师们可着重关注如下要素: 1.过滤条件 2.连接模式 3.Sql个数 4.Sql数据量
下面将分别从这几个方面具体谈谈如何优化BI分析报表的设计。
BI分析表中的过滤条件在报表计算时都会转换成SQL语句中的WHERE条件,在大数据量的情况下,WHERE条件不够优化,会直接导致SQL语句运行效率低下,最直接的表现就是SQL语句执行时没用到索引或者用到的索引不够好。 我们都知道索引在rolap数据仓库中至关重要,好的索引对查询统计分析的性能提升具有不可替代的作用。因此,一个好的where条件会尽可能充分利用好的索引,决不会破坏数据查询走索引的可能。 BI报表的主题集、维表元、指标表元上都可以设置数据期条件和过滤条件,报表工程师们在这些过滤条件中编写的表达式都将直接决定报表sql语句中where子句的质量;什么样的过滤能构成一个质量上乘的where子句?什么样的过滤一定会造成where子句效率的损失?我们在编写BI报表过滤条件时又该注意哪些问题呢?