--- title: 诊断 TEMP TABLE 抽取的性能问题-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 诊断 TEMP TABLE 抽取的性能问题相关的常见问题和使用技巧，帮助您快速解决 诊断 TEMP TABLE 抽取的性能问题的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 诊断 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`。 示例。 1. 创建表。 ```shell obclient> CREATE TABLE t1(c1 int, c2 int); Query OK, 0 rows affected (0.104 sec) ``` ```shell obclient> CREATE TABLE t2(c1 int, c2 int); Query OK, 0 rows affected (0.090 sec) ``` 2. 创建视图 v。 ```shell 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) ``` 3. 使用 EXPLAIN 查看 SQL 执行计划。 ```shell obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = 1), (SELECT aggr FROM v WHERE c1=2) FROM t1; ``` 输出结果如下： ```shell +----------------------------------------------------------------------------------------------------------------------+ | 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) ``` 4. 使用 EXPLAIN 查看改写后的 SQL 执行计划。 ```shell 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; ``` 输出结果如下： ```shell +----------------------------------------------------------------------------------------------------------------------+ | 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 的全表扫描与聚合，性能低。 1. 在表 t2 上创建索引。 ```shell obclient> CREATE INDEX idx ON t2(c1); Query OK, 0 rows affected (0.310 sec) ``` 2. 使用 EXPLAIN 查看 SQL 执行计划。 ```shell obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1), (SELECT aggr FROM v WHERE c1=t1.c2) FROM t1; ``` 输出结果如下： ```shell +----------------------------------------------------------------------------------------------------------+ | 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 抽取带来的性能问题会有如下的表现。 查询的每个部分分别执行很快，合在一起后执行慢。 示例。 obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1), (SELECT aggr FROM v WHERE c1=t1.c2) FROM t1; 输出结果如下： ``` 3. 将 SQL 的两个子查询拆分到两个查询中。 a. 执行 SQL 拆分查询一。 ```shell obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c1) FROM t1; ``` 输出结果如下： ```shell +---------------------------------------------------------------------------------------------+ | 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 拆分查询二。 ```shell obclient> EXPLAIN SELECT (SELECT aggr FROM v WHERE c1 = t1.c2) FROM t1; ``` 输出结果如下： ```shell +---------------------------------------------------------------------------------------------+ | 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 抽取带来的问题。 ![7u78i-ku730-hky798-yu683](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240226explain00001.png) ## 解决方案 当 SQL 的执行计划中带有 `TEMP TABLE TRANSFORMATION` 算子，可以尝试以下二种方法提升 SQL 执行性能。 - 在 SQL 中添加 Hint `opt_param('xsolapi_generate_with_clause', 'false')` 禁止 TEMP TABLE 抽取。 ```shell 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; ``` 输出结果如下： ```shell +----------------------------------------------------------------------------------------------------------+ | 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。 1. 修改集群级别的配置项 `_xsolapi_generate_with_clause` 为 false，命令如下。 ```shell obclient> ALTER SYSTEM SET _xsolapi_generate_with_clause = false; Query OK, 0 rows affected (0.017 sec) ``` 输出结果如下： ``` ## 适用版本 OceanBase 数据库 V3.2.x 及后续版本。 Previous [SQL Audit 内存管理与淘汰机制](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000217865) Next [如何设置及查看 SQL 审计及执行计划](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000217861) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线