MySQL数据库连接池项目

2019年9月15日00:43:57

MySQL数据库连接池项目功能技术点简介

使用到的技术

1. MySQL数据库编程
2. 单例模式
3. queue队列容器
4. C++11多线程编程
5. 线程互斥
6. 线程同步通信
7. unique_lock
8. 基于CAS的原子整型
9. 智能指针shared_ptr
10. lambda表达式
11. 生产者-消费者线程模型

与线程相关的，并没有使用OS提供的API。而是基于C++11的语言级别实现的线程，所以其跨平台的特性十分好。

项目背景

MySQL两种存储引擎: MyISAM和InnoDB 详见我的博客：存储引擎
数据库在本质上还是一个磁盘上的操作（数据存放在磁盘上），MySQL的两种存储引擎：MyISAM和InnoDB最终把表结构 索引 和表的数据都是存放在磁盘上的。不同的存储引擎 文件的后缀名是不一样的。

若是数据库访问过多，也即：磁盘I/O就比较多了，此刻就遇上了数据库访问的一个瓶颈（像MySQL SQL Server 和Oracle三者比较相似的一点就是：基于C/S 设计的）。数据库的访问瓶颈解决有两种：为了减少磁盘的I/O的次数 ，或者说为了提高MySQL数据库（基于C/S设计）的访问瓶颈，除了在（第一种优化：在数据库 和 服务器应用的中间加上一层 缓存数据库，来把热点数据都放在缓存数据库当中，而非所有的数据都去访问MySQL数据库，开启一个磁盘I/O，热点数据的访问量都比较大，对于数据库来说：磁盘的访问就成了一个访问瓶颈了）

缓存数据库与关系型数据库的不同在于：前者存放的是键值对，后者存的是二维表。

服务器应用在访问MySQL server的时候，相当于一个数据库客户端。在client上，通过一些API 把SQL语句通过网络发送到MySQL server上，MySQL server执行一套SQL处理流程：根据SQL语句具体进行一些增删改查，然后通过网络把结果返回到客户端。然后关闭一条通信连接。

在进行数据库的访问时，首先要进行从client到server的一个connect 发起一个连接（涉及了TCP的三次握手），网络连接成功之后MySQL server肯定要对新的连接进行一些身份 权限的验证。之后返回连接成功。然后client就会发起相应的增删改查的SQL语句，然后MySQL server在服务端进行SQL语句的具体实现，进行数据的增删改查。然后把相应的结果 进行向MySQL client（服务器应用）的返回。服务器应用在接收到这个返回之后（数据处理完了，就要及时地关闭这条数据库的连接。也即在增删改查完了之后，不能一直打开着这条连接）。服务器在实现上都是多线程的，对于每一个用户的增删改查而言 不能一直占用着一条数据库连接，就要及时地关闭这条数据库的连接。且对于server端，一个SQL的通信就要占据一个线程。通信完了之后，服务器应用就要把MySQL这条连接给disconnect 或者 close掉，这时MySQL server这里就涉及到 给之前连接的用户分配的资源的回收。回收完了之后，就要涉及TCP的四次挥手。

在每秒的并发量非常大的时候，也即每秒的服务器应用访问数据库的次数是相当大的。以上的四个过程被在 每一次的数据库增删改查的访问之中涉及到，于是在数据量大的时候也是相当费时的。

第二种优化： 增加连接池。（在并发连接量大的时候，每一个用户的请求达到服务器 都有可能对应一个数据库的增删改查操作。对于服务器应用而言：是MySQL的客户端，首先要TCP三次握手与MySQL server创建一个新的网络连接、然后进行MySQL Server连接认证（密码是否正确、用户是否合法）、（SQL语句执行完成之后，把结果返回给服务器应用，也即MySQL的客户端）MySQL Server关闭连接回收资源和TCP四次挥手，这都是性能的耗费。）
服务器端增加缓存服务器缓存常用的数据之外（例如redis）。还可以增加连接池，来提高MySQL Server的访问效率，在高并发情况下，大量的TCP三次握手、MySQL Server连接认证、MySQL Server关闭连接回收资源和TCP四次挥手所耗费的性能时间也是很明显的，增加连接池就是为了减少这一部分（四个过程）的性能损耗。

在市场上比较流行的连接池包括阿里的druid，c3p0以及apache dbcp连接池，它们对于短时间内大量的数据库增删改查操作性能的提升是很明显的，但是它们有一个共同点就是，全部由Java实现的。在服务器应用中涉及到数据库的话，都是要使用连接池，包括缓存数据库。都可以提高关系型数据库的效率。

那么本项目就是为了在C/C++项目中，提供MySQL Server的访问效率，实现基于C++代码的数据库连接池模块。

