1. 达梦数据库SQL优化实战指南作为一名在数据库领域摸爬滚打多年的DBA我深知SQL优化对于系统性能的重要性。达梦数据库作为国产数据库的佼佼者在企业级应用中越来越普及但其SQL优化方法与Oracle等传统数据库既有相似之处也有独特之处。本文将分享我在达梦数据库SQL优化方面的实战经验从慢SQL定位到执行计划分析再到具体优化技巧希望能帮助大家少走弯路。2. 优化前置准备慢SQL定位与执行计划分析2.1 慢SQL定位工具与方法优化SQL的第一步是准确找出需要优化的SQL语句。达梦提供了多种工具来帮助我们定位慢SQLSQL日志追踪基础方法 通过配置dm.ini参数开启SQL日志可以记录所有SQL的执行信息。具体操作如下-- 临时开启SQL日志无需重启数据库 SP_SET_PARA_VALUE(1, SVR_LOG, 1); -- 1开启0关闭 SP_SET_PARA_VALUE(1, SQL_TRACE_MASK, 1); -- 记录SELECT语句1-SELECT,2-INSERT等可叠加 SP_SET_PARA_VALUE(1, SVR_LOG_SWITCH_COUNT, 1000000); -- 日志切换阈值 -- 查询慢SQL日志默认存储在数据库日志目录文件名如dm_20260107.log -- 或通过系统视图直接查询 SELECT SQL_TEXT, EXEC_TIME, ELAPSED_TIME FROM V$SQL_STATISTICS WHERE ELAPSED_TIME 1000 -- 筛选执行时间1秒的SQL ORDER BY ELAPSED_TIME DESC;达梦性能监控工具可视化高效SQLark百灵连接支持执行计划分析模式可直接可视化查看慢SQL执行过程DEM达梦企业管理器提供慢SQLTopN统计、执行耗时趋势图AWR报告生成性能快照定位特定时间段内的SQL性能瓶颈提示在实际生产环境中我通常先用DEM找出Top慢SQL再用SQLark进行详细分析这样可以快速定位问题。2.2 达梦执行计划解读实战执行计划是SQL优化的地图读懂执行计划是优化的关键。达梦查看执行计划的方法-- 方式1使用EXPLAIN关键字推荐 EXPLAIN SELECT t.rin_user_name, t.start_time, a.temp_type FROM plc_rin t LEFT JOIN plc_rin_temp a ON t.id a.rin_id WHERE a.rin_id IN (44dc5165-xxx, 5ea6b18c-xxx); -- 方式2SQLark工具可视化查看达梦执行计划中常见的操作符及其优化建议操作符含义优化建议CSCN2全表扫描检查是否缺少索引或查询条件未命中索引SSCN索引扫描确认索引是否合理避免索引失效HJ哈希连接适用于大数据量关联需调整HJ_BUF_SIZE参数NL嵌套循环确保驱动表数据量小且关联字段有索引执行计划分析步骤查看执行顺序缩进越多越先执行同缩进上先下后关注代价三元组如CSCN2: (1, 3960, 56)分别代表估算代价、输出结果集行数、数据长度定位关键问题优先优化包含CSCN2全表扫描、大结果集的操作步骤3. 核心优化技巧从索引到语句重构3.1 索引优化创建、失效与修复索引是SQL优化的核心武器但使用不当反而会成为负担。达梦索引优化要点高效索引创建实践-- 1. 普通索引适用于单字段查询、排序 CREATE INDEX IDX_PLC_RIN_TEMP_RIN_ID ON plc_rin_temp(rin_id); -- 2. 复合索引遵循最左匹配原则 CREATE INDEX IDX_PLC_RIN_TEMP_RIN_MINUTE ON plc_rin_temp(rin_id, temp_minute DESC); -- 3. 函数索引适用于查询条件含函数操作的场景 CREATE INDEX IDX_PLC_RIN_TEMP_RIN_ID_SUB ON plc_rin_temp(SUBSTR(rin_id, 1, 8)); -- 4. 分区索引适用于大表 CREATE INDEX IDX_PLC_RIN_TEMP_TIME ON plc_rin_temp(collect_time) GLOBAL PARTITION BY RANGE(collect_time) ( PARTITION P202601 VALUES LESS THAN (2026-02-01), PARTITION P202602 VALUES LESS THAN (2026-03-01) );索引失效常见场景与修复失效场景示例SQL修复方案索引列使用函数SELECT * FROM t WHERE SUBSTR(name,1,3)abc创建函数索引或改写语句索引列使用不等于SELECT * FROM t WHERE status ! 1改为范围查询或全表扫描更优时放弃索引模糊查询前缀通配符SELECT * FROM t WHERE name LIKE %test避免前缀通配符或使用全文索引隐式类型转换SELECT * FROM t WHERE id123id为INT型统一数据类型改为id123索引维护技巧定期重建碎片化索引ALTER INDEX 索引名 REBUILD;避免索引冗余通过V$INDEXES视图查询未使用的索引及时删除3.2 查询语句重构高效语法实践合理的SQL语句重构可以带来显著的性能提升避免全表扫描的语句改写-- 反面示例全表扫描 SELECT * FROM plc_rin_temp WHERE rin_id IN (44dc5165-xxx, 5ea6b18c-xxx); -- 正面示例1使用索引 SELECT rin_id, temp_type, temp_minute FROM plc_rin_temp WHERE rin_id IN (44dc5165-xxx, 5ea6b18c-xxx); -- 正面示例2IN列表超长时改为临时表关联 CREATE GLOBAL TEMPORARY TABLE temp_rin_ids(rin_id VARCHAR(64)) ON COMMIT PRESERVE ROWS; INSERT INTO temp_rin_ids VALUES (44dc5165-xxx), (5ea6b18c-xxx), ...; SELECT t.* FROM plc_rin_temp t JOIN temp_rin_ids tmp ON t.rin_id tmp.rin_id;分页查询优化-- 低效写法全表排序后分页 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY create_time DESC) rn, * FROM plc_rin ) t WHERE rn BETWEEN 100001 AND 100100; -- 高效写法利用索引排序 SELECT * FROM plc_rin WHERE create_time (SELECT create_time FROM plc_rin ORDER BY create_time DESC LIMIT 100000, 1) ORDER BY create_time DESC LIMIT 100;避免重复计算-- 反面示例重复计算SUBSTR(rin_id,1,8) SELECT SUBSTR(rin_id,1,8) AS prefix, COUNT(*) FROM plc_rin_temp GROUP BY SUBSTR(rin_id,1,8); -- 正面示例使用CTE或子查询复用计算结果 WITH temp_prefix AS ( SELECT SUBSTR(rin_id,1,8) AS prefix FROM plc_rin_temp ) SELECT prefix, COUNT(*) FROM temp_prefix GROUP BY prefix;3.3 关联查询与子查询优化关联方式选择小表驱动大表优先使用NL连接大数据量关联使用HJ连接调整HJ_BUF_SIZE参数避免笛卡尔积必须指定ON条件子查询优化-- 低效写法相关子查询 SELECT t.*, (SELECT MAX(temp_minute) FROM plc_rin_temp WHERE rin_id t.id) AS max_minute FROM plc_rin t; -- 高效写法改为JOIN查询 SELECT t.*, tmp.max_minute FROM plc_rin t LEFT JOIN ( SELECT rin_id, MAX(temp_minute) AS max_minute FROM plc_rin_temp GROUP BY rin_id ) tmp ON t.id tmp.rin_id;4. 进阶优化参数调优与特殊场景处理4.1 关键参数调优dm.ini达梦数据库参数直接影响SQL执行效率以下是核心优化参数内存相关参数-- 系统缓冲区行存表建议设为物理内存的1/2~2/3 SP_SET_PARA_VALUE(0, BUFFER, 8192); -- 8GB0表示重启生效 -- 公共内存池减少并发内存申请冲突 SP_SET_PARA_VALUE(1, MEMORY_POOL, 1024); -- 1GB1表示立即生效 SP_SET_PARA_VALUE(1, N_MEM_POOLS, 8); -- 划分8个独立内存单元 -- 排序缓冲区提升ORDER BY/GROUP BY效率 SP_SET_PARA_VALUE(1, SORT_BUF_SIZE, 64); -- 64MB计划缓存参数-- 开启模糊匹配计划缓存适用于OLTP场景 SP_SET_PARA_VALUE(1, USE_PLN_POOL, 2); -- 计划缓存大小默认10M高并发场景可增大 SP_SET_PARA_VALUE(0, CACHE_POOL_SIZE, 128); -- 128MBI/O优化参数开启缓冲区自动扩展SP_SET_PARA_VALUE(0, BUFFER_POOLS, 1);配置RECYCLE缓冲区SP_SET_PARA_VALUE(0, RECYCLE, 1024);-- 1GB4.2 大批量数据与高并发场景优化大批量数据查询优化分表查询按时间或业务维度分表并行查询SELECT /* PARALLEL(4) */ rin_id, COUNT(*) FROM plc_rin_temp GROUP BY rin_id;高并发场景优化避免长事务拆分大事务为小事务使用绑定变量-- 推荐使用绑定变量 PREPARE stmt FROM SELECT * FROM plc_rin WHERE id ?; EXECUTE stmt USING 44dc5165-xxx; -- 不推荐常量拼接 SELECT * FROM plc_rin WHERE id 44dc5165-xxx;5. 实战案例典型慢SQL优化落地5.1 案例1多表关联慢查询优化原始问题SQL执行耗时2.3秒SELECT p.rin_user_name, p.start_time, a.temp_type, a.temp_bearing FROM plc_rin p LEFT JOIN plc_rin_temp a ON p.id a.rin_id WHERE a.rin_id IN (44dc5165-xxx, 5ea6b18c-xxx, ...) -- IN列表含200个值 ORDER BY a.temp_minute DESC;优化步骤-- 步骤1创建复合索引 CREATE INDEX IDX_PLC_RIN_TEMP_RIN_MINUTE ON plc_rin_temp(rin_id, temp_minute DESC); -- 步骤2改写IN为JOIN CREATE GLOBAL TEMPORARY TABLE temp_rin_ids(rin_id VARCHAR(64)) ON COMMIT PRESERVE ROWS; INSERT INTO temp_rin_ids VALUES (44dc5165-xxx), (5ea6b18c-xxx), ...; -- 优化后SQL执行耗时0.01秒 SELECT p.rin_user_name, p.start_time, a.temp_type, a.temp_bearing FROM plc_rin p LEFT JOIN plc_rin_temp a ON p.id a.rin_id LEFT JOIN temp_rin_ids tmp ON a.rin_id tmp.rin_id ORDER BY a.temp_minute DESC;优化效果执行耗时从2.3秒降至0.01秒性能提升230倍。5.2 案例2统计查询慢SQL优化原始问题SQL统计每日温度采集量耗时1.8秒SELECT DATE_FORMAT(collect_time, %Y-%m-%d) AS collect_date, COUNT(*) AS total FROM plc_rin_temp GROUP BY DATE_FORMAT(collect_time, %Y-%m-%d);优化步骤-- 步骤1创建函数索引 CREATE INDEX IDX_PLC_RIN_TEMP_COLLECT_DATE ON plc_rin_temp(DATE_FORMAT(collect_time, %Y-%m-%d)); -- 步骤2优化GROUP BY SELECT collect_date, COUNT(*) AS total FROM ( SELECT DATE_FORMAT(collect_time, %Y-%m-%d) AS collect_date FROM plc_rin_temp ) t GROUP BY collect_date;优化效果执行耗时降至0.05秒性能提升36倍。6. 常见问题排查与优化误区常见问题产生原因解决方案索引创建后仍全表扫描1. 查询条件未命中索引2. 数据量过小3. 统计信息过期1. 修正查询条件2. 小表无需强制使用索引3. 执行ANALYZE TABLE更新统计信息高并发下SQL执行变慢1. 计划缓存命中率低2. 内存不足3. 锁等待1. 开启USE_PLN_POOL22. 提升内存参数3. 用V$LOCK视图查询锁信息优化后性能无提升1. 瓶颈不在SQL2. 执行计划分析错误3. 参数未生效1. 检查硬件资源2. 重新分析执行计划3. 确认参数修改方式大批量插入/更新慢1. 日志I/O频繁2. 索引过多3. 未批量操作1. 临时关闭日志2. 批量操作前禁用索引3. 使用INSERT BATCH过度建索引忽略索引维护开销1. 仅对高频查询字段创建索引2. 定期删除未使用索引在实际工作中我发现很多开发人员容易陷入索引越多越好的误区。其实索引是一把双刃剑合理的索引确实能提升查询性能但过多的索引会增加插入、更新、删除操作的开销同时占用额外的存储空间。建议定期检查索引使用情况删除那些很少或从未被使用的索引。