1、GaussDB 常用管理操作整理

[TOC]
# GaussDB 实验与操作整理

## 一、常用管理操作

### 环境准备

```bash
/usr/bin/chroot /var/chroot
source /home/Ruby/gaussdb_env-file
```

---

### 创建用户

```sql
CREATE USER user1 WITH PASSWORD 'password';
CREATE USER user2 WITH PASSWORD 'password';
GRANT ALL PRIVILEGES TO user2;
```

---

### 表空间管理

```sql
-- 创建表空间
CREATE TABLESPACE my_tablespace
OWNER user1
RELATIVE LOCATION 'test_tablespace/test_tablespace1';

-- 查看表空间
SELECT * FROM pg_tablespace;
SELECT * FROM pg_tablespace_location(
    (SELECT oid FROM pg_tablespace WHERE spcname='my_tablespace')
);

-- 修改表空间所有者
ALTER TABLESPACE my_tablespace OWNER TO user2;

-- 修改表空间名
ALTER TABLESPACE my_tablespace RENAME TO my_tb2;

-- 删除表空间
DROP TABLESPACE my_tb2;
```

---

### 表管理

```sql
-- 创建模式
CREATE SCHEMA hr;

-- 设置当前模式
SET search_path = hr;

-- 创建表
CREATE TABLE employees(
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    department_id NUMBER,
    position VARCHAR2(50)
);

-- 插入数据
INSERT INTO employees VALUES (1, 'john', 1, 'Manager');
INSERT INTO employees VALUES (2, 'jane', 2, 'Developer');
INSERT INTO employees VALUES (3, 'alice', 1, 'Developer');
INSERT INTO employees VALUES (4, 'bob', 2, 'Manager');
```

#### 行级访问控制

```sql
-- 创建用户
CREATE USER alica WITH PASSWORD 'password';

-- 授权
GRANT USAGE ON SCHEMA hr TO alica;
GRANT SELECT ON employees TO alica;

-- 启用行级安全
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE ROW LEVEL SECURITY POLICY employees_rls
ON employees USING (name = current_user);

-- 切换用户测试
\c alica
SELECT * FROM hr.employees;

-- 删除表
\c root
DROP TABLE employees;
```

---

### 分区表

```sql
-- 范围分区
CREATE TABLE range_emp(
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(40),
    last_name   VARCHAR2(40),
    hire_date DATE
)
PARTITION BY RANGE(hire_date)(
    PARTITION p1 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD'))
);

-- 列表分区
CREATE TABLE list_emp(
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(40),
    last_name   VARCHAR2(40),
    department_id NUMBER
)
PARTITION BY LIST(department_id)(
    PARTITION p_sales VALUES('1'),
    PARTITION p_hr VALUES('2'),
    PARTITION p_it VALUES('3'),
    PARTITION p_marketing VALUES('4')
);

-- 哈希分区
CREATE TABLE hash_emp (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
)
PARTITION BY HASH (employee_id)
PARTITIONS 4;
```

#### 分区操作

```sql
-- 查询分区信息
SELECT table_name, partition_name, high_value
FROM db_tab_partitions
WHERE table_name = 'range_emp';

-- 添加分区
ALTER TABLE range_emp ADD PARTITION p3 VALUES LESS THAN (TO_DATE('2030-01-01', 'YYYY-MM-DD'));

-- 拆分分区
ALTER TABLE range_emp
SPLIT PARTITION p2 AT (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
INTO (PARTITION p2a, PARTITION p2b);

-- 合并分区
ALTER TABLE range_emp
MERGE PARTITIONS p2b, p3 INTO PARTITION p3;

-- 删除分区
ALTER TABLE range_emp DROP PARTITION p1;

-- 删除整个表
DROP TABLE list_emp;
DROP TABLE hash_emp;
```

---

### 索引管理

```sql
-- 创建索引
CREATE INDEX idx_last_name ON range_emp(last_name);

-- 查看索引
\di+ idx_last_name;

-- 查询系统和用户定义的所有索引
SELECT relname FROM pg_class WHERE relkind IN ('i','I');

-- 删除索引
DROP INDEX idx_last_name;
```

---

### 视图管理

