Druid简介

Druid是一个拥有大数据实时查询和分析的高容错、高性能开源分布式系统，旨在快速处理大规模的数据，并能够实现快速查询和分析。尤其是当发生代码部署、机器故障以及其他产品系统遇到宕机等情况时，Druid仍然能够保持100%正常运行。创建Druid的最初意图主要是为了解决查询延时问题，当时试图使用hadoop来实现交互式查询分析，但是很难满足实时分析的需要。而Druid提供了以交互方式访问数据的能力，并权衡了查询的灵活性和性能二采取了特殊的存储格式。

使用场景

1、需要交互式聚合和快速探究大量数据时；

2、需要实时查询分析时；

3、具有大量数据时，如每天数亿事件的新增、每天数10T数据的增加；

4、对数据尤其是大数据进行实时分析时；

5、需要一个高可用、高容错、高性能数据库时。

/**
 * @author xsj
 * druid配置类
 */
@Configuration
public class DruidConfiguration {


        private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);

        private static final String DB_PREFIX = "spring.datasource";

        @Bean
        public ServletRegistrationBean druidServlet() {
            logger.info("init Druid Servlet Configuration ");
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
            // IP白名单
            servletRegistrationBean.addInitParameter("allow", "192.168.2.25,127.0.0.1");
            // IP黑名单(共同存在时，deny优先于allow)
            servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
            //控制台管理用户
            servletRegistrationBean.addInitParameter("loginUsername", "admin");
            servletRegistrationBean.addInitParameter("loginPassword", "admin");
            //是否能够重置数据 禁用HTML页面上的“Reset All”功能
            servletRegistrationBean.addInitParameter("resetEnable", "false");
            return servletRegistrationBean;
        }

        @Bean
        public FilterRegistrationBean filterRegistrationBean() {
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }

        //解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去
        @ConfigurationProperties(prefix = DB_PREFIX)
        class IDataSourceProperties {
            private String url;
            private String username;
            private String password;
            private String driverClassName;
            private int initialSize;
            private int minIdle;
            private int maxActive;
            private int maxWait;
            private int timeBetweenEvictionRunsMillis;
            private int minEvictableIdleTimeMillis;
            private String validationQuery;
            private boolean testWhileIdle;
            private boolean testOnBorrow;
            private boolean testOnReturn;
            private boolean poolPreparedStatements;
            private int maxPoolPreparedStatementPerConnectionSize;
            private String filters;
            private String connectionProperties;

            @Bean     //声明其为Bean实例
            @Primary  //在同样的DataSource中，首先使用被标注的DataSource
            public DataSource dataSource() {
                DruidDataSource datasource = new DruidDataSource();
                datasource.setUrl(url);
                datasource.setUsername(username);
                datasource.setPassword(password);
                datasource.setDriverClassName(driverClassName);

                //configuration
                datasource.setInitialSize(initialSize);
                datasource.setMinIdle(minIdle);
                datasource.setMaxActive(maxActive);
                datasource.setMaxWait(maxWait);
                datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
                datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
                datasource.setValidationQuery(validationQuery);
                datasource.setTestWhileIdle(testWhileIdle);
                datasource.setTestOnBorrow(testOnBorrow);
                datasource.setTestOnReturn(testOnReturn);
                datasource.setPoolPreparedStatements(poolPreparedStatements);
                datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
                try {
                    datasource.setFilters(filters);
                } catch (SQLException e) {
                    System.err.println("druid configuration initialization filter: " + e);
                }
                datasource.setConnectionProperties(connectionProperties);
                return datasource;
            }

            public String getUrl() {
                return url;
            }

            public void setUrl(String url) {
                this.url = url;
            }

            public String getUsername() {
                return username;
            }

            public void setUsername(String username) {
                this.username = username;
            }

            public String getPassword() {
                return password;
            }

            public void setPassword(String password) {
                this.password = password;
            }

            public String getDriverClassName() {
                return driverClassName;
            }

            public void setDriverClassName(String driverClassName) {
                this.driverClassName = driverClassName;
            }

            public int getInitialSize() {
                return initialSize;
            }

            public void setInitialSize(int initialSize) {
                this.initialSize = initialSize;
            }

            public int getMinIdle() {
                return minIdle;
            }

            public void setMinIdle(int minIdle) {
                this.minIdle = minIdle;
            }

            public int getMaxActive() {
                return maxActive;
            }

            public void setMaxActive(int maxActive) {
                this.maxActive = maxActive;
            }

            public int getMaxWait() {
                return maxWait;
            }

            public void setMaxWait(int maxWait) {
                this.maxWait = maxWait;
            }

            public int getTimeBetweenEvictionRunsMillis() {
                return timeBetweenEvictionRunsMillis;
            }

            public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
                this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
            }

            public int getMinEvictableIdleTimeMillis() {
                return minEvictableIdleTimeMillis;
            }

            public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
                this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
            }

            public String getValidationQuery() {
                return validationQuery;
            }

            public void setValidationQuery(String validationQuery) {
                this.validationQuery = validationQuery;
            }

            public boolean isTestWhileIdle() {
                return testWhileIdle;
            }

            public void setTestWhileIdle(boolean testWhileIdle) {
                this.testWhileIdle = testWhileIdle;
            }

            public boolean isTestOnBorrow() {
                return testOnBorrow;
            }

            public void setTestOnBorrow(boolean testOnBorrow) {
                this.testOnBorrow = testOnBorrow;
            }

            public boolean isTestOnReturn() {
                return testOnReturn;
            }

            public void setTestOnReturn(boolean testOnReturn) {
                this.testOnReturn = testOnReturn;
            }

            public boolean isPoolPreparedStatements() {
                return poolPreparedStatements;
            }

            public void setPoolPreparedStatements(boolean poolPreparedStatements) {
                this.poolPreparedStatements = poolPreparedStatements;
            }

            public int getMaxPoolPreparedStatementPerConnectionSize() {
                return maxPoolPreparedStatementPerConnectionSize;
            }

            public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
                this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
            }

            public String getFilters() {
                return filters;
            }

            public void setFilters(String filters) {
                this.filters = filters;
            }

            public String getConnectionProperties() {
                return connectionProperties;
            }

            public void setConnectionProperties(String connectionProperties) {
                this.connectionProperties = connectionProperties;
            }
        }

    }

spring:
  datasource:
      url: jdbc:mysql://47.98.131.179:3306/bingfatest?useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: root
      driverClassName: com.mysql.cj.jdbc.Driver
      platform: mysql
      type: com.alibaba.druid.pool.DruidDataSource
      # 下面为连接池的补充设置，应用到上面所有数据源中
      # 初始化大小，最小，最大
      initialSize: 1
      minIdle: 3
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 30000
      validationQuery: select 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      #useGlobalDataSourceStat: true