search

vieyahn2017 / javaway

java on the way
5 stars 3 forks source link

9.29 Druid数据库连接池 #28

Closed vieyahn2017 closed 6 years ago

vieyahn2017 commented 6 years ago

springboot使用druid连接池，并配置数据源监控 https://segmentfault.com/a/1190000015564484

vieyahn2017 commented 6 years ago

springboot使用druid连接池，并配置数据源监控 https://segmentfault.com/a/1190000015564484

首先肯定是引入druid的依赖:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>

然后将druid配置到项目中：

在application.yml中加入

spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource

        ###数据源的其他配置
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        ### 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

这里配置好之后，数据源是从默认的tomcat.pool切换到了druid，可是druid的属性比如initialSize、minIdle还未生效，因为在DataSourceProperties.class下没有对应的匹配项，所以还需要我们手动配置:

新建DruidConfig.java

@Configuration
public class DruidConfig {
    // 将所有前缀为spring.datasource下的配置项都加载到DataSource中
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

至此，Druid的所有配置都已完成，打断点可以看到配置已经生效： clipboard.png clipboard.png

最后，需要配置druid的监控页面

在上面的DruidConfig中加入代码：

@Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
        Map<String, String> initParams = new HashMap<>();
        //　可配的属性都在 StatViewServlet 和其父类下
        initParams.put("loginUsername", "admin-druid");
        initParams.put("loginPassword", "111111");
        servletRegistrationBean.setInitParameters(initParams);
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean druidWebStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");
        filterRegistrationBean.setInitParameters(initParams);
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        return filterRegistrationBean;
    }

打开浏览器测试输入：http://localhost:8080/druid/login.html

可以看到 clipboard.png

输入我们刚才在servlet中配置的账号密码就可以登录了

vieyahn2017 commented 6 years ago

数据库阿里连接池 druid配置详解 https://blog.csdn.net/hj7jay/article/details/51686418

vieyahn2017 commented 6 years ago

druid简单教程 https://blog.csdn.net/yunnysunny/article/details/8657095?utm_source=blogxgwz4

1 配置

2 代码编写

2.1 使用spring

2.2 不使用spring

类似于dbcp，druid也提供了原生态的支持。这里仅仅列出来了如何获取一个DataSource对象，实际使用中要将获取DataSource的过程封装到一个单体模式类中。先看下面这段代码：

///代码片段2.3 手动读取配置文件初始化连接池

package com.whyun.util.db;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.whyun.util.config.MySqlConfigProperty;
import com.whyun.util.config.MySqlConfigProperty2;
import com.whyun.util.db.source.AbstractDataSource;
import com.whyun.util.db.source.impl.DbcpSourceMysql;
import com.whyun.util.db.source.impl.DruidSourceMysql;
import com.whyun.util.db.source.impl.DruidSourceMysql2;

// TODO: Auto-generated Javadoc
/**
 * The Class DataSourceUtil.
 */
public class DataSourceUtil {

    /** 使用配置文件dbconfig.properties构建Druid数据源. */
    public static final int DRUID_MYSQL_SOURCE = 0;

    /** The duird mysql source. */
    private static DataSource duirdMysqlSource;

    /** 使用配置文件dbconfig2.properties构建Druid数据源. */
    public static final int DRUID_MYSQL_SOURCE2 = 1;

    /** The druid mysql source2. */
    private static DataSource druidMysqlSource2;

    /** 使用配置文件dbconfig.properties构建Dbcp数据源. */
    public static final int DBCP_SOURCE = 4;

    /** The dbcp source. */
    private static  DataSource dbcpSource;

    /**
     * 根据类型获取数据源.
     *
     * @param sourceType 数据源类型
     * @return druid或者dbcp数据源
     * @throws Exception the exception
     * @NotThreadSafe
     */
    public static final DataSource getDataSource(int sourceType)
        throws Exception {
        DataSource dataSource = null;
        switch(sourceType) {
        case DRUID_MYSQL_SOURCE:            

            if (duirdMysqlSource == null) {
                duirdMysqlSource = DruidDataSourceFactory.createDataSource(
                    MySqlConfigProperty.getInstance().getProperties());
            }
            dataSource = duirdMysqlSource;
            break;
        case DRUID_MYSQL_SOURCE2:
            if (druidMysqlSource2 == null) {
                druidMysqlSource2 = DruidDataSourceFactory.createDataSource(
                    MySqlConfigProperty2.getInstance().getProperties());
            }
            dataSource = druidMysqlSource2;
            break;
        case DBCP_SOURCE:
            if (dbcpSource == null) {
                dbcpSource = BasicDataSourceFactory.createDataSource(
                    MySqlConfigProperty.getInstance().getProperties());
            }
            dataSource = dbcpSource;
            break;
        }
        return dataSource;
    }

    /**
     * 根据数据库类型标示获取DataSource对象，跟{@link com.whyun.util.db.DataSourceUtil#getDataSource(int)}
     * 不同的是，这里DataSource获取的时候使用了单体模式
     *
     * @param sourceType 数据源类型
     * @return 获取到的DataSource对象
     * @throws Exception the exception
     */
    public static final DataSource getDataSource2(int sourceType) throws Exception {

        AbstractDataSource abstractDataSource = null;
        switch(sourceType) {
        case DRUID_MYSQL_SOURCE:            
            abstractDataSource = DruidSourceMysql.getInstance();
            break;
        case DRUID_MYSQL_SOURCE2:
            abstractDataSource = DruidSourceMysql2.getInstance();
            break;
        case DBCP_SOURCE:
            abstractDataSource = DbcpSourceMysql.getInstance();
            break;
        }
        return abstractDataSource == null ?
                null :
                    abstractDataSource.getDataSource();
    }
}

