手写数据库连接池

项目背景

在高并发情况下，如果每次用户访问数据库都需要创建和销毁数据库连接对象，这将导致大量的TCP三次握手、MySQL Server连接认证、MySQL Server关闭连接以及资源回收的操作，这些过程会带来巨大的性能开销。如果在一开始就创建好一些连接对象存放在连接池中，当用户需要时从连接池中取出，用完时归还，就可以起到资源重用，从而节省了频繁创建连接销毁连接所花费的时间，提升系统响应的速度。

连接池功能

• 初始连接量（initSize）

  • 连接池事先和MySQLServer创建initSize个连接

  • 当应用发起MySQL访问时，直接从连接池中获取一个可用的连接，使用完成后归还连接池

• 最大连接量（maxSize）

  • 当并发访问MySQL请求增多至超过初始连接量时，连接池会根据数量请求去创建更多的连接。

  • 但是连接的数量上限是maxSize，因为连接占用socket资源，如果连接池占用过多的socket资源，那么服务器就不能接收太多的客户端请求了。（连接池和服务器部署在同一台主机上）

• 最大空闲时间（maxIdleTime）

  • 如果新增加的连接在最大空闲时间内都没有被使用，那么这些连接就会被回收

• 连接超时时间（connectionTimeout）

  • 如果在connectionTimeout内都没有空闲的连接可以使用，那么连接就会失败，无法访问数据库

连接池设计

关键技术点

• MySQL数据库编程
• 单例模式
• C++11多线程编程、生产者-消费者线程模型
• 智能指针shared_ptr、lambda表达式

项目配置

使用vs2019编写项目代码，由于需要调用MySQL API来完成数据库的连接、查询和更新等操作，需要在vs2019中导入MySQL的头文件、库文件。

1. VS2019选择X64，因为安装的MySQL是64位的

   

2. 右键项目 - 属性 - C/C++ - 常规 - 附加包含目录，填写mysql.h头文件的路径（例如：D:\developer_tools\MySQL\Files\MySQL Server 5.7\include）。注意：先在源文件下创建一个.cpp文件后，下图中C/C++的选项才会出现。

   

3. 右键项目 - 属性 - 链接器 - 常规 - 附加库目录，填写libmysql.lib的路径（D:\developer_tools\MySQL\Files\MySQL Server 5.7\lib）

4. 右键项目 - 属性 - 链接器 - 输入 - 附加依赖项，填写libmysql.lib库的名字（libmysql.lib）（静态库）

5. 把libmysql.dll动态链接库（Linux下后缀名是.so库）放在工程目录下（动态库）

   

数据库编程

创建数据表

create databases pool;
use pool;
CREATE TABLE user(id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) DEFAULT NULL,
    age INT(11) DEFAULT NULL,
    sex ENUM('male','female') DEFAULT NULL)ENGINE=INNODB DEFAULT CHARSET=utf8;
SQL

MySQL C API

需要包含头文件#include <mysql.h>

• 初始化MySQL连接

  // 成功返回mysql指针，失败返回NULL
  MYSQL *mysql_init(MYSQL *mysql) ;
  C

• 连接MySQL服务器

  // 成功返回mysql指针，失败返回NULL
  MYSQL * STDCALL mysql_real_connect(MYSQL *mysql, 
                                  const char *host,   // mysql服务器的主机地址
                                  const char *user,   // 登录用户名
                                  const char *passwd, // 登录密码
                                  const char *db,     // 使用的数据库名称
                                  unsigned int port,  // MySQL端口
                                  const char *unix_socket,    // 本地套接字, 不使用指定为NULL
                                  unsigned long clientflag);  // 连接标志，通常为0
  C

• 执行sql语句

  // 执行一个sql语句, 增删查改的sql语句都可以
  int mysql_query(MYSQL *mysql, const char *query);
  参数:
      - mysql: mysql_real_connect()的返回值
      - query: 一个可以执行的sql语句, 结尾的位置不需要加';'
  返回值: 
      - 如果查询成功，返回0。如果是查询, 结果集在mysql对象中
      - 如果出现错误，返回非0值。
  C

• 获取结果集

  MYSQL_RES *mysql_use_result(_conn);
  SCSS

• 关闭MySQL

  mysql_close(&mysql);
  C

使用C++封装MySQL C API

#ifndef CONNECTION_H
#define CONNECTION_H
#include <mysql.h>
#include <string>
#include <ctime>
#include "public.h"
using namespace std;
class Connection {
public:
	// 初始化数据库连接
	Connection();

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

	// 连接数据库
	bool connect(string ip, unsigned short port, string user, string passward, string dbname);

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

