JAVA开发常用类库--JDBC故障诊断

发表于 | 分类于 |

有不错的JDBC扩展库的存在使得调试变得很容易，例如P6spy，这是一个针对数据库访问操作的动态监测框架，它使得数据库数据可无缝截取和操纵，而不必对现有应用程序的代码作任何修改。P6Spy 分发包包括P6Log，它是一个可记录任何 Java 应用程序的所有JDBC事务的应用程序。其配置完成使用时，可以进行数据访问性能的监测。

在我们 Java 开发应用程序的过程中，难免会碰到系统的性能问题，特别在企业应用的开发过程中，都会与数据库进行打交道。当我们碰到数据库性能时，最有效的就是直接跟踪每一个 SQL 语句的执行情况，SQL 语句的优化、索引的优化往往也是最容易取得最直接的效果的。

在应用程序开发过程中，为了方便调试，通常都需要知道在DAO层程序执行的SQL是什么，而P6spy这个组件正是提供了该功能。

已在Github上开源：https://github.com/p6spy/p6spy

依赖添加

<dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>3.6.0</version>
</dependency>

然后将p6spy的配置文件spy.properties放置项目的src/main/resources目录下,该文件只需要修改logfile，logMessageFormat，dateformat属性即可，如下图所示:

logfile=mybatis-practices-core.log   //具体文件位置
logMessageFormat= net.ittimeline.mybatis.practices.core.p6spy.CustomizeLineFormat   //具体文件信息格式
dateformat=yyyy-MM-dd HH:mm:ss   //数据格式
databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss   //数据库方言数据格式

配置参数文档说明在 https://github.com/p6spy/p6spy/blob/master/docs/configandusage.md 中写的很清楚

然后实现自定义的SQL输出格式

为了输出的内容足够的简洁，这里只保留了当前时间，执行SQL的耗时以及执行的SQL语句,具体实现如下所示
这里用了alibaba.druid的数据库连接池技术，具体大同小异。

package net.ittimeline.mybatis.practices.core.p6spy;

import com.alibaba.druid.sql.SQLUtils;
import com.p6spy.engine.spy.appender.MessageFormattingStrategy;

/**
 * @author tony ittimeline@163.com
 * @date 2018-01-30-下午10:45
 * @website wwww.ittimeline.net
 * @see
 * @since JDK8u162
 */
public class CustomizeLineFormat implements MessageFormattingStrategy {

    public String buildMessage(String now, long elapsed, String sql) {
        StringBuffer content = new StringBuffer();
        if (org.apache.commons.lang3.StringUtils.isNotEmpty(now) && org.apache.commons.lang3.StringUtils.isNotEmpty(Long.valueOf(elapsed).toString())
                && org.apache.commons.lang3.StringUtils.isNotEmpty(sql)) {
            content.append("当前时间:" + now);
            content.append(" SQL执行耗时(毫秒)为" + elapsed);
            content.append(" SQL执行的语句是\n" + SQLUtils.formatMySql(sql)+"\n\n");
        }
        return content.toString();
    }

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        return buildMessage(now, elapsed, sql);
    }

}

然后增加一个database.properties文件，配置内容如下

和传统的jdbc配置相比，不同之处在于驱动类和连接地址的配置。

jdbc.driver=com.p6spy.engine.spy.P6SpyDriver
jdbc.url=jdbc:p6spy:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false
jdbc.username=koronto
jdbc.password=xxxxxxxx

而mybatis-config.xml文件中只需要增加一行配置，然后就可以采用${属性名}的方式获取数据库配置了，配置如下所示

<properties resource="database.properties"/>

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"></transactionManager>
        <dataSource type="POOLED">
            <property name="driver" value="${jdbc.driver}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
</environments>

然后运行CountryMapperTest.selectAll()方法，会发现在mybatis-practices-core模块的根路径下存在一个mybatis-practices-core.log的文件，内容如下

