京东罗盘优化
京东罗盘优化：
一．MDX优化，以“市场竞争强度”为例，有这么一段MDX（查“竞争指数”）： SELECT NON EMPTY { HIERARCHIZE({ [Measures].[竞争指数] }) } ON COLUMNS, NON EMPTY { HIERARCHIZE( { { [商品扩展属性.扩展属性值].1.2.3 }, FILTER( { { [商品扩展属性.扩展属性值].[扩展属性名称].members } }, ( exists(ANCESTOR([商品扩展属性.扩展属性值].currentmember, [商品扩展属性.扩展属性值].[三级分类名称]), { [商品扩展属性.扩展属性值].1.2.3}).count > 0 ) ), FILTER( { { [商品扩展属性.扩展属性值].[扩展属性值名称].members } }, ( exists(ANCESTOR([商品扩展属性.扩展属性值].currentmember, [商品扩展属性.扩展属性值].[三级分类名称]), { [商品扩展属性.扩展属性值].1.2.3}).count > 0 ) ) } ) } ON ROWS FROM [MarketCompetStrength] WHERE CROSSJOIN( { [时间.年季月日].4.5.6.7:[时间.年季月日].4.5.6.8 }, { [部门].9 } ) 。
优化后的MDX为： SELECT NON EMPTY { { [Measures].[竞争指数] } } ON COLUMNS, NON EMPTY { HIERARCHIZE( DESCENDANTS( [商品扩展属性.扩展属性值].1.2.3, [商品扩展属性.扩展属性值].[扩展属性名称], self_and_after ) ) } ON ROWS FROM [MarketCompetStrength] WHERE CROSSJOIN( { [时间.年季月日].4.5.6.7:[时间.年季月日].4.5.6.8 }, { [部门].9 } )
以现在的数据库来说，这个MDX的条件中，有三天的数据：20141031，20141104，20141105，经过saiku-ui测试，优化前每次查询时间大约在10秒左右。优化后的MDX语句，查询时间一般在6秒以内。说明我们写的MDX应该有优化的空间。
这个优化的内容主要是： 去除了不必要的三级分类下的值。 用了DESCENDANTS函数，具体用法参照MSDN的： http://msdn.microsoft.com/zh-cn/library/ms146075.aspx
通过两次日志比较，分析性能提升的原因。（主要就是时间、事实表、扩展属性值关联查询时，第二个MDX取了更少的信息，带上了条件）。
MDX优化的方法，需要对MDX不断的学习，并且不断的通过看日志进行分析。可以参照在群已经共享的“MDX解决方案.pdf”一书和网上资料。
二．Mysql优化：以上面优化过的MDX中产生的一个SQL为例：
SELECT `dim_categories_ext`.`ext_attr_cd` AS `c0`, `dim_categories_ext`.`ext_attr_name` AS `c1` FROM (SELECT department_id, dept_id, dept_name, department_full, dept_id_1, dept_level_1, dept_id_2, dept_level_2, dept_id_3, dept_level_3 FROM dim_department_market t1 WHERE -1 = 35 OR dept_id = 35 OR dept_id_1 = 35 OR dept_id_2 = 35 OR dept_id_3 = 35) AS `view_dim_department`, `fact_market_compet_strength_attr` AS `fact_market_compet_strength_attr`, `dim_time_v2` AS `dim_time_v2`, `dim_categories_ext` AS `dim_categories_ext` WHERE `fact_market_compet_strength_attr`.`dept_id_3` = `view_dim_department`.`dept_id` AND `view_dim_department`.`dept_id_1` = '35.0' AND `fact_market_compet_strength_attr`.`data_date` = `dim_time_v2`.`dim_date` AND `dim_time_v2`.`dim_year` = 2014 AND `dim_time_v2`.`dim_month` IN ( 10, 11 ) AND `dim_time_v2`.`quarter` = 4 AND `dim_time_v2`.`dim_day` IN ( 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, 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 ) AND `fact_market_compet_strength_attr`.`categories_full` = `dim_categories_ext`.`categories_full` AND `dim_categories_ext`.`code_level_1` = '737' AND `dim_categories_ext`.`code_level_2` = '794' AND `dim_categories_ext`.`code_level_3` = '870' GROUP BY `dim_categories_ext`.`ext_attr_cd`, `dim_categories_ext`.`ext_attr_name` ORDER BY Isnull(`dim_categories_ext`.`ext_attr_cd`) ASC, `dim_categories_ext`.`ext_attr_cd` ASC
有索引没有用上（演示一下执行计划）。
根据执行计划的分析，可以看到有两个查询没有用到索引，从网上查询的资料以及分析，发现：Mysql中“子查询”后索引就失效了，可以用JOIN的表连接的方式解决这个问题：
SELECT `dim_categories_ext`.`ext_attr_cd` AS `c0`, `dim_categories_ext`.`ext_attr_name` AS `c1` FROM `dim_department_market` AS `view_dim_department`, `fact_market_compet_strength_attr` AS `fact_market_compet_strength_attr`, `dim_time_v2` AS `dim_time_v2`, `dim_categories_ext` AS `dim_categories_ext` WHERE `fact_market_compet_strength_attr`.`dept_id_3` = `view_dim_department`.`dept_id` AND `view_dim_department`.`dept_id_1` = '35.0' AND ( dept_id = 35 OR `view_dim_department`.dept_id_1 = 35 OR `view_dim_department`.dept_id_2 = 35 OR `view_dim_department`.dept_id_3 = 35 ) AND `fact_market_compet_strength_attr`.`data_date` = `dim_time_v2`.`dim_date` AND `dim_time_v2`.`dim_year` = 2014 AND `dim_time_v2`.`dim_month` IN ( 10, 11 ) AND `dim_time_v2`.`quarter` = 4 AND `dim_time_v2`.`dim_day` IN ( 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, 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 ) AND `fact_market_compet_strength_attr`.`categories_full` = `dim_categories_ext`.`categories_full` AND `dim_categories_ext`.`code_level_1` = '737' AND `dim_categories_ext`.`code_level_2` = '794' AND `dim_categories_ext`.`code_level_3` = '870' GROUP BY `dim_categories_ext`.`ext_attr_cd`, `dim_categories_ext`.`ext_attr_name` ORDER BY Isnull(`dim_categories_ext`.`ext_attr_cd`) ASC, `dim_categories_ext`.`ext_attr_cd` ASC
再看一下这个SQL的执行计划。
根据测试，优化前查询时间差不多需要2秒左右，优化后查询时间在0.25秒左右。
那么，问题来了：是不是问题已经解决了？再试试二级部门，三给部门。发现子查询里，如果数据量很小，也会很快，甚至到了3级部门，比优化后的还要快。但是优化后（用表连接的方式）的性能是一个比较平衡的方式，所以还是应该使用表连接的方式。（除非子查询的条目一定非常少）
注意：一个表的索引是有长度限制的，不能超过767 bytes。所以之前这个表的定义：
中，长度设置为500，所以索引是会失效的。根据数据观察，考虑是否将长度设为100？（测试数据库中为了演示，已经将长度改成100了，包括事实表的字段长度。线上的库和代码还没有改）
下面看的几个比较好的Mysql优化介绍： http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html （雪楠推荐） http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html （执行计划说明） http://tech.meituan.com/mysql-index.html （美团网一个技术人员写的，个人觉得写的非常好）
总结： 优化数据库性能，最有效也是也简单的就是建立索引。但是建立最合适的索引，也是最难的。索引不是越多越好，到底是组合索引，还是多个索引的问题，一直有点迷惑。在26万数据下，没有测出来组合索引的明显优势或劣势。以后有时间再深入研究。 测试中，尝试了使用MyISAM数据库引擎（fact_market_compet_strength_attr），在26万左右的数据量下，没发现有明显的性能提升，故而没有使用。时间关系，也没有特意去深研。 Mysql对于子查询的优化做的不是很好，会使一些索引失效，考虑用表连接的方式进行优化。（即李涛之前提到过的加了子查询索引就失效的问题）。 因为Mondrian的权限控制并不是很强，Mysql的优化机制也不如Oracle那么强，所以才导致很容易出现mondrian拼出的sql性能很差。这也是为什么我们直接写sql的性能比Mondrian高很多的原因。（我们的sql也要做优化，不要再用子查询，全部用表连接）
三．Mondrian多学习，使用退化维、聚合表等特性。 退化维：http://mondrian.pentaho.com/documentation/schema.php#Degenerate_dimensions 聚合表：http://mondrian.pentaho.com/documentation/aggregate_tables.php
建议：如果我们想基于Mondrian做一个优秀的产品（或项目），建议把Mondrian的文档和MDX语言系统的学习一下，明白其它原理和细节。（Saiku生成的MDX谨做参考）。