【阿里云 MVP 月度分享】DRDS分布式数据库的CRUD调优指南

2021-07-20 01:08:59

优化的核心原则

由于 DRDS 是一个分布式关系数据库服务，处理的是分布式关系运算。分布式无疑会带来额外的跨库网络开销，而大家都知道，网络通信的延迟比单机内通信的延迟大得多。因此分布式环境中优化更应侧重考虑：

减少网络传输；
减少 DRDS 计算量，尽量将计算下推到下层的数据节点上，让计算在数据所在的机器上执行；
充分发挥下层存储的全部能力。

数据库表创建优化

数据库表的创建优化是最基本的数据优化，是需要在数据模型建立时就需要确定数据的存储、分片和路由的方式。如果模型设计的不够科学合理，后期通过应用的 SQL 来优化，都是成效甚微的。特别是对 DRDS 分布式数据库而言，一旦数据表建立了，后期的分库分表的拆分方式是无法进行修改的，即便数据表删除重建，数据的恢复相对麻烦。

在 DRDS 的数据库表中，主要存在有以下几种形式：

单库单表

对于数据量不大的数据表，可以如同普通的单库 RDS 表一样，建立单库单表。但由于 DRDS 上单库单表只存在 0 库上，和其他不在 0 库的表可能存在跨库 JOIN 的风险。此外，过多的单库单表，容易造成 DRDS 后端分库资源损耗不平衡，0 库的 IO 消耗过大的问题，应尽量予以避免。

小表广播

对于数据量少，且数据变化不频繁，数据一致性要求不高的单库单表，为了解决上述的跨库问题，可以考虑使用小表广播。小表广播是指将表复制到每个分库上，在分库上通过同步机制实现数据一致，但存在秒级延迟。好处在于，可以将 JOIN 操作下推到底层的分库，来避免跨库 JOIN，提高执行效率。如下所示，建表时使用BROADCAST关键字：

CREATE TABLE users (
  user_id int, 
  user_name varchar(50), 
  create_time date,
  primary key(id)
) ENGINE=InnoDB BROADCAST;

除了使用命令，也可以在 DRDS 控制面板上指定广播表。
但需要额外注意，小表广播的使用限定：

表数据量少，尽量不应超过 10 万；
数据的更新不能太频繁；
过大的表和更新过于频繁，都容易增加 DRDS 数据库底层 IO 压力和网络消耗。

分库分表

DRDS 在后端将数据量较大的数据表水平拆分到后端的每个 RDS 数据库中，这些拆分到 RDS 中的数据库被称为分库，分库中的表称为分表。拆分后，每个分库负责每一份数据的读写操作，从而有效的分散了整体访问压力。而分库分表优化的目的在于，重点减轻分布式环境中的网络 IO 开销，尽量将 SQL 中的运算下推到底层各个分库执行，从而减少网络 IO 开销、提升 SQL 执行效率。

如何确定是否分库分表

一般情况下，单个物理分表的容量不超过 500 万行数据。我们通常可以预估未来的数据增长量，用估算出的总数据量除以总的物理分库数，再除以建议的最大数据量 500 万，即可得出每个物理分库上需要创建的物理分表数。阿里公有云环境上，每个 RDS 实例上默认会创建 8 个物理分库，因此公式如下：

物理分库上的物理分表数 = 向上取整(估算的总数据量 / (RDS 实例数 * 8) / 5,000,000)

如果取得的分表数<=1，可以考虑只分库，不分表。如果>1，则除了分库外，最好在各物理分库上进行分表。

拆分方式

要实现数据表的分库分表的拆分，主要包括 2 个方面，即拆分键和拆分算法（函数）。例如，以下建表语句中对数据表做了分库处理：

CREATE TABLE users (
  user_id int, 
  user_name varchar(50), 
  create_time date,
  primary key(id)
) ENGINE=InnoDB dbpartition by hash(id);

