全网首发｜阿里资深技术专家数仓调优经验分享（下）

 

随着云原生数据仓库AnalyticDB for MySQL（下文统一简称：AnalyticDB）在阿里集团各个业务线、社会上各行各业的推广应用，我们沉淀了一些最佳实践，现在笔者整理在这里，供大家参考，希望对大家有帮助。本篇文章总结了AnalyticDB表的设计的最佳经验、数据写入的最佳经验、高效查询的最佳实践，以及一些常见的问题。

 

05 业务行业线上的最佳实践

（一）营销业务使用场景

随着互联网流量成本的增加，花大价钱砸流量的时代成为历史，客户广告营销越来越讲究精细化运营，越来越依赖对已有的客户数据做实时的、精准的分析，提高广告的转化率。在对人群的营销方面，一般存在以下典型的场景：
 

典型的系统架构：

 核心表建表语句如下：

CREATE TABLE db.order (
  order_id,
  user_id,
  shop_vip,
  last_trade_time,
  last_cart_time,
  member_grade,
  seller_zone,
  member_credits,
  clustered key index_mmsi(`user_id`)
)
DISTRIBUTED BY HASH(order_id)
PARTITION BY VALUE(DATE_FORMAT(last_trade_time, '%Y%m%d')) LIFECYCLE 30
COMMENT '订单信息表';

 

备注：采用order_id作为分布键，确保数据均匀分布，不会出现倾斜。同时由于需要频繁的按照user_id查询或者关联，将user_id用作聚集索引。

1.人群透视

人群透视是指根据用户的各种标签，来选取特定的人群。通常情况下，以用户或者用户行为的表作为事实表，用户的各类标签/属性作为维度表，采用星型模型，用事实表来JOIN各个维度表，做多维分析（有时也可能会采用数据冗余的反范式方式，以牺牲数据存储为代价，将事实表构建为一张大宽表，目的是省去分析时的多表关联）。正是因为用户分析标签的不确定性，采用传统的数据库（传统数据库的索引是不能无限制的创建的）是无法做到这种不定维度的分析的，那么AnalyticDB就是解决该类问题的最佳方案。典型的SQL如下：

SELECT
  t2.buyer_id,
  t3.seller_id,
  t1.shop_vip,
  t1.last_trade_time,
  t1.last_cart_time,
  t1.member_grade,
  t1.seller_zone,
  t1.member_credits,
  sum(t1.pay_amount)
FROM
  db.order t1
  JOIN db.dimension_table1 t2 ON t1.user_id= t2.buyer_id
  JOIN db.dimension_table2 t3 ON t1.user_id= t3.seller_id
WHERE
  t1.is_market_target IN('4')
  AND t1.seller_zone = 1019
  AND t1.attributes IN('6742081')
  AND t3.buyer_id = ‘xxxx’
  and t3.tseller_id = ‘yyyy’
group by
  t2.buyer_id,
  t3.seller_id,
  t1.shop_vip,
  t1.last_trade_time,
  t1.last_cart_time,
  t1.member_grade,
  t1.seller_zone,
  t1.member_credits;

其中的order表可能是万亿级别，巨量数据的多维、多表关联在线实时分析对底层分析系统的能力要求极高。

2.人群圈选

人群圈选和人群透视类似，更多的时候可能是圈选具体的人群数量，而不是具体的明细数据，这时更多使用到AnalyticDB的聚合计算能力，即根据各个不定的维度进行COUNT DISTINCT或者GROUP BY的操作。典型的SQL语句如下：

SELECT count(1) AS cnt
  FROM(
SELECT DISTINCT t1.buyer_id
  FROM(
SELECT buyer_id
  FROM db.order
 WHERE seller_zone= 11111
   AND seller_id= 121211121
   AND algorithm_crowd IN('84')) t1
 JOIN(
SELECT user_id AS buyer_id
  FROM db.dimension_table1) t2
      ON t1.buyer_id= t2.buyer_id
JOIN(
SELECT user_id AS seller_id
  FROM db.dimension_table2) t3
      ON t1.buyer_id= t3.seller_id
) t;

 

3.人群投放

人群投放是指按照一定的促销渠道，将营销信息投放到上面圈选的人群中，比如短信投放、门户网站的广告投放等。不同渠道的数据可以使用不同OSS来存放，而AnalyticDB可以方便地将库内数据Dump到OSS或者其他的下游产品上，而且Dump的效率非常高，很好地提高了用户人群投放的效率。在ADB2.0中的典型SQL如下：

