首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
诊断 TEMP TABLE 抽取的性能问题
更新时间：2024-04-11 07:06
分享
适用版本：V3.2.x、V4.0.x、V4.1.x、V4.2.x内容类型：TechNote
TEMP TABLE 抽取是 Oceanbase 优化器的众多改写策略之一，它的目的是识别出 SQL 中那些相似的部分进行物化，以减少执行次序。本文介绍诊断 TEMP TABLE 抽取的性能问题。
问题背景
通过以下示例来进行说明。
首先创建一个对 t2 做聚合的视图 v，SQL 中包含两个从视图 v 中值的子查询。此时优化器会发现，查询中包含了两次相同的聚合操作，可以通过添加一个公共表达式（CTE）来减少重复的计算。在执行计划中，表现为 0 号算子 TEMP TABLE TRANSFORMATION 和 1 号算子 TEMP TABLE INSERT。
示例。
创建表。
obclient> CREATE TABLE t1(c1 int, c2 int); Query OK, 0 rows affected (0.104 sec)obclient> CREATE TABLE t2(c1 int, c2 int); Query OK, 0 rows affected (0.090 sec)创建视图 v。
obclient> CREATE OR REPLACE VIEW v AS SELECT c1, MAX(c2) AS aggr FROM t2 GROUP BY c1; Query OK, 0 rows affected (0.101 sec)使用 EXPLAIN 查看 SQL 执行计划。
obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = 1), (SELECT aggr FROM v WHERE c1=2) FROM t1;输出结果如下：
+----------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------+ | ================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------- | | |0 |TEMP TABLE TRANSFORMATION| |1 |9 | | | |1 |├─TEMP TABLE INSERT |TEMP1 |1 |5 | | | |2 |│ └─HASH GROUP BY | |1 |5 | | | |3 |│ └─TABLE FULL SCAN |t2 |1 |4 | | | |4 |└─SUBPLAN FILTER | |1 |4 | | | |5 | ├─TABLE FULL SCAN |t1 |1 |4 | | | |6 | ├─TEMP TABLE ACCESS |VIEW1(TEMP1)|1 |1 | | | |7 | └─TEMP TABLE ACCESS |VIEW2(TEMP1)|1 |1 | | | ================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([:0], [:1]), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | 2 - output([T_FUN_MAX(t2.c2)], [t2.c1]), filter(nil), rowset=16 | | group([t2.c1]), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c1], [t2.c2]), filter([t2.c1 = 1 OR t2.c1 = 2]), rowset=16 | | access([t2.c1], [t2.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 4 - output([:0], [:1]), filter(nil), rowset=16 | | exec_params_(nil), onetime_exprs_([subquery(1)(:0)], [subquery(2)(:1)]), init_plan_idxs_(nil), use_batch=false | | 5 - output(nil), filter(nil), rowset=16 | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 6 - output([VIEW1.T_FUN_MAX(t2.c2)]), filter([VIEW1.t2.c1 = 1]), rowset=16 | | access([VIEW1.T_FUN_MAX(t2.c2)], [VIEW1.t2.c1]) | | 7 - output([VIEW2.T_FUN_MAX(t2.c2)]), filter([VIEW2.t2.c1 = 2]), rowset=16 | | access([VIEW2.T_FUN_MAX(t2.c2)], [VIEW2.t2.c1]) | +----------------------------------------------------------------------------------------------------------------------+ 32 rows in set (0.016 sec)使用 EXPLAIN 查看改写后的 SQL 执行计划。
obclient> EXPLAIN WITH TEMP1 AS (SELECT * FROM v) SELECT (SELECT aggr FROM TEMP1 WHERE c1 = 1), (SELECT aggr FROM TEMP1 WHERE c1=2) FROM t1;输出结果如下：
+----------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |TEMP TABLE TRANSFORMATION| |1 |9 | | | |1 |├─TEMP TABLE INSERT |TEMP1|1 |5 | | | |2 |│ └─HASH GROUP BY | |1 |5 | | | |3 |│ └─TABLE FULL SCAN |t2 |1 |4 | | | |4 |└─SUBPLAN FILTER | |1 |4 | | | |5 | ├─TABLE FULL SCAN |t1 |1 |4 | | | |6 | ├─TEMP TABLE ACCESS |TEMP1|1 |1 | | | |7 | └─TEMP TABLE ACCESS |TEMP1|1 |1 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([:0], [:1]), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | 2 - output([t2.c1], [T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group([t2.c1]), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c1], [t2.c2]), filter([t2.c1 = 1 OR t2.c1 = 2]), rowset=16 | | access([t2.c1], [t2.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 4 - output([:0], [:1]), filter(nil), rowset=16 | | exec_params_(nil), onetime_exprs_([subquery(1)(:0)], [subquery(2)(:1)]), init_plan_idxs_(nil), use_batch=false | | 5 - output(nil), filter(nil), rowset=16 | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 6 - output([TEMP1.aggr]), filter([TEMP1.c1 = 1]), rowset=16 | | access([TEMP1.c1], [TEMP1.aggr]) | | 7 - output([TEMP1.aggr]), filter([TEMP1.c1 = 2]), rowset=16 | | access([TEMP1.c1], [TEMP1.aggr]) | +----------------------------------------------------------------------------------------------------------------------+ 32 rows in set (0.009 sec)SQL 中多了一个 with 公共表达式（CTE），执行计划仍和步骤 3 一样。
性能问题
虽然优化器会对 CTE 抽取进行一些评估，但当前还是存在一些不足，主要集中在命中索引的情况。我们简单修改下上面的例子，首先创建一个 t2 上的索引，并将 SQL 中子查询里的过滤条件从一个常量值修改为相关表 t1 的参数。可以看到，现在优化器仍然生成了一个 TEMP TABLE，并没有利用上我们刚刚建立的索引。这会导致 t2 的全表扫描与聚合，性能低。
在表 t2 上创建索引。
obclient> CREATE INDEX idx ON t2(c1); Query OK, 0 rows affected (0.310 sec)使用 EXPLAIN 查看 SQL 执行计划。
obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1), (SELECT aggr FROM v WHERE c1=t1.c2) FROM t1;输出结果如下：
+----------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------+ | ================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------- | | |0 |TEMP TABLE TRANSFORMATION| |1 |9 | | | |1 |├─TEMP TABLE INSERT |TEMP1 |1 |5 | | | |2 |│ └─HASH GROUP BY | |1 |5 | | | |3 |│ └─TABLE FULL SCAN |t2 |1 |4 | | | |4 |└─SUBPLAN FILTER | |1 |4 | | | |5 | ├─TABLE FULL SCAN |t1 |1 |4 | | | |6 | ├─TEMP TABLE ACCESS |VIEW1(TEMP1)|1 |1 | | | |7 | └─TEMP TABLE ACCESS |VIEW2(TEMP1)|1 |1 | | | ================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([subquery(1)], [subquery(2)]), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | 2 - output([T_FUN_MAX(t2.c2)], [t2.c1]), filter(nil), rowset=16 | | group([t2.c1]), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c1], [t2.c2]), filter(nil), rowset=16 | | access([t2.c1], [t2.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 4 - output([subquery(1)], [subquery(2)]), filter(nil), rowset=16 | | exec_params_([t1.c1(:0)], [t1.c2(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 5 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 6 - output([VIEW1.T_FUN_MAX(t2.c2)]), filter([VIEW1.t2.c1 = :0]), rowset=16 | | access([VIEW1.T_FUN_MAX(t2.c2)], [VIEW1.t2.c1]) | | 7 - output([VIEW2.T_FUN_MAX(t2.c2)]), filter([VIEW2.t2.c1 = :1]), rowset=16 | | access([VIEW2.T_FUN_MAX(t2.c2)], [VIEW2.t2.c1]) | +----------------------------------------------------------------------------------------------------------+ 32 rows in set (0.010 sec)
性能问题的表现
一般来说，TEMP TABLE 抽取带来的性能问题会有如下的表现。
查询的每个部分分别执行很快，合在一起后执行慢。
示例。
在表 t2 上创建索引。
obclient> CREATE INDEX idx ON t2(c1); Query OK, 0 rows affected (0.310 sec)使用 EXPLAIN 查看 SQL 执行计划。
obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1), (SELECT aggr FROM v WHERE c1=t1.c2) FROM t1;
输出结果如下：
+----------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------+ | ================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------- | | |0 |TEMP TABLE TRANSFORMATION| |1 |9 | | | |1 |├─TEMP TABLE INSERT |TEMP1 |1 |5 | | | |2 |│ └─HASH GROUP BY | |1 |5 | | | |3 |│ └─TABLE FULL SCAN |t2 |1 |4 | | | |4 |└─SUBPLAN FILTER | |1 |4 | | | |5 | ├─TABLE FULL SCAN |t1 |1 |4 | | | |6 | ├─TEMP TABLE ACCESS |VIEW1(TEMP1)|1 |1 | | | |7 | └─TEMP TABLE ACCESS |VIEW2(TEMP1)|1 |1 | | | ================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([subquery(1)], [subquery(2)]), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | 2 - output([T_FUN_MAX(t2.c2)], [t2.c1]), filter(nil), rowset=16 | | group([t2.c1]), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c1], [t2.c2]), filter(nil), rowset=16 | | access([t2.c1], [t2.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 4 - output([subquery(1)], [subquery(2)]), filter(nil), rowset=16 | | exec_params_([t1.c1(:0)], [t1.c2(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 5 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 6 - output([VIEW1.T_FUN_MAX(t2.c2)]), filter([VIEW1.t2.c1 = :0]), rowset=16 | | access([VIEW1.T_FUN_MAX(t2.c2)], [VIEW1.t2.c1]) | | 7 - output([VIEW2.T_FUN_MAX(t2.c2)]), filter([VIEW2.t2.c1 = :1]), rowset=16 | | access([VIEW2.T_FUN_MAX(t2.c2)], [VIEW2.t2.c1]) | +----------------------------------------------------------------------------------------------------------+ 32 rows in set (0.010 sec)将 SQL 的两个子查询拆分到两个查询中。
a. 执行 SQL 拆分查询一。
obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1) FROM t1;输出结果如下：
+---------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------+ | =================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------- | | |0 |SUBPLAN FILTER | |1 |27 | | | |1 |├─TABLE FULL SCAN |t1 |1 |4 | | | |2 |└─MERGE GROUP BY | |1 |23 | | | |3 | └─DISTRIBUTED TABLE RANGE SCAN|t2(idx)|1 |23 | | | =================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([subquery(1)]), filter(nil), rowset=16 | | exec_params_([t1.c1(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 1 - output([t1.c1]), filter(nil), rowset=16 | | access([t1.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c2]), filter(nil), rowset=16 | | access([t2.__pk_increment], [t2.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([t2.c1 = :0]) | +---------------------------------------------------------------------------------------------+ 23 rows in set (0.008 sec)b. 执行 SQL 拆分查询二。
obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c2) FROM t1;输出结果如下：
+---------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------+ | =================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------- | | |0 |SUBPLAN FILTER | |1 |27 | | | |1 |├─TABLE FULL SCAN |t1 |1 |4 | | | |2 |└─MERGE GROUP BY | |1 |23 | | | |3 | └─DISTRIBUTED TABLE RANGE SCAN|t2(idx)|1 |23 | | | =================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([subquery(1)]), filter(nil), rowset=16 | | exec_params_([t1.c2(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 1 - output([t1.c2]), filter(nil), rowset=16 | | access([t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c2]), filter(nil), rowset=16 | | access([t2.__pk_increment], [t2.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([t2.c1 = :0]) | +---------------------------------------------------------------------------------------------+ 23 rows in set (0.008 sec)SQL 的两个子查询拆开到两个查询中后，都是可以使用索引的。
综上通过 EXPLAIN 查看执行计划， 0 号算子为 TEMP TABLE TRANSFORMATION，基本可以确定是 TEMP TABLE 抽取带来的问题。 
解决方案
当 SQL 的执行计划中带有 TEMP TABLE TRANSFORMATION 算子，可以尝试以下二种方法提升 SQL 执行性能。
在 SQL 中添加 Hint
opt_param('xsolapi_generate_with_clause', 'false')禁止 TEMP TABLE 抽取。obclient> EXPLAIN SELECT /*+opt_param('xsolapi_generate_with_clause', 'false')*/ (SELECT aggr FROM v WHERE c1 = t1.c1), (SELECT aggr FROM v WHERE c1=t1.c2) FROM t1;输出结果如下：
+----------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------+ | =================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------- | | |0 |SUBPLAN FILTER | |1 |50 | | | |1 |├─TABLE FULL SCAN |t1 |1 |4 | | | |2 |├─MERGE GROUP BY | |1 |23 | | | |3 |│ └─DISTRIBUTED TABLE RANGE SCAN|t2(idx)|1 |23 | | | |4 |└─MERGE GROUP BY | |1 |23 | | | |5 | └─DISTRIBUTED TABLE RANGE SCAN|t2(idx)|1 |23 | | | =================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([subquery(1)], [subquery(2)]), filter(nil), rowset=16 | | exec_params_([t1.c1(:0)], [t1.c2(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c2]), filter(nil), rowset=16 | | access([t2.__pk_increment], [t2.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([t2.c1 = :0]) | | 4 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(t2.c2)]) | | 5 - output([t2.c2]), filter(nil), rowset=16 | | access([t2.__pk_increment], [t2.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([t2.c1 = :1]) | +----------------------------------------------------------------------------------------------------------+ 32 rows in set (0.009 sec)修改集群级别的配置项
_xsolapi_generate_with_clause为 false，所有 SQL 都不会抽取 TEMP TABLE。修改集群级别的配置项
_xsolapi_generate_with_clause为 false，命令如下。obclient> ALTER SYSTEM SET _xsolapi_generate_with_clause = false; Query OK, 0 rows affected (0.017 sec)使用 EXPLAIN 查看 SQL 执行计划。
obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1), (SELECT aggr FROM v WHERE c1=t1.c2) FROM t1;输出结果如下：
+----------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------+ | =================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------- | | |0 |SUBPLAN FILTER | |1 |50 | | | |1 |├─TABLE FULL SCAN |t1 |1 |4 | | | |2 |├─MERGE GROUP BY | |1 |23 | | | |3 |│ └─DISTRIBUTED TABLE RANGE SCAN|t2(idx)|1 |23 | | | |4 |└─MERGE GROUP BY | |1 |23 | | | |5 | └─DISTRIBUTED TABLE RANGE SCAN|t2(idx)|1 |23 | | | =================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([subquery(1)], [subquery(2)]), filter(nil), rowset=16 | | exec_params_([t1.c1(:0)], [t1.c2(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(t2.c2)]) | | 3 - output([t2.c2]), filter(nil), rowset=16 | | access([t2.__pk_increment], [t2.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([t2.c1 = :0]) | | 4 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_MAX(t2.c2)]) | | 5 - output([t2.c2]), filter(nil), rowset=16 | | access([t2.__pk_increment], [t2.c2]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c1], [t2.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true, | | range_cond([t2.c1 = :1]) | +----------------------------------------------------------------------------------------------------------+ 32 rows in set (0.009 sec)
适用版本
OceanBase 数据库 V3.2.x 及后续版本。