连接池功能点

四大池：进程池 线程池 内存池 连接池。
连接池主要是针对数据库实现的：就是在系统服务器端 系统启动的时候，事先创建和MySQL server的一定数量的连接。当用户发起请求（涉及到数据库的增删改查操作）的时候，此时就不用去进行 上面的四个过程损耗。用户的请求需要执行 数据库的增删改查操作时，直接从连接池里面 拿出来一条空闲的可以使用的连接给当前的用户请求使用；请求处理完成之后，也不要去把这个连接给关掉 而是直接放回到连接池里面即可。

以下是连接池最基础的几个功能点：
连接池一般包含了数据库连接所用的ip地址、port端口号、用户名和密码以及其它的性能参数，例如初始连接量，最大连接量，最大空闲时间、连接超时时间等，该项目是基于C++语言实现的数据库连接池，主要也是实现以上几个所有连接池都支持的通用基础功能。
初始连接量（initSize）：表示连接池事先会和MySQL Server创建initSize个数的connection连接，当服务器应用发起MySQL访问时，不用再创建和MySQL Server新的连接，直接从连接池中获取一个可用的连接就可以，使用完成后，并不去释放connection，而是把当前connection再归还到连接池当中。
最大连接量（maxSize）：当并发访问MySQL Server的请求增多时，初始连接量已经不够使用了，此时会根据新的请求数量去创建更多的连接给应用去使用（访问数据库），但是新创建的连接数量上限是maxSize，不能无限制的创建连接，因为每一个连接都会占用服务器端的一个socket资源，一般连接池和服务器程序是部署在一台主机上的，如果连接池占用过多的服务端的socket资源，那么服务器（可用的socket就非常少了）就不能接收太多的新用户的客户端请求了。当这些连接使用完成后，再次归还到连接池当中来维护。

注释下图：客户端（例如 浏览器），请求到服务器应用（server APP），再到后台的数据库。后两者若是创建的连接过多（与数据库建立连接 占用的socket资源太多了），则服务器应用上可用的socket就比较少了，于是接收用户的连接也就比较少了（因为前两者 也是网络通信 也是要占据socket资源的）。

最大空闲时间（maxIdleTime）：当访问MySQL的并发请求多了以后，连接池里面的连接数量会动态增加，上限是maxSize个，当这些连接用完再次归还到连接池当中。如果在指定的maxIdleTime里面，这些新增加的连接都没有被再次使用过，那么新增加的这些连接资源就要被回收掉（他们所占据的socket也就可以复用起来，去接收来自客户端的新的连接），只需要保持初始连接量initSize个连接就可以了。
连接超时时间（connectionTimeout）：当MySQL的并发请求量过大（都要访问数据库），连接池中的连接数量已经到达maxSize了，而此时没有空闲的连接可供使用，那么此时应用从连接池获取连接无法成功，它通过阻塞的方式获取连接的时间如果超过connectionTimeout时间，（在这connectionTimeout=100ms之内，若有空闲的连接从连接池里面出现了，直接给它用；否则connectionTimeout时间等完了 还是空池）那么获取连接失败，无法访问数据库。

该项目主要实现上述的连接池四大功能，其余连接池更多的扩展功能，可以自行实现。

项目功能点设计和技术细节详解

功能实现设计

ConnectionPool.cpp和ConnectionPool.h：连接池代码实现
Connection.cpp和Connection.h：数据库操作代码、增删改查代码实现。负责连接的

在ConnectionPool里面，主要就是包含：数据库连接所用到的 数据库的IP地址、数据库登录的用户名 密码、端口号、以及要选用的数据库、以及初始连接量 最大连接量 最大空闲时间 连接超时时间。
在Connection里面，主要是数据库操作相关的内容：数据库的连接、增删改查操作。

连接池主要的功能点详细描述