CREATE TABLE output WITH(oss_dump_endpoint= 'xxxxxx.oss-internal.aliyun-inc.com', oss_dump_bucket_name= 'xxxx',
                         oss_dump_file_name= 'xx_prod/20190710/63218721',
                         oss_dump_is_overwrite= true,
                         oss_dump_compatibility_mode= false,
                         oss_dump_access_key_id= 'xxxxxxxxx',
                         oss_dump_access_key_secret= 'xxxxxxxxxxxxxxxxxxxx',
                         oss_dump_row_del= '\r\n',
                         oss_dump_col_del= '\t', table_type= 'oss_dump', dump_charset_code= 'UTF-8',
                         oss_dump_table_header= 'false', return_dump_result_count= true) as
SELECT DISTINCT t1.buyer_id
  FROM(
SELECT buyer_id
  FROM db.order
 WHERE last_cart_time>= 20190610
   AND last_cart_time< 20190710
   AND is_market_target IN('1')
   AND seller_zone= 1018
   AND seller_id= 3687815378) t1
JOIN(
SELECT user_id AS buyer_id
  FROM db.dimension_table) t2
ON t1.buyer_id= t2.buyer_id
LIMIT 1000;

 

（二）监控大屏类场景

 

由于AnalyticDB支持实时写入，而且实时写入的数据又能进行比较复杂的实时分析，因此在一些监控大屏、监控大盘、实时看板等场景中有着非常广泛的应用。

典型的系统架构如下：

上游生产数据通过Flink、DTS、精卫或者Dataworks等工具实时写入到AnalyticDB中，在AnalyticDB中进行实时在线分析，然后在报表展现工具做大屏显示。

 

该类的业务，对数据的时效要求很高，特别是对数据的实时写入要求很高，不仅实时写入的数据量大，且要求写入后实时可见，还要能快速地分析。因此在表的设计时要特别注意。本文总结了此类场景的几点注意事项：

• 表要设置主键。主键用于排重，一旦有重复的数据写入可以直接覆盖，参考之前上篇：全网首发｜阿里资深技术专家数仓调优经验分享（上）。
• 表要设计二级分区。一来该类数据往往量比较大，需要采用二级分区做数据的生命周期管理，自动淘汰过期的数据；二是实时写入的数据可以根据二级分区来构建索引，这样只需要增量数据构建索引，大大提高了构建索引的效率，有了索引后，数据的查询也就能快很多。
• 在特别大量的数据写入情况下，往往CPU的消耗也比较厉害，需要合理控制构建索引任务的并发度和时间，以避免和大流量写入峰值重合，而加重对实时写入的影响。

典型的SQL如下：

CREATE TABLE tb__record_info
(
  a_info_id bigint NOT NULL AUTO_INCREMENT,
  domain varchar NOT NULL,
  region varchar NOT NULL,
  ip varchar NOT NULL,
  result_ts varchar NOT NULL,
  time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  key idx_domain(domain),
  key idx_time(time_stamp),
  primary key (a_info_id, domain, time_stamp)
)
DISTRIBUTE BY HASH(domain)
PARTITION BY VALUE(DATE_FORMAT(time_stamp,'%Y%m%d')) LIFECYCLE 60;

 

（三）游戏行业的使用场景

 

游戏领域的竞争变得更加激烈，在互联网高速增长的同时，流量成本不断升高，市场营销开始往精细化发展。游戏厂商对于渠道、用户和游戏表现的评估需要更加细化和准确的数据，希望利用优秀的数据分析工具来帮助团队更全面的分析市场和用户的趋势，同时玩家的游戏行为和喜好也在慢慢变化，如何能够及时发现这些变化并针对性的调整产品和游戏设计也是非常重要的，因此提出了如下业务要求：

• 提供全面的游戏运营指标分析功能：全面提高游戏开发者的日常数据运营工作效率，不仅提供付费用户、付费率、付费金额和ARPU等运营指标，还强化了付费用户的留存率、回访率、用户生命周期价值等更加精细化的运营指标，游戏开发者可以更加深入，更加有效率地掌握游戏运营状态。
• 提供有效的渠道效果分析，使每分钱都花在刀刃上：实时的分渠道数据统计可以监测到不同渠道用户的增长、活跃、留存状况以及充值状况，更加全面、快速地分析出投资回报率，让开发者对渠道的评估更加准确。
• 针对付费用户追踪分析，了解付费用户的习惯：针对付费用户群，通过简单易懂的数据分析模型和图表，跟踪付费用户的留存、流失、回访和充值数据，更好地反映付费用户在整个生命周期的关键行为和价值。
• 细致分析玩家游戏行为，改进产品体验，提高游戏收益：关卡、道具、消费行为分析的功能可以了解道具和物品在使用过程中使用和消耗的总量以及趋势，开发者可以借此来做到恰到好处的数值平衡设计，也可充分利用数据分析的结果优化游戏内付费商品的收益。

