联 系 我 们
售前咨询
售后咨询
微信关注:星环科技服务号
更多联系方式 >
9.6 Skew Join
更新时间:11/27/2024, 12:06:28 PM

在数据密集型行业中,数据倾斜是一个常见且复杂的问题,其通常表现为某些键值对的分布极不平衡,例如表中的某个键值对应的记录量远超过其他键时,会导致连接(Join)操作中个别节点的处理时间过长,从而成为整个查询过程的瓶颈,影响整个系统的处理效率和性能。

面对数据倾斜问题,传统的方法主要依赖于 MapJoin 或手动改写 SQL 查询语句,然而,这两种方法都有其局限性:

  • MapJoin:要求至少有一侧的表足够小,能够将其加载进内存,过大的表可能引发内存溢出。

  • 改写 SQL:需要数据库管理员或数据工程师深入理解业务场景,不仅耗时且容易出错,常常需要反复调试。

面对这一挑战,Inceptor 推出了 Skew Join 功能,旨在自动化处理数据倾斜问题,优化 Join 操作的性能。本章主要向您介绍 Skew Join 的实现原理及使用限制,以及 Inceptor 处理数据倾斜的方式:

  • 当出现 NULL 倾斜时,只需直接设置 Quark 参数打开 NULL 倾斜优化开关:

    quark.join.null.optimize = true
    复制
  • 非 NULL 倾斜,可通过在查询语句中添加 Hint 信息进行优化:

    • 在 SQL 语句中手动添加数据表的 Hint 倾斜信息

    • DBA Service 平台提供识别数据倾斜并进行优化分析的功能,帮助部分不熟悉数据库运维操作的人员降低操作门槛。当

      • 生成 Hint 信息:在 DBA Service 诊断界面中生成 SKEWJOIN hint,客户可以在对应 SQL 中直接加入该 hint 重新执行。

      • 针对 Holodesk 表出现非 NULL 倾斜时,DBA Service 还提供优化倾斜功能收集分析表的倾斜信息,下次在 Quark 中再次对该表 Join 查询时将会自动进行走 Skew Join 优化,无需手动添加 Hint 操作。

  • 非 NULL 倾斜,也可通过设置 Quark 参数,自动识别表中的倾斜键值属性,无需在查询 SQL 语句中收到指定。

原理及限制

工作原理

skew join principle
图 77. Skew Join 工作原理

Skew Join 针对倾斜的键值采取特殊处理,将倾斜键值的数据分散到多个节点上进行处理,从而避免单个节点上的负载过重。它的基本步骤包括:

表 11. 工作原理
操作 说明

自动处理倾斜优化

为表设置倾斜键值并开启自动识别后,无需每次都在 SQL 语句中加入 Hint 信息,可自动识别 SQL 并在执行时采用倾斜优化策略。

智能数据分发

对倾斜键值进行均匀分发,使其分布到多个计算节点上,非倾斜的键则正常执行 Shuffle 操作。

优化连接操作

并行处理倾斜键值的数据,并且优化执行计划来减轻单个节点的负载。

合并结果

将处理后的结果合并,以完成整个连接操作。

适用场景

  • 大表与大表的连接操作:当两个较大的表进行连接操作时,如果连接键的分布不均匀,会造成数据倾斜。

  • 小表与大表的连接操作:即使是小表与大表的连接,如果大表中某些键值的数据量过大,也可能产生倾斜。

  • 键值分布极不均匀:当某些键值对应的记录数量远远超过其他键值时,常规的连接操作会导致数据倾斜。

使用限制

  • 仅支持连接中的单张表存在数据倾斜的情况,例如:

    • Inner Join 左右任意一侧倾斜

    • Left Join 的左侧表倾斜

    • Right Join 的右侧表倾斜(实质与 Left Join 左表倾斜等价)

  • 连接条件需为等值连接,即 SQL 语句中 Join On 条件中存在形如 keyA = keyB 或类似条件,此时 keyA、keyB 就是 join key。

  • 对于子查询下的数据倾斜场景,需通过手动指定 Hint 方法来执行 Skew Join 优化策略,暂不支持自动识别。

相关参数说明

为帮助您更清晰准确的了解对 Skewjoin 数据倾斜场景的优化,我们向您提供以下优化参数说明:

参数 默认值 含义

quark.skewjoin.hint.enable

false

是否开启支持 Skew Join hint 功能

quark.join.skew.row.report.threshold

