AI专业者开发社区 1 号成员
  •  0 回帖  •  0 浏览  •  4 个月前
   

JAVA和C#中数据库连接池原理与应用

JAVA 和 C# 中数据库连接池原理

在现在的互联网发展中，高并发成为了主流，而最关键的部分就是对数据库操作和访问，在现在的互联网发展中，ORM 框架曾出不穷， 比如：.Net-Core 的 EFCore、SqlSugar、Dapper。JAVA 的 Spring-DataJpa（EntityManager），Mybatis，MybatisPlus 等等

但是说到 ORM 其实本质都是操作最底层的数据库访问组件：Ado.net，Jdbc

今天我就来聊一聊这两个数据库访问的连接池原理

在说到 Ado.net 和 jdbc 的数据连接池之前, 首先我们需要了解数据库连接池是什么

连接到数据库服务器通常由几个需要很长时间的步骤组成。 必须建立 物理通道（例如套接字或命名管道），必须与服务器进行初次握手， 必须分析连接字符串信息，必须由服务器对连接进行身份验证，必 须运行检查以便在当前事务中登记，等等。

实际上，大多数应用程序仅使用一个或几个不同的连接配置。 这意味着在执行应用程序期间，许多相同的连接将反复地打开和关闭。这很耗费 Cpu 的性能。为了将打开连接的成本降至最低，ADO.NET 使用称为连接池的优化技术。而 Java 则是 jdbc 连接池的优化技术。

一般来说，Java 应用程序访问数据库的过程是：

  1. 装载数据库驱动程序；
  2. 通过 jdbc 建立数据库连接；
  3. 访问数据库，执行 sql 语句；
  4. 断开数据库连接。

 

 

这是常用的 Tomcat 的数据库连接导图和 Jdbc 进行数据库连接的步骤

 

而.Net Framwork/.Net Core 应用程序访问数据库的过程是由 .NET 数据提供程序的四个核心对象：

1.Connection: 连接数据库 2.Command: 执行数据库命令 3.DataReader: 负责从数据源中读取数据 4.DataAdapter: 负责数据集和数据库的联系

这是 Ado.net 数据库连接的导图

Ado.net：

Ado.net 连接数据库的步骤：

1. 新建一个数据库连接字符串
string conStr = “Data Source=.;Initial Catalog=MySchoolDB;Integrated Security=True”;
2. 引入命名空间：
using System.Data.SqlClient;
3. 创建 SqlConnection 对象
SqlConnection conn = new SqlConnection(conStr);
4. 打开连接：
conn.Open();
5. 关闭连接：
conn.Close();
五、使用 Command 对象的步骤：
1. 创建数据库连接
SqlConnection conn = new SqlConnection(conStr);
2. 定义 sql 语句
string sql = “insert into Admin values(‘值’)”;
3. 创建 SqlCommand 对象
SqlCommand cmd = new SqlCommand(conn,sql);
4. 执行命令
cmd.ExecuteScalar();

 

我们已经知道了在连接时，如果在一瞬间的访问量突然激增的情况下，那么线程就会开辟越多的数据库访问连接，这时候基本的连接已经不足以应对高并发高 QPS 的访问了

这个时候 Mircosoft 创造了由 Data Provider 提供的一种数据库连接池 --Ado.net 连接池：它使得应用程序使用的连接保存在连接池里而避免每次都要完成建立 / 关闭连接的完整过程。

Data Provider 在收到连接请求时建立连接的完整过程是：

  1. 先连接池里建立新的连接（即“逻辑连接”），然后建立该“逻辑连接”对应的“物理连接”。建立“逻辑连接”一定伴随着建立“物理连接”。
  2. Data Provider 关闭一个连接的完整过程是先关闭“逻辑连接”对应的“物理连接”然后销毁“逻辑连接”。
  3. 销毁“逻辑连接”一定伴随着关闭“物理连接”，SqlConnection.Open() 是向 Data Provider 请求一个连接 Data Provider 不一定需要完成建立连接的完整过程，可能只需要从连接池里取出一个可用的连接就可以；
  4. SqlConnection.Close() 是请求关闭一个连接，Data Provider 不一定需要完成关闭连接的完整过程，可能只需要把连接释放回连接池就可以。

