KingBaseES 数据库与表

概述

数据库与表是 KingBaseES 中最基本的逻辑对象，用于组织和存储数据。了解 KingBaseES 中数据库和表的创建、管理和优化对于 DBA 至关重要。本文档将介绍 KingBaseES 数据库与表的基本概念、创建方法、管理技巧和最佳实践。

数据库管理

1. 数据库概述

数据库是 KingBaseES 中存储表、索引等对象的逻辑容器。每个数据库都有独立的命名空间，包含自己的表、索引、视图等对象。

2. 数据库创建

基本语法

sql

CREATE DATABASE database_name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ];

示例

sql

-- 创建数据库
CREATE DATABASE mydb;

-- 指定所有者和表空间
CREATE DATABASE mydb
    OWNER kingbase
    TABLESPACE userspace
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8'
    CONNECTION LIMIT 100;

3. 数据库查看

查看所有数据库

sql

-- 使用 psql 命令
\l

-- 查询系统视图
SELECT datname FROM pg_database;

查看数据库详情

sql

-- 使用 psql 命令
\l+ mydb

-- 查询系统视图
SELECT * FROM pg_database WHERE datname = 'mydb';

4. 数据库修改

修改数据库属性

sql

-- 修改所有者
ALTER DATABASE mydb OWNER TO new_owner;

-- 修改表空间
ALTER DATABASE mydb SET TABLESPACE new_tablespace;

-- 修改连接限制
ALTER DATABASE mydb CONNECTION LIMIT 200;

-- 修改配置参数
ALTER DATABASE mydb SET work_mem = '16MB';

5. 数据库删除

基本语法

sql

DROP DATABASE [ IF EXISTS ] database_name [ WITH (FORCE) ];

示例

sql

-- 删除数据库
DROP DATABASE mydb;

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

-- 强制删除（断开所有连接）
DROP DATABASE mydb WITH (FORCE);

表管理

1. 表概述

表是 KingBaseES 中存储数据的基本单位，由行和列组成。每个表都有自己的结构定义，包括列名、数据类型、约束等。

2. 数据类型

数值类型

整数类型：SMALLINT、INT、INTEGER、BIGINT
浮点类型：REAL、DOUBLE PRECISION
精确数值类型：NUMERIC(precision, scale)
货币类型：MONEY

字符类型

定长字符：CHAR(n)、CHARACTER(n)
变长字符：VARCHAR(n)、CHARACTER VARYING(n)
文本类型：TEXT

日期/时间类型

日期类型：DATE
时间类型：TIME [ WITHOUT TIME ZONE ]、TIME WITH TIME ZONE
时间戳类型：TIMESTAMP [ WITHOUT TIME ZONE ]、TIMESTAMP WITH TIME ZONE
间隔类型：INTERVAL

布尔类型

布尔类型：BOOLEAN（TRUE、FALSE、NULL）

二进制类型

二进制数据：BYTEA

枚举类型

枚举类型：ENUM ('value1', 'value2', ...)

复合类型

复合类型：使用 CREATE TYPE 定义

数组类型

数组类型：任意数据类型后跟 []，如 INT[]、VARCHAR(50)[]

JSON 类型

JSON：存储 JSON 数据
JSONB：二进制 JSON，支持索引

3. 表创建

基本语法

sql

CREATE TABLE table_name (
    column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    [, ... ]
    [ table_constraint [ ... ] ]
) [ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ];

示例

sql

-- 创建基本表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status BOOLEAN DEFAULT TRUE
);

-- 创建带外键的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
);

-- 指定表空间和存储参数
CREATE TABLE large_table (
    id BIGSERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE big_tablespace
WITH (
    autovacuum_enabled = true,
    toast.autovacuum_enabled = true,
    autovacuum_vacuum_scale_factor = 0.05
);

4. 表结构查看

查看所有表

sql

-- 使用 psql 命令
\d

-- 查询系统视图
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

查看表结构

sql

-- 使用 psql 命令
\d table_name

-- 使用 INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table_name';

5. 表修改

添加列

sql

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

修改列

sql

-- 修改数据类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

-- 修改默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT FALSE;

-- 设置列非空
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

删除列

sql

ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS last_login;

添加约束

sql

-- 添加主键
ALTER TABLE orders ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加检查约束
ALTER TABLE orders ADD CHECK (total_amount > 0);

删除约束

sql

-- 删除主键
ALTER TABLE orders DROP CONSTRAINT orders_pkey;

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT users_email_key;

6. 表删除

基本语法

sql

DROP TABLE [ IF EXISTS ] table_name [, ... ] [ CASCADE | RESTRICT ];

示例

sql

-- 删除表
DROP TABLE users;

-- 如果存在则删除
DROP TABLE IF EXISTS users;

-- 级联删除（删除依赖对象）
DROP TABLE users CASCADE;

-- 限制删除（如果有依赖对象则失败）
DROP TABLE users RESTRICT;

表结构设计

1. 设计原则

范式设计

第一范式（1NF）：确保每列原子性
第二范式（2NF）：确保非主键列完全依赖于主键
第三范式（3NF）：确保非主键列不传递依赖于主键
BCNF：消除主属性对主键的部分依赖和传递依赖

反范式设计

在特定场景下适当冗余数据
提高查询性能
减少表连接操作
适合读多写少的场景

2. 主键设计

选择原则

唯一性：主键值必须唯一
稳定性：主键值不应经常变化
简洁性：主键应尽可能简单
不可为空：主键值不能为 NULL

常见主键类型

自增整数：SERIAL、BIGSERIAL
UUID：全局唯一标识符
复合主键：多个列组合作为主键
业务主键：使用业务字段作为主键

示例

sql

-- 自增整数主键
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    ...
);

