拒绝玄学调优！YashanDB科学分析精准狙击慢SQL

本文链接：https://blog.youkuaiyun.com/cod0410/article/details/146972808

在数据库管理中，SQL查询性能优化是最常见且关键的任务之一。尤其是在大型应用程序和高并发环境中，慢SQL查询会严重影响系统的响应时间和吞吐量，导致用户体验下降。本文将详细介绍如何通过视图、执行计划、AWR报告、慢日志、系统资源等方面定位、分析并优化慢SQL查询，以提升数据库性能。

1、什么是慢SQL？

慢SQL指的是那些执行时间过长的SQL查询。不同数据库系统对慢查询的定义标准不同，不同复杂程度的查询语句对慢查询的界定也存在较大差异。在高并发环境下，慢SQL可能会成为性能瓶颈，影响整个系统的响应速度和可扩展性。

2、定位慢SQL

定位慢SQL查询是性能优化的第一步，先找到慢SQL语句才能“对症下药”。YashanDB有着丰富的定位慢SQL方法，可以通过SQL视图，自动工作负载存储库报告（AWR），数据库系统的慢日志等方法来定位。

YashanDB数据库中，有大量SQL相关视图来记录SQL语句的相关信息，如表1所示，列出部分相关SQL视图：

通过查询这些视图中执行时间列，并按其降序输出就能获取相关慢SQL。其中视图相关字段如表2所示：

**注：**以下各SQL语句在不同环境中执行，查询结果存在差异。

**例：**查询执行时间最长的TOP3 SQL，可以使用如下SQL语句进行查询，可以得到查询的SQL语句以及对应的执行时间（毫秒），结果如图1所示：

select sql_text,elapsed_time from v$sqlarea order by elapsed_time desc limit 3;

执行计划可用于分析慢SQL，SQL视图也能查询慢SQL语句当时的执行计划。YashanDB的SQL_PLAN视图可用于慢SQL执行计划查询，该视图的部分字段如表3所示：

例：查询执行时间最长SQL的执行计划以及时间消耗，结果如图2所示：

select operation, cost from v $sql\_plan where sql\_id in (select sql\_id from v$ sqlarea order by elapsed_time desc limit 1);

AWR(Automatic Workload Repository)为自动工作负载存储库，它的核心是数据库每隔一段时间，将数据库的状态数据保存一份快照到数据库相关历史表中；然后通过生成AWR报告来分析数据库是否存在异常，如慢SQL等。

YashanDB的AWR通过内置高级包DBMS_AWR实现，主要包含两方面功能：快照管理与报告生成。快照的管理包括快照生成、快照配置更改、快照清理功能；报告生成的原理是将两次快照之间的SQL信息做差值，并将结果整理后输出。对应的SQL语句执行命令如表4中所示。

其中可以通过SYS.WRM $_SNAPSHOT: 查看当前已经生成的快照信息；通过SYS.WRM$ _WR_CONTROL：查看快照保存时间跟生成间隔。通过命令可以生成如图3的AWR报告，报告包含中数据库信息，快照信息，等待事件、多维度统计慢SQL语句。

YashanDB提供慢查询日志功能来记录执行时间超过阈值的查询。在使用慢日志功能时需要做如下设置：

1、开启慢日志功能，将配置参数ENABLE_SLOW_LOG设置为true。

2、设置慢日志阈值，对应配置参数SLOW_LOG_TIME_THRESHOLD；设置后执行时间超过阈值的会在日志中记录；SLOWLOG的目录在 YASDB_DATA/log/slow/slow.log。

图4为慢日志中的一条记录，时间阈值设置为100ms，记录了SQL语句、SQL_ID、运行时间、执行时间、返回结果集数目等信息。

在使用慢日志时有如下几点需要注意：

1、慢日志采用异步输出方式，即执行完不会立即输出，而是放到日志队列里，通过后台线程分批次输出。

2、日志队列最大长度256。

3、基于上述原理，在掉电情况下可能会丢失日志；如果时间阈值设置过小，致使队列满的情况下亦会丢失日志。