	// 查询操作select
	MYSQL_RES* query(string sql);
private:
	MYSQL* conn_; // 表示和MySQL Server的一条连接
};
#endif

C++

#include "Connection.h"
/*
* 实现MySQL数据库的增删改查操作
*/
// 初始化数据库连接
Connection::Connection() {
	conn_ = mysql_init(nullptr);
}

// 释放数据库连接
Connection::~Connection() {
	if (conn_ != nullptr)
		mysql_close(conn_);
}

// 连接数据库
bool Connection::connect(string ip, unsigned short port, string user, string password, string dbname) {
	MYSQL* p = mysql_real_connect(conn_, ip.c_str(), user.c_str(),
		password.c_str(), dbname.c_str(), port, nullptr, 0);
	// 输出连接失败原因
	cerr << mysql_error(conn_) << endl;
	return p != nullptr;
}

// 更新操作insert/delete/update
bool Connection::update(string sql) {
	if (mysql_query(conn_, sql.c_str())) {
		LOG("Update Failed:" + sql);
		return false;
	}
	return true;
}

// 查询操作select
MYSQL_RES* Connection::query(string sql) {
	if (mysql_query(conn_, sql.c_str())){
		LOG("Query Failed:" + sql);
		return nullptr;
	}
	return mysql_use_result(conn_);
}
CPP

测试一下效果：

#include "Connection.h"
#include <iostream>
using namespace std;
int main() {
	Connection conn;
	string sql = "insert into user(name,age,sex) values('li', 21, 'female')";
	conn.connect("127.0.0.1", 13306, "root", "123456", "pool");
	conn.update(sql);
	return 0;
}
C++

可以看到，成功插入一条新数据：

功能模块实现

线程安全的懒汉式单例模式

连接池只需要一个实例，所以ConnectionPool应该按照单例模式设计，即一个类不管创建多少对象，永远只能得到该类型的一个对象实例。单例模式又分为懒汉式单例模式和饿汉式单例模式。相比于懒汉式单例模式，饿汉式单例模式在没有需要获取实例对象前，实例对象就已经产生了，这样会浪费系统资源使启动时间过长。因此，在数据库连接池的设计中，我们使用线程安全的懒汉式单例模式。

#ifndef CONNECTIONPOOL_H
#define CONNECTIONPOOL_H
#include "Connection.h"
class ConnectionPool {
public:
	// 获取类的唯一实例对象的接口方法
	static ConnectionPool* getConnectionPool();
private:
	// 私有化构造函数
	ConnectionPool(); 
};
#endif

#include "ConnectionPool.h"
// 获取类的唯一实例对象的接口方法
ConnectionPool* ConnectionPool::getConnectionPool() {
	static ConnectionPool pool; // 编译器自动lock和unlock
	return &pool;
}
C++

从配置文件加载配置项

• 打开配置文件
• 判断文件是否存在
• 循环按行读取记录，按=和\n分割提取key和value
• 给私有成员赋值

// 从配置文件加载配置项
bool ConnectionPool::loadConfigFile() {
	// 以只读方式打开mysql.ini
	ifstream ifs;
	ifs.open("mysql.ini", ios::in);
	
	// 判断配置文件是否存在
	if (!ifs.is_open()) {
		LOG("mysql.ini file is not exist!");
		return false;
	}

	// 循环按行读取记录，按=和\n分割提取key和value
	string line;
	while (std::getline(ifs, line)) {
		string str = line;
		int idx = str.find('=', 0);
		if (idx == -1) { // 无效的配置项
			continue;
		}
		int endIdx = str.find('\n', idx);
		string key = str.substr(0, idx);
		string value = str.substr(idx + 1, endIdx - idx - 1);
		if (key == "ip") {
			ip_ = value;
		}
		else if (key == "port") {
			port_ = stoi(value);
		}
		else if (key == "username") {
			username_ = value;
		}
		else if (key == "passward") {
			passward_ = value;
		}
		else if (key == "dbname") {
			dbname_ = value;
		}
		else if (key == "initSize") {
			initSize_ = stoi(value);
		}
		else if (key == "maxSize") {
			maxSize_ = stoi(value);
		}
		else if (key == "maxIdleTime") {
			maxIdleTime_ = stoi(value);
		}
		else if (key == "connectionTimeout") {
			connectionTimeout_ = stoi(value);
		}
	}
	ifs.close();
	return true;
}
C++

连接池的构造

• 加载配置项
• 创建初始数量的连接
• 启动连接池的生产者线程
• 启动连接池定时清理线程

#ifndef CONNECTIONPOOL_H
#define CONNECTIONPOOL_H
#include "Connection.h"
#include <fstream>
#include <string>
#include <mutex>
#include <queue>