其中 dbpartition by 指的是需要分库，hash(id) 指的是分库的拆分键是 id 字段，分库使用的拆分算法是 hash。

选择拆分键的原则

拆分键即分库/分表字段，DRDS 根据拆分键的值将数据表水平拆分到每个 RDS 实例上的物理分库中。

DRDS 目前支持数字型、字符型、日期型的字段做拆分键；
尽可能找到数据表中的数据在业务逻辑上的主体，并确定大部分（或核心的）数据库操作都是围绕这个主体的数据进行，然后可使用该主体对应的字段作为拆分键；
根据数据分布和访问的均衡度来考虑拆分键，尽量将数据表中的数据相对均匀地分布在不同的物理分库/分表中;

拆分键的合理选取，对于数据表的性能，起着至关重要的作用。如果未能科学合理的选取拆分键，可能造成：

数据分布不均匀，各底层 RDS 库的 IO 无法有效分摊，造成有的分库空闲，有的分库繁忙；
上层应用查询处理数据时，无法带上拆分键，造成大量数据汇总和计算需要由底层各节点 RDS 汇集到 DRDS 层，大大增加网络 IO 开销和 DRDS 内存资源损耗。同时由于查询执行时间加长，容易造成 DRDS 数据库连接池无法及时释放；
过多的跨库语句也造成增加应用中跨库事务处理的复杂性，降低了系统 TPS。

拆分算法的选择

拆分算法决定了分片数目与路由算法。
目前常用的拆分算法主要有：

哈希函数，常用如 HASH，UNI_HASH;
日期函数，常用如 MM，DD，WEEK，YYYYMMDD等;
双字段哈希函数，RANGE_HASH。
除了 RANGE_HASH 可以同时用2个字段外，其他拆分算法都只支持单字段。

当数据表通过上述的拆分键、拆分算法被拆分为多个分库分表时，数据在分库分表的分布规则就固定了。但是通常数据的业务使用场景非常复杂，如果数据的查询纬度和数据拆分分布的规则一致，单条 SQL 会在一个分库分表上执行；如果数据的查询使用纬度和数据拆分分布的规格不一致，单条 SQL 就很有可能在多个分库分表上执行，出现跨库查询，跨库查询会增加网络 I/O 的成本，查询效率必然下降。

这时候可使用拆分算法RANGE_HASH和异构索引来实现。

RANGE_HASH

双字段哈希函数 RANGE_HASH 与其他拆分算法不同，可设定2个拆分键，是根据任一拆分键后 N 位计算哈希值，然后再按分库数去取余，完成路由计算。N 为函数第三个参数。例如：RANGE_HASH(COL1, COL2, N) ，计算时会优先选择 COL1，截取其后N位进行计算。 COL1 不存在时找 COL2。适用于需要有两个拆分键，并且查询时仅有其中一个拆分键值的场景。
但有以下约束：

两个拆分键的类型必须是字符类型或数字类型；
两个拆分键皆不能修改；
插入数据时如果发现两个拆分键指向不同的分库或分表时，插入会失败。
RANGE_HASH 只能解决2个查询维度的问题，超过 2 个以上的，则需要使用 异构索引 。

异构索引

异构索引 的本质实际采用的方式就是用“空间换效率”的方案，也就是将同一份数据表，冗余存储多份，按照不同的业务使用场景进行拆分，保持拆分纬度和使用纬度统一，而多份数据之间会实时数据复制以解决数据一致性问题。

使用异构索引主要注意的是：

过多的异构索引表会影响同步效率，对源数据表造成同步压力，同时增加数据库底层 IO 压力；
异构索引表之间的同步存在秒级延迟，对实时数据一致性要求较高的存在风险；
异构索引数据的修改更新只能通过主表进行，索引表只读。
目前公有云上的异构索引仍处于内部测试阶段，建立必须通过后台工单的方式。

建表语句中的自增长

