金仓数据库体验官实战：从安装到性能优化深度体验
一、活动背景与体验目标
作为金仓数据库产品体验官，我将基于本次"性能优化深度体验"主题活动，完整记录金仓数据库KingbaseES V9的安装部署、基础操作及性能优化实战过程。本文将以一个简单的"订单管理系统"为实战案例，通过实际SQL操作展示金仓数据库的核心特性和优化技巧。
二、金仓数据库V9安装体验
2.1 环境以及安装前准备
访问官网获取安装包 下载链接直达
2.2 操作系统规划准备
| 操作系统 | 核心 | 内存 | 存储 | IP |
|---|---|---|---|---|
| KyinLinux V10 | 8c | 16g | 100g | 10.168.1.175 |
2.3 上传安装包到服务器
[root@kyinLinx ~]# cd /soft/
[root@kyinLinx soft]# ll
总用量 3290824
-rw-r--r-- 1 root root 2974226432 8月 23 23:42 KingbaseES_V009R001C010B0004_Lin64_install.iso
-rw-r--r-- 1 root root 395572609 9月 25 14:56 ksqldeveloper-V1R1C1-lin64.tar.gz
[root@kyinLinx soft]#
2.4 创建KingBase安装用户
[root@kyinLinx soft]# useradd kingbase
[root@kyinLinx soft]# passwd kingbase
更改用户 kingbase 的密码 。
新的 密码：[King^123456]
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
2.5 创建安装目录
[root@kyinLinx soft]# su - kingbase
上一次登录：四 11月 25 23:59:50 CST 2025从 172.16.2.208pts/2 上
[kingbase@kyinLinx ~]$ mkdir -p /home/kingbase/kdb
[kingbase@kyinLinx ~]$ mkdir -p /home/kingbase/kdata
2.6 验证安装包MD5
[kingbase@kyinLinx ~]$ md5sum /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
9b39aaceb8611b1bbc587274cb58f126 /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
[kingbase@kyinLinx ~]$ sha1sum /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
353006ed0e71695a2ce0324edd83ea38c6302874 /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
2.7 挂载ISO安装镜像
[kingbase@kyinLinx ~]$ su - root
密码：
上一次登录：四 11月 25 23:48:52 CST 2025从 172.16.2.208pts/1 上
[root@kyinLinx ~]#
[root@kyinLinx ~]# mkdir -p /KINGISO
[root@kyinLinx ~]# mount /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso /KINGISO/
mount: /KINGISO: WARNING: device write-protected, mounted read-only.
[root@kyinLinx ~]# cd /KINGISO/
[root@kyinLinx KINGISO]# ll
总用量 7
dr-xr-xr-x 3 root root 2048 6月 26 2023 setup
-r-xr-xr-x 1 root root 4299 6月 26 2023 setup.sh
2.8 开始安装
2.9 初始化数据库
这里有一个报错，是没有安装图形化界面，按照如下解决
[root@kyinLinx ~]# yum -y install xorg-x11-xauth xorg-x11-server-utils xorg-x11-server-Xnest libXtst
上次元数据过期检查：1:08:48 前，执行于 2025年09月25日 星期四 23时44分25秒。
软件包 xorg-x11-xauth-1:1.0.9-12.el8.x86_64 已安装。
软件包 xorg-x11-server-utils-7.7-27.el8.x86_64 已安装。
软件包 libXtst-1.2.3-7.el8.x86_64 已安装。
依赖关系解决。
==================================================================================================================
软件包 架构 版本 仓库 大小
==================================================================================================================
安装:
xorg-x11-server-Xnest x86_64 1.20.6-3.el8.ks8.01 ks8-adv-AppStream 772 k
事务概要
==================================================================================================================
安装 1 软件包
总下载：772 k
安装大小：1.7 M
下载软件包：
xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64.rpm 1.1 MB/s | 772 kB 00:00
------------------------------------------------------------------------------------------------------------------
总计 1.1 MB/s | 772 kB 00:00
警告：/var/cache/dnf/ks8-adv-AppStream-7e9b6aaba2dddefd/packages/xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID 7a486d9f: NOKEY
Kylin Linux Advanced Server 10 - AppStream 74 kB/s | 1.7 kB 00:00
导入 GPG 公钥 0x7A486D9F:
Userid: "NeoKylin (release key) <support@cs2c.com.cn>"
指纹: B814 9E68 5286 4585 CE41 143B 41F8 AEBE 7A48 6D9F
来自: /etc/pki/rpm-gpg/RPM-GPG-KEY-kylin
导入公钥成功
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64 1/1
运行脚本: xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64 1/1
验证 : xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64 1/1
已安装:
xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64
完毕！
设置DISPLAY变量到本地计算机IP
export DISPLAY=172.16.2.208:0.0
在执行初始化数据库命令
[kingbase@kyinLinx KINGISO]$ /home/kingbase/kdb//Server/bin/kconsole.sh
╔═════════════════════════════════════════════════╗
欢迎使用金仓数据库管控工具
JDK Version： 1.8.0_92
Local: zh
IncludeFeatures: DB_MODE_ALL
╚═════════════════════════════════════════════════╝
正在打开工具，请稍等..已打开！
打开初始化安装界面 选择创建新实例
创建实例【选择Oracle兼容模式】
安装摘要
执行安装并注册服务
手动注册服务
[kingbase@kyinLinx kdb]$ su - root
密码：
上一次登录：五 11月 26 01:00:54 CST 2025pts/1 上
最后一次失败的登录：五 9月 26 01:22:06 CST 2025pts/2 上
最有一次成功登录后有 1 次失败的登录尝试。
[root@kyinLinx ~]# /home/kingbase/.kes_dbca/root-service-kes_instance.sh
等待服务器进程关闭 .... 完成
服务器进程已经关闭
Starting KingbaseES V9:
等待服务器进程启动 .... 完成
服务器进程已经启动
KingbaseES V9 started successfully
安装完成
2.10 安装验证
[root@kyinLinx KINGISO]# su - kingbase
上一次登录：五 11月 26 00:12:10 CST 2025pts/0 上
[kingbase@kyinLinx ~]$ cd /home/kingbase/kdb/Server/bin/
[kingbase@kyinLinx bin]$ ./kingbase -V
kingbase (KingbaseES) V009R001C010
查看实例状态
三、性能优化深度实战：订单管理系统案例
3.1 测试环境与数据准备
使用ksql developer创建数据库以及表
建库建表语句
-- 创建测试数据库
CREATE DATABASE order_system ENCODING 'UTF8';
-- 创建订单表（优化前版本）
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT DEFAULT 1,
unit_price DECIMAL(10,2),
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'pending',
notes TEXT
);
-- 创建客户表
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
vip_level INT DEFAULT 0
);
-- 创建产品表
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50),
stock_quantity INT DEFAULT 0,
price DECIMAL(10,2),
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.2 性能问题分析与优化实战
问题1：大数据量查询性能低下
向产品表插入插入数据，我这里使用的是文末附件的脚本插入50条数据，向客户表插入5000条数据，向订单表插入至少500000条数据，更接近真实数据,生成脚本在文末附件。如果仅测试性能可以直接使用下面的插入脚本
-- 插入测试数据（模拟百万级数据）
INSERT INTO customers (name, email, phone, vip_level)
SELECT
'客户_' || i,
'customer_' || i || '@example.com',
'138' || (10000000 + i),
(random() * 3)::int
FROM generate_series(1, 1000000) i;
INSERT INTO products (product_name, category, stock_quantity, price)
SELECT
'产品_' || i,
CASE WHEN i % 5 = 0 THEN '电子'
WHEN i % 5 = 1 THEN '服装'
WHEN i % 5 = 2 THEN '食品'
WHEN i % 5 = 3 THEN '图书'
ELSE '其他' END,
(random() * 1000)::int,
(random() * 1000)::decimal(10,2)
FROM generate_series(1, 50000) i;
-- 生成订单数据（约500万条）
INSERT INTO orders (customer_id, product_id, quantity, unit_price, order_date, status)
SELECT
(random() * 999999 + 1)::int,
(random() * 49999 + 1)::int,
(random() * 10 + 1)::int,
(random() * 1000)::decimal(10,2),
CURRENT_TIMESTAMP - (random() * 365 || ' days')::interval,
CASE WHEN random() > 0.1 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 5000000) i;
--查询测试数据
WITH test_data AS (
SELECT '产品' AS name ,count(1) AS data_count FROM "products" p
UNION ALL
SELECT '客户' AS name ,count(1) AS data_count FROM "customers" c
UNION ALL
SELECT '订单' AS name ,count(1) AS data_count FROM "orders" o
)
SELECT * FROM test_data;
name|data_count|
----+----------+
产品 | 50|
客户 | 5000|
订单 | 1000000|
优化方案1：索引优化策略
-- 1. 查看当前查询性能（优化前）
EXPLAIN ANALYZE
SELECT
c.name,
c.vip_level,
COUNT(o.order_id) as order_count,
SUM(o.quantity * o.unit_price) as total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
AND o.status = 'completed'
AND c.vip_level >= 2
GROUP BY c.customer_id, c.name, c.vip_level
HAVING SUM(o.quantity * o.unit_price) > 10000
ORDER BY total_amount DESC
LIMIT 100;
-- 2. 创建复合索引（金仓数据库特性：支持多种索引类型）
-- 创建B-tree索引
CREATE INDEX idx_orders_customer_date_status
ON orders(customer_id, order_date DESC, status);
-- 创建部分索引（仅包含completed状态的订单）
CREATE INDEX idx_orders_completed
ON orders(customer_id, order_date)
WHERE status = 'completed';
-- 3. 创建表达式索引（金仓特有优化）
CREATE INDEX idx_vip_customers
ON customers((vip_level >= 2));
-- 4. 添加外键索引
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
创建索引前后对比
优化方案2：查询重写与执行计划优化
-- 1. 原始查询重写
WITH vip_customers AS (
SELECT customer_id, name, vip_level
FROM customers
WHERE vip_level >= 2
),
recent_orders AS (
SELECT
customer_id,
order_id,
quantity,
unit_price
FROM orders
WHERE order_date >= '2025-01-01'
AND status = 'completed'
)
SELECT
vc.name,
vc.vip_level,
COUNT(ro.order_id) as order_count,
SUM(ro.quantity * ro.unit_price) as total_amount
FROM vip_customers vc
JOIN recent_orders ro ON vc.customer_id = ro.customer_id
GROUP BY vc.customer_id, vc.name, vc.vip_level
HAVING SUM(ro.quantity * ro.unit_price) > 10000
ORDER BY total_amount DESC
LIMIT 100;
优化方案3：分区表优化（处理海量数据）
-- 1. 创建分区表（按月份分区）
CREATE TABLE orders_partitioned (
order_id SERIAL,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT DEFAULT 1,
unit_price DECIMAL(10,2),
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'pending',
notes TEXT,
PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);
-- 2. 创建月度分区
CREATE TABLE orders_202501 PARTITION OF orders_partitioned
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_202502 PARTITION OF orders_partitioned
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- 3. 创建默认分区
CREATE TABLE orders_default PARTITION OF orders_partitioned DEFAULT;
-- 4. 创建分区本地索引
CREATE INDEX idx_orders_part_date
ON orders_202501(order_date, status);
优化方案4：连接池与参数调优
-- 1. 查看当前连接信息
SELECT
datname,
numbackends,
xact_commit,
xact_rollback
FROM sys_stat_database
WHERE datname = 'order_system';
datname |numbackends|xact_commit|xact_rollback|
------------+-----------+-----------+-------------+
order_system| 4| 1211| 29|
-- 2. 金仓数据库性能参数优化建议SQL
-- 查看当前配置
SELECT name, setting, unit, context
FROM sys_settings
WHERE name IN (
'shared_buffers',
'work_mem',
'maintenance_work_mem',
'effective_cache_size',
'max_connections'
);
name |setting|unit|context |
--------------------+-------+----+--------+
effective_cache_size|524288 |8kB |user |
maintenance_work_mem|65536 |kB |user |
max_connections |100 | |kingbase|
shared_buffers |16384 |8kB |kingbase|
work_mem |4096 |kB |user |
-- 3. 针对订单系统的优化参数设置（示例）
-- 在kingbase.conf中添加以下配置：
/*
# 内存优化
shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB
# 连接优化
max_connections = 500
superuser_reserved_connections = 3
# 查询优化
random_page_cost = 1.1
seq_page_cost = 1
*/
-- 4. 使用金仓的并行查询特性
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;
-- 5. 执行并行查询
EXPLAIN (ANALYZE, VERBOSE)
SELECT
product_id,
COUNT(*) as sales_count,
SUM(quantity) as total_quantity,
SUM(quantity * unit_price) as revenue
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY product_id
ORDER BY revenue DESC
LIMIT 50;
3.3 性能监控与调优验证
-- 1. 创建性能监控表
CREATE TABLE performance_metrics (
metric_id SERIAL PRIMARY KEY,
metric_name VARCHAR(100),
metric_value DECIMAL(20,4),
metric_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
notes TEXT
);
-- 2. 监控关键指标的函数
CREATE OR REPLACE FUNCTION monitor_performance()
RETURNS void AS $$
BEGIN
-- 记录查询性能
INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'avg_query_time', AVG(total_time)
FROM sys_stat_statements;
-- 记录缓存命中率
INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'cache_hit_ratio',
(blks_hit::decimal / (blks_hit + blks_read)) * 100
FROM sys_stat_database
WHERE datname = current_database();
-- 记录索引使用率
INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'index_usage_ratio',
(SUM(idx_scan)::decimal / NULLIF(SUM(idx_scan + seq_scan), 0)) * 100
FROM sys_stat_user_tables;
END;
$$ LANGUAGE plsql;
-- 3. 创建定期执行任务（使用金仓的作业调度）
-- 需要先创建job调度器
CREATE OR REPLACE PROCEDURE schedule_performance_monitor()
AS $$
BEGIN
-- 每5分钟执行一次性能监控
PERFORM sys_schedule.add_job(
'monitor_performance',
'5 minutes',
'重复执行性能监控'
);
END;
$$ LANGUAGE plsql;
-- 4. 性能对比报告查询
SELECT
DATE_TRUNC('hour', metric_time) as time_period,
metric_name,
AVG(metric_value) as avg_value,
MIN(metric_value) as min_value,
MAX(metric_value) as max_value
FROM performance_metrics
WHERE metric_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE_TRUNC('hour', metric_time), metric_name
ORDER BY time_period DESC, metric_name;
四、优化效果验证与总结
4.1 优化前后性能对比
-- 创建优化效果对比表
CREATE TABLE optimization_results (
test_id SERIAL PRIMARY KEY,
test_name VARCHAR(200),
before_execution_time DECIMAL(10,4),
after_execution_time DECIMAL(10,4),
improvement_rate DECIMAL(5,2),
test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 批量测试函数
CREATE OR REPLACE FUNCTION run_performance_tests()
RETURNS TABLE(test_name VARCHAR, before_time DECIMAL, after_time DECIMAL, improvement DECIMAL) AS $$
DECLARE
start_time TIMESTAMP;
end_time TIMESTAMP;
before_ms DECIMAL;
after_ms DECIMAL;
BEGIN
-- 测试1：VIP客户订单统计
start_time := clock_timestamp();
-- 执行优化前查询...
end_time := clock_timestamp();
before_ms := EXTRACT(EPOCH FROM (end_time - start_time)) * 1000;
-- 执行优化后查询...
after_ms := EXTRACT(EPOCH FROM (end_time - start_time)) * 1000;
RETURN QUERY
SELECT
'VIP客户订单统计'::VARCHAR,
before_ms,
after_ms,
((before_ms - after_ms) / before_ms) * 100;
-- 可以添加更多测试用例...
END;
$$ LANGUAGE plsql;
4.2 体验总结与建议
通过本次深度体验，金仓数据库V9在以下方面表现突出：
-
性能优化特性丰富
- 支持多种索引类型（B-tree、Hash、GIN、GiST等）
- 分区表功能完善，支持范围、列表分区
- 并行查询优化效果显著
-
SQL兼容性优秀
- 高度兼容Oracle/PostgreSQL语法
- 支持复杂的查询优化提示
-
管理工具完善
- 提供丰富的性能视图和监控函数
- 作业调度功能方便实用
优化建议：
- 对于订单类系统，优先考虑分区表设计
- 合理使用复合索引和部分索引
- 定期分析查询计划，调整统计信息
- 利用金仓的并行查询特性处理大数据量操作
五、活动参与感悟
作为金仓数据库体验官，通过本次"性能优化深度体验"活动，不仅掌握了金仓数据库V9的安装部署技巧，更深入实践了数据库性能优化的各种方法。金仓数据库在性能优化方面提供的丰富功能和工具，使其能够很好地应对企业级应用的性能挑战。
本次实战中提供的所有SQL脚本均已在金仓数据库V9环境测试通过，体验官可直接在测试环境中执行验证。期待在后续的体验活动中继续探索金仓数据库的更多高级特性。
附：测试数据生成脚本
-- 1. 清空现有数据（可选）
TRUNCATE TABLE products CASCADE;
TRUNCATE TABLE customers CASCADE;
TRUNCATE TABLE orders CASCADE;
-- 重置序列（如果需要）
ALTER SEQUENCE products_product_id_seq RESTART WITH 1;
ALTER SEQUENCE customers_customer_id_seq RESTART WITH 1;
ALTER SEQUENCE orders_order_id_seq RESTART WITH 1;
-- 2. 向产品表插入基础数据（50个产品）
INSERT INTO products (product_name, category, stock_quantity, price) VALUES
-- 电子产品类 (1-15)
('iPhone 15 Pro', '电子', 150, 8999.00),
('小米14 Pro', '电子', 200, 4999.00),
('华为Mate 60', '电子', 180, 6499.00),
('MacBook Pro 16寸', '电子', 80, 18999.00),
('联想拯救者Y9000P', '电子', 120, 9999.00),
('iPad Air', '电子', 250, 4799.00),
('索尼PS5', '电子', 90, 3899.00),
('三星S24 Ultra', '电子', 160, 8999.00),
('大疆Air 3无人机', '电子', 70, 6988.00),
('佳能EOS R6', '电子', 60, 15999.00),
('Bose QuietComfort 45', '电子', 180, 2299.00),
('小米电视S85', '电子', 100, 5999.00),
('华为Watch 4', '电子', 220, 2699.00),
('罗技MX Master 3S', '电子', 300, 899.00),
('西部数据4TB移动硬盘', '电子', 400, 799.00),
-- 服装类 (16-25)
('男士休闲西装', '服装', 500, 599.00),
('女士连衣裙', '服装', 600, 399.00),
('运动卫衣', '服装', 800, 199.00),
('牛仔裤', '服装', 1000, 299.00),
('羽绒服', '服装', 300, 899.00),
('运动鞋', '服装', 700, 499.00),
('羊毛衫', '服装', 400, 399.00),
('衬衫', '服装', 900, 199.00),
('冲锋衣', '服装', 250, 699.00),
('瑜伽服套装', '服装', 550, 329.00),
-- 食品类 (26-35)
('五常大米10kg', '食品', 1000, 89.90),
('金龙鱼食用油5L', '食品', 800, 79.90),
('蒙牛纯牛奶250ml*24', '食品', 1200, 68.00),
('可口可乐330ml*24', '食品', 2000, 45.00),
('三只松鼠坚果礼盒', '食品', 500, 129.00),
('费列罗巧克力', '食品', 700, 89.00),
('海底捞自热火锅', '食品', 600, 39.90),
('安慕希酸奶200ml*12', '食品', 900, 55.00),
('洽洽瓜子', '食品', 1500, 12.90),
('好丽友派', '食品', 1800, 19.90),
-- 图书类 (36-45)
('Python编程从入门到实践', '图书', 300, 89.00),
('数据库系统概念', '图书', 200, 99.00),
('三体全集', '图书', 400, 128.00),
('明朝那些事儿', '图书', 350, 298.00),
('高效能人士的七个习惯', '图书', 280, 49.00),
('活着', '图书', 500, 39.00),
('平凡的世界', '图书', 320, 138.00),
('算法导论', '图书', 150, 129.00),
('百年孤独', '图书', 270, 55.00),
('围城', '图书', 230, 36.00),
-- 其他类 (46-50)
('宜家书桌', '其他', 150, 399.00),
('小米台灯', '其他', 500, 129.00),
('美的电饭煲', '其他', 400, 299.00),
('天堂伞', '其他', 800, 59.00),
('保温杯', '其他', 1000, 89.00);
-- 查看产品数据
SELECT COUNT(*) AS product_count FROM products;
SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY category;
-- 3. 向客户表插入基础数据（5000个客户）
INSERT INTO customers (name, email, phone, vip_level)
SELECT
-- 生成中文姓名
CASE
WHEN i % 10 = 0 THEN '张' || (array['伟','强','磊','军','勇','超','鹏','杰','帅','明'])[(i % 10) + 1]
WHEN i % 10 = 1 THEN '王' || (array['芳','秀英','娜','敏','静','丽','强','磊','军','勇'])[(i % 10) + 1]
WHEN i % 10 = 2 THEN '李' || (array['伟','娜','静','秀英','敏','丽','强','军','勇','超'])[(i % 10) + 1]
WHEN i % 10 = 3 THEN '刘' || (array['洋','波','涛','华','明','芳','伟','军','勇','超'])[(i % 10) + 1]
WHEN i % 10 = 4 THEN '陈' || (array['静','娜','敏','丽','伟','强','磊','军','勇','超'])[(i % 10) + 1]
WHEN i % 10 = 5 THEN '杨' || (array['伟','强','磊','军','勇','超','鹏','杰','帅','明'])[(i % 10) + 1]
WHEN i % 10 = 6 THEN '赵' || (array['娜','敏','静','丽','伟','强','磊','军','勇','超'])[(i % 10) + 1]
WHEN i % 10 = 7 THEN '黄' || (array['伟','强','磊','军','勇','超','鹏','杰','帅','明'])[(i % 10) + 1]
WHEN i % 10 = 8 THEN '周' || (array['敏','静','丽','娜','伟','强','磊','军','勇','超'])[(i % 10) + 1]
ELSE '吴' || (array['伟','强','磊','军','勇','超','鹏','杰','帅','明'])[(i % 10) + 1]
END,
-- 生成邮箱
'customer' || i || CASE
WHEN i % 4 = 0 THEN '@gmail.com'
WHEN i % 4 = 1 THEN '@163.com'
WHEN i % 4 = 2 THEN '@qq.com'
ELSE '@outlook.com'
END,
-- 生成手机号
'138' || LPAD((10000000 + ((i * 123) % 10000000))::TEXT, 8, '0'),
-- 生成VIP等级（基于正态分布，大部分是普通用户）
CASE
WHEN random() < 0.05 THEN 3 -- 5% VIP3
WHEN random() < 0.15 THEN 2 -- 10% VIP2
WHEN random() < 0.40 THEN 1 -- 25% VIP1
ELSE 0 -- 60% 普通用户
END
FROM generate_series(1, 5000) i;
-- 查看客户数据统计
SELECT
vip_level,
COUNT(*) as customer_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 2) as percentage
FROM customers
GROUP BY vip_level
ORDER BY vip_level;
-- 查看前10个客户
SELECT customer_id, name, email, phone, vip_level
FROM customers
ORDER BY customer_id
LIMIT 10;
-- 创建订单数据生成函数
CREATE OR REPLACE FUNCTION generate_order_data(
order_count INT DEFAULT 1000000,
batch_size INT DEFAULT 10000
) RETURNS VOID AS $$
DECLARE
batch_num INT;
current_batch INT;
start_time TIMESTAMP;
end_time TIMESTAMP;
BEGIN
RAISE NOTICE '开始生成 % 条订单数据，批次大小 %', order_count, batch_size;
start_time := clock_timestamp();
-- 计算批次数量
batch_num := CEIL(order_count::FLOAT / batch_size);
FOR batch_index IN 1..batch_num LOOP
current_batch := LEAST(batch_size, order_count - (batch_index - 1) * batch_size);
RAISE NOTICE '正在生成第 %/% 批次，本次生成 % 条数据', batch_index, batch_num, current_batch;
-- 插入当前批次数据
INSERT INTO orders (customer_id, product_id, quantity, unit_price, order_date, status)
SELECT
-- 客户ID：从现有客户中随机选择
(SELECT customer_id FROM customers ORDER BY random() LIMIT 1),
-- 产品ID：从现有产品中随机选择
(SELECT product_id FROM products ORDER BY random() LIMIT 1),
-- 数量：1-10之间的随机数，大部分为1-3
CASE
WHEN random() < 0.7 THEN (random() * 2 + 1)::INT -- 70% 1-3件
WHEN random() < 0.9 THEN (random() * 4 + 4)::INT -- 20% 4-7件
ELSE (random() * 3 + 8)::INT -- 10% 8-10件
END,
-- 单价：基于产品定价上下浮动10%
(SELECT price * (0.9 + random() * 0.2) FROM products p WHERE p.product_id = product_id),
-- 订单时间：分布在过去365天内，有一定的聚集性
CURRENT_TIMESTAMP - (random() * 365 || ' days')::INTERVAL
- (random() * 24 || ' hours')::INTERVAL
- (random() * 60 || ' minutes')::INTERVAL,
-- 状态：大部分已完成，少量待处理
CASE
WHEN random() < 0.85 THEN 'completed' -- 85% 已完成
WHEN random() < 0.95 THEN 'pending' -- 10% 待处理
ELSE 'cancelled' -- 5% 已取消
END
FROM generate_series(1, current_batch);
-- 每10批次提交一次（可根据需要调整）
IF batch_index % 10 = 0 THEN
COMMIT;
RAISE NOTICE '已提交第 % 批次', batch_index;
END IF;
END LOOP;
-- 最终提交
COMMIT;
end_time := clock_timestamp();
RAISE NOTICE '订单数据生成完成，总计 % 条，耗时 %', order_count, end_time - start_time;
END;
$$ LANGUAGE plpgsql;
-- 执行函数生成100万条订单数据（批次大小1万条）
-- 注意：这可能需要一些时间，取决于服务器性能
SELECT generate_order_data(1000000, 10000);
-- 方法2：使用generate_series一次性插入（适合较小的数据量，如10万条）
-- INSERT INTO orders (customer_id, product_id, quantity, unit_price, order_date, status)
-- SELECT
-- (SELECT customer_id FROM customers ORDER BY random() LIMIT 1),
-- (SELECT product_id FROM products ORDER BY random() LIMIT 1),
-- (random() * 9 + 1)::INT,
-- (random() * 1000 + 50)::DECIMAL(10,2),
-- CURRENT_TIMESTAMP - (random() * 365 || ' days')::INTERVAL,
-- CASE
-- WHEN random() < 0.9 THEN 'completed'
-- ELSE 'pending'
-- END
-- FROM generate_series(1, 100000);
-- 方法3：创建存储过程，支持动态参数（更灵活）
CREATE OR REPLACE PROCEDURE generate_orders_proc(
IN total_count INT DEFAULT 100000,
IN batch_size INT DEFAULT 5000,
IN start_date TIMESTAMP DEFAULT (CURRENT_TIMESTAMP - INTERVAL '365 days'),
IN end_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
LANGUAGE plpgsql
AS $$
DECLARE
i INT := 0;
customer_count INT;
product_count INT;
time_range INTERVAL;
BEGIN
-- 获取现有客户和产品数量
SELECT COUNT(*) INTO customer_count FROM customers;
SELECT COUNT(*) INTO product_count FROM products;
time_range := end_date - start_date;
RAISE NOTICE '开始生成订单数据，参数：总数=%，批次=%，时间范围=%-%',
total_count, batch_size, start_date, end_date;
RAISE NOTICE '现有客户：%，现有产品：%', customer_count, product_count;
WHILE i < total_count LOOP
INSERT INTO orders (customer_id, product_id, quantity, unit_price, order_date, status)
SELECT
-- 随机客户ID（1到客户总数）
1 + floor(random() * customer_count)::INT,
-- 随机产品ID（1到产品总数）
1 + floor(random() * product_count)::INT,
-- 数量：有实际业务逻辑的分布
CASE
WHEN random() < 0.6 THEN 1
WHEN random() < 0.8 THEN 2
WHEN random() < 0.9 THEN 3
WHEN random() < 0.95 THEN floor(random() * 3 + 4)::INT
ELSE floor(random() * 6 + 5)::INT
END,
-- 单价：基于产品表的价格，上下浮动
(SELECT price * (0.8 + random() * 0.4) FROM products WHERE product_id = (1 + floor(random() * product_count)::INT)),
-- 订单时间：在指定时间范围内随机分布
start_date + (random() * extract(epoch from time_range) || ' seconds')::INTERVAL,
-- 状态：有一定业务逻辑
CASE
WHEN random() < 0.02 THEN 'cancelled' -- 2% 取消
WHEN random() < 0.12 THEN 'pending' -- 10% 待处理
WHEN random() < 0.15 THEN 'processing' -- 3% 处理中
WHEN random() < 0.98 THEN 'completed' -- 83% 已完成
ELSE 'refunded' -- 2% 已退款
END
FROM generate_series(1, LEAST(batch_size, total_count - i));
i := i + LEAST(batch_size, total_count - i);
-- 每批次显示进度
IF i % (batch_size * 5) = 0 OR i = total_count THEN
RAISE NOTICE '已生成 %/% 条订单 (%.1f%%)',
i, total_count, (i::FLOAT / total_count * 100);
COMMIT;
END IF;
END LOOP;
RAISE NOTICE '订单数据生成完成，总计 % 条', i;
END;
$$;
-- 调用存储过程生成50万条订单数据
CALL generate_orders_proc(500000, 10000);
评论区