我要评论前言
在数据库应用中，sql语句的性能直接决定了系统的响应速度和吞吐量。kingbasees作为一款高度兼容oracle的企业级数据库，提供了丰富的sql优化手段。下面我们就从索引优化、hint使用、参数调整、并行查询等核心维度，带您掌握实战化的sql优化技巧，附代码示例和操作建议。
一、索引优化：提升查询效率的基石
索引是一种有序的存储结构，也是一项极为重要的sql 优化手段，可以提高数据检索的速度。通过在表中的一个或多个列上创建索引，很多sql语句的执行效率可以得到极大的提高。
1.1 主流索引类型及适用场景
kingbasees提供8种索引类型，不同类型对应不同查询需求，核心类型及应用场景如下表所示：
| 索引类型 | 核心原理 | 适用场景 | 支持操作符 |
|---|---|---|---|
| btree索引 | 基于b+树结构，有序存储 | 范围查询、排序（order by/min/max）、等值查询 | >、<、>=、<=、=、in、like（前匹配） |
| hash索引 | 哈希表映射，快速定位等值数据 | 仅等值查询（=），不支持范围查询 | = |
| bitmap索引 | 位图存储，用bit位标记数据存在性 | 低基数列（如性别、状态）、多条件组合查询（and/or） | =、in |
| gin索引 | 通用倒排索引，存储（关键词+位置）映射 | 数组、全文检索、多值字段查询 | @@（全文匹配）、@>（包含） |
| brin索引 | 块范围索引，存储数据块的取值范围 | 有序数据（如时间序列日志），数据块内值连续 | >、<、>=、<= |
代码示例：btree索引优化范围查询
btree 是索引是最常见的索引类型，也是 kingbasees 的默认索引，采用 b+ 树 (n 叉排序树) 实现，由于树状结构每一层节点都有序列，因此非常适合用来做范围查询和优化排序操作。btree索引支持的操作符有>，<，>=，<=，=，in，like 等，同时，优化器也会优先选择btree来对orderby、min\max、mergejoin进行有序操作
-- 创建测试表
create table t_orders (
order_id int,
order_time timestamp,
amount numeric(10,2)
);
-- 插入100万条测试数据
insert into t_orders
values (generate_series(1,1000000),
current_timestamp - (random()*365)::int,
random()*1000);
-- 无索引时查询：全表扫描，耗时较长
explain analyze
select * from t_orders where order_time > '2024-01-01';
-- 执行结果：seq scan on t_orders (cost=0.00..22000.00 rows=300000 width=20) (actual time=0.03..500.12 ms)
-- 创建btree索引
create index idx_orders_time on t_orders using btree(order_time);
-- 有索引时查询：索引扫描，耗时显著降低
explain analyze
select * from t_orders where order_time > '2024-01-01';
-- 执行结果：index scan using idx_orders_time on t_orders (cost=0.43..8000.00 rows=300000 width=20) (actual time=0.05..80.36 ms)
1.2 索引使用实战技巧
表达式索引：解决函数/计算导致的索引失效
当查询条件包含函数或表达式时（如upper(name)），普通索引无法生效，需创建表达式索引：
-- 创建表达式索引（忽略大小写查询） create index idx_emp_upper_name on emp (upper(ename)); -- 查询时直接使用表达式，触发索引 explain analyze select * from emp where upper(ename) = 'smith';
联合索引：遵循“最左前缀原则”
联合索引是在建立在某个关系表上多列的索引，也叫复合索引。创建联合索引时，应该将最常被访问的列放在索引列表前面。当where子句中引用了联合索引中的所有列，或者前导列，联合索引可以加快检索速度。
-- 创建联合索引（order_time过滤性强，放在左侧） create index idx_orders_time_amount on t_orders (order_time, amount); -- 有效查询：命中联合索引（使用前导列order_time） select * from t_orders where order_time > '2024-01-01' and amount > 500; -- 无效查询：未使用前导列，无法命中索引 select * from t_orders where amount > 500;
like模糊查询优化：按匹配方式选择索引
- 前匹配（如
'abc%'）：使用btree索引（需指定text_pattern_ops）； - 后匹配（如
'%abc'）：通过reverse()函数转换为前匹配； - 中间匹配（如
'%abc%'）：使用trgm索引（依赖sys_trgm插件）。
-- 1. 前匹配：btree索引
create index idx_emp_name_pattern on emp (ename text_pattern_ops);
select * from emp where ename like 'sm%';
-- 2. 后匹配：reverse()表达式索引
create index idx_emp_name_reverse on emp (reverse(ename) collate "c");
select * from emp where reverse(ename) like reverse('%ith'); -- 等价于ename like '%ith'
-- 3. 中间匹配：trgm索引
create extension sys_trgm; -- 启用插件
create index idx_emp_name_trgm on emp using gin(ename gin_trgm_ops);
select * from emp where ename like '%mit%';
定期维护索引：避免索引膨胀
删除长期未使用的索引，定期执行vacuum和索引重建，解决索引页面稀疏问题：
-- 查看索引使用情况（idx_scan为0表示未使用） select relname as 表名, indexrelname as 索引名, idx_scan as 扫描次数 from sys_stat_user_indexes order by idx_scan; -- 重建索引（优化索引结构） reindex index idx_orders_time; -- 全表vacuum（释放删除数据的空间，确保覆盖索引生效） vacuum analyze t_orders;
二、hint：手动干预执行计划
kingbasees使用的是基于成本的优化器。优化器会估计sql语句的每个可能的执行计划的成本，然后选择成本最低的执行计划来执行。因为优化器不计算数据的某些属性，比如列之间的相关性，优化器有时选择的计划并不一定是最优的。
2.1 核心hint类型及用法
kingbasees支持多种hint，常用类型及示例如下：
| hint类型 | 功能 | 示例 |
|---|---|---|
| 扫描类型hint | 指定表的扫描方式（如索引扫描、顺序扫描） | /*+indexscan(t_orders idx_orders_time)*/ |
| 连接类型hint | 强制两表连接算法（嵌套循环、哈希连接等） | /*+hashjoin(t_orders t_customers)*/ |
| 连接顺序hint | 指定多表连接顺序 | /*+leading((t_customers t_orders) t_products)*/ |
| 并行hint | 开启并行查询及worker进程数 | /*+parallel(t_orders 4)*/ |
| rows hint | 修正优化器对结果行数的估算 | /*+rows(t_orders #1000)*/（强制估算为1000行） |
2.2 实战示例：hint优化多表连接
假设t_orders（100万行）与t_customers（10万行）连接查询，优化器误选嵌套循环连接（适合小表），需强制哈希连接：
-- 原始查询：优化器选择nested loop，耗时较长 explain analyze select o.order_id, c.cust_name from t_orders o join t_customers c on o.cust_id = c.cust_id where o.order_time > '2024-01-01'; -- 使用hint强制hashjoin，提升效率 explain analyze select /*+hashjoin(o c)*/ o.order_id, c.cust_name from t_orders o join t_customers c on o.cust_id = c.cust_id where o.order_time > '2024-01-01';
2.3 注意事项
- 启用hint需先配置
kingbase.conf：enable_hint = on； - hint仅作用于当前sql，避免全局修改参数影响其他查询；
- 优先通过更新统计信息（
analyze）解决计划问题，hint作为补充手段。
三、性能参数调整：优化数据库资源分配
通过调整kingbasees的核心参数，可适配硬件环境和业务负载，提升sql执行效率。
3.1 核心参数分类及优化建议
成本参数：匹配硬件性能
优化器内部使用基于成本的算法来获取总成本最低的访问路径。在计算成本的公式中，会用到一些定义好的参数因子，这些参数因子会影响到最终计算出出来的总成本。
成本参数决定优化器对i/o和cpu代价的评估，需根据硬件配置调整：
-- 1. 磁盘i/o优化（ssd磁盘可降低随机读成本） set random_page_cost = 2.0; -- 默认4.0，ssd建议2.0-3.0 set seq_page_cost = 0.5; -- 默认1.0，ssd建议0.5-1.0 -- 2. cpu性能优化（高性能cpu可降低cpu代价系数） set cpu_tuple_cost = 0.005; -- 默认0.01，cpu强可调至0.005 set cpu_operator_cost = 0.001; -- 默认0.0025，cpu强可调至0.001
内存参数：避免临时文件开销
数据比较多大的情况，主要和排序的数据有关系，排序数据越大，设置的就越大，比如 16g内存，tpch 测试，单用户 10g 规模数据，设置 2g 的 work_mem。数值以 kb 为单位的，缺省是1024(1mb)。索引扫描不用 work_mem。
-- 查看当前work_mem配置 show work_mem; -- 临时调整work_mem（应对复杂排序查询） set work_mem = '64mb'; -- 永久配置（kingbase.conf） work_mem = 32mb; -- 默认1mb，复杂查询建议32mb-128mb maintenance_work_mem = 256mb; -- 维护操作（如create index）内存，默认16mb
并行参数：利用多核cpu
开启并行查询可将单条sql的执行任务分配到多个cpu核心，适合大数据量查询：
-- 1. 全局并行配置（kingbase.conf） max_worker_processes = 16; -- 最大后台进程数，建议等于cpu核心数 max_parallel_workers = 8; -- 最大并行worker数 max_parallel_workers_per_gather = 4; -- 单查询最大并行worker数 -- 2. 临时开启并行查询（hint方式） explain analyze select /*+parallel(t_orders 4)*/ count(*) from t_orders where order_time > '2024-01-01';
四、并行查询：突破单核心性能瓶颈
kingbasees 能使用多核 cpu 来加速一个 sql 语句的执行时间，这种特性被称为并行查询。由于现实条件的限制或因为没有比并行查询计划更快的查询计划存在，很多查询并不能从并行查询获益。但是，对于那些可以从并行查询获益的查询来说，并行查询带来的速度提升是显著的。很多查询在使用并行查询时查询速度比之前快了超过两倍，有些查询是以前的四倍甚至更多的倍数。
4.1 并行查询适用场景
- 全表扫描或大表索引扫描（数据量>8mb，可通过
min_parallel_table_scan_size调整）； - 哈希连接、归并连接（多表大数据量连接）；
- 聚集操作（如
count、sum，需开启parallel_hashagg）。
4.2 实战示例：并行聚集查询
-- 创建大表（1000万行）
create table t_sales (
sale_id int,
sale_date date,
amount numeric(10,2)
);
insert into t_sales
values (generate_series(1,10000000),
current_date - (random()*365)::int,
random()*2000);
-- 关闭并行：单进程执行，耗时较长
set max_parallel_workers_per_gather = 0;
explain analyze
select sale_date, sum(amount)
from t_sales
group by sale_date;
-- 执行结果：hashaggregate (cost=200000.00..210000.00 rows=365 width=12) (actual time=1500.23..1800.56 ms)
-- 开启并行（4个worker）：多进程并行聚集，耗时降低
set max_parallel_workers_per_gather = 4;
explain analyze
select /*+parallel(t_sales 4) parallelhashagg*/
sale_date, sum(amount)
from t_sales
group by sale_date;
-- 执行结果：finalize hashaggregate (cost=120000.00..130000.00 rows=365 width=12) (actual time=500.12..600.34 ms)
六、总结
总的来说，kingbasees 的 sql 优化是一项系统性工程，需结合业务场景灵活运用索引优化、hint 干预、参数调整和并行查询等多种手段。实际操作中，通过执行计划定位瓶颈后，优先用合理建索引等结构性优化，再辅以参数与 hint 调优，同时定期维护统计信息与索引，即可高效应对高并发、大数据量场景。作为高度兼容 oracle 的企业级数据库，kingbasees 不仅提供丰富且实用的优化工具，还能保障业务平滑迁移，是支撑企业核心系统稳定运行的可靠选择。
到此这篇关于kingbasees数据库中索引和并行查询的sql优化方法实战指南的文章就介绍到这了,更多相关kingbasees sql优化内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论