暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GBase 8a 学习笔记 009 —— GBase 8a MPP Cluster SQL DDL-视图、索引

GBase 8a 学习笔记 009 —— GBase 8a MPP Cluster SQL DDL-视图、索引

心有阳光 2023-03-04
625

GBase 8a 学习笔记 009 —— GBase 8a MPP Cluster SQL DDL-视图、索引

视图VIEW

视图

  • 是由SELECT语句组成的查询定义的虚拟表（Virtual Table）。
  • 视图跟表不同，表有实际储存数据，而视图是虚拟表，本身不实际储存数据。
  • 视图就像一个窗口，引用视图时动态生成，数据来自当前或其它数据库的一个或多个表，或者其它视图，对视图的查询操作跟表相同。

作用

  • 简单性：简化用户对数据的理解，也简化操作，被经常使用的查询可以被定义为视图。
  • 安全性：通过视图用户只能查询他们所能见到的数据。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上。

限制

  • 禁止对视图进行INSERT、UPDATE 和 DELETE 操作

视图操作语法

创建视图

CREATE [OR REPLACE] VIEW [<视图名> [ ( <列名1>, <列名2>, ...) ] AS SELECT…；

修改视图

ALTER VIEW <视图名> [ ( <列名1>, <列名2>, ...) ] AS SELECT …;

删除视图

DROP VIEW [IF EXISTS]<视图名>;

示例：

gbase> select * from tab001 order by id;
+------+--------+---------+------+
| id   | name   | address | sex  |
+------+--------+---------+------+
|    1 | 张三   | 甲区    | 男   |
|    2 | 李四   | 甲区    | 男   |
|    3 | 王丽   | 丙区    | 女   |
+------+--------+---------+------+
3 rows in set (Elapsed: 00:00:00.05)

gbase> CREATE OR REPLACE VIEW v_t AS SELECT name, address, sex FROM tab001;
Query OK, 0 rows affected (Elapsed: 00:00:02.25)

gbase> select * from v_t;
+--------+---------+------+
| name   | address | sex  |
+--------+---------+------+
| 王丽   | 丙区    | 女   |
| 李四   | 甲区    | 男   |
| 张三   | 甲区    | 男   |
+--------+---------+------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> ALTER VIEW test.v_t(a, b) AS SELECT name, address FROM tab001;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase> select * from v_t;
+--------+--------+
| a      | b      |
+--------+--------+
| 王丽   | 丙区   |
| 李四   | 甲区   |
| 张三   | 甲区   |
+--------+--------+
3 rows in set (Elapsed: 00:00:00.01)

gbase8a087.png

gbase> show tables from test like 'v%';
+---------------------+
| Tables_in_test (v%) |
+---------------------+
| v_t                 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> DROP VIEW if exists v_t;
Query OK, 0 rows affected (Elapsed: 00:00:17.46)

gbase> show tables from test like 'v%';
Empty set (Elapsed: 00:00:00.00)

gbase>

gbase8a088.png

表和视图查看操作语法

命令语法

SHOW TABLES [FROM DB_NAME] [LIKE 'pattern'];
DESC table_name;
SHOW CREATE TABLE [database_name.]table_name
SHOW CREATE VIEW [database_name.]view_name

示例

# 显示以t字母开头的表和视图
gbase> show tables from test like 't%';
+---------------------+
| Tables_in_test (t%) |
+---------------------+
| t201                |
| t202                |
| t203                |
| t204                |
| t205                |
| t206                |
| t207                |
| t208                |
| tab001              |
+---------------------+
9 rows in set (Elapsed: 00:00:00.00)

# 显示以v字母开头的表和视图
gbase> show tables from test like 'v%';
+---------------------+
| Tables_in_test (v%) |
+---------------------+
| v_t                 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

# 查看tab001表的字段信息
gbase> desc tab001;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
| sex     | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)

# 查看tab001表的建表语句
gbase> SHOW create table tab001;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                     |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab001 | CREATE TABLE "tab001" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "address" varchar(20) DEFAULT NULL,
  "sex" varchar(20) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

# 查看v_t视图的创建语句
gbase> SHOW create view v_t;
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                             | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_t  | CREATE ALGORITHM=TEMPTABLE DEFINER="root"@"%" SQL SECURITY DEFINER VIEW "v_t" AS select "tab001"."name" AS "name","tab001"."address" AS "address","tab001"."sex" AS "sex" from "tab001" | utf8                 | utf8_general_ci      |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

