一、環境準備與數據構建
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等
金倉數據庫在性能優化方面提供了完整的工具鏈和優化策略，能夠有效解決企業級應用中的性能瓶頸問題。