4、主备环境中，备库将SLOW_LOG_OUTPUT设置为FILE时，慢日志输出到文件，将备库的SLOW_LOG_OUTPUT设置为TABLE虽然不会报错，但不会生效，日志也不会继续输出到文件。备库SLOW_LOG $的内容是直接同步的主库的SLOW\_LOG$ 。

1、慢SQL如何产生的？

**编写较低效的SQL：**编写的SQL执行了不必要的操作，那么优化器将无法提升其性能，例如：没有连接条件的连接语句（笛卡尔积）、指定大表做驱动表、指定UNION而不是UNION ALL、使用子查询针对外部查询中的每一行执行等。
**执行计划不是最优：**表的连接顺序、统计信息、使用的索引、算子、过滤条件的应用顺序等都会影响执行计划。此外，使用低选择率的谓词条件，可能会在大表上使用全表扫描而不是索引；或者使用过时的统计信息。
**资源问题：**影响因素包括等待事件、内存、IO、网络、锁。

2、分析慢SQL

分析慢SQL需要依赖数据库已有的功能实现，以及相关从业人员的数据库能力。分析出慢SQL的性能瓶颈才有利于进一步的SQL调优。YashanDB提供大量的慢SQL分析方法，可以通过各种内存动态视图、系统级的配置参数以及执行计划等来辅助分析出语句的瓶颈点。

系统的资源是有限的，数据库只有利用好这些资源，才能将数据库的性能发挥到最优。在YashanDB中有大量的分析类视图，如：各内存使用情况、等待事件、锁使用等来协助分析慢SQL。表5中列举出部分分析类动态视图：

表中的视图在不同场景中有助于快速分析出慢SQL的瓶颈，以下列举部分视图的分析场景：

V$GLOBAL_MPOOL：

系统并发场景。当整体执行变慢时，就可以查看视图确认是否内存不足；如果不足则会触发内存extend以及recycle，额外增加开销，影响SQL执行速度。

V$PLANCACHE：

sqlmainpool内存不足时，会触发lru淘汰，将缓存对象换出，这样会导致SQL语句做硬解析。如果一个业务场景前面跑了大量语句，大量SQL占用plancache内存，导致后面的语句在执行完后就被淘汰，每次执行都需要重新走解析，生成计划，那业务系统的整体执行效率会大打折扣。

V$SPINLOCK：

对公共资源进行访问时，数据库通过锁机制来保证资源获取的正确性。通过spinlock视图就能查看出哪些资源的竞争过大导致等待次数增加，锁等待时间变成从而影响整个SQL运行效率。

V$VMSTAT：

数据库执行算子需要使用vm内存存储中间计算结果，当SQL语句很复杂时并且数据量比较大时，大量的内存需求会使vm与磁盘做换入换出来满足；而io的频繁读写会消耗大量时间，SQL语句执行自然变慢。可以通过视图观测SQL语句执行前后是否有换入换出作为一种分析手段。

从上述章节可以得到SQL语句性能的好坏跟数据库内存息息相关，不同的内存配置在特定场景下对性能有着极大的影响。本章节介绍YashanDB数据库中部分配置参数如表6所示，并指导如何配置才能获得较好的性能效果。

通过执行计划来分析数据库SQL性能是最常用，也是最重要的手段之一。执行计划上展示了查询的逻辑流程，包括表连接方式、聚合、排序、过滤等。这些信息对于诊断性能瓶颈、优化查询速度和提高资源利用率至关重要。YashanDB可以开启autotrace开关查看执行计划的详细信息，包括算子实际执行时间，算子执行时返回的结果集大小，评估的结果集大小，算子调用次数等。关于SQL语句应该选择何种计划，各算子的最优应用场景，可以阅读上一篇SQL优化系列文章《两个案例带你看懂YashanDB执行计划》进行深入了解。

慢SQL查询会对系统性能产生负面影响，优化慢查询是数据库中的重要任务。通过合理使用系统资源、分析执行计划、优化查询逻辑和连接操作，可以显著提高SQL查询的效率。性能优化是一个持续的过程，随着业务和数据量的增长，数据库查询的优化工作也需要不断进行调整和优化。YashanDB所提供的慢SQL定位与分析还有很多，更多的信息欢迎到崖山官网文档中心（https://doc.yashandb.com/）进行查阅。