现在我写一段测试代码测试不使用连接池的数据库连接效果： 同时我用 windows 的性能计数器侦测了 Cpu 的消耗

class Program
{
    static void Main(string[] args)
    {
        SqlConnection con = new SqlConnection("server=.\\sqlexpress;database=zsw;pooling=true;trusted_connection=true;uid=sa;pwd=zsw158991626ZSW;");
        for (int i = 0; i < 10; i++)
        {
            try
            {con.Open();
                Console.WriteLine("开始连接数据库" + System.Threading.Thread.CurrentThread.Name);
                System.Threading.Thread.Sleep(1000);}
            catch (Exception e) {Console.WriteLine(e.Message); }
            finally
            {con.Close();
                System.Threading.Thread.Sleep(1000);}
        }
        Console.Read();}
}

 

这个时候我的代码是开启了数据库池连接，而我的连接数只有 1，但是当我们去掉 Console.Readkey 的时候设置 pooling=false 的时候此时我的数据连接占用了 10 个，由于我的电脑 sqlserver 性能检测打不开，但是大家可以去网上百度后试试查看连接数

但是！ .Net Core 连接了数据库好像是默认打开数据连接池，这个我找了半天的文档也没有结果。

那么这个 pooling 是什么呢？

每当程序需要读写数据库的时候。Connection.Open()会使用 ConnectionString 连接到数据库，数据库会为程序建立 一个连接，并且保持打开状态，此后程序就可以使用 T-SQL 语句来查询 / 更新数据库。当执行到 Connection.Close() 后，数据库就会关闭当 前的连接。很好，一切看上去都是如此有条不紊。

但是如果我的程序需要不定时的打开和关闭连接，（比如说 ASP.Net 或是 Web Service ），例如当 Http Request 发送到服务器的时候、，我们需要打开 Connection 然后使用 Select* from Table 返回一个 DataTable/DataSet 给客户端 / 浏览器，然后关闭当前的 Connection。那每次都 Open/Close Connection 如此的频繁操作对于整个系统无疑就成了一种浪费。

ADO.Net Team 就给出了一个比较好地解决方法。将先前的 Connection 保存起来，当下一次需要打开连接的时候就将先前的 Connection 交给下一个连接。这就是 Connection Pool。

那么这个 pooling 是如何工作的呢？

首先当一个程序执行 Connection.open()时候，ADO.net 就需要判断，此连接是否支持 Connection Pool (Pooling 默认为 True)，如果指定为 False, ADO.net 就与数据库之间创建一个连接（为了避免混淆，所有数据库中的连接，都使用”连接”描述），然后返回给程序。 如果指定为 True，ADO.net 就会根据 ConnectString 创建一个 Connection Pool，然后向 Connection Pool 中填充 Connection（所有.net 程序中的连接，都使用”Connection”描述）。填充多少个 Connection 由 Min Pool Size (默认为 0) 属性来决定。例如如果指定为 5，则 ADO.net 会一次与 SQL 数据库之间打开 5 个连接，然后将 4 个 Connection，保存在 Connection Pool 中，1 个 Connection 返回给程序。

当程序执行到 Connection.close()的时候。如果 Pooling 为 True，ADO.net 就把当前的 Connection 放到 Connection Pool 并且保持与数据库之间的连接。 同时还会判断 Connection Lifetime( 默认为 0) 属性，0 代表无限大，如果 Connection 存在的时间超过了 Connection LifeTime，ADO.net 就会关闭的 Connection 同时断开与数据库的连接，而不是重新保存到 Connection Pool 中。

