SQL优化:基于代价的优化方法的介绍与使用（下）

前言

在 SQL优化:基于代价的优化方法的介绍与使用（上）一文中我们介绍了CBO优化概念和它的基本使用方法，包括实现统计信息收集的两种手段：ANALYZE 语句； 脚本工具preanalyze.sh。并通过Hive CBO 和 Inceptor CBO 在Inceptor引擎中带来的性能提升幅度对比，以体现Inceptor CBO的良好优化能力。

本文将衔接上文内容，对CBO继续进行介绍，分析实际生产中利用CBO优化执行计划过程中会出现的异常问题，然后提供相应的解决方案，并通过一个具体的案例来讲解使用CBO优化的具体步骤。

CBO常见问题和解决办法

在利用CBO进行优化的过程中，用户可能会遇到如下异常现象。

• 统计信息收集异常

统计信息收集失败会直接导致CBO 功能失效，所以如果用户发现CBO开关打开后没有效果，请通过如下步骤排查导致异常的原因。

首先应注意，收集信息之前，一定要提前关闭优化开关inceptor.optimizer.on，否则会报错。关闭此开关的命令如下：

SET  inceptor.optimizer.on = FALSE;

接着请检查原本的执行计划是否已为最优，然后查看ANALYZE 运行状况，查看信息是否成功收集。

统计信息收集是否成功的检查方法是，在ANALYZE或者preanalyze运行成功后执行如下语句：

DESC FORMATTED <table_name>;

如果该语句输出结果中的numRows 值等于实际的表条目数，则说明信息收集成功；否则如为-1，则执行失败。

• 优化执行计划生成异常

正常情况下，确认统计信息生成无误后，打开CBO 就可以使自动优化执行计划。但是有一些特殊情况，CBO 无法作用于它们。为了确认CBO 是否会对当前语句起作用，建议用户在执行语句之前，先通过EXPLAIN 查看在启用CBO 前后执行计划是否会发生变化。

EXPLAIN  <SQL_statement>;

例如对于TPC-DS 500G 中的query3，该语句涉及store_sales（约14 亿条）、date_dim（约7.3 万条）、item（约2.8 万）三表之间的INNER JOIN。在CBO开关关闭的情况下，通过执行EXPLAIN 知道优化之前执行计划的JOIN 顺序是：store_sales JOIN date_dim JOIN item。因为item在query3中有较高的过滤率，这种计划的效率明显不够好。

当打开CBO 开关后，EXPLAIN 的结果表现优化后的执行顺序是 ：store_sales JOIN item JOIN date_dim，执行效率高于前者。

如果打开CBO 开关后，如果执行计划发生了变化，则表明CBO 生效。反之，如果EXPLAIN 的结果没有任何改变，则说明CBO 对当前语句失效。对于后者现象，可能的成因是CBO 目前暂不支持下列特定场景：

1. SQL 中含有HINTS。

   解决方案是去掉HINTS，交由CBO进行整体分析和优化（在将来的版本Inceptor会逐步支持常用的HINTS）。

2. SQL 中含有复杂WITH-AS 从句和VIEW。

   因为WITH-AS 或者VIEW 在没有物化的情况下无法得到统计信息，所以CBO 不能进行代价计算。解决方案是将WITH-AS 和VIEW 改成物化的表，然后对这些表进行ANALYZE 或者preanalyze.sh 分析。在得到相应统计信息之后，再使用CBO 优化。

3. 不支持非等值JOIN。目前没有解决方案。

说明

对于以上三种特殊场景，若没有较好的替代方案解决，CBO 会自动退化到正常执行逻辑，对SQL 执行和结果不会造成影响，也就是说使用CBO 没有负面影响。

案例分析

假设某证券交易公司有一位员工Alice，为了了解2015年股票交易情况，目前需完成两项任务：一是统计A级别账户购买的不同类型的股票数目；二是统计购买s类型股票的不同级别的账户数目。用到的表和字段分别如下所示：

数据库：trans_platform
表名	字段名	字段含义
transactions  （交易记录）	stock_id	交易成交的股票号
acc_num	发生交易的账户号
trans_id	交易号
accounts （账户记录）	id	账户号
acc_level	账户级别
stocks （股票记录）	id	股票号
stock_type	股票类型

其中transactions记录了2015年一年的全部交易记录，总记录数约有1 亿条；accounts 记录了2015年交易涉及的所有账户信息，约20万条；stocks 提供了2015 年交易涉及的所有股票信息，约3万条。这三张表都位于数据库trans_platform 中。

