云栈社区»论坛 › 站务中心「 Forum Service 」 › 崖山数据库标量子查询CACHE功能实测与性能优化指南 ...

发回帖发新帖

3694 积分	0 好友	508 主题

发消息

崖山数据库标量子查询CACHE功能实测与性能优化指南

发表于 2 小时前 | 查看: 4| 回复: 0

标量子查询的CACHE功能：为何重要？

在SQL优化中，标量子查询是一个常见的场景。它的性能表现，尤其是查询引擎是否支持CACHE功能，对整体执行效率有着至关重要的影响。

Oracle数据库在标量子查询算法上有一个显著的优势：当主表返回N行数据时，子表并非简单地被扫描N次。其实际扫描次数等于主表关联列的不同值（DISTINCT VALUE）的数量，即 COUNT(DISTINCT NVL(主表JOIN列, 0))。这意味着Oracle的标量子查询内建了CACHE机制，可以有效避免对相同输入值的重复计算。

目前，许多国产数据库的标量子查询尚未实现这一优化功能，执行算法仍停留在“主表返回多少行，子表就被扫描多少次”的阶段。

一个真实的性能优化案例

这个CACHE特性在极致的SQL性能调优场景中非常有用。笔者曾利用该特性优化过数百条SQL语句。就在今年夏天，在为某头部券商进行SQL优化时，遇到一条典型SQL：执行耗时3.7秒，逻辑读高达70万次。该语句经过GROUP BY后最终返回4294行数据，而业务方的要求是将执行时间优化到1秒以内。

SQL的大致结构如下（出于证券行业的保密要求，无法展示完整SQL及执行计划，敬请谅解）：

select ...,b.一个列,d.一个列
        from (select c_pa_code,
                     n_hldcst,
                     n_hldcst_locl,
                     c_ml_attr,
                     c_cury_code,
                     n_valrate,
                     c_sec_code,
                     c_port_code,
                     d_hold
                from gzdb.vh_repurchase
               where c_pa_code in
                     ('MRFSJRZC', 'MCHGJRZC', 'YSLX_ZQ', 'YFLX_ZQ', 'JZZB')) a
        left join gzdb.vb_port_baseinfo b  ---b.c_port_code是唯一的
          on a.c_port_code = b.c_port_code
        left join gzdb.vb_security c
          on c.c_sec_code = a.c_sec_code
         and c.c_sec_var in ('CJ', 'HG')
        left join (select c_port_code,
                          d_biz,
                          n_hldmkv_locl,
                          row_number() over(partition by c_port_code, d_biz order by c_update_time desc) as r
                     from gzdb.vn_port_index
                    where c_idx_code = 'ZCJZ'
                      and c_port_class = 'NA') d
          on a.c_port_code = d.c_port_code
         and a.d_hold = d.d_biz
         and d.r = 1
        where a.d_hold = to_date(:endt, 'yyyy-mm-dd')) a
 group by ....

性能瓶颈分析：

经过过滤后，驱动表 a 返回31793行数据。
a 通过连接列将谓词条件“下推”到子查询 d，导致 d 被扫描了 31793次。
a 与 d 进行嵌套循环连接（NL）后累计耗时已达3.5秒，而整个SQL总耗时才3.7秒，此处是绝对的热点。

优化思路：
关键在于 a.c_port_code 和 a.d_hold 这两列存在大量重复值。同时，b 表（c_port_code唯一）和 d 子查询（按关联列分区后只取第1行）都只返回一列。这为利用标量子查询的CACHE功能创造了绝佳条件。

优化手段就是将 b 和 d （特别是d）改写到标量子查询中，利用其CACHE机制，将子表的扫描次数从“行数”级别降低到“不同值个数”级别，从而大幅减少 b 和 d 的扫描次数。

改写后的SQL在此不再赘述。优化效果立竿见影：逻辑读从70万次降至20万次，SQL执行时间成功降低到1秒以内，满足了业务要求。

需要注意：

如果 a.c_port_code 和 a.d_hold 没有大量重复值，改写为标量子查询将无法带来性能提升。
如果数据库的标量子查询本身没有CACHE功能，那么为了实现类似的缓存效果，就只能将 d 和 b 封装到自定义函数中，并为函数加上 RESULT CACHE 功能，这无疑复杂了许多。

基础演示：Oracle标量子查询CACHE功能