在我们建数据表时，经常有使用到自增长的字段类型，有时候也拿来做表的ID主键。在传统的RDS单数据库中，将某个字段设置成自增长，可以实现该字段的全局唯一的单调连续递增。但DRDS将数据库表进行了分库分表，是否能够保证此自增长字段也具有上述特性呢。事实上，在 DRDS 中，当拆分表和广播表的字段指定了 AUTO_INCREMENT 后，DRDS 就会创建隐式的 Sequence 来生成全局唯一和有序递增的数字序列。注意，非拆分表的 AUTO_INCREMENT 的值是由底层 RDS 自己生成的。
目前 DRDS 的 Sequence 有 3 种类型：GROUP(默认)、TIME、SIMPLE。语法如下：

CREATE TABLE <name> (
   <column> ... AUTO_INCREMENT [ BY GROUP | SIMPLE | TIME ],
   <column definition>,
   ...
) ... AUTO_INCREMENT=<start value>

需要注意的区别是：

GROUP，是系统默认的，大部分场景下建议选用。全局统一，但非连续的非单调递增的（整体来看），不能循环。优点，速度快，不存在性能问题；
TIME，只能用于 BIGINT 类型字段，数值完全随机，全局统一，性能高；
SIMPLE，最接近于传统 RDS 的自增长序列，全局唯一、连续、单调递增。但是由于每次产生都需要持久化，效率较低，容易造成性能瓶颈，除非特定业务强制性要求，否则请谨慎使用。

SQL优化

DRDS 对 SQL 的优化方法与单机关系数据库有所不同，侧重考虑分布式环境中的网络 IO 开销，应尽量将 SQL 中的运算下推到底层各个分库执行，避免全库扫描，从而减少网络 IO 开销、提升 SQL 执行效率。

本文侧重DRDS的优化，并不等于原有单库的SQL优化策略就不适用。当最终SQL语句下推到下层RDS分库节点执行时，原有MYSQL的优化策略也是起效的；
下面介绍的有些DRDS的SQL优化策略是与单机RDS(MYSQL)是完全相同的。

基于上述核心原则，可以有以下优化方向：

条件优化

所谓条件优化，就是优化 SQL 语句 RUD 中 WHERE 中过滤条件的优化，包括了 SELECT、UPDATE、DELETE。

尽量包含拆分键
基本原则是，过滤条件中应尽量包含带有拆分键的条件，可以让 DRDS 根据拆分键对应的值将查询直接下推到特定的分库，避免全表扫描。经测试，在 10 万记录的分库表中，过滤条件中含有拆分键的比不含拆分键的，速度秒数至少提高3个数量级。

含拆分键的过滤条件的取值范围越小，速度越快。例如，执行消耗时间上的对比

= < 区间 < 子查询

以上区间的记录条数<子查询记录条数
经常有些时候，查询很难明确目前拆分键的值，这时候，可能需要中间表或存储进行转换。例如，查询A表时，过滤条件并不能明确A表的拆分键值或者范围，这时候可先从 B 表（或 REDIS 存放）查出相应的拆分键值或者范围，然后在叠加到 A 表的过滤条件中，以提高语句执行效率。

避免类型转换和列运算
WHERE 条件中使用类型转换和列运算，一方面会导致分库中索引无法使用，进行全表扫描，另外也会增加 DRDS 的计算负担。例如：

SELECT order_id FROM orders WHERE YEAR(create_time)<2017;

应改成：

SELECT order_id FROM orders WHERE create_time<’2017-01-01’;

用 NOT EXIST 替换 NOT IN
NOT IN 无法使用索引，会进行全表扫描，将加重分库的负担。
尽量少排序
如果业务上无强制排序要求，尽量不要进行 ORDER BY 排序。如果用的话，最好用在带有索引的列上，或者考虑在应用中进行排序。因为在 DRDS 中，各分库的排序必然是需要推到各个分库中排序并将结果汇集到DRDS内存中进行计算。

数据量优化

基本原则，查询交互和返回的数据量应保持尽量少。