-- UUID 主键
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    PRIMARY KEY (order_id, product_id),
    ...
);

3. 索引设计

基本原则

为经常查询的列创建索引
为外键列创建索引
为经常排序、分组的列创建索引
避免为频繁更新的列创建索引
避免创建过多索引

示例

sql

-- 创建 B-tree 索引
CREATE INDEX idx_users_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_orders_user_id_status ON orders(user_id, status);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

表空间与存储

1. 表空间分配

将表移动到不同表空间

sql

ALTER TABLE users SET TABLESPACE new_tablespace;

-- 移动表和索引
ALTER TABLE users SET TABLESPACE new_tablespace INCLUDING INDEXES;

2. 存储参数优化

常用存储参数

autovacuum_enabled：是否启用自动清理
toast.autovacuum_enabled：是否对 TOAST 表启用自动清理
autovacuum_vacuum_scale_factor：自动清理的比例因子
autovacuum_analyze_scale_factor：自动分析的比例因子
fillfactor：表填充因子（默认 90）

示例

sql

-- 修改表存储参数
ALTER TABLE large_table SET (
    autovacuum_enabled = true,
    autovacuum_vacuum_scale_factor = 0.05,
    fillfactor = 80
);

表维护

1. VACUUM 操作

概述

VACUUM 用于回收表中被删除行占用的空间，防止表膨胀。

基本语法

sql

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_name [ (column_name [, ... ]) ] ];

示例

sql

-- 普通 VACUUM
VACUUM users;

-- 带分析
VACUUM ANALYZE users;

-- 全量 VACUUM（需要独占表锁）
VACUUM FULL users;

-- 详细输出
VACUUM VERBOSE ANALYZE users;

2. ANALYZE 操作

概述

ANALYZE 用于收集表的统计信息，帮助查询优化器生成更好的执行计划。

基本语法

sql

ANALYZE [ VERBOSE ] [ table_name [ (column_name [, ... ]) ] ];

示例

sql

-- 分析表
ANALYZE users;

-- 分析特定列
ANALYZE users (username, email);

-- 详细输出
ANALYZE VERBOSE users;

3. 表重建

概述

表重建可以优化表的物理存储，减少表碎片。

方法

sql

-- 方法 1：使用 CREATE TABLE AS
CREATE TABLE users_new AS SELECT * FROM users ORDER BY id;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
ALTER TABLE users ADD PRIMARY KEY (id);

-- 方法 2：使用 VACUUM FULL
VACUUM FULL users;

-- 方法 3：使用 CLUSTER
CLUSTER users USING idx_users_id;

最佳实践

1. 数据库设计最佳实践

合理规划数据库：根据业务需求规划数据库数量
选择合适的字符集：建议使用 UTF-8
设置合适的连接限制：避免资源耗尽
定期备份数据库：确保数据安全

2. 表设计最佳实践

遵循范式设计：确保数据完整性
选择合适的数据类型：减少存储空间，提高性能
设计合理的主键：确保唯一性和稳定性
创建适当的索引：优化查询性能
设置合理的约束：确保数据完整性

3. 表管理最佳实践

定期执行 VACUUM ANALYZE：保持表的良好状态
监控表空间使用情况：及时扩容
定期检查表碎片：重建或整理碎片化的表
合理设置存储参数：根据表的特性优化

4. 性能优化最佳实践

优化查询语句：减少全表扫描
合理使用索引：避免过多或过少索引
使用分区表：管理大表
优化存储结构：选择合适的表空间

常见问题（FAQ）

Q: KingBaseES 支持哪些数据类型？

A: KingBaseES 支持多种数据类型，包括数值类型、字符类型、日期/时间类型、布尔类型、二进制类型、枚举类型、复合类型、数组类型和 JSON 类型等。

Q: 如何创建一个自增主键？

A: 可以使用 SERIAL 或 BIGSERIAL 数据类型创建自增主键，例如：

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    ...
);

Q: 如何查看表的大小？

A: 可以使用以下方法查看表的大小：

sql

-- 使用 psql 命令
\d+ table_name

-- 查询系统函数
SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_size,
       pg_size_pretty(pg_relation_size('table_name')) AS table_size,
       pg_size_pretty(pg_indexes_size('table_name')) AS index_size;