为了帮助SQL优化基础稍弱的同学理解，这里用一个简单的例子演示Oracle 11.2.0.4中标量子查询的CACHE功能。

首先，准备测试数据：

SQL> select *
  2  from (select owner, object_id
  3          from test02
  4         where owner = 'SYS'
  5         order by object_id)
  6  where rownum <= 3;

OWNER                           OBJECT_ID
------------------------------ ----------
SYS                                     2
SYS                                     3
SYS                                     4

SQL> select *
  2  from (select owner, object_id
  3          from test02
  4         where owner = 'PUBLIC'
  5         order by object_id)
  6  where rownum <= 3;

OWNER                           OBJECT_ID
------------------------------ ----------
PUBLIC                               117
PUBLIC                               280
PUBLIC                               367

开启执行统计，执行包含标量子查询的SQL：

SQL> alter session set statistics_level=all;
Session altered.

SQL> select object_id, (select count(*) from test01 where owner = t2.owner) cnt
  2  from test02 t2
  3  where object_id in (2, 3, 4, 117, 280, 367);

 OBJECT_ID        CNT
---------- ----------
         2    19348992
         3    19348992
         4    19348992
       117    17408512
       280    17408512
       367    17408512

查看执行计划，关键看 Starts 列：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  dcm994g6n6tq0, child number 3
-------------------------------------
select object_id, (select count(*) from test01 where owner = t2.owner)
cnt   from test02 t2  where object_id in (2, 3, 4, 117, 280, 367)
Plan hash value: 384367355
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |      6 |00:00:00.01 |      15 |
|   1 |  SORT AGGREGATE              |               |      2 |      1 |      2 |00:00:05.60 |    1271K|
|*  2 |   TABLE ACCESS FULL          | TEST01        |      2 |    421K|     36M|00:00:04.52 |    1271K|
|   3 |  INLIST ITERATOR             |               |      1 |        |      6 |00:00:00.01 |      15 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TEST02        |      6 |      6 |      6 |00:00:00.01 |      15 |
|*  5 |    INDEX RANGE SCAN          | IDX_TEST02_ID |      6 |      6 |      6 |00:00:00.01 |       9 |
--------------------------------------------------------------------------------------------------------

结果分析：

主表 TEST02 过滤后返回6行数据，其 OWNER 列只有两个不同值：SYS 和 PUBLIC。
在执行计划中，Id=2 的操作（对应标量子查询中的 TEST01 表全扫描）的 Starts 次数为 2。
这证明了Oracle的标量子查询具有CACHE功能。如果没有该功能，TEST01 表应该被扫描6次（与主表行数一致）。
（注：显然，TEST01 表的 OWNER 列应该创建索引，这里只是为了演示而故意不创建。）

重点验证：崖山数据库标量子查询是否有CACHE？

现在，我们将环境切换到崖山数据库 23.5.1 版本进行验证。

执行相同的SQL并查看执行计划：

SQL> alter session set statistics_level=all;
Succeed.

SQL> set autot trace
SQL> select object_id, (select count(*) from test01 where owner = t2.owner) cnt
  2  from test02 t2
  3  where object_id in (2,3,4,117,280,367);
Execution Plan
----------------------------------------------------------------
SQL hash value: 2923445856
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | E - Rows | A - Rows | Cost(%CPU)  | A -Time | Loops    | Memory   | Disk     | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |        6|             |       90|         7|         0|         0|                                |
|  1 |  SUBQUERY                      | QUERY[1]             |            |          |        2|             |  7889087|         4|         0|         0|                                |
|  2 |   AGGREGATE                    |                      |            |        1 |        2|  1151005( 0)|  7889079|         4|         0|         0|                                |
|* 3 |    TABLE ACCESS FULL           | TEST01               | SCOTT      |      446 |  36757504|  1151005( 0)|  6349946|  36757506|         0|         0|                                |
|  4 |   TABLE ACCESS BY INDEX ROWID  | TEST02               | SCOTT      |        6 |          |       1( 0)|          |          |          |          |                                |
|* 5 |    INDEX RANGE SCAN            | IDX_TEST02_ID        | SCOTT      |        6 |        6|       1( 0)|       88|         7|         0|         0|                                |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+

初步观察与疑问：

