使用Druid优化项目

墨蓝 2024-02-19 10:11 59阅读 0赞

## 前言： ##

项目体量大了总需要优化接口，优化sql查询等，使用druid可以方便的来查找运行慢，次数多的接口sql，配置druid的步骤也相对简单

### 一、pom包添加依赖 ###

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.28</version>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.2.9</version>
            </dependency>

### 二、添加druid配置的工具类 ###

package com.cgy.template.db.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    /**
     * 数据库连接池配置
     * @author cgy
     */
    @Slf4j
    @Configuration
    public class DruidConfig {
        @Value("${spring.datasource.url}")
        private String dbUrl;
        @Value("${spring.datasource.username}")
        private String username;
        @Value("${spring.datasource.password}")
        private String password;
        @Value("${spring.datasource.druid.initial-size}")
        private int initialSize;
        @Value("${spring.datasource.druid.max-active}")
        private int maxActive;
        @Value("${spring.datasource.druid.min-idle}")
        private int minIdle;
        @Value("${spring.datasource.druid.max-wait}")
        private int maxWait;
        @Value("${spring.datasource.druid.pool-prepared-statements}")
        private boolean poolPreparedStatements;
        @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
        private int maxPoolPreparedStatementPerConnectionSize;
        @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
        private int timeBetweenEvictionRunsMillis;
        @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
        private int minEvictableIdleTimeMillis;
        @Value("${spring.datasource.druid.validation-query}")
        private String validationQuery;
        @Value("${spring.datasource.druid.test-while-idle}")
        private boolean testWhileIdle;
        @Value("${spring.datasource.druid.test-on-borrow}")
        private boolean testOnBorrow;
        @Value("${spring.datasource.druid.test-on-return}")
        private boolean testOnReturn;
        @Value("${spring.datasource.druid.filters}")
        private String filters;
        @Value("${spring.datasource.druid.connection-properties}")
        private String connectionProperties;
        @Value("${spring.datasource.druid.stat-view-servlet.enabled}")
        private boolean enabled;
        @Value("${spring.datasource.druid.stat-view-servlet.url-pattern}")
        private String urlMappings;
        @Value("${spring.datasource.druid.stat-view-servlet.login-username}")
        private String loginUsername;
        @Value("${spring.datasource.druid.stat-view-servlet.login-password}")
        private String loginPassword;
        @Value("${spring.datasource.druid.stat-view-servlet.reset-enable}")
        private String resetEnable;
        @Value("${spring.datasource.druid.stat-view-servlet.allow}")
        private String allow;
        @Value("${spring.datasource.druid.stat-view-servlet.deny}")
        private String deny;
        @Value("${spring.datasource.druid.web-stat-filter.enabled}")
        private boolean filterEnabled;
        @Value("${spring.datasource.druid.web-stat-filter.exclusions}")
        private String exclusions;
        @Value("${spring.datasource.druid.web-stat-filter.url-pattern}")
        private String filterUrlPattern;
    
    
        /**
         * Druid 连接池配置
         */
        @Bean
        public DruidDataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(dbUrl);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (Exception e) {
                log.error("druid configuration initialization filter", e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
    
        /**
         * JDBC操作配置
         */
        @Bean(name = "dataOneTemplate")
        public JdbcTemplate jdbcTemplate (@Autowired DruidDataSource dataSource) {
            return new JdbcTemplate(dataSource) ;
        }
    
        /**
         * 配置 Druid 监控界面
         */
        @Bean
        @SuppressWarnings("rawtypes")
        public ServletRegistrationBean statViewServlet() {
            ServletRegistrationBean srb = new ServletRegistrationBean<>(new StatViewServlet(), urlMappings);
            srb.setEnabled(enabled);
            // 设置控制台管理用户
            srb.addInitParameter("loginUsername", loginUsername);
            srb.addInitParameter("loginPassword", loginPassword);
            // 是否可以重置数据
            srb.addInitParameter("resetEnable", resetEnable);
            srb.addInitParameter("allow", allow);
            srb.addInitParameter("deny", deny);
            return srb;
        }
    
        @Bean
        @SuppressWarnings("rawtypes")
        public FilterRegistrationBean statFilter() {
            // 创建过滤器
            FilterRegistrationBean frb = new FilterRegistrationBean<>(new WebStatFilter());
            frb.setEnabled(filterEnabled);
            // 设置过滤器过滤路径
            frb.addUrlPatterns(filterUrlPattern);
            // 忽略过滤的形式
            frb.addInitParameter("exclusions", exclusions);
            return frb;
        }
    }

### 三、配置文件添加 ###

spring:
      datasource:
        url: jdbc:mysql://localhost:3306/xxxxxxxx
        username: xxxxx
        password: xxxxx
        // 添加配置
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          # 初始化大小，最小，最大
          initial-size: 5
          min-idle: 5
          max-active: 20
          # 配置获取连接等待超时的时间
          max-wait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
          time-between-eviction-runs-millis: 60000
          # 配置一个连接在池中最小生存时间
          min-evictable-idle-time-millis: 300000
          validation-query: SELECT VERSION()
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          # 打开 PSCache，并且指定每个连接上 PSCache 的大小
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          # 配置监控统计拦截的 Filter，去掉后监控界面 SQL 无法统计，wall 用于防火墙
          filters: stat,wall,slf4j
          # 通过 connection-properties 属性打开 mergeSql 功能；慢 SQL 记录
          connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
          # 配置 DruidStatFilter
          web-stat-filter:
            enabled: true
            url-pattern: /*
            exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
          # 配置 DruidStatViewServlet
          stat-view-servlet:
            enabled: true
            url-pattern: /druid/*
            # IP 白名单，没有配置或者为空，则允许所有访问
            allow: 127.0.0.1,localhost
            # IP 黑名单，若白名单也存在，则优先使用
            deny: 192.168.1.100
            # 禁用 HTML 中 Reset All 按钮
            reset-enable: false
            # 登录用户名/密码
            login-username: test
            login-password: test

### 四、启动查看 ###

地址栏输入ip/druid 就可以查看

![9d8f5aa0e3304237be155fcfb2f162a3.png][]

[9d8f5aa0e3304237be155fcfb2f162a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/350d6f3d29424be381a2b573144c3fe5.png