关于连接池之sqlserver篇

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。

• 参考[1]：配置 user connections 服务器配置选项
• 参考[2]：SQL Server 连接池 (ADO.NET)
• 参考[3]：SQL Server 2017 connection strings
• 参考[4]：Sql Server 连接池及其用法
• 参考[5]：数据库阿里连接池 druid配置详解
• 参考[6]：数据库连接池原理及常用连接池介绍

---

[TOC]

服务端配置

配置 user connections

user connections 选项指定 SQL Server实例上允许同时建立的最大用户连接数。默认值为 0，表示允许的最多用户连接数为 32767 。

实际允许的用户连接数还取决于 SQL Server 版本以及应用程序和硬件的限制。

使用 user connections 选项有助于避免由于过多并发连接而使服务器超载。

重要：如果必须使用此选项，请不要将值设置得太高，这是因为不管是否使用连接，每个连接都会产生开销。 如果超过了用户连接的最大允许值，将收到一条错误消息，而且直到出现一个可用连接之后才能建立连接。

--.设置方式1
(右键实例) >> 属性 >> 连接 >> 最大并发连接数

--.设置方式2
USE master;
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'user connections', 2500
RECONFIGURE WITH OVERRIDE 

--.确认
SELECT name,value_in_use,description FROM sys.configurations WITH(NOLOCK) WHERE name = 'user connections'

必须重启 SQL 实例，设置才会生效。

查看当前实例总的连接数

SELECT COUNT(1) FROM sys.dm_exec_connections WITH(NOLOCK) WHERE session_id>50

连接字符串之最佳实践-for开发

Microsoft Connection Pooling

ADO.NET

C

sqlConnection对象，推荐示例：

// 疑似 .NET Framework 程序连接池
sqlConnection conn = new sqlConnection();
conn.ConnectionString = "pooling=true;connection lifetime=5;min pool size=2;max pool size=4;data source=127.0.0.1,1433;initial catalog=db01;user id=test;password=***";
conn.Open();

// 用完及时关闭
conn.Close();

注意里面和连接池有关的参数：

参数名	参数默认值	推荐值	是否重启	参数描述
pooling	true	true		是否启用连接池（默认为开启)
min pool size		2		最小连接池大小：即什么也没执行初次连接的时候先和数据库服务建立n个连接
max pool size		4		最大连接池大小：允许建立的最大连接数，是在需要的时候建立
connection lifetime	0	5
Connect Timeout	15			在终止尝试并产生错误之前，等待与服务器的连接的时间长度（以秒为单位）
Packet Size	8192	0		用来与 SQL Server 的实例进行通信的网络数据包的大小（以字节为单位）

.NET libraries

Microsoft SqlClient Data Provider for SQL Server

Standard Security 示例：

Server=127.0.0.1,2433;Database=db01;User Id=test;Password=***;

.NET Framework Data Provider for SQL Server

Standard Security 示例：

Server=127.0.0.1,2433;Database=db01;User Id=test;Password=***;

OLE DB providers

Microsoft OLE DB Driver for SQL Server

SQLXML 4.0 OLEDB Provider

ODBC drivers

Microsoft ODBC Driver 17 for SQL Server

Microsoft ODBC Driver 13 for SQL Server

Wrappers and others

.NET Framework Data Provider for ODBC

.NET Framework Data Provider for OLE DB

Use an OLE DB provider from .NET 示例：

Provider=any oledb provider's name;OledbKey1=someValue;OledbKey2=someValue;

JAVA

Tomcat JDBC

Microsoft JDBC Provider for SQL Server

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

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

Public void FindAllUsers(){
        //1、装载sqlserver驱动对象
        DriverManager.registerDriver(new SQLServerDriver());             
        //2、通过JDBC建立数据库连接
        Connection con =DriverManager.getConnection("jdbc:sqlserver://192.168.2.6:1433;DatabaseName=customer", "test", "***");            
        //3、创建状态
        Statement state =con.createStatement();           
        //4、查询数据库并返回结果
        ResultSet result =state.executeQuery("select * from users");           
        //5、输出查询结果
        while(result.next()){
            System.out.println(result.getString("email"));
        }            
        //6、断开数据库连接
        result.close();
        state.close();
        con.close();
    }

推荐示例：