时间格式：崖山的 A-Time 显示似乎没有转换成类似Oracle HH:MI:SS.FF 的易读格式，希望后续能改进。
循环次数：Id=3 的 Loops 显示为 36757506，这个数字令人困惑。如果CACHE功能生效，此处应为2（不同值个数）；如果未生效，应为6（主表行数）。这个数值显然不准确，希望崖山能改进此处指标，最好能与Oracle的 Starts 含义保持一致。

既然 Loops 指标不准，我们如何验证崖山标量子查询的CACHE功能呢？可以通过对比SQL执行时间来间接判断。

通过自定义函数进行验证

1. 创建自定义函数

（注：正是因为Loops指标不准，才采用此方法验证，并无他意。）

CREATE OR REPLACE FUNCTION f_get_cnt_by_owner(p_owner varchar) RETURN int AS
  v_cnt int;
BEGIN
  select count(*) into v_cnt from test01 where owner = p_owner;
  RETURN v_cnt;
END;
/

2. 恢复统计级别并测试

alter session set statistics_level=TYPICAL;

扫描函数一次，耗时约2.7秒：

SQL> select object_id,f_get_cnt_by_owner(owner) from test02 where object_id in(2);
  OBJECT_ID F_GET_CNT_BY_OWNER(OWNER)
----------- -------------------------
          2                 19348992
1 row fetched.
Elapsed: 00:00:02.707

扫描函数六次，预期耗时约 2.7秒 * 6 = 16.2秒，但实际仅 5.7秒：

SQL> select object_id,f_get_cnt_by_owner(owner) from test02 where object_id in(2,3,4,117,280,367);
  OBJECT_ID F_GET_CNT_BY_OWNER(OWNER)
----------- -------------------------
          2                 19348992
          3                 19348992
          4                 19348992
        117                 17408512
        280                 17408512
        367                 17408512
6 rows fetched.
Elapsed: 00:00:05.731

实际耗时接近 2.7秒 * 2 = 5.4秒，这表明函数很可能只被调用了2次。查看执行计划：

SQL> set autot trace
SQL> select object_id,f_get_cnt_by_owner(owner) from test02 where object_id in(2,3,4,117,280,367);
Execution Plan
----------------------------------------------------------------
SQL hash value: 1080230727
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | E - Rows | A - Rows | Cost(%CPU)  | A -Time | Loops    | Memory   | Disk     | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |          |             |          |          |          |          |                                |
|  1 |  SUBQUERY                      | QUERY[1]             |            |          |          |             |          |          |          |          |                                |
|  2 |   AGGREGATE                    |                      |            |        1 |          |  1151005( 0)|          |          |          |          |                                |
|* 3 |    TABLE ACCESS FULL           | TEST01               | SCOTT      |      446 |          |  1151005( 0)|          |          |          |          |                                |
|  4 |   TABLE ACCESS BY INDEX ROWID  | TEST02               | SCOTT      |        6 |          |       1( 0)|          |          |          |          |                                |
|* 5 |    INDEX RANGE SCAN            | IDX_TEST02_ID        | SCOTT      |        6 |          |       1( 0)|          |          |          |          |                                |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+

关键发现：执行计划显示为 SUBQUERY，且出现了 Subquery NDV info。这说明崖山数据库的优化器自动将SELECT列表中的“纯SQL”自定义函数调用改写成了标量子查询！同时也间接证明了崖山标量子查询具有CACHE功能。

在Oracle中，要实现类似的效果，需要手动将函数调用改写为 SELECT (SELECT 自定义函数 FROM DUAL) FROM … 的形式，或者为函数启用 RESULT CACHE 功能。

验证非纯SQL函数的场景

现在，我们对函数稍作修改，使其不再是“纯SQL”：

CREATE OR REPLACE FUNCTION f_get_cnt_by_owner(p_owner varchar) RETURN int AS
  v_cnt int;
BEGIN
  IF 1=1 THEN  ---加了这里
    select count(*) into v_cnt from test01 where owner = p_owner;
  END IF;      ---加了这里
  RETURN v_cnt;
END;
/

再次执行查询，耗时约 15.2秒：

SQL> select object_id,f_get_cnt_by_owner(owner) from test02 where object_id in(2,3,4,117,280,367);
  OBJECT_ID F_GET_CNT_BY_OWNER(OWNER)
----------- -------------------------
          2                 19348992
          3                 19348992
          4                 19348992
        117                 17408512
        280                 17408512
        367                 17408512