（这个设置主要用于群集的 SQL 数据库中，达到负载平衡的目的）。如果 Pooling 指定为 False，则直接断开与数据库之间的连接。

然后当下一次 Connection.Open() 执行的时候，ADO.Net 就会判断新的 ConnectionString 与之前保存在 Connection Pool 中的 Connection 的 connectionString 是否一致。 （ADO.Net 会将 ConnectionString 转成二进制流，所 以也就是说，新的 ConnectionString 与保存在 Connection Pool 中的 Connection 的 ConnectionString 必须完全一致，即使多加了一个空格，或是修改了 Connection String 中某些属性的次序都会让 ADO.Net 认为这是一个新的连接，而从新创建一个新的连接。所以如果您使用的 UserID,Password 的认 证方式，修改了 Password 也会导致一个 Connection，如果使用的是 SQL 的集成认证，就需要保存两个连接使用的是同一个）。

然后 ADO.net 需要判断当前的 Connection Pool 中是否有可以使用的 Connection(没有被其他程序所占用)，如果没有的话，ADO.net 就需要判断 ConnectionString 设 置的 Max Pool Size （默认为 100），如果 Connection Pool 中的所有 Connection 没有达到 Max Pool Size，ADO.net 则会再次连接数据库，创建一个连接，然后将 Connection 返回给程序。

如果已经达到了 MaxPoolSize，ADO.net 就不会再次创建任何新的连接，而是等待 Connection Pool 中被其他程序所占用的 Connection 释放，这个等待时间受 SqlConnection.ConnectionTimeout（默认是 15 秒）限制，也就是说如果时间超过了 15 秒，SqlConnection 就会抛出超时错误（所以有时候如果 SqlConnection.open() 方法抛 出超时错误，一个可能的原因就是没有及时将之前的 Connnection 关闭，同时 Connection Pool 数量达到了 MaxPoolSize。）

如果有可用的 Connection，从 Connection Pool 取出的 Connection 也不是直接就返回给程序，ADO.net 还需要检查 ConnectionString 的 ConnectionReset 属性 (默认为 True) 是否需要对 Connection 最一次 reset。这是由于，之前从程序中返回的 Connection 可能已经被修改过，比如说使用 SqlConnection.ChangeDatabase method 修改当前的连接，此时返回的 Connection 可能就已经不是连接当前的 Connection String 指定的 Initial Catalog 数据库了。所以需要 reset 一次当前的连接。但是由于所有的额外检查都会增大 ADO.net Connection Pool 对系统的开销。

连接池是为每个唯一的连接字符串创建的。 当创建一个池后，将创建多个连接对象并将其添加到该池中，以满足最小池大小的需求。 连接根据需要添加到池中，但是不能超过指定的最大池大小（默认值为 100）。 连接在关闭或断开时释放回池中。

总结

在请求 SqlConnection 对象时，如果存在可用的连接，将从池中获取该对象。 连接要可用，必须未使用，具有匹配的事务上下文或未与任何事务上下文关联，并且具有与服务器的有效链接。

连接池进程通过在连接释放回池中时重新分配连接，来满足这些连接请求。 如果已达到最大池大小且不存在可用的连接，则该请求将会排队。 然后，池进程尝试重新建立任何连接，直至到达超时时间（默认值为 15 秒）。 如果池进程在连接超时之前无法满足请求，将引发异常。

用好连接池将会大大提高应用程序的性能。相反，如果使用不当的话，则百害而无一益。一般来说，应当遵循以下原则：

  1. 在最晚的时刻申请连接，在最早的时候释放连接。
  2. 关闭连接时先关闭相关用户定义的事务。
  3. 确保并维持连接池中至少有一个打开的连接。
  4. 尽力避免池碎片的产生。主要包括集成安全性产生的池碎片以及使用许多数据库产生的池碎片。

JDBC：

JDBC 默认的数据库连接池