dataSource.main.driver-class-name = com.microsoft.sqlserver.jdbc.SQLServerDriver
dataSource.main.initialSize = 5
dataSource.main.minIdle = 5
dataSource.main.maxActive = 20
dataSource.main.maxWait = 60000
dataSource.main.minEvictableIdleTimeMillis = 300000
dataSource.main.timeBetweenEvictionRunsMillis = 60000
dataSource.main.validationQuery = SELECT 1
dataSource.main.testWhileIdle = true
dataSource.main.testOnBorrow = false
dataSource.main.testOnReturn = false
dataSource.main.poolPreparedStatements = true
dataSource.main.maxPoolPreparedStatementPerConnectionSize = 20
dataSource.main.filters = stat
druid.stat.slowSqlMillis=5000
dataSource.main.queryTimeout = 10
dataSource.main.transactionQueryTimeout = 12

PHP

Python

第三方数据库连接池jar包

区分一个数据库连接池是属于第一代产品还是代二代产品，有一个最重要的特征就是看它在架构和设计时采用的线程模型，因为这直接影响的是并发环境下存取数据库连接的性能。

一般来讲采用单线程同步的架构设计都属于第一代连接池，二采用多线程异步架构的则属于第二代。比较有代表性的就是Apache Commons DBCP，在1.x版本中，一直延续着单线程设计模式，到2.x才采用多线程模型。

用版本发布时间来辨别区分两代产品，则是一个偷懒的好方法。以下是这些常见数据库连接池最新版本的发布时间：

数据库连接池	最新版本	发布时间
C3P0	c3p0-0.9.5.2	2015-12-09
DBCP	2.2.0	2017-12-27
Druid	0.11.0	2017-12-04
HikariCP	2.7.6	2018-01-14

从表中可以看出，C3P0已经很久没有更新了，DBCP更新速度很慢，基本处于不活跃状态，而Druid和HikariCP处于活跃状态的更新中，这就是我们说的二代产品了。

C3P0 - 彻底已死

C3P0在很长一段时间内，它一直是Java领域内数据库连接池的代名词，当年盛极一时的Hibernate都将其作为内置的数据库连接池，可以业内对它的稳定性还是认可的。C3P0功能简单易用，稳定性好这是它的优点，但是性能上的缺点却让它彻底被打入冷宫。C3P0的性能很差，差到即便是同时代的产品相比它也是垫底的，更不用和Druid、HikariCP等相比了。正常来讲，有问题很正常，改就是了，但c3p0最致命的问题就是架构设计过于复杂，让重构变成了一项不可能完成的任务。随着国内互联网大潮的涌起，性能有硬伤的c3p0彻底的退出了历史舞台。

推荐示例：

<c3p0-config>
  <named-config name="myApp">
    <!-- myApp是在类中引用数据库连接池的名称 -->
    <property name="user">test</property>
      <property name="password">***</property>
    <property name="driverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
    <property name="jdbcUrl">jdbc:sqlserver://127.0.0.1:1433;databaseName=java_web</property>
    <!-- 数据库中的连接不足时，一次可以向数据库服务器申请多少个连接 -->
    <property name="acquireIncrement">2</property>
    <!-- 初始换数据库连接池连接的数量 -->
    <property name="initialPoolSize">5</property>
    <!-- 数据连接池中的最小数据库数量 -->
    <property name="minPoolSize">5</property>
     <!-- 数据连接池中的最大数据库数量 -->
    <property name="maxPoolSize">15</property>
    <!-- c3p0可以维护的statement的个数 -->
    <property name="maxStatements">10</property> 
    <!--每个连接同时可以使用的statement对象的个数  -->
    <property name="maxStatementsPerConnection">5</property>
  </named-config>
</c3p0-config>

DBCP - 咸鱼翻身

DBCP（DataBase Connection Pool）属于Apache顶级项目Commons中的核心子项目，在Apache的生态圈中的影响里十分广泛，比如最为大家所熟知的Tomcat就在内部集成了DBCP，实现JPA规范的OpenJPA，也是默认集成DBCP的。但DBCP并不是独立实现连接池功能的，它内部依赖于Commons中的另一个子项目Pool，连接池最核心的“池”，就是由Pool组件提供的，因此，DBCP的性能实际上就是Pool的性能，DBCP和Pool的依赖关系如下表：

Apache Commons DBCP	Apache Commons Pool
v1.2.2	v1.3
v1.3	v1.5.4
v1.4	v1.5.4
v2.0.x	v2.2
v2.1.x	v2.4.2
v2.2.x	v2.5.0

可以看到，因为核心功能依赖于Pool，所以DBCP本身只能做小版本的更新，真正大版本的更迭则完全依托于pool。有很长一段时间，pool都还是停留在1.x版本，这直接导致DBCP也更新乏力。很多依赖DBCP的应用在遇到性能瓶颈之后，别无选择，只能将其替换掉，DBCP忠实的拥趸tomcat就在其tomcat 7.0版本中，自己重新设计开发出了一套连接池（Tomcat JDBC Pool）。好在，在2013年事情终于迎来转机，13年9月Commons-Pool 2.0版本发布，14年2月份，DBCP也终于迎来了自己的2.0版本，基于新的线程模型全新设计的“池”让DBCP重焕青春，虽然和新一代的连接池相比仍有一定差距，但差距并不大，DBCP2.x版本已经稳稳达到了和新一代产品同级别的性能指标。

