共计 5832 个字符，预计需要花费 15 分钟才能阅读完成。
简介：本篇文章总结了 AnalyticDB 表的设计的最佳教训、数据写入的最佳教训、高效查问的最佳实际，以及一些常见的问题。
随着云原生数据仓库 AnalyticDB for MySQL（下文对立简称：AnalyticDB）在阿里团体各个业务线、社会上各行各业的推广应用，咱们积淀了一些最佳实际，当初笔者整顿在这里，供大家参考，心愿对大家有帮忙。本篇文章总结了 AnalyticDB 表的设计的最佳教训、数据写入的最佳教训、高效查问的最佳实际，以及一些常见的问题。
阐明：
1. 在读这篇文章之前，请先理解 AnalyticDB 的产品官网文档，以提前适当理解 AnalyticDB；
2. 本文写的最佳实际次要针对 AnalyticDB 3.0，AnalyticDB 2.0 在原理上也同样实用。
01 表设计的最佳实际
AnalyticDB，作为一个分布式数据仓库，可能为海量数据的实时剖析带来卓越的性能体验。为了充分发挥 AnalyticDB 在数据分析方面的性能劣势，设计表时，须要留神以下几点规定。
（一）抉择适合的表类型（维度表 or 一般表）
维度表：又称播送表，是数据仓库中的一个概念，个别存储维度数据。在 AnalyticDB 中建表语句中有 DISTRIBUTED BY BROADCAST 的关键字，这些表会在集群的每个节点存储一份数据，因而维度表的数据量不宜太大，倡议每张维度表存储的数据不超过 2 万行。
留神：维度表太大，会导致数据存储空间的收缩，节点越多收缩越大，同时也会导致实时写入时性能降落，IOPS 会比拟高。
一般表：也叫作分区表、事实表，个别存储业务的主题数据。一般表可存储的数据量通常比拟大，能够存储千万条甚至万亿条数据，能够通过一级分区对数据做分片以及二级分区对数据进行生命周期治理。
（二）抉择适合的散布键（一级分区键）
AnalyticDB 中创立一般表时，默认须要通过 DISTRIBUTED BY HASH(column_name,…)指定散布键，依照 column_name 的 HASH 值进行分区。
AnalyticDB 反对将多个字段作为散布键。
散布键的抉择根据：
- 尽可能抉择值散布平均的字段作为散布键，例如交易 ID、设施 ID、用户 ID 或者自增列作为散布键；
- 尽可能抉择参加 JOIN 的字段作为散布键，例如进行用户画像剖析时，能够抉择 user_id 作为散布键。
留神：散布键不平均容易导致数据分布不均，重大影响写入和查问的效率，此外也容易使单节点磁盘写满从而导致整个集群锁定不可用。除非凡的业务场景外，建表优先思考数据是否平均，而后再思考 JOIN KEY 对齐的问题。
（三）抉择适合的分区键（二级分区键）
对于表的数据量十分大的表，须要思考创立二级分区表来对数据做进一步的切分，设置了二级分区后，也能带来两个益处：
1）对数据进行生命周期治理，比方设置了肯定数量的二级分区数量后，过期的二级分区会主动被淘汰掉；
2）当查问条件带上了二级分区字段时，是能够对二级分区进行裁剪的，从而晋升查问的性能。
- 间接用 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’))
二级分区的注意事项：
请提前布局好实例中所有表的二级分区键，充分利用二级分区，不要让每个二级分区的数据量过小，如果，用天进行二级分区，每天数据量很小，那么能够思考用月作为二级分区。二级分区数据量过小，会导致数据库中须要保留分区数据的元数据特地多，而这些元数据寄存在内存中，过多的元数据会占据较多的内存空间，导致系统的 GC 或者 OOM，同时也会导致实时写入的 IOPS 较高。
二级分区的数据量倡议：
（四）抉择适合的主键
在表中定义主键能够实现数据消重 (REPLACE INTO) 和数据更新(DELETE、UPDATE)。只有定义过主键的表反对数据更新操作（DELETE、UPDATE）。
主键的抉择根据：
- 尽可能抉择数值类型的单个字段作为主键，表的性能绝对更好。
- 如果数值类型的繁多主键无奈满足业务须要，也能够应用字符串或者多字段组合作为主键。
- 主键中必须蕴含散布键和分区键，如果表中定义了二级分区键的话，主键必须蕴含二级分区键。
留神：作为主键的字段不宜太大，字段的长度不宜过长，否则会影响写入的性能。
（五）抉择适合汇集索引
汇集索引会将一个或者多个字段排序，保障该字段雷同或者相近的数据存储在磁盘的雷同或相近地位，当以汇集索引中的字段作为查问条件时，查问后果放弃在磁盘的雷同地位，能够缩小磁盘的 IO。
汇集索引的抉择根据：
查问肯定会携带的过滤条件的字段能够设计为汇集索引。例如，电商卖家透视平台中每个卖家只拜访本人的数据，卖家 ID 能够定义为汇集索引，保证数据的局部性，晋升数据查问性能。
留神：目前只反对一个汇集索引，但一个汇集索引能够蕴含多列。目前除非对十分扩散的数据进行点查，否则汇集索引对性能的帮忙很少。
（六）设计适合的数据类型
倡议用户尽可能应用数值类型，缩小应用字符串类型。
AnalyticDB 解决数值类型的性能远好于解决字符串类型，起因在于：
- 数值类型定长，占用内存少，存储空间小。
- 数值类型计算更快，尤其是在数据关联场景。
- 从外部索引机制上，字符串类型适宜等值查问和范畴查问，而工夫类型、数值类型性能更好。
- 抉择尽可能小的字段长度，比方，性别能够应用 Boolean 或者 Byte 类型，数据长度不大的能够用 Int 类型。
- 在同一个业务模型内，雷同字段设计成雷同的数据类型和字段长度，字段命名也保持一致，特地是波及到主外键关联的字段更要留神，防止不同的数据类型的字段关联导致隐式转换。
常见字符串数据的解决倡议：
- 蕴含字符前缀或后缀，例如 E12345，E12346 等。倡议去掉前缀或者将前缀映射为数字。
- 字段只有少数几个值，例如国家名。倡议对每个国家编码，每个国家对应一个惟一数字。
- 工夫 / 日期类型数据，防止应用 Varchar 字符类型存储，尽量应用 Date，Timestamp 或者 Int 类型。
- 天文的经度 / 纬度数据，倡议采纳 Double 数据类型进行存储。
如果您在建表前，不分明本身业务的数据分布特色，可在数据导入后，应用优化倡议进行优化。具体请拜访 AnalyticDB 控制台的建表诊断页面：https://help.aliyun.com/docum…，查看建表问题及优化倡议。
02 数据写入的最佳实际
（一）实时写入
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 批量打包。
- 高频基于主键的行级笼罩更新, 利用不能补齐所有列，请应用 INSERT ON DUPLICATE KEY UPDATE 批量打包。
- 低频任意条件更新，请应用 UPDATE SET WHERE。
留神：UPDATE 须要查表来填补更新中缺失的旧值，因而比 REPLACE INTO 多一次查问，性能较低，不倡议做高频、大批量的 UPDATE 操作。如果线上 UPDATE 性能无奈满足需要，需思考替换成 REPLACE INTO，由利用端填补旧值。
3. 删除数据
数据删除有多种形式，应用区别如下：
- 低频主键条件删除，请应用 DELETE FROM WHERE primary key = xxx。
- 低频任意条件删除，请应用 DELETE FROM WHERE。
- 删除单个二级分区，请应用 TRUNCATE PARTITION。
- 删除单表（包含所有二级分区），请应用 TRUNCATE TABLE 或 DROP TABLE。
（二）批量导入
1. 如何抉择批量导入还是实时导入
- 从 ODPS、OSS 导入 AnalyticDB，举荐应用 INSERT OVERWRITE SELECT 做批量导入，有以下两个起因：一，批量导入适宜大数据量导入，性能好；二，批量导入适宜数仓语义，即导入过程中旧数据可查，导入实现一键切换新数据，如果导入失败，新数据会回滚，不影响旧数据的查问。
- 从 RDS、MySQL、AnalyticDB 等导入 AnalyticDB，依据数据量状况，如果数据量不大（百万级别的表），举荐应用 INSERT INTO SELECT 做实时导入；如果数据量较大，举荐应用 INSERT OVERWRITE SELECT 做批量导入。
- 对雷同的一张表，不能既采纳 INSERT OVERWRITE SELECT 又采纳 INSERT INTO SELECT 操作，否则数据会被笼罩。
2. 导入并发和资源阐明
- 单张表的导入会在零碎外部排队串行，而多张表的导入，会产生 n 个并行导入工作（并行度可调整，默认并行度是 2），出于资源管制的思考，超出并行度的工作也会排队。
- 数据导入，同查问一样，会耗费 AnalyticDB 实例的计算资源。因而，倡议在查问 QPS 较低时执行数据导入，并举荐通过定时工作进行错峰导入。
03 高效查问的最佳实际
AnalyticDB 的劣势是能在海量数据场景下，面对简单查问，做到实时的在线剖析。AnalyticDB 的查问调优，不仅兼容数据库查问优化的通用办法，还提供一些专门的优化办法，使其可能充分发挥出分布式计算的性能劣势。
（一）查问优化的通用法令
依照叶正盛早些年在《ORACLE DBA 手记》上写的文章，数据拜访优化满足以下漏斗法令：
1. 缩小数据拜访（缩小磁盘拜访）
尽量多的应用过滤条件，尽早的提前过滤数据，从而缩小参加计算的数据量，例如在子查问里提前把能过滤的数据先过滤。
2. 返回更少数据（缩小网络传输或磁盘拜访）
在 OLAP 数据库中，因为表的列数往往比拟多，且是基于列存或者行列混存，所以 SELECT 的操作，会导致较多的申请 IO。因而，请尽量避免 SELECT 的查问。
3. 缩小交互次数（缩小网络传输）
倡议应用上文提到的批量导入，缩小交互次数。
4. 缩小服务器 CPU 开销（缩小 CPU 及内存开销）
- 缩小不必要的排序和分页，特地是子查问中的排序。
- 在满足业务前提下，尽量减少 COUNT DISTINCT 操作。
- 在满足业务前提下，特地是在海量数据下，采纳相似 Hyperloglog 的近似计算代替精确计算。
5. 利用更多资源（减少资源）
设计表的时候，尽量避免分区歪斜，不要把存储和计算压在某一个节点上。倡议尽量把数据都平均的散列到所有的节点上，充分利用所有机器的能力，最大水平地施展分布式数据库的效力。
AnalyticDB 自身就是 MPP 大规模并行处理的典型零碎，在内核层面做了大量的优化解决，可能充分利用更多的资源。
（二）AnalyticDB 非凡场景的优化
1. 表面查问的最佳实际
- 不举荐应用表面进行简单计算。表面计算会拉取全副数据，因而表面的简单计算会导致重大的 GC，也会给网络带宽造成较大压力。
- 内部表不反对 DML 操作（DELETE、UPDATE、TRUNCATED）。如果须要批改表面数据，请到源表中进行 DML 操作。
2. 正当的应用索引
正当应用索引是数据库调优的一个十分重要的伎俩，AnalyticDB 也不例外。在 AnalyticDB 中，默认每列都会创立索引。然而也有例外情况。如果某列的 Cardinality 值较低，索引的选择性不高，通过索引查问，性能可能会更差。此时，倡议在建表时敞开主动创立索引的性能。如果表曾经建好，能够应用如下 SQL 语句，删除索引或者通过 hint 绕过索引。
ALTER TABLE table_name DROP INDEX index_name;
-- 办法一：删除枚举列的索引
/+no_index_columns=[t_order_content.fdelete;fdbid]/
-- 办法二：通过 hint 使查问绕过索引3. 奇妙的应用汇集索引
当查问条件肯定蕴含某列，特地是该列数据在存储上十分扩散时，对该列建设汇集索引，性能会有显著的晋升。您能够采纳相似如下的 SQL 语句增加汇集索引：
ALTER TABLE table_name ADD CLUSTERED INDEX index_cls (d_fdbid);
留神：如果表中曾经有了数据，间接 ADD CLUSTER INDEX 不会对存量的数据排序，须要从新建表，并在建表的时候加上汇集列关键字；或者在增加完汇集索引后对该表做一次 build 操作：build table table_name force=true。
4. 缩小节点间的数据交互
分布式数据库，在充分发挥分布式计算劣势的同时，有时也会加大跨节点间的网络开销。特地是申请的数据量较少，数据却扩散在较多节点的状况，跨网络开销的状况就非常明显。本文提供以下两个思路：
- 尽量在本地节点内进行 Join，充分利用 Local Join 个性，大大减少跨网络拜访。具体做法为：尽量采纳一级分区键关联；
- 尽量在本地节点内进行聚合剖析，缩小跨网络拜访 shuffle 的数据量。具体做法为：尽量对一级分区键进行 GROUP BY。
04 AnalyticDB 连贯的最佳实际
在应用办法上，AnalyticDB 与 MySQL 的兼容水平高达 99% 以上，反对多种连贯形式，包含 MySQL 命令行，JDBC 连贯，Python 连贯，C# 连贯，PHP 连贯等等。更具体地应用办法，请参考官网文档：https://help.aliyun.com/docum…。
原文链接
本文为阿里云原创内容，未经容许不得转载。
