由于数据分布和查询复杂度等因素,可能出现查询性能不符合预期的情况,检查查询的执行计划是重要的问题排查方式之一。

常见计划问题

  • Join Method以及Inner/Outer表

    根据Join Method选择Inner和Outer表, 一般情况下AnalyticDB for MySQL自动选择Join的左右表,您也可以自行检查左右表的选择是否合理。

    Hash Join中,右表创建Hash,左表去右表中查找符合条件的数据,一般右表要尽量小于左表,以减少创建Hash表的开销以及Hash表的大小。您可以通过检查Join表过滤后的大小来查看对应的左右表选择是否合理。但由于还有多表Join的中间结果,以及Join Type等因素影响,Join的左右表的选择也不能单纯依赖表过滤后的大小来选择。

  • Join Order

    Join Order的优化是优化器的核心挑战之一,也是经典的NP-hard问题。AnalyticDB for MySQL优化器对于不同的负载提供两种Join Reordering策略。Left Deep Tree (LDT)一般适用于较简单查询场景,Bushy Tree (BT)适用较复杂的负载查询。Bushy Tree一般会将过滤效果更好的表放在前面,对于复杂查询能够生成更优的Join Order。

    对于复杂查询,人为确认最佳Join Order非常困难。Join表数量较大时,需要资深的专家进行调优。建议将过滤效果更好的表放在join sequence之前,可以更早更快的过滤掉不需要的数据。

  • 分布式Aggregation

    AnalyticDB for MySQL提供分布式聚合计算能力,可以根据计算数据量分步做聚合计算。一般情况下,AnalyticDB for MySQL的优化器可以选择最佳聚合计算计划,但在数据倾斜比较严重等场景下,优化器对于聚合数据分布估算的误差会比较大,从而造成聚合计算性能问题。例如,一般AnalyticDB for MySQL会选择两阶段聚合计算,在各个计算节点本地做一次部分聚合 (Partial Aggregation),减少聚合计算数据量,然后再根据聚合列Reshuffle做一次final aggregation。一般情况下,部分聚合可以显著减少聚合计算的数据量,但如果遇到数据倾斜严重,或者部分聚合列比较Unique从而不能减少聚合计算数量的情况下,部分聚合反而会带来额外的性能开销而非收益。

  • 其它问题

    本文只列举了几类常见的查询计划问题,例如全表扫描时出现无过滤条件的大表、可以下推的过滤条件没有下沉到存储等复杂计划和性能问题等,需要AnalyticDB for MySQL专家服务小组来协助定位排查。

改进执行计划

  • 收集统计信息

    AnalyticDB for MySQL的查询优化器根据统计信息估算不同计划的开销,因此,准确的统计信息对于生成查询计划至关重要。如果有查询计划问题,最佳处理办法是更新统计信息,确保查询优化器可以拿到最新、准确的统计信息。

    如果未执行过Analyze收集统计信息,建议执行analyze table table_name;收集某张表的统计信息。 如果一个数据库中有多张表,可以使用use db_name;analyze table all;一次收集所有表的统计信息。收集完统计信息后,优化器可以对绝大多数查询生成最优计划。但在数据极端分布等场景,优化器也可能无法生成最优计划,需要一些特殊的调优手段,其中以调整Join Order最为常见。

  • 调整Join Order

    通常AnalyticDB for MySQL可以选择最佳的Join Order,由于数据分布特性以及查询自身的复杂度等因素,在某些场景下可能存在无法选择最优Join Order,查询性能较低,您可以通过在Hint中引入reorder_joins参数设置是否手动调整Join Order。

    • /*+reorder_joins=false*/;,打开手动调整Join Order开关,然后通过修改查询中各个表出现的顺序来控制Join Order。
    • /*+reorder_joins=true*/;,关闭手动调整Join Order开关,系统会自动选择join order,绝大多数场景下可以获得最佳Join Order。
    例如上述示例中的nation、region、customer表,AnalyticDB for MySQL给出的Join Order是region > nation > customer。如果根据实际查询性能得出更好的Join Order:customer > nation > region,可以如下所示在查询前使用Hint改变查询中表的顺序。
    /*+ reorder_joins=false */
           EXPLAIN SELECT count(*)
           FROM    customer, nation, region
           WHERE c_nationkey = n_nationkey
           AND n_regionkey = r_regionkey
           AND r_name = 'ASIA';                       
           | Plan Summary  |
           +---------------+
           1- Output[ Query plan ]
           2  -> Aggregate (FINAL)
           3    -> LocalExchange[SINGLE]
           4      -> Exchange[GATHER]
           5        -> Aggregate (PARTIAL)
           6          -> InnerJoin[Hash Join]
           7            - Project
           8              -> InnerJoin[Hash Join]
           9                - ScanProject {table: customer}
           10                  -> TableScan {table: customer}
           11                -> LocalExchange[HASH]
           12                  -> Exchange[REPLICATE]
           13                    - ScanProject {table: nation}
           14                      -> TableScan {table: nation}
           15            -> LocalExchange[HASH]
           16              -> Exchange[REPLICATE]
           17                - ScanProject {table: region}
           18                  -> TableScan {table: region}                    
  • Plan Hint For Join Order
    除了上述通过reorder_joins参数修改查询中表出现的顺序来调整Join Order外,AnalyticDB for MySQL还提供Plan Hint For Join Order帮助您调整Join Order。通过在查询头部嵌入leading_join_order来调整AnalyticDB for MySQL的Join Order。
    说明 为了了解同一个表在查询中被多次引用的情况,使用Join Order Plan Hint时需要为查询中的表指定别名。

    对于上述示例,在查询前加入Plan Hint For Join Order也可以得到相同的Join Order。

     /*+ leading_join_order=((c n) r) */
          EXPLAIN SELECT count(*)
          FROM    nation n , region r, customer c
          WHERE c_nationkey = n_nationkey
          AND n_regionkey = r_regionkey
          AND r_name = 'ASIA';                    

查询计划调优是一个非常广泛的领域,本文简要讨论AnalyticDB for MySQL中的查询计划调优基础方法,后续将不断更新更多的调优最佳实践。