`

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

阅读更多

搞点多维分析,糙快猛的解决方案就是使用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主要关注行列旋转变化,还可以用来对聚合数据进行格式化用于报表展现,在此不再复述

分享到:
评论

相关推荐

    169集全新MySQL课程 MySQL技能全面探索 MySQL核心特训教程 MySQL零基础实战班视频

    ├─7、课程:SQL进阶使用(上).12、MySQL查询过程.mp4 ├─7、课程:SQL进阶使用(上).13、EXPLAIN语句.mp4 ├─7、课程:SQL进阶使用(上).14、约束详解Primary KEY.mp4 ├─7、课程:SQL进阶使用(上).1、...

    数据库原理(第5版)

    ● 结构化查询语言(SQL) ● 数据建模 ● 数据库设计 ● 数据库管理 由于当前Internet、World Wide Web和分析工具的广泛使用,因此另外增加两个基本概念: ● Web数据库处理 ● 商业智能(BI)系统 像Colin这样的用户...

    2013年中国数据库大会PPT第一部分

    17.DM7 MPP架构——同时满足OLAP与OLTP需求.pdf 18.SAP 让大数据飞翔.pdf 19.阿里数据库关键技术.pdf 20.基于Oracle的SQL优化典型案例分.pdf 21.赢在起点–谈数据库设计规范.pdf 22.大型业务系统Oracle数据库 10G...

    2013中国数据库大会ppt(1)

    DM7 MPP架构——同时满足OLAP与OLTP需求.pdf SAP 让大数据飞翔.pdf 阿里数据库关键技术.pdf 基于Oracle的SQL优化典型案例分.pdf 赢在起点–谈数据库设计规范.pdf 大型业务系统Oracle数据库 10G升级11G实践.pdf 基于...

    2013中国数据大会ppt(2)

    DM7 MPP架构——同时满足OLAP与OLTP需求.pdf SAP 让大数据飞翔.pdf 阿里数据库关键技术.pdf 基于Oracle的SQL优化典型案例分.pdf 赢在起点–谈数据库设计规范.pdf 大型业务系统Oracle数据库 10G升级11G实践.pdf 基于...

    2013中国数据库大会ppt(3)

    DM7 MPP架构——同时满足OLAP与OLTP需求.pdf SAP 让大数据飞翔.pdf 阿里数据库关键技术.pdf 基于Oracle的SQL优化典型案例分.pdf 赢在起点–谈数据库设计规范.pdf 大型业务系统Oracle数据库 10G升级11G实践.pdf 基于...

    java连接sqoop源码-big-data-engineering-indonesia:大数据工程工具、资源和社区的精选列表

    的增强型替代品。 世界上最先进的开源数据库。 具有 NOSQL 优点的可扩展 SQL 数据库。 CockroachDB 是一个云原生 SQL 数据库,用于构建可在灾难中幸存的全球可扩展云服务。 YugabyteDB 是一个高性能、云原生的分布式...

    03开源NewSql数据库TiDB-Deep Dive into TiDB

    使用 Chunk 结构重构所有执行器算子,提升分析型语句执行性能,减少内存占用,显著提升 TPC-H 结果 支持 Streaming Aggregation 算子下推 优化 `Insert Into Ignore` 语句性能,提升 10 倍以上 优化 `Insert On ...

    大数据学习计划.pdf

    4 在上个模块中,OLAP 类型的需求得到了很好的解决⽅案,即针对数据查询分析的应⽤。但是这些组件对于数据的随机删改并不擅长。针对 此种 OLTP 类型应⽤,⼤数据⽣态系统中有另外⼀类 组件处理这样的问题,那就是 ...

    jpivot学习总结.doc

    uniqueMembers 该属性用于优化产生的 SQL ,如果你知道这个级别和其父级别交叉后的值或者是维度表中给定的级别所有的值是唯一的,那么就可以设置该值为 true ,否则为 false 。 levelType 该 Level 的类型,默认...

Global site tag (gtag.js) - Google Analytics