6 rows fetched.
Elapsed: 00:00:15.228

2.7秒 * 6 ≈ 16.2秒，与15.2秒接近，说明此时函数被调用了6次，优化器没有进行自动改写。

此时，我们手动采用Oracle的优化写法，将其改写为标量子查询形式：

SQL> select object_id,(select f_get_cnt_by_owner(owner) from dual) from test02 where object_id in(2,3,4,117,280,367);
  OBJECT_ID (SELECTF_GET_CNT_BY_OWNER(OWNER)FROMDUAL)
----------- -----------------------------------------
          2                                 19348992
          3                                 19348992
          4                                 19348992
        117                                 17408512
        280                                 17408512
        367                                 17408512
6 rows fetched.
Elapsed: 00:00:09.910

改写效果：SQL耗时从 15.2秒降低到 9.9秒，性能得到提升。但这里存在一个疑问：按CACHE原理（2个不同值），预期耗时应在 2.7秒 * 2 = 5.4秒 左右，实际9.9秒偏高，希望崖山后续能对此场景进行进一步优化。

回看Oracle的对比测试

切换回Oracle 11.2.0.4环境，创建“纯SQL”函数进行对比。

创建函数并还原统计级别：


CREATE OR REPLACE FUNCTION f_get_cnt_by_owner(p_owner varchar) RETURN int AS
v_cnt int;
BEGIN
select count(*) into v_cnt from test01 where owner = p_owner;
RETURN v_cnt;
END;
/

alter session set statistics_level=TYPICAL;

2.  直接调用函数6次，耗时 **7.75秒**：
```sql
SQL> select object_id,f_get_cnt_by_owner(owner) from test02 where object_id in(2,3,4,117,280,367);
 OBJECT_ID F_GET_CNT_BY_OWNER(OWNER)
---------- -------------------------
         2                19348992
         3                19348992
         4                19348992
       117                17408512
       280                17408512
       367                17408512
6 rows selected.
Elapsed: 00:00:07.75

改写为标量子查询形式，耗时 降至2.54秒：

SQL> select object_id,(select f_get_cnt_by_owner(owner) from dual) from test02 where object_id in(2,3,4,117,280,367);
OBJECT_ID (SELECTF_GET_CNT_BY_OWNER(OWNER)FROMDUAL)
---------- -----------------------------------------
     2                          19348992
     3                          19348992
     4                          19348992
   117                          17408512
   280                          17408512
   367                          17408512
6 rows selected.
Elapsed: 00:00:02.54

耗时从7.75秒降到2.54秒，清晰地证明了改写为标量子查询后，函数只被调用了2次，Oracle的CACHE功能生效。

总结与展望

核心结论：崖山数据库的标量子查询具备CACHE功能，这一点值得肯定，因为许多国产数据库尚未实现这一重要的优化特性。
智能优化：崖山在SELECT列表中调用自定义函数时，如果该函数是“纯SQL”构成，优化器会自动将其改写为标量子查询，从而利用CACHE功能。若函数包含非SQL逻辑（如流程控制），则需像在Oracle中一样，手动改写为 SELECT (SELECT func() FROM DUAL) ... 的形式。
改进建议：希望崖山能早日完善执行计划中 A-Time 的时间格式显示，并修正 Loops 指标的统计逻辑，使其能像Oracle的 Starts 列一样，准确反映标量子查询等操作的实际执行次数，这对SQL优化工作至关重要。
场景优化：崖山在处理 SELECT (SELECT 非纯SQL自定义函数 FROM DUAL) FROM ... 这种写法时，其执行效率仍有优化空间（对比理论耗时）。
延伸话题：在Oracle中，FILTER 操作与标量子查询的算法原理相似，也具备CACHE功能。但崖山优化器在处理 FILTER 时的行为与Oracle差异较大，这需要另起一文专门探讨。

希望这篇基于实战的测试与分析，能帮助你更深入地理解标量子查询的CACHE原理，并在实际的数据库性能优化工作中加以应用。如果你有更多的优化案例或想法，欢迎来云栈社区交流分享。

上一篇：支付逻辑漏洞实战挖掘：九大类型技巧与安全测试思路解析
下一篇：OceanBase 的架构核心是什么？是分布式对等还是主从复制？

崖山数据库, Oracle, 数据库优化, SQL性能, 缓存