第37行中调用了类com.alibaba.druid.pool.DruidDataSourceFactory中createDataSource方法来初始化一个连接池。对比dbcp的使用方法，两者很相似。

下面给出一个多线程的测试程序。运行后可以比较druid和dbcp的性能差别。

///代码片段2.4 连接池多线程测试程序
package com.whyun.druid.test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

import com.whyun.druid.model.TableOperator;
import com.whyun.util.db.DataSourceUtil;

public class MutilThreadTest {
    public static void test(int dbType, int times)
        throws Exception { 
        int numOfThreads =Runtime.getRuntime().availableProcessors()*2;
        ExecutorService executor = Executors.newFixedThreadPool(numOfThreads);  
        final TableOperator test = new TableOperator();
//        int dbType = DataSourceUtil.DRUID_MYSQL_SOURCE;
//        dbType = DataSourceUtil.DBCP_SOURCE;
        test.setDataSource(DataSourceUtil.getDataSource(dbType));

        boolean createResult = false;
        try {
            test.createTable();
            createResult = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (createResult) {
            List<Future<Long>> results = new ArrayList<Future<Long>>();   
            for (int i = 0; i < times; i++) {  
                results.add(executor.submit(new Callable<Long>() {  
                    @Override  
                    public Long call() throws Exception {  
                            long begin = System.currentTimeMillis();
                                try {
                                    test.insert();
                                    //insertResult = true;
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }                           
                            long end = System.currentTimeMillis();  
                        return end - begin;  
                    }  
                }));  
            }  
            executor.shutdown();  
            while(!executor.awaitTermination(Long.MAX_VALUE, TimeUnit.DAYS));  

            long sum = 0;  
            for (Future<Long> result : results) {  
                sum += result.get();  
            }  

            System.out.println("---------------db type "+dbType+"------------------");  
            System.out.println("number of threads :" + numOfThreads + " times:" + times);  
            System.out.println("running time: " + sum + "ms");  
            System.out.println("TPS: " + (double)(100000 * 1000) / (double)(sum));  
            System.out.println();  
            try {
                test.tearDown();
                //dropResult = true;
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("初始化数据库失败");
        }

    }  

    public static void main (String argc[])
        throws Exception {
        test(DataSourceUtil.DBCP_SOURCE,50);
        test(DataSourceUtil.DRUID_MYSQL_SOURCE,50);

    }
}
vieyahn2017 commented 6 years ago

阿里Druid连接池监控的两个坑 https://blog.csdn.net/moakun/article/details/80055960?utm_source=blogxgwz0

问题1：不断打印error级别的错误日志

问题2：DruidStatView类异常

java.util.ConcurrentModificationException
    at java.util.LinkedHashMap$LinkedHashIterator.nextEntry(LinkedHashMap.java:394)
    at java.util.LinkedHashMap$ValueIterator.next(LinkedHashMap.java:409)
    at java.util.Collections$UnmodifiableCollection$1.next(Collections.java:1067)
    at com.alibaba.druid.support.http.stat.WebAppStat.getSessionStatDataList(WebAppStat.java:504)
    at com.alibaba.druid.support.http.stat.WebAppStatUtils.getSessionStatDataList(WebAppStatUtils.java:64)
    at com.alibaba.druid.support.http.stat.WebAppStatManager.getSessionStatData(WebAppStatManager.java:100)
    at com.alibaba.druid.stat.DruidStatService.getWebSessionStatDataList(DruidStatService.java:205)
    at com.alibaba.druid.stat.DruidStatService.service(DruidStatService.java:161)
    at com.alibaba.druid.support.http.StatViewServlet.process(StatViewServlet.java:162)
    at com.alibaba.druid.support.http.ResourceServlet.service(ResourceServlet.java:253)

看源码，发现又是session监控的坑

无力吐槽。。

for循环里面重复定义Map，可能在别的地方有元素变动，导致发生ConcurrentModificationException异常。

所以，最后关闭了session监控。

很好奇，阿里工程师都这种水平吗？还是为了偷懒？

vieyahn2017 commented 6 years ago

DruidParser - 源代码篇（1）

2016年05月09日 13:44:39 张哈希 阅读数：8213 标签： druid sqlparser 源代码 初始化 个人分类： SQL解析器（Druid SQLParser） 版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/zhxdick/article/details/51350854 最近用阿里的Druid的SQL parser来解析SQL语句。在此记录下研究： 调用它来解析出AST语意树一般这么写（针对MySQL）：

MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
for(SQLStatement statement:statementList){
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    statemen.accept(visitor);
}

对于每一个SQL请求（可能包含多语句），需要先新建一个MySqlStatementParser。注意，MySqlStatementParser 不是线程安全的，所以一种做法是针对每个session的请求，需要新建一个MySqlStatementParser。 那么这个初始化过程究竟是怎样的呢？涉及到哪些类？

涉及到的类如下所示： 这里写图片描述 SQL解析可以分为三层：语句解析->表达式解析->词法解析。对应的主要类分别是MySqlStatementParser，MySqlExprParser，MySqlLexer。可以说，MySqlLexer是解析出每个词的词义，表达式由词组成，MySqlExprParser用来解析出不同表达式的含义。多个表达式和词组成完整的语句，这个由MySqlStatementParser解析。

vieyahn2017 commented 6 years ago

另有

Druid原理架构（

时序数据库

，不是ali的数据库连接池 ...