南大通用GBase 8a同义词synonym使用样例

GBase 8a支持同义词synonym,本文用862Build43版本对同义词功能进行了适用,并对适用场景进行建议。

语法

其中public 关键字创建公共同义词,用户无需指定库名就可以访问。否则默认创建的是私有同义词,只有在当前指定库可用。

create [public] synonym SYNONYM_NAME for DBNAME.OBJECT_NAME;

大数据场景

除了传统的同义词使用场景(这部分请自行网络搜索),对GBase 8a这种大数据分析场景,一般会有非常多的对象,特别是表,会有几十万,几百万的表。

这么多的对象,对操作系统的文件系统提出了压力,在访问一个目录下几百万的文件,也非常耗时且消耗IO资源。

通过同义词,可以考虑将对象,分类到不同的支持库下,比如按月分库。但在主库,用同义词,这样可以极大的减少每个库下文件数量,减少磁盘IO,从而提升了系统性能。

比如视图,可以在其它库建视图,然后在主库建同义词。视图的元数据,可以用原始库,也可以用同义词的元数据表。

样例环境

如下创建了3个数据库,其中dbmain是主库,db1是测试库1, db2是测试库2。在2个测试库分别建了1个表。

[gbase@rh6-1 ~]$ gccli testdb

GBase client 8.6.2.43-R30.124361. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> create database db1;
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> create database db2;
Query OK, 1 row affected (Elapsed: 00:00:00.04)

gbase> create database dbmain;
Query OK, 1 row affected (Elapsed: 00:00:00.01)

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create table db1_t1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.36)

gbase> insert into db1_t1 values(1),(2);
Query OK, 2 rows affected (Elapsed: 00:00:00.38)
Records: 2  Duplicates: 0  Warnings: 0

gbase> use db2;
Query OK, 0 rows affected (Elapsed: 00:00:00.22)

gbase> create table db2_t1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.49)

gbase> insert into db2_t1 values(221),(222);
Query OK, 2 rows affected (Elapsed: 00:00:00.41)
Records: 2  Duplicates: 0  Warnings: 0

表的私有同义词样例

在dbmain分别创建db1和db2的两个同义词。

注意:同义词并不检查对象是否存在。

gbase> use dbmain;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> create synonym db1_t1 for db1.db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

gbase> create synonym db2_t1 for db2.db2_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

我们对同义词的表可以查询,变动数据等操作,就如同本地表一样。

表私有同义词查询


gbase> select * from db1_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.02)

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  221 |
|  222 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

表私有同义词DML


gbase> insert into db2_t1 values (2001),(2002);
Query OK, 2 rows affected (Elapsed: 00:00:00.26)
Records: 2  Duplicates: 0  Warnings: 0

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  221 |
|  222 |
| 2001 |
| 2002 |
+------+
4 rows in set (Elapsed: 00:00:00.01)

gbase> update db2_t1 set id=id+1;
Query OK, 4 rows affected (Elapsed: 00:00:00.84)
Rows matched: 4  Changed: 4  Warnings: 0

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  222 |
|  223 |
| 2002 |
| 2003 |
+------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> delete db2_t1 where id>=2003;
Query OK, 1 row affected (Elapsed: 00:00:00.40)

gbase> select * from db2_t1;
+------+
| id   |
+------+
|  222 |
|  223 |
| 2002 |
+------+
3 rows in set (Elapsed: 00:00:00.01)


表私有同义词DDL

当前版本,不支持对同义词的表结构变动。因为它不是个表。而同义词又没有【表结构】

gbase> alter table db2_t1 add column(name varchar(100));
ERROR 1702 (HY000): gcluster table error: (GBA-02DD-0017) Unknown table 'dbmain.db2_t1'

表共有同义词样例

共有同义词创建后,不需要使用库前缀,可以直接使用,等同于【全局】可用。

gbase> create public synonym pub_db1_t1 for db1.db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> use db2;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.06)

gbase> use dbmain;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.01)

gbase> use gclusterdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase> select count(*) from pub_db1_t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