```sql
-- 普通视图
CREATE VIEW employee_full_name AS
SELECT employee_id, first_name || ' ' || last_name AS full_name, hire_date
FROM range_emp;

SELECT * FROM employee_full_name;

-- 创建表用于物化视图
CREATE TABLE employees_atore(
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
) WITH(orientation=row, storage_type=astore);

-- 插入数据
INSERT INTO employees_atore VALUES (1, 'John', 'Doe', TO_DATE('2020-01-15','YYYY-MM-DD'));
INSERT INTO employees_atore VALUES (2, 'Jane', 'Smith', TO_DATE('2019-06-30','YYYY-MM-DD'));
INSERT INTO employees_atore VALUES (3, 'Michael', 'Johnson', TO_DATE('2021-02-20','YYYY-MM-DD'));
INSERT INTO employees_atore VALUES (4, 'Emily', 'Davis', TO_DATE('2018-10-05','YYYY-MM-DD'));
INSERT INTO employees_atore VALUES (5, 'James', 'Brown', TO_DATE('2022-08-12','YYYY-MM-DD'));

-- 物化视图
CREATE MATERIALIZED VIEW emp_atore_full_name AS
SELECT employee_id, first_name || ' ' || last_name AS full_name, hire_date
FROM employees_atore;

SELECT * FROM emp_atore_full_name;

-- 修改并刷新
UPDATE employees_atore SET hire_date = TO_DATE('2025-01-01','YYYY-MM-DD') WHERE employee_id = 3;
REFRESH MATERIALIZED VIEW emp_atore_full_name;

-- 删除视图
DROP VIEW employee_full_name;
DROP MATERIALIZED VIEW emp_atore_full_name;
```

---

### 序列管理

```sql
-- 创建序列
CREATE SEQUENCE employee_id_seq
START WITH 1000
INCREMENT BY 1;

-- 使用序列插入数据
INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
VALUES (employee_id_seq.NEXTVAL, 'Alice', 'Smith', TO_DATE('2023-01-15','YYYY-MM-DD'));

-- 查询数据
SELECT * FROM range_emp;
```

---

### 同义词管理

```sql
-- 创建同义词
CREATE OR REPLACE SYNONYM remp FOR range_emp;

-- 使用同义词
SELECT * FROM remp;
INSERT INTO remp VALUES (2, 'John', 'King', TO_DATE('2025-03-26','YYYY-MM-DD'));

-- 查看同义词
SELECT synname, synobjschema, synobjname
FROM pg_synonym
WHERE synname = 'remp';

-- 删除同义词
DROP SYNONYM remp;
```

---

## 二、PL/SQL 编程整理

### 匿名块

```sql
BEGIN
    DBE_OUTPUT.PRINT_LINE('Hello, world!');
END;
/
```

### 存储过程

```sql
CREATE OR REPLACE PROCEDURE insert_employee(
    p_employee_id NUMBER,
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_hire_date DATE
)
IS
BEGIN
    INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
    VALUES (p_employee_id, p_first_name, p_last_name, p_hire_date);
    COMMIT;
END;
/

CALL insert_employee(1, 'John', 'Doe', TO_DATE('2022-05-15','YYYY-MM-DD'));
```

### 函数

```sql
CREATE OR REPLACE FUNCTION get_full_name(p_employee_id NUMBER)
RETURN VARCHAR2
IS
    v_full_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_full_name
    FROM range_emp
    WHERE employee_id = p_employee_id;

RETURN v_full_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Employee not found';
END;
/

SELECT get_full_name(1) FROM dual;
```

### 结构与声明

```sql
DECLARE
    v_employee_id NUMBER := 1;
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_employee_name
    FROM range_emp
    WHERE employee_id = v_employee_id;

DBE_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/
```

### IF 语句

```sql
CREATE OR REPLACE PROCEDURE check_hire_date(p_employee_id NUMBER)
IS
    v_hire_date DATE;
BEGIN
    SELECT hire_date INTO v_hire_date
    FROM range_emp
    WHERE employee_id = p_employee_id;

IF v_hire_date < TO_DATE('2020-01-01','YYYY-MM-DD') THEN
        DBE_OUTPUT.PUT_LINE('Employee hired before 2020');
    ELSIF v_hire_date < TO_DATE('2022-01-01','YYYY-MM-DD') THEN
        DBE_OUTPUT.PUT_LINE('Employee hired after 2020 but before 2022');
    ELSE
        DBE_OUTPUT.PUT_LINE('Employee hired after 2022');
    END IF;
END;
/
```

### 游标

```sql
DECLARE
    CURSOR emp_cursor IS
        SELECT first_name, last_name FROM range_emp;

v_first_name VARCHAR2(50);
    v_last_name  VARCHAR2(50);
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_first_name, v_last_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBE_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    CLOSE emp_cursor;
END;
/
```

### 错误捕获

