DRDS分析型只读实例最佳实践

简介

分析型只读实例是用来增强DRDS主实例对于复杂的大查询的处理能力，通过在DRDS主实例的本地执行引擎基础上增加MPP架构的分布式计算引擎，可以通过外接计算集群突破DRDS主实例上单机计算资源（内存、CPU、IO）的限制。分析型只读实例主要用于加速海量数据下的复杂分析类 SQL 的执行效率，可极大降低此类 SQL 的响应时间，尤其是对于海量数据量下的多表Join、聚合、排序操作有明显效果。

对于已使用 DRDS 主实例的业务来说，分析型只读实例可以用于支撑涉及分析型需求的业务场景例如运营、BI相关后台系统的报表以及统计大盘等。这类业务的SQL一般都涉及大数据量下join、聚合、排序、子查询等一种或多种操作。由于主实例执行引擎的内存和CPU限制此类SQL很可能无法执行或者执行速度不够理想从而无法满足业务需求。针对这种情况，推荐将此类SQL迁往DRDS分析型只读实例上执行来获得一个比较理想的执行效率。此外还可以通过使用DRDS的垂直拆分能力进行跨库的关联查询，将各个业务模块的数据进行关联分析，挖掘出更多的数据价值（5.3.8及以上版本）。

典型场景

经典电商场景下的数据分析

在电商场景，业务系统里为了降低耦合性， 通常被会拆分成很多子系统。下图就是模拟电商场景分别对交易库、商品库、用户库进行关联查询的示意图，各个子系统会使用单独MySQL实例(即RDS实例)进行存储：

一旦业务要搞营销活动，如双11大促、发红包等，应用的逻辑必然会涉及到多个数据库实例的读写，由于MySQL本身是不能支持跨实例级别的关联聚合查询的，同时这种涉及多个业务库的查询通常涉及的数据量巨大，常规的面向在线联机交易处理的数据库执行引擎很难支撑这种量级的计算，这会对业务后台的数据分析、监控、对账等场景会带来很大的麻烦，而使用DRDS分析型只读实例提供的跨库查询以及MPP引擎加速能力能够解决这个问题。DRDS对于挂载的每一个RDS数据库会统一抽象成逻辑库的概念，简单理解，就是会为每个库生成一份配置信息和元数据，在整个过程中用户本身并不需要做任何数据导入或数据同步的操作。基于这一层逻辑库的抽象，可以如同在单机MySQL一般地随意执行跨DRDS数据库的关联分析查询，并且不需要对业务的数据库任何其它改造，带来极大的使用便利。例如可以进行如下的查询：

新零售场景下的数据分析

新零售业务一定程度上相较传统电商业务来说更为复杂，新零售业务一般围绕人、货、场、仓四个维度展开，即线上业务与线下门店以及仓库实时联动，子系统相较普通线上电商业务更多，对全维度数据分析的需求更多，实时性要求更高，例如日结库存对账需求、实时跨域库存查询及实时履约情况统计。这种场景一般只能通过使用数据仓库将各个子系统的数据统一收集后进行离线分析，在付出较大的额外成本的同时分析结果的时效性也大打折扣。而此种情况下使用DRDS分析型只读实例可以很好的兼顾计算速度与数据实时性，免去离线数据同步的需要。DRDS只读实例在内部一直以来在内部为新零售的代表业务盒马提供了支持，使盒马业务可以方便地进行多种跨业务库的复杂分析类查询（如下图），满足不同场景的需要。

实际性能参考

以16C32的规格为例，参与计算数据量规模为100万行。

注：

1. 数据拉取速度受拉取到的行自身大小及数据捞取SQL执行时间影响，数据仅供参考。
2. 此Join场景为100万行数据与40万行数据进行关联。
3. 以下所列的计算部分的速度与分析型只读实例规格正相关。
4. SQL执行时间估算需考虑数据拉取时间，如本例中的join对于左右两侧数据拉取耗时分别为1s与0.4s,因两侧数据拉取可以并行执行，故最终估算执行时间为2.5s。

执行模式

DRDS分析型只读实例拥有两种执行模式：多机并行执行与单机本地执行。分析型只读实例的默认执行模式为多机并行模式并且可以识别出大多数不需要使用多机并行计算的简单查询，自动切换为单机本地执行模式。对于个别无法做到自动切换的SQL，可以使用如下HINT来强制使用单机本地执行模式：

/*+TDDL:cmd_extra(rbo_rule_type="CoronaDB", execution_engine="CoronaDB")*/

多机并行执行模式

