跳到主要内容

深入浅出数据库宽表

在真实的业务场景中，一个简单的数据分析常常需要 7-8 张表的复杂 JOIN。这不仅导致查询性能低下，更让业务人员等待数小时才能获取一份基础报表。

本文将深入探讨宽表技术如何破解这一困局，并结合 CloudCanal 最新推出的可视化宽表构建功能，零代码构建宽表，轻松实现跨表数据的实时整合。

背景：数据分析困境​

在结构化数据系统中，随着业务模型的复杂化，数据表之间的关联不断增多。以电商系统为例，订单、商品、用户等表结构天然具有关联性：

  • 订单数据：商品 ID (关联 商品数据)、数量、总价、优惠信息、买家 ID (关联 用户数据)等
  • 商品数据：名称、颜色、质地、库存、商家 ID (关联 用户数据)等
  • 用户数据：账号、密码、昵称、手机、邮箱等

关系型数据库通过建立关系范式实现了存储效率最大化（冗余信息少），并优化了事务型操作的性能。但一旦进入数据分析阶段，这种表结构会给查询带来极大挑战。

在进行批量聚合、筛选等复杂分析时，如“计算过去 1 个月商品销售额 Top 10”，往往需要多表 JOIN 操作。而随着关联表数量增加，可能的执行计划（搜索空间）也呈几何级增长：

关联表数量排列种类
22
36
424
5120
6720
75040
840320
9362880
103628800

对于一个 ERP 或 CRM 系统而言，5 张表以上的关联是常态。面对如此庞大的执行计划可能性，如何高效地找到最优解 成为数据库分析查询的核心难题。

面对这一挑战，行业演进出了两种技术路线：查询优化 和 预计算。而 打宽表 是预计算的核心能力之一。

技术路线：查询优化 vs 预计算​

查询优化​

面对这么多种可能性，解决方式之一就是通过减少可能性加快搜索，这就是数据库优化器术语——剪枝。其中衍生出两种技术：基于规则的优化器（RBO: Rule-Based Optimizer） 和 基于代价的优化器（CBO: Cost-Based Optimizer）。

  • 基于规则的优化器（RBO）：RBO不考虑数据实际分布，而是依据预设的静态规则对 SQL 执行计划进行调整。市面上的数据库产品，都有一些通用的优化规则，如谓词下推等。不同数据库产品会基于其业务属性和架构特点，还拥有独特的优化规则，如 SAP Hana 承载 SAP ERP 业务，针对全内存和大量 Join 的场景，其优化器规则便与其他数据库存在显著差异。

  • 基于代价的优化器（CBO）：相较于 RBO，CBO 通过评估不同执行计划的 I/O、CPU 等资源消耗，选择代价最低的计划。这种优化方式会通过数据的具体分布与查询 SQL 的特点动态来调整，即使两条一模一样的 SQL，因为设置的参数值不一样，可能最后的执行计划大相径庭。CBO 一般具备一个精密复杂的统计信息子系统，包括各个表的数据量、基于主键或者关联维度的数据分布直方图等信息。

现代数据库内核一般会联合使用 RBO 和 CBO 对 SQL 进行优化。

预计算​

预计算认为 关联表之间的数据关系是确定的，所以在数据生成时就进行聚合与关联，形成一张“宽”的表结构，从而避免后续每次查询都重新进行关联。数据发生变化时，仅需增量更新对应的宽表数据。它的早期实现可以追溯到关系型数据库的物化视图。

从计算量消耗角度而言，预计算大幅优于即时查询优化。然而，其局限性亦不容忽视：

  • JOIN 语义实现受限：预计算对于非 Left Join 语义的实现逻辑复杂且代价巨大，通常难以在流式处理中高效支持。
  • 大量数据更新影响稳定性：在 1 对 N 的数据关系中，若“1”端数据发生变化，可能导致宽表数据大规模更新，对服务稳定性构成挑战。
  • 功能与性能权衡：预计算通常无法像即时查询那样支持各种聚合、过滤条件及函数，部分是功能实现难度，部分是性能代价考量。

最佳实践​

