# 1. 环境准备
$ /yashandb/app/bin/yasql -U sys -P sys123 -D fgedudb

# 2. 创建部门模式
SQL> CREATE SCHEMA dept1 AUTHORIZATION fgedu;
SQL> CREATE SCHEMA dept2 AUTHORIZATION fgedu;
SQL> CREATE SCHEMA dept3 AUTHORIZATION fgedu;

# 3. 创建部门用户
SQL> CREATE USER dept1_user IDENTIFIED BY dept1_pass;
SQL> GRANT USAGE ON SCHEMA dept1 TO dept1_user;
SQL> GRANT ALL PRIVILEGES ON SCHEMA dept1 TO dept1_user;

SQL> CREATE USER dept2_user IDENTIFIED BY dept2_pass;
SQL> GRANT USAGE ON SCHEMA dept2 TO dept2_user;
SQL> GRANT ALL PRIVILEGES ON SCHEMA dept2 TO dept2_user;

SQL> CREATE USER dept3_user IDENTIFIED BY dept3_pass;
SQL> GRANT USAGE ON SCHEMA dept3 TO dept3_user;
SQL> GRANT ALL PRIVILEGES ON SCHEMA dept3 TO dept3_user;

# 4. 创建部门表
# 部门1创建表
$ /yashandb/app/bin/yasql -U dept1_user -P dept1_pass -D fgedudb
SQL> CREATE TABLE dept1.employees (
id INT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10,2)
);
SQL> INSERT INTO dept1.employees VALUES (1, ‘John’, ‘Manager’, 50000);

# 部门2创建表
$ /yashandb/app/bin/yasql -U dept2_user -P dept2_pass -D fgedudb
SQL> CREATE TABLE dept2.employees (
id INT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10,2)
);
SQL> INSERT INTO dept2.employees VALUES (1, ‘Jane’, ‘Engineer’, 60000);

# 部门3创建表
$ /yashandb/app/bin/yasql -U dept3_user -P dept3_pass -D fgedudb
SQL> CREATE TABLE dept3.employees (
id INT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100),
salary DECIMAL(10,2)
);
SQL> INSERT INTO dept3.employees VALUES (1, ‘Bob’, ‘Analyst’, 45000);

# 5. 验证隔离性
# 部门1用户尝试访问部门2的数据
$ /yashandb/app/bin/yasql -U dept1_user -P dept1_pass -D fgedudb
SQL> SELECT * FROM dept2.employees;

ERROR: permission denied for schema dept2
LINE 1: SELECT * FROM dept2.employees;
^

# 部门1用户只能访问自己的数据
SQL> SELECT * FROM dept1.employees;

id | name | position | salary
—-+——+———-+——–
1 | John | Manager | 50000
(1 row)

# 1. 配置资源组
$ /yashandb/app/bin/yasql -U sys -P sys123 -D fgedudb

# 创建资源组
SQL> CREATE RESOURCE GROUP dept1_group WITH (
cpu_limit = 20,
memory_limit = ‘1GB’,
max_connections = 50
);

SQL> CREATE RESOURCE GROUP dept2_group WITH (
cpu_limit = 30,
memory_limit = ‘2GB’,
max_connections = 80
);

SQL> CREATE RESOURCE GROUP dept3_group WITH (
cpu_limit = 25,
memory_limit = ‘1.5GB’,
max_connections = 60
);

# 分配用户到资源组
SQL> ALTER USER dept1_user RESOURCE GROUP dept1_group;
SQL> ALTER USER dept2_user RESOURCE GROUP dept2_group;
SQL> ALTER USER dept3_user RESOURCE GROUP dept3_group;

# 2. 验证资源限制
# 查看资源组配置
SQL> SELECT * FROM information_schema.resource_groups;

resource_group_name | cpu_limit | memory_limit | max_connections
——————–+———–+————–+—————-
dept1_group | 20 | 1GB | 50
dept2_group | 30 | 2GB | 80
dept3_group | 25 | 1.5GB | 60

# 查看用户资源组分配
SQL> SELECT usename, resource_group FROM pg_user WHERE usename LIKE ‘dept%’;

usename | resource_group
————+—————-
dept1_user | dept1_group
dept2_user | dept2_group
dept3_user | dept3_group

# 1. 查看租户资源使用情况
$ /yashandb/app/bin/yasql -U sys -P sys123 -D fgedudb
SQL> SELECT
usename,
current_query,
query_start,
state,
wait_event
FROM pg_stat_activity
WHERE usename LIKE ‘dept%’;

# 2. 查看表空间使用情况
SQL> SELECT
schemaname,
relname,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE ‘dept%’
ORDER BY pg_total_relation_size(c.oid) DESC;

# 3. 优化方案：
# a. 为大表创建索引
SQL> CREATE INDEX idx_dept1_employees_name ON dept1.employees(name);
SQL> CREATE INDEX idx_dept2_employees_position ON dept2.employees(position);
SQL> CREATE INDEX idx_dept3_employees_salary ON dept3.employees(salary);

# b. 调整资源分配
SQL> ALTER RESOURCE GROUP dept1_group SET (cpu_limit = 25);
SQL> ALTER RESOURCE GROUP dept2_group SET (memory_limit = ‘2.5GB’);

# c. 分区表优化
SQL> CREATE TABLE dept1.orders (
id INT PRIMARY KEY,
order_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

SQL> CREATE TABLE dept1.orders_2023 PARTITION OF dept1.orders
FOR VALUES FROM (‘2023-01-01’) TO (‘2024-01-01’);

SQL> CREATE TABLE dept1.orders_2024 PARTITION OF dept1.orders
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);

# 4. 验证优化效果
# 查看查询执行计划
SQL> EXPLAIN ANALYZE SELECT * FROM dept1.employees WHERE name = ‘John’;

# 查看资源使用情况
SQL> SELECT * FROM pg_stat_user_tables WHERE schemaname LIKE ‘dept%’;