达梦数据库SQL优化实战：从执行计划解读到索引设计

1. 项目概述为什么达梦SQL优化是DBA的必修课最近在几个项目上做性能巡检发现一个挺普遍的现象很多团队把达梦数据库用起来了业务跑得也挺顺畅但一遇到数据量增长或者并发上来的情况系统响应就明显变慢。一查十有八九是SQL语句写得不够“讲究”全表扫描、嵌套循环连接满天飞。问起开发同事他们往往对达梦的执行计划Explain Plan感到陌生觉得那是DBA才需要看的“黑盒”。这其实是个误区。在我看来无论是开发还是运维只要你的代码需要和达梦数据库交互读懂执行计划并据此进行SQL优化就是一项必须掌握的硬核技能。这就像开车你不能只管踩油门还得会看仪表盘了解发动机的实时状态。达梦作为一款成熟的企业级国产数据库其优化器已经相当智能但它不是万能的。优化器的决策严重依赖于统计信息的准确性、索引的设计以及SQL本身的写法。一条在测试环境跑得飞快的SQL到了生产环境可能就变成性能瓶颈根源往往就在于数据分布发生了变化而执行计划没能做出最佳选择。SQL优化的核心就是通过分析执行计划这个“诊断报告”找到消耗资源的瓶颈点然后通过调整索引、重写SQL、更新统计信息等手段引导优化器选择更高效的执行路径。这个过程我们称之为“给SQL做体检和开药方”。2. 执行计划深度解读看懂达梦的“执行蓝图”执行计划是达梦数据库优化器生成的、关于如何执行一条SQL语句的详细步骤说明。它不是一个预测而是优化器基于当前数据库对象统计信息、系统参数和SQL文本经过成本计算后选定的“它认为最优”的执行方案。看不懂它优化就无从谈起。2.1 获取执行计划的几种方式在达梦里获取执行计划主要有三种方法适用于不同场景EXPLAIN 命令这是最基础、最常用的方式。在SQL语句前加上EXPLAIN即可。EXPLAIN SELECT * FROM EMP WHERE DEPTNO 10;它会在当前会话中生成执行计划但并不真正执行SQL语句。适合在开发、测试环境分析单条SQL。ET执行时间功能这是达梦提供的一个非常强大的性能分析工具。通过设置参数ENABLE_MONITOR1和MONITOR_SQL_EXEC1后执行SQL然后查看V$SQL_MONITOR或V$SQL_MONITOR_HISTORY视图。ET不仅能给出执行计划还能提供每一步操作的实际执行时间、返回行数、逻辑读、物理读等详尽的运行时统计信息。这对于分析复杂、长时间运行的SQL至关重要因为EXPLAIN只展示“计划”而ET展示的是“实际执行”。管理工具图形化界面像达梦管理工具DM Management Tool或DBeaver这类第三方工具都提供了直观的图形化执行计划展示。它们通常也是调用EXPLAIN功能但以树形结构或流程图呈现关系箭头清晰对于理解操作之间的父子、依赖关系特别有帮助尤其适合初学者。注意EXPLAIN基于统计信息估算成本而ET反映实际执行代价。有时两者显示的“最优”计划可能不同务必以ET的实际执行为准进行调优。2.2 执行计划核心操作符详解达梦执行计划由一系列操作符Operator构成。理解这些操作符是读懂计划的关键。下面我挑几个最核心、最常遇到的来讲CSCN / SSCN / SSEK这是表扫描和索引扫描的“家族”。CSCN全表扫描Cluster Scan。从头到尾扫描堆表的所有数据页。当需要访问表中大部分数据或表上没有合适的索引时会发生。这是我们需要警惕的操作特别是在大表上。SSCN索引全扫描Secondary Scan。按索引键的顺序扫描整个索引。当SQL需要的数据或排序键全部包含在某个索引中且需要有序输出时使用。虽然也是扫描但通常比CSCN快因为索引条目通常比数据行小。SSEK索引范围扫描Secondary Seek。通过索引定位到范围的起点然后沿索引叶节点链表扫描。这是效率非常高的操作通常发生在WHERE条件包含索引前缀列时。例如在IDX_EMP_DEPT(DEPTNO, SAL) 索引上查询WHERE DEPTNO10就会走SSEK。NEST LOOP嵌套循环连接。它就像两重for循环。取左表驱动表的一行去右表被驱动表中逐行匹配连接条件。如果右表有高效的索引通常针对连接列那么每次匹配会很快SSEK如果没有就会变成灾难性的全表扫描。选择小表或返回结果集小的表作为驱动表至关重要。HASH JOIN哈希连接。它通常会选择两个表中较小的一个或筛选后结果集小的在内存中构建一个哈希表然后扫描大表并对每一行计算哈希值去哈希表中查找匹配。它在处理没有索引的大表等值连接时效率往往远高于NEST LOOP。但很耗内存如果内存不足会写磁盘性能急剧下降。SORT排序操作。当SQL中有ORDER BY、GROUP BY非索引、DISTINCT或窗口函数时可能出现。排序是CPU和内存密集型操作特别是在处理大量数据时。尽可能利用索引的有序性来避免排序是优化的一个重要方向。PRJT / SLCT投影和选择。PRJT投影。负责从底层操作符返回的结果集中提取出SQL语句SELECT列表里指定的列。SLCT选择。负责应用WHERE子句中的过滤条件。理想情况下过滤应尽可能在靠近数据源的地方完成例如通过索引SSEK以减少向上传递的数据量。如果看到SLCT在很上层且过滤率很高可能意味着错过了索引。2.3 读懂执行计划的成本与顺序执行计划是树形结构阅读顺序是从最内层、最右边的叶子节点开始向上向左。每个操作符都会产生一个“成本COST”估算值这个值综合了CPU和I/O开销是一个相对值用于比较不同计划的优劣。但记住它只是估算。我们来看一个简单的例子EXPLAIN SELECT e.NAME, d.DNAME FROM EMP e, DEPT d WHERE e.DEPTNO d.DEPTNO AND e.SAL 5000;一个可能的计划输出简化表示1 #NSET2: [1, 100, 156] 2 #PRJT2: [1, 100, 156] 3 #HASH2 INNER JOIN: [1, 100, 156] 4 #SLCT2: [1, 50, 104] 5 #CSCN2: [1, 1000, 104] (INDEX: NULL) TABLE: EMP 6 #CSCN2: [1, 4, 52] TABLE: DEPT解读第6行最先执行对DEPT表进行全表扫描CSCN2。第5行同时或稍后执行对EMP表进行全表扫描。第4行对EMP扫描的结果进行过滤SLCT2对应e.SAL 5000。注意这里是在扫描了所有EMP行之后才过滤如果EMP表很大且高薪员工很少这非常低效。第3行将过滤后的EMP结果集作为构建端与DEPT表扫描结果作为探测端进行哈希连接HASH2 INNER JOIN。第2行从连接结果中投影出所需的NAME和DNAME列PRJT2。第1行将最终结果集返回给客户端NSET2。这个计划明显有优化空间EMP表缺少对SAL的过滤索引导致全表扫描。3. SQL优化核心策略与实战技巧知道了问题在哪接下来就是如何解决。SQL优化不是玄学是一套有章可循的方法论。3.1 索引优化创建合适的“高速公路”索引是优化的第一利器但绝不是越多越好。不当的索引会降低插入、更新速度并占用额外空间。单列索引与复合索引等值查询用单列索引通常就够了。但对于WHERE A? AND B?或WHERE A? ORDER BY B这类场景复合索引(A, B)是更佳选择。复合索引的第一列引导列至关重要查询条件必须包含它索引才能被使用。覆盖索引是王牌如果索引包含了查询所需的所有列即SELECT的列和WHERE的列数据库可以直接从索引中取得数据无需回表访问数据页这能极大提升性能。例如对于SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO创建索引(DEPTNO)就能实现覆盖索引因为COUNT(*)不需要具体列值。避免在索引列上使用函数或计算WHERE UPPER(name) ‘SMITH’会导致索引失效。如果必须这样做可以考虑创建函数索引如果数据库支持或调整设计。选择性不高的列慎建索引像“性别”、“状态”这种只有几个枚举值的列创建索引帮助不大优化器可能仍然选择全表扫描。实操心得我习惯在业务上线稳定后通过达梦的V$SQL_PLAN或V$SQL_HISTORY视图找出执行频率高、代价大的SQL针对其WHERE、JOIN、ORDER BY子句来设计索引。而不是在设计阶段拍脑袋创建一堆可能用不上的索引。3.2 SQL语句重写写出优化器喜欢的“句式”同样的业务逻辑不同的写法可能导致完全不同的执行计划。用EXISTS替代IN当子查询结果集很大时IN会导致数据库先执行子查询物化一个结果列表再进行匹配效率低下。而EXISTS是关联性子查询只要找到一条匹配记录就返回通常效率更高。当然如果子查询结果集很小IN可能更快具体要看执行计划。避免SELECT *明确列出需要的列。这不仅能减少网络传输更重要的是如果这些列都在某个索引上就可能利用覆盖索引避免回表。将OR改写为UNION ALL对于不同字段的OR条件例如WHERE A1 OR B2如果A和B上分别有索引优化器可能无法同时利用导致全表扫描。可以尝试改写为SELECT * FROM T WHERE A1 UNION ALL SELECT * FROM T WHERE B2 AND A1 -- 注意去重如果A、B不互斥可用UNION这样两个子查询可以分别走索引。注意隐式类型转换WHERE id ‘100’id是数字列会导致列上的索引失效因为数据库需要将每行的id转换为字符串再比较。务必确保比较双方数据类型一致。3.3 统计信息与优化器提示给优化器“导航”优化器是依赖统计信息表的数据量、列的数值分布、索引的密度等来做成本计算的。过时的统计信息是导致执行计划变差的头号元凶。定期更新统计信息对于核心业务表在大量增删改操作后应执行DBMS_STATS.GATHER_TABLE_STATS(‘模式名’ ‘表名’)。达梦也支持自动收集统计信息但在变化剧烈的场景下可能需要更频繁的手动干预。使用优化器提示Hints当你确信优化器选错了计划时可以使用提示来强制干预。这是高级技巧需谨慎使用。/* INDEX(T IDX_NAME) */强制使用特定索引。/* USE_HASH(T1 T2) */强制使用哈希连接。/* FULL(T) */强制全表扫描有时走索引反而慢。重要警告提示是把双刃剑。数据分布一旦改变强制使用的计划可能变得极差。它应该是验证想法或解决紧急问题的临时手段长期方案还是优化索引和SQL。4. 实战案例一条慢SQL的优化全过程光说不练假把式。我们模拟一个真实场景。假设有一张订单表orders约1亿行结构简化如下CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id INT, product_id INT, amount DECIMAL(10,2), status TINYINT, -- 1待支付2已支付3已完成4已取消 create_time DATETIME, pay_time DATETIME );有一条频繁执行的查询用于统计某产品近期已完成订单的总额最初是这样写的SELECT product_id, SUM(amount) FROM orders WHERE status 3 AND product_id 10086 AND create_time DATEADD(day, -30, GETDATE()) GROUP BY product_id;执行时间超过15秒。第一步获取并解读执行计划使用EXPLAIN查看发现计划如下1 #NSET2: [1, 1, 12] 2 #PRJT2: [1, 1, 12] 3 #HASH2 GROUP BY: [1, 1, 12] 4 #SLCT2: [1, 5000, 12] 5 #CSCN2: [1, 100000000, 12] TABLE: ORDERS问题诊断计划显示进行了全表扫描CSCN2扫描了1亿行然后在内存中对这1亿行数据应用过滤条件SLCT2最后做聚合。status、product_id、create_time三个条件都没有用到索引。第二步设计并创建索引三个条件中product_id和create_time的选择性可能较好假设产品众多时间范围明确。status选择性较差只有几个状态。根据复合索引“等值在前范围在后”的原则创建索引CREATE INDEX IDX_ORDERS_PRODUCT_STATUS_TIME ON orders(product_id, status, create_time);为什么把status放在中间因为product_id 10086是等值条件create_time xxx是范围条件。status虽然是等值但选择性差放在中间可以让索引先通过product_id快速定位到所有该产品的订单再从中过滤出status3的最后按时间范围扫描。如果status选择性很好也可以考虑放在最前面。第三步验证优化效果再次执行EXPLAIN1 #NSET2: [1, 1, 12] 2 #PRJT2: [1, 1, 12] 3 #HASH2 GROUP BY: [1, 1, 12] 4 #SSEK2: [1, 5000, 12] INDEX: IDX_ORDERS_PRODUCT_STATUS_TIME完美执行计划从全表扫描CSCN2变成了高效的索引范围扫描SSEK2。估算行数从1亿降到了5000。实际执行时间从15秒降到了50毫秒以内。第四步进一步优化——使用覆盖索引观察SQLSELECT的列是product_id和amountGROUP BY的是product_id。我们创建的索引包含了product_id, status, create_time但不包含amount。这意味着数据库在通过索引找到符合条件的行ID后还需要“回表”到主数据页去读取amount的值。 我们可以创建一个覆盖索引来消除回表CREATE INDEX IDX_ORDERS_COVERING ON orders(product_id, status, create_time, amount);再次检查执行计划会发现SSEK2操作符的详细信息里可能多了一个[WITH_COVERING_IDX]之类的提示表示直接使用索引数据性能会再有小幅提升。对于海量数据这个提升可能非常可观。5. 高级主题并行执行与参数调优当单条SQL涉及的数据量极其庞大时单线程执行可能成为瓶颈。达梦数据库支持并行执行Parallel Execution可以将一个大的查询任务分解成多个子任务由多个CPU核心同时处理。何时考虑并行通常针对大表全表扫描、大规模数据聚合GROUP BY、多表大结果集连接等CPU密集型或I/O密集型的操作。对于本身很快的OLTP点查询开启并行反而会增加协调开销降低性能。如何启用并行会话级SET PARALLEL_POLICY 1;并设置PARALLEL_THRD_NUM。语句级使用提示/* PARALLEL(T, 4) */指定对表T使用4个并行度。对象级在创建或修改表/索引时指定PARALLEL子句。核心参数调优PARALLEL_POLICY并行策略0禁止1手动2自动。PARALLEL_THRD_NUM默认并行线程数。OPTIMIZER_MODE优化器模式通常保持默认1基于成本即可。在某些复杂查询下可以尝试改为0基于规则进行比较但这不是常规手段。USE_PLN_POOL是否启用执行计划缓存。对于重复执行的相同SQL绑定变量不同启用缓存可以避免重复硬解析显著提升性能。这是生产环境必开参数。踩坑记录曾经在一个报表系统里对一张百亿级别的表做月度聚合最初串行执行需要2小时。在确认服务器CPU和IO资源充足后尝试使用/* PARALLEL(16) */提示。结果执行时间不仅没降系统整体响应都变慢了。通过监控发现并行任务产生了大量的临时段I/O挤占了正常业务IOPS。后来通过优化SQL写法减少中间结果集、增加临时表空间专用磁盘的IO能力并将并行度降到8最终将时间稳定在25分钟左右。教训是并行不是银弹需要充足的硬件资源特别是I/O带宽支撑并且要找到合适的并行度避免过度并行导致资源争用。6. 构建SQL优化闭环监控、分析与持续改进优化不是一劳永逸的。随着数据增长、业务变化今天高效的SQL明天可能就会变慢。因此需要建立一个持续的监控优化机制。建立慢SQL监控配置达梦数据库的SVR_LOG或使用SP_SET_SYSTEM_PARA_VALUE设置SQL_TRACE相关参数定期抓取执行时间超过阈值的SQL例如1秒。将这些SQL记录到日志文件或专用表中。定期分析TOP SQL每周或每天从慢SQL日志或V$SQL_HISTORY、V$SQL_PLAN等动态性能视图中找出执行频率最高、总耗时最长、单次执行最慢的TOP 10 SQL。使用ET进行深度诊断对找出的问题SQL使用ET功能获取其详细的执行计划和运行时统计信息。重点关注哪些操作符消耗了最多时间EXEC_TIME逻辑读LOGIC_READ和物理读PHYS_READ是否异常高返回行数RESULT_ROWS与估算行数CARDINALITY是否差异巨大差异巨大往往意味着统计信息不准。制定并实施优化方案根据分析结果采取相应措施更新统计信息、增加或调整索引、重写SQL语句。测试与回滚方案任何优化方案都必须先在测试环境验证。验证不仅要看SQL本身变快还要观察对同一张表其他SQL有无负面影响例如新索引导致写操作变慢。准备好回滚脚本。知识沉淀将优化案例记录下来形成知识库。同样的模式可能会在其他SQL上重现。我个人习惯在团队内部推行“SQL代码评审”制度在功能上线前对核心复杂SQL进行执行计划审查将性能问题扼杀在摇篮里。这比事后救火要高效得多。数据库性能优化是一个需要耐心、细心和系统化方法的工作而读懂并驾驭执行计划无疑是其中最核心的一把钥匙。