表公有同义词的DML/DDL 操作和私有同义词无区别,就不多写了。

视图的同义词

我们在db1创建一个视图,然后再dbmain创建一个同义词。

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)


gbase> create view v_t1 as select * from db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.07)

gbase> select * from v_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> use dbmain;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create synonym v_t1 for db1.v_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from v_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

共有视图的DML,遵循视图是否可以的规定,当前版本不支持视图的insert。DDL不支持。

gbase> insert into v_t1 values(111),(112);
ERROR 1149 (42000): (GBA-02SC-1001) The query includes syntax that is not supported by the gcluster.
gbase>

其它同义词

任何数据库的对象,都可以创建同义词,包括同义词的同义词。

gbase> create synonym v_v_t1 for dbmain.v_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from v_v_t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

查看同义词

查看当前库可用同义词

能使用的私有同义词和共有同义词

gbase> select * from gbase.synonyms where owner=database() or owner=''\G;
*************************** 1. row ***************************
       owner: dbmain
synonym_name: db1_t1
object_owner: db1
 object_name: db1_t1
     db_link: NULL
*************************** 2. row ***************************
       owner: dbmain
synonym_name: db2_t1
object_owner: db2
 object_name: db2_t1
     db_link: NULL
*************************** 3. row ***************************
       owner: dbmain
synonym_name: v_t1
object_owner: db1
 object_name: v_t1
     db_link: NULL
*************************** 4. row ***************************
       owner: dbmain
synonym_name: v_t_notexist
object_owner: db1
 object_name: v_t1_178291728712
     db_link: NULL
*************************** 5. row ***************************
       owner:
synonym_name: v_t_notexist
object_owner: db1
 object_name: v_t1_178291728712
     db_link: NULL
5 rows in set (Elapsed: 00:00:00.00)

查看同义词的创建语法

gbase> show create synonym v_v_t1;
+---------------+----------------------------------------------+
| Synonym       | Create synonym                               |
+---------------+----------------------------------------------+
| dbmain.v_v_t1 | CREATE SYNONYM dbmain.v_v_t1 FOR dbmain.v_t1 |
+---------------+----------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> show create synonym v_t1;
+-------------+-----------------------------------------+
| Synonym     | Create synonym                          |
+-------------+-----------------------------------------+
| dbmain.v_t1 | CREATE SYNONYM dbmain.v_t1 FOR db1.v_t1 |
+-------------+-----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

提示

同义词不检查对象是否存在

利用这一点,可以提前创建同义词,等真是对象存在后,该同义词及时生效。


gbase> show create synonym v_t_notexist;
+---------------------+--------------------------------------------------------------+
| Synonym             | Create synonym                                               |
+---------------------+--------------------------------------------------------------+
| dbmain.v_t_notexist | CREATE SYNONYM dbmain.v_t_notexist FOR db1.v_t1_178291728712 |
+---------------------+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from v_t_notexist;
ERROR 1146 (42S02): Table 'db1.v_t1_178291728712' doesn't exist

gbase> create view db1.v_t1_178291728712 as select * from db1.db1_t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> select * from v_t_notexist;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase>

同义词不能和对象重名

和对象一样,作用域内不能重复。不能创建和表名重复的同义词,同样的,实体表也不能和同义词重名;

gbase> select * from v_t_notexist;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> create view v_t_notexist as select * from testdb.t1;
ERROR 1712 (HY000): gcluster view error: name 'dbmain.v_t_notexist' is already used by an existing synonym.
gbase>

权限

public 同义词必须有管理员权限,只有库级权限的是无法创建全局同义词的。

[gbase@rh6-1 ~]$ gccli -utestdb -ptestdb testdb

GBase client 8.6.2.43-R35.508906e415d. Copyright (c) 2004-2022, GBase.  All Righ

gbase> create public synonym pub_sy_t1 for t1;
ERROR 1822 (HY000): CREATE command denied to user 'testdb'@'localhost' for public synonym 'pub_sy_t1'
gbase>