解锁达梦数据库：SQL优化实战秘籍

张

张建站

2026/7/4 9:59:02

10分钟阅读

目录一、优化前置准备慢 SQL 定位与执行计划分析1.1 慢 SQL 定位工具与方法1.2 达梦执行计划解读实战二、核心优化技巧从索引到语句重构2.1 索引优化创建、失效与修复2.2 查询语句重构高效语法实践2.3 关联查询与子查询优化三、进阶优化参数调优与特殊场景处理3.1 关键参数调优dm.ini3.2 大批量数据与高并发场景优化四、实战案例典型慢 SQL 优化落地案例 1多表关联慢查询优化案例 2统计查询慢 SQL 优化五、常见问题排查与优化误区5.1 常见问题排查5.2 优化误区六、总结一、优化前置准备慢 SQL 定位与执行计划分析1.1 慢 SQL 定位工具与方法在达梦数据库的 SQL 优化过程中准确找到慢 SQL 是优化的第一步而这依赖于有效的工具和方法。SQL 日志筛选通过配置 dm.ini 参数来开启 SQL 日志是一种基础且有效的方式。在实际操作中可使用存储过程来临时开启 SQL 日志比如SP_SET_PARA_VALUE(1, ‘SVR_LOG’,1)这里1表示开启0则表示关闭同时使用SP_SET_PARA_VALUE(1, ‘SQL_TRACE_MASK’,1)来记录 SELECT 语句其中1代表 SELECT2代表 INSERT 等可叠加设置并通过SP_SET_PARA_VALUE(1, ‘SVR_LOG_SWITCH_COUNT’,1000000)设置日志切换阈值。开启日志后慢 SQL 日志默认存储在数据库日志目录文件名类似dm_20260107.log 也能通过系统视图V$SQL_STATISTICS直接查询如SELECT SQL_TEXT, EXEC_TIME, ELAPSED_TIME FROMV$SQL_STATISTICSWHERE ELAPSED_TIME 1000 ORDER BY ELAPSED_TIME DESC;这条语句可以筛选出执行时间大于 1 秒的 SQL并按执行时间从长到短排序展示。SQLark 工具SQLark百灵连接为达梦数据库用户提供了可视化的操作界面在执行计划分析模式下用户无需手动格式化执行计划数据就能够直接清晰地查看慢 SQL 的执行过程。用户只需打开 SQLark 并连接到达梦数据库输入待分析的 SQL 语句点击「执行计划」按钮切换到表格模式就能详细查看每一步的执行细节这大大提高了分析效率减少了人工分析的复杂性。DEM达梦企业管理器DEM 是一款功能强大的可视化管理工具。在慢 SQL 定位方面它提供了慢 SQL TopN 统计功能能直观展示执行时间最长的前 N 条 SQL 语句。同时DEM 还支持生成执行耗时趋势图帮助用户分析 SQL 执行时间随时间的变化趋势。此外通过 DEM用户可以按照时间段、用户、应用等多个维度对 SQL 进行筛选精准定位出特定场景下的慢 SQL。AWR 报告AWRAutomatic Workload Repository报告能够生成达梦数据库的性能快照。它收集了数据库在一段时间内的各种性能数据包括 SQL 的执行信息。通过分析 AWR 报告用户可以定位特定时间段内的 SQL 性能瓶颈了解数据库负载情况、资源消耗等从而找出导致性能问题的慢 SQL。1.2 达梦执行计划解读实战执行计划对于 SQL 优化至关重要它就像是数据库执行 SQL 语句的详细蓝图通过分析执行计划DBA 和开发人员能够深入了解 SQL 语句在数据库中的执行过程快速定位全表扫描、低效关联等性能问题。查看执行计划的方法使用 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’); 只需在其前面加上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’);执行后就会返回该 SQL 语句的执行计划信息这些信息包括操作符、执行顺序、代价估算等关键内容。SQLark 工具可视化查看利用 SQLark 工具先打开 SQLark 并连接到达梦数据库输入 SQL 语句后点击「执行计划」按钮然后切换到表格模式即可清晰、直观地查看执行计划的详细内容相比纯文本的执行计划输出SQLark 的可视化展示更易于理解和分析。关键执行计划操作符解读CSCN2全表扫描当执行计划中出现 CSCN2 操作符时表示数据库对表进行全表扫描操作。在数据量较小的情况下全表扫描的性能影响可能不明显但当表数据量非常大时全表扫描会导致性能急剧下降。此时需要检查是否缺少合适的索引或者查询条件是否未能命中已有的索引如果是这些问题导致的全表扫描可通过创建索引或调整查询条件来优化。SSCN索引扫描SSCN 操作符意味着数据库通过索引来检索数据通常情况下索引扫描的效率较高。不过即使使用了索引扫描也需要确认索引是否合理是否存在索引失效的情况例如索引列使用了函数、不等于操作符、模糊查询前缀通配符或者发生了隐式类型转换等都可能导致索引失效进而影响查询性能。HJ哈希连接哈希连接适用于大数据量的关联查询场景。它的原理是在内存中构建哈希表来实现关联操作因此在大数据量下性能表现较好但需要占用较多的内存资源。如果在执行哈希连接时出现内存不足的情况可以考虑调整HJ_BUF_SIZE参数来增加哈希连接缓冲区的大小以提高关联查询的性能。NL嵌套循环嵌套循环连接适用于小表驱动大表的场景并且要求关联条件高效。在这种连接方式下驱动表的每一条记录都会与被驱动表进行匹配所以确保驱动表数据量小能够减少匹配次数提高查询效率。同时关联字段上要有合适的索引以加快匹配速度。分析执行计划的步骤查看执行顺序执行计划中的操作步骤是有先后顺序的通常缩进越多的操作步骤越先执行而相同缩进的操作步骤则按照从上到下的顺序执行。理解执行顺序有助于把握整个 SQL 语句的执行流程从而找出可能存在性能问题的环节。关注代价三元组执行计划中会包含代价三元组信息例如CSCN2:(1, 3960, 56) 其中第一个数字代表估算代价它反映了数据库执行该操作所需的资源消耗第二个数字表示输出结果集行数结果集行数越大后续操作处理的数据量就越大对性能的影响也就可能越大第三个数字是数据长度。在分析执行计划时要重点关注结果集行数较大的操作步骤因为这些步骤可能是性能瓶颈所在。定位关键问题优先优化包含CSCN2全表扫描的操作步骤因为全表扫描在大数据量下性能最差。同时对于产生大结果集的操作步骤也需要重点关注和优化通过调整索引、重构查询语句等方式来减少结果集的大小提高查询性能。二、核心优化技巧从索引到语句重构2.1 索引优化创建、失效与修复索引在数据库查询中扮演着至关重要的角色它如同书籍的目录能够帮助数据库快速定位所需数据从而显著提升查询效率。在达梦数据库中索引的优化涉及多个方面包括不同类型索引的创建、索引失效问题的排查与修复以及索引的日常维护。索引创建普通索引普通索引是最基本的索引类型它能够加速对单个列的查询操作。在达梦数据库中创建普通索引的语法如下CREATE INDEX index_name ON table_name(column_name); 例如对于employees表的last_name列若经常需要根据姓氏进行查询可创建索引CREATE INDEX idx_last_name ON employees(last_name); 。这样在执行SELECT * FROM employees WHERE last_name ‘Smith’;这类查询时数据库可以通过索引快速定位到满足条件的记录而无需进行全表扫描从而大大提高查询效率。复合索引复合索引是对多个列组合创建的索引它适用于包含多个条件的查询场景。创建复合索引时需要特别注意列的顺序遵循 “最左前缀原则”。例如在orders表中若经常按照customer_id和order_date进行查询可创建复合索引CREATE INDEX idx_customer_date ON orders(customer_id, order_date); 。此时对于SELECT * FROM orders WHERE customer_id 123 AND order_date ‘2023 - 01 - 01’;这样的查询索引能够有效发挥作用。但如果查询条件仅为WHERE order_date ‘2023 - 01 - 01’; 由于不满足最左前缀原则该复合索引将无法被使用。函数索引当查询条件中对列使用了函数时普通索引通常会失效而函数索引则可以解决这一问题。函数索引是基于函数计算结果创建的索引。例如在users表中若需要进行大小写不敏感的用户名查询即使用SELECT * FROM users WHERE UPPER(username) ‘ALICE’;这样的查询语句可创建函数索引CREATE INDEX idx_username_upper ON users(UPPER(username)); 。这样在执行上述查询时数据库能够利用函数索引快速定位到匹配的记录避免了全表扫描显著提升查询性能。分区索引分区索引适用于大数据量的表它能够将索引按照一定的规则进行分区从而提高查询效率和管理效率。分区索引分为本地分区索引和全局分区索引。本地分区索引的每个分区与表的分区一一对应例如对于按sale_date进行范围分区的sales表创建本地分区索引的语句如下CREATETABLEsales(sale_id NUMBERPRIMARYKEY,product_id NUMBER,customer_id NUMBER,sale_dateDATE,amount NUMBER)PARTITIONBYRANGE(sale_date)(PARTITIONp1VALUESLESS THAN(DATE2023 - 01 - 01),PARTITIONp2VALUESLESS THAN(DATE2023 - 07 - 01),PARTITIONp3VALUESLESS THAN(DATE2024 - 01 - 01));CREATEINDEXsales_date_idxONsales(sale_date)LOCAL(PARTITIONp1,PARTITIONp2,PARTITIONp3);全局分区索引的分区方式与表的分区方式无关可以根据不同的列进行分区如CREATEINDEXsales_amount_idxONsales(amount)GLOBALPARTITIONBYRANGE(amount)(PARTITIONpaVALUESLESS THAN(100),PARTITIONpbVALUESLESS THAN(500),PARTITIONpcVALUESLESS THAN(1000));索引失效分析函数操作当查询条件中的索引列被函数包裹时索引通常会失效。例如对于employees表的hire_date列若查询语句为SELECT * FROM employees WHERE YEAR(hire_date) 2020; 由于YEAR(hire_date)是对hire_date进行了函数操作此时即使hire_date列上有索引该索引也无法被使用数据库将执行全表扫描导致查询性能下降。解决方法是创建函数索引如CREATE INDEX idx_hire_year ON employees(YEAR(hire_date)); 。数据类型不一致如果查询条件中的数据类型与索引列的数据类型不一致可能会引发隐式类型转换从而导致索引失效。例如employees表的employee_id列是INT类型若查询语句写成SELECT * FROM employees WHERE employee_id ‘123’; 数据库会将employee_id列的值转换为字符串后再进行比较这会使索引失效。正确的做法是确保查询条件的数据类型与索引列的数据类型一致即SELECT * FROM employees WHERE employee_id 123; 。模糊查询前缀通配符当使用LIKE进行模糊查询时如果通配符%出现在前面如SELECT * FROM employees WHERE last_name LIKE ‘%Smith’; 索引将无法使用。因为数据库无法通过索引快速定位到满足条件的记录只能进行全表扫描。若通配符在后面如SELECT * FROM employees WHERE last_name LIKE ‘Smith%’; 索引则可以正常使用。OR 条件当查询条件中使用OR连接多个条件且这些条件涉及不同的索引列时索引可能无法生效。例如SELECT * FROM employees WHERE department_id 1 OR salary 5000; 如果department_id和salary分别有索引数据库可能无法同时利用这两个索引甚至可能放弃使用索引改为全表扫描。在这种情况下可以考虑将查询拆分成两个UNION ALL的查询或者创建一个覆盖这两个字段的复合索引。索引修复与维护重建索引随着数据的不断更新索引可能会出现碎片化导致查询性能下降。此时可以通过重建索引来优化索引结构提高查询效率。在达梦数据库中重建索引的语句为ALTER INDEX index_name REBUILD; 。例如对于employees表的idx_last_name索引若发现其性能下降可执行ALTER INDEX idx_last_name REBUILD; 来重建索引。删除与重建如果确定某个索引不再被使用或者该索引对性能产生了负面影响可以先删除该索引再根据实际需求重新创建。删除索引的语句为DROP INDEX index_name; 。例如若employees表中的idx_old_index索引不再使用可执行DROP INDEX idx_old_index; 之后根据业务需求若需要再次使用该索引可按照相应的索引创建语法重新创建。2.2 查询语句重构高效语法实践查询语句的编写方式对数据库的性能有着直接的影响。在达梦数据库中通过合理重构查询语句可以避免一些常见的低效操作显著提升查询效率。以下是一些重要的查询语句重构技巧。避免全表扫描添加合适索引如前文所述为经常出现在WHERE条件中的列添加索引是避免全表扫描的有效方法。例如在students表中若经常根据student_id进行查询可创建索引CREATE INDEX idx_student_id ON students(student_id); 这样在执行SELECT * FROM students WHERE student_id 123;查询时数据库能够利用索引快速定位到目标记录而不是扫描整个表。优化查询条件确保查询条件能够命中索引。避免使用导致索引失效的操作如函数操作、数据类型不一致、模糊查询前缀通配符等。例如对于orders表的order_date列若查询语句为SELECT * FROM orders WHERE TO_CHAR(order_date, ‘YYYY - MM - DD’) ‘2023 - 01 - 01’; 由于对order_date进行了函数操作索引将失效应改为SELECT * FROM orders WHERE order_date TO_DATE(‘2023 - 01 - 01’, ‘YYYY - MM - DD’); 以确保索引能够被使用。避免笛卡尔积笛卡尔积是指在多表查询时没有添加有效的连接条件导致结果集为两张表记录数的乘积这会产生大量的数据严重影响性能。例如在departments表和employees表进行查询时若写成SELECT * FROM departments, employees; 就会产生笛卡尔积。正确的做法是添加连接条件如SELECT * FROM departments d JOIN employees e ON d.department_id e.department_id; 这样可以确保查询结果是基于正确的连接关系得到的避免了大量无用数据的产生。合理使用 JOIN内连接INNER JOIN内连接用于返回两个表中满足连接条件的所有记录。例如在查询orders表和customers表中获取订单及其对应的客户信息时可使用内连接SELECT o.order_id, c.customer_name FROM orders o INNER JOIN customers c ON o.customer_id c.customer_id; 。内连接适用于只需要获取两个表中相互关联的数据的场景。左连接LEFT JOIN左连接会返回左表中的所有记录以及右表中满足连接条件的记录。如果右表中没有匹配的记录则对应字段为NULL。例如在查询所有员工及其所属部门信息时即使某个员工没有分配部门即departments表中没有对应的记录也希望在结果中显示该员工的信息可使用左连接SELECT e.employee_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id d.department_id; 。右连接RIGHT JOIN右连接与左连接相反它会返回右表中的所有记录以及左表中满足连接条件的记录。在实际应用中右连接的使用场景相对较少因为可以通过左连接进行等价转换。例如SELECT e.employee_name, d.department_name FROM departments d RIGHT JOIN employees e ON d.department_id e.department_id; 与上述左连接查询的结果是等价的只是表的顺序发生了变化。子查询优化子查询与 JOIN 转换在某些情况下子查询可以转换为 JOIN 来提高性能。例如子查询SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name ‘HR’); 可以转换为 JOINSELECT e.* FROM employees e JOIN departments d ON e.department_id d.department_id AND d.department_name ‘HR’; 。JOIN 操作通常比子查询更高效因为它可以在一次扫描中完成数据的匹配而子查询可能需要多次扫描表。避免多层嵌套子查询多层嵌套子查询会增加查询的复杂度和执行时间。尽量减少子查询的嵌套层数将复杂的子查询拆分成多个简单的查询或者使用 JOIN 来替代。例如对于三层嵌套子查询SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2 WHERE column2 IN (SELECT column3 FROM table3 WHERE condition)); 可以尝试将其重构为 JOIN 操作以提高查询性能。使用临时表和 WITH 子句临时表当需要多次使用某个复杂查询的结果时可以将该结果存储在临时表中以减少重复计算。例如在一个复杂的报表查询中需要多次使用某个中间结果集可先将该结果集存储在临时表中CREATE TEMPORARY TABLE temp_result AS SELECT column1, column2 FROM table1 WHERE condition; 然后在后续的查询中直接使用临时表SELECT * FROM temp_result WHERE another_condition; 。临时表在会话结束时会自动删除不会占用过多的系统资源。WITH 子句CTEWITH 子句Common Table Expression公共表表达式也可以用于定义一个临时的命名结果集它的作用类似于临时表但语法更加简洁并且作用域仅限于当前查询。例如使用 WITH 子句查询员工及其所属部门的平均工资WITH avg_salary AS (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) SELECT e.employee_name, d.department_name, a.avg_sal FROM employees e JOIN departments d ON e.department_id d.department_id JOIN avg_salary a ON e.department_id a.department_id; 。WITH 子句可以提高查询的可读性和可维护性同时在某些情况下也能提升查询性能。2.3 关联查询与子查询优化在数据库查询中关联查询和子查询是常用的操作但如果使用不当容易导致性能问题。因此了解它们的优化方法对于提升数据库性能至关重要。关联查询优化内连接INNER JOIN内连接是最常用的关联查询类型之一它返回两个表中满足连接条件的所有行。在优化内连接时首先要确保连接条件上有合适的索引。例如在orders表和products表进行内连接查询时连接条件为orders.product_id products.product_id 那么在orders表和products表的product_id列上都应创建索引这样可以加快连接操作的速度。同时根据实际业务需求合理选择连接顺序也能提高性能。通常情况下应将数据量小的表作为驱动表因为驱动表的每一行都要与被驱动表进行匹配数据量小可以减少匹配次数。左连接LEFT JOIN左连接返回左表中的所有行以及右表中满足连接条件的行。在优化左连接时要注意左表和右表的顺序不能随意调换。因为左连接是以左表为基准即使右表中没有匹配的记录左表中的记录也会出现在结果集中。例如在查询所有客户及其订单信息时即使某个客户没有订单也希望在结果中显示该客户的信息此时就需要使用左连接并且客户表应作为左表。此外同样要确保连接条件上有索引以提高连接效率。右连接RIGHT JOIN右连接与左连接相反它返回右表中的所有行以及左表中满足连接条件的行。右连接的优化方法与左连接类似但由于右连接可以通过左连接进行等价转换所以在实际应用中通常优先考虑使用左连接。例如SELECT * FROM table1 RIGHT JOIN table2 ON table1.id table2.id; 可以等价转换为SELECT * FROM table2 LEFT JOIN table1 ON table2.id table1.id; 。外连接FULL OUTER JOIN外连接返回两个表中所有满足连接条件的行以及左表和右表中不满足连接条件的行。在达梦数据库中没有直接的FULL OUTER JOIN语法但可以通过UNION操作将左连接和右连接的结果合并来实现类似的效果。例如(SELECT * FROM table1 LEFT JOIN table2 ON table1.id table2.id) UNION (SELECT * FROM table1 RIGHT JOIN table2 ON table1.id table2.id); 。在使用这种方式实现外连接时要注意去重操作以避免结果集中出现重复的记录。子查询优化子查询与 JOIN 的转换子查询和 JOIN 在某些情况下可以相互转换而 JOIN 操作通常比子查询更高效。例如子查询SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name ‘IT’); 可以转换为 JOINSELECT e.* FROM employees e JOIN departments d ON e.department_id d.department_id AND d.department_name ‘IT’; 。这是因为 JOIN 操作可以在一次扫描中完成数据的匹配而子查询可能需要多次扫描表从而增加了查询的时间和资源消耗。避免多层嵌套子查询多层嵌套子查询会使查询的执行计划变得复杂增加查询的时间和资源消耗。因此应尽量避免使用多层嵌套子查询。如果确实需要使用子查询可以尝试将复杂的子查询拆分成多个简单的子查询或者将子查询转换为 JOIN 操作。例如对于三层嵌套子查询SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2 WHERE column2 IN (SELECT column3 FROM table3 WHERE condition)); 可以将其拆分成两个子查询或者转换为 JOIN 操作以提高查询的性能。使用 EXISTS 替代 IN在某些情况下使用EXISTS关键字替代IN关键字可以提高子查询的性能。IN子查询是先执行子查询然后将结果集与主查询进行比较而EXISTS子查询则是先执行主查询然后根据主查询的每一行去检查子查询是否存在匹配的行。当子查询返回的结果集较大时使用EXISTS通常会比IN更高效。例如SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id d.department_id AND d.department_name ‘HR’); 比SELECT * FROM employees e WHERE e.department_id IN (SELECT department_id FROM departments WHERE department_name ‘HR’);三、进阶优化参数调优与特殊场景处理3.1 关键参数调优dm.ini在达梦数据库中dm.ini 文件是核心配置文件其中众多参数对 SQL 性能有着至关重要的影响。合理调整这些参数能够显著提升数据库的运行效率和响应速度。内存分配参数BUFFER该参数用于设置系统缓冲区大小以 M 为单位。系统缓冲区主要用于缓存数据页对查询性能影响极大。一般建议将其配置为操作系统物理内存的 60% - 80% 例如若服务器拥有 64GB 物理内存可将 BUFFER 设置为 38400M64GB * 60% 。这样可以确保数据库有足够的内存来缓存常用数据页减少磁盘 I/O 操作从而提高查询速度。BUFFER_POOLS它表示 BUFFER 系统分区数每个 BUFFER 分区的大小为 BUFFER / BUFFER_POOLS。默认值是 19在内存不大的情况下建议调小该值以提高内存使用效率。例如当 BUFFER 设置为 38400M 时如果将 BUFFER_POOLS 设置为 10每个分区大小为 3840M这样可以更精细地管理内存避免内存碎片化。MEMORY_POOL共享内存池初始大小以 M 为单位有效值范围为 64 - 67108864。共享内存池是由 DM 管理的内存在物理内存较大如大于 64G的情况下可以将 MEMORY_POOL 设置为 2048即 2G 以满足数据库对内存的需求。MEMORY_TARGET共享内存池在扩充最大值以 M 为单位有效值范围为 0 - 671088640 表示不限制。建议设置该参数以确保共享内存池在必要时能够合理扩充满足数据库运行的内存需求。缓存设置参数KEEPKEEP 缓冲区大小以 M 为单位默认 8M有效值范围为 8 - 1048576。KEEP 缓冲区的特性是对其中的数据页很少或几乎不淘汰出去主要针对那些需要经常处于内存中的数据。例如对于一些热点数据表可以将其数据页缓存到 KEEP 缓冲区中提高数据访问速度。RECYCLERECYCLE 缓冲区供临时表空间使用其大小以 M 为单位有效值范围为 8 - 1048576建议设置为 500M 以上以满足临时表空间对内存的需求。在高并发或大量使用临时表、排序等操作时适当增大 RECYCLE 缓冲区可以提高操作效率。RECYCLE_POOLSRECYCLE 缓冲区分区数每个 RECYCLE 分区的大小为 RECYCLE / RECYCLE_POOLS。有效值范围为 1 - 512一般配置为质数建议 RECYCLE / RECYCLE_POOLS 500MB以提高内存使用效率。QUERY_CACHE_SIZE用于设置查询缓存的大小单位是 KB是一个静态参数需重启数据库才能生效。通常建议设置为物理内存的 10% - 20% 但不应超过系统可用内存的 30%。例如对于 64GB 物理内存的服务器可将 QUERY_CACHE_SIZE 设置为 6400MB64GB * 10%左右。通过设置查询缓存可以缓存频繁查询的结果集减少重复查询的开销提高查询性能。ENABLE_QUERY_CACHE该参数用于启用或禁用查询缓存1 表示启用0 表示禁用可通过SP_SET_PARA_VALUE(2, ‘ENABLE_QUERY_CACHE’,1);动态开启或关闭缓存不改变大小。在 OLTP 系统中如果查询重复率低可以适当减小缓存大小或关闭缓存因为维护缓存也会消耗一定的系统资源而对于报表系统由于查询相对稳定且重复率较高可增大缓存以提高性能。排序与哈希参数SORT_BUF_SIZE排序区大小默认为 2。在内存小于 64G 时建议设置为 10M大于 64G 时建议设置为 512M。合理设置排序区大小可以优化排序操作的性能减少排序过程中的磁盘 I/O。SORT_BUF_GLOBAL_SIZE默认为 1000M。当内存小于 16G 时建议设置为 500M大于 16G 小于 64G 时建议设置为 2000M当内存大于 64G 时建议设置为 5120M 。该参数控制排序操作的全局内存大小对于大规模排序操作适当增大该参数可以提高排序效率。HJ_BUF_SIZE单个 HASH 连接操作符的数据总缓存大小默认值 50M有效值范围为 2 - 100000。在大数据量关联查询中若内存不足可调整该参数以提高哈希连接的性能。例如当服务器物理内存大于 16GB小于 64GB 时建议将 HJ_BUF_SIZE 设置为 500M 。HJ_BUF_GLOBAL_SIZEHASH 连接操作符的数据总缓存大小HJ_BUF_SIZE默认为 500M系统级参数以兆为单位有效值范围为 10 - 500000。当物理内存大于 16GB小于 64GB 时建议将 HJ_BUF_GLOBAL_SIZE 设置为 10000M 以上以满足大数据量关联查询对内存的需求。3.2 大批量数据与高并发场景优化在实际应用中经常会遇到大批量数据操作和高并发访问的场景这些场景对数据库的性能提出了严峻的挑战。通过合理的优化策略可以有效提升 SQL 在这些场景下的执行性能。大批量数据操作优化批量插入使用INSERT ALL INTO VALUES语法进行批量插入能够减少网络往返次数显著提升批量插入效率。例如传统的单条插入语句INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, ‘John’, ‘Doe’); 若要插入多条记录需要多次执行该语句。而使用批量插入语法INSERT ALL INTO employees (employee_id, first_name, last_name) VALUES (1, ‘John’, ‘Doe’) INTO employees (employee_id, first_name, last_name) VALUES (2, ‘Jane’, ‘Smith’) SELECT * FROM DUAL; 可以将多条插入操作合并为一次大大提高插入速度。此外也可以结合应用程序使用 JDBC 的addBatch()方法进行批量提交进一步减少网络交互。事务控制在进行大批量数据插入、更新或删除操作时合理控制事务的提交频率至关重要。默认情况下每条 INSERT 语句后都会提交事务这在大数据量操作时会导致频繁的日志写入增加 I/O 负担。可以通过设置COMMIT_BATCH_SIZE参数合并事务提交频率减少日志写入次数。例如将COMMIT_BATCH_SIZE设置为 1000表示每插入 1000 条记录后提交一次事务这样可以有效减少 I/O 操作提高数据操作效率。索引策略在进行大批量数据插入前禁用非必要的索引可以避免索引维护带来的性能损耗。因为插入操作需要同时维护索引结构索引越多写入性能越差。例如对于一个包含多个索引的employees表在进行大批量数据插入前可以使用ALTER INDEX index_name DISABLE;语句禁用相关索引插入完成后再使用ALTER INDEX index_name REBUILD;语句重建索引以恢复索引的正常功能。高并发场景优化事务控制在高并发环境下确保事务的原子性、一致性、隔离性和持久性ACID 属性至关重要。达梦数据库实现了基于快照隔离级别的 MVCC 模型允许非阻塞读取操作在高并发读写混合负载中表现良好。通过合理设置事务的隔离级别可以在保证数据一致性的前提下提高并发性能。例如对于读多写少的场景可以将事务隔离级别设置为READ COMMITTED SNAPSHOT这样可以避免读操作被写操作阻塞提高系统的并发处理能力。锁机制优化达梦数据库采用乐观锁与悲观锁相结合的设计思路。在低冲突场景下启用乐观并发控制减少锁等待开销提高系统的并发性能。当检测到争用加剧时动态切换为悲观锁模式以保证数据的一致性。例如在一个电商系统中对于商品库存的更新操作在并发量较低时可以使用乐观锁只有在更新失败时才进行重试而在并发量较高时切换为悲观锁确保库存数据的准确性。读写分离对于读操作频繁的高并发场景可以采用读写分离架构将读操作和写操作分别分配到不同的数据库节点上。达梦数据库支持读写分离集群架构具备多种负载均衡策略与自动路由功能可实现查询性能随节点数量线性增长。通过读写分离可以减轻主数据库的压力提高系统的整体性能和可用性。例如在一个新闻网站中大量用户同时进行新闻浏览读操作而新闻发布写操作相对较少采用读写分离架构可以将读操作分配到多个从数据库节点上提高新闻浏览的响应速度。四、实战案例典型慢 SQL 优化落地案例 1多表关联慢查询优化在一个电商系统中有三张表orders订单表、customers客户表和products产品表。orders表记录了订单的相关信息customers表存储了客户的详细资料products表包含了产品的具体信息。现在需要查询每个客户的订单信息以及对应的产品名称原始的 SQL 语句如下SELECTc.customer_name,o.order_id,o.order_date,p.product_nameFROMorders oJOINcustomers cONo.customer_idc.customer_idJOINproducts pONo.product_idp.product_id;这条 SQL 语句在数据量较小的时候运行正常但随着业务的发展数据量逐渐增大查询速度变得非常缓慢。通过使用EXPLAIN关键字查看执行计划发现orders表和customers表、products表关联时都进行了全表扫描这是导致查询缓慢的主要原因。针对这个问题我们采取了以下优化措施创建索引在orders表的customer_id和product_id列、customers表的customer_id列以及products表的product_id列上分别创建索引。创建索引的语句如下CREATEINDEXidx_orders_customer_idONorders(customer_id);CREATEINDEXidx_orders_product_idONorders(product_id);CREATEINDEXidx_customers_customer_idONcustomers(customer_id);CREATEINDEXidx_products_product_idONproducts(product_id);优化关联方式分析数据量和查询特点发现customers表的数据量相对较小而orders表和products表的数据量较大。因此将关联顺序调整为以customers表为驱动表先与orders表进行关联再与products表进行关联。优化后的 SQL 语句如下SELECTc.customer_name,o.order_id,o.order_date,p.product_nameFROMcustomers cJOINorders oONc.customer_ido.customer_idJOINproducts pONo.product_idp.product_id;优化后再次查看执行计划发现索引被有效利用全表扫描的操作被避免查询速度得到了显著提升。通过性能测试工具对比优化前后的查询时间优化前查询时间平均为 5 秒优化后查询时间缩短至 0.5 秒性能提升了 10 倍。案例 2统计查询慢 SQL 优化在一个销售系统中有一张sales表记录了销售订单的详细信息包括订单日期、产品 ID、客户 ID 和销售金额等字段。现在需要统计每个产品的总销售额以及销售次数原始的 SQL 语句如下SELECTproduct_id,SUM(sales_amount)AStotal_sales,COUNT(*)ASsales_countFROMsalesGROUPBYproduct_id;随着数据量的不断增加这条统计查询的执行时间越来越长。通过分析执行计划发现存在以下问题缺少索引product_id列上没有索引导致在分组和统计时需要全表扫描效率低下。不必要的排序在执行GROUP BY操作时数据库默认会对分组字段进行排序这在数据量较大时会消耗大量的时间和资源。针对这些问题我们采取了以下优化措施创建索引在product_id列上创建索引以加快分组和统计的速度。创建索引的语句如下CREATEINDEXidx_sales_product_idONsales(product_id);优化分组条件使用HASH GROUP BY方式替代默认的排序分组方式减少排序操作。在达梦数据库中可以通过在 SQL 语句中添加/* USE_HASH_AGGREGATION */提示来实现。优化后的 SQL 语句如下SELECT/* USE_HASH_AGGREGATION */product_id,SUM(sales_amount)AStotal_sales,COUNT(*)ASsales_countFROMsalesGROUPBYproduct_id;优化后再次执行查询通过性能测试工具对比优化前查询时间平均为 3 秒优化后查询时间缩短至 0.3 秒性能提升了 10 倍。通过创建索引和优化分组条件有效地提高了统计查询的效率。五、常见问题排查与优化误区5.1 常见问题排查在 SQL 优化过程中经常会遇到各种问题以下是一些常见问题及排查和解决思路。锁等待当多个事务同时访问相同的数据资源时可能会发生锁等待现象导致部分事务长时间等待影响系统性能。可以通过查询V$LOCK视图来排查锁等待问题例如SELECT * FROMV$LOCKWHERE BLOCKED 1;该语句可以查询出被阻塞的锁信息从而定位到锁等待的源头。解决锁等待问题的方法包括优化事务逻辑尽量减少事务的持有时间调整事务的隔离级别在保证数据一致性的前提下降低锁冲突的概率对于长时间持有锁的事务可考虑手动回滚或提交该事务以释放锁资源。资源争用资源争用是指多个数据库操作同时竞争有限的系统资源如 CPU、内存、磁盘 I/O 等这会导致系统性能下降。可以通过系统监控工具如 top、iostat 等和数据库视图如V$SESSION_WAIT、V$SYSTEM_EVENT等来监控资源使用情况。例如通过SELECT * FROMV$SESSION_WAITWHERE WAIT_TYPE ‘IO_WAIT’;可以查询出等待 I/O 资源的会话信息判断是否存在 I/O 瓶颈。解决资源争用问题的方法包括增加系统资源如扩充内存、升级磁盘等优化 SQL 语句减少资源消耗合理分配资源例如调整数据库参数优化内存分配和 I/O 调度。索引失效索引失效是导致 SQL 查询性能下降的常见原因之一。常见的索引失效场景包括索引列使用函数、数据类型不一致、模糊查询前缀通配符、OR 条件等。可以通过查看执行计划来判断索引是否失效若执行计划中出现全表扫描CSCN2操作而预期应该使用索引则可能是索引失效。解决索引失效问题的方法包括创建函数索引以应对查询条件中对索引列使用函数的情况确保查询条件的数据类型与索引列的数据类型一致避免隐式类型转换避免使用模糊查询前缀通配符或改用全文索引对于 OR 条件导致的索引失效可考虑将查询拆分成多个UNION ALL的查询或者创建覆盖多个字段的复合索引。统计信息过期数据库的查询优化器依赖统计信息来生成执行计划。当表数据发生大量增删改后统计信息可能过期导致优化器生成的执行计划不合理从而影响查询性能。可以通过查询V$SYSTEM_PARAMETER视图来确认统计信息自动更新的参数设置例如SELECT PARAM_NAME, PARAM_VALUE FROMV$SYSTEM_PARAMETERWHERE PARAM_NAME ‘AUTO_STAT_UPD’;。解决统计信息过期问题的方法是手动更新统计信息使用ANALYZE TABLE语句如ANALYZE TABLE table_name COMPUTE STATISTICS;该语句可以重新计算表的统计信息使优化器能够生成更准确的执行计划。5.2 优化误区在进行 SQL 优化时存在一些常见的误区若不加以注意可能会导致性能问题未得到解决甚至恶化。过度创建索引索引虽然能够提高查询效率但并非越多越好。创建过多的索引会占用大量的磁盘空间增加数据插入、更新和删除操作的时间因为在进行这些操作时数据库需要同时维护索引结构。此外过多的索引还可能导致查询优化器在选择执行计划时花费过多的时间反而降低了查询性能。例如在一个数据量较小的表上创建多个索引或者为低区分度的列创建索引都可能是不必要的。因此在创建索引时应根据实际查询需求有针对性地创建避免过度创建索引。盲目使用复杂查询复杂查询如多层嵌套子查询、复杂的关联查询等虽然能够实现复杂的业务逻辑但往往也会带来性能问题。复杂查询通常需要更多的资源和时间来执行因为它涉及更多的数据扫描和计算。同时复杂查询的执行计划也更难优化容易出现全表扫描、低效关联等问题。例如在一个查询中使用了多层嵌套子查询而这些子查询可以通过 JOIN 操作更高效地实现此时使用多层嵌套子查询就会导致性能下降。因此在编写 SQL 语句时应尽量避免使用复杂查询优先考虑使用简单、高效的查询方式来实现业务需求。忽视数据库参数调优数据库参数的设置对 SQL 性能有着重要影响但有些开发者或管理员在优化 SQL 时往往忽视了参数调优。例如缓冲区大小设置过小会导致频繁的磁盘 I/O 操作降低查询性能排序区大小设置不合理会导致排序操作效率低下。因此在进行 SQL 优化时应根据数据库的实际运行环境和业务需求合理调整数据库参数以提高 SQL 性能。未考虑业务场景SQL 优化不仅仅是技术层面的优化还需要考虑业务场景。在优化 SQL 时若不了解业务需求和数据特点可能会采取不恰当的优化措施导致优化效果不佳。例如在一个读多写少的业务场景中过度优化写入性能而忽视了读取性能的优化就会影响业务的正常运行。因此在进行 SQL 优化时应充分了解业务场景根据业务需求和数据特点制定合理的优化策略。六、总结在达梦数据库的使用中SQL 优化是提升系统性能、保障业务高效运行的关键环节。通过一系列的优化实战技巧我们能够显著提升 SQL 语句的执行效率减少资源消耗为企业应用提供稳定、高效的数据支持。在优化前置准备阶段利用 SQL 日志、SQLark、DEM 和 AWR 报告等工具精准定位慢 SQL并深入解读执行计划能够快速发现性能瓶颈为后续优化提供方向。索引优化作为核心技巧之一按需创建普通索引、复合索引、函数索引和分区索引同时避免索引失效定期进行索引维护是提升查询效率的重要手段。查询语句重构则要求我们遵循高效语法实践避免全表扫描、笛卡尔积等低效操作合理使用 JOIN 和子查询并善于利用临时表和 WITH 子句。在关联查询和子查询优化中根据不同的业务场景选择合适的关联方式和优化策略能够有效提高查询性能。进阶优化方面深入理解并合理调整 dm.ini 中的关键参数如内存分配、缓存设置、排序与哈希等参数能够从系统层面提升 SQL 性能。对于大批量数据操作和高并发场景采用相应的优化策略如批量插入、合理控制事务、优化索引策略以及优化事务控制、锁机制和读写分离等能够确保系统在复杂场景下的稳定运行。通过实际案例我们看到了这些优化技巧在解决具体问题中的显著效果。同时在优化过程中我们也要善于排查常见问题如锁等待、资源争用、索引失效和统计信息过期等并避免陷入过度创建索引、盲目使用复杂查询、忽视数据库参数调优和未考虑业务场景等优化误区。SQL 优化并非一蹴而就数据库的性能会随着业务的发展、数据量的增长以及用户需求的变化而动态变化。因此持续监控和优化 SQL 性能至关重要。建议读者在实际工作中不断积累经验灵活运用这些优化技巧针对不同的业务场景和数据特点制定个性化的优化方案以实现达梦数据库性能的最大化。