1. 连接池只需要一个实例，所以实现ConnectionPool类（管理了很多的连接 Connection有很多连接对象）的时候，以单例模式进行设计。
2. 上图注释：处理流程为：左边3个客户端，用户发起了请求到中间的服务器应用，服务器应用（相当于一个MySQL client）会根据用户的request请求，进行相应的数据库的访问（增删改查）。进行具体的访问数据库MySQL server。若是没有连接池，则server app的某一个线程直接与MySQL server进行TCP连接、验证、发送SQL语句、关闭连接回收资源、TCP四次挥手。然现在在服务器应用端 维护了一个连接池，选择queue作为数据结构来存储 Connection，于是事先可以创建很多的Connection进行排队。在最开始创建连接池的时候，大小为initSize（初始连接量）。此时服务器应用发起一个数据库访问请求的话，肯定是现在队列里面队头 取出一个Connection，然后通过Connection 与MySQL server直接通信（发送SQL语句）即可。省去了之前的四个耗时的过程，使用完成之后 也不要把Connection关掉，而是把Connection入队添加到连接池队列里面。由于server app是多线程的设计，于是就可以出现多个线程同时请求数据库，也即：多线程都在从队列里面获取Connection（就是把Connection出队，从队头删掉；用完之后再归还连接，入队）。所以一定要保证这个队列容器的线程安全特性。比如server app采用epoll + 多线程，还是使用外部的三方库libevent moduo网络库等（都是epoll + 多线程）的。至于线程安全 采用互斥锁mutex进行保证的，需要手动lock和unlock，所以这里使用lock_guard或者unique_lock 利用智能指针的思想 去对锁进行自动的加锁 解锁。ConnectionPool是单例设计的，且有可能多个线程都在获取ConnectionPool的单例，所以这得是一个线程安全的单例模式。这里直接使用一个线程安全的懒汉式单例模式即可。
3. 从ConnectionPool中可以获取和MySQL的连接 Connection。服务器应用的多个线程从这个线程安全的队列容器当中获取Connection。进行与MySQL server的SQL的发送，以及接收返回数据 完了之后把Connection归还到连接池当中。
4. 空闲连接Connection全部维护在一个线程安全的Connection队列中，使用线程互斥锁保证队列的线程安全。因为服务器应用是多线程设计的，为了最大可能的压榨多核CPU的性能，肯定使用的是多线程编程。
5. 如果Connection队列为空，还需要再获取连接，此时需要动态创建连接，上限数量是maxSize。此时若是并发量比较大，那么从连接池队列里面取出Connection的速度会非常快，当把Connection取完之后 接下来还有请求需要访问数据库，连接池空了，此时要创建新的连接，然后把这个Connection进行复用。创建新的Connection的设计为：此时连接池空了，服务器应用的一个线程要去访问数据库 需要从连接池里面拿到Connection，空的。暂时获取不到Connection了，于是该线程需要在connectionTimeout=100ms 时间内等待，但不是死等（不是睡100ms，再起来看连接池里面有没有其他线程归还的Connection，或者新建的Connection）。而是在这100ms过程中 不断的去检测队列里面有没有Connection，有的话 拿走使用；没有的话 在connectionTimeout=100ms之后还没有等到可用的Connection（没有归还的，没有新建的），就100ms之后 获取Connection失败。对于服务器应用的这个线程而言，它想要的只是一个可用的Connection，与怎么来的（连接池新建的 别的线程归还的）不关心。这是属于ConnectionPoll的模块。
6. 于是设计：在服务器应用上 要从ConnectionPoll里面拿Connection为getConnection为消费者线程，而在ConnectionPoll里面启一个线程 专门为生产者线程，为连接池队列生成Connection的，上限为maxSize。若是系统整个连接池的Connection数量已经达到上限，就不再生产了。此时若是达到上限 生产者线程不再生产Connection，且服务器应用的其他线程又占据着Connection不放，则导致某个线程在花费100ms之后，看队列还是一个空的，那么获取Connection失败，无法访问数据库。
7. 队列中空闲连接时间超过maxIdleTime的就要被释放掉，只保留初始的initSize个连接就可以了，这个功能点肯定需要放在独立的线程中去做。也即：最终这个队列里面Connection用完了之后，缓冲了很多的Connection，但是过多的连接并没有必要。毕竟连接池也是在服务器端实现的，不必占用服务器端的socket资源。所以当额外生成的Connection的maxIdleTime=60s超过了，额外的Connection就要被释放掉，只保留初始的initSize个连接就可以了。
8. 如果Connection队列为空，而此时连接的数量已达上限maxSize，那么等待connectionTimeout时间，如果还获取不到空闲的连接，那么获取连接失败。此处从Connection队列获取空闲连接，可以使用带超时时间的mutex互斥锁来实现连接超时时间。
9. 用户获取的连接用shared_ptr智能指针来管理，用lambda表达式定制连接释放的功能（不是真正释放 关闭连接，而是把连接归还到连接池中）
10. 连接的生产和连接的消费采用生产者-消费者线程模型来设计，使用了线程间的同步通信机制条件变量和互斥锁。
11. 此时还需要一个定时线程，负责清理连接队列多余的空闲连接（超过maxIdleTime的还没有被使用的 多余的Connection被回收掉，以节省服务器的socket资源，只保留初始的连接量即可。）这里面也涉及到了：线程的生产者 消费者线程的同步通信、队列的安全、包括队列连接的计数用原子整型类型、基于CAS的保证该整型的++ --操作是线程安全的操作、以及线程安全的懒汉式单例模式。如下图所示：
    

开发平台选型