gbase8a089.png

gbase8a090.png

索引管理

索引概述

  • 数据库索引是为了提升查询定位效率而对表字段附加的一种标识，避免全表扫描。
  • 索引文件单独存储， GBase 8a 系统索引文件存储在 metadata目录下。
  • GBase 8a 系统支持三类索引：
    • 智能索引：
      粗粒度，在DC满块时自动创建智能索引，所有列都有，对用户透明，无需用户手动维护。
    • hash索引：
      提升等值查询的性能，需用户根据查询列手动创建，会影响数据入库性能。
    • 全文检索：
      提升文本内容的查询效率，采用全单字索引方式，并且可以保证 100%的查询召回率，
      需要用户手动创建，需特别安装支持全文检索的插件包后才能使用全文检索功能。

hash索引

  • hash索引：提高等值精确查询性能
    • 默认创建 GLOBAL 的哈希索引，基于整列数据创建索引。
    • 可以基于指定的 DC窗口建立分段索引；
    • hash索引文件为独立的文件存储。

创建hash索引的语法有2种形式

CREATE INDEX index_name ON [vc_name.][database_name.]table_name(column_name) [key_block_size = size_value] USING HASH [GLOBAL| key_dc_size=num];

# 或者通过 ALTER 语法创建索引:
ALTER TABLE [table_name] ADD INDEX index_name（column_name）USING HASH 
[key_dc_size = num][key_block_size = size_value];

说明：

  • key_dc_size：设置按 DC窗口大小来建立分段的hash索引文件；
  • key_block_size：索引数据按页存储，设置每个数据块占用的页大小，4k~32k之间，为4k的倍数；

Hash 索引操作语法

查看 INDEX 名称

SHOW INDEX FROM [database_name.]table_name;

删除索引的2种语法

DROP INDEX <索引名> ON <表名> ;
# 或者：
ALTER TABLE [table_name] DROP INDEX index_name;
  • 限制说明
    • BLOB、TEXT列不能创建HASH索引
    • 该列数据量较大，但distinct 值较少，即重复值较多时，也不适合使用HASH索引。
    • 创建索引时，只能指定单列，不能指定多列创建联合索引；
    • 创建索引会影响加载性能，需慎用。

Hash 索引操作示例

# 基于列的全部数据建立索引
gbase> create index idx_t1_a on tab001 (id) using hash global;
Query OK, 0 rows affected (Elapsed: 00:00:00.14)
Records: 0  Duplicates: 0  Warnings: 0

# 基于列的全部数据建立索引，索引数据页大小为4k
gbase> alter table tab001 ADD INDEX h_index(name) key_block_size=4096 USING hash;
Query OK, 0 rows affected (Elapsed: 00:00:00.10)
Records: 0  Duplicates: 0  Warnings: 0

# 基于指定的 50个DC窗口建立分段hash索引文件
gbase> create index idx_t1_c on tab001(address) key_dc_size=50 using hash global;
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
Records: 0  Duplicates: 0  Warnings: 0

# 查看已创建的索引
gbase> show index from tab001;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type  | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| tab001 |          1 | idx_t1_a |            1 | id          | NULL      |        NULL |     NULL | NULL   | YES  | GLOBAL HASH |         |
| tab001 |          1 | h_index  |            1 | name        | NULL      |        NULL |     NULL | NULL   | YES  | GLOBAL HASH |         |
| tab001 |          1 | idx_t1_c |            1 | address     | NULL      |        NULL |     NULL | NULL   | YES  | GLOBAL HASH |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
3 rows in set (Elapsed: 00:00:00.00)

# 删除已创建的索引
gbase> alter table tab001 drop index idx_t1_a;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
Records: 0  Duplicates: 0  Warnings: 0

gbase> alter table tab001 drop index h_index;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
Records: 0  Duplicates: 0  Warnings: 0

gbase> alter table tab001 drop index idx_t1_c;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
Records: 0  Duplicates: 0  Warnings: 0

gbase> show index from tab001;
Empty set (Elapsed: 00:00:00.00)

gbase>

gbase8a091.png

gbase墨力计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

GBase 8a 学习笔记（共21篇）
GBase 8a 学习笔记

评论