Q: 如何重命名表？

A: 使用 ALTER TABLE 语句重命名表：

sql

ALTER TABLE old_table_name RENAME TO new_table_name;

Q: 如何复制表结构？

A: 可以使用以下方法复制表结构：

sql

-- 复制表结构（不包括数据）
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=0;

-- 复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM old_table;

Q: 如何查看表的索引？

A: 可以使用以下方法查看表的索引：

sql

-- 使用 psql 命令
\d table_name

-- 查询系统视图
SELECT indexname FROM pg_indexes WHERE tablename = 'table_name';

Q: 什么是 TOAST 表？

A: TOAST（The Oversized-Attribute Storage Technique）是 KingBaseES 用于存储大对象的技术。当表中的某些列数据超过页大小的 2KB 时，这些数据会被存储在 TOAST 表中。

Q: 如何优化大表查询性能？

A: 优化大表查询性能的方法包括：

创建适当的索引
使用分区表
优化查询语句
增加内存配置
使用合适的存储设备

总结

数据库与表是 KingBaseES 中最基本的逻辑对象，掌握其创建、管理和优化技巧对于 DBA 至关重要。通过合理的数据库设计、表结构设计和索引设计，可以提高数据库的性能、可靠性和可扩展性。

在实际运维过程中，DBA 应该根据业务需求和系统特点，合理规划数据库和表，定期进行维护和优化，确保数据库系统的稳定运行。同时，了解 KingBaseES 中数据库和表的最佳实践，可以帮助 DBA 提高工作效率，避免常见问题。

KingBaseES 数据库与表 ​

概述 ​

数据库管理 ​

1. 数据库概述 ​

2. 数据库创建 ​

基本语法 ​

示例 ​

3. 数据库查看 ​

查看所有数据库 ​

查看数据库详情 ​

4. 数据库修改 ​

修改数据库属性 ​

5. 数据库删除 ​

基本语法 ​

示例 ​

表管理 ​

1. 表概述 ​

2. 数据类型 ​

数值类型 ​

字符类型 ​

日期/时间类型 ​

布尔类型 ​

二进制类型 ​

枚举类型 ​

复合类型 ​

数组类型 ​

JSON 类型 ​

3. 表创建 ​

基本语法 ​

示例 ​

4. 表结构查看 ​

查看所有表 ​

查看表结构 ​

5. 表修改 ​

添加列 ​

修改列 ​

删除列 ​

添加约束 ​

删除约束 ​

6. 表删除 ​

基本语法 ​

示例 ​

表结构设计 ​

1. 设计原则 ​

范式设计 ​

反范式设计 ​

2. 主键设计 ​

选择原则 ​

常见主键类型 ​

示例 ​

3. 索引设计 ​

基本原则 ​

示例 ​

表空间与存储 ​

1. 表空间分配 ​

将表移动到不同表空间 ​

2. 存储参数优化 ​

常用存储参数 ​

示例 ​

表维护 ​

1. VACUUM 操作 ​

概述 ​

基本语法 ​

示例 ​

2. ANALYZE 操作 ​

概述 ​

基本语法 ​

示例 ​

3. 表重建 ​

概述 ​

方法 ​

最佳实践 ​

1. 数据库设计最佳实践 ​

2. 表设计最佳实践 ​

3. 表管理最佳实践 ​

4. 性能优化最佳实践 ​

常见问题（FAQ） ​

Q: KingBaseES 支持哪些数据类型？ ​

Q: 如何创建一个自增主键？ ​

Q: 如何查看表的大小？ ​

KingBaseES 数据库与表

概述

数据库管理

1. 数据库概述

2. 数据库创建

基本语法

示例

3. 数据库查看

查看所有数据库

查看数据库详情

4. 数据库修改

修改数据库属性

5. 数据库删除

基本语法

示例

表管理

1. 表概述

2. 数据类型

数值类型

字符类型

日期/时间类型

布尔类型

二进制类型

枚举类型

复合类型

数组类型

JSON 类型

3. 表创建

基本语法

示例

4. 表结构查看

查看所有表

查看表结构

5. 表修改

添加列

修改列

删除列

添加约束

删除约束

6. 表删除

基本语法

示例

表结构设计

1. 设计原则

范式设计

反范式设计

2. 主键设计

选择原则

常见主键类型

示例

3. 索引设计

基本原则

示例

表空间与存储

1. 表空间分配

将表移动到不同表空间

2. 存储参数优化

常用存储参数

示例

表维护

1. VACUUM 操作

概述

基本语法

示例

2. ANALYZE 操作

概述

基本语法

示例

3. 表重建

概述

方法

最佳实践

1. 数据库设计最佳实践

2. 表设计最佳实践

3. 表管理最佳实践

4. 性能优化最佳实践

常见问题（FAQ）

Q: KingBaseES 支持哪些数据类型？

Q: 如何创建一个自增主键？

Q: 如何查看表的大小？