JDBC 的 API 中没有提供连接池的方法。一些大型的 WEB 应用服务器如 BEA 的 WebLogic 和 IBM 的 WebSphere 等提供了连接池的机制，但是必须有其第三方的专用类方法支持连接池的用法。

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器 (Weblogic, WebSphere, Tomcat) 提供实现，也有一些开源组织提供实现：

　　①DBCP 数据库连接池

　　②C3P0 数据库连接池

　　DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池

　　数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。

　　当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

JDBC 的数据库连接池的工作机制：

数据库连接池负责分配、管理和释放数据库连接的。数据库连接池在初始化时, 会创建一定数量的连接放入连接池中, 这些数据库连接的数量是由最小数据库连接数量来设定的。无论这些数据库连接有没有被使用，连接池一直都将保持有至少有这么多数量的连接。连接池的最大数据库连接数量限制了这个连接池占有的最大连接数，当应用程序向连接池请求的连接数大于这个限制时，这些请求将会被加入到等待队列中。 数据库的最小连接数和最大连接数的设置要考虑一下几个因素：

1) 最小连接数是数据库连接池会一直保持的数据库连接, 如果当应用程序对数据库连接的使用不是特别大时, 将会有大量的数据库连接资源被浪费;

2) 最大连接数是指数据库能申请的最大连接数, 如果数据库连接请求超过这个数时, 后面的数据库连接请求就会被加入到等待队列, 这样会影响后面的数据库操作;

3) 如果最小连接数和最大连接数相差太大的话, 那么最先的连接请求会获利, 之后超过最小连接数量的连接就等价于重新创建了一个新的数据库连接. 不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放。

现在我们试试用 DBCP 的方式连接数据库

1、首先建立一个 maven 项目，然后在 resources 文件下新建一个 db.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql?&useSSL=false&serverTimezone=UTC
jdbc.username=root // 用户名
jdbc.password=123456 // 密码
initSize=10 // 初始化连接数
maxTotal=200 // 最大连接数
maxIdle=60 // 最大空闲数，数据库连接的最大空闲时间。超过空闲时间，数据库连接将被标记为不可用，然后被释放。设为 0 表示无限制。

 

2、接着导入 maven 的包依赖

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.19</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.7.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-pool2</artifactId>
        <version>2.7.0</version>
    </dependency>
 
    <dependency>
        <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
        <version>1.2</version>
    </dependency>
</dependencies>

 

直接复制粘贴即可，但是请注意你的 jdk 默认版本必须再 8 以上！

3、再新建一个 JdbcUtil 类

package com.jdbc.util;    
import org.apache.commons.dbcp2.BasicDataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