在真实场景中，业界普遍倾向于 “双管齐下” 的策略：预计算生成中间数据，而即时查询则在此基础上完成最终的聚合与过滤，实现功能与性能的平衡。

  • 预计算：当前最流行的方案即 流计算，近些年出现流式数据库也尝试更好得解决这类问题，传统关系型数据库或者数据仓库实现的物化视图也是一种很好的解决方案。

  • 即时查询：随着 列式、行列混合 数据结构普及，AVX 512 等新指令集落地，甚至 FPGA、GPU 等专用或高性能计算硬件尝试，以及知识网格、分布式计算 等软件手段应用，实时分析数据库在数据筛选和聚合性能上取得了显著提升。

两者结合使用，为在线数据分析场景带来了巨大的助益。

CloudCanal 的宽表历程​

CloudCanal 在宽表构建方面的演进，体现了从“高代码门槛”到“零代码普惠”的转变。早期版本中通过自定义代码支持用户在数据迁移同步过程中，自行查询关联表数据并构建宽表，再回写到目标端。尽管功能可行，但用户需要写代码，难度太高，因而未形成大规模应用。

为此，CloudCanal 在最新版本（4.7.2.0）中实现了 可视化宽表构建。用户仅需在界面中选择需要打宽表的表，并选择关联的列，即可构建出一个既支持历史数据迁移，又支持实时更新的宽表任务。目前，该功能已支持 MySQL -> MySQL / StarRocks / Doris / SelectDB，未来将持续开放更多链路。

CloudCanal 可视化构建宽表​

宽表定义​

CloudCanal 宽表构建中的核心角色包括：

  • 驱动表：仅可选择一张，作为宽表的基准数据源。
  • 关联表：可选择多张，提供驱动表需要补充的字段信息。

CloudCanal 默认实现 Left Join 语义，确保返回驱动表所有数据，即使关联表中无匹配数据。

目前，CloudCanal 支持 2 种关联拓扑结构：

  • 线形 关系：A.b_id = B.id and B.c_id = C.id and ...，其中所有表均不可重复选择，且不支持形成环状关联。
  • 星形 关系：A.b_id = B.id and A.c_id = C.id and ...，不支持形成环状关联。

其中 A 表定义为驱动表，B、C 表定义为关联表。

数据变更处理策略​

对端为关系型数据库(如 MySQL)​

  • 驱动表 INSERT：自动补全关联表字段。
  • 驱动表 UPDATE/DELETE：不做关联表字段关联。
  • 关联表 INSERT：如有下一层关联表，则做关联表字段关联，INSERT 操作转换为 UPDATE。
  • 关联表 UPDATE：如有下一层关联表，不做关联表字段关联。
  • 关联表 DELETE：如有下一层关联表，则做关联表字段关联，DELETE 操作转换为全字段为 NULL 的 UPDATE 操作。

对端为整行覆盖类数据库(如 StarRocks/Doris)​

  • 驱动表 INSERT/UPDATE/DELETE：自动补全关联表字段。

  • 关联表 INSERT/UPDATE/DELETE：自动忽略。

    信息

    若需实现到覆盖类数据库 关联表变更，可以分阶段完成。

    首先创建 源库 > 关系型数据库宽表 迁移同步任务，再创建 关系型数据库宽表 -> 覆盖类数据库 迁移同步任务。

操作示例​

  1. 进入 CloudCanal 控制台，点击 同步任务 > 创建任务 。

  2. 进入 选择表 步骤，选择需要打宽表的表，对端均选择宽表进行映射。

  3. 进入 数据处理 步骤。

    1. 页面左侧选择 驱动表，并点击 操作 > 宽表，设定宽表定义。

      • 关联列 选择和设置，支持多个。

      • 关联表 其他列 选择，以及宽表映射字段填写，此操作主要防止多张来源表字段名在目标宽表中重复。

        信息

        1. 关联表如果级联关联其他表，务必选择其对应关联列。如 A.b_id = B.id and B.c_id = C.id, 则选择 B 的其他列时，必须选择 c_id。

        2. 在复杂关联关系中，不同驱动表或关联表存在相同列名，故在选择其他列时，映射到不同列以规避冲突。

    2. 点击 确定 完成设置。

    3. 页面左侧选择 关联表，确认列和宽表列映射正确。

  4. 继续完成任务创建流程，即可启动宽表同步任务。

总结​

宽表作为一种核心的数据分析预计算策略，有效缓解了关系范式在OLAP场景下的性能瓶颈。CloudCanal 的可视化宽表构建能力，显著降低了技术门槛，为构建具备实时性和灵活性的数据分析平台提供了重要工具，让数据架构师和 DBA 能够更高效地构建实时数据分析层。