class ConnectionPool {
public:
	// 获取类的唯一实例对象的接口方法
	static ConnectionPool* getConnectionPool();
private:
	// 私有化构造函数
	ConnectionPool(); 

	// 从配置文件加载配置项
	bool loadConfigFile();

	// 生产者线程
	void produceConnectionTask();

	// 定时清理连接线程
	void scannerConnectionTask();

	string ip_; // ip地址
	unsigned int port_; // 端口号
	string username_; // 用户名
	string passward_; // 密码
	string dbname_; // 数据库名
	int initSize_; // 初始连接量
	int maxSize_; // 最大连接量
	int maxIdleTime_; // 最大空闲时间
	int connectionTimeout_; // 连接超时时间

	queue<Connection*> connectionQue_; // 存储 mysql 连接的队列
	mutex queueMutex_; // 维护连接队列线程安全的互斥锁
	atomic_int connectionCnt_; // 记录创建的连接的总数量
	condition_variable cv; // 设置条件变量，用于连接生产者线程和消费者线程的通信
};
#endif
C++

// 连接池的构造
ConnectionPool::ConnectionPool() {
	if (!loadConfigFile()) {
		return;
	}

	// 创建初始数量的连接
	for (int i = 0; i < initSize_; i++) {
		Connection* p = new Connection();
		p->connect(ip_, port_, username_, passward_, dbname_);
		connectionQue_.push(p);
		connectionCnt_++;
	}

	// 启动连接池的生产者线程
	thread produce(bind(&ConnectionPool::produceConnectionTask, this));
	produce.detach();

	// 启动连接池定时清理连接线程
	thread scanner(bind(&ConnectionPool::scannerConnectionTask, this));
	scanner.detach();
}
C++

生产者线程

作用：生产连接

• 作为类的成员方法，可以很方便地访问类的成员变量
  • 由于线程函数是C接口的方法，因此传递函数时需要用绑定器将this指针绑定到生产者线程上
• 加锁
• 连接池队列不空时，进入等待状态
• 连接池队列为空时，生产连接
• 通知消费者线程

// 启动连接池的生产者线程
thread produce(bind(&ConnectionPool::produceConnectionTask, this));
produce.detach();

// 生产者线程
void ConnectionPool::produceConnectionTask() {
	for (;;) {
		unique_lock<mutex> lock(queueMutex_);

		// 连接池队列不空，生产者线程进入等待状态
		while (!connectionQue_.size())
			cv.wait(lock);

		// 连接数量没有达到上限，继续生产新的连接
		if (connectionCnt_ < maxSize_) {
			Connection* p = new Connection();
			p->connect(ip_, port_, username_, passward_, dbname_);
			connectionQue_.push(p);
			connectionCnt_++;
		}

		// 通知消费者线程获取连接
		cv.notify_all();
	}
}
C++

消费者线程

作用：提供给外部的接口，从连接池中获取一个可用的空闲连接。返回一个智能指针（封装了连接），因为智能指针能够出作用域自动析构，可以重定义其删除器归还连接，避免让用户自己调用函数将连接归还，简化用户操作

• 加锁
• 连接池队列为空时，进入等待状态，并判断是否连接超时
• 连接池队列不空时，消费连接
• 通知生产者线程

// 消费者线程，提供给外部的接口，从连接池中获取一个可用的空闲连接
shared_ptr<Connection> ConnectionPool::getConnection() {
	unique_lock<mutex> lock(queueMutex_);

	// 连接池队列为空，消费者线程进入等待状态
	while (connectionQue_.empty()) {
		cv_status status = cv.wait_for(lock, chrono::milliseconds(connectionTimeout_)); // 线程池有连接唤醒/超时唤醒
		if (status == cv_status::timeout && connectionQue_.empty()) { // 超时唤醒
			LOG("获取空闲连接超时...获取连接失败！");
			return nullptr;
		}
	}

	/*
	* shared_ptr智能指针析构时，会把connection的资源delete，相当于调用connection的析构函数，connection就被close了
	* 这里需要自定义shared_ptr的释放资源的方式，把connection直接归还到队列中
	*/

	// 从连接池队列取出一个连接
	shared_ptr<Connection> sp(connectionQue_.front(),
		[&](Connection* pcon) {
			// 这是在服务器应用线程中调用的
			unique_lock<mutex> lock(queueMutex_);
			connectionQue_.push(pcon);
		});
	connectionQue_.pop();

	// 通知生产者线程生产
	if (connectionQue_.empty()) {
		cv.notify_all();
	}
	return sp;
}
C++

定时清理连接线程

