dm8 SQL性能优化学习总结

引言

作为数据库运维人员，SQL性能优化是一个无法绕过且复杂的话题。它不仅涉及到查询语句的编写技巧，还包括数据库设计、索引规划、执行计划分析等多个层面。sql的优化能力也是运维人员最重要的技能之一，能够快速定位sql语句的性能问题，并且提出恰当的优化方案是一个需要长期锻炼的重要能力。本文将从基础的sql性能优化出发，结合部分过去优化其他类型数据库的经验，做出一些简单的学习总结。

SQL性能问题的原因分析

SQL性能问题通常由以下几个方面引起：

查询逻辑复杂：涉及大量数据的多表关联查询，特别是不恰当的连接条件、关联列缺少索引等导致关联后查询代价几何级增加，除此之外还有不合理的查询逻辑，例如滥用not exist等语法造成的代价增加。
索引使用不当：未为查询条件创建合适的索引或索引未能被查询条件有效利用，其中较为常见的是索引区分度不高、查询条件中未包含联合索引的最左列等问题。
统计信息不准确：数据库优化器依赖统计信息生成执行计划，统计信息不准确会导致优化器做出错误的决策，对于新迁移的表、数据变动量较大的表，应当注意统计信息的实时性，最好是配置定时的统计信息更新任务。
系统资源限制：硬件资源（如CPU、内存、I/O）的限制也会影响SQL语句的执行效率。比较常见的瓶颈在于io，即磁盘的读写效率，同步在涉及大数据量的关联查询、复杂逻辑结构分析时，也需要对内存和cpu起到一定的资源消耗，具体需要根据实际情况进行分析。

SQL优化的策略与实践

1. 定位慢SQL

dm8本身提供了一系列配置，供运维人员定位慢sql、高代价sql，我个人理解，慢sql、高代价sql、大sql三者并不能简单的等同看待，而应当根据实际情况判断优化的优先级，例如某类定期导数、稽核性质的sql，仅定时的在业务低峰期执行，虽耗时较长，涉及数据量较大，但优化优先级并不高，甚至可以忽略。

1.1 开启跟踪日志记录

通过配置SVR_LOG参数和sqllog.ini文件，可以启用SQL日志记录，记录下执行时间超过特定阈值的SQL语句，即是我们常见的慢sql日志，这是最常用且直接的记录问题方法，但也要考虑对慢sql的日志本身是否也会拖累数据库的运行性能，因此达梦也引入了异步日志功能，尽量减少记录日志本身对数据库的消耗。可以通过直接阅读慢sql日志，或调用额外的日志分析工具DMlog（类似于mysql常用的pt-digest-query）进行进一步的分析，定位对当前数据库性能影响较大的待优化语句。

1.2 系统视图分析

利用系统视图如V$LONG_EXEC_SQLS，自动记录并分析执行时间超过设定阈值的SQL语句。开启系统视图需要配置动态参数 ENABLE_MONITOR=1、MONITOR_TIME=xxxx，但enable_monitor等于1的情况下，对某些大sql可能会造成性能影响，从我个人的角度出发，我认为使用跟踪日志是更为合适也更为易用的方法。

2. SQL语句分析与优化

2.1 执行计划分析

执行计划是数据库执行SQL语句的具体步骤，分析执行计划是最重要的sql性能定位步骤之一，通过分析执行计划，可以看出sql语句在运行过程中的具体动作，判断具体的性能瓶颈位置，包括是否存在索引规划不合适、统计信息过期等问题。

2.2 常见操作符解读与优化

NSET：结果集收集，通常无需优化。
PRJT：投影操作，优化空间较小。
SLCT：选择操作，可以关注执行计划中的估算结果集一列，即方框中的第二处，据此估计该列的过滤性，若过滤性好可在此列考虑添加索引。
AAGR/FAGR：聚集函数，主要出现在没有过滤条件或分组的情况下，聚集函数的计算。
HAGR： HASH 分组聚集，分组列没有索引，只能走全表扫描，若该处代价较高，可以考虑对分组列添加索引。
SAGR：有序的分组聚集，性能高于HAGR，可理解为HAGR加了索引后的表现。
BLKUP：即回表，表示通过二级索引过滤完之后，还需要通过主键等再次查询，优化方案可以是建立覆盖索引，即将该语句中所有需要查询的字段建立一个联合索引，或者建立聚簇索引。
CSCN：全表扫描，即CLUSTER INDEX SCAN，一般是代价最高，最优先需要优化的。
SSEK：二级索引扫描，一般来说扫完还需要回表，应当和BLKUP前后出现。
CSEK：聚簇索引扫描，相比SSEK省略了回表过程，效率更高。
SSCN：索引全扫，无需再扫表，即上面提到的覆盖索引情况。
NEST LOOP：嵌套循环连接，一般在关联查询下出现，关注该列代价可以看出关联查询是否设计合理
HASH JOIN：在连接列没有索引的情况下，常用的优化方法，效率高但对内存资源消耗较大
MERGE JOIN：连接列有索引的情况下使用的连接方式

3. 索引优化

dm的索引存储结构采用的是b*tree，与mysql使用的b+tree稍有差异，主要体现在b*tree结点使用率要求为2/3，略高于b+tree的一半，但基本优化思路类似，一是需要减少检索的树高度，即减少叶子节点的层数，二是尽量减少回表。

3.1 索引创建原则

为高频查询字段创建索引。
避免为低选择性的列创建索引，即选择过滤性高的字段。
考虑联合索引以优化多条件查询。
不要过多创建赘余索引，避免占用存储空间。
但在使用过程中，还需要考虑部分索引无法使用的情况：
组合索引中，条件列中没有组合索引的首列。
条件列带有函数或计算。
索引排序是按照字段值进行排序的，字段值通过函数或计算后的值索引无法获取。
索引过滤性能不好时。

3.2 索引维护

定期关注并更新索引统计信息，避免因统计信息过期导致的执行计划变动。

4. SQL语句改写技巧

改写SQL语句可以减少资源消耗，提升执行效率。

4.1 优化`GROUP BY`

通过WHERE子句预先过滤，尽量将更少的结果集放入分组计算中，减少分组计算的代价。

4.2 使用`UNION ALL`替换`UNION`

当合并操作不需要去除重复数据时，使用UNION ALL替换掉UNION，

4.3 使用`EXISTS`替换`IN`

在子查询中，使用EXISTS可以避免不必要的全表扫描。

4.4 多用commit

在程序使用过程中，多使用commit，即在不影响程序逻辑的前提下，尽量将大事务拆分为多个小事务，可以提高程序的运行效率，释放出在运行过程中所持的锁等资源。

个人思考与总结

在实际工作中，SQL优化是一个需要持续关注和投入的过程。在日常运维过程中，应该培养性能的敏感度，及时发现性能退化。实际的业务运行过程中，数据库仅是其中的一环，当遇到性能问题时，在分析慢SQL的同时，还要考虑分析整个应用的性能瓶颈，从整体链路出发判断性能的瓶颈所在。当然，最重要的一点仍然是需要不断的学习和实践，积累丰富的经验，才能更高效的定位和处理问题。

引言