为了实现以上任务，Alice 编写了下面两条SQL 执行语句。

【语句一】通过表transactions、accounts、stocks 查询最受A级别账户欢迎的股票类型。

SELECT  stock_type, COUNT(*) num_cnt

FROM  transactions a JOIN stocks b

ON a.stock_id  = b.id

JOIN accounts  c

ON a.acc_num  = c.id

WHERE c.acc_level = 'A'

GROUP BY stock_type;

【语句二】通过表transactions、accounts、stocks 查询统计购买s类型股票的不同级别的账户数目。

SELECT acc_level, COUNT(*) num_cnt

FROM  transactions a JOIN stocks b

ON a.stock_id  = b.id

JOIN accounts  c

ON a.acc_num  = c.id

WHERE b.stock_type = 's'

GROUP BY acc_level;

语句一和语句二分别写于文件getStockTypeCnt.sql和getAccLevelCnt.sql，并同时存放在目录/home/Alice/Documents/sqls/getTransInfo下，该目录仅有这两份文件。由于被查询表的数据量较大，语句涉及JOIN操作且有一定复杂性，为保障高效执行上述语句，Alice 决定采用CBO对执行计划进行优化。

Alice决定首先通过Preanalyze工具收集统计信息。由于两个语句都不包含Preanalyze 不可处理的情况，所以可直接执行preanalyze.sh。

接着Alice 进入preanalyze.sh 所在目录/usr/lib/hive/bin，执行如下两条命令要求Inceptor 自动收集优化语句一、二时用到的相关表和列的信息。

./preanalyze  -d /home/Alice/Documents/sqls/getTransInfo

--database  trans_platform -v hive2 -p alice/SE@TDH

由于SQL 文件中没有指明数据库，她在执行命令中通过参数--database 指定语句访问的数据库trans_platform。而且因为数据库类型为Inceptor Server 2，认证方式是Kerberos，所以还须指明版本为hive2并提供principal。

命令运行成功后，Alice 再次进入trans_platform数据库，通过DESC FORMATTED 结果确认统计信息确实收集成功。

为确保CBO 生效，Alice 对比了在开启CBO 前后这两条语句的执行计划，发现语句一中过滤条件acc_level = 'A'是作用于accounts上，过滤掉80%的记录，因而CBO将执行计划从原来的

transactions JOIN stocks JOIN accounts

优化为

transactions JOIN accounts JOIN stocks

而语句二中过滤条件stock_type='s'是作用于stocks上，过滤率达75%，因而其本身的执行计划已为最优：

transactions JOIN stocks JOIN accounts

所以对于语句二，她在CBO开关前后获得了同样的执行计划。

最终在CBO 开启的状态下执行了这两个语句，得到结果。

通过上述案例，可以将CBO优化的使用过程总结为这样的顺序：

用户在执行语句前应该先了解表的行量、字段数量等基本信息。在访问数据量很大的表或执行JOIN相关的复杂语句时，要想到采用CBO 优化，使过滤率大的表先被JOIN。首先收集统计信息，然后检查CBO关开时执行计划的变化，最后再优化执行语句。

总结

本文分析了CBO优化过程中会导致异常出现的情况，并提出了相应的解决办法。随后通过一个案例，讲解了执行CBO优化的具体过程和注意事项。

在使用CBO时，为保证执行计划的优化能顺利进行，为读者提供以下四条建议：

1. 建议利用Preanalyze工具进行收集信息。使用该工具时，注意Inceptor Server版本和认证问题，注意数据库的指定。

2. 面临Preanalyze 和CBO 禁止的情况时，选择合适的方法，对SQL 进行改写，或使用其他优化方式。

3. 对于复杂或者访问数据量较大的语句，不要急于直接运行，首先确认是否需要CBO优化，再观察统计是否成功，然后看启动CBO执行计划是否有变化，有了多重保障之后再运行。

4. 针对CBO功能失效的问题，我们可以从统计信息收集异常和优化执行计划生成异常两个方面去查找原因，寻求解决办法，确保CBO顺利运行。

Inceptor CBO 是对多表JOIN 案例的执行计划优化的重要工具，我们希望可以通过这两篇文章，加深用户对CBO概念和原理的理解，增强对使用CBO的具体情境的把握，熟练并充分利用它以提升数据分析效率。

往期原创文章

---

大数据开放实验室由星环信息科技（上海）有限公司运营，专门致力于大数据技术的研究和传播。若转载请在文章开头明显注明“文章来源于微信订阅号——大数据开放实验室”，并保留作者和账号介绍。