```sql
ALTER TABLE range_emp
ADD CONSTRAINT uq_employee_id UNIQUE (employee_id);

CREATE OR REPLACE PROCEDURE insert_employee_with_exception(
    p_employee_id NUMBER,
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_hire_date DATE
)
IS
BEGIN
    BEGIN
        INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
        VALUES (p_employee_id, p_first_name, p_last_name, p_hire_date);
        COMMIT;
    EXCEPTION
        WHEN unique_violation THEN
            RAISE NOTICE 'Error: Employee ID % already exists.', p_employee_id;
        WHEN OTHERS THEN
            RAISE NOTICE 'An unexpected error occurred.';
    END;
END;
/
```

### 事务管理

```sql
CREATE OR REPLACE PROCEDURE insert_multiple_employees
IS
BEGIN
    INSERT INTO range_emp VALUES (3, 'Mary', 'Doe', TO_DATE('2022-05-15','YYYY-MM-DD'));
    INSERT INTO range_emp VALUES (4, 'Jack', 'Smith', TO_DATE('2023-06-10','YYYY-MM-DD')); -- 故意重复

COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBE_OUTPUT.PUT_LINE('Transaction failed and rolled back');
END;
/
```

### 动态语句

```sql
CREATE OR REPLACE PROCEDURE dynamic_query_employee(p_employee_id NUMBER)
IS
    v_sql VARCHAR2(1000);
    v_first_name VARCHAR2(50);
    v_last_name  VARCHAR2(50);
BEGIN
    v_sql := 'SELECT first_name, last_name FROM range_emp WHERE employee_id = ' || p_employee_id;
    EXECUTE IMMEDIATE v_sql INTO v_first_name, v_last_name;

DBE_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);
END;
/
```

---

## 三、JDBC 负载均衡与读写分离

### 环境准备

```bash
ssh root@xxx.xxx.x.xx
yum install -y wget
mkdir -p /opt/expt/db/libs
cd /opt/expt/db/libs
wget https://sandbox-experiment-files.obs.cn-north-4.myhuaweicloud.com/20000743/GaussDB_driver.zip
unzip GaussDB_driver.zip
cd GaussDB_driver/Distributed/Kylinv10_arm_64/
tar -xzvf GaussDB-Kernel_505.2.0_Kylin_64bit_Jdbc.tar.gz
```

安装 JDK：

```bash
cd /root
wget https://sandbox-experiment-files.obs.cn-north-4.myhuaweicloud.com/20001943/jdk-8u442-linux-aarch64.tar.gz
tar -xzvf jdk-8u442-linux-aarch64.tar.gz
mv jdk1.8.0_442 /usr/lib/
ln -s /usr/lib/jdk1.8.0_442/bin/java /usr/local/bin/java
ln -s /usr/lib/jdk1.8.0_442/bin/javac /usr/local/bin/javac
java -version
javac -version
```

---

### JDBC 测试代码

```java
import java.sql.*;

public class test {
    private static String gs_user = "root";
    private static String gs_passwd = "password";
    private static String gs_url = "jdbc:gaussdb://ip1:port,ip2:port,ip3:port/postgres?autoBalance=roundrobin&logLevel=DEBUG";

public static void main(String[] args) {
        String testQuery = "SELECT 1";
        try (Connection conn = DriverManager.getConnection(gs_url, gs_user, gs_passwd);
             Statement st = conn.createStatement();
             PreparedStatement pst = conn.prepareStatement(testQuery)) {
            for (int i = 0; i < 100; i++) {
                try {
                    ResultSet resultSet = pst.executeQuery();
                    if (resultSet.next()) {}
                } catch (Exception e) {
                    System.err.println("第 " + (i + 1) + " 次连接失败: " + e.getMessage());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
```

编译运行：

```bash
javac test.java
java -cp $CLASSPATH:./*:/opt/expt/db/libs/GaussDB_driver/Distributed/Kylinv10_arm_64/* test
```

---

### 负载均衡策略

* `autoBalance=roundrobin` → 轮询分配连接
* `autoBalance=priorityn` → 优先前 n 个 CN 节点，失效后随机
* `autoBalance=shuffle` → 随机分配连接
* `autoBalance=false` → 不开启负载均衡（默认）

---

### 读写分离

修改 URL：

```java
private static String gs_url = "jdbc:gaussdb://ip1:port,ip2:port,ip3:port/postgres?targetServerType=master";
```

* `targetServerType=master` → 只连接主库
* `targetServerType=slave` → 只连接备库
* `targetServerType=preferSlave` → 优先备库，不可用时主库

FYF

导航1234

最近发表

友情链接