原文：ATA：https://ata.atatech.org/articles/11000164007?layout=%2Fvelocity%2Flayout%2Fblank.vm
各位亲爱的用户，随着AnalyticDB for MySQL（下文统一简称：ADB）在集团各个BU、社会上各行各业的推广应用，我们沉淀了一些最佳实践，现在笔者整理在这里，供大家参考，希望对大家有帮助。
在读这篇文章之前，请先了解ADB的产品相关的文档：
1.ADB 官方文档。
2.集团内用户使用ADB过程中的答疑手册。
说明：
本文写的最佳实践主要针对ADB3.0，ADB2.0在原理上同样适用，具体细节参考官方文档和我之前写的ATA系列文章
通用最佳实践
表设计的最佳实践：
ADB做为一个分布式的，追求实时分析海量数据的极致性能，需要充分发挥分布式数据库的优势，满足ADB达到最佳性能的特征要求，对表的设计时，需要注意以下几点规则。
1.选择合适的表类型（维度表or普通表）：
维度表：又称广播表，数据仓库中的一个概念，一般存储一些维度数据。在ADB中建表语句中有DISTRIBUTED BY BROADCAST 的关键字，这些表会在集群的每个节点存储一份数据，因此建议维度表的数据量不宜太大，每张维度表存储的数据不超过10万行。
普通表：也叫作分区表、事实表，一般存储业务的主题数据。普通表可存储的数据量通常比较大，可以存储千万条甚至万亿条数据，可以对其设置一级分区对数据做sharding或者二级分区进行数据的生命周期管理。
注意：维度表如果太大会导致数据存储空间的膨胀，节点越多膨胀越大，同时也会导致实时写入时性能下降，iops会比较高。
2.选择合适的分布键（一级分区键）：
ADB中创建普通表时，默认需要通过DISTRIBUTED BY HASH(column_name,…)指定分布键，按照column_name的HASH值进行分区。ADB支持将多个字段作为分布键。
分布键的选择依据：
尽可能选择参与JOIN的字段作为分布键，例如按照用户维度透视或者圈人，可以选择user_id作为分布键。
尽可能选择值分布均匀的字段作为分布键，例如交易ID、设备ID、用户ID或者自增列作为分布键。
注意：分布键不均匀容易导致数据分布不均，严重影响写入和查询的效率，此外也容易导致单节点磁盘写满导致整个集群锁定不可用。一般情况数据均匀是第一优先级，然后才考虑JOIN KEY对齐的问题，除非有业务就是想定制化。
3.选择合适的分区键（二级分区键）：
如果业务明确有增量数据导入需求，创建普通表时可以同时指定分布键和分区，分区可以实现数据的增量同步。
创建普通表时，通过PARTITION BY {VALUE(column_name) | VALUE(date_format(column_name, ?)}指定分区。
例如，PARTITION BY VALUE(column_name)表示使用column_name的值来做分区，PARTITION BY VALUE(DATE_FORMAT(column_name, ‘%Y%m%d’))表示将column_name格式化为类似20190101的日期格式做分区。
- 直接用ds的值来做分区
PARTITION BY VALUE(ds)
- ds转换后的天做分区
PARTITION BY VALUE(DATE_FORMAT(ds, ‘%Y%m%d’))
- ds转换后的月做分区
PARTITION BY VALUE(DATE_FORMAT(ds, ‘%Y%m’))
- ds转换后的年做分区
PARTITION BY VALUE(DATE_FORMAT(ds, ‘%Y’))
注意：一个实例能承载的最大二级分区数目是有限的，当前限制是10240。请提前规划后这个实例的所有表二级分区键，尽量充分利用二级分区，不要让每个二级分区的数据量过小，如：你使用天做二级分区，但是每天数据量很小，这时可考虑用月来做二级分区。否则会导致数据库中需要保存分区数据的元数据特别多，这些元数据是需要存放在内存中，会占据内存较多的空间，容易导致系统的GC或者OOM，同时也会导致实时写入时的iops比较高
二级分区的过期策略：
目前二级分区过期策略是依据大小排序，只保留最大的N个二级分区，其中N为生命周期的大小。假设表A定义的生命周期个数为3，目前存在的二级分区为202001，202002，202003。当分区值为202004的数据写入进来时202001分区就会被淘汰。需要注意的是分区淘汰是延迟进行的，不保证202004的数据写入后立即会淘汰202001。此外在使用二级分区时也要注意脏数据带来的误淘汰问题，如果此时表A分别写入了分区值为300001,300002,300003的三条脏数据，那么分区淘汰策略也会被触发，整表将只剩下分区值最大的三条脏数据。
4.选择合适的主键：
在表中定义主键可以去实现数据消重(Replace into)和数据更新操作(Delete、Update)。只有定义过主键的表支持数据更新操作（DELETE和UPDATE）。
主键的选择依据：
- 尽可能选择单数字类型字段作为主键，表的性能相对更好。ADB支持将字符串或者多字段组合作为主键。
- 主键中必须包含分布键和分区键，如果有二级分区键的话，需要包含二级分区键。
注意：设置的主键的字段不宜太大，或者某个字段的长度不宜过长，否则的话，会导致数据库的IOPS很高。
曾经某业务的主键设置太长：
导致在很多的tps下 iops特别高：
5.选择合适聚集索引：
聚集索引会将该列或者多列排序，保证该列相同或者相近的数据存在磁盘的相同或相近位置，当以聚集列做为查询条件时，查询结果保持在磁盘的相同位置，这样可以减少磁盘的IO。
聚集索引的选择依据：
查询一定会携带的字段可以作为聚集索引。例如，电商卖家透视平台中每个卖家只访问自己的数据，卖家ID可以定义为聚集索引，保证数据的局部性，提升数据查询性能。
注意：目前聚集索引只支持一个，但该聚集索引可以有多列。目前除非对非常分散的数据进行点查，否则聚集索引对性能的帮助很少。
6.设计合适的数据类型：
原理：
ADB处理数值类型的性能远好于处理字符串类型。原因在于：
- 数值类型定长，占用内存少，存储空间小。
- 数值类型计算更快，尤其是join时。
- 从内部索引机制上，字符串类型适合等值查询和范围查询情况，而时间，数值类型性能更高，建议用户尽可能- - 使用数值类型，减少使用字符串类型。
- 选择尽可能小的列，列类型要尽可能选择匹配的列，比如性别就可以用boolean或者byte类型，数据长度不大的可以用int
- 在同一个业务模型内，相同字段设计成相同的数据类型和字段长度，字段命名也保持一致，特别是涉及到主外键关联的字段更要注意，避免表在关联时不同的数据类型的字段关联导致隐式转换。
方法：
常见将字符串转换为数值类型方法：
- 包含字符前缀或后缀，例如E12345，E12346等。可以直接去掉前缀或者将前缀映射为数字。
- 该列只有少数几个值，例如国家名。可以对每个国家编码，每个国家对应一个唯一数字。
- 时间/日期类型数据，避免使用varchar字符类型存储，尽量使用date，timestamp或者int类型存储时间类型。
- 对于地理经度维度的使用，需要通过地理函数查询情况，数据类型采用double数据类型。
数据写入方面的最佳实践
实时写入：
1.批量打包的方式提交：
向表中写入数据时，可以通过批量打包方式INSERT INTO和REPLACE INTO提高数据写入性能。建议如下：
- 通过每条INSERT或者REPLACE语句写入的数据行数大于1000行，但写入的总数据量不宜太大，不要超过16MB。
- 通过批量打包方式写入数据时，单个批次的写入延迟相对会高一些，但是整体的性能会提升。
- 写入报错时，需要做重试确保数据被写入，重试导致的数据重复可以通过表的主键来消除。
- 如果不需要对原始的数据进行修改，直接使用insert into比replace into效率会高3倍以上。
样例：
INSERT INTO test
(id, name,sex,age,login_time)
values
(1,'dcs',0,23,'2018-03-02 10:00:00'),
(2,'hl',0,23,'2018-03-02 10:01:00'),
(3,'xx',0,23,'2018-03-02 10:02:00')
......;
2.更新数据
数据更新有多种方式，使用区别如下：
• 高频基于主键的行级覆盖更新, 且应用可以补齐所有列，请使用replace into values批量打包
• 高频基于主键的行级覆盖更新, 应用不能补齐所有列，请使用update into values批量打包
• 低频基于主键更新，可以使用replace into或者update into单条数据
• 低频任意条件更新，请使用 update set where
注意：update需要查表来填补更新中缺失的旧值，因此比replace into多一次查询，性能较低，不建议做高频、大批量的update操作。如果线上update性能无法满足需求，需考虑替换成Replace into，由应用端补旧值。
3.删除数据
数据删除有多种方式，使用区别如下：
• 低频主键条件删除，请使用 delete from where pk = xxx
• 低频任意条件删除，请使用 delete from where
• 删除单个二级分区，请使用 truncate partition
• 删除单表（包括所有二级分区，如有），请使用truncate table或drop table
批量导入：
如何选择是批量导入还是实时导入
- 从ODPS、OSS导入ADB，推荐使用insert overwrite select做批量导入，原因有二：
一方面，批量导入适合大数据量导入，性能好
二方面，批量导入适合数仓语义，即导入过程中旧数据可查，导入完成一键切换新数据。如果导入失败，新数据会回滚，不影响旧数据的查询。 - 从RDS、MySQL、ADB等导入ADB，看数据量情况，数据量不大的(百万级别的表)，推荐使用insert into select做实时导入，数据量大的，推荐使用insert overwrite select做批量导入。
导入并发和资源说明
- 单张表的导入会在系统内部串行，不同表之间的导入任务会并行，默认并行度是2；从ODPS分区表导入到ADB时，每次导入横跨的分区数不要超过30个，同一张表的不同分区导入是排队串行，不同表的导入，同时提交，有并行度n个任务同时导入，出于资源控制，超出的任务也会排队。
- 导入使用的是ADB内部的资源，与查询一样，属于同一个实例的资源。推荐导入任务在查询qps比较低的时候进行，比如凌晨0点以后，并推荐用户配置d2等定时任务，错峰做导入。
高效查询的最佳实践
ADB的优势是能在海量数据场景下，面对复杂查询，做到实时的在线分析。ADB的SQL调优需要充分发挥分布式计算优势，以及ADB本身的一些特征，同时对于通用的数据库优化的方法论同样是适用。
查询优化的通用法则：
按照斗佛早些年在《ORACLE DBA手记》上写的文章，数据访问优化满足以下漏斗法则：
1、 减少数据访问（减少磁盘访问）
例如：尽量多的使用过滤条件，尽早的提前过滤数据，减少参与计算的数据量，能在子查询里面把数据先过滤的提前过滤。
2、 返回更少数据（减少网络传输或磁盘访问）
例如：避免select * 的查询，特别的在OLAP数据库下，往往表的列数比较多，同时由于基于列存或者行列混存，对于这种select * 的操作，需要请求的IO回更多。
3、 减少交互次数（减少网络传输）
例如：上文提到的批量提交。
4、 减少服务器CPU开销（减少CPU及内存开销）
例如：
A. 减少不必要的排序和分页，特别是在子查询中的排序
B. 在满足业务前提下，尽量减少count distinct操作
C. 在满足业务前提下，特别是在海量数据下，采用类似Hyperloglog的近似计算代替准确计算。
5、 利用更多资源（增加资源）
例如：
A. 设计表的时候，尽量避免分区倾斜，导致存储和计算压在某一个节点上，尽量把数据都均匀的散列到所有的节点上，充分利用所有机器的能力，最大发挥分布式的数据库的效能
B. ADB本身就是MPP大规模并行处理的典型系统，在内核层面也做了大量的优化处理，充分利用更多的资源。
ADB特殊场景的优化：
外表的查询最佳实践
不要尝试对外表进行较为复杂的计算，这样会导致比较严重的GC，因为外表的计算是全部把数据拖过来算的，且网络带宽的压力也会变大。
巧妙的使用聚集索引：
当查询条件一定包含某列时，特别是该列的数据在存储上非常分散时，对该列建立聚集索引，性能会有明显的提升，可以采用类似如下的sql语句添加聚集索引：
alter table table_name ADD CLUSTERED INDEX index_cls (d_fdbid);
注意：如果表里面的数据已经有了，直接add cluster index不会对存量的数据排序，需要重建表，在建表的时候加上聚集列关键字。
减少节点间的数据交互：
分布式数据库，在充分发挥分布式计算的同时，有时也会加大跨节点间的网络开销，特别是请求的数据散列在各个节点上时，请求的数据量有比较少，且节点个数又比较多情况下，跨网络开销的情况就非常明显，因此可以采用以下几个思路：
- 如果能采用本地计算，在各个节点内join或者聚合分析时，尽量在本节点内计算，具体做法就是，如果在满足业务前提下，能用户一级分区键关联的，采用一级分区键关联；能对一级分区键进行group by的，采用一级分区键group by，这样可以尽量采用localjoin，大大减少跨网络的访问。
- 合理的控制节点数量，并不是节点越多越好，当数据库规模不大，且每次查询的数据量很少，且跨网络访问很严重的情况下，节点越多，问题越严重。
合理的使用索引：
合理使用索引在数据库调优中，非常重要，在ADB中也不例外。在ADB中，默认每列都会创建索引。但是也有例外情况，如果某列的cardinality值比较少时，通过索引查询可能会更慢，因为他需要多查一次索引再回表，且索引的选择性又不高，性能就会很差，这时可以在建表时把这些disable掉建索引的功能，这样就不会在建表后自动建索引了，如果索引已经创建了，可以把索引删除掉，或者通过hint 不走索引访问：
alter table table_name drop index index_name 把枚举列的索引删除掉。
或者使用/+no_index_columns=[t_order_content.fdelete;fdbid]/ 类似这样的hint把索引去掉不走
ADB连接的最佳实践
ADB在使用方式上做到和99%以上和mysql兼容，支持多种连接方式，比如mysql命令行，JDBC连接，Python连接，c#连接，PHP连接等等。整体请参考官方文档 。
另补充Golang连接最佳实践：
分析型数据库MySQL版3.0 Golang最佳实践：请参考
分析型数据库MySQL版2.0 Golang最佳实践：请参考
注意：由于ADB已经是一个分布式数据库，不需要再通过TDDL中间件来连接ADB。
业务行业线上的最佳实践参考：
客户运营行业
客户运营行业使用场景：
随着互联网流量成本的增加，花大价钱砸流量的时代成为历史，客户广告营销越来越讲究精细化运营，越来越依赖对已有的客户数据做实时的，精准的分析，提高广告的转化率，在对人群的营销方面一般存在以下典型的场景：
典型的系统架构：
核心表建表语句如下：
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各个维度表，做多维分析（有时也可能会采用数据冗余的反范式方式，以牺牲数据存储为代价，将事实表构建为一张大宽表，目的是省去分析时的多表关联）。正是因为用户分析标签的不确定性，采用传统的数据（传统数据库的索引是不能无限制的创建的）是无法做到这种不定维度的分析的，那么ADB就是解决该类问题的最佳方案。
典型的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）人群圈选
人群圈选场景和人群透视的场景类似，更多的时候可能是圈选具体的人群数量，而不是具体的明细数据，这时更多的时候是使用到ADB的聚合计算的能力，即根据各个不定的维度进行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来存放，而ADB支持将库内的数据，很方便的dump到oss或者其他的下游产品上，而且dump的效率也非常的高效。很好的节省了用户人群投放的效率。
典型的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 10000
监控大屏类：
由于ADB支持实时写入，而且实时写入的数据又能进行比较复杂的实时分析，因此在一些监控大屏，监控大盘，实时看板等应用场景中也使用的非常广泛。
典型的系统架构如下：
上游生产数据通过Blink、DTS、精卫或者dataworks等工具实时写入到ADB中，在ADB中进行实时在线分析，然后在报表展现工具做大屏显示。
对于该类的业务，对数据的时效要求很高，特别是对数据的实时写入要求很高，实时写入的数据量大，且要求写入后实时可见，还要能快速的分析，因此在表的设计时要特别注意这方面的需求，需要注意以下几点：
- 表设计时要设置主键：主键用于排重，一旦有重复的数据写入可以直接覆盖，具体主键设计的细则参考上文；
- 表的设计要设计二级分区：一来该类数据往往量比较大，需要采用二级分区做数据的生命周期管理，自动淘汰过期的数据；二是实时写入数据的索引的构建可以根据二级分区来构建，这样只需要增量数据构建索引，大大提高构建索引的效率，而不需对全表构建索引，有了索引后，数据的查询也就能快很多。
- 在特别大量的写入情况下，往往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
游戏行业：
ADB在游戏行业的使用场景：
游戏领域的竞争变得更加激烈，在互联网高速增长的同时，流量成本不断升高，市场营销开始往精细化发展。游戏厂商对于渠道、用户和游戏表现的评估需要更加细化和准确的数据的要求，希望利用优秀的数据分析工具来帮助团队更全面的分析市场和用户的趋势，同时玩家的游戏行为和喜好也在慢慢变化，如何能够及时发现这些变化并针对性的调整产品和游戏设计也是非常重要的，因此提出了如下业务要求：
- 提供全面的游戏运营指标分析功能：全面提高游戏开发者的日常数据运营工作效率，不仅提供付费用户、付费率、付费金额和ARPU等运营指标，还强化了付费用户的留存率、回访率、用户生命周期价值等更加精细化的运营指标，游戏开发者可以更加深入，更加有效率的掌握游戏运营状态。
- 提供有效分析渠道效果，使每分钱都花在刀刃上：实时的分渠道数据统计可以监测到不同渠道用户的增长、活跃、留存状况以及充值状况，更加全面、快速的分析出投资回报率，让开发者对渠道的评估更加准确。
- 针对对付费用户追踪分析，了解付费用户的习惯：针对付费用户群，通过简单易懂的数据分析模型和图表，跟踪付费用户的留存、流失、回访和充值数据，更好的反映付费用户在整个生命周期的关键行为和价值。
- 细致分析玩家游戏行为，改进产品体验，提高游戏收益：关卡、道具、消费行为分析的功能可以了解道具和物品在使用过程中使用和消耗的总量以及趋势，开发者可以借此来做到恰到好处的数值平衡设计，也可充分利用数据分析的结果来帮助开发者优化游戏内付费商品的收益。
典型场景的查询SQL：
（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>;
基于上述的基本统计，可以对玩家的活跃状态做更多的探索，比如：
• 活跃账号分析：
按照日期分析，常见的DAU/WAU/MAU等
按照渠道分析，比如如分包渠道或者广告渠道等
• 在线分析：
平均在线玩家数
峰值在线玩家数
• 玩家行为分析：
人均游戏次数，所选日期内，总游戏次数 / 游戏人数（该数值无法完全精确统计，仅供参考）
人均游戏时长分析等
（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
交通行业:
随着全国开始推广取消跨省收费站，原来的各个省各自为政的线下收费搬到了线上，这些信息化系统的建设，一方面大大的提高了收费和通行的效率，同时也为国家打通全国收费一盘棋，深化高速公路收费体系改革，降低中国流通成本提供了数据基础；另一方面，国家相关部门依赖这些数据，进行一些更加精准的分析，又能进一步推动交通行业的发展。
几个典型的应用场景
（1）车辆通行查询
全国高速公路取消跨省收费站后，所有的车辆经过龙门架后，自动计费。全国1.9亿的ETC，和25万的龙门架，针对这些海量龙门架数据和ETC数据做统计查询，既要保证能存的下，还要要查的快，算的准，这时选用ADB是一个很好的选择方案，既能满足按照门架id、车牌号等任意维度的统计分析，还能满足存储下这些海量的数据，做到比较弹性的扩展。
通常表和数据库的设计要注意如下几点：
- 保证表的数据在各个节点分布均匀，不能将门架号做为分布键，因为在有些区域的车流量特别大，对应的门架的数据量会比较大，有些经济欠发达地区，车流量很少，门架的车流数据很少，这样会导致很严重的数据倾斜。可以考虑把把ETCID 或者车牌号做为分布键，将门架id设计为聚集索引。
- 由于要满足海量的数据实时写入，在建表和数据库调优时要充分考虑数据实时写入的性能，设计的主键不宜太长，构建索引的频率选择一个合适的平衡点，最好和实时写入峰值的时间错开等等。
（2）数据稽核
作为智慧高速的一部分，既要提供车流实时分析、通行费用结算、智能交互等多样的应用服务，又要加强高速管理，对偷逃费等行为具备智能稽核的能力。在稽核业务中，关于可疑车辆的行驶轨迹查询是非常常见的场景，可是关于可疑车辆的信息描述则可能非常的模糊，比如“车牌尾号是“NV0”的车辆”。如果只是使用倒排索引，车牌 like ‘%NV0’这种查询条件不可避免的会触发全表扫描，在数据量这么巨大的场景下，全表扫描的代价是十分巨大的。如果对车牌字段构建全文索引，返回结果中又会包含很多‘NV0’不在末尾的车牌信息。最佳的解决方案是在表结构中冗余一个车牌字段，并将它创建全文索引，同时使用这两个字段作为查询条件：先用match(车牌_2) against (’“NV0”')筛选出所有包含NV0的车牌信息，在此基础上，再用车牌_1 like '%NV0’筛选出NV0在结尾的车牌。（注释：车牌_1和车牌_2均存储车牌信息，为车牌_1构建倒排索引，车牌_2构建全文索引）。
select * from demo where match(licence_code2) against ('"NV0"') and licence_code like '%NV0';
（3）数据分发
在有些专有云场景下，由于不同的ISV，不同的部门对数据有着不同的需求和访问权限，甲方的数据资产部门不可能把核心的数据仓库（基于ADB构建的实时数仓）全部开放给需求方，即使可以用子账号的方式开放，但是对于一些不同网络环境的部门的数据需求就无法满足，在这种场景下，可以使用ADB的dump能力把实时数仓的数据dump到不同的需求方的oss的bucket里面，需求方根据约定的规则到oss上取数据。具体架构如下：
这样的应用，ADB在满足实时数仓的高效分析的同时，还起到了数据分发HUB的作用，让数据资产部门能很好的掌控数据。
FAQ:
1. 磁盘占用大小包含哪些数据？为什么会触发磁盘满锁定？
磁盘占用量主要包括数据和索引两部分。索引在构建过程中，会临时额外占用少量空间，期间可能会有少量数据膨胀。
用户可以使用如下sql查询使用空间：（延迟统计的，1小时统计一次）
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种可能原因：一是一级分区键选择不合理导致某些节点数据倾斜，二是数据分布比较平均，总体使用量过大。是否存在表有分区倾斜可以在控制台页面观察。
2. 是否支持磁盘大小扩缩，是否支持节点数扩缩？节点数扩缩需要多久?
目前磁盘使用ecs云盘，只支持扩容，不支持缩容。节点数支持扩缩，数量范围与实例初始规格相关，控制台变配页面可以看到当前实例节点数变配范围。节点数扩缩会在节点间进行部分数据迁移，正常情况下最大耗时为最大单节点磁盘使用量/40(MB/s) + 20min。
3. 如何进一步提高写入性能？
数据写和导入尽可能使用批量写入的方式，使用dataworks进行数据同步可关注是否并发任务数和写入批大小设置过小。主键选择尽可能精简。写入表的分区键选择尽可能均衡。
4. 如何选择合适的一级分区列
ADB内部将数据拆分为若干个一级分区，通常情况下一个ADB实例内部大概有100数量级左右的一级分区。在进行查询时不同的一级分区并发进行。因此一级分区列最重要的一点是需要保证数据尽可能的均匀，否则会出现长尾查询拖慢整体查询进度。
不同的表如果一级分区列相同，那么这些表在执行以一级分区列为join key的join时可以大幅度减少数据shuffle。因此在保证数据均匀的前提下，相同的一级分区列可以加速join。
5. 如何选择合适的二级分区列
二级分区是对一级分区的进一步拆分，一般是在时间维度上进行。大部分情况下单二级分区的数据尽量超过一百万，达到百万级，同时也不要达到数千万。
下面以一张订单表为例来选择合适的二级分区。假设这张表单天增量百万左右，需要保留10年的数据。由于我们单ADB集群通常情况下，有100左右的一级分区。若该表按日为分区，则单二级分区的大小约为1w左右远低于我们的建议值。因此用月或者年作为二级分区比较合适。
二级分区的生命周期是支持修改的。如下语句: alter table lineitem partitions 12展示了如何将lineitem的二级分区个数修改为12。需要注意的是二级分区个数的修改是后台异步执行的，执行build table lineitem可以加速分区修改任务。
6. 二级分区的过期策略是怎样的。
目前二级分区过期策略是依据大小排序，只保留最大的N个二级分区，其中N为生命周期的大小。假设表A定义的生命周期个数为3，目前存在的二级分区为202001，202002，202003。当分区值为20204的数据写入进来时202001分区就会被淘汰。需要注意的是分区淘汰是延迟进行的，不保证20204的数据写入后立即会淘汰202001。此外在使用二级分区时也要注意脏数据带来的误淘汰问题，如果此时表A分别写入了分区值为300001,300002,300003的三条脏数据，那么分区淘汰策略也会被触发，整表将只剩下分区值最大的三条脏数据。
7. 聚集索引是什么，什么情况下适合使用聚集索引。
聚集索引就是让数据根据若干字段进行排序。对于有这相同的排序字段的数据在物理上尽可能的存储在一起。
如果查询一定会带的某个字段，比如电商中卖家透视平台，每个卖家只访问自己的数据，那卖家id就是可以选择为聚集索引，可以保证数据的locality，进而性能有量级的提升。
目前聚集索引只支持一个，但该聚集索引可以有多列。目前除非对非常分散的数据进行点查，否则聚集索引对性能的帮助很少，请谨慎选择。
8. 主键如何选择，是否能够修改主键？
主键一般情况下用于数据的去重。主键的长度与去重的效率成反比，因此非常不建议使用较长的String如UUID作为主键，建议为1~3个long值。
此外需要注意的是，主键需要包含一级分区键和二级分区键。目前不支持主键的修改。
9. 如何自己指定索引
a. ADB默认是全字段索引，一般不需要自己维护索引。
b. 如何查看一个表有哪些索引，跟mysql一样使用这个语句：show index from t
c. 如果想要drop掉某个索引可以使用：alter table t drop key key_name。其中key_name可以通过上面的语句查询。注意drop掉索引会导致查询变慢
d. 如果想要自己指定索引，那跟mysql一样，使用key关键字：key key_name (column_name)。如：create table t(id bigint,c1 varchar,key id_idx(id))DISTRIBUTE BY HASH(id)
10. 直接用mysql的建表ddl可以在adb中执行建表吗？
可以的，具体行为是这样的：
• 如果DDL中有主键，用主键做distribute key
• 如果DDL中没有主键，会自动给他添加一个字段：adb_auto_id，然后用__adb_auto_id__做主键和分区键
11. 可以直接用adb2.0的建表语句在adb3.0中执行吗？
可以
参考文档：
李华植：《海量数据库解决方案》
白鳝 ：《DBA的思想天空》
叶正盛：《面向程序员的数据库访问性能优化法则》
南靖：《新一代游戏数据运营融合分析最佳实践V2.docx》
王庶：《全国高速公路取消省界收费站项目落地实践》