有关MySQL数据库编程、多线程编程、线程互斥和同步通信操作、智能指针、设计模式、容器等等这些技术在C++语言层面都可以直接实现，因此该项目选择直接在windows平台上进行开发，当然放在Linux平台下用g++也可以直接编译运行。

当项目开发完成之后，需要进行一项压力测试。

压力测试

根据数据量的大小1000 5000 10000 20000，进行性能测试 。
验证数据的插入操作所花费的时间，第一次测试使用普通的数据库访问操作，第二次测试使用带连接池的数据库访问操作，对比两次操作同样数据量所花费的时间。

MySQL数据库编程代码

首先要在Windows上安装MySQL开发版，MySQL的windows安装文件云盘地址如下（development开发版，包含了mysql头文件和libmysql库文件）：
链接：https://pan.baidu.com/s/1Y1l7qvpdR2clW5OCdOTwrQ
提取码：95de

我的这个项目还是使用我以前的旧的5.5的版本。接下来，安排 在服务里面，打开数据库服务。
MySQL默认工作在3306端口上
进入下面的MySQL client上，先操作一下！！！


delete from score;是指把这个score表里面的数据都删完！
这都是MySQL的基本语句，仅作练手。可以忽略！

下面开始操作：
第一步：创建数据库 chat

第二步：建表 不会SQL语句的话，你直接看我的把吧。
第三步：找到mysql.h头文件和库文件路径。
C:\Program Files\MySQL\MySQL Server 5.5\include
C:\Program Files\MySQL\MySQL Server 5.5\lib
这是我的路径，慢慢找。肯定会有的


第四步：创建一个工程：通用连接池的实现

之后就是编码实现了。

Oracle开发商已经为我们封装好了，就在上面两个路径下的。所以我们想直接包含其头文件< mysql.h >，但是我的VS2019 怎么知道去那两个路径去找呢？
在此之前，需要先行配置一下：
这里的MySQL数据库编程直接采用oracle公司提供的MySQL C/C++客户端开发包，在VS上需要进行相应的头文件和库文件的配置，如下：
1.右键项目 - C/C++ - 常规 - 附加包含目录，填写mysql.h头文件的路径
2.右键项目 - 链接器 - 常规 - 附加库目录，填写libmysql.lib的路径
3.右键项目 - 链接器 - 输入 - 附加依赖项，填写libmysql.lib库的名字
4.把libmysql.dll动态链接库（Linux下后缀名是.so库）放在工程目录下
……………………………………………………………………………………
选择x64位 因为MySQL是64位版本的，它的库lib和dll都是64位生成的，可是我们的VS默认生成的都是x86，,32位的。

包含头文件路径：

包含库路径：

包含一下 库文件完整的名称：libmysql.lib 它的前面必须 加上一个 ; 一定得有这个分号。

把动态链接库 ：
以上四步，即可。

初始编程：
主要有这么几个文件：

第一个文件：

Connection.h文件如下：

#ifdef _MSC_VER
#ifdef _WIN64
#include <WinSock2.h>
#elif _WIN32
#include <winsock.h>
#endif
#endif
//上面的这个预编译 你先不要加，不能通过。若是出现什么 error fd什么的，再加

#include <string>
#include <mysql.h>
using namespace std;
// 数据库操作类
class Connection
{
   
public:
	// 初始化数据库连接
	Connection();

	// 释放数据库连接资源
	~Connection();

	// 连接数据库
	bool connect(string ip, //IP地址
		unsigned short port, //端口号
		string user,//用户名
		string password,//密码
		string dbname);//数据库名

	// 更新操作 insert、delete、update
	bool update(string sql);

	// 查询操作 select
	MYSQL_RES* query(string sql);

private:
	MYSQL* _conn; // 表示和MySQL Server的一条连接
};

第二个文件：

public.h文件：

//存放：把这个项目的各个文件共享的 宏 类型，给其他源文件直接使用
#define LOG(str) \
	cout<<__FILE__<<":"<<__LINE__<<" "<<__TIMESTAMP__<<" : "<<str<<endl

第三个文件：

Connection.cpp文件如下：

#include <iostream>
#include "public.h"
#include "Connection.h"
using namespace std;

Connection::Connection()
{
   
	_conn = mysql_init(nullptr);// 初始化数据库连接
}
// 释放数据库连接资源
Connection::~Connection()
{
   
	if (_conn != nullptr)
		mysql_close(_conn);
}
	// 连接数据库
bool Connection::connect(string ip, 
	unsigned short port, 
	string username, 
	string password,
	string dbname)
{
   
	MYSQL* p = mysql_real_connect(_conn, ip.c_str(), username.c_str(),
		password.c_str(), dbname.c_str(), port, nullptr, 0);
	return p != nu