默认情况下对于需要进行比较耗时的二次计算的SQL，分析型只读实例会启动多机并行执行模式，将该SQL转换成一组分布式计算任务下发给后端的计算集群（Fireworks), 通过同时调用多计算节点的资源来实现SQL执行的加速（相较主实例），通常来说加速效果与所购买的分析型只读实例规格正相关。分析型只读实例会通过分析执行计划来决定是否开启多机并行执行模式，对于含有join、聚合、排序中一个或多个的SQL，将会默认使用多机并行模式来执行，此模式下因总体计算资源受限，尽量不要并发。

例如：

SELECT COUNT(*) FROM T1 INNER JOIN T2 ON T1.ID = T2.ID GROUP BY T1.NAME;

其执行计划为：

Project(COUNT(*)="COUNT(*)")                                                                                                     
  Aggregate(group="name", COUNT(*)="COUNT()")                                                                                           
    Project(id="id")                                                                                                                  
      NlJoin(n_nationkey="n_nationkey", id="id", condition="T1.id = T2.id", type="inner")                                          
        LogicalView(tables="0000.NATION1", sql="SELECT `id`,`name` FROM `T1`")                                                 
        UnionAll(concurrent=true)                                                                                                     
          LogicalView(tables="[0000-0003].T2_[0000-0007]", shardCount=8, sql="SELECT `id`,`NAME` FROM `T2`")

这个SQL的执行计划中包含有聚合以及连接两种涉及较多计算的算子。对于Join算子，可以将两表数据分别按照id进行重新分布（Shuffle）到多个Partition中再利计算集群多机并行的能力将各个Partition内使用HASH JOIN或SORT MERGE JOIN的算法将最终的结果计算出来。

对于Aggregate(聚合)算子，同样可以采取按聚合键将数据重新分区的方法来生成多个可以并行计算的Partition, 在各个Partition内再分别进行聚合，最后合并全部Partition的到最终结果。各个Partition间利用多机并行模式的能力同时进行计算，从而算获得整个聚合计算的加速。

从这个例子可以看出，多数情况下（尤其是数据量较大时）对于含有Join或者聚合操作的SQL使用多机并行执行模式不仅可以突破单机执行模式下内存的限制还可以获得数倍的执行速度提升。

单机本地执行模式

在一些特殊情况下用户可能会在分析型只读实例上执行一些不需要进行进一步汇总计算的简单查询，例如像这种：

SELECT * FROM T1 WHERE ID = 5;

通过多机并行方式执行简单查询不仅无法获得加速反而会因为额外启动分布式集群的开销而影响执行效率。对于此类SQL，DRDS分析型只读实例可以自动识别，并直接通过自身的本地执行引擎执行可以实现与DRDS主实例一致的执行效率。对于一些特殊场景，因为DRDS优化器所具有的信息有限可能无法判断，此时需要用户根据实际情况使用HINT来指定一种执行模式来获得最高的执行效率。

例如：

SELECT * FROM T1 ORDER BY ID DESC LIMIT 10;

其中T1为为拆分表且字段ID上建有索引。从表面上看这条SQL因为包含有排序这一涉及到大量计算的操作，所以使用多机并行执行模式会比较快，尤其是在数据量较大的情况下。然而实际上因为ID字段上建有有索引而MySQL的索引又是按顺序组织的并且最后又是只取TOP 10, 那么实际上这条SQL的最优执行模式应该是直接将排序下推MySQL各自取分片内TOP 10，然后再以单机模式在DRDS节点上做一次归并排序得出最终的TOP 10。 因为可以使用索引进行分片内排序的加速，所以整个SQL执行速度要远远高于使用多机并行模式直接排序。对于此类SQL，可以在SQL里添加上文所提及的HINT切换为单机本地执行模式来获取更快的执行速度。

最佳实践

总的来说，判断一个SQL是否适合使用分析型只读实例来执行，多数情况下均可通过分析执行计划来得出结论，然而对于普通用户来说通过执行计划来判断可能过于繁琐与困难，下面总结一些常见的适合使用分析型只读实例的场景：

1. SQL在主实例执行时报出内存不足相关错误。
2. SQL包含复杂计算（Join, Agg, Sort等）且主实例上执行较慢，需要数秒甚至数十秒。
3. SQL为简单查询但是有权限隔离方面的需求。
4. SQL在主实例执行效率满足需求但是执行时占用较多主实例的资源（连接、cpu等），需要隔离来保证主实例稳定性。此种情况可能需要考虑添加强制使用单机本地执行的HINT。

另外如果只是需求流量和资源相对于主实例的隔离，对复杂SQL的加速几乎无需求，可以考虑使用DRDS并发型只读实例。