10000000(一千万)

倾斜上报阈值,当查询 Join Key 超过该值时就会向 DBA Service 上报异常通知

quark.skewjoin.stats.optimize.enable

true

是否开启 DBA Service 自动识别分析信息进行 Skew Join 优化倾斜功能,默认开启

quark.skewjoin.stats.row.count.threshold

10000000(一千万)

DBA Service 通过分析统计信息自动走 Skew Join 优化的阈值

quark.holodesk.analyze.skew.num.limit

10000000(一千万)

倾斜 Join Key 值行数,判断 Join key 的数据行数超过设定的阈值时, 将会触发 SkewJoin 优化。

quark.holodesk.analyze.skew.total.limit

20

最多处理倾斜值个数:设置系统在优化过程中能够处理的倾斜 Join Key 的数量。

指定倾斜 Hint

开启 Hint 功能
  • 设置 Quark 参数,支持使用 Hint 指定倾斜键值功能

    quark.skewjoin.hint.enable = true -- 默认 false
    复制
确定 Hint 信息
方法一:手动确定添加 Hint 信息

在执行查询 SQL 时,针对产生数据倾斜的表字段,添加 Hint 手动指定倾斜信息:

SELECT /*+SKEWJOIN(<table1_alias> (<column_name>) [(<skew_value>)],
                   <table2_alias> (<column_name>) [(<skew_value>)]...) */
