DRDS-最佳实践

database

发布日期: 2022-12-17

文章字数: 3.1k

阅读次数:

如何选择拆分键

拆分键即分库/分表字段，是在水平拆分过程中用于生成拆分规则的数据表字段。根据拆分键的值将数据表水平拆分到每个数据库实例上的物理分库中。

注意:无论选择什么拆分键，采用何种拆分策略，都要注意拆分值是否存在热点的问题，尽量规避热点数据来选择拆分键。

业务逻辑主体有对应的字段

业务逻辑上的主体，通常与业务的应用场景相关，下面的一些典型应用场景都有明确的业务逻辑主体，可用于拆分键：

面向用户的互联网应用，都是围绕用户维度来做各种操作，那么业务逻辑主体就是用户，可使用用户对应的字段作为拆分键；
侧重于卖家的电商应用，都是围绕卖家维度来进行各种操作，那么业务逻辑主体就是卖家，可使用卖家对应的字段作为拆分键；
游戏类的应用，是围绕玩家维度来做各种操作，那么业务逻辑主体就是玩家，可使用玩家对应的字段作为拆分键；
车联网方面的应用，则是基于车辆信息进行操作，那么业务逻辑主体就是车辆，可使用车辆对应的字段作为拆分键；
税务类的应用，主要是基于纳税人的信息来开展前台业务，那么业务逻辑主体就是纳税人，可使用纳税人对应的字段作为拆分键。

例如，某面向买家的电商应用，由于确定了业务逻辑主体为卖家，那么建议选择对应的字段买家标识作为拆分键。

create table xxx_order (
      id int(11) not null
    , seller_id int(11) not null
    , trade_id int(11) not null
    , buyer_id int(11) not null
    , buyer_nick varchar(64)
    , primary key (id)
) dbpartition by hash(buyer_id) tbpartition by hash(buyer_id) tbpartitions 8;

非业务逻辑主体

根据数据分布和访问的均衡度来考虑拆分键，尽量将数据表中的数据相对均匀地分布在不同的物理分库/分表中，适用于大量分析型查询的应用场景（查询并发度大部分能维持为1）；
按照数字（字符串）类型与时间类型字段相结合作为拆分键，进行分库和分表，适用于日志检索类的应用场景。

例如，某日志系统记录用户的所有操作，可以选择用户标识与时间字段相结合作为拆分键，并按照一周七天进行分表。

create table xxx_log (
      userid int(11) not null
    , name varchar(64) not null
    , operation varchar(128)
    , actiondate date
) dbpartition by hash(userid) tbpartition by week(actiondate) tbpartitions 7;

如何选择分片数

DRDS中的水平拆分有两个层次：分库和分表。
每个 RDS 实例上默认会创建8个物理分库，每个物理分库上可以创建一个或多个物理分表。
分表数通常也被称为分片数。

一般情况下，建议单个物理分表的容量不超过500万行数据。通常可以预估1到2年的数据增长量，用估算出的总数据量除以总的物理分库数，再除以建议的最大数据量500万，即可得出每个物理分库上需要创建的物理分表数：

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

因此，当计算出的物理分表数等于1时，分库即可，无需再进一步分表，即每个物理分库上一个物理分表；若计算结果大于1，则建议既分库又分表，即每个物理分库上多个物理分表。

# 例如，某用户预估一张表在2年后的总数据量大概是1亿行，有4个 RDS 实例，那么按照上述公式计算：
# 结果为1，那么只分库即可，即每个物理分库上1个物理分表。
物理分库上的物理分表数 = CEILING(100,000,000 / ( 4 * 8 ) / 5,000,000) = CEILING(0.625) = 1

# 若上述例子中仅有1个 RDS 实例，那么按照上述公式计算：
# 结果为3，那么建议既分库又分表，即每个物理分库上3个物理分表。
物理分库上的物理分表数 = CEILING(100,000,000 / ( 1 * 8 ) / 5,000,000) = CEILING(2.5) = 3

DRDS中的SQL优化

查看统计信息

-- 查看整体的统计信息，这些信息都是瞬时值
show stats;
show full stats;

-- 用于查看物理库容量/性能信息，所有返回值为实时信息
-- 容量信息通过MySQL系统表获得，与真实容量情况可能有差异
show db status;
show full db status like xxx_db;

-- 获取表的信息，该指令聚合了底层各个物理分表的数据
show table status like 'xxx_tb%';
/!TDDL:SCAN='xxx_db'*/show table status like 'xxx_tb%';

查看执行计划

DRDS执行计划分为两个层次，DRDS层的执行计划与 RDS/MySQL 层的执行计划。对执行计划的分析是进行 SQL 优化的有效方法，可以了解 DRDS 或 RDS/MySQL 是否对 SQL 语句生成了最优化的执行计划，是否有优化的空间等，从而为 SQL 优化提供重要的参考信息。

# 查看 DRDS 层 SQL 语句的概要执行计划，包括执行的分库、物理语句和整体参数。
explain select * from xxx_table limit 10;

# 查看 DRDS 层 SQL 语句的详细执行计划，包括执行语句类型、并发度、返回字段信息、物理表和库分组等。
explain detail select * from xxx_table limit 10;

# 查看底层 RDS/MySQL 的执行计划，等同于 MySQL 的 EXPLAIN 语句。
explain execute select * from xxx_table limit 10;

TRACE指令

TRACE 指令可以跟踪 SQL 的执行过程和各个阶段的执行开销，与执行计划相结合，更有助于对 SQL 进行优化。
TRACE 指令包含两条相关的指令：TRACE 和 SHOW TRACE，需要在一起配合使用。

