GBase 8a
性能调优
文章

南大通用数据库-Gbase-8a-SQL优化之视图展开

阳光九叶草LZL
发表于2024-12-31 14:00:42111次浏览0个评论

​

 一、环境信息

名称值
CPUIntel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz
操作系统CentOS Linux release 7.9.2009 (Core)
内存3G
逻辑核数2
Gbase8a版本8.6.2-R43

 

二、参数介绍

参数名描述
_t_gcluster_fold_tree_optimize_new默认值为 0，表示关闭新 from 子查询展开功能。
取值为 1 时，表示打开新 from 子查询展开功能。

 

三、实验

1、登录集群

[gbase@czg2 ~]$ gccli -c

GBase client 8.6.2-R43.34.27468a27. Copyright (c) 2004-2024, GBase.  All Rights Reserved.

gbase>

为什么要加-c参数呢，忘记了的小伙伴可以看一下之前的博客《南大通用数据库-Gbase-8a-学习-32-gccli客户端》

2、测试表结构

gbase> SHOW CREATE TABLE CZG.TESTTAB_COPY;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                            |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TESTTAB_COPY | CREATE TABLE "testtab_copy" (
  "a" int(11) DEFAULT NULL,
  "b" double DEFAULT NULL,
  "c" varchar(100) DEFAULT NULL,
  "d" text,
  "e" blob,
  "f" longblob,
  "g" date DEFAULT NULL,
  "h" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

3、测试数据

gbase> SELECT DISTINCT A FROM CZG.TESTTAB_COPY;
+------+
| A    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.49)

gbase> SELECT COUNT(*) FROM CZG.TESTTAB_COPY WHERE A = 2;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT COUNT(*) FROM CZG.TESTTAB_COPY WHERE A = 1;
+----------+
| COUNT(*) |
+----------+
|  1310720 |
+----------+
1 row in set (Elapsed: 00:00:00.01)

测试数据A只有两种不同的值，且A等于2的条数远远小于等于1的。

4、创建视图

gbase> CREATE VIEW CZG.V1 AS SELECT * FROM CZG.TESTTAB_COPY WHERE A = 2;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> CREATE VIEW CZG.V2 AS SELECT * FROM CZG.TESTTAB_COPY;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

5、测试SQL

gbase> EXPLAIN SELECT V1.* FROM CZG.V1 INNER JOIN CZG.V2 ON V1.A = V2.A;
+----+-------------+-------------+-------------------+------------+-----------------+
| ID | MOTION      | OPERATION   | TABLE             | CONDITION  | NO STAT Tab/Col |
+----+-------------+-------------+-------------------+------------+-----------------+
| 01 | [RESULT]    |  INNER JOIN |                   | (a = a)    |                 |
|    |             |   Step      | <00>              |            |                 |
|    |             |   SubQuery1 | v1                |            |                 |
|    |             |    SCAN     | testtab_copy[DIS] | (a{S} = 2) |                 |
| 00 | [BROADCAST] |  SubQuery2  | v2                |            | testtab_copy    |
|    |             |   Table     | testtab_copy[DIS] |            |                 |
+----+-------------+-------------+-------------------+------------+-----------------+
6 rows in set (Elapsed: 00:00:00.01)

我们通过执行计划可以看出，两个SQL子查询分别进行物化，v2视图变成复制表，与v1物化后的结果进行关联，这其中的问题就是v1视图中a=2这个过滤条件没有传递到视图v2，因为他们的关联条件是a=a，这是可以传递的，这时我们可以通过修改v2视图定义来解决这个问题，但生产环境可以这么轻易的修改视图定义吗，通常情况下是不允许的。

6、加HINT

gbase> EXPLAIN SELECT /*+_t_gcluster_fold_tree_optimize_new(1)*/ V1.* FROM CZG.V1 INNER JOIN CZG.V2 ON V1.A = V2.A;
+----+-------------+-------------+--------------------------------+------------+-----------------+
| ID | MOTION      | OPERATION   | TABLE                          | CONDITION  | NO STAT Tab/Col |
+----+-------------+-------------+--------------------------------+------------+-----------------+
| 01 | [RESULT]    |  INNER JOIN |                                | (a = a)    |                 |
|    |             |   Step      | <00>                           |            |                 |
|    |             |   SCAN      | czg.testtab_copy_v2_53897[DIS] | (a{S} = 2) |                 |
| 00 | [BROADCAST] |  SCAN       | czg.testtab_copy_v1_53897[DIS] | (a{S} = 2) | testtab_copy    |
+----+-------------+-------------+--------------------------------+------------+-----------------+
4 rows in set (Elapsed: 00:00:00.01)

视图展开之后，过滤条件传递到了v2，下面我们来看看效率如何。

7、SQL效率对比

我们每个SQL执行三次取最好的，使每次SQL执行都是软解析，并且都从缓冲区中读取数据。

（1）不加HINT

gbase> SELECT V1.* FROM CZG.V1 INNER JOIN CZG.V2 ON V1.A = V2.A;
+------+------+------+------+--------+--------+------------+---------------------+
| a    | b    | c    | d    | e      | f      | g          | h                   |
+------+------+------+------+--------+--------+------------+---------------------+
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
+------+------+------+------+--------+--------+------------+---------------------+
49 rows in set (Elapsed: 00:00:00.10)

（3）加HINT

gbase> SELECT /*+_t_gcluster_fold_tree_optimize_new(1)*/ V1.* FROM CZG.V1 INNER JOIN CZG.V2 ON V1.A = V2.A;
+------+------+------+------+--------+--------+------------+---------------------+
| a    | b    | c    | d    | e      | f      | g          | h                   |
+------+------+------+------+--------+--------+------------+---------------------+
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:09 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:11 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:12 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:13 |
|    2 |  2.2 | zxj  | lx   | lxlxlx | lxglxg | 1995-05-17 | 2024-08-15 17:58:14 |
+------+------+------+------+--------+--------+------------+---------------------+
49 rows in set (Elapsed: 00:00:00.02)

效率上确实有一定的提升。

​

本人此片博客CSDN地址：https://blog.csdn.net/qq_45111959/article/details/142052413

评论

登录后才可以发表评论

热门帖子