KingbaseES 高级 SQL 调优：Query Mapping、物化视图与函数缓存

5. 变量交换与多表关联优化

-- 变量交换
CREATE QUERY RULE('var_swap', 'SELECT * FROM t2 WHERE t2.id < $1 AND t2.val = $2', 'SELECT * FROM t2 WHERE t2.id < $2 AND t2.val = $1', true, 'text');
SELECT * FROM t2 WHERE t2.id < 50 AND t2.val = 5;

-- 多表关联过滤优化
CREATE QUERY RULE('join_filter', 'SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < $1 AND t2.val = $2', 'SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < $1 AND t2.val = $2 AND t2.id = $2', true, 'text');
SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 30 AND t2.val = 5;

常用操作函数

二、物化视图：高频复杂查询的预计算缓存

物化视图将查询结果集存储起来，适合基表数据更新不频繁但查询频繁的场景。

核心原理与限制

• 存储方式：数据落地存储，支持手动全量刷新或增量刷新。
• 适用场景：统计报表查询、多表关联查询、外部表数据缓存。

实战代码示例

1. 创建物化视图

CREATE TABLE order_info(order_id INT PRIMARY KEY, user_id INT, amount NUMERIC, create_time TIMESTAMP);
CREATE TABLE user_info(user_id INT PRIMARY KEY, user_name TEXT, region TEXT);

INSERT INTO user_info SELECT GENERATE_SERIES(1, 1000), 'user_' || GENERATE_SERIES(1, 1000), 'region_' || (GENERATE_SERIES(1, 1000) % 10);
INSERT INTO order_info SELECT GENERATE_SERIES(1, 10000), (RANDOM() * 1000)::INT + 1, RANDOM() * 1000, NOW() - (RANDOM() * 30)::INTERVAL;

CREATE MATERIALIZED VIEW region_order_stats AS
SELECT u.region, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount 
FROM user_info u JOIN order_info o ON u.user_id = o.user_id 
GROUP BY u.region;

2. 刷新物化视图

-- 全量刷新
REFRESH MATERIALIZED VIEW region_order_stats;
-- 增量刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY region_order_stats;

3. 物化视图加索引

CREATE INDEX idx_region_order_stats_region ON region_order_stats(region);
SELECT * FROM region_order_stats WHERE region = 'region_0';

4. 性能对比

直接查询物化视图通常比实时关联计算快得多。

三、并行查询：利用多核 CPU 加速

KingbaseES 支持将单个 SQL 分给多个 Worker 进程并行执行。

核心配置与参数

max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
min_parallel_table_scan_size = 8MB

实战代码示例

1. 并行全表扫描

CREATE TABLE big_table(id INT PRIMARY KEY, content TEXT, create_time TIMESTAMP);
INSERT INTO big_table SELECT GENERATE_SERIES(1, 10000000), MD5(RANDOM()::TEXT), NOW() - (RANDOM() * 100)::INTERVAL;
ANALYZE big_table;

EXPLAIN ANALYZE SELECT /*+Parallel(big_table 4)* FROM big_table WHERE create_time > '2024-01-01';

2. 并行哈希连接

CREATE TABLE small_table(id INT PRIMARY KEY, category TEXT);
INSERT INTO small_table SELECT GENERATE_SERIES(1, 1000), 'cat_' || (GENERATE_SERIES(1, 1000) % 20);

EXPLAIN ANALYZE SELECT /*+Parallel(big_table 2)* b.*, s.category 
FROM big_table b JOIN small_table s ON b.id = s.id 
WHERE b.create_time > '2024-01-01';

3. 并行聚集计算

EXPLAIN ANALYZE SELECT /*+Parallel(order_info 3)* SUM(amount) AS total 
FROM order_info WHERE create_time BETWEEN '2024-01-01' AND '2024-04-01';

关键注意事项

• Worker 进程数需根据 CPU 核数合理设置，过多会增加通信开销。
• 小结果集不建议使用并行查询。

四、函数结果集缓存

对于 immutable 或 stable 类型的函数，KingbaseES 会缓存执行结果。

核心条件与配置

• 缓存条件：函数属性是 immutable 或 stable、返回单个值、入参不超过 16 个。
• 控制参数：

function_result_cache = on
function_cache_number = 1000

实战代码示例

1. 创建可缓存函数

CREATE OR REPLACE FUNCTION get_user_region(user_id INT) RETURNS TEXT AS $$
    SELECT region FROM user_info WHERE user_id = $1;
$$ LANGUAGE SQL STABLE;

SET function_result_cache = on;

2. 复用缓存结果

SELECT order_id, get_user_region(user_id) AS region1, get_user_region(user_id) AS region2 
FROM order_info WHERE order_id < 100;

3. 验证缓存效果

SELECT funcname, calls FROM sys_stat_user_functions WHERE funcname = 'get_user_region';

4. 临时禁用函数缓存

SET function_result_cache = off;

五、其他高级调优手段补充

1. 分区表执行计划优化

SET partition_table_limit = 30;

2. 逻辑优化规则（kdb_rbo 插件）

-- kingbase.conf 配置
shared_preload_libraries = 'kdb_rbo'

-- 启用 count(distinct) 优化
SET kdb_rbo.attribute_distinct_value_threshold = 0.1;
-- 启用子查询公共表达式合并
SET kdb_rbo.enable_merge_comm_expr = on;

3. SQL 监控与调优报告

CREATE EXTENSION sys_sqltune;

SELECT PERF.QUICK_TUNE_BY_SQL('SELECT * FROM big_table WHERE create_time > ''2024-01-01''');
SELECT PERF.QUICK_TUNE_BY_SQL_TO_FILE('SELECT * FROM big_table WHERE create_time > ''2024-01-01''', 'TEXT', '/tmp/sql_tune_report.txt');

六、调优手段选型指南

总结

KingbaseES 的高级 SQL 调优手段覆盖了语句改写、结果缓存到硬件资源利用的全链路。建议先通过 SQL 监控找到性能瓶颈，再根据数据特征选择对应的工具组合，以提升数据库吞吐量和响应速度。