当前时间:2018-01-30 23:03:07 SQL执行耗时(毫秒)为0 SQL执行的语句是

SELECT
 country_id 
AS
 countryId, country_name 
AS
 countryName, country_code 
AS
 countryCode

FROM
 t_country

把具体的SQL语句信息给打印出来了。

在 P6Spy 发布包中，它包含 P6Log 和 P6Outage 两个模块：

  • P6Log
    P6Log 是用来拦截和记录任务应用程序的 JDBC 语句的。这个功能对于开发者监控 EJB 服务器上的 SQL 语句执行情况尤其有用，可以让开发者完成尽可能高效的代码。同时 P6Spy 的部署是极其简单的，而且根本不需要更改任何一行代码，即对现有的应用是无侵入性的。

  • P6Outage
    P6Outage 专门用来检测和记录执行时间比较长的 SQL 语句，P6Outage 只记录超过配置条件里时间的那些信息，并对可能影响到数据库的运行效率减小到最低。

架构原理

简单地讲，我们可以认为 P6Spy 就是一个代理（Proxy），它只做了一层对 JDBC 驱动的拦截，然后转发出去，这样的设计与实际的应用程序没有任何的耦合性，除了在配置中将驱动程序改成 P6Spy 的拦截驱动外，程序其他地方并不需要做任何的改变。这层拦截器除了可能会给系统带来略微的性能下降外，对程序其他方面没有任何的影响。而相对于这一点点的性能下降，在开发环境中对于开发人员来说是无法感觉到，相比它所带来的好处，完全可以忽略不计。

P6Spy 对数据库进行拦截监控的处理过程如下：

logo

问题与解决

如果在你的应用程序启动后，却在 spy.log 文件中发现了如下的提示信息，那就是驱动程序加载先后的问题了。

<你的程序的数据库驱动名称>
 is a real driver in spy.properties, but it has 

been
 loaded before p6spy.  p6spy will not wrap these connections.  Either 

prevent
 the driver from loading, or try setting'deregisterdrivers' to true in 

spy.properties

解决办法：

请把 spy.properties 配置文件里的 deregisterdrivers=false 改为 deregisterdrivers=true，重新运行即可。
这是因为有些应用系统中会先于 P6Spy 加载了真正的数据库的驱动程序，导致 P6Spy 无法监控到，设置 deregisterdrivers 为 true，是显式地把真正的数据库的驱动程序进行反注册掉，而采用 P6Spy 的驱动程序。

配置参数及相关意义

下表列出了 spy.properties 配置文件中的各配置项的名称、默认值及其意义和相关注意事项：

配置项名称                                                 默认值
module.log                                                 com.p6spy.engine.logging. P6LogFactory
module.outage                                             com.p6spy.engine.outage. P6OutageFactory
realdriver     
realdriver2     
realdriver3     
deregisterdrivers                                         false
executionthreshold     
outagedetection    false
outagedetectioninterval     
filter                                                     false
include     
exclude     
sqlexpression     
autoflush                                                 true
dateformat     
includecategories     
excludecategories     
stringmatcher     
stacktrace                                                 false
stacktraceclass     
reloadproperties                                         false
reloadpropertiesinterval                                 60
useprefix                                                 false
appender                                                 com.p6spy.engine.logging. appender.FileLogger
logfile                                                     spy.log
append    true
log4j.appender.STDOUT                                     org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout                            org.apache.log4j.PatternLayout
log4j.appender.STDOUT. layout.ConversionPattern            p6spy - %m%n
log4j.logger.p6spy                                        INFO,STDOUT
realdatasource     
realdatasourceclass     
realdatasourceproperties     
jndicontextfactory     
jndicontextproviderurl     
jndicontextcustom

SQL Profiler

SQL Profiler 是一个由 Jahia.org 提供的基于 P6Spy 引擎的快速剖析工具，用来统计 SQL 查询语句以便了解哪里是性能瓶颈，在哪里创建索引或者采取相应的办法才能提高效率，并且能根据 SQL 查询语句的情况帮你生成合适的索引脚本。

