一、环境准备与数据构建
1.1 安装部署KingbaseES
首先从官网下载KingbaseES V9R2C13安装包，配置环境变量并初始化数据库：
# 创建安装目录
mkdir -p /data/kdb
chown -R kingbase:kingbase /data/kdb
# 设置环境变量
export PATH=/data/kdb/V9/Server/bin:$PATH
export KINGBASE_DATA=/data/kdb/V9/data
# 挂载镜像并安装
mount KingbaseES_V009R002C013B0005_Lin64_install.iso /mnt
cd /mnt
./setup.sh
1.2 创建测试数据
构建订单管理系统测试环境，模拟真实业务场景：
– 创建测试数据库
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);
-- 插入测试数据（模拟数据倾斜）
INSERT INTO customers
SELECT generate_series(1, 5000),
'客户_' || generate_series(1, 5000),
'cus' || generate_series(1, 5000) || '@company.com',
'138' || generate_series(1000001, 1005000),
CURRENT_TIMESTAMP,
(random() * 5) ::int;
INSERT INTO products
SELECT generate_series(1, 100),
'产品_' || generate_series(1, 100),
CASE
WHEN random() < 0.3 THEN
'电子'
WHEN random() < 0.6 THEN
'服装'
ELSE
'食品'
END,
(random() * 1000) ::int,
(random() * 1000 + 10) ::NUMERIC(10, 2),
CURRENT_TIMESTAMP;
– 插入50万条订单数据
INSERT INTO orders
SELECT generate_series(1, 500000),
(random() * 5000 + 1) ::int,
(random() * 100 + 1) ::int,
(random() * 10 + 1) ::int,
(random() * 1000 + 10) ::numeric(10, 2),
CURRENT_TIMESTAMP - (random() * 365) ::int * interval '1 day',
CASE
WHEN random() < 0.8 THEN
'completed'
ELSE
'pending'
END,
NULL;
二、性能监控工具配置
2.1 启用性能监控插件
在kingbase.conf中配置性能监控参数：
# 加载性能监控插件shared_preload_libraries = ‘plsql, sys_stat_statements, sys_sqltune, sys_kwr’
# 开启SQL跟踪
track_sql = on
sys_stat_statements.track = ‘top’
track_io_timing = on
track_functions = ‘all’
# 开启KWR自动快照
sys_kwr.enable = on
# 每30分钟采集一次快照
sys_kwr.interval = 30
# 保留15天历史数据
sys_kwr.history_days = 15
重启数据库后创建插件：
– 创建性能监控插件
CREATE EXTENSION sys_stat_statements;
CREATE EXTENSION sys_sqltune;
CREATE EXTENSION sys_kwr;
2.2 清空历史统计信息
– 清空统计信息
SELECT sys_stat_statements_reset();
三、SQL调优建议器实战
3.1 识别性能问题SQL
执行一个典型的业务查询，观察性能表现：
– 查询VIP客户的订单统计
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 c.vip_level >= 3
AND o.order_date >= '2025-01-01'
AND o.status = 'completed'
GROUP BY c.customer_id, c.name, c.vip_level
ORDER BY total_amount DESC LIMIT 100;
执行计划显示全表扫描，耗时约12.8秒。
3.2 使用SQL调优建议器
通过QUICK_TUNE函数获取优化建议：
– 获取SQL调优建议
SELECT DBMS_SQLTUNE.QUICK_TUNE
('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 c.vip_level >= 3
AND o.order_date >= ''2025-01-01''
AND o.status = ''completed''
GROUP BY c.customer_id, c.name, c.vip_level
ORDER BY total_amount DESC
LIMIT 100');
调优建议器输出：
建议在orders表的customer_id、order_date、status字段上创建复合索引
建议在customers表的vip_level字段上创建索引
建议收集统计信息
3.3 执行优化建议
– 创建建议的索引
CREATE INDEX idx_orders_customer_date_status ON orders(customer_id, order_date, status);
CREATE INDEX idx_customers_vip_level ON customers(vip_level);
– 收集统计信息
ANALYZE orders;
ANALYZE customers;
– 再次执行查询验证效果
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 c.vip_level >= 3
AND o.order_date >= '2025-01-01'
AND o.status = 'completed' GROUP BY c.customer_id, c.name, c.vip_level ORDER BY total_amount DESC LIMIT 100;
优化后查询耗时降至0.8秒，性能提升约16倍。
四、数据库时间模型分析
4.1 查看TOP SQL
– 查看按数据库时间排序的TOP SQL
SELECT queryid, query, calls, total_plan_time, mean_plan_time, ROWS
FROM sys_stat_statements
WHERE query NOT LIKE '%sys_stat_statements%'
ORDER BY total_plan_time DESC
LIMIT 10;
4.2 分析数据库时间分解
– 查看数据库时间组成
SELECT *
FROM sys_stat_dbtime
WHERE metric IN ('DB Time', 'DB CPU', 'Parse Time', 'Plan Time', 'Execute Time')
ORDER BY total_time DESC;
4.3 查看具体SQL的时间分解
– 获取问题SQL的queryid
SELECT queryid, query
FROM sys_stat_statements
WHERE query LIKE '%customers c JOIN orders o%' LIMIT 5;
– 查看SQL时间分解
SELECT queryid, query, db_time, parse_time, exec_time
FROM sys_stat_sql
WHERE queryid = -4865733531632946068;
– 替换为实际queryid
五、KWR性能报告生成
5.1 创建KWR快照
– 创建快照1（优化前）
SELECT perf.create_snapshot();
– 执行测试SQL
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 c.vip_level >= 3
AND o.order_date >= '2025-01-01'
AND o.status = 'completed'
GROUP BY c.customer_id, c.name, c.vip_level
ORDER BY total_amount DESC
LIMIT 100;
– 创建快照2（优化后）
SELECT perf.create_snapshot();
5.2 生成KWR报告
– 查看快照列表
SELECT snap_id, snap_time, sess_count,snap_version FROM perf.kwr_snapshots ORDER BY snap_id DESC;
– 生成HTML格式KWR报告
SELECT perf.kwr_report(1, 2, 'html');
– 将报告保存到文件
SELECT perf.kwr_report_to_file(1, 2, 'html', '/tmp/kwr_report.html');
KWR报告显示：
优化前：DB Time 12.8秒，CPU利用率45%，I/O等待占比42%
优化后：DB Time 0.8秒，CPU利用率75%，I/O等待占比12%
六、NOT IN子链接优化
6.1 测试NOT IN性能
– 查询未下过订单的客户
EXPLAIN ANALYZE
SELECT c.customer_id, c.name, c.email
FROM customers c
WHERE c.customer_id NOT IN (SELECT DISTINCT customer_id
FROM orders)
AND c.vip_level >= 1;
执行计划显示使用Nested Loop Anti Join，耗时约8.5秒。
6.2 使用NOT EXISTS优化
– 改写为NOT EXISTS
EXPLAIN ANALYZE
SELECT c.customer_id, c.name, c.email
FROM customers c
WHERE NOT EXISTS ( SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
)
AND c.vip_level >= 1;
优化后使用Hash Anti Join，耗时降至1.2秒，性能提升约7倍。
七、OR条件转UNION ALL优化
7.1 测试OR条件性能
– 查询特定类别或价格范围的产品
EXPLAIN ANALYZE
SELECT product_id, product_name, category, price
FROM products
WHERE category = '电子'
OR price > 500;
执行计划显示全表扫描，耗时约0.5秒。
7.2 改写为UNION ALL
– 改写为UNION ALL
EXPLAIN ANALYZE
SELECT product_id, product_name, category, price
FROM products
WHERE category = '电子'
UNION ALL SELECT product_id, product_name, category, price
FROM products
WHERE price > 500
AND category != '电子';
优化后使用索引扫描，耗时降至0.1秒，性能提升5倍。
八、JDBC元信息查询优化
8.1 传统JDBC元信息查询
// 传统方式查询100张表的元信息（耗时约8秒）
DatabaseMetaData metaData = connection.getMetaData();
for (String tableName : tableNames)
{
// 处理元信息
ResultSet rs = metaData.getColumns(null, null, tableName, null);
}
8.2 使用金仓批量接口
// 使用金仓批量查询（耗时约1.2秒）
Properties props = new Properties();
props.setProperty("metadataQueryOptimize", "true"); // 启用元信息查询优化
DatabaseMetaData kbMetaData = (DatabaseMetaData) connection.getMetaData();
// 处理元信息
ResultSet rs = kbMetaData.getBatchColumns(null, null, tableNames.toArray(new String[0]), null);
批量查询性能提升约6.7倍。
九、NDP批量DML性能测试
9.1 传统JDBC批量插入
// 传统批量插入10000条记录（耗时约12秒）
String sql = "INSERT INTO orders (customer_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
pstmt.setInt(1, random.nextInt(5000) + 1);
pstmt.setInt(2, random.nextInt(100) + 1);
pstmt.setInt(3, random.nextInt(10) + 1);
pstmt.setBigDecimal(4, new BigDecimal(random.nextDouble() * 1000 + 10));
pstmt.addBatch();
}
pstmt.executeBatch();
9.2 使用NDP批量插入
// NDP批量插入10000条记录（耗时约2秒）
String url = "jdbc:kingbase8://localhost:54321/test?ndp=true";
Connection ndpConn = DriverManager.getConnection(url, "system", "123456");
PreparedStatement ndpStmt = (PreparedStatement) ndpConn.prepareStatement("INSERT INTO orders (customer_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)");
for (int i = 0; i < 10000; i++) {
ndpStmt.setInt(1, random.nextInt(5000) + 1);
ndpStmt.setInt(2, random.nextInt(100) + 1);
ndpStmt.setInt(3, random.nextInt(10) + 1);
ndpStmt.setBigDecimal(4, new BigDecimal(random.nextDouble() * 1000 + 10));
ndpStmt.addBatch();
}
ndpStmt.executeBatch();
NDP批量插入性能提升约6倍。
十、总结与建议
通过本次深度体验，验证了金仓数据库在性能优化方面的多项特性：
SQL调优建议器：能够自动识别性能问题并提供准确的优化建议，包括索引建议、统计信息建议和SQL改写建议。
数据库时间模型：通过sys_stat_statements和sys_stat_sql等动态性能视图，可以精准定位性能瓶颈。
KWR性能报告：提供全面的性能监控和诊断能力，支持历史性能对比分析。
NOT IN子链接优化：自动将NOT IN子查询转换为Hash Anti Join，性能提升显著。
OR转UNION ALL：通过改写SQL语句，充分利用索引，提升查询效率。
JDBC元信息查询优化：批量查询接口大幅减少元信息查询时间。
NDP批量DML：通过减少网络交互次数，显著提升批量数据操作性能。
优化建议：
定期使用SQL调优建议器分析TOP SQL
开启KWR自动快照，定期生成性能报告
对于大数据量查询，优先考虑使用NOT EXISTS替代NOT IN
在应用开发中使用NDP协议进行批量数据操作
合理配置数据库参数，如shared_buffers、work_mem等
金仓数据库在性能优化方面提供了完整的工具链和优化策略，能够有效解决企业级应用中的性能瓶颈问题。