AnalyticDB可以支持如下典型场景。

1.活跃分析

游戏产品日活DAU/月活MAU等均为评价该款游戏是否被玩家广泛接受的一个非常重要的指标。

DAU计算的SQL示例如下：

SELECT count(DISTINCT uid) AS count 
FROM login_log
WHERE timestamp >= <start_timestamp>
AND timestamp   <= <end_timestamp>
AND qita1 = <x>
AND qita2 = <y>;

 

基于上述的基本统计，可以对玩家的活跃状态做更多的探索，比如：

• 活跃账号分析
      1.按照日期分析，常见的DAU/WAU/MAU等
      2.按照渠道分析，比如分包渠道或者广告渠道等
• 在线分析
      1.平均在线玩家数
      2.峰值在线玩家数
• 玩家行为分析
      1.人均游戏次数，即所选日期内，总游戏次数 / 游戏人数（该数值无法完全精确统计，仅供参考）
      2.人均游戏时长分析等

2.来源分析

游戏玩家来源分析里面，新增设备分析用来预测该款游戏的生命周期和拉新效率等，也是评价该款游戏是否被玩家广泛接受的一个非常重要的指标。

新增设备、新增玩家等计算的SQL示例如下：

SELECT Count(*) AS count FROM  
(
    SELECT deviceid
    FROM login_log
    WHERE  channel_id = ‘X’
    AND timestamp >= ‘XXX’
    AND timestamp <= ‘YYY’
    GROUP  BY deviceid
) AS d1
    LEFT JOIN 
(
    SELECT deviceid
    FROM   login_log
    WHERE  channel_id = ‘X’
    AND timestamp < ‘YYY’
) AS d2
ON d1.deviceid = d2.deviceid
WHERE  d1.deviceid IS NULL;

 

3.留存分析

留存指标在某些方面反映了游戏产品的质量和保留玩家的能力，也在另一方面反映了渠道与游戏目标用户的契合度及渠道质量。所以对留存指标的分析显得更为重要。

玩家留存率等计算的SQL示例如下：

SELECT
  channel_id,
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 0,
      user_id,
      NULL
    )
  ) AS 'liucun_1',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 1,
      user_id,
      NULL
    )
  ) AS 'liucun_2',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 2,
      user_id,
      NULL
    )
  ) AS 'liucun_3',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 3,
      user_id,
      NULL
    )
  ) AS 'liucun_4',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 4,
      user_id,
      NULL
    )
  ) AS 'liucun_5',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 5,
      user_id,
      NULL
    )
  ) AS 'liucun_6',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 6,
      user_id,
NULL
    )
  ) AS 'liucun_7',
  count(
    DISTINCT IF (
      datediff(payorder_riqi, login_riqi) = 14,
      user_id,
      NULL
    )
  ) AS 'liucun_15'
FROM
  pay_order p
  LEFT JOIN login_log l ON p.uid = l.uid
WHERE
  payorder_riqi >= '2019-01-17'
  AND payorder_riqi <= '2019-01-24'
GROUP BY
  `channel_id`
ORDER BY
  `liucun_1` DESC;

 

06  FAQ

 

1.磁盘占用大小包含哪些数据？为什么会触发磁盘满锁定？

磁盘占用量主要包括数据和索引两部分。
索引在构建过程中，会临时额外占用少量空间，期间可能会有少量数据膨胀。
用户可以使用如下SQL语句查询磁盘占用量（说明：该指标是延迟统计的，每小时统计一次）：SELECT (SUM(data_length)+SUM(index_length))/1024/1024/1024 AS '数据空间(GB)' FROM information_schema.tables;用户还可以使用如下SQL语句查询当前日志占用空间：show binary logs返回结果中的adb-bin.log表示binlog，adb-system.log表示系统日志。

当单节点磁盘使用量超过80%则会触发锁定。

单节点磁盘使用量过高可能有两种原因：一是一级分区键选择不合理导致某些节点数据倾斜；二是数据分布比较平均，总体使用量过大。用户可以根据控制台页面的存储水位判断是否存在表分区倾斜。

2.是否支持磁盘大小扩缩，是否支持节点数扩缩？节点数扩缩需要多久?