这个小工具可以实时地显示数据库查询的情况，通过集成的 SQL 解析器，在访问大多数表与列上面建立统计分析，并生成索引脚本。当然，其它的信息也会进行收集和显示，比如：单个数据库请求的时间、一类请求的时间以及所有请求的时间。因此，可以有效地通过视图的排序来检测数据的性能问题所在。这个工具对于大量的需要进行分析的请求是非常有用的，而不是人工一个个地去做分析。当你需要知道比如对相同的表和列进行访问但是采用不同的查询值时，这种分组的查询可以用建立在 ANTLR 上的 SQL 解析器进行分析。

使用步骤

首先，你的应用系统同样也应当是基于数据库的，然后你需要去获取 SQL Profiler 相关的文件（在 参考资源 中可以找到下载链接，您可以直接下载软件包）。下面介绍 SQL Profiler 的安装与使用的详细操作过程：
下载 SQL Profiler 的文件包进行安装；
把 p6spy.jar 及 sqlprofiler.jar 放到 CLASSPATH 中，如果是 Web 应用程序则放在 YourWebApp/WEB-INF/lib/ 目录下；
把 spy.properties 放到 CLASSPATH 目录下，如果是 Web 应用程序就放在 YourWebApp/WEB-INF/classess/ 目录下，注意不是 lib/ 目录；
修改你应用系统中的数据库驱动名称为 P6Spy 的驱动程序名称 com.p6spy.engine.spy.P6SpyDriver 其它的全部使用默认值，暂时不用修改；
打开 spy.properties 文件，把 realdriver 的值改为你的程序的数据库驱动名称；
注意要先运行 java -jar sqlprofiler.jar 来启动 SQL Profiler，并成功看到启动界面；
然后再启动你的应用程序或服务器，并开始进行正常的系统请求处理操作；
这样就可以在 SQL Profiler 图形化的界面上看到结果并进行分析了。

参考资料：http://blog.csdn.net/heyeqingquan/article/details/71743814

分析结果

经过一段时间的系统运行后，点击 Pause 按钮停止拦截，可以得到分析结果如下图：

SQL Profiler 的分析结果 Profiler 视图

logo

接着，可以切换到 Loggers 视图，这是 Lgger 视图的信息：

SQL Profiler 的分析结果 Logger 视图

logo

当然，也可以切换到 Analysis 视图，这是 Analysis 视图的分析结果信息：

SQL Profiler 的分析结果 Analysis 视图

logo

在经过分析后，我们可以直接通过 SQLProfiler 提交的保存按钮，直接导出应当进行数据库优化的建议的索引脚本，通过查看索引脚本，我们可以看到创建索引的详细 SQL 脚本，这样，我们就可以非常方便地进行数据库调优了。

问题与解决

最后一个需要注意的问题就是需要先启动 SQLProfiler，然后再启动应用程序或者 Tomcat 等应用服务器。这是因为 SQLProfiler 默认使用的是 Log4j 的 SocketAppender，所以要先启动。否则，会因你的应用程序或应用服务器中的 Web 应用之类的因连接不到 Socket 的服务器（SQLProfiler 相当于 Socket 的服务器）而发生错误，可以通过 SQL Profiler 控制界面最下面的连接状态就可以知道是否有程序连接上来。

logo

IronTrack SQL

