SpringBoot使用druid的monitor工具查看sql执行性能(20)

简介

Druid是一个非常好用的数据库连接池，但是他的好并不止体现在作为一个连接池加快数据访问性能上和连接管理上，他带有一个强大的监控工具：Druid Monitor。不仅可以监控数据源和慢查询，还可以监控Web应用、URI监控、Session监控、Spring监控。

pom.xml依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        
         <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

修改配置

server:
  port: 8080
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/comparison?createDatabaseIfNotExist=true&useSSL=false&serverTimezone=GMT%2b8&characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    filters: stat,wall,log4j
    # 数据连接池配置
    druid:
      #连接池最大连接数
      max-active: 20
      #获取连接的最大等待时间
      max-wait: 6000
      #连接池最小连接数
      min-idle: 10
      #连接池初始化大小
      initial-size: 10
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      max-pool-prepared-statement-per-connection-size: 20
      pool-prepared-statements: true
      test-on-borrow: false
      test-on-return: false
      test-while-idle: true
      validation-query:  SELECT 1
      min-evictable-idle-time-millis: 300000
      time-between-eviction-runs-millis: 60000
      #监控信息   localhost:${server.port}/druid/login.html
      stat-view-servlet:
        login-username: admin
        login-password: admin
        #允许哪个机器登录
        allow: 127.0.0.1
        #sql监控开关
        enabled: true
      #url监控
      web-stat-filter:
        enabled: true
        #session监控
        session-stat-enable: true
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1

spring.datasource.druid配置监控的属性如下

• login-username,login-password:设置登录的帐号密码
• allow:设置登录的机器
• stat-view-servlet.enabled:设置sql监控的开关
• web-stat-filter.enabled:设置url监控开关
• session-stat-enable:设置session监控的开关
• log-slow-sql:设置打印慢sql 日志

访问druid监控页面

端口号需要换成你项目的
访问:http://localhost:8012/druid/login.html
输入 配置文件配置的帐号密码登录

去除广告图片

@Configuration
@ConditionalOnWebApplication
@AutoConfigureAfter(DruidDataSourceAutoConfigure.class)
@ConditionalOnProperty(name = "spring.datasource.druid.stat-view-servlet.enabled", havingValue = "true",
        matchIfMissing = true)
public class RemoveDruidAdConfig {

    /**
     * 除去页面底部的广告
     */
    @Bean
    public FilterRegistrationBean removeDruidAdFilterRegistrationBean(DruidStatProperties properties) {
        // 获取web监控页面的参数
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        // 提取common.js的配置路径
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\*", "js/common.js");

        final String filePath = "support/http/resources/js/common.js";

        //创建filter进行过滤
        Filter filter = new Filter() {
            @Override
            public void init(FilterConfig filterConfig) throws ServletException {
            }

            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws
                    IOException, ServletException {
                chain.doFilter(request, response);
                // 重置缓冲区，响应头不会被重置
                response.resetBuffer();
                // 获取common.js
                String text = Utils.readFromResource(filePath);
                // 正则替换banner, 除去底部的广告信息
                text = text.replaceAll("<a.*?banner"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }

            @Override
            public void destroy() {
            }
        };
        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }

}