# TRACE命令会实际执行SQL，在执行过程中记录所有节点消耗的时间，并返回执行结果
trace select * from xxx_table limit 10;

# TRACE指令执行完毕后，可以执行SHOW TRACE命令查看结果，根据每个组件的时间消耗来判断慢SQL的瓶颈。
# 根据TIME_COST（单位毫秒）列可以判断哪个节点上的执行时间消耗大。
show trace;

查询慢SQL

# SQL字符串匹配等方式来获取指定的慢SQL
show full slow where `SQL` like '%select sleep(50)%';

# 根据逻辑慢SQL中获取到的TRACE_ID,获取这个SQL的物理执行情况
show full physical_slow where trace_id = 'XXXXX';

# 可以在RDS的SQL明细与慢SQL中，根据TRACE_ID查看SQL在RDS上的执行情况
show slow where trace_id='XXXXX';

# 查看当前实时SQL执行信息
show processlist where command != 'sleep';

# 查看所有正在执行的物理SQL信息
show physical_processlist;
show full physical_processlist;

# 查看慢SQL记录
show slow limit 10;

# 跟踪 SQL 的执行过程和各个阶段的执行开销
trace {SQL};

# 查看追踪结果，断慢SQL的瓶颈
# 根据TIME_COST（单位毫秒）列可以判断哪个节点上的执行时间消耗大。
show trace;

# 检查该慢SQL语句在RDS for MySQL上的执行计划
/!TDDL:node={GROUP_NAME}*/ EXPLAIN {SQL};

SQL优化的基本原则

基本原则：尽量让更多的计算可下推到RDS for MySQL上执行。
- JOIN连接；
- 过滤条件，如WHERE或HAVING中的条件；
- 聚合计算，如COUNT，GROUP BY等；
- 排序，如ORDER BY；
- 去重，如DISTINCT；
- 函数计算，如NOW()函数等；
- 子查询。

尽量在 JOIN 条件或过滤条件中带上拆分键，避免全表扫描；
拆分键的过滤条件的取值范围越小，越有助于提高的查询速度，等值过滤效率最高，如果拆分键的条件是 IN 条件，则 IN 后面的值的数目应尽可能少（需要远少于分片数，并且数目不会随业务的增长而增多）；
如果 SQL 语句不带有拆分键，那么 DISTINCT、GROUP BY 和 ORDER BY 在同一个 SQL 语句中尽量只出现一种；
尽可能早的减少查询的数据量，比如使用 LIMIT ，子查询中添加过滤条件等；
如果在一条 SQL 查询中必须同时使用 DISTINCT、GROUP BY 与 ORDER BY，应尽可能保证 DISTINCT、GROUP BY 与 ORDER BY 语句后所带的字段相同，且尽量为拆分键，使最终的 SQL 查询只返回少量数据；
在进行 JOIN 操作时，尽可能早的过滤掉不需要的数据后再进行关联，把小表作为驱动表（将 JOIN 中的左表称为驱动表），且让驱动表带有尽可能多的过滤条件；

如何处理DDL异常

失败的情况

DDL在分库执行失败，DDL 在任意分库执行出错都可能导致各分表结构不一致
执行长时间无响应。在对大表执行 DDL 操作时，有可能由于分库的执行时间过长导致 DDL 长时间无响应。

判断 DDL 操作是 In-place 还是 Copy Table 操作，可以查看操作结束后 “rows affected” 这一项的返回值，一个非0的值意味着该操作需要重建整张表，这时可能需要考虑在流量低谷去执行该操作。

执行一个大表 DDL 操作前，可以先通过以下步骤判定这是一个快速或慢速操作：

1.复制表结构生成一张克隆表；
2.插入一些数据
3.在克隆表上执行这个 DDL 操作
4.检查操作完成后 “rows affected” 值是否是0。一个非0的值意味着该操作需要重建整张表，这时可能需要考虑在流量低谷去执行该操作。

失败的处理

1.使用 CHECK TABLE 指令检查表结构。如果返回结果只有一行且为状态正常则可认为表状态一致。此时进行步骤2，否则进行步骤3；
2.使用 SHOW CREATE TABLE 指令检查表结构。如果显示的表结构符合 DDL 执行后的预期则可认为 DDL 执行成功，否则继续进行步骤3；
3.使用 SHOW PROCESSLIST 指令观察所有当前执行的 SQL 状态。如有仍在执行的 DDL 操作，请等候其执行完成后再进行步骤1、2，检查表结构是否符合预期，否则进行步骤4；
4.重新执行 DDL 操作。如果出现 Lock conflict 的报错请进行步骤5，否则进行步骤3；
5.使用 RELEASE DBLOCK 指令释放 DDL 操作锁，然后进行步骤4。

# 使用 CHECK TABLE 指令检查表结构，当返回结果只有一行且显示状态 OK 时，表明表结构一致
check table xxxx;

# 检查表结构
# 使用 SHOW CREATE TABLE 指令检查表结构，如果表结构一致且表结构无误时，可认为 DDL 已执行成功
show create table xxxx;

# 观察当前正在执行的 SQL 语句
# 有些 DDL 执行速度过慢，发现 DDL 长时间无响应后，可执行 SHOW PROCESSLIST 指令观察所有当前执行的 SQL 状态
show [full] processlist where command != 'sleep';
show [full] physical_processlist;

# 发现过慢指令后，可以使用kill命令来取消慢指令
kill process_id;
kill 'physical_process_id';
kill 'ALL';

# Lock conflict 报错处理
# DDL 操作先会加库级锁，操作完后再释放掉。KILL DDL 操作很可能会导致该锁没有释放，此时再执行 DDL 会有以下报错:
Lock conflict , maybe last DDL is still running

# 取消及锁释放
release dblock;