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

原创已于 2026-03-18 13:28:09 修改 · 523 阅读

13 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

经验分享

于 2026-03-18 13:05:47 首次发布

一、优化前置准备：慢 SQL 定位与执行计划分析
- 1.1 慢 SQL 定位工具与方法
- 1.2 达梦执行计划解读实战
二、核心优化技巧：从索引到语句重构
三、进阶优化：参数调优与特殊场景处理
- 3.1 关键参数调优（dm.ini）
- 3.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代表 SELECT，2代表 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 FROM V$SQL_STATISTICS WHERE 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 报告：AWR（Automatic 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表，创建本地分区索引的语句如下：

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    customer_id NUMBER,
    sale_date DATE,
    amount NUMBER
) PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES LESS THAN (DATE '2023 - 01 - 01'),
    PARTITION p2 VALUES LESS THAN (DATE '2023 - 07 - 01'),
    PARTITION p3 VALUES LESS THAN (DATE '2024 - 01 - 01')
);
CREATE INDEX sales_date_idx ON sales(sale_date) LOCAL (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3
);

全局分区索引的分区方式与表的分区方式无关，可以根据不同的列进行分区，如：

CREATE INDEX sales_amount_idx ON sales(amount) GLOBAL PARTITION BY RANGE (amount) (
    PARTITION pa VALUES LESS THAN(100),
    PARTITION pb VALUES LESS THAN(500),
    PARTITION pc VALUES LESS 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 子句（CTE）：WITH 子句（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 设置为 38400M（64GB * 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 - 67108864，0 表示不限制。建议设置该参数，以确保共享内存池在必要时能够合理扩充，满足数据库运行的内存需求。
缓存设置参数：
- KEEP：KEEP 缓冲区大小，以 M 为单位，默认 8M，有效值范围为 8 - 1048576。KEEP 缓冲区的特性是对其中的数据页很少或几乎不淘汰出去，主要针对那些需要经常处于内存中的数据。例如，对于一些热点数据表，可以将其数据页缓存到 KEEP 缓冲区中，提高数据访问速度。
- RECYCLE：RECYCLE 缓冲区供临时表空间使用，其大小以 M 为单位，有效值范围为 8 - 1048576，建议设置为 500M 以上，以满足临时表空间对内存的需求。在高并发或大量使用临时表、排序等操作时，适当增大 RECYCLE 缓冲区可以提高操作效率。
- RECYCLE_POOLS：RECYCLE 缓冲区分区数，每个 RECYCLE 分区的大小为 RECYCLE / RECYCLE_POOLS。有效值范围为 1 - 512，一般配置为质数，建议 RECYCLE / RECYCLE_POOLS >= 500MB，以提高内存使用效率。
- QUERY_CACHE_SIZE：用于设置查询缓存的大小，单位是 KB，是一个静态参数，需重启数据库才能生效。通常建议设置为物理内存的 10% - 20% ，但不应超过系统可用内存的 30%。例如，对于 64GB 物理内存的服务器，可将 QUERY_CACHE_SIZE 设置为 6400MB（64GB * 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_SIZE：HASH 连接操作符的数据总缓存大小（>=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 语句如下：

SELECT c.customer_name, o.order_id, o.order_date, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

这条 SQL 语句在数据量较小的时候运行正常，但随着业务的发展，数据量逐渐增大，查询速度变得非常缓慢。通过使用EXPLAIN关键字查看执行计划，发现orders表和customers表、products表关联时都进行了全表扫描，这是导致查询缓慢的主要原因。

针对这个问题，我们采取了以下优化措施：

创建索引：在orders表的customer_id和product_id列、customers表的customer_id列以及products表的product_id列上分别创建索引。创建索引的语句如下：

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_customers_customer_id ON customers(customer_id);
CREATE INDEX idx_products_product_id ON products(product_id);

优化关联方式：分析数据量和查询特点，发现customers表的数据量相对较小，而orders表和products表的数据量较大。因此，将关联顺序调整为以customers表为驱动表，先与orders表进行关联，再与products表进行关联。优化后的 SQL 语句如下：

SELECT c.customer_name, o.order_id, o.order_date, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id;

优化后，再次查看执行计划，发现索引被有效利用，全表扫描的操作被避免，查询速度得到了显著提升。通过性能测试工具对比优化前后的查询时间，优化前查询时间平均为 5 秒，优化后查询时间缩短至 0.5 秒，性能提升了 10 倍。

案例 2：统计查询慢 SQL 优化

在一个销售系统中，有一张sales表，记录了销售订单的详细信息，包括订单日期、产品 ID、客户 ID 和销售金额等字段。现在需要统计每个产品的总销售额以及销售次数，原始的 SQL 语句如下：

SELECT product_id, SUM(sales_amount) AS total_sales, COUNT(*) AS sales_count
FROM sales
GROUP BY product_id;

随着数据量的不断增加，这条统计查询的执行时间越来越长。通过分析执行计划，发现存在以下问题：

缺少索引：product_id列上没有索引，导致在分组和统计时需要全表扫描，效率低下。
不必要的排序：在执行GROUP BY操作时，数据库默认会对分组字段进行排序，这在数据量较大时会消耗大量的时间和资源。

针对这些问题，我们采取了以下优化措施：

创建索引：在product_id列上创建索引，以加快分组和统计的速度。创建索引的语句如下：

CREATE INDEX idx_sales_product_id ON sales(product_id);

优化分组条件：使用HASH GROUP BY方式替代默认的排序分组方式，减少排序操作。在达梦数据库中，可以通过在 SQL 语句中添加/*+ USE_HASH_AGGREGATION */提示来实现。优化后的 SQL 语句如下：

SELECT /*+ USE_HASH_AGGREGATION */ product_id, SUM(sales_amount) AS total_sales, COUNT(*) AS sales_count
FROM sales
GROUP BY product_id;

优化后，再次执行查询，通过性能测试工具对比，优化前查询时间平均为 3 秒，优化后查询时间缩短至 0.3 秒，性能提升了 10 倍。通过创建索引和优化分组条件，有效地提高了统计查询的效率。

五、常见问题排查与优化误区

5.1 常见问题排查

在 SQL 优化过程中，经常会遇到各种问题，以下是一些常见问题及排查和解决思路。

锁等待：当多个事务同时访问相同的数据资源时，可能会发生锁等待现象，导致部分事务长时间等待，影响系统性能。可以通过查询V$LOCK视图来排查锁等待问题，例如SELECT * FROM V$LOCK WHERE BLOCKED = 1;，该语句可以查询出被阻塞的锁信息，从而定位到锁等待的源头。解决锁等待问题的方法包括优化事务逻辑，尽量减少事务的持有时间；调整事务的隔离级别，在保证数据一致性的前提下，降低锁冲突的概率；对于长时间持有锁的事务，可考虑手动回滚或提交该事务，以释放锁资源。
资源争用：资源争用是指多个数据库操作同时竞争有限的系统资源，如 CPU、内存、磁盘 I/O 等，这会导致系统性能下降。可以通过系统监控工具（如 top、iostat 等）和数据库视图（如V$SESSION_WAIT、V$SYSTEM_EVENT等）来监控资源使用情况。例如，通过SELECT * FROM V$SESSION_WAIT WHERE 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 FROM V$SYSTEM_PARAMETER WHERE 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 性能至关重要。建议读者在实际工作中，不断积累经验，灵活运用这些优化技巧，针对不同的业务场景和数据特点，制定个性化的优化方案，以实现达梦数据库性能的最大化。

标签

#数据库 #达梦 #SQL优化 #实战