DBCP终于靠Pool咸鱼翻身，打了一个漂亮的翻身仗，但长时间的等待已经完全消磨了用户的耐心，与新一代的产品项目相比，DBCP没有任何优势，试问，谁会在有选择的前提下，去选择那个并不优秀的呢？也许，现在还选择DBCP2的唯一理由，就是情怀吧。

HikariCP - 性能无敌

HikariCP号称“性能杀手”（It’s Faster），它的表现究竟如何呢，先来看下官网提供的数据：

那它是怎么做到如此强劲的呢？官网给出的说明如下：

• 字节码精简：优化代码，直到编译后的字节码最少，这样，CPU缓存可以加载更多的程序代码；
• 优化代理和拦截器：减少代码，例如HikariCP的Statement proxy只有100行代码；
• 自定义数组类型（FastStatementList）代替ArrayList：避免每次get()调用都要进行range check，避免调用remove()时的从头到尾的扫描；
• 自定义集合类型（ConcurrentBag）：提高并发读写的效率；
• 其他缺陷的优化，比如对于耗时超过一个CPU时间片的方法调用的研究

Druid - 功能全面

Druid，是阿里众多优秀开源项目中的一个。它除了提供性能卓越的连接池功能外，还集成了SQL监控，黑名单拦截等功能，用它自己的话说，Druid是“为监控而生”。

<!-- 阿里 druid 数据库连接池 -->
    < bean id = "dataSource" class = "com.alibaba.druid.pool.DruidDataSource"destroy-method = "close" >  
         <!-- 数据库基本信息配置 -->
         < property name = "url" value = "${url}" />  
         < property name = "username" value = "${username}" />  
         < property name = "password" value = "${password}" />  
         < property name = "driverClassName" value = "${driverClassName}" />  
         < property name = "filters" value = "${filters}" />  
          <!-- 最大并发连接数 -->
         < property name = "maxActive" value = "${maxActive}" />
         <!-- 初始化连接数量 -->
         < property name = "initialSize" value = "${initialSize}" />
         <!-- 配置获取连接等待超时的时间 -->
         < property name = "maxWait" value = "${maxWait}" />
         <!-- 最小空闲连接数 -->
         < property name = "minIdle" value = "${minIdle}" />  
         <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
         < property name = "timeBetweenEvictionRunsMillis" value ="${timeBetweenEvictionRunsMillis}" />
         <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
         < property name = "minEvictableIdleTimeMillis" value ="${minEvictableIdleTimeMillis}" />  
         < property name = "validationQuery" value = "${validationQuery}" />  
         < property name = "testWhileIdle" value = "${testWhileIdle}" />  
         < property name = "testOnBorrow" value = "${testOnBorrow}" />  
         < property name = "testOnReturn" value = "${testOnReturn}" />  
         < property name = "maxOpenPreparedStatements" value ="${maxOpenPreparedStatements}" />
         <!-- 打开 removeAbandoned 功能 -->
         < property name = "removeAbandoned" value = "${removeAbandoned}" />
         <!-- 1800 秒，也就是 30 分钟 -->
         < property name = "removeAbandonedTimeout" value ="${removeAbandonedTimeout}" />
         <!-- 关闭 abanded 连接时输出错误日志 -->   
         < property name = "logAbandoned" value = "${logAbandoned}" />
    </ bean >

常见异常分析

疑似连接池不够大导致的异常

2019.03.12.开发同事反馈，前端页面报如下错误：

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

处理过程如下：

• 1.首先排除DB是否有死锁、慢查询；
• 2.连接(Connection)对象一定要及时关闭(Close)；

  要关闭连接，可以使用 Connection 对象的conn.Close()方法，

• 3.JAVA业务：修改连接字符串

    <bean>
        <property name="user" value="***"/>
        <property name="password" value="***"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="100"/>
        <property name="borrowConnectionTimeout" value="60"/>
        <property name="maxLifetime" value="1200"/>
    </bean>

    #.不知道....
    <property name="initialSize" value="5" />        <!-- 初始连接数量 -->  
    <property name="maxActive" value="15" />         <!-- 最大连接数量 -->

• 4.找出程序中要求返回大数据量的会话请求，调整代码
• 5.设置IIS连接数上限(不推荐，迫不得已)