MySQL与OLAP:分析型SQL查询最佳实践探索

标签: mysql olap 分析 | 发表时间:2016-02-02 11:44 | 作者:fengbin2005
出处:http://www.iteye.com

搞点多维分析,糙快猛的解决方案就是使用ROLAP(关系型OLAP)了。数据经维度建模后存储在MySQL,ROLAP引擎(比如开源的Mondrian)负责将OLAP请求转化为SQL语句提交给数据库。OLAP计算分析功能导致MySQL需要进行较多复杂SQL查询,性能调优必不可少,本文总结了一些实用原则。

OLAP特点

OLAP的典型应用包括复杂动态报表,需要支持钻取(上卷和下钻)、切片、切块和旋转操作。下表总结了OLAP和OLTP系统的主要区别。OLAP的特点决定了SQL的查询场景和优化方案,下文将从索引、聚合、子查询、表连接和Pivoting等几个方面分别介绍。

 

OLAP

OLTP

用户量

分析人员用户量相对小

高并发

数据库设计

维度模型:星型、雪花型号

规范化

数据量

大,动辄千万级别

小,一般不超过百万级别

SQL读写场景

定期导入,一般无更新,复杂查询每次检索大量数据

以事务为单位每次读写少量数据

老生常谈之索引

在权衡数据容错恢复和性能之后,存储引擎选择的是Innodb。Innodb索引的特性是主键聚集索引和B+Tree数据结构。利用这两个特性,能够提升数据导入和多维度组合切片的性能。

1)       数据导入速度

下图为Innodb表主键索引示意图,聚集索引使表中所有数据必须按照主键顺序存储在主键索引叶子节点上。如果不按照主键顺序导入数据,会导致额外的分页、数据查找、移动IO操作,这样,Innodb表的插入速度严重依赖于插入顺序。解决方法比较简单:主键使用Auto_Increment列。

2)       多维度切片

多维度组合查询、分组和汇总操作非常常见,那么在多个维度字段上添加复合索引是必不可少的,而复合索引的字段选择和顺序尤为重要。

谁排NO.1?一般遵循以下原则:

a)        Mysql只进行索引最左前缀匹配,可以选择最常查询的字段排首位。特殊情况:如果少量查询场景不存在该字段怎么处理?需要另外再建索引吗?假设在盘古系统中,运营单位一般会出现在所有查询中,所以会建立[运营单位,行业,产品线……]的复合索引,但某些高级别管理人员的查询语句中,不包含运营单位,那么需要再建立[行业,产品线……]的复合索引吗?答案是看情况,提供小技巧:应用层处理,在不包括运营单位条件的查询SQL中加入“运营单位 in(所有运营单位)”条件

b)        最佳性能优化原则决定索引区分度最大的字段排首位(可用count(distinct column)/count(*)计算)

还有个大家往往会忽略的问题,谁排最后呢?答案是:将可能存在范围条件检索的字段放最后。来个案例

……WHEREavg_csm_weekly >100ANDtrade_id= 19ORDER BY balance

假设建立的复合索引为[avg_cms_weekly,trade_id, ,balance],那么由于在avg_csm_weekly上存在范围条件,MySQL不会使用剩余的索引。

聚合

MySQL不支持Hash聚合,仅支持流聚合。流聚合会先根据GROUP BY的字段进行排序,然后流式访问排序好的数据,进行分组聚合。如果在explain的extra列中看到Using temporary和Using filesort,说明聚合使用了临时表和文件排序操作,这可能导致性能低下。最佳优化目标是让聚合操作使用Covering Index,即完全不用查询表数据,只在索引上完成聚合查询。

下面查询语句会使用复合索引[trade_id,product_line_id]

select trade_id,product_line_id,count(*) from data_acct_info_weekly group bytrade_id,product_line_id

观察查询计划,在extra列显示Using index,说明该操作为Covering Index查询。

在OLAP分析中,时间范围上的聚合操作非常普遍。下面以账号每日消费表为示例,总结几种常见的时间聚合查询模板

account_id(账户)

stdate(数据日期)

click_pay(点击消费)

1

2013-08-01

100

1

2013-08-02

150

2

2013-08-01

125

1)累计聚合

返回账户加入某度以来累计消费和平均值。

SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)FROM data_account_csm_daily a INNER JOIN data_account_csm_daily bON a.account_id=b.account_idANDb.stdate<=a.stdateGROUP BY a.account_id,a.stdateORDER BY a.account_id,a.stdate

2)滑动累计

返回账户固定窗口时间内累计消费和平均值

SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)FROM data_account_csm_daily a INNER JOIN data_account_csm_daily bON a.account_id=b.account_idANDb.stdate<=a.stdateAND b.stdate>=DATE_ADD(a.stdate,INTERVAL -30 DAY)GROUP BY a.account_id,a.stdateORDER BY a.account_id,a.stdate

3)MTD累计

返回账户月初以来累计消费和平均值

SELECT a.account_id,a.stdate,SUM(click_pay),AVG(click_pay)FROM data_account_csm_daily a INNER JOIN data_account_csm_daily bON a.account_id=b.account_idANDb.stdate<=a.stdateAND b.stdate>=DATE_FORMAT(a.stdate,”%Y-%M-01”)GROUP BY a.account_id,a.stdateORDER BY a.account_id,a.stdate

再探讨下ROLLUP和CUBE。假设用户需要对N个维度进行聚合操作,需要进行N次GROUP BY再将结果进行UNION,而使用ROLLUP可以一次查询出N次GROUP BY 操作的结果。下面的两条语句查询结果一致,执行计划上却不同,前者只需要扫描一次,后者则需要扫描表四次。

语句1:

SELECT col1,col2,col3,SUM(col4) FROM tableGROUP BYcol1,col2,col3WITH ROLLUP

语句2:

SELECT col1,col2,col3,SUM(col4) FROM tableGROUP BYcol1,col2,col3UNIONSELECT col1,col2,NULL,SUM(col4) FROM tableGROUP BYcol1,col2UNIONSELECT col1,NULL,NULL ,SUM(col4) FROM tableGROUP BY col1UNIONSELECT NULL,NULL,NULL,SUM(col4) FROM table

与ROLLUP只在同一层次上对维度进行汇总不同,CUBE对所有维度进行汇总,N个维度CUBE需要2的N次方分组操作。当前版本的MySQL还不支持CUBE操作,但和用多个GROUP操作UNION模拟ROLLUP同理,也可以用多个ROLLUP操作UNION模拟CUBE。

子查询vs JOIN

复杂的需求场景导致某些子查询场景不可避免。关于子查询,存在不少性能陷阱和认识误区值得关注。

1)MySQL子查询性能差的主要原因是子查询产生临时表吗?不完全正确,临时表并不可怕,一个完整的SQL语句,FROM/JOIN/GROUP/WHERE/ORDER等操作,不考虑索引优化的情况下,都有可能产生临时表。所以更严格的表述是在子查询产生的临时表上查询无法利用索引导致性能低下。

2)IN子查询往往性能不佳的真实原因是什么?是IN查询的临时表数据量太大,MySQL太弱,只能支持极少数量的IN子查询吗?不一定,显示列表IN(a,b,c)查询的性能并不算差,IN子查询真正的性能陷阱在于Mysql优化器往往将IN独立子查询优化成EXISTS相关子查询!所以当观察SELECT * FROM table1 WHERE table1.id IN(SELECT id FROM table2)的查询计划,会发现table2的查询为DEPEDENTSUBQUERY,原因其实是MySQL优化策略+历史原因。

3)子查询的性能一定弱于JOIN吗?未必,由于Mysql不支持Semi Join(注),所以在某些需要场景下,使用子查询性能优于JOIN。比如A表和B表一对多关系,如果仅仅想查询在B表中存在对应记录的A表记录,如果使用JOIN,需要用DISTINCT或者GROUP操作进行去重操作。使用关联子查询可以避免这部分开销。SELECT id FROM table1 WHERE EXISTS(SELECT table2.id FROM table2WHERE table2.id=table1.id)

关于Join,Mysql使用Nested Loop算法(注)。在典型的星型维度模型中,维度表数据量远小于事实表,JOIN操作往往是大小表连接,性能问题不大,这方面不多讲。结合前面提到的Covering Index,介绍一个利用JOIN提高分页效率的歪招:

分页往往需要用到LIMIT OFFSET,在偏移量很大的时候,比如LIMIT 100000,50,MySQL需要检索100050数据,性能严重下降。常见的处理方式是a)增加排序辅助列,将LIMIT转化为在辅助列上范围查找操作b)应用层缓存机制c)需求折中,没有人会翻到100000页。以上皆不灵的时候,可以选择Covering Index+Join。

SELECT * FROM table1 INNER JOIN (SELECT id FROM table1 ORDER BY indexed_col limit 100000,50) AS a ON table1.id = a.id

这种方式效率较高,因为临时表a仅在索引上进行操作(Innodb索引叶子节点上存储了主键值),取得所需行id之后,再和完整的表进行Join获取其他所需列。

注:MySQL的著名分支MarioDB支持Semi Join和Hash Join

其他

Pivoting&Unpivoting主要关注行列旋转变化,还可以用来对聚合数据进行格式化用于报表展现,在此不再复述



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql olap 分析] 推荐:

MySQL与OLAP:分析型SQL查询最佳实践探索

- - Web前端 - ITeye博客
搞点多维分析,糙快猛的解决方案就是使用ROLAP(关系型OLAP)了. 数据经维度建模后存储在MySQL,ROLAP引擎(比如开源的Mondrian)负责将OLAP请求转化为SQL语句提交给数据库. OLAP计算分析功能导致MySQL需要进行较多复杂SQL查询,性能调优必不可少,本文总结了一些实用原则.

多维分析OLAP引擎Mondrian学习

- - Web前端 - ITeye博客
随着信息技术的飞速发展,在电力、电信、金融、大型制造等各个行业ERP、CRM、SCM、OA等越来越多的IT系统得以成功实施,这些分散建设的IT系统为各部门的运营效率提升发挥了很大的作用. 同时,为了满足业务管理和决策的报表系统(包括传统报表、数据仓库、OLAP等)也被创建出来,企业主管通过报表了解企业的总体运行状态.

Kylin:基于Hadoop的开源数据仓库OLAP分析引擎

- - 标点符
Kylin是一个开源、分布式的OLAP分析引擎,它由eBay公司开发,并且基于Hadoop提供了SQL接口和OLAP接口,能够支持TB到PB级别的数据量. OLAP即联机分析处理,它能够帮助分析人员、管理人员或执行人员从多角度快速、一致、交互地存取信息和更加深入的了解信息. OLAP的目标是满足决策支持或者满足在多维环境下特定的查询和报表需求.

唯品会海量实时OLAP分析技术升级之路

- - 运维派
本文根据谢麟炯老师在〖DAMS 2017中国数据资产管理峰会〗现场演讲内容整理而成. 谢麟炯,唯品会大数据平台高级技术架构经理,主要负责大数据自助多维分析平台,离线数据开发平台及分析引擎团队的开发和管理工作,加入唯品会以来还曾负责流量基础数据的采集和数据仓库建设以及移动流量分析等数据产品的工作. 海量数据实时OLAP场景的困境.

分布式大数据多维分析(OLAP)引擎:Apache Kylin 在百度地图的实践

- - leejun2005的个人页面
百度地图开放平台业务部数据智能组主要负责百度地图内部相关业务的大数据计算分析,处理日常百亿级规模数据,为不同业务提供单条SQL毫秒级响应的OLAP多维分析查询服务. 对于Apache Kylin在实际生产环境中的应用,在国内,百度地图数据智能组是最早的一批实践者之一. Apache Kylin在2014年11月开源,当时,我们团队正需要搭建一套完整的大数据OLAP分析计算平台,用来提供百亿行级数据单条SQL毫秒到秒级的多维分析查询服务,在技术选型过程中,我们参考了Apache Drill、Presto、Impala、Spark SQL、Apache Kylin等.

MySQL锁表机制分析

- - 小彰
为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制. MySQL有三种锁的级别:页级、表级、行级. MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level. locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁.

MySQL锁阻塞分析

- - CSDN博客数据库推荐文章
日常维护中,经常会碰到线程被阻塞,导致数据库响应非常慢,下面就看看如何获取是哪个线程导致了阻塞的. blog地址: http://blog.csdn.net/hw_libo/article/details/39080809. 3.1  使用show processlist查看. | | 3 | root | localhost | test | Query | 70 | Sending data | select count(*) from t3 a,t3 b.

MySQL执行状态分析

- - CSDN博客推荐文章
当感觉mysql性能出现问题时,通常会先看下当前mysql的执行状态,使用 show processlist 来查看,例如:. 其中state状态列信息非常重要,先看下各列含义,然后看下state常用状态. 一个标识,你要kill一个语句的时候使用,例如 mysql> kill 207;. 显示当前用户,如果不是root,这个命令就只显示你权限范围内的sql语句.

[转]用mysqldumpslow分析mysql的slow query log

- - 小彰
mysql有一个功能就是可以log下来运行的比较慢的sql语句,默认是没有这个log的,为了开启这个功能,要修改my.cnf或者在mysql启动的时候加入一些参数. 如果在my.cnf里面修改,需增加如下几行. long_query_time 是指执行超过多久的sql会被log下来,这里是1秒. log-slow-queries 设置把日志写在那里,可以为空,系统会给一个缺省的文件 host_name-slow.log,我生成的log就在mysql的data目录.

mysql优化之profile查询分析

- - 数据库 - ITeye博客
通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态. 如果觉得explain的信息不够详细,可以同通过 profiling命令得到更准确的SQL执行消耗系统资源的信息. 这里还需要注意一点就是,需要安装profile模块才能实现.