/**


    

  • DBCP 的方式链接数据库
    /
    public class JdbcUtil {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static int initSize;
    private static int maxTotal;
    private static  int maxIdle;
    private static BasicDataSource ds;
    static {
    ds     = new BasicDataSource();
    Properties cfg    =new Properties();
    try { //读取 db.properties 文件
    InputStream in = JdbcUtil.class
    .getClassLoader()
    .getResourceAsStream(    "db.properties");
    cfg.load(in);
    //初始化参数
    driver=cfg.getProperty("jdbc.driver");
    url    =cfg.getProperty("jdbc.url");
    username    =cfg.getProperty("jdbc.username");
    password    =cfg.getProperty("jdbc.password");
    initSize    =Integer.parseInt(cfg.getProperty("initSize"));
    maxTotal    =Integer.parseInt(cfg.getProperty("maxTotal"));
    maxIdle    =Integer.parseInt(cfg.getProperty("maxIdle"));
    in.close();
    //初始化连接池
                ds.setDriverClassName(driver);
    ds.setUrl(url);
    ds.setUsername(username);
    ds.setPassword(password);
    ds.setInitialSize(initSize);
    ds.setMaxTotal(maxTotal);
    ds.setMaxIdle(maxIdle);
    }     catch (Exception e) {
    e.printStackTrace();
    throw new RuntimeException(e);
    }
    }
    public static Connection getConnection() {//连接数据库封装类
    try {
    /
    * getConnection() 从连接池中获取的重用
    * 连接，如果连接池满了，则等待。
    * 如果有归还的连接线，则获取重用的连接
    */
    Connection conn     = ds.getConnection();
    return conn;
    }     catch (Exception e) {
    e.printStackTrace();
    throw new RuntimeException(e);
    }
    }
    public static void close(Connection conn) {//关闭数据库的连接方法，封装复杂的关闭过程；
    if(conn!=null) {
    try {
    //将用过的连接归还到连接池
                    conn.close();
    }     catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    }

 

4、我们编写一个测试类进行验证

package com.jdbc.service;
import com.jdbc.util.JdbcUtil;

import java.sql.Connection;
import java.sql.SQLException;


public class JdbcTest {
public static void main(String[] args) {
try {
for (int i=0;i<1000;i++){
Thread a= new Thread(new TestThread(),"线程:"+(i+1));
a.start();
System.out.println(a.getName()+"已启动");
}
}
catch (Exception ex){
ex.printStackTrace();
}
}
private static class TestThread implements Runnable{


    </span><span style="color: rgba(0, 0, 255, 1)">private</span> Connection con=<span style="color: rgba(0, 0, 0, 1)"> JdbcUtil.getConnection();
    @Override
    </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> run() {
        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (con.isClosed()){
                System.out.println(</span>"连接已经关闭"<span style="color: rgba(0, 0, 0, 1)">);
            }
        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (SQLException e) {
            e.printStackTrace();
        }
        </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">JdbcUtil.close(con);
            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">System.out.println("\t"+Thread.currentThread().getName()+"已关闭");</span>



            }
}
}
}

 

现在运行测试，发现输出

端口占据了 200 个，线程池开启了工作，只不过我没有释放连接端口，但是我修改一下 db.properties 的最大连接数为 300，现在我们来看看效果

可以看到我们的数据库连接已经报错了，这是为什么呢？因为我本地的 MySQL 连接只有 200 端口，当超过 200 个端口连接时就会崩溃。这也是常见的数据库连接性能瓶颈

现在我们关闭连接的代码取消注释，可以看到即使有 1000 个连接也会快速执行，而且不会占用多余的端口

DBCP 的方式也是服务器 Tomcat 的所使用的方式，所以在 tomcat 使用数据连接池还是很有必要的，至少能扛得住一般的并发！该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。

总结

JAVA 的 JDBC 和微软 Ado.net 其实本质上的差别并不大，因为都是对于数据库的操作，其根本数据库的性能最大瓶颈真的就是链接问题吗？

那么数据库的索引，现在的数据库分库分表，读写分离技术的存在是因为什么呢？所以，数据库连接池也是性能优化之一的，未来还有更多的数据库优化操作等待着人们去探索

比如现在的阿里巴巴的 Druid，就是最求精益求精的结果，微软的 ORM 也纷纷早都开启了数据库连接池的优化，这标志着未来的互联网性能瓶颈已经不在局势与传统的关系型数据库了

未来 Nosql 的流行介入让高并发更能承担起互联网大项目的重任！

其实对于 Ado.net 和 jdbc 我并没有花时间去进行性能比较，我喜欢 C# 也喜欢 Java，优秀的语言本就是互相借鉴，就和我们写代码、学算法一样，如果你开始就懂得了如何写出优秀的代码我相信，你也不会在乎语言的性能优势了。

本文引用：

https://blog.csdn.net/huwei2003/article/details/71459198

https://blog.csdn.net/hliq5399/article/details/73292023

https://blog.csdn.net/weixin_40751299/article/details/81609332

https://www.cnblogs.com/justdoitba/p/8087984.html

https://www.cnblogs.com/albertrui/p/8421791.html

https://blog.csdn.net/L_it123/article/details/88205528

感谢以上的大佬们的文章，让我得以节约时间写出这篇文章。