// 定时清理连接线程
void ConnectionPool::scannerConnectionTask() {
	for (;;) {
		// 通过sleep模拟定时效果
		this_thread::sleep_for(chrono::seconds(maxIdleTime_));

		// 扫描整个队列，释放多余连接
		unique_lock<mutex> lock(queueMutex_);
		while (!connectionQue_.empty()) {
			Connection* p = connectionQue_.front();
			if (connectionCnt_ > initSize_ && p->getAlivetime() >= maxIdleTime_ * 1000) {
				connectionQue_.pop();
				delete p;
				connectionCnt_--;
			}
			else { // 队头连接没有超过最大空闲时间，其他连接肯定也没有
				break;
			}
		}
	}
}
C++

在Linux下编译数据库连接池

将源文件和头文件放在一个文件夹下，如下：

.
├── Connection.cpp
├── Connection.h
├── ConnectionPool.cpp
├── ConnectionPool.h
├── main.cpp
├── mysql.ini
└── public.h
STYLUS

在终端执行g++指令：

# 进入ConnectionPool目录
cd ConnectionPool
# g++ -o 可执行文件名 源文件1/2/3... 
# 指定头文件的位置（-I）、库文件的目录（-L）、库名（-l）
# mysql头文件路径（-I/usr/include/mysql） mysql库文件路径（-L/usr/lib/x86_64-linux-gnu） 库名（-lmysqlclient）
g++ -o ConnectionPool Connection.cpp ConnectionPool.cpp main.cpp -I/usr/include/mysql -L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread
AWK

执行：

./ConnectionPool
JBOSS-CLI

Linux动态库

动态库的工作原理

• 静态库：GCC 进行链接时，会把静态库中代码打包到可执行程序中
• 动态库：GCC 进行链接时，动态库的代码不会被打包到可执行程序中（会记录动态库的信息）
• 动态库在程序运行时动态载入内存，当程序用到了动态库的哪个api，动态载入器会去寻找动态库文件放到内存里面
• 可以通过ldd （list dynamic dependencies）命令检查动态库依赖关系

如何定位共享文件？

• 当系统加载可执行代码时候，能够知道其所依赖的库的名字，但是还需要知道绝对路径
• 系统的动态载入器来获取该绝对路径
• 对于elf格式的可执行程序，是由ld-linux.so（动态载入器）来完成的，它先后搜索elf文件的 DT_RPATH段（无法修改） ——> 环境变量LD_LIBRARY_PATH ——> /etc/ld.so.cache文件列表 ——> /lib/，/usr/lib目录找到库文件后将其载入内存。

数据库连接池动态库的生成

生成.o的二进制目标文件，得到与位置无关的代码：

# -fPIC 选项表示生成位置无关
g++ -c -fPIC Connection.cpp ConnectionPool.cpp
R

生成动态库：

g++ -shared Connection.o ConnectionPool.o -o libconpool.so
STYLUS

配置动态载入器加载路径：

// 修改/etc/ld.so.conf 
sudo vim /etc/ld.so.conf
// 添加路径
/home/ubuntu/workspace/lib
// 使其生效
sudo ldconfig
AWK

编译链接程序：

g++ -o conn main.cpp -L/home/ubuntu/workspace/lib -lconpool -pthread -I/usr/include/mysql -L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread
AWK

运行：

./conn
BASH

压力测试

数据量	未使用连接池花费的时间	使用连接池花费的时间
1000	单线程：0.85s 四线程：0.73	单线程：0.06 四线程：0.04
5000	单线程：4.13s 四线程：3.72	单线程：0.23 四线程：0.16
10000	单线程：8.36s 四线程：7.56	单线程：0.44 四线程：0.3

遇到的问题

在windows下能够正常运行，但是在linux下连接服务器上数据库报错‘@’localhost’ (using password: YES) - Access denied for user ‘root。排查了密码和权限都没问题，最后发现是因为Linux和Unix使用的换行符是’\n’，而Windows使用的是’\r\n’。在windows下，std::getline() 函数默认会将\r\n视为换行符，因此它会读取整个行，但会忽略掉 \r。而在Linux中，std::getline 仅将\n视为换行符，因此在读取到换行符时，\r 会作为普通字符一起读入。

在加载配置文件函数中增加去除’/r’的逻辑：

// 去除空格和'\r'
for(int i=0; i < line.size(); i++) {
    if(line[i]==' ' || line[i]=='\r') continue;
    str += line[i];
}
C++

TIP：GitHub链接：https://github.com/ZHCANO/ConnectionPool

参考资料

• https://juejin.cn/post/7141190286838857735

• https://open.alipay.com/portal/forum/post/126001176