字段补全
SELECT * 这种未明确指定返回字段的 SQL 语句应尽量避免。只把需要的字段返回回来，这样不仅可以加快数据读取速度，同时也可以减轻网络 IO 的消耗；
返回记录限定
有时业务仅需要返回一条记录，就可以使用 LIMIT 1 来终止数据库引擎继续扫描整个表，减少数据库IO损耗；
少用UNION
可以考虑使用 UNION ALL 替代 UNION。UNION 和 UNION ALL 的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出重复结果或者不在乎重复结果的时候，尽量使用 UNION ALL 而不是 UNION。目前，貌似 UNION 在 DRDS 中等于 UNION ALL，尚不具有去重功能；
LIMIT优化
我们在数据清单查询时，经常使用到分页，底层通过 SQL 语句携带 LIMIT 关键字来实现指定游标记录的返回。DRDS 在执行带有 LIMIT [ offset_, ] _row_count 的查询时，实际上是依次将 offset 之前的记录读取出来并直接丢弃，这样当 offset 非常大的时候，即使 row_count 很小，也会导致查询非常缓慢。例如：

SELECT * FROM users LIMIT 10000000,10;

DRDS 实际上的操作是，从各分库中读取0~10000010的记录，汇集到上层后，再将多余的记录丢弃掉，导致了产生大量无用网络IO。针对上述情况，SQL 优化方向是先查出 ID 集合，再通过 IN 匹配真正的记录内容：

SELECT * FROM users WHERE user_id IN
(SELECT user_id FROM users LIMIT 10000000,10);

这种优化当返回的_offset_很大，且字段数比较多的时候，效果尤为明显。

关联优化

原则上，应尽可能将 JOIN 查询优化成能够在分库上执行的可下推的 JOIN 形式。如果一个 JOIN 查询不可下推（即 JOIN 条件和过滤条件中均不带有拆分键），则需要由 DRDS 完成查询中的部分计算。正如上述条件优化所述，各关联表的过滤条件中应尽量包含拆分键，有利于将查询下推到特定的分库，避免全表扫描。更进一步，如果能达到关联各表中所带的分库拆分键是相同的，则能保证在同一物理分库中，则效率更高。

单库单表形成广播表
小表形成广播表后，当同分库中的表进行 JOIN 时，语句就可以下推到分库中执行，提高执行效率。
子表查询优化
应尽量改成 JOIN 的形式。通过 EXPLAIN 可以查看到子查询是一个相关子查询( DEPENDENCE SUBQUERY )。分库是先对外表A执行全表查询，然后根据 ID 逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。
JOIN 优化
应尽量将数据量较小的表作为驱动表，也即 LEFT JOIN 的左表。如果有过滤条件应当尽量放到驱动表的过滤条件中，将驱动表的数据集尽可能缩少。在此优化规则中，如果驱动表很小，关联表很大，那么优化后的效率将会提高很多。

例如：

SELECT * FROM a LEFT JOIN b ON a.id=b.ref_id WHERE b.ref_id>10;

可以优化为：

SELECT * FROM (SELECT * FROM a WHERE id >10) a LEFT JOIN b ON a.id=b.ref_id;

关联表个数限定
建议不能下推到同一个物理分库的 SQL 语句中，表关联尽量不要超过 2 个。超过 2 个以上的语句，可考虑在应用中拆分成多次查询。

合理利用HINT

用 HINT 限定读取只读实例
当 DRDS 配置了读写分离时，应用会自动根据配置的读权重来透明地选取是从主实例还是只读实例中读取数据。

而通过显式地在 SQL 语句中使用 HINT，可指定语句是在主实例上执行还是在只读实例上执行。如下所示，指定了只在只读实例中执行：

/!TDDL:SLAVE*/ SELECT * FROM users;

好处在于，对于应用中数据一致性要求不高的，个别资源消耗较大的或者执行时间较长的 SQL 语句，通过显式指定在只读库上执行，可以避免分配到主实例库上，消耗主库的资源。

