适用范围: 本手册覆盖87种数据库平台/应用类型，统一按41个分类整理 测试标识: 所有测试数据均包含
csdun标识，便于识别和清理 业务场景: 银行核心系统（客户、账户、交易流水、贷款、存款产品） 数据规模: 单库5-10GB，主要通过交易流水表大字段+大量行实现 库/表命名:csdu前缀，如csdu_customer、csdu_transaction编制日期: 2026-08-22
目录
通用银行业务表结构说明
所有关系型数据库统一创建以下5张表：
| 表名 | 说明 | 数据量策略 |
|---|---|---|
csdu_customer |
客户信息表 | 10万条，约100MB |
csdu_account |
账户信息表 | 20万条，约150MB |
csdu_transaction |
交易流水表（主力大表） | 3000万条+大字段，约5-10GB |
csdu_loan |
贷款信息表 | 5万条，约50MB |
csdu_deposit_product |
存款产品表 | 100条，约1MB |
csdu_transaction 大字段造数策略: trans_detail 字段填充 REPEAT('CSDUN_LARGE_DATA_', 500) + 序号，单条约10KB，3000万条约300GB原始文本，经压缩后实际占用5-10GB。
1. Oracle
1.1 技术原理
Oracle采用表空间(Tablespace)管理存储，数据以数据块(Block)为单位存储在数据文件中。核心组件包括：
-
实例(Instance): 内存结构(SGA) + 后台进程，是数据库的运行时环境
-
数据库(Database): 物理数据文件集合，与实例可以一对多（RAC）
-
归档模式(ARCHIVELOG): 开启后，redo日志在覆盖前会被归档保存，支持时间点恢复(PITR)
-
备份恢复: RMAN(Recovery Manager)是专用工具，支持全备、增量备、镜像备份
1.2 创建数据库/用户
-- 创建表空间
CREATE TABLESPACE csdu_ts DATAFILE '/u01/app/oracle/oradata/csdu_ts01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 20G;
-- 创建用户（Oracle使用Schema即用户）
CREATE USER csdu IDENTIFIED BY csdu_password DEFAULT TABLESPACE csdu_ts QUOTA UNLIMITED ON csdu_ts;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE, DBA TO csdu;
-- 切换用户
CONN csdu/csdu_password;
1.3 创建业务表
-- 客户信息表
CREATE TABLE csdu.csdu_customer (
cust_id VARCHAR2(32) PRIMARY KEY,
cust_name VARCHAR2(100),
id_type VARCHAR2(20),
id_no VARCHAR2(50),
phone VARCHAR2(30),
address VARCHAR2(500),
cust_type CHAR(1),
risk_level CHAR(1),
open_date DATE,
status CHAR(1),
branch_no VARCHAR2(20),
remark CLOB,
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 账户信息表
CREATE TABLE csdu.csdu_account (
acct_no VARCHAR2(32) PRIMARY KEY,
cust_id VARCHAR2(32),
acct_type CHAR(1),
currency CHAR(3),
balance NUMBER(18,2),
available_amt NUMBER(18,2),
open_date DATE,
close_date DATE,
status CHAR(1),
branch_no VARCHAR2(20),
passwd VARCHAR2(100),
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 交易流水表（主力大表）
CREATE TABLE csdu.csdu_transaction (
trans_id VARCHAR2(64) PRIMARY KEY,
acct_no VARCHAR2(32),
trans_type VARCHAR2(10),
trans_date DATE,
trans_time TIMESTAMP,
dr_cr_flag CHAR(1),
trans_amt NUMBER(18,2),
balance NUMBER(18,2),
opp_acct_no VARCHAR2(32),
opp_name VARCHAR2(100),
opp_bank_no VARCHAR2(20),
trans_channel VARCHAR2(20),
trans_summary VARCHAR2(200),
trans_detail CLOB,
teller_no VARCHAR2(20),
branch_no VARCHAR2(20),
check_flag CHAR(1),
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 贷款信息表
CREATE TABLE csdu.csdu_loan (
loan_no VARCHAR2(32) PRIMARY KEY,
cust_id VARCHAR2(32),
acct_no VARCHAR2(32),
loan_type VARCHAR2(20),
loan_amt NUMBER(18,2),
loan_balance NUMBER(18,2),
rate NUMBER(8,4),
start_date DATE,
end_date DATE,
term_months NUMBER(10),
repay_type CHAR(1),
status CHAR(1),
guarantee_type VARCHAR2(20),
purpose VARCHAR2(500),
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
-- 存款产品表
CREATE TABLE csdu.csdu_deposit_product (
prod_code VARCHAR2(20) PRIMARY KEY,
prod_name VARCHAR2(100),
prod_type CHAR(1),
min_amt NUMBER(18,2),
term_days NUMBER(10),
rate NUMBER(8,4),
auto_renew CHAR(1),
status CHAR(1),
create_date DATE,
csdun_tag VARCHAR2(20)
) TABLESPACE csdu_ts;
1.4 插入测试数据（5-10GB）
-- 插入客户数据
INSERT INTO csdu.csdu_customer
SELECT
'C' || LPAD(ROWNUM, 10, '0'),
'CSDUN客户_' || ROWNUM,
DECODE(MOD(ROWNUM,3),0,'身份证',1,'护照','军官证'),
'csdun_' || LPAD(ROWNUM, 18, '0'),
'138' || LPAD(MOD(ROWNUM,100000000), 8, '0'),
'CSDUN测试地址_' || ROWNUM || REPEAT('_地址补充', 20),
DECODE(MOD(ROWNUM,2),0,'1','2'),
DECODE(MOD(ROWNUM,5),0,'A',1,'B',2,'C',3,'D','E'),
SYSDATE - MOD(ROWNUM, 3650),
'1',
'BR' || LPAD(MOD(ROWNUM,999), 3, '0'),
REPEAT('CSDUN_REMARK_', 100),
'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 100000;
-- 插入账户数据
INSERT INTO csdu.csdu_account
SELECT
'6222' || LPAD(ROWNUM, 10, '0'),
'C' || LPAD(MOD(ROWNUM,100000), 10, '0'),
DECODE(MOD(ROWNUM,3),0,'1',1,'2','3'),
'CNY',
ROUND(DBMS_RANDOM.VALUE(0, 1000000), 2),
ROUND(DBMS_RANDOM.VALUE(0, 1000000), 2),
SYSDATE - MOD(ROWNUM, 3650),
NULL,
'1',
'BR' || LPAD(MOD(ROWNUM,999), 3, '0'),
'csdun_passwd_' || ROWNUM,
'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 200000;
-- 插入交易流水（主力大表，PL/SQL存储过程批量插入3000万条）
CREATE OR REPLACE PROCEDURE insert_csdu_trans AS
BEGIN
FOR i IN 1..30000000 LOOP
INSERT INTO csdu.csdu_transaction VALUES (
'TXN' || LPAD(i, 15, '0'),
'6222' || LPAD(MOD(i,200000), 10, '0'),
DECODE(MOD(i,5),0,'DEPOSIT',1,'WITHDRAW',2,'TRANSFER',3,'PAYMENT','FEE'),
SYSDATE - MOD(i,365),
SYSDATE - i/86400,
DECODE(MOD(i,2),0,'1','2'),
ROUND(DBMS_RANDOM.VALUE(1,100000),2),
ROUND(DBMS_RANDOM.VALUE(1,500000),2),
'6222' || LPAD(MOD(i+1,200000), 10, '0'),
'CSDUN测试对方户名_' || i,
'104' || LPAD(MOD(i,10000), 8, '0'),
DECODE(MOD(i,4),0,'COUNTER',1,'ATM',2,'MOBILE','WEB'),
'CSDUN银行业务测试交易摘要-' || i,
REPEAT('CSDUN_LARGE_DATA_', 500) || i,
'T' || LPAD(MOD(i,9999), 4, '0'),
'BR' || LPAD(MOD(i,999), 3, '0'),
'0',
'csdun_' || i
);
IF MOD(i, 10000) = 0 THEN COMMIT; END IF;
END LOOP;
COMMIT;
END;
/
EXEC insert_csdu_trans;
-- 插入贷款数据
INSERT INTO csdu.csdu_loan
SELECT
'L' || LPAD(ROWNUM, 10, '0'),
'C' || LPAD(MOD(ROWNUM,100000), 10, '0'),
'6222' || LPAD(MOD(ROWNUM,200000), 10, '0'),
DECODE(MOD(ROWNUM,4),0,'个人消费贷',1,'住房按揭',2,'企业经营贷','小微企业贷'),
ROUND(DBMS_RANDOM.VALUE(10000, 1000000), 2),
ROUND(DBMS_RANDOM.VALUE(1000, 500000), 2),
ROUND(DBMS_RANDOM.VALUE(0.03, 0.06), 4),
SYSDATE - MOD(ROWNUM, 3650),
SYSDATE + MOD(ROWNUM, 3650),
MOD(ROWNUM, 360),
DECODE(MOD(ROWNUM,3),0,'1',1,'2','3'),
DECODE(MOD(ROWNUM,3),0,'1',1,'2','3'),
DECODE(MOD(ROWNUM,4),0,'信用',1,'抵押',2,'质押','保证'),
'CSDUN测试贷款用途_' || ROWNUM,
'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 50000;
-- 插入存款产品
INSERT INTO csdu.csdu_deposit_product VALUES
('P001', 'CSDUN活期存款', '1', 0.00, 0, 0.0030, '1', '1', SYSDATE, 'csdun_001'),
('P002', 'CSDUN三个月定期', '2', 50000.00, 90, 0.0145, '1', '1', SYSDATE, 'csdun_002'),
('P003', 'CSDUN一年定期', '2', 50000.00, 365, 0.0175, '1', '1', SYSDATE, 'csdun_003'),
('P004', 'CSDUN三年大额存单', '3', 200000.00, 1095, 0.0285, '1', '1', SYSDATE, 'csdun_004'),
('P005', 'CSDUN结构性存款', '4', 100000.00, 180, 0.0350, '0', '1', SYSDATE, 'csdun_005');
1.5 数据库状态查询
-- 实例状态
SELECT instance_name, status, database_status FROM v$instance;
-- 数据库打开模式、归档模式
SELECT name, open_mode, log_mode FROM v$database;
-- 会话状态
SELECT sid, serial#, username, status, machine, program FROM v$session WHERE username='CSDU';
-- 表空间使用率
SELECT tablespace_name, ROUND(used_space*8192/1024/1024/1024,2) used_gb,
ROUND(tablespace_size*8192/1024/1024/1024,2) total_gb,
ROUND((used_space/tablespace_size)*100,2) pct_used
FROM dba_tablespace_usage_metrics WHERE tablespace_name='CSDU_TS';
1.6 归档查询
-- 是否开启归档
SELECT log_mode FROM v$database;
-- 归档日志列表
ARCHIVE LOG LIST;
-- 归档目的地
SELECT destination, status, space_limit/1024/1024/1024 space_limit_gb,
space_used/1024/1024/1024 space_used_gb
FROM v$archive_dest WHERE status != 'INACTIVE';
1.7 数据量统计
-- CSDU用户下所有表大小
SELECT table_name, ROUND(SUM(bytes)/1024/1024/1024,2) gb
FROM dba_segments WHERE owner='CSDU' GROUP BY table_name ORDER BY gb DESC;
-- 交易流水表记录数
SELECT COUNT(*) FROM csdu.csdu_transaction;
-- 按天统计交易量
SELECT trans_date, COUNT(*) FROM csdu.csdu_transaction GROUP BY trans_date ORDER BY trans_date;
1.8 性能查询
-- Top 10慢SQL
SELECT sql_id, elapsed_time/1000000 sec, sql_text
FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
-- 交易流水表全表扫描性能
SET TIMING ON;
SELECT COUNT(*) FROM csdu.csdu_transaction WHERE trans_detail LIKE '%CSDUN_LARGE_DATA_1000000%';
SET TIMING OFF;
1.9 删除/清理语句
-- 方式1：删除测试数据（保留表结构，可重复测试）
DELETE FROM csdu.csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_loan WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_deposit_product WHERE csdun_tag LIKE 'csdun_%';
COMMIT;
-- 方式2：截断表（快速清空，不可回滚）
TRUNCATE TABLE csdu.csdu_transaction;
TRUNCATE TABLE csdu.csdu_account;
TRUNCATE TABLE csdu.csdu_customer;
TRUNCATE TABLE csdu.csdu_loan;
TRUNCATE TABLE csdu.csdu_deposit_product;
-- 方式3：彻底删除（删除用户和表空间）
DROP USER csdu CASCADE;
DROP TABLESPACE csdu_ts INCLUDING CONTENTS AND DATAFILES;
1.10 备份恢复
-- RMAN联机备份
RMAN> BACKUP TABLESPACE csdu_ts FORMAT '/backup/csdu_ts_%U.bak';
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
-- 恢复
RMAN> STARTUP MOUNT;
RMAN> RESTORE TABLESPACE csdu_ts;
RMAN> RECOVER TABLESPACE csdu_ts;
RMAN> ALTER DATABASE OPEN;
2. SQL Server
2.1 技术原理
SQL Server采用页(Page, 8KB)为基本存储单位，数据组织在区(Extent)中：
-
数据库文件: MDF（主数据文件）+ NDF（辅助数据文件）+ LDF（日志文件）
-
恢复模式: 简单(Simple)、完整(Full)、大容量日志(Bulk-Logged)
-
高可用: AlwaysOn可用性组、故障转移群集、日志传送、数据库镜像
-
备份: 支持完整备份、差异备份、事务日志备份，形成备份链
2.2 创建数据库
CREATE DATABASE csdu_db
ON PRIMARY (NAME='csdu_db', FILENAME='D:\SQLData\csdu_db.mdf', SIZE=10GB, FILEGROWTH=1GB)
LOG ON (NAME='csdu_db_log', FILENAME='D:\SQLData\csdu_db.ldf', SIZE=2GB, FILEGROWTH=512MB);
GO
ALTER DATABASE csdu_db SET RECOVERY FULL;
GO
USE csdu_db;
GO
2.3 创建业务表
CREATE TABLE dbo.csdu_customer (
cust_id NVARCHAR(32) PRIMARY KEY,
cust_name NVARCHAR(100), id_type NVARCHAR(20), id_no NVARCHAR(50),
phone NVARCHAR(30), address NVARCHAR(500), cust_type NCHAR(1),
risk_level NCHAR(1), open_date DATE, status NCHAR(1),
branch_no NVARCHAR(20), remark NVARCHAR(MAX), csdun_tag NVARCHAR(20)
);
CREATE TABLE dbo.csdu_account (
acct_no NVARCHAR(32) PRIMARY KEY, cust_id NVARCHAR(32),
acct_type NCHAR(1), currency NCHAR(3), balance DECIMAL(18,2),
available_amt DECIMAL(18,2), open_date DATE, close_date DATE,
status NCHAR(1), branch_no NVARCHAR(20), passwd NVARCHAR(100),
csdun_tag NVARCHAR(20)
);
CREATE TABLE dbo.csdu_transaction (
trans_id NVARCHAR(64) PRIMARY KEY, acct_no NVARCHAR(32),
trans_type NVARCHAR(10), trans_date DATE, trans_time DATETIME2,
dr_cr_flag NCHAR(1), trans_amt DECIMAL(18,2), balance DECIMAL(18,2),
opp_acct_no NVARCHAR(32), opp_name NVARCHAR(100), opp_bank_no NVARCHAR(20),
trans_channel NVARCHAR(20), trans_summary NVARCHAR(200),
trans_detail NVARCHAR(MAX), teller_no NVARCHAR(20), branch_no NVARCHAR(20),
check_flag NCHAR(1), csdun_tag NVARCHAR(20)
);
CREATE TABLE dbo.csdu_loan (
loan_no NVARCHAR(32) PRIMARY KEY, cust_id NVARCHAR(32), acct_no NVARCHAR(32),
loan_type NVARCHAR(20), loan_amt DECIMAL(18,2), loan_balance DECIMAL(18,2),
rate DECIMAL(8,4), start_date DATE, end_date DATE, term_months INT,
repay_type NCHAR(1), status NCHAR(1), guarantee_type NVARCHAR(20),
purpose NVARCHAR(500), csdun_tag NVARCHAR(20)
);
CREATE TABLE dbo.csdu_deposit_product (
prod_code NVARCHAR(20) PRIMARY KEY, prod_name NVARCHAR(100),
prod_type NCHAR(1), min_amt DECIMAL(18,2), term_days INT,
rate DECIMAL(8,4), auto_renew NCHAR(1), status NCHAR(1),
create_date DATE, csdun_tag NVARCHAR(20)
);
2.4 插入测试数据（5-10GB）
-- 插入客户数据
INSERT INTO dbo.csdu_customer (cust_id, cust_name, id_type, id_no, phone, address, cust_type, risk_level, open_date, status, branch_no, remark, csdun_tag)
SELECT TOP 100000
'C' + RIGHT('0000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 10),
'CSDUN客户_' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR),
CASE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3) WHEN 0 THEN '身份证' WHEN 1 THEN '护照' ELSE '军官证' END,
'csdun_' + RIGHT('000000000000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 18),
'138' + RIGHT('00000000' + CAST(ABS(CHECKSUM(NEWID())) % 100000000 AS VARCHAR), 8),
REPLICATE('CSDUN测试地址_', 50),
CASE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 2) WHEN 0 THEN '1' ELSE '2' END,
CASE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5) WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C' WHEN 3 THEN 'D' ELSE 'E' END,
DATEADD(DAY, -(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3650), GETDATE()),
'1', 'BR' + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 999 AS VARCHAR), 3),
REPLICATE('CSDUN_REMARK_', 100), 'csdun_' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR)
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
-- 插入交易流水（循环批量插入3000万条）
DECLARE @i INT = 0;
WHILE @i < 30000000
BEGIN
INSERT INTO dbo.csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
SELECT TOP 100000
'TXN' + RIGHT('000000000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i AS VARCHAR), 15),
'6222' + RIGHT('0000000000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 200000 AS VARCHAR), 10),
CASE ((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 5) WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATEADD(DAY, -((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 365), GETDATE()),
DATEADD(SECOND, -((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i)), GETDATE()),
CASE ((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 2) WHEN 0 THEN '1' ELSE '2' END,
ROUND(RAND(CHECKSUM(NEWID())) * 100000, 2), ROUND(RAND(CHECKSUM(NEWID())) * 500000, 2),
'6222' + RIGHT('0000000000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i + 1) % 200000 AS VARCHAR), 10),
'CSDUN测试对方户名_' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR),
'104' + RIGHT('00000000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 10000 AS VARCHAR), 8),
CASE ((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 4) WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR),
REPLICATE('CSDUN_LARGE_DATA_', 500) + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR),
'T' + RIGHT('0000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 9999 AS VARCHAR), 4),
'BR' + RIGHT('000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 999 AS VARCHAR), 3),
'0', 'csdun_' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR)
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
SET @i = @i + 100000;
IF @i % 1000000 = 0 PRINT 'Inserted ' + CAST(@i AS VARCHAR);
END;
2.5 状态查询
SELECT name, state_desc, recovery_model_desc FROM sys.databases WHERE name='csdu_db';
SELECT DB_NAME(dbid) AS db, COUNT(*) AS connections FROM sys.sysprocesses WHERE DB_NAME(dbid)='csdu_db' GROUP BY dbid;
SELECT TOP 10 wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
2.6 归档/日志查询
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name='csdu_db';
SELECT DB_NAME(database_id) AS db, CAST(SUM(size * 8.0 / 1024 / 1024) AS DECIMAL(10,2)) AS log_gb
FROM sys.master_files WHERE type_desc='LOG' AND database_id=DB_ID('csdu_db') GROUP BY database_id;
2.7 数据量统计
SELECT t.name, CAST(SUM(a.total_pages) * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS total_gb,
CAST(SUM(a.used_pages) * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS used_gb
FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name LIKE 'csdu_%' GROUP BY t.name ORDER BY total_gb DESC;
SELECT COUNT(*) FROM dbo.csdu_transaction;
2.8 性能查询
SELECT TOP 10 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS sql_text,
qs.total_elapsed_time/1000 AS total_ms, qs.total_logical_reads
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time DESC;
2.9 删除语句
DELETE FROM dbo.csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM dbo.csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM dbo.csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM dbo.csdu_loan WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM dbo.csdu_deposit_product WHERE csdun_tag LIKE 'csdun_%';
USE master;
ALTER DATABASE csdu_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE csdu_db;
2.10 备份恢复
BACKUP DATABASE csdu_db TO DISK='D:\Backup\csdu_db.bak' WITH COMPRESSION, FORMAT;
BACKUP LOG csdu_db TO DISK='D:\Backup\csdu_db_log.trn' WITH COMPRESSION;
RESTORE DATABASE csdu_db FROM DISK='D:\Backup\csdu_db.bak' WITH NORECOVERY;
RESTORE LOG csdu_db FROM DISK='D:\Backup\csdu_db_log.trn' WITH RECOVERY;
3. MySQL / MySQL主备集群 / MySQL逻辑备份
3.1 技术原理
MySQL采用插件式存储引擎架构，最常用的是InnoDB：
-
InnoDB: 支持事务(ACID)、行级锁、MVCC多版本并发控制
-
binlog: 二进制日志记录所有数据变更，用于复制和点时间恢复
-
redo log: InnoDB特有，保证事务持久性
-
备份工具: mysqldump(逻辑)、xtrabackup(物理热备)
3.2 创建数据库
CREATE DATABASE csdu_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE csdu_db;
3.3 创建业务表
CREATE TABLE csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY COMMENT '客户编号',
cust_name VARCHAR(100) COMMENT '客户姓名',
id_type VARCHAR(20) COMMENT '证件类型',
id_no VARCHAR(50) COMMENT '证件号码',
phone VARCHAR(30) COMMENT '联系电话',
address VARCHAR(500) COMMENT '联系地址',
cust_type CHAR(1) COMMENT '客户类型(1-个人 2-企业)',
risk_level CHAR(1) COMMENT '风险等级',
open_date DATE COMMENT '开户日期',
status CHAR(1) COMMENT '状态',
branch_no VARCHAR(20) COMMENT '所属网点',
remark LONGTEXT COMMENT '备注',
csdun_tag VARCHAR(20) COMMENT '测试标识'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户信息表';
CREATE TABLE csdu_account (
acct_no VARCHAR(32) PRIMARY KEY COMMENT '账号',
cust_id VARCHAR(32) COMMENT '客户编号',
acct_type CHAR(1) COMMENT '账户类型(1-活期 2-定期 3-理财)',
currency CHAR(3) COMMENT '币种',
balance DECIMAL(18,2) COMMENT '余额',
available_amt DECIMAL(18,2) COMMENT '可用余额',
open_date DATE COMMENT '开户日期',
close_date DATE COMMENT '销户日期',
status CHAR(1) COMMENT '状态(1-正常 2-冻结 3-销户)',
branch_no VARCHAR(20) COMMENT '开户网点',
passwd VARCHAR(100) COMMENT '密码',
csdun_tag VARCHAR(20) COMMENT '测试标识'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户信息表';
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY COMMENT '交易流水号',
acct_no VARCHAR(32) COMMENT '账号',
trans_type VARCHAR(10) COMMENT '交易类型',
trans_date DATE COMMENT '交易日期',
trans_time DATETIME COMMENT '交易时间',
dr_cr_flag CHAR(1) COMMENT '借贷标志(1-借 2-贷)',
trans_amt DECIMAL(18,2) COMMENT '交易金额',
balance DECIMAL(18,2) COMMENT '交易后余额',
opp_acct_no VARCHAR(32) COMMENT '对方账号',
opp_name VARCHAR(100) COMMENT '对方户名',
opp_bank_no VARCHAR(20) COMMENT '对方行号',
trans_channel VARCHAR(20) COMMENT '交易渠道',
trans_summary VARCHAR(200) COMMENT '交易摘要',
trans_detail LONGTEXT COMMENT '交易详情(大字段)',
teller_no VARCHAR(20) COMMENT '柜员号',
branch_no VARCHAR(20) COMMENT '交易网点',
check_flag CHAR(1) COMMENT '对账标志',
csdun_tag VARCHAR(20) COMMENT '测试标识',
INDEX idx_acct_no (acct_no),
INDEX idx_trans_date (trans_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易流水表';
CREATE TABLE csdu_loan (
loan_no VARCHAR(32) PRIMARY KEY COMMENT '贷款编号',
cust_id VARCHAR(32) COMMENT '客户编号',
acct_no VARCHAR(32) COMMENT '放款账号',
loan_type VARCHAR(20) COMMENT '贷款类型',
loan_amt DECIMAL(18,2) COMMENT '贷款金额',
loan_balance DECIMAL(18,2) COMMENT '贷款余额',
rate DECIMAL(8,4) COMMENT '利率',
start_date DATE COMMENT '起息日',
end_date DATE COMMENT '到期日',
term_months INT COMMENT '期限(月)',
repay_type CHAR(1) COMMENT '还款方式',
status CHAR(1) COMMENT '状态',
guarantee_type VARCHAR(20) COMMENT '担保方式',
purpose VARCHAR(500) COMMENT '贷款用途',
csdun_tag VARCHAR(20) COMMENT '测试标识'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='贷款信息表';
CREATE TABLE csdu_deposit_product (
prod_code VARCHAR(20) PRIMARY KEY COMMENT '产品代码',
prod_name VARCHAR(100) COMMENT '产品名称',
prod_type CHAR(1) COMMENT '产品类型',
min_amt DECIMAL(18,2) COMMENT '起存金额',
term_days INT COMMENT '期限(天)',
rate DECIMAL(8,4) COMMENT '利率',
auto_renew CHAR(1) COMMENT '自动转存',
status CHAR(1) COMMENT '状态',
create_date DATE COMMENT '创建日期',
csdun_tag VARCHAR(20) COMMENT '测试标识'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='存款产品表';
3.4 插入测试数据（5-10GB）
-- 插入客户数据
INSERT INTO csdu_customer
SELECT
CONCAT('C', LPAD(seq, 10, '0')),
CONCAT('CSDUN客户_', seq),
CASE seq % 3 WHEN 0 THEN '身份证' WHEN 1 THEN '护照' ELSE '军官证' END,
CONCAT('csdun_', LPAD(seq, 18, '0')),
CONCAT('138', LPAD(seq % 100000000, 8, '0')),
REPEAT(CONCAT('CSDUN测试地址_', seq, ' '), 20),
CASE seq % 2 WHEN 0 THEN '1' ELSE '2' END,
CASE seq % 5 WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C' WHEN 3 THEN 'D' ELSE 'E' END,
DATE_SUB(CURDATE(), INTERVAL (seq % 3650) DAY),
'1',
CONCAT('BR', LPAD(seq % 999, 3, '0')),
REPEAT('CSDUN_REMARK_', 100),
CONCAT('csdun_', seq)
FROM (SELECT @row:=@row+1 AS seq FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t4,
(SELECT @row:=0) init) sub WHERE seq <= 100000;
-- 插入交易流水存储过程（主力大表）
DELIMITER //
CREATE PROCEDURE insert_csdu_trans()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE batch_size INT DEFAULT 10000;
WHILE i < 30000000 DO
INSERT INTO csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
SELECT
CONCAT('TXN', LPAD(i + seq, 15, '0')),
CONCAT('6222', LPAD((i + seq) % 200000, 10, '0')),
CASE (i + seq) % 5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATE_SUB(CURDATE(), INTERVAL ((i + seq) % 365) DAY),
DATE_SUB(NOW(), INTERVAL (i + seq) SECOND),
CASE (i + seq) % 2 WHEN 0 THEN '1' ELSE '2' END,
ROUND(RAND() * 100000, 2), ROUND(RAND() * 500000, 2),
CONCAT('6222', LPAD((i + seq + 1) % 200000, 10, '0')),
CONCAT('CSDUN测试对方户名_', i + seq),
CONCAT('104', LPAD((i + seq) % 10000, 8, '0')),
CASE (i + seq) % 4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
CONCAT('CSDUN银行业务测试交易摘要-', i + seq),
REPEAT(CONCAT('CSDUN_LARGE_DATA_', i + seq, ' '), 500),
CONCAT('T', LPAD((i + seq) % 9999, 4, '0')),
CONCAT('BR', LPAD((i + seq) % 999, 3, '0')),
'0', CONCAT('csdun_', i + seq)
FROM (SELECT @s:=@s+1 AS seq FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
(SELECT @s:=0) init) sub WHERE seq < batch_size;
SET i = i + batch_size;
IF i % 1000000 = 0 THEN SELECT CONCAT('Inserted ', i, ' rows') AS progress; END IF;
END WHILE;
END //
DELIMITER ;
CALL insert_csdu_trans();
3.5 状态查询
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;
SELECT * FROM performance_schema.threads WHERE NAME LIKE '%sql%' LIMIT 10;
3.6 归档查询
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;
SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'binlog_format';
3.7 数据量统计
SELECT table_name, ROUND(data_length/1024/1024/1024, 2) AS data_gb,
ROUND(index_length/1024/1024/1024, 2) AS idx_gb,
ROUND((data_length+index_length)/1024/1024/1024, 2) AS total_gb
FROM information_schema.tables WHERE table_schema='csdu_db' ORDER BY total_gb DESC;
SELECT COUNT(*) FROM csdu_transaction;
SELECT trans_date, COUNT(*) FROM csdu_transaction GROUP BY trans_date ORDER BY trans_date;
3.8 性能查询
SELECT DIGEST_TEXT, COUNT_STAR, ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;
SELECT SQL_NO_CACHE COUNT(*) FROM csdu_transaction WHERE trans_detail LIKE '%CSDUN_LARGE_DATA_1000000%';
3.9 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_loan WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_deposit_product WHERE csdun_tag LIKE 'csdun_%';
DROP DATABASE IF EXISTS csdu_db;
3.10 备份恢复
# 逻辑备份
mysqldump -u root -p csdu_db > /backup/csdu_db_backup.sql
# 物理热备
xtrabackup --backup --target-dir=/backup/csdu/
# 逻辑恢复
mysql -u root -p csdu_db < /backup/csdu_db_backup.sql
# 物理恢复
xtrabackup --prepare --target-dir=/backup/csdu/
xtrabackup --copy-back --target-dir=/backup/csdu/
4. MariaDB / MariaDB主备集群
MariaDB是MySQL的分支，完全兼容MySQL协议和语法。 SQL语法与MySQL完全一致，请参考第3章MySQL的创建表、插入数据、查询、删除SQL。
4.1 MariaDB特有备份恢复
# 物理热备
mariabackup --backup --target-dir=/backup/mariadb/ --user=root --password=xxx
mariabackup --prepare --target-dir=/backup/mariadb/
mariabackup --copy-back --target-dir=/backup/mariadb/
# Galera集群状态
SHOW STATUS LIKE 'wsrep%';
5. PostgreSQL / PostgreSQL集群 / PostgreSQL逻辑备份
5.1 技术原理
PostgreSQL是先进的开源对象关系型数据库：
-
MVCC: 多版本并发控制，读不阻塞写，写不阻塞读
-
WAL(预写日志): 所有修改先写日志再写数据，保证崩溃恢复
-
归档: 开启archive_mode后，WAL文件会被归档到指定目录
-
备份: pg_dump(逻辑)、pg_basebackup(物理)、PITR(点时间恢复)
5.2 创建数据库
CREATE DATABASE csdu_db WITH ENCODING='UTF8' LC_COLLATE='zh_CN.UTF-8' LC_CTYPE='zh_CN.UTF-8';
\c csdu_db;
5.3 创建业务表
CREATE TABLE csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY, cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR(20),
remark TEXT, csdun_tag VARCHAR(20)
);
CREATE TABLE csdu_account (
acct_no VARCHAR(32) PRIMARY KEY, cust_id VARCHAR(32), acct_type CHAR(1),
currency CHAR(3), balance NUMERIC(18,2), available_amt NUMERIC(18,2),
open_date DATE, close_date DATE, status CHAR(1), branch_no VARCHAR(20),
passwd VARCHAR(100), csdun_tag VARCHAR(20)
);
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag CHAR(1), trans_amt NUMERIC(18,2),
balance NUMERIC(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail TEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20)
);
CREATE INDEX idx_trans_acct ON csdu_transaction(acct_no);
CREATE INDEX idx_trans_date ON csdu_transaction(trans_date);
CREATE TABLE csdu_loan (
loan_no VARCHAR(32) PRIMARY KEY, cust_id VARCHAR(32), acct_no VARCHAR(32),
loan_type VARCHAR(20), loan_amt NUMERIC(18,2), loan_balance NUMERIC(18,2),
rate NUMERIC(8,4), start_date DATE, end_date DATE, term_months INTEGER,
repay_type CHAR(1), status CHAR(1), guarantee_type VARCHAR(20),
purpose VARCHAR(500), csdun_tag VARCHAR(20)
);
CREATE TABLE csdu_deposit_product (
prod_code VARCHAR(20) PRIMARY KEY, prod_name VARCHAR(100), prod_type CHAR(1),
min_amt NUMERIC(18,2), term_days INTEGER, rate NUMERIC(8,4), auto_renew CHAR(1),
status CHAR(1), create_date DATE, csdun_tag VARCHAR(20)
);
5.4 插入测试数据（5-10GB）
INSERT INTO csdu_customer
SELECT 'C' || LPAD(i::text, 10, '0'), 'CSDUN客户_' || i,
CASE i % 3 WHEN 0 THEN '身份证' WHEN 1 THEN '护照' ELSE '军官证' END,
'csdun_' || LPAD(i::text, 18, '0'), '138' || LPAD((i % 100000000)::text, 8, '0'),
REPEAT('CSDUN测试地址_' || i || ' ', 20),
CASE i % 2 WHEN 0 THEN '1' ELSE '2' END,
CASE i % 5 WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C' WHEN 3 THEN 'D' ELSE 'E' END,
CURRENT_DATE - (i % 3650), '1', 'BR' || LPAD((i % 999)::text, 3, '0'),
REPEAT('CSDUN_REMARK_', 100), 'csdun_' || i
FROM generate_series(1, 100000) AS i;
INSERT INTO csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
SELECT 'TXN' || LPAD(i::text, 15, '0'), '6222' || LPAD((i % 200000)::text, 10, '0'),
CASE i % 5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
CURRENT_DATE - (i % 365), CURRENT_TIMESTAMP - (i || ' seconds')::interval,
CASE i % 2 WHEN 0 THEN '1' ELSE '2' END,
(random() * 100000)::numeric(18,2), (random() * 500000)::numeric(18,2),
'6222' || LPAD(((i + 1) % 200000)::text, 10, '0'), 'CSDUN测试对方户名_' || i,
'104' || LPAD((i % 10000)::text, 8, '0'),
CASE i % 4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' || i, REPEAT('CSDUN_LARGE_DATA_' || i || ' ', 500),
'T' || LPAD((i % 9999)::text, 4, '0'), 'BR' || LPAD((i % 999)::text, 3, '0'),
'0', 'csdun_' || i
FROM generate_series(1, 30000000) AS i;
5.5 状态查询
SELECT pg_size_pretty(pg_database_size('csdu_db'));
SELECT * FROM pg_stat_activity WHERE datname='csdu_db';
SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit
FROM pg_stat_database WHERE datname='csdu_db';
5.6 归档查询
SHOW archive_mode;
SHOW archive_command;
SELECT * FROM pg_stat_archiver;
5.7 数据量统计
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables WHERE relname LIKE 'csdu_%' ORDER BY pg_total_relation_size(relid) DESC;
SELECT COUNT(*) FROM csdu_transaction;
SELECT trans_date, COUNT(*) FROM csdu_transaction GROUP BY trans_date ORDER BY trans_date;
5.8 性能查询
SELECT query, calls, ROUND(mean_exec_time::numeric, 2) AS avg_ms, total_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM csdu_transaction WHERE trans_detail LIKE '%CSDUN_LARGE_DATA_1000000%';
5.9 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_loan WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_deposit_product WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE IF EXISTS csdu_transaction, csdu_account, csdu_customer, csdu_loan, csdu_deposit_product CASCADE;
DROP DATABASE csdu_db;
5.10 备份恢复
pg_dump -h localhost -U postgres -d csdu_db -F c -f /backup/csdu_db.dump
pg_basebackup -h localhost -U postgres -D /backup/csdu_base -Ft -z -P
pg_restore -h localhost -U postgres -d csdu_db /backup/csdu_db.dump
6. DB2 / DB2 DPF集群 / DB2 HADR集群 / DB2 HACMP集群
6.1 技术原理
IBM DB2是商业关系型数据库：
-
表空间: SMS(系统管理)和DMS(数据库管理)两种模式
-
日志: 循环日志(默认)和归档日志(需配置LOGARCHMETH1)
-
备份: 支持在线/离线备份，增量备份，前滚恢复(Rollforward)
-
DPF: 数据库分区特性，支持MPP大规模并行处理
6.2 创建数据库
CREATE DATABASE csdu_db ON /db2data USING CODESET UTF-8 TERRITORY CN PAGESIZE 32 K;
CONNECT TO csdu_db;
CREATE BUFFERPOOL csdu_bp SIZE 1000 PAGESIZE 32K;
CREATE TABLESPACE csdu_ts PAGESIZE 32K MANAGED BY DATABASE USING (FILE '/db2data/csdu_ts.dbf' 10G) BUFFERPOOL csdu_bp;
6.3 创建业务表
CREATE TABLE csdu.csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY, cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR(20),
remark CLOB(10M), csdun_tag VARCHAR(20)
) IN csdu_ts;
CREATE TABLE csdu.csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail CLOB(10M), teller_no VARCHAR(20), branch_no VARCHAR(20),
check_flag CHAR(1), csdun_tag VARCHAR(20)
) IN csdu_ts;
6.4 插入测试数据
CREATE PROCEDURE insert_csdu_trans()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 30000000 DO
INSERT INTO csdu.csdu_transaction VALUES (
'TXN' || RIGHT('000000000000000' || CAST(i AS VARCHAR), 15),
'6222' || RIGHT('0000000000' || CAST(MOD(i, 200000) AS VARCHAR), 10),
CASE MOD(i, 5) WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
CURRENT DATE - MOD(i, 365) DAYS, CURRENT TIMESTAMP - i SECONDS,
CASE MOD(i, 2) WHEN 0 THEN '1' ELSE '2' END,
DECIMAL(RAND() * 100000, 18, 2), DECIMAL(RAND() * 500000, 18, 2),
'6222' || RIGHT('0000000000' || CAST(MOD(i + 1, 200000) AS VARCHAR), 10),
'CSDUN测试对方户名_' || CAST(i AS VARCHAR),
'104' || RIGHT('00000000' || CAST(MOD(i, 10000) AS VARCHAR), 8),
CASE MOD(i, 4) WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' || CAST(i AS VARCHAR),
REPEAT('CSDUN_LARGE_DATA_', 500) || CAST(i AS VARCHAR),
'T' || RIGHT('0000' || CAST(MOD(i, 9999) AS VARCHAR), 4),
'BR' || RIGHT('000' || CAST(MOD(i, 999) AS VARCHAR), 3), '0', 'csdun_' || CAST(i AS VARCHAR));
SET i = i + 1;
IF MOD(i, 10000) = 0 THEN COMMIT; END IF;
END WHILE;
END;
CALL insert_csdu_trans();
6.5 状态查询
db2 get dbm cfg
db2 get db cfg for csdu_db
db2 list applications
db2pd -db csdu_db -tab
6.6 归档查询
db2 get db cfg for csdu_db | grep -i logarch
6.7 数据量统计
SELECT tabname, SUM(data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size)/1024/1024/1024 AS gb
FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('CSDU', '')) GROUP BY tabname;
SELECT COUNT(*) FROM csdu.csdu_transaction;
6.8 性能查询
SELECT stmt_text, total_exec_time, num_executions
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2))
ORDER BY total_exec_time DESC FETCH FIRST 10 ROWS ONLY;
6.9 删除语句
DROP TABLE csdu.csdu_transaction;
DROP TABLE csdu.csdu_account;
DROP TABLE csdu.csdu_customer;
DROP TABLE csdu.csdu_loan;
DROP TABLE csdu.csdu_deposit_product;
DROP TABLESPACE csdu_ts;
CONNECT RESET;
DROP DATABASE csdu_db;
6.10 备份恢复
db2 backup db csdu_db online to /backup/ compress
db2 backup db csdu_db online incremental to /backup/ compress
db2 restore db csdu_db from /backup/ taken at <timestamp>
db2 rollforward db csdu_db to end of logs and stop
7. 达梦(Dameng) / 达梦DataWatch / 达梦DSC / 达梦DPC / 达梦RWC / 达梦逻辑备份
7.1 技术原理
达梦数据库是国产自主可控的关系型数据库：
-
兼容Oracle: 语法、数据类型、PL/SQL高度兼容Oracle
-
存储结构: 表空间 -> 数据文件 -> 簇 -> 页(默认8KB)
-
归档: 支持本地归档、实时归档、即时归档、异步归档
-
集群: 支持DataWatch(主备)、DSC(共享存储集群)、DPC(分布式)
7.2 创建数据库/表空间/用户
CREATE TABLESPACE csdu_ts DATAFILE 'csdu_ts.dbf' SIZE 10240 AUTOEXTEND ON NEXT 1024 MAXSIZE 20480;
CREATE USER csdu IDENTIFIED BY csdu_password DEFAULT TABLESPACE csdu_ts;
GRANT DBA TO csdu;
CONN csdu/csdu_password;
7.3 创建业务表
CREATE TABLE csdu.csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY, cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR(20),
remark TEXT, csdun_tag VARCHAR(20)
) STORAGE (ON csdu_ts);
CREATE TABLE csdu.csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail TEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20)
) STORAGE (ON csdu_ts);
CREATE INDEX idx_trans_acct ON csdu.csdu_transaction(acct_no);
CREATE INDEX idx_trans_date ON csdu.csdu_transaction(trans_date);
7.4 插入测试数据（5-10GB）
INSERT INTO csdu.csdu_customer
SELECT 'C' || LPAD(ROWNUM, 10, '0'), 'CSDUN客户_' || ROWNUM,
DECODE(MOD(ROWNUM,3),0,'身份证',1,'护照','军官证'),
'csdun_' || LPAD(ROWNUM, 18, '0'), '138' || LPAD(MOD(ROWNUM,100000000), 8, '0'),
'CSDUN测试地址_' || ROWNUM || REPEAT('_地址补充', 20),
DECODE(MOD(ROWNUM,2),0,'1','2'),
DECODE(MOD(ROWNUM,5),0,'A',1,'B',2,'C',3,'D','E'),
SYSDATE - MOD(ROWNUM, 3650), '1', 'BR' || LPAD(MOD(ROWNUM,999), 3, '0'),
REPEAT('CSDUN_REMARK_', 100), 'csdun_' || ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 100000;
CREATE OR REPLACE PROCEDURE insert_csdu_trans AS
BEGIN
FOR i IN 1..30000000 LOOP
INSERT INTO csdu.csdu_transaction VALUES (
'TXN' || LPAD(i, 15, '0'), '6222' || LPAD(MOD(i,200000), 10, '0'),
DECODE(MOD(i,5),0,'DEPOSIT',1,'WITHDRAW',2,'TRANSFER',3,'PAYMENT','FEE'),
SYSDATE - MOD(i,365), SYSDATE - i/86400,
DECODE(MOD(i,2),0,'1','2'), ROUND(DBMS_RANDOM.VALUE(1,100000),2),
ROUND(DBMS_RANDOM.VALUE(1,500000),2),
'6222' || LPAD(MOD(i+1,200000), 10, '0'), 'CSDUN测试对方户名_' || i,
'104' || LPAD(MOD(i,10000), 8, '0'),
DECODE(MOD(i,4),0,'COUNTER',1,'ATM',2,'MOBILE','WEB'),
'CSDUN银行业务测试交易摘要-' || i, REPEAT('CSDUN_LARGE_DATA_', 500) || i,
'T' || LPAD(MOD(i,9999), 4, '0'), 'BR' || LPAD(MOD(i,999), 3, '0'), '0', 'csdun_' || i);
IF MOD(i, 10000) = 0 THEN COMMIT; END IF;
END LOOP;
COMMIT;
END;
/
EXEC insert_csdu_trans;
7.5 状态查询
SELECT instance_name, status$ AS status, host_name FROM v$instance;
SELECT name, create_time, arch_mode, open_status$ AS open_status FROM v$database;
SELECT sess_id, sql_id, state, seq_no, create_time, clnt_ip FROM v$sessions WHERE state='ACTIVE';
SELECT t.name AS tablespace_name, d.total_size*page()/1024/1024/1024 AS total_gb,
d.free_size*page()/1024/1024/1024 AS free_gb,
ROUND((d.total_size-d.free_size)*100.0/d.total_size, 2) AS used_pct
FROM v$tablespace t JOIN v$datafile d ON t.id = d.group_id WHERE t.name = 'CSDU_TS';
7.6 归档查询
SELECT arch_mode FROM v$database;
SELECT arch_name, arch_type, arch_dest, arch_file_size, arch_space_limit FROM v$dm_arch_ini;
SELECT * FROM v$archived_log ORDER BY first_time DESC;
7.7 数据量统计
SELECT table_name, num_rows, blocks, avg_row_len FROM user_tables WHERE table_name LIKE 'CSDU_%' ORDER BY num_rows DESC;
SELECT 'csdu_transaction' AS table_name, COUNT(*) AS cnt FROM csdu.csdu_transaction
UNION ALL SELECT 'csdu_account', COUNT(*) FROM csdu.csdu_account
UNION ALL SELECT 'csdu_customer', COUNT(*) FROM csdu.csdu_customer;
SELECT trans_date, COUNT(*) AS txn_count, SUM(trans_amt) AS txn_amt
FROM csdu.csdu_transaction GROUP BY trans_date ORDER BY trans_date;
7.8 性能查询
SELECT sql_text, elapsed_time/1000 AS elapsed_ms, executions FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
SET TIMING ON;
SELECT COUNT(*) FROM csdu.csdu_transaction WHERE trans_detail LIKE '%CSDUN_LARGE_DATA_1000000%';
SET TIMING OFF;
EXPLAIN SELECT * FROM csdu.csdu_transaction WHERE trans_date > SYSDATE - 30;
7.9 删除/清理语句
DELETE FROM csdu.csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_customer WHERE csdun_tag LIKE 'csdun_%';
COMMIT;
TRUNCATE TABLE csdu.csdu_transaction;
DROP USER csdu CASCADE;
DROP TABLESPACE csdu_ts;
7.10 备份恢复
BACKUP DATABASE FULL TO '/backup/csdu_db.bak' COMPRESSED;
BACKUP TABLESPACE csdu_ts FULL TO '/backup/csdu_ts.bak' COMPRESSED;
RESTORE DATABASE '/backup/csdu_db.bak' TO '/dm8/data/csdu_db';
RECOVER DATABASE '/dm8/data/csdu_db' WITH ARCHIVEDIR '/dm8/arch';
8. 人大金仓(KingbaseES) / KingbaseRWC集群 / KingbaseES逻辑备份
基于PostgreSQL，兼容Oracle语法（DBCOMPATIBILITY='ORA'）。 创建表、插入数据、查询SQL与第5章PostgreSQL或第7章达梦（Oracle兼容模式）一致。
8.1 备份恢复
# 逻辑备份
sys_dump -h localhost -U system -d csdu_db -F c -f /backup/csdu_db.dump
# 物理备份
sys_basebackup -h localhost -U system -D /backup/csdu_base -Ft -z -P
# 恢复
sys_restore -h localhost -U system -d csdu_db /backup/csdu_db.dump
9. GaussDB / GaussDB(DWS) / 云数据库GaussDB
华为GaussDB兼容PostgreSQL/Oracle语法。 创建表、插入数据、查询SQL与第5章PostgreSQL或第10章openGauss一致。
9.1 备份恢复
# 逻辑备份
gs_dump -h localhost -U gaussdb -d csdu_db -F c -f /backup/csdu_db.dump
# 物理备份
gs_basebackup -h localhost -U gaussdb -D /backup/csdu_base -Ft -z -P
# 恢复
gs_restore -h localhost -U gaussdb -d csdu_db /backup/csdu_db.dump
10. openGauss / openGauss集群
基于PostgreSQL，兼容Oracle语法（DBCOMPATIBILITY='ORA'）。
10.1 创建数据库
CREATE DATABASE csdu_db WITH ENCODING 'UTF8' DBCOMPATIBILITY='ORA';
\c csdu_db;
10.2 创建业务表（兼容Oracle语法）
CREATE TABLE csdu_customer (
cust_id VARCHAR2(32) PRIMARY KEY, cust_name VARCHAR2(100), id_type VARCHAR2(20),
id_no VARCHAR2(50), phone VARCHAR2(30), address VARCHAR2(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR2(20),
remark CLOB, csdun_tag VARCHAR2(20)
);
CREATE TABLE csdu_transaction (
trans_id VARCHAR2(64) PRIMARY KEY, acct_no VARCHAR2(32), trans_type VARCHAR2(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag CHAR(1), trans_amt NUMBER(18,2),
balance NUMBER(18,2), opp_acct_no VARCHAR2(32), opp_name VARCHAR2(100),
opp_bank_no VARCHAR2(20), trans_channel VARCHAR2(20), trans_summary VARCHAR2(200),
trans_detail CLOB, teller_no VARCHAR2(20), branch_no VARCHAR2(20), check_flag CHAR(1),
csdun_tag VARCHAR2(20)
);
10.3 插入测试数据
-- openGauss同时兼容Oracle函数和PG的generate_series
INSERT INTO csdu_transaction
SELECT 'TXN' || LPAD(i::text, 15, '0'), '6222' || LPAD((i%200000)::text, 10, '0'),
CASE i%5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
CURRENT_DATE - (i%365), CURRENT_TIMESTAMP - (i || ' seconds')::interval,
CASE i%2 WHEN 0 THEN '1' ELSE '2' END,
(random()*100000)::numeric(18,2), (random()*500000)::numeric(18,2),
'6222' || LPAD(((i+1)%200000)::text, 10, '0'), 'CSDUN测试对方户名_' || i,
'104' || LPAD((i%10000)::text, 8, '0'),
CASE i%4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' || i, REPEAT('CSDUN_LARGE_DATA_', 500) || i,
'T' || LPAD((i%9999)::text, 4, '0'), 'BR' || LPAD((i%999)::text, 3, '0'), '0', 'csdun_' || i
FROM generate_series(1, 30000000) AS i;
10.4 状态/归档/数据量/性能查询
SELECT * FROM pg_stat_activity WHERE datname='csdu_db';
SELECT pg_database_size('csdu_db');
SHOW archive_mode;
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables WHERE relname LIKE 'csdu_%';
SELECT query, calls, ROUND(mean_exec_time::numeric,2) AS avg_ms FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
10.5 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE IF EXISTS csdu_transaction, csdu_account, csdu_customer, csdu_loan, csdu_deposit_product CASCADE;
DROP DATABASE csdu_db;
10.6 备份恢复
gs_dump -h localhost -U omm -d csdu_db -F c -f /backup/csdu_db.dump
gs_restore -h localhost -U omm -d csdu_db /backup/csdu_db.dump
gs_basebackup -h localhost -U omm -D /backup/csdu_base -Ft -z -P
11. Vastbase / Vastbase集群
基于PostgreSQL，兼容Oracle。SQL同第10章openGauss。
11.1 备份恢复
vb_dump -h localhost -U vastbase -d csdu_db -F c -f /backup/csdu_db.dump
vb_restore -h localhost -U vastbase -d csdu_db /backup/csdu_db.dump
12. PanWeiDB / PanWeiDB集群
基于PostgreSQL。SQL同第5章PostgreSQL。
12.1 备份恢复
pg_dump -h localhost -U panwei -d csdu_db -F c -f /backup/csdu_db.dump
pg_restore -h localhost -U panwei -d csdu_db /backup/csdu_db.dump
13. MogDB
基于openGauss，兼容Oracle。SQL同第10章openGauss。
13.1 备份恢复
mog_dump -h localhost -U mogdb -d csdu_db -F c -f /backup/csdu_db.dump
mog_restore -h localhost -U mogdb -d csdu_db /backup/csdu_db.dump
14. AntDB / AntDB分布式集群
基于PostgreSQL，支持分布式事务。SQL同第5章PostgreSQL。
14.1 分布式特有操作
-- 查看分片分布
SELECT * FROM pgxc_class WHERE relname='csdu_transaction';
-- 查看数据节点
SELECT * FROM pgxc_node;
14.2 备份恢复
ant_dump -h localhost -U antdb -d csdu_db -F c -f /backup/csdu_db.dump
ant_restore -h localhost -U antdb -d csdu_db /backup/csdu_db.dump
15. Informix / Informix集群 / SinoDB / SinoDB集群
15.1 技术原理
IBM Informix/星瑞格SinoDB采用动态可伸缩架构(DSA)：
-
存储: dbspace -> chunk -> page(默认2KB/4KB)
-
日志: 逻辑日志(Logical Log)记录事务，支持连续备份
-
备份: ontape(磁带)、onbar(存储管理器)、外部备份
15.2 创建数据库
CREATE DATABASE csdu_db WITH LOG IN '/informix/data/csdu_db';
DATABASE csdu_db;
15.3 创建业务表
CREATE TABLE csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY, cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR(20),
remark TEXT, csdun_tag VARCHAR(20)
);
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time DATETIME YEAR TO FRACTION(5), dr_cr_flag CHAR(1),
trans_amt DECIMAL(18,2), balance DECIMAL(18,2), opp_acct_no VARCHAR(32),
opp_name VARCHAR(100), opp_bank_no VARCHAR(20), trans_channel VARCHAR(20),
trans_summary VARCHAR(200), trans_detail TEXT, teller_no VARCHAR(20),
branch_no VARCHAR(20), check_flag CHAR(1), csdun_tag VARCHAR(20)
);
15.4 插入测试数据
CREATE PROCEDURE insert_csdu_trans()
DEFINE i INT;
FOR i = 1 TO 30000000
INSERT INTO csdu_transaction VALUES (
'TXN' || LPAD(i, 15, '0'), '6222' || LPAD(MOD(i, 200000), 10, '0'),
CASE MOD(i, 5) WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
TODAY - MOD(i, 365), CURRENT YEAR TO FRACTION(5) - i UNITS SECOND,
CASE MOD(i, 2) WHEN 0 THEN '1' ELSE '2' END,
ROUND(RAND() * 100000, 2), ROUND(RAND() * 500000, 2),
'6222' || LPAD(MOD(i + 1, 200000), 10, '0'), 'CSDUN测试对方户名_' || i,
'104' || LPAD(MOD(i, 10000), 8, '0'),
CASE MOD(i, 4) WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' || i, REPEAT('CSDUN_LARGE_DATA_', 500) || i,
'T' || LPAD(MOD(i, 9999), 4, '0'), 'BR' || LPAD(MOD(i, 999), 3, '0'), '0', 'csdun_' || i);
IF MOD(i, 10000) = 0 THEN COMMIT; END IF;
END FOR;
END PROCEDURE;
EXECUTE PROCEDURE insert_csdu_trans();
15.5 状态/归档/数据量查询
onstat -
onstat -g sql
onstat -g ses
onstat -g arc
echo "SELECT COUNT(*) FROM csdu_transaction;" | dbaccess csdu_db -
echo "SELECT tabname, (nrows * rowsize)/1024/1024/1024 AS est_gb FROM systables WHERE tabname LIKE 'csdu_%';" | dbaccess csdu_db -
15.6 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_transaction; DROP TABLE csdu_account; DROP TABLE csdu_customer;
DROP TABLE csdu_loan; DROP TABLE csdu_deposit_product; DROP DATABASE csdu_db;
15.7 备份恢复
ontape -a -d csdu_db
onbar -b -L 0 csdu_db
onbar -r -t "2026/08/20 22:00:00" csdu_db
16. GBase 8s / GBase 8s集群
基于Informix，语法和工具链完全相同。参考第15章Informix。
16.1 备份恢复
ontape -a -d csdu_db
onbar -b -L 0 csdu_db
17. GBase 8a
17.1 技术原理
GBase 8a是MPP分析型数据库：
-
列存储: 数据按列组织，适合OLAP分析场景
-
分布式: 无共享MPP架构，线性扩展
17.2 创建数据库
CREATE DATABASE csdu_db;
USE csdu_db;
17.3 创建业务表
CREATE TABLE csdu_customer (
cust_id VARCHAR(32), cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR(20),
remark VARCHAR(4000), csdun_tag VARCHAR(20)
) DISTRIBUTED BY ('cust_id');
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64), acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time DATETIME, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail TEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20)
) DISTRIBUTED BY ('acct_no');
17.4 插入测试数据
INSERT INTO csdu_transaction
SELECT CONCAT('TXN', LPAD(seq, 15, '0')), CONCAT('6222', LPAD(seq%200000, 10, '0')),
CASE seq%5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATE_SUB(CURDATE(), INTERVAL (seq%365) DAY), DATE_SUB(NOW(), INTERVAL seq SECOND),
CASE seq%2 WHEN 0 THEN '1' ELSE '2' END, ROUND(RAND()*100000, 2), ROUND(RAND()*500000, 2),
CONCAT('6222', LPAD((seq+1)%200000, 10, '0')), CONCAT('CSDUN测试对方户名_', seq),
CONCAT('104', LPAD(seq%10000, 8, '0')),
CASE seq%4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
CONCAT('CSDUN银行业务测试交易摘要-', seq), REPEAT(CONCAT('CSDUN_LARGE_DATA_', seq, ' '), 500),
CONCAT('T', LPAD(seq%9999, 4, '0')), CONCAT('BR', LPAD(seq%999, 3, '0')), '0', CONCAT('csdun_', seq)
FROM (SELECT @row:=@row+1 AS seq FROM information_schema.tables LIMIT 30000000) t, (SELECT @row:=0) init;
17.5 状态/数据量/性能查询
SHOW VARIABLES LIKE 'gbase%';
SHOW PROCESSLIST;
SELECT table_name, table_rows, data_length/1024/1024/1024 AS data_gb
FROM information_schema.tables WHERE table_schema='csdu_db';
EXPLAIN SELECT * FROM csdu_transaction WHERE trans_date > '2024-01-01';
17.6 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
TRUNCATE TABLE csdu_transaction;
DROP TABLE csdu_transaction;
DROP DATABASE csdu_db;
17.7 备份恢复
gccli -e "SELECT * FROM csdu_db.csdu_transaction INTO OUTFILE '/backup/csdu_trans.txt'"
LOAD DATA INFILE '/backup/csdu_trans.txt' INTO TABLE csdu_db.csdu_transaction;
18. 神通
基于PostgreSQL。SQL同第5章PostgreSQL。
18.1 备份恢复
os_dump -h localhost -U sysdba -d csdu_db -F c -f /backup/csdu_db.dump
os_restore -h localhost -U sysdba -d csdu_db /backup/csdu_db.dump
19. HighGo DB / HighGo DB集群
基于PostgreSQL。SQL同第5章PostgreSQL。
19.1 备份恢复
hg_dump -h localhost -U highgo -d csdu_db -F c -f /backup/csdu_db.dump
hg_restore -h localhost -U highgo -d csdu_db /backup/csdu_db.dump
20. TelePG
基于PostgreSQL。SQL同第5章PostgreSQL。
20.1 备份恢复
pg_dump -h localhost -U telepg -d csdu_db -F c -f /backup/csdu_db.dump
pg_restore -h localhost -U telepg -d csdu_db /backup/csdu_db.dump
21. TeleDB
兼容MySQL协议。SQL同第3章MySQL。
21.1 备份恢复
mysqldump -u root -p csdu_db > /backup/csdu_db.sql
mysql -u root -p csdu_db < /backup/csdu_db.sql
22. Caché / Caché集群 / InterSystems IRISHealth集群
22.1 技术原理
InterSystems Caché/IRIS是后关系型数据库：
-
多维数据模型: 数据以稀疏多维数组(Globals)存储
-
对象-SQL双接口: 支持对象访问和SQL查询
22.2 创建命名空间/数据库
在管理门户中创建 csdu_db 命名空间。
22.3 创建业务表（SQL接口）
CREATE TABLE csdu.csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY, cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type VARCHAR(1),
risk_level VARCHAR(1), open_date DATE, status VARCHAR(1), branch_no VARCHAR(20),
remark %Stream.GlobalCharacter, csdun_tag VARCHAR(20)
)
CREATE TABLE csdu.csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time %TimeStamp, dr_cr_flag VARCHAR(1), trans_amt %Decimal,
balance %Decimal, opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail %Stream.GlobalCharacter, teller_no VARCHAR(20), branch_no VARCHAR(20),
check_flag VARCHAR(1), csdun_tag VARCHAR(20)
)
22.4 插入测试数据（ObjectScript）
SET $NAMESPACE="csdu_db"
FOR i=1:1:30000000 {
&sql(INSERT INTO csdu.csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
VALUES (:i, :acct, :ttype, :tdate, :ttime, :flag, :amt, :bal, :opp, :oname, :obank, :channel, :summary, :detail, :teller, :branch, :check, :tag))
IF i#10000=0 { DO $SYSTEM.SQL.Commit() WRITE "Inserted ", i, ! }
}
22.5 删除语句
DELETE FROM csdu.csdu_transaction WHERE csdun_tag LIKE 'csdun_%'
DELETE FROM csdu.csdu_account WHERE csdun_tag LIKE 'csdun_%'
DELETE FROM csdu.csdu_customer WHERE csdun_tag LIKE 'csdun_%'
DROP TABLE csdu.csdu_transaction
DROP TABLE csdu.csdu_account
DROP TABLE csdu.csdu_customer
DROP TABLE csdu.csdu_loan
DROP TABLE csdu.csdu_deposit_product
22.6 备份恢复
DO ^BACKUP
DO ^RESTORE
23. Sybase
23.1 技术原理
SAP Sybase ASE采用页式存储，支持DUMP/LOAD命令备份恢复。
23.2 创建数据库
CREATE DATABASE csdu_db ON csdu_dat='10G' LOG ON csdu_log='2G';
USE csdu_db;
23.3 创建业务表
CREATE TABLE dbo.csdu_customer (
cust_id VARCHAR(32) PRIMARY KEY, cust_name VARCHAR(100), id_type VARCHAR(20),
id_no VARCHAR(50), phone VARCHAR(30), address VARCHAR(500), cust_type CHAR(1),
risk_level CHAR(1), open_date DATE, status CHAR(1), branch_no VARCHAR(20),
remark TEXT, csdun_tag VARCHAR(20)
);
CREATE TABLE dbo.csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time DATETIME, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail TEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20)
);
23.4 插入测试数据
DECLARE @i INT = 0;
WHILE @i < 30000000
BEGIN
INSERT INTO dbo.csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
SELECT TOP 100000
'TXN' + RIGHT('000000000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i AS VARCHAR), 15),
'6222' + RIGHT('0000000000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 200000 AS VARCHAR), 10),
CASE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATEADD(DAY, -((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 365), GETDATE()),
DATEADD(SECOND, -((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i)), GETDATE()),
CASE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 2 WHEN 0 THEN '1' ELSE '2' END,
ROUND(RAND(CHECKSUM(NEWID())) * 100000, 2), ROUND(RAND(CHECKSUM(NEWID())) * 500000, 2),
'6222' + RIGHT('0000000000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i + 1) % 200000 AS VARCHAR), 10),
'CSDUN测试对方户名_' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR),
'104' + RIGHT('00000000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 10000 AS VARCHAR), 8),
CASE (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR),
REPLICATE('CSDUN_LARGE_DATA_', 500) + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR),
'T' + RIGHT('0000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 9999 AS VARCHAR), 4),
'BR' + RIGHT('000' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) % 999 AS VARCHAR), 3), '0',
'csdun_' + CAST((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @i) AS VARCHAR)
FROM master..spt_values a CROSS JOIN master..spt_values b;
SET @i = @i + 100000;
END;
23.5 状态/归档/数据量查询
sp_helpdb csdu_db
sp_who
sp_lock
sp_helpdb csdu_db -- 查看trunc log on chkpt
sp_spaceused csdu_transaction
sp_spaceused csdu_db
SELECT COUNT(*) FROM csdu_transaction;
23.6 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_transaction
DROP TABLE csdu_account
DROP TABLE csdu_customer
DROP TABLE csdu_loan
DROP TABLE csdu_deposit_product
DROP DATABASE csdu_db
23.7 备份恢复
DUMP DATABASE csdu_db TO '/backup/csdu_db.dmp'
DUMP TRANSACTION csdu_db TO '/backup/csdu_db_log.trn'
LOAD DATABASE csdu_db FROM '/backup/csdu_db.dmp'
LOAD TRANSACTION csdu_db FROM '/backup/csdu_db_log.trn'
24. TDSQL / TDSQL MySQL版集中式
24.1 技术原理
腾讯TDSQL是分布式MySQL数据库：
-
自动分片: 基于shardkey自动水平拆分
-
强一致: 基于Raft协议的多副本同步
24.2 创建数据库
CREATE DATABASE csdu_db;
USE csdu_db;
SET shardkey=acct_no;
24.3 创建业务表
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time DATETIME, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail LONGTEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20), shardkey(acct_no)
);
24.4 插入测试数据
-- 使用LOAD DATA批量导入（推荐）
LOAD DATA INFILE '/tmp/csdu_trans.csv' INTO TABLE csdu_transaction;
-- 或同MySQL存储过程批量插入
24.5 状态查询
SHOW STATUS LIKE 'Threads_%';
SHOW PROCESSLIST;
SHOW SHARDS; -- TDSQL特有，查看分片状态
24.6 归档查询
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;
24.7 数据量统计
SELECT table_name, ROUND(data_length/1024/1024/1024,2) AS data_gb
FROM information_schema.tables WHERE table_schema='csdu_db';
SELECT COUNT(*) FROM csdu_transaction;
24.8 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_transaction;
DROP DATABASE csdu_db;
24.9 备份恢复
# 通过TDSQL控制台进行逻辑备份
# 或使用mysqldump通过Proxy
mysqldump -h proxy_ip -u root -p csdu_db > csdu_db.sql
mysql -h proxy_ip -u root -p csdu_db < csdu_db.sql
25. GoldenDB
中兴分布式数据库，兼容MySQL。SQL同第3章MySQL。
25.1 备份恢复
# 通过GoldenDB管理平台进行备份恢复
# 或mysqldump通过Proxy
mysqldump -h proxy_ip -u root -p csdu_db > csdu_db.sql
26. Kingwow集群
兼容MySQL协议。SQL同第3章MySQL。
26.1 备份恢复
mysqldump -u root -p csdu_db > csdu_db.sql
mysql -u root -p csdu_db < csdu_db.sql
27. SAP HANA
27.1 技术原理
SAP HANA是内存数据库平台：
-
列式存储: 数据主要以列式存储在内存中
-
持久化: 内存数据通过Savepoint持久化到磁盘
27.2 创建Schema
CREATE SCHEMA csdu;
-- 或在SYSTEMDB中创建租户
CREATE DATABASE csdu_db SYSTEM USER PASSWORD csdu_password;
27.3 创建业务表
CREATE COLUMN TABLE csdu.csdu_customer (
cust_id NVARCHAR(32) PRIMARY KEY, cust_name NVARCHAR(100), id_type NVARCHAR(20),
id_no NVARCHAR(50), phone NVARCHAR(30), address NVARCHAR(500), cust_type NCHAR(1),
risk_level NCHAR(1), open_date DATE, status NCHAR(1), branch_no NVARCHAR(20),
remark NCLOB, csdun_tag NVARCHAR(20)
);
CREATE COLUMN TABLE csdu.csdu_transaction (
trans_id NVARCHAR(64) PRIMARY KEY, acct_no NVARCHAR(32), trans_type NVARCHAR(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag NCHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no NVARCHAR(32), opp_name NVARCHAR(100),
opp_bank_no NVARCHAR(20), trans_channel NVARCHAR(20), trans_summary NVARCHAR(200),
trans_detail NCLOB, teller_no NVARCHAR(20), branch_no NVARCHAR(20), check_flag NCHAR(1),
csdun_tag NVARCHAR(20)
);
27.4 插入测试数据
IMPORT FROM CSV FILE '/backup/csdu_trans.csv' INTO csdu.csdu_transaction;
-- 或批量INSERT
INSERT INTO csdu.csdu_transaction
SELECT 'TXN' || LPAD(ROW_NUMBER() OVER(), 15, '0'), ... FROM DUMMY LIMIT 30000000;
27.5 状态查询
SELECT * FROM M_DATABASE;
SELECT * FROM M_CONNECTIONS WHERE DATABASE_NAME='csdu_db';
SELECT * FROM M_SERVICE_STATISTICS;
27.6 归档查询
SELECT * FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME='log backup';
27.7 数据量统计
SELECT SCHEMA_NAME, TABLE_NAME, DISK_SIZE/1024/1024/1024 AS gb
FROM M_TABLE_PERSISTENCE_STATISTICS WHERE SCHEMA_NAME='CSDU';
SELECT COUNT(*) FROM csdu.csdu_transaction;
27.8 删除语句
DELETE FROM csdu.csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu.csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu.csdu_transaction;
DROP TABLE csdu.csdu_account;
DROP TABLE csdu.csdu_customer;
DROP SCHEMA csdu CASCADE;
27.9 备份恢复
BACKUP DATA FOR csdu_db USING FILE ('/backup/csdu_db');
BACKUP LOG FOR csdu_db USING FILE ('/backup/csdu_db_log');
RECOVER DATA FOR csdu_db USING FILE ('/backup/csdu_db')
USING LOG ('/backup/csdu_db_log') UNTIL TIMESTAMP '2026-08-20 22:00:00';
28. TiDB / TiDB逻辑备份
28.1 技术原理
PingCAP TiDB是分布式NewSQL数据库：
-
TiKV存储层: 基于RocksDB的分布式键值存储，使用Raft协议
-
TiDB计算层: 无状态SQL引擎，可水平扩展
28.2 创建数据库
CREATE DATABASE csdu_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE csdu_db;
28.3 创建业务表
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time DATETIME, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail LONGTEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20), KEY idx_acct (acct_no), KEY idx_date (trans_date)
);
28.4 插入测试数据（5-10GB）
INSERT INTO csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
SELECT CONCAT('TXN', LPAD(seq, 15, '0')), CONCAT('6222', LPAD(seq%200000, 10, '0')),
CASE seq%5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATE_SUB(CURDATE(), INTERVAL (seq%365) DAY), DATE_SUB(NOW(), INTERVAL seq SECOND),
CASE seq%2 WHEN 0 THEN '1' ELSE '2' END, ROUND(RAND()*100000, 2), ROUND(RAND()*500000, 2),
CONCAT('6222', LPAD((seq+1)%200000, 10, '0')), CONCAT('CSDUN测试对方户名_', seq),
CONCAT('104', LPAD(seq%10000, 8, '0')),
CASE seq%4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
CONCAT('CSDUN银行业务测试交易摘要-', seq), REPEAT(CONCAT('CSDUN_LARGE_DATA_', seq, ' '), 500),
CONCAT('T', LPAD(seq%9999, 4, '0')), CONCAT('BR', LPAD(seq%999, 3, '0')), '0', CONCAT('csdun_', seq)
FROM (SELECT @row:=@row+1 AS seq FROM information_schema.tables t1, information_schema.tables t2 LIMIT 30000000) t, (SELECT @row:=0) init;
28.5 状态查询
SELECT * FROM information_schema.cluster_info;
SELECT * FROM information_schema.tikv_region_status WHERE db_name='csdu_db';
SELECT * FROM information_schema.slow_query ORDER BY query_time DESC LIMIT 10;
28.6 归档查询
SHOW CONFIG WHERE NAME LIKE '%binlog%';
28.7 数据量统计
SELECT table_name, ROUND(data_length/1024/1024/1024,2) AS data_gb
FROM information_schema.tables WHERE table_schema='csdu_db';
SELECT COUNT(*) FROM csdu_transaction;
28.8 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_transaction;
DROP TABLE csdu_account;
DROP TABLE csdu_customer;
DROP DATABASE csdu_db;
28.9 备份恢复
# 物理备份（BR工具）
br backup full --pd "192.168.1.1:2379" --storage "local:///backup/csdu"
# 逻辑备份（Dumpling）
dumpling -u root -p -h 127.0.0.1 -P 4000 -B csdu_db -o /backup/csdu_db
# 物理恢复
br restore full --pd "192.168.1.1:2379" --storage "local:///backup/csdu"
# 逻辑恢复（Lightning）
tidb-lightning -config lightning.toml
29. OceanBase
29.1 技术原理
OceanBase是蚂蚁集团自研的分布式数据库：
-
多租户: 一个集群可承载多个租户(数据库实例)
-
Paxos协议: 三副本强一致，自动故障切换
-
LSM-Tree存储: 数据先写内存(MemTable)，再合并到磁盘(SSTable)
29.2 在租户中创建数据库
-- 连接目标租户：mysql -h127.0.0.1 -P2881 -uroot@tenant1#cluster -p
CREATE DATABASE csdu_db;
USE csdu_db;
29.3 创建业务表
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag CHAR(1), trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail LONGTEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20), INDEX idx_acct (acct_no), INDEX idx_date (trans_date)
) PARTITION BY HASH(acct_no) PARTITIONS 16;
29.4 插入测试数据（5-10GB）
INSERT INTO csdu_transaction (trans_id, acct_no, trans_type, trans_date, trans_time, dr_cr_flag, trans_amt, balance, opp_acct_no, opp_name, opp_bank_no, trans_channel, trans_summary, trans_detail, teller_no, branch_no, check_flag, csdun_tag)
SELECT CONCAT('TXN', LPAD(seq, 15, '0')), CONCAT('6222', LPAD(seq%200000, 10, '0')),
CASE seq%5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATE_SUB(CURDATE(), INTERVAL (seq%365) DAY), DATE_SUB(NOW(), INTERVAL seq SECOND),
CASE seq%2 WHEN 0 THEN '1' ELSE '2' END, ROUND(RAND()*100000, 2), ROUND(RAND()*500000, 2),
CONCAT('6222', LPAD((seq+1)%200000, 10, '0')), CONCAT('CSDUN测试对方户名_', seq),
CONCAT('104', LPAD(seq%10000, 8, '0')),
CASE seq%4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
CONCAT('CSDUN银行业务测试交易摘要-', seq), REPEAT(CONCAT('CSDUN_LARGE_DATA_', seq, ' '), 500),
CONCAT('T', LPAD(seq%9999, 4, '0')), CONCAT('BR', LPAD(seq%999, 3, '0')), '0', CONCAT('csdun_', seq)
FROM (SELECT @row:=@row+1 AS seq FROM information_schema.tables t1, information_schema.tables t2 LIMIT 30000000) t, (SELECT @row:=0) init;
29.5 状态查询
SELECT * FROM oceanbase.__all_tenant;
SELECT * FROM oceanbase.__all_zone;
SHOW PROCESSLIST;
SELECT * FROM oceanbase.__all_virtual_server_stat;
29.6 归档查询
SHOW PARAMETERS LIKE 'log_archive_dest';
29.7 数据量统计
SELECT table_name, ROUND(data_length/1024/1024/1024,2) AS data_gb
FROM information_schema.tables WHERE table_schema='csdu_db';
SELECT COUNT(*) FROM csdu_transaction;
29.8 性能查询
EXPLAIN SELECT * FROM csdu_transaction WHERE acct_no='622200000001';
SELECT * FROM oceanbase.__all_virtual_sql_audit WHERE tenant_id=xxx ORDER BY request_time DESC LIMIT 10;
29.9 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_account WHERE csdun_tag LIKE 'csdun_%';
DELETE FROM csdu_customer WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_transaction;
DROP TABLE csdu_account;
DROP TABLE csdu_customer;
DROP DATABASE csdu_db;
29.10 备份恢复
# 使用OCP或obbackup进行物理备份
obbackup backup --tenant tenant_name --data_backup_dest /backup/csdu
# 逻辑备份
mysqldump -h 127.0.0.1 -P 2881 -u root@tenant -p csdu_db > csdu_db.sql
# 物理恢复
obbackup restore --tenant tenant_name --data_backup_dest /backup/csdu
# 逻辑恢复
mysql -h 127.0.0.1 -P 2881 -u root@tenant -p csdu_db < csdu_db.sql
30. Hadoop生态（Hadoop / Hive / CDH / CDP / TDH / TDC / FusionInsight / MRS）
30.1 技术原理
Hadoop大数据生态包含：
-
HDFS: 分布式文件系统，数据分块(默认128MB)多副本存储
-
Hive: 数据仓库工具，将SQL转换为MapReduce/Tez/Spark任务
-
YARN: 资源调度框架
30.2 Hive中创建数据库和表
CREATE DATABASE csdu_db;
USE csdu_db;
CREATE TABLE csdu_customer (
cust_id STRING, cust_name STRING, id_type STRING, id_no STRING,
phone STRING, address STRING, cust_type STRING, risk_level STRING,
open_date DATE, status STRING, branch_no STRING, remark STRING, csdun_tag STRING
) STORED AS ORC;
CREATE TABLE csdu_transaction (
trans_id STRING, acct_no STRING, trans_type STRING, trans_date DATE,
trans_time TIMESTAMP, dr_cr_flag STRING, trans_amt DECIMAL(18,2),
balance DECIMAL(18,2), opp_acct_no STRING, opp_name STRING,
opp_bank_no STRING, trans_channel STRING, trans_summary STRING,
trans_detail STRING, teller_no STRING, branch_no STRING, check_flag STRING,
csdun_tag STRING
) STORED AS ORC;
30.3 插入测试数据
INSERT OVERWRITE TABLE csdu_db.csdu_transaction
SELECT
CONCAT('TXN', LPAD(CAST(rand()*30000000 AS INT), 15, '0')),
CONCAT('6222', LPAD(CAST(rand()*200000 AS INT), 10, '0')),
CASE CAST(rand()*5 AS INT) WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
DATE_SUB(CURRENT_DATE, CAST(rand()*365 AS INT)),
CAST(CONCAT(DATE_SUB(CURRENT_DATE, CAST(rand()*365 AS INT)), ' ', LPAD(CAST(rand()*24 AS INT),2,'0'), ':', LPAD(CAST(rand()*60 AS INT),2,'0'), ':', LPAD(CAST(rand()*60 AS INT),2,'0')) AS TIMESTAMP),
CASE CAST(rand()*2 AS INT) WHEN 0 THEN '1' ELSE '2' END,
CAST(rand()*100000 AS DECIMAL(18,2)), CAST(rand()*500000 AS DECIMAL(18,2)),
CONCAT('6222', LPAD(CAST(rand()*200000 AS INT), 10, '0')),
CONCAT('CSDUN测试对方户名_', CAST(rand()*30000000 AS INT)),
CONCAT('104', LPAD(CAST(rand()*10000 AS INT), 8, '0')),
CASE CAST(rand()*4 AS INT) WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
CONCAT('CSDUN银行业务测试交易摘要-', CAST(rand()*30000000 AS INT)),
REPEAT('CSDUN_LARGE_DATA_', 500),
CONCAT('T', LPAD(CAST(rand()*9999 AS INT), 4, '0')),
CONCAT('BR', LPAD(CAST(rand()*999 AS INT), 3, '0')),
'0', CONCAT('csdun_', CAST(rand()*30000000 AS INT))
FROM generate_series_table LIMIT 30000000;
30.4 状态查询
hdfs dfsadmin -report
yarn node -list
SHOW COMPACTIONS;
30.5 数据量统计
hdfs dfs -du -h /user/hive/warehouse/csdu_db.db
SELECT COUNT(*) FROM csdu_db.csdu_transaction;
30.6 删除语句
DROP TABLE csdu_db.csdu_transaction;
DROP TABLE csdu_db.csdu_account;
DROP TABLE csdu_db.csdu_customer;
DROP DATABASE csdu_db;
30.7 备份恢复
hdfs distcp /user/hive/warehouse/csdu_db.db /backup/csdu_db
EXPORT TABLE csdu_db.csdu_transaction TO '/backup/csdu_trans';
hdfs distcp /backup/csdu_db /user/hive/warehouse/csdu_db.db
IMPORT TABLE csdu_db.csdu_transaction FROM '/backup/csdu_trans';
31. Greenplum
31.1 技术原理
VMware Greenplum是MPP分析型数据库：
-
主从架构: Master负责SQL解析，Segment并行执行
-
分布键: 表数据按分布键哈希分布到各Segment
31.2 创建数据库
CREATE DATABASE csdu_db WITH ENCODING='UTF8';
\c csdu_db;
31.3 创建业务表
CREATE TABLE csdu_transaction (
trans_id VARCHAR(64) PRIMARY KEY, acct_no VARCHAR(32), trans_type VARCHAR(10),
trans_date DATE, trans_time TIMESTAMP, dr_cr_flag CHAR(1), trans_amt NUMERIC(18,2),
balance NUMERIC(18,2), opp_acct_no VARCHAR(32), opp_name VARCHAR(100),
opp_bank_no VARCHAR(20), trans_channel VARCHAR(20), trans_summary VARCHAR(200),
trans_detail TEXT, teller_no VARCHAR(20), branch_no VARCHAR(20), check_flag CHAR(1),
csdun_tag VARCHAR(20)
) DISTRIBUTED BY (acct_no);
31.4 插入测试数据
INSERT INTO csdu_transaction
SELECT 'TXN' || LPAD(i::text, 15, '0'), '6222' || LPAD((i%200000)::text, 10, '0'),
CASE i%5 WHEN 0 THEN 'DEPOSIT' WHEN 1 THEN 'WITHDRAW' WHEN 2 THEN 'TRANSFER' WHEN 3 THEN 'PAYMENT' ELSE 'FEE' END,
CURRENT_DATE - (i%365), CURRENT_TIMESTAMP - (i || ' seconds')::interval,
CASE i%2 WHEN 0 THEN '1' ELSE '2' END,
(random()*100000)::numeric(18,2), (random()*500000)::numeric(18,2),
'6222' || LPAD(((i+1)%200000)::text, 10, '0'), 'CSDUN测试对方户名_' || i,
'104' || LPAD((i%10000)::text, 8, '0'),
CASE i%4 WHEN 0 THEN 'COUNTER' WHEN 1 THEN 'ATM' WHEN 2 THEN 'MOBILE' ELSE 'WEB' END,
'CSDUN银行业务测试交易摘要-' || i, REPEAT('CSDUN_LARGE_DATA_' || i || ' ', 500),
'T' || LPAD((i%9999)::text, 4, '0'), 'BR' || LPAD((i%999)::text, 3, '0'), '0', 'csdun_' || i
FROM generate_series(1, 30000000) AS i;
31.5 状态查询
SELECT * FROM pg_stat_activity;
SELECT * FROM gp_segment_configuration;
SELECT gp_segment_id, COUNT(*) FROM csdu_transaction GROUP BY gp_segment_id;
31.6 数据量统计
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables WHERE relname LIKE 'csdu_%';
SELECT COUNT(*) FROM csdu_transaction;
31.7 删除语句
DELETE FROM csdu_transaction WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_transaction;
DROP DATABASE csdu_db;
31.8 备份恢复
gpbackup --dbname csdu_db --backup-dir /backup/
gprestore --timestamp <ts> --backup-dir /backup/ --create-db
32. MongoDB
32.1 技术原理
MongoDB是文档型NoSQL数据库：
-
BSON格式: 以二进制JSON存储文档
-
副本集: 一主多从，自动故障切换
-
分片集群: 数据按片键分布到多个分片
32.2 创建数据库和集合
use csdu_db;
db.createCollection("csdu_transaction");
db.createCollection("csdu_customer");
32.3 插入测试数据
for (var i = 0; i < 30000000; i++) {
db.csdu_transaction.insertOne({
trans_id: 'TXN' + i.toString().padStart(15, '0'),
acct_no: '6222' + (i % 200000).toString().padStart(10, '0'),
trans_type: ['DEPOSIT', 'WITHDRAW', 'TRANSFER', 'PAYMENT', 'FEE'][i % 5],
trans_date: new Date(Date.now() - (i % 365) * 86400000),
trans_time: new Date(Date.now() - i * 1000),
dr_cr_flag: (i % 2 === 0) ? '1' : '2',
trans_amt: Math.random() * 100000,
balance: Math.random() * 500000,
opp_acct_no: '6222' + ((i + 1) % 200000).toString().padStart(10, '0'),
opp_name: 'CSDUN测试对方户名_' + i,
opp_bank_no: '104' + (i % 10000).toString().padStart(8, '0'),
trans_channel: ['COUNTER', 'ATM', 'MOBILE', 'WEB'][i % 4],
trans_summary: 'CSDUN银行业务测试交易摘要-' + i,
trans_detail: 'CSDUN_LARGE_DATA_'.repeat(500) + i,
teller_no: 'T' + (i % 9999).toString().padStart(4, '0'),
branch_no: 'BR' + (i % 999).toString().padStart(3, '0'),
check_flag: '0',
csdun_tag: 'csdun_' + i
});
if (i % 10000 === 0) print('Inserted ' + i);
}
32.4 状态查询
db.serverStatus()
db.currentOp()
rs.status()
32.5 归档查询
rs.printReplicationInfo()
32.6 数据量统计
db.csdu_transaction.stats()
db.csdu_db.stats()
db.csdu_transaction.countDocuments()
32.7 删除语句
db.csdu_transaction.deleteMany({ csdun_tag: { $regex: '^csdun_' } })
db.csdu_account.deleteMany({ csdun_tag: { $regex: '^csdun_' } })
db.csdu_customer.deleteMany({ csdun_tag: { $regex: '^csdun_' } })
db.csdu_transaction.drop()
db.csdu_account.drop()
db.csdu_customer.drop()
db.dropDatabase()
32.8 备份恢复
mongodump --host localhost --db csdu_db --out /backup/
mongodump --host rs0/primary:27017 --db csdu_db --out /backup/
mongorestore --host localhost --db csdu_db /backup/csdu_db/
mongorestore --host localhost --db csdu_db --oplogReplay /backup/csdu_db/
33. Elasticsearch
33.1 技术原理
Elasticsearch是分布式搜索引擎：
-
倒排索引: 基于Lucene，适合全文检索
-
分片(Shard): 索引分为多个主分片和副本分片
33.2 创建索引
PUT /csdu_db
{
"settings": { "number_of_shards": 3, "number_of_replicas": 1 },
"mappings": {
"properties": {
"trans_id": { "type": "keyword" },
"acct_no": { "type": "keyword" },
"trans_type": { "type": "keyword" },
"trans_date": { "type": "date" },
"trans_amt": { "type": "scaled_float", "scaling_factor": 100 },
"trans_detail": { "type": "text" },
"csdun_tag": { "type": "keyword" }
}
}
}
33.3 插入测试数据
POST /_bulk
{ "index": { "_index": "csdu_db", "_id": "1" } }
{ "trans_id": "TXN000000000000001", "acct_no": "6222000000000001", "trans_type": "DEPOSIT", "trans_date": "2024-01-01", "trans_amt": 10000.00, "trans_detail": "CSDUN_LARGE_DATA_...", "csdun_tag": "csdun_1" }
// 重复3000万次或使用Logstash/Filebeat导入
33.4 状态查询
GET /_cluster/health
GET /_cluster/stats
GET /_cat/indices/csdu_db?v
33.5 数据量统计
GET /csdu_db/_stats/store
GET /csdu_db/_count
33.6 删除语句
POST /csdu_db/_delete_by_query
{
"query": { "wildcard": { "csdun_tag": "csdun_*" } }
}
DELETE /csdu_db
33.7 备份恢复
PUT /_snapshot/backup_repo
{ "type": "fs", "settings": { "location": "/backup/es" } }
PUT /_snapshot/backup_repo/csdu_snapshot?wait_for_completion=true
{ "indices": "csdu_db" }
POST /_snapshot/backup_repo/csdu_snapshot/_restore
{ "indices": "csdu_db" }
34. ClickHouse
34.1 技术原理
ClickHouse是列式OLAP数据库：
-
列式存储: 每列单独存储，高压缩比
-
MergeTree引擎: 数据按主键排序，分块合并
34.2 创建数据库和表
CREATE DATABASE csdu_db;
USE csdu_db;
CREATE TABLE csdu_transaction (
trans_id String, acct_no String, trans_type String, trans_date Date,
trans_time DateTime, dr_cr_flag String, trans_amt Decimal(18,2),
balance Decimal(18,2), opp_acct_no String, opp_name String,
opp_bank_no String, trans_channel String, trans_summary String,
trans_detail String, teller_no String, branch_no String, check_flag String,
csdun_tag String
) ENGINE = MergeTree()
ORDER BY (acct_no, trans_date)
PARTITION BY toYYYYMM(trans_date);
34.3 插入测试数据
INSERT INTO csdu_transaction
SELECT
concat('TXN', leftPad(toString(number), 15, '0')),
concat('6222', leftPad(toString(number%200000), 10, '0')),
['DEPOSIT','WITHDRAW','TRANSFER','PAYMENT','FEE'][number%5+1],
today() - number%365, now() - number,
['1','2'][number%2+1], randUniform(1, 100000), randUniform(1, 500000),
concat('6222', leftPad(toString((number+1)%200000), 10, '0')),
concat('CSDUN测试对方户名_', toString(number)),
concat('104', leftPad(toString(number%10000), 8, '0')),
['COUNTER','ATM','MOBILE','WEB'][number%4+1],
concat('CSDUN银行业务测试交易摘要-', toString(number)),
repeat('CSDUN_LARGE_DATA_', 500) || toString(number),
concat('T', leftPad(toString(number%9999), 4, '0')),
concat('BR', leftPad(toString(number%999), 3, '0')), '0',
concat('csdun_', toString(number))
FROM numbers(30000000);
34.4 状态查询
SELECT * FROM system.processes;
SELECT * FROM system.metrics;
SELECT * FROM system.clusters;
34.5 数据量统计
SELECT database, table, formatReadableSize(sum(bytes)) AS size, sum(rows) AS rows
FROM system.parts WHERE database='csdu_db' GROUP BY database, table;
SELECT COUNT(*) FROM csdu_db.csdu_transaction;
34.6 删除语句
ALTER TABLE csdu_db.csdu_transaction DELETE WHERE csdun_tag LIKE 'csdun_%';
DROP TABLE csdu_db.csdu_transaction;
DROP DATABASE csdu_db;
34.7 备份恢复
clickhouse-backup create csdu_backup
clickhouse-backup restore csdu_backup
clickhouse-client --query="SELECT * FROM csdu_db.csdu_transaction" > /backup/csdu_trans.csv
clickhouse-client --query="INSERT INTO csdu_db.csdu_transaction FORMAT CSV" < /backup/csdu_trans.csv
35. Redis
35.1 技术原理
Redis是内存数据结构存储：
-
数据结构: String、Hash、List、Set、ZSet、Stream等
-
持久化: RDB(快照) + AOF(追加日志)
35.2 创建数据
SELECT 0
35.3 插入测试数据
for i in {0..29999999}; do
redis-cli HSET csdu:transaction:$i trans_id "TXN$(printf '%015d' $i)" acct_no "6222$(printf '%010d' $((i % 200000)))" trans_type "$(echo 'DEPOSIT WITHDRAW TRANSFER PAYMENT FEE' | cut -d' ' -f$((i % 5 + 1)))" trans_amt "$(awk -v seed=$i 'BEGIN{srand(seed); print int(rand()*100000)}')" csdun_tag "csdun_$i"
if [ $((i % 10000)) -eq 0 ]; then echo "Inserted $i"; fi
done
35.4 状态查询
INFO
INFO replication
INFO memory
CLIENT LIST
35.5 归档查询
CONFIG GET appendonly
CONFIG GET save
35.6 数据量统计
DBSIZE
INFO keyspace
MEMORY USAGE csdu:transaction:1
35.7 删除语句
redis-cli --eval "
local keys = redis.call('keys', 'csdu:*')
for i=1,#keys do redis.call('del', keys[i]) end
return #keys
"
FLUSHDB
35.8 备份恢复
BGSAVE
cp /var/lib/redis/dump.rdb /backup/redis_dump.rdb
cp /backup/redis_dump.rdb /var/lib/redis/dump.rdb
# 重启Redis
36. SequoiaDB
36.1 技术原理
巨杉SequoiaDB是分布式文档数据库：
-
双引擎: 支持文档存储(JSON/BSON)和块存储(LOB)
-
高可用: 数据多副本，自动故障切换
36.2 创建集合空间和集合
var db = new Sequoiadb('localhost', 11810);
db.createCS('csdu_cs');
var cs = db.getCS('csdu_cs');
cs.createCL('csdu_transaction', { ShardingKey: { acct_no: 1 }, ShardingType: 'hash' });
36.3 插入测试数据
var bulk = cs.getCL('csdu_transaction').bulkInsert();
for (var i = 0; i < 30000000; i++) {
bulk.insert({
trans_id: 'TXN' + i.toString().padStart(15, '0'),
acct_no: '6222' + (i % 200000).toString().padStart(10, '0'),
trans_type: ['DEPOSIT', 'WITHDRAW', 'TRANSFER', 'PAYMENT', 'FEE'][i % 5],
trans_date: new Date(Date.now() - (i % 365) * 86400000),
trans_amt: Math.random() * 100000,
trans_detail: 'CSDUN_LARGE_DATA_'.repeat(500) + i,
csdun_tag: 'csdun_' + i
});
if (i % 10000 === 0) print('Inserted ' + i);
}
bulk.execute();
36.4 状态查询
db.snapshot(SDB_SNAP_DATABASES)
db.snapshot(SDB_SNAP_COLLECTIONS)
db.snapshot(SDB_SNAP_SESSIONS)
36.5 数据量统计
db.snapshot(SDB_SNAP_COLLECTIONS, { Name: 'csdu_cs.csdu_transaction' })
cs.getCL('csdu_transaction').count()
36.6 删除语句
cs.getCL('csdu_transaction').remove({ csdun_tag: { $regex: '^csdun_' } })
cs.dropCL('csdu_transaction')
db.dropCS('csdu_cs')
36.7 备份恢复
db.backup({ Path: '/backup/csdu', Overwrite: true, Description: 'csdu backup test' });
db.restore({ Path: '/backup/csdu', Overwrite: true });
37. TDengine
37.1 技术原理
涛思TDengine是时序数据库：
-
超级表(STable): 定义表结构，子表按标签自动创建
-
列式压缩: 时序数据高压缩比存储
37.2 创建数据库和超级表
CREATE DATABASE csdu_db KEEP 365 DAYS 10 BLOCKS 6;
USE csdu_db;
CREATE STABLE csdu_transaction (
ts TIMESTAMP, trans_amt DOUBLE, trans_detail NCHAR(2000), csdun_tag NCHAR(20)
) TAGS (acct_no NCHAR(32), trans_type NCHAR(10), branch_no NCHAR(20));
37.3 插入测试数据
CREATE TABLE txn_001 USING csdu_transaction TAGS ('622200000001', 'DEPOSIT', 'BR001');
INSERT INTO txn_001 VALUES (NOW, 10000.00, 'CSDUN_LARGE_DATA_TEST', 'csdun_001');
-- 使用taosBenchmark工具批量导入
taosBenchmark -f csdu_insert.json
37.4 状态查询
SHOW DATABASES;
SHOW STABLES;
SHOW TABLES;
SHOW DNODES;
SHOW MNODES;
37.5 归档查询
SHOW CREATE DATABASE csdu_db;
37.6 数据量统计
SELECT COUNT(*) FROM csdu_db.csdu_transaction;
SHOW CREATE TABLE csdu_db.csdu_transaction;
37.7 删除语句
DROP TABLE csdu_db.csdu_transaction;
DROP STABLE csdu_db.csdu_transaction;
DROP DATABASE csdu_db;
37.8 备份恢复
taosdump -h localhost -u root -p -D csdu_db -o /backup/csdu_db
taosdump -h localhost -u root -p -i /backup/csdu_db
38. 云RDS-MySQL（阿里云公有云/华为云公有云/阿里云专有云/华为云HCSO）
云托管MySQL服务。SQL同第3章MySQL。
38.1 备份恢复
# 阿里云RDS: 在DMS或控制台查看监控，创建快照/逻辑备份
# 华为云RDS: 在Console查看实例状态，创建自动/手动备份
# 或使用mysqldump从客户端导出
mysqldump -h rm-xxx.mysql.rds.aliyuncs.com -u root -p csdu_db > csdu_db.sql
mysql -h rm-xxx.mysql.rds.aliyuncs.com -u root -p csdu_db < csdu_db.sql
39. 云RDS-PostgreSQL（阿里云公有云）
云托管PostgreSQL服务。SQL同第5章PostgreSQL。
39.1 备份恢复
# 云控制台创建快照/逻辑备份
pg_dump -h pg-xxx.pg.rds.aliyuncs.com -U root -d csdu_db -F c -f csdu_db.dump
pg_restore -h pg-xxx.pg.rds.aliyuncs.com -U root -d csdu_db csdu_db.dump
40. ArkDB / ArkDB主备集群
兼容MySQL协议。SQL同第3章MySQL。
40.1 备份恢复
mysqldump -u root -p csdu_db > csdu_db.sql
mysql -u root -p csdu_db < csdu_db.sql
41. GBase 8c逻辑备份
GBase 8c基于openGauss，兼容PostgreSQL语法。SQL同第10章openGauss。
41.1 备份恢复
gbase8c_dump -h localhost -U gbase -d csdu_db -F c -f /backup/csdu_db.dump
gbase8c_restore -h localhost -U gbase -d csdu_db /backup/csdu_db.dump
附录：通用查询速查表
| 查询类型 | Oracle | SQL Server | MySQL/MariaDB | PostgreSQL/openGauss |
|---|---|---|---|---|
| 实例状态 | v$instance |
sys.databases |
SHOW STATUS |
pg_stat_activity |
| 归档状态 | v$database.log_mode |
recovery_model_desc |
SHOW MASTER STATUS |
SHOW archive_mode |
| 表大小 | dba_segments |
sys.allocation_units |
information_schema.tables |
pg_stat_user_tables |
| 慢SQL | v$sql |
dm_exec_query_stats |
events_statements_summary |
pg_stat_statements |
| 连接数 | v$session |
sys.sysprocesses |
SHOW PROCESSLIST |
pg_stat_activity |
文档版本: v1.0 编制日期: 2026-08-22 适用范围: 87种数据库平台/应用类型，41个分类 测试标识: csdun 业务场景
