1. SQL语句优化:从源头提升查询效率
金仓数据库作为企业级关系型数据库,SQL语句的编写质量直接影响查询性能。我在实际项目中见过太多因为SQL写法不当导致的性能问题,有些查询优化前后性能差距能达到几十倍。下面分享几个最实用的优化技巧。
1.1 基础语法优化实战
避免全表扫描是最基本的原则。有一次排查一个慢查询,发现开发人员写了SELECT * FROM 百万级用户表 WHERE status=1,这个查询竟然用了12秒。改成SELECT id,name FROM 用户表 WHERE status=1后,由于只需要读取这两列,时间直接降到0.8秒。
隐式类型转换也是个隐形杀手。有次发现一个本该走索引的查询却做了全表扫描,原来是因为字段是整型但查询条件用了字符串:WHERE user_id='123'。改成WHERE user_id=123后,执行时间从3秒降到0.05秒。
子查询优化也有讲究。我遇到过这样一个案例:
-- 优化前:相关子查询
SELECT * FROM orders
WHERE EXISTS (SELECT 1 FROM users WHERE users.id=orders.user_id AND users.vip=1)
改成JOIN后性能提升明显:
-- 优化后:JOIN查询
SELECT orders.* FROM orders
JOIN users ON users.id=orders.user_id
WHERE users.vip=1
1.2 高级优化规则应用
金仓的kdb_rbo插件提供了很多自动优化规则。比如count(distinct)优化,通过调整kdb_rbo.attribute_distinct_value_threshold参数(默认0.1),对高基数列的去重计数性能可提升3-5倍。
谓词下推是另一个实用功能。优化器会自动将WHERE条件推到数据源层面,减少中间结果集。实测一个多表关联查询,启用谓词下推后内存使用减少40%,执行时间缩短35%。
外连接转内连接的优化也很有意思。当WHERE子句包含"空值拒绝条件"时,比如WHERE 外联字段 IS NOT NULL,优化器会自动把外连接转为内连接。我在一个报表查询中应用这个优化,性能提升了60%。
1.3 实际案例与效果对比
最近优化过一个电商系统的订单查询,原始SQL是这样的:
SELECT * FROM orders o
WHERE o.create_time > '2023-01-01'
AND EXISTS (
SELECT 1 FROM order_items i
WHERE i.order_id=o.id
AND i.product_id IN (1001,1002,1003)
)
ORDER BY o.create_time DESC
LIMIT 100
优化后的版本:
SELECT o.id,o.order_no,o.create_time
FROM orders o
JOIN order_items i ON i.order_id=o.id
WHERE o.create_time > '2023-01-01'
AND i.product_id IN (1001,1002,1003)
GROUP BY o.id,o.order_no,o.create_time -- 去重
ORDER BY o.create_time DESC
LIMIT 100
优化前后对比:
- 执行时间:从2.1秒降到0.15秒
- 逻辑读:从12万降到8500
- 内存使用:从1.2GB降到280MB
2. 执行计划优化:让优化器更智能
执行计划是SQL执行的路线图,优化执行计划相当于为查询选择最优路径。我处理过的一个典型案例是,同样的SQL在不同时段性能差异很大,这就是执行计划不稳定的典型表现。
2.1 统计信息维护策略
统计信息不准会导致优化器"误判"。有次一个查询突然变慢,查看执行计划发现预估行数是100,实际是10万行。执行ANALYZE VERBOSE 表名更新统计信息后,查询恢复正常。
自动收集策略也很重要。对于频繁变更的表,我通常这样配置:
ALTER TABLE 订单表 SET (
autovacuum_analyze_threshold = 50,
autovacuum_analyze_scale_factor = 0.05
);
这样当50行发生变化或5%的数据变化时就会触发统计信息更新。
多列关联查询需要扩展统计信息。比如WHERE 省份='浙江' AND 城市='杭州',单列统计可能不准,可以创建依赖统计:
CREATE STATISTICS 省城依赖 (dependencies) ON 省份,城市 FROM 客户表;
2.2 执行计划分析与干预
使用EXPLAIN ANALYZE查看实际执行情况很重要。有个查询预估成本很低但实际很慢,分析发现是优化器低估了JOIN的行数。通过设置SET enable_nestloop=off强制使用Hash Join后,性能提升8倍。
对于线上慢查询,我习惯启用auto_explain:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
常见问题处理方案:
- 缺少索引:为过滤条件创建合适索引
- 连接方式不当:小表用NestLoop,大表用HashJoin
- 内存不足:出现"external merge Disk"时调大work_mem
- 排序开销大:考虑创建合适的索引避免排序
HINT是最后的干预手段。比如强制走索引:
/*+ IndexScan(orders idx_order_date) */
SELECT * FROM orders WHERE order_date > '2023-01-01'
3. 多核并行计算实战
现代服务器都是多核CPU,但很多数据库查询还是单核执行。金仓的并行查询功能可以让查询速度提升数倍,我在数据分析场景实测过最高6倍的性能提升。
3.1 并行查询配置要点
核心参数有三个层级关系:
max_parallel_workers_per_gather ≤ max_parallel_workers ≤ max_worker_processes
对于16核服务器,我通常这样配置:
max_worker_processes = 16
max_parallel_workers = 12
max_parallel_workers_per_gather = 8
min_parallel_table_scan_size = 8MB
并行查询触发条件:
- 表大小超过min_parallel_table_scan_size
- 查询成本高于parallel_min_cost(默认0)
- 系统负载允许
3.2 适用场景与效果
并行全表扫描效果最明显。一个3GB的表,全表扫描从15秒降到3秒。但要注意,如果过滤条件选择率高(返回大部分数据),并行效果会打折扣。
聚合查询也很适合并行。一个统计报表查询:
SELECT 产品类别,COUNT(*),SUM(金额)
FROM 销售表
WHERE 日期 BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 产品类别
启用并行后从28秒降到7秒。
UNION ALL查询可以用ParallelAppend:
/*+ ParallelAppend(4) */
SELECT * FROM 订单2023
UNION ALL
SELECT * FROM 订单2022
3.3 注意事项与调优建议
并行不是越多越好。OLTP系统建议设置较低的并行度(2-4),避免影响并发事务。有次设置过高导致CPU争用,反而使整体吞吐量下降30%。
监控并行工作进程:
SELECT * FROM sys_stat_activity
WHERE backend_type LIKE '%Parallel Worker%'
并行查询内存要单独考虑。每个worker都会分配work_mem,8个worker就需要8倍的work_mem。我遇到过并行查询导致OOM的情况,需要合理设置max_parallel_workers_per_gather。
4. 参数调优与资源分配
参数调优需要根据硬件和业务特点来定。有一次客户抱怨数据库慢,检查发现work_mem还是默认的1MB,排序操作频繁写临时文件。调整到64MB后,查询速度提升明显。
4.1 内存参数配置
shared_buffers:通常设为内存的25%-40%。32GB内存的机器可以设8-12GB。但要注意,设置过大会增加检查点开销。
work_mem:排序和哈希操作的内存。OLTP系统设8-32MB,报表系统设64-256MB。可以通过日志监控调整:
log_temp_files = '64MB' # 记录临时文件使用
maintenance_work_mem:维护操作的内存。创建大索引时可以设大些:
SET maintenance_work_mem = '1GB';
CREATE INDEX 大表索引 ON 大表(字段);
4.2 成本参数调整
random_page_cost:SSD硬盘建议设为1.1-1.5(默认4.0)。这个参数影响优化器选择索引扫描还是全表扫描。
cpu_tuple_cost:CPU处理元组的成本。高性能CPU可以设低些(0.005),虚拟机或老旧CPU设高些(0.02)。
effective_cache_size:告诉优化器系统有多少缓存可用。通常设为shared_buffers + 系统缓存。
4.3 连接与并发控制
max_connections:不要设置过大,每个连接都会占用内存。建议配合连接池使用。
work_mem:高并发时要谨慎设置,总内存=work_mem * 可能并发的排序操作数。
锁相关参数:
- deadlock_timeout:死锁检测间隔,默认1s
- max_locks_per_transaction:控制锁数量
5. 监控与持续优化
性能优化不是一次性的工作。我经历过一个系统上线初期很快,但随着数据增长逐渐变慢的情况,需要持续监控和调整。
5.1 监控工具使用
sys_sqltune插件是利器:
CREATE EXTENSION sys_sqltune;
SELECT * FROM V$SQL_MONITOR;
生成HTML报告更直观:
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
sql_id => 'abc123',
type => 'HTML',
report_level => 'ALL'
);
5.2 优化流程与方法
系统化优化流程:
- 识别TOP SQL(sys_stat_statements)
- 分析执行计划和等待事件
- 针对性优化(索引/SQL改写/参数)
- 验证效果
- 定期复查
索引优化建议查询:
SELECT * FROM sys_index_usage_stats
WHERE idx_scan < 100
ORDER BY idx_scan ASC;
5.3 自动化运维脚本
定期收集统计信息脚本:
#!/bin/bash
DBNAME=mydb
TABLES=$(psql -d $DBNAME -Atc "SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
for TABLE in $TABLES; do
psql -d $DBNAME -c "ANALYZE VERBOSE $TABLE"
done
性能基线对比:
-- 创建基线
SELECT PERF.CREATE_SNAPSHOT();
-- 比较差异
SELECT * FROM PERF.DIFF_SNAPSHOTS(1, 2);
1900



被折叠的 条评论
为什么被折叠?