...;
复制
  • <table1_alias>:存在数据倾斜的表的别名

  • <column_name>:表中存在数据倾斜的列名,必须包含所有用作 Join 条件的列,即 SQL 中 Join 语句的 ON 条件中匹配的列名,多个列名间用英文逗号(,)分隔

  • <skew_value>:倾斜的键值,倾斜值内部不同列的值用英文逗号(,)分隔,多个倾斜值整体用方括号包裹,例如:[('name1', 100), ('name2', 200)]。

  • 此外,每个倾斜值必须与 column_name 中倾斜列的顺序对应,且不允许出现 null。[#skewjoin-dba-hint]

方法二:DBA Service 生成 Hint 信息

针对部分不熟悉数据库运维操作的人员,指定 Hint 信息存在操作难度门槛,为此 DBA Service 运维平台将收集到的统计信息直接生成 SKEWJOIN hint 反馈给客户

  1. 提交 SQL 查询后,若 Join Key 的行数超过以下参数设定的阈值时,Quark 将像 DBA Service 发送提示信息,记录表倾斜:

    set quark.join.skew.row.report.threshold=10; -- 默认为 10,000,000
    复制
  2. 登录 DBA Service 平台,左侧菜单栏选择 查询(Query)> 查询(Queries)。在对应 SQL 语句右侧诊断按钮上会出现异常提示,点击诊断按钮,进入 SQL 问题诊断界面。

    skewjoin dba queries
  3. 在诊断界面中,点击【生成 hint 信息】,填写倾斜行数阈值(默认 10000000)和最大倾斜值数量(默认20)

    skewjoin dba hint info
  4. 系统在经过对表的统计后,提示分析成功并生成一个 Hint 信息(如下图中的 /*+SKEWJOIN(t1s (id) [(1),(2)])*/)。

    skewjoin dba generate hint
  5. 将生成的 Hint 添加到 SQL 语句的 SELECT 后,再次执行就能实现 SkewJoin 优化,可参考生效验证方式

  6. 针对 Holodesk 表中出现的数据倾斜问题,DBA Service 还提供优化倾斜功能。通过收集记录每一列的统计信息并记录进入内部表,在下次查询该表时可以自动接入 Skew Join。

使用示例
例 20. 人工确认 Hint 信息指定倾斜键值
  1. 通过 Beeline 登录 Inceptor 数据库

  2. 设置 Quark 参数,支持 Hint 语法手动指定倾斜信息:

    set quark.skewjoin.hint.enable = true;
    set quark.join.null.optimize = true;
    复制
    本方法设置后的参数仅在当前 Session 级别生效 ,如仅需全局生效,可登录 Manager 平台进行设置,参考示例:自动识别数据倾斜优化
  3. 确认表中的键值是否存在数据倾斜。

    store_returns 表为例,查看 sr_reason_sk 列是否存在值倾斜,您可以替换下述 SQL 中的表名和列名为真实环境中的名称。

    WITH partition_percentages AS (
    SELECT sr_reason_sk, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS percentage
    FROM store_returns
    GROUP BY sr_reason_sk
    )
    SELECT sr_reason_sk, percentage, 'top' AS distribution_type
    FROM partition_percentages
    ORDER BY percentage DESC
    LIMIT 5
    UNION ALL
    SELECT sr_reason_sk, percentage, 'bottom' AS distribution_type
    FROM partition_percentages
    ORDER BY percentage ASC
    LIMIT 5;
    复制

    返回结果如下,可以看到 sr_reason_sk 列中 15 的值占比约 68.42%,键值倾斜非常严重:

    +---------------+-----------------------+--------------------+
    | sr_reason_sk  | percentage            | distribution_type  |
    +---------------+-----------------------+--------------------+
    | 15            | 68.432423177848438    | top                |
    | 12            | 12.27944843355184288  | top                |
    | 19            | 3.51045386920265545   | top                |
    | NULL          | 3.5016363906611824    | top                |
    | 11            | 1.75668204429367795   | top                |
    | 16            | 1.75047265018885171   | bottom             |
    | 20            | 1.75147976668236379   | bottom             |
    | 18            | 1.75281680064788846   | bottom             |
    | 13            | 1.75372667830754421   | bottom             |
    | 8             | 1.75445249674597188   | bottom             |
    +---------------+-----------------------+--------------------+
    复制
  4. 设置 Hint 信息: Inceptor 在执行 SQL 时,会对 store_returns 表(别名为 sr)中,所有 sr_reason_sk 值为 15 的数据做均匀分发,避免数据倾斜。

    SELECT /*+SKEWJOIN(sr (sr_reason_sk) [(15)])*/
        sr.sr_item_sk,
        r.r_reason_desc,
        sr.sr_returned_date_sk,
        SUM(sr.sr_return_quantity) AS item_returns,
        AVG(sr.sr_return_amt) AS avg_return_amt,
        COUNT(DISTINCT sr.sr_ticket_number) AS unique_tickets,
        STDDEV(sr.sr_return_amt) AS std_dev_return_amt,
        MIN(sr.sr_return_amt) AS min_return_amt,
        MAX(sr.sr_return_amt) AS max_return_amt
    FROM store_returns sr
    JOIN reason r ON sr.sr_reason_sk = r.r_reason_sk
    WHERE sr.sr_reason_sk >= 15 and sr.sr_reason_sk < 16
    GROUP BY sr.sr_item_sk, r.r_reason_desc, sr.sr_returned_date_sk
    ORDER BY item_returns DESC, avg_return_amt DESC
    LIMIT 10;
    复制
    若 HINT 信息(本例中的 /*+SKEWJOIN(sr (sr_reason_sk) [(15)])*/ )的确定对您存在困难,您可以使用 DBA Service 平台辅助生成 HINT,并手动添加至 SELECT 后面进行执行。
  5. 确认 SQL 执行时正确地采用了 Skew Join 优化策略。请参考验证生效方式

例 21. Holodesk 表的倾斜优化
  1. 登录 DBA Service 平台,左侧菜单栏选择 查询(Query)> 查询(Queries)。

  2. 提交 SQL 查询后出现 Holodesk 表数据倾斜问题时,对应右侧诊断按钮上会出现异常提示,点击诊断按钮,进入 SQL 问题诊断界面。

    skewjoin dba queries holo
  3. 在诊断界面中,点击【优化倾斜】,分析统计原表单字段的倾斜信息,然后点击【确认】。

    skewjoin dba sql diagnose
    • 倾斜 Join Key 值行数:判断 Join key 的数据行数超过设定的阈值时, DBA Service 将会自动进行 SkewJoin 优化。

    • 最多处理倾斜值个数:设置系统在优化过程中能够处理的倾斜 Join Key 的数量。

  4. 分析成功后,DBA Service 将生成并自动执行倾斜信息查询语句(可点击【收集倾斜信息查询语句】查看具体分析优化 SQL 命令),状态变更为【已处理】,

    skewjoin diagnose done
    skewjoin diagnose auto sql
  5. 设置以下 Quark 参数,支持 DBA Service 分析上一步中的统计信息并执行优化:

    • quark.skewjoin.stats.optimize.enable:开启使用 DBA Service 自动优化倾斜功能,默认开启为 true。

    • quark.skewjoin.stats.row.count.threshold:设置通过 DBA Service 分析自动走 Skew Join 优化的阈值,默认为 10000000

  6. 再次执行该倾斜表 JOIN 时,将会根据以上统计信息自动选择进入 SkewJoin 优化,可参考生效验证方式

    skewjoin diagnose sql again
    skewjoin sql again ok

参数识别优化

功能描述

当数据库表中存在数据倾斜时,为表设置倾斜键值并开启自动识别参数后,无需每次都在 SQL 语句中加入 Hint 信息,可自动识别 SQL 并在执行时采用倾斜优化策略。

  • 设置 Quark 参数,开启自动识别倾斜键值并进行优化的功能:

    quark.skew.join.auto.optimize = true -- 默认为 false
    复制
  • 使用 ALTER TABLE SKEWED BY 设置指定表的倾斜键值;使用 ALTER TABLE NOT SKEWED 删除指定表中设置的倾斜键值。

    设置表倾斜键值属性信息:
    ALTER TABLE <table_name>
    SKEWED BY (<column_name1>, [column_name2], ...)
    ON (
        (<skew_value_1>, <skew_value_2>),
        (<skew_value_1>, <skew_value_2>),
        ...
    );
    复制
    删除表的 Skew Join 设置
    ALTER TABLE <table_name> NOT SKEWED;
    复制
    查看表的 Skew Join 设置
    DESC FORMATTED <table_name>;
    复制
    • 在 DESC 查询输出的结果中,通过 Skewed ColumnsSkewed Values 的值来查看。

    • <table_name>:存在数据倾斜的表名称。

    • <column_name1>,<column_name2> … :列名,多个列之间用英文逗号(,)分隔。

    • <skew_value_1>,<skew_value_2> … :倾斜的值,多个值之间用英文逗号(,)分隔,如果指定了多组倾斜值,则每组值需和指定的列顺序对应。

使用示例
  1. 登录至 Transwarp Manager 平台。

  2. 修改 Quark 服务配置。

    本方法设置后的参数生效级别为全局,如仅需 Session 级生效,您可以在 SQL 命令行中,通过 SET 命令来完成配置,例如 SET quark.skew.join.auto.optimize = ture

    1. 在页面左侧,单击仪表盘 > 集群,找到并单击 Quark 服务。

    2. 单击配置页签,然后单击页面右侧的添加自定义参数

    3. 在弹出的对话框中,根据下述说明完成参数配置,然后单击确定

      turn on skewjoin
      • 配置项:填写为 quark.skew.join.auto.optimize,即开启 Skew Join 优化的自动识别。

      • :填写为 true

      • 配置文件:选择为 hive-site.xml

    4. 参考上一步骤,依次添加下述参数,将其值设置为 true,配置文件选择为 hive-site.xml

      • quark.skewjoin.hint.enable:开启使用 Hint 语法手动指定倾斜键值信息。

      • quark.join.null.optimize:开启 Null 值倾斜优化。

    5. 单击页面右上角的配置服务,在弹出的对话框中单击确定

    6. 等待配置应用完成后,单击重启

      重启该服务可能导致业务短暂不可用,请在业务低峰期或运维窗口期操作。
  3. 通过 Beeline 登录 Inceptor 数据库

  4. 确认表中的键值是否存在数据倾斜。参考手动指定 Hint 示例中步骤 3 一致。

  5. 设置 store_returns 表设置了两个倾斜列(sr_reason_sk 和 sr_item_sk),它们对应的倾斜值分别是 10, 154370, 6601

    ALTER TABLE store_returns
    SKEWED BY (sr_reason_sk, sr_item_sk)
    ON ((10, 15), (4370, 6601));
    复制
  6. 查看 store_returns 表的 Skew Join 设置信息:

    DESC FORMATTED store_returns;
    复制
  7. 确认 SQL 执行时正确地采用了 Skew Join 优化策略。请参考验证生效方式

验证生效方式

  • 方式一:通过 EXPLAIN 结果进行判断

    • 在 Join 查询语句的最前面添加 EXPLAIN,查看 SQL 语句的处理方法步骤。

      EXPLAIN <select-join-statement>;
      复制
    • 当结果中出现 handleSkewJoin: true 的字样,即表示 SKEWJOIN 生效。

  • 方式二:通过 DBA Service 中的 DAG 图判断

    在 DBA Service 平台的查询页面中,找到执行完毕的查询,查看其 DAG 图,示例如下:

    skewjoin dag process
    图 78. DAG 图示例