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

原文 http://blog.csdn.net/idontwantobe/article/details/25052199

搞点多维分析，糙快猛的解决方案就是使用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(*)计算）

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

[sql] view plain copy

1. ……WHERE  avg_csm_weekly >100  AND  trade_id= 19  ORDER 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]

[sql] view plain copy

1. 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）累计聚合

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

[sql] view plain copy

1. SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)  
2. FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b  
3. ON a.account_id=b.account_id  ANDb.stdate<=a.stdate  
4. GROUP BY a.account_id,a.stdate  
5. ORDER BY a.account_id,a.stdate  

2）滑动累计

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

[sql] view plain copy

1. SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)  
2. FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b  
3. ON a.account_id=b.account_id  ANDb.stdate<=a.stdate  
4.     AND b.stdate>=DATE_ADD(a.stdate,INTERVAL -30 DAY)  
5. GROUP BY a.account_id,a.stdate  
6. ORDER BY a.account_id,a.stdate  

3）MTD累计

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

[sql] view plain copy

1. SELECT a.account_id,a.stdate,SUM(click_pay),AVG(click_pay)  
2. FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b  
3. ON a.account_id=b.account_id  ANDb.stdate<=a.stdate  
4.     AND b.stdate>=DATE_FORMAT(a.stdate,”%Y-%M-01”)  
5. GROUP BY a.account_id,a.stdate  
6. ORDER BY a.account_id,a.stdate  

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

语句1：

[sql] view plain copy

1. SELECT col1,col2,col3,SUM(col4) FROM table  
2. GROUP BYcol1,col2,col3  
3. WITH ROLLUP  

语句2：

[sql] view plain copy

1. SELECT col1,col2,col3,SUM(col4) FROM table  
2. GROUP BYcol1,col2,col3  
3. UNION  
4. SELECT col1,col2,NULL,SUM(col4) FROM table  
5. GROUP BYcol1,col2  
6. UNION  
7. SELECT col1,NULL,NULL ,SUM(col4) FROM table  
8. GROUP BY col1  
9. UNION  
10. SELECT 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。

[sql] view plain copy

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

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

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

其他

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