IronEye，一个专注于 JDBC 性能的监控和测试的开源项目，它包含有三个工具：IronEye SQL，IronEye Cache，IronTrack SQL。其中，IronEye SQL 用于监测 Java 应用和数据库服务器之间查询开销的时间，诊断在性能方面是否存在着相关问题，让开发人员在测试之前就能发现问题。IronEye 于 2003 年 10 月 1 日开始基于 Apache Software License 发布。
IronEye SQL 这个轻量级的 Java 工具提供所有流动在数据库与应用程序之间的 SQL 统计信息并用多张图表展现，可以快速优化程序的性能。
IronGrid 相对于 Continuous Integration 提出了 Continuous Performance 的概念，即在项目开发过程中随时关注性能问题，而不是传统的出了问题再解决的方案。
IronGrid 在应用程序对数据库的操作上的 Continuous Performance 是通过 IronTrack SQL 进行体现的。IronTrack SQL 能通过对 JDBC 的包装来拦截应用程序对数据库的请求，完成性能监控。IronTrack SQL 的好处在于不需要修改任何代码或者在数据库端安装任何程序，只需要在测试时把依赖的 JDBC 替换就可以了。

使用步骤

首先，你的应用系统同样也应当是基于数据库的，然后你需要去获取 IronTrack SQL 相关的文件（在 参考资源 中可以找到下载链接，您可以直接下载软件包）。下面介绍 IronTrack SQL 的安装与使用的详细操作过程：
下载 IronTrack SQL 的文件包进行安装；
把 irontracksql.jar, p6spy.jar 和 log4j-1.2.8.jar 放到 CLASSPATH 中，如果是 Web 应用程序则放在 YourWebApp/WEB-INF/lib/ 目录下；
把 spy.properties 放到 CLASSPATH 目录下，如果是 Web 应用程序就放在 YourWebApp/WEB-INF/classess/ 目录下，注意不是 lib/ 目录；
修改你程序的数据库驱动名称为 P6Spy 的驱动程序名称 com.p6spy.engine.spy.P6SpyDriver 其它的都不用更改；
打开配置文件 spy.properties 文件，找到 realdriver，把它的值改为你的应用系统的真正的数据库驱动名称；
设置监听端口号 monitorport=2000；
先运行 java -jar irontracksql.jar 来启动 IronTrack SQL；
再启动你的应用程序或服务器；
可以在 IronTrack SQL 图形化的界面上看到结果并进行分析了。

连接设置
点击“Config”按钮就可以设置主机名、端口与刷新的时间（毫秒为单位）。根据你的服务器与端口的不同而进行相应地改变，下面以本地和 2000 端口，刷新时间为 500 毫秒为示例。设置完成后，确定，点击“Connect”就可以连接应用系统并进行监测与分析了，当要停止分析时，只要点击“Disconnect”按扭即可立刻停止分析了。
在分析的过程中，我们可以根据需要点击“Purge”按钮，它可以清除目前所监测到的内容，然后重新进行记录监测信息，很方便地进行重新开始。

IronTrack SQL 连接示例

logo

分析结果

经过一段时间的系统运行后，我们可以直接得到分析的结果与相应的图形分析示例。相关的信息显示如下：

IronTrack SQL 分析结果

logo

Count 列显示 SQL 语句的调用次数；
Avg Time 列显示 SQL 语句的执行平均时间；
Max Time 列显示 SQL 语句花费的最高时间；
SQL 列显示真正执行的 SQL 语句内容。
同时也可以通过设置过滤条件来显示指定条件的结果，比如：只关注平均调用次数大于 100 次 的结果。点击“Filtering”左边的小三角图标，可以显示如下的过滤条件设置栏目：

IronTrack SQL 设置相关的过滤条件

logo

设置完成后，点击“Apply Filter”按钮即可以获取所需要的相关结果了。这样可以更加方便地集中精力进行所需要的内容分析，可以更加方便快速地定位到问题的所在之处，然后进行解决。

总结

通过使用 P6Spy、SQL Profiler、IronTrack SQL 工具，我们可以无侵入已有的应用系统而有效地进行数据库操作的监控与剖析，为发现系统的性能瓶颈，寻找系统的性能调优提供了相当便利的方法。

p6spy专区：http://www.p6spy.com/
sqlprofiler专区：https://sourceforge.net/projects/sqlprofiler/
https://www.ibm.com/developerworks/cn/java/

坚持原创技术分享，您的支持将鼓励我继续创作！
+