目前磁盘使用ECS云盘，只支持扩容。节点数支持扩缩，数量范围与实例初始规格相关，控制台变配页面可以看到当前实例节点数变配范围。节点数扩缩会在节点间进行部分数据迁移。

3.如何进一步提高写入性能？

尽可能使用批量写入的方式；使用Dataworks进行数据同步时，注意并发任务数和写入批大小是否过小；尽可能精简地设置主键；尽可能均衡地选择写入表的分区键。

4.如何选择合适的一级分区键？

AnalyticDB内部将数据拆分为若干个一级分区，通常情况下一个AnalyticDB实例内部大概有100数量级左右的一级分区。在进行查询时不同的一级分区并发进行。因此一级分区键最重要的一点是需要保证数据尽可能的均匀，否则会出现长尾查询，拖慢整体查询进度。
不同的表如果一级分区键相同，那么这些表在执行以一级分区键为Join Key的JOIN时可以大幅度减少数据Shuffle。因此在保证数据均匀的前提下，相同的一级分区键可以加速JOIN。

5.如何选择合适的二级分区键？

二级分区是对一级分区的进一步拆分，一般是在时间维度上进行。但是不建议二级分区分的太多，建议单表二级分区数量不要超过100个。假设一张订单表单天增量百万左右，需要保留10年的数据。由于一个AnalyticDB集群，通常情况下，有大约100个一级分区。若该表按日进行分区，则单个二级分区的数据量大小约为1万左右，远低于我们的建议值。因此用月或者年作为二级分区比较合适。
AnalyticDB支持修改二级分区的生命周期。示例ALTER TABLE lineitem PARTITIONS 12表示将lineitem的二级分区个数修改为12。需要注意的是，二级分区个数的修改是后台异步执行的，执行BUILD TABLE lineitem可以加速分区修改任务。

6.二级分区的过期策略是怎样的？

目前二级分区的过期策略是，依据大小排序，只保留最大的N个二级分区，其余分区将会被淘汰，其中N为生命周期的大小。假设表A当前拥有3个二级分区，分别是202001、202002、202003，其生命周期个数为3，那么，当分区值为202004的数据写入时，202001分区就会被淘汰。需要注意的是分区淘汰是异步执行的，如果需要尽快把过期的二级分区淘汰掉，可以通过执行 build table  table_name 的方式触发过期的二级分区淘汰机制。
此外在使用二级分区时也要注意脏数据带来的误淘汰问题，如果此时表A分别写入了分区值为300001、300002、300003的三条脏数据，那么分区淘汰策略也会被触发，整表将只剩下分区值最大的三条脏数据。

7.聚集索引是什么，什么情况下适合使用聚集索引？

聚集索引就是让数据根据若干字段进行排序。通过排序，将值相同或者相近的数据在物理上存储在一起。
如果查询一定会带有某个字段，比如电商卖家透视平台的卖家id就可以作为聚集索引，保证数据的Locality，进而让性能得到量级的提升。
目前只支持一个聚集索引，但一个聚集索引可以包含多列。目前除非对非常分散的数据进行点查，否则聚集索引对性能的帮助很少。

8.主键如何选择，是否能够修改主键？

主键一般用于数据的去重。主键的长度与去重的效率成反比，因此非常不建议使用较长的String，建议使用1～3个长整型字段作为主键。
此外需要注意的是，主键需要包含一级分区键和二级分区键。目前不支持主键的修改。

9.如何自己指定索引？

AnalyticDB默认是全字段索引，一般不需要自己维护索引。
查看表索引的方法：SHOW INDEX FROM tablename。如果想要删除某个索引可以使用：ALTER TABLE tablename DROP KEY keyname。其中keyname可以通过上面的语句查询。注意：删除索引可能会导致查询变慢。
如果想要自己指定索引，使用KEY关键字：KEY key_name (column_name)。例如：CREATE TABLE tablename (id bigint,c1 varchar,key id_idx(id)) DISTRIBUTE BY HASH(id);。

10.直接用MySQL的建表DDL可以在AnalyticDB中执行建表吗？

可以，具体行为是这样的：

如果DDL中有主键，用主键作Distribute Key。

如果DDL中没有主键，会自动创建一个字段__adb_auto_id__，然后使用__adb_auto_id__作主键和分区键。

 

11.可以直接在AnalyticDB 3.0中执行AnalyticDB 2.0的建表语句吗？

可以，AnalyticDB 3.0已经兼容了AnalyticDB 2.0的建表语句。

 

文章转自公众号：阿里云数据库