定义SQL语句执行超时时间
在 DRDS 中，DRDS 节点与 RDS 的默认的 SQL 执行超时时间是 900 秒，但是对于某些特定的慢 SQL，其执行时间可能超过了 900 秒。针对这种慢 SQL，DRDS 提供了调整超时时间的自定义 HINT。通过这个自定义 HINT 可以任意调整 SQL 执行时长。如下所示，设置语句超时时间 30 秒：

/!TDDL:SOCKET_TIMEOUT=30000*/ SELECT * FROM users;

注意：超时时间设置得越长，占用数据库资源的时间就会越长。如果同一时间长时间执行的 SQL 过多，可能消耗大量的数据库资源，从而导致无法正常使用数据库服务。

索引优化

在索引字段尽量不要出现空值，这样将导致用不到索引，可以考虑建表时字段增加默认值以避免空值出现；
创建二级索引时，最好创建在查询相对多，更新相对少的字段上；
创建联合索引要注意字段排列顺序，把过滤最多记录数的字段放在第一位，以此类推；
目前 DRDS 中，PK 和 UK 只能保证在某个分库(分表)是唯一的，并不能保证全局。如果要做到全局唯一，必须同时还是分库键才行

配置读写分离

通过配置 DRDS 读写分离功能，能有效对主实例的读流量进行分流，减轻 RDS 主实例的读压力。同时，从另一个角度来说，从只读实例上进行读取时，也避免了主实例写库时事务锁造成的数据库 IO 问题，加快了数据的读取速度。
由于DRDS 的读写分离功能是对应用透明的设计，应用可以在不修改任何代码的情况下，调整读权重，即可将读流量按配置的比例在主 RDS 实例与多个只读实例之间进行分流；写流量则全部到主实例，不做分流。

优化常用指令

工欲善其事，必先利其器。DRDS为我们提供了一系列的指令，便于我们跟踪数据库表的创建和SQL语句执行情况，方便进行调优。以下是比较常用的指令：

查看数据表存储

使用 SHOW CREATE TABLE 指令检查表结构；
执行 SHOW TOPOLOGY FROM TABLE_NAME 指令获取表拓扑结构，例如分成了几个库几个表等；
使用 SHOW FULL RULE查看数据库下逻辑表的拆分规则。

查找慢SQL

使用 SHOW PROCESSLIST 指令观察所有当前执行的 SQL 状态；
使用 SHOW FULL SLOW 指令显示历史逻辑慢 SQL，即应用发送到 DRDS 的 SQL；
使用 SHOW FULL PHYSICAL_SLOW 指令显示历史物理慢 SQL，即 DRDS 发送到 RDS（MySQL）的 SQL。

查看语句执行计划

使用 EXPLAIN SQL 指令查看指定 SQL 在 DRDS 层面的执行计划。可观察到SQL语句在DRDS中的分片情况和路由算法；
使用 EXPLAIN EXECUTE SQL 指令查看底层存储的执行计划，等同于 MYSQL 的 EXPLAIN 语句。可观察到SQL在各分库中如何执行语句操作及索引使用情况；
搭配使用 TRACE SQL 和 SHOW TRACE 指令可以查看具体 SQL 的执行情况，需要注意，使用TRACE，SQL语句会真正执行。

后记

路漫漫其修远兮，吾将上下而求索。性能优化是一个庞大、复杂而又细致的工作，不可能一朝一夕一蹴而就，范围可涉及到硬件、网络、应用、存储等等层面，绝不能脱离整体而片面、孤立地看待问题。本文针对分布式关系型数据库 DRDS 的优化做了一些入门简要介绍，仅仅是冰山上的一角，希望借此能帮助大家快速入门，能逐步挖掘释放 DRDS 效能，扬长避短。

文中部分内容借鉴了阿里官网DRDS的用户指南，更多的实例大家可以参阅官方文档。内容如有不正确、不合理的地方，也欢迎大家批评指正。

码农公寓