手寫資料庫連線池

?資料庫連線池專案

一、專案意義

在設計前先了解一下資料庫連線池的作用：

除了在伺服器端增加快取伺服器快取常用的資料 之外（例如redis），還可以增加連線池，來提高MySQL Server的訪問效率，在高併發情況下，大量的 TCP三次握手、MySQL Server連線認證、MySQL Server關閉連線回收資源和TCP四次揮手所耗費的 效能時間也是很明顯的，增加連線池就是為了減少這一部分的效能損耗。

二、環境配置

MySQ資料庫程式設計環境配置：

在win10的vs專案中用C/C++客戶端開發包，vs做如下配置：

1.右鍵專案屬性 - C/C++ - 常規 - 附加包含目錄，填寫mysql.h標頭檔案的路徑

2.右鍵專案屬性 - 連結器 - 常規 - 附加庫目錄，填寫libmysql.lib的路徑

3.右鍵專案屬性 - 連結器 - 輸入 - 附加依賴項，填寫libmysql.lib庫的名字

4.把libmysql.dll動態連結庫（Linux下字尾名是.so庫）放在工程目錄下；

如果執行資料庫連線檔案出現：

說明系統環境變數中沒有配置MySQL環境，找不到動態連結，解決方案：在系統環境變數新增MySQL的bin資料夾路徑； 也可以在 右鍵專案屬性 - 除錯 - 環境，填寫 PATH=自己MySQL的bin資料夾路徑；

三、專案設計

所需要實現的資料庫連線池功能：連線池有連線數量的起始值和閾值；連線池可以動態自動生成和回收連線池裡面的資源；連線池和資料庫資訊等分離，達到複用簡單的效果；

①設計一個資料庫的的基本操作類

• 封裝資料庫的連線、增刪改查操作；
• 給該基本操作類加上存活時間相關的屬性和方法；

②資料庫連線池類

• 因為是一個池對應多個資源（物件）的關係，我們也只需要一個池，設計成單例模式；

• 儲存含有基本操作類一群物件資源；

• 有資源的初始化、增加、釋放操作（釋放和存活時間相關）

四、詳細程式碼

程式碼結構：

①public.h： 該連線池的全域性日誌輸出，列印一些錯誤在log中；

#pragma once
/*
* 定義巨集等全域性定義
*/

#define LOG(str) \
	cout << __FILE__ << ":" << __LINE__ << " " << \
	__TIMESTAMP__ << " : " << str << endl;

②mysql.ini：mysql的詳細資訊

#資料庫連線池的配置檔案,和巨集定義一樣，注意行後面不要有空格
ip=127.0.0.1
port=3306
username=root
password=root
dbname=chat
initSize=10
maxSize=1024
#最大空閒時間預設秒
maxIdleTime=60
#連線超時時間單位是毫秒
connectionTimeout=100

③Connection.h 和 Connection.cpp ：資料庫操作類 標頭檔案和實現；

Connection.h：

using namespace std;
#include "public.h"

// 資料庫操作類
class Connection
{
public:
	// 初始化資料庫連線
	Connection();
	// 釋放資料庫連線資源
	~Connection();
	// 連線資料庫
	bool connect(string ip, unsigned short port, string user, string password, string dbname);
	// 更新操作 insert、delete、update
	bool update(string sql);
	// 查詢操作:select;
	MYSQL_RES* query(string sql);

	//重新整理一下連線的起始空閒時間點
	void refreshAliveTime() { _alivetime = clock(); }//clock()函式當下時間
	// 返回存活的時間
	clock_t getAliveTime() { return clock() - _alivetime; }

private:
	MYSQL* _conn; // 表示和MySQL Server的一條連線
	clock_t _alivetime;// 記錄進入空閒狀態後的存活時間
};

Connection.cpp：

#include "Connection.h"
#include "public.h"

// 初始化資料庫連線
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 != nullptr;
}
// 更新操作 insert、delete、update
bool Connection::update(string sql)
{
	if (mysql_query(_conn, sql.c_str()))//如果查詢成功，返回0。如果出現錯誤，返回非0值。
	{
		LOG("更新失敗:" + sql);
		return false;
	}
	return true;
}
// 查詢操作:select;
MYSQL_RES* Connection::query(string sql)
{
	if (mysql_query(_conn, sql.c_str()))
	{
		LOG("查詢失敗:" + sql);
		return nullptr;
	}
	return mysql_use_result(_conn);
}

④MySQLConnectionPool.h和MySQLConnectionPool.cpp：連線池類標頭檔案和實現

MySQLConnectionPool.h：

#pragma once
#include <iostream>
#include <string>
#include <queue>
#include <mutex>
#include <condition_variable>
#include <atomic>
#include <thread>
#include <memory>
#include <functional>
#include "Connection.h"

using namespace std;
/*
* 實現連線池模組
*/
class ConnectionPool
{
public:
	//獲取連線池物件例項
	static ConnectionPool* getConnectionPool();
	// 消費者執行緒函式：給使用者連線，歸還時放回連線池
	shared_ptr<Connection> getConnection();

private:
	//單例#1 建構函式私有化
	ConnectionPool(); 

	//從配置檔案中載入配置
	bool loadConfigFile();

	//生產者執行緒函式：執行獨立的執行緒中，負責生產新連線,放在類內方便訪問成員變數
	void produceConnectionTask();

	//回收執行緒函式：掃描超過maxIdleTime時間的空閒連線，進行多餘的連線回收
	void scannerConnectionTask();

	string _ip;// mysql ip
	unsigned short _port; //mysql 埠 預設3306
	string _username;// mysql 使用者名稱；
	string _password;// mysql登入祕密
	string _dbname; // 資料庫名稱
	int _initSize;// 連線池的初始連線量
	int _maxSize;// 連線池的最大連線量
	int _maxIdleTime; //連線池最大空閒時間
	int _connectionTimeout;//連線池獲取連線的超時時間

	queue<Connection*> _connectionQue; //儲存mysql連線的佇列，必須是執行緒安全的；
	mutex _queueMutex;//維護連線佇列的執行緒安全互斥鎖
	atomic_int _connectionCnt;// 記錄連線所建立的connection連線的總數量，考慮了連線生產消費數量變化的執行緒安全問題
	condition_variable cv; //設定條件變數，用於連線 生產執行緒和消費執行緒的通訊
};

MySQLConnectionPool.cpp：

// MySQlConnectionPool.cpp : 此檔案包含 "main" 函式。程式執行將在此處開始並結束。
//避免重複包含標頭檔案
#ifndef __COMPLEX__
#define __COMPLEX__

#include <iostream>
#include <string>
#include "MySQLConnectionPool.h"
#include "public.h"

#endif;
// 執行緒安全的懶漢單例函式介面
ConnectionPool* ConnectionPool::getConnectionPool()
{
	static ConnectionPool pool; // 靜態變數實現lock和unlock,拿單例的執行緒池；
	return &pool;
}

// 從配置檔案中載入配置項
bool ConnectionPool::loadConfigFile()
{
	FILE* pf = fopen("mysql.ini", "r");
	if (pf == nullptr)
	{
		LOG("mysql.ini file is not exist!");
		return false;
	}

	while (!feof(pf))//末尾查一下
	{
		char line[1024] = { 0 };
		fgets(line, 1024, pf);
		string str = line;
		int idx = str.find('=', 0);//找出第一個出現=號的下標
		if (idx == -1)//找不到，無效配置項
		{
			continue;
		}

		//實際中是由\n結尾的，password=root\n
		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 = atoi(value.c_str());
		else if (key == "username") _username = value;
		else if (key == "password") _password = value;
		else if (key == "dbname") _dbname = value;
		else if (key == "initSize") _initSize = atoi(value.c_str());
		else if (key == "maxSize") _maxSize = atoi(value.c_str());
		else if (key == "maxIdleTime") _maxIdleTime = atoi(value.c_str());
		else if (key == "connectionTimeout") _connectionTimeout = atoi(value.c_str());

	}
}

// 連線池的構造
ConnectionPool::ConnectionPool()
{
	//載入配置項
	if (!loadConfigFile())
	{
		return;
	}

	//建立初始數量的連線
	for (int i = 0; i < _initSize; ++i)
	{
		Connection* p = new Connection();
		p->connect(_ip, _port, _username, _password, _dbname);
		p->refreshAliveTime();// 重新整理一下開始空閒起始時間；
		_connectionQue.push(p);
		_connectionCnt++;
	}

	//需要啟動一個新執行緒，作為連線的生產者（生產者執行緒） 
	//c++的執行緒函式在linux裡面底層也是pthread_creat,需要傳入c介面，所以傳入類方法需要繫結
	thread produce(std::bind(&ConnectionPool::produceConnectionTask, this));
	produce.detach();

	//啟動一個新的定時執行緒，掃描超過maxIdleTime時間的空閒連線，進行多餘的連線回收
	thread scanner(std::bind(&ConnectionPool::scannerConnectionTask, this));
	scanner.detach();
}

//生產者執行緒：執行獨立的執行緒中，負責生產新連線,放在類內方便訪問成員變數
void ConnectionPool::produceConnectionTask()
{
	for (;;)
	{
		unique_lock<mutex> lock(_queueMutex);
		while (!_connectionQue.empty())
		{
			cv.wait(lock); //佇列不為空，此處生產執行緒進入等待狀態,釋放鎖
		}

		//可以生產新連線，建立新連線
		if (_connectionCnt < _maxSize) { 
			Connection* p = new Connection();
			p->connect(_ip, _port, _username, _password, _dbname);
			p->refreshAliveTime();// 重新整理一下開始空閒起始時間；		
			_connectionQue.push(p);
			_connectionCnt++;
		}

		//通知消費者執行緒可以消費連線了
		cv.notify_all();
	}
}

// 消費者執行緒：給使用者連線，從連線池中獲取一個可用的空閒連線
shared_ptr<Connection> ConnectionPool::getConnection()
{
	unique_lock<mutex> lock(_queueMutex);
	//if (_connectionQue.empty())//空的就讓生產者生產
	//{
	//	//不可以用sleep，sleep是直接睡，而wait_for是被通知就可以馬上繼續走
	//	cv.wait_for(lock, chrono::milliseconds(_connectionTimeout));//毫秒，超過時間沒有被喚醒的話也會出來
	//	if (_connectionQue.empty())
	//	{
	//		LOG("獲取空閒連線超時了!!!獲取連線失敗");
	//		return nullptr;
	//	}
	//}

	//上述沒有考慮好，有可能等待過程中是被喚醒的，但是拿鎖慢，還是被拿走了鎖
	//優化一下：
	while (_connectionQue.empty())
	{
		if (cv_status::timeout == cv.wait_for(lock, chrono::milliseconds(_connectionTimeout)))
		{
			//是真的超時了， 並且連線池為空
			if (_connectionQue.empty())
			{
				LOG("獲取空閒連線超時了!!!獲取連線失敗");
				return nullptr;
			}			
		}
	}

	//有連線在池子裡
	/*
	shared_ptr智慧指標析構時，預設會呼叫connection解構函式，connection就會被close
	這裡就需要自定義share_ptr的釋放資源方式：把connection直接歸還到_connectionQue中；
	*/
	shared_ptr<Connection> sp(_connectionQue.front(),
		[&](Connection* pcon) {
			//這裡是在伺服器(多執行緒)消費者執行緒中呼叫的，涉及了共享資料，所以一定要考慮佇列的執行緒安全操作
			unique_lock<mutex> lock(_queueMutex);
			pcon->refreshAliveTime();// 重新整理一下開始空閒起始時間；
			_connectionQue.push(pcon);
		});
	_connectionQue.pop();
	//if (_connectionQue.empty()) //這樣寫也可以
	//{
	//	cv.notify_all();//消費完連線後發現佇列為空，通知生產者執行緒；
	//}
	cv.notify_all();//消費完連線後，通知生產者執行緒檢查執行緒池是否為空；
	return sp;
}

//回收執行緒函式：掃描超過maxIdleTime時間的空閒連線，進行多餘的連線回收
void ConnectionPool::scannerConnectionTask()
{
	for (;;)
	{
		//通過sleep模擬定時效果
		this_thread::sleep_for(chrono::seconds(_maxIdleTime));
	
		// 掃描整個佇列，釋放多餘連線
		unique_lock<mutex> lock(_queueMutex);
		while (_connectionCnt > _initSize)
		{
			Connection* p = _connectionQue.front();
			//這裡都釋放？應該釋放大於initSize以上的？
			if (p->getAliveTime() > _maxIdleTime * 1000) //##隊頭的空閒時間是最長的，只用看隊頭就行
			{
				_connectionQue.pop();
				_connectionCnt--;
				delete p;//呼叫connectin解構函式
			}
			else
			{
				break;//隊頭都小於，後面肯定小；
			}
		}
	}
}

五、程式碼測試

進行壓力測試對比一下使用連線池和不使用連線池的效果；

測試程式碼：main函式手動測試

#include <iostream>
#include <thread>
#include "Connection.h"
#include "MySQLConnectionPool.h"


using namespace std;


int main()
{
	/*
	 * 資料庫測試
	*/
	//Connection conn;
	//char sql[1024] = { 0 };
	//sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//conn.connect("127.0.0.1", 3306, "root", "root", "chat");
	//conn.update(sql);

	//壓力測試：
	// 
	//①不用連線池，單執行緒,更改資料1000、5000、10000
	clock_t begin = clock();
	for (int i = 0; i < 1000; ++i) {
		Connection conn;
		char sql[1024] = { 0 };
		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
		conn.connect("127.0.0.1", 3306, "root", "root", "chat");
		conn.update(sql);
	}
	clock_t end = clock();
	cout << end - begin << "ms" << endl;

	//②用連線池單執行緒，更改資料1000、5000、10000
	//clock_t begin = clock();
	//ConnectionPool *cp = ConnectionPool::getConnectionPool();
	//for (int i = 0; i < 5000; ++i) {
	//	shared_ptr<Connection> sp= cp->getConnection();
	//	char sql[1024] = { 0 };
	//	sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//	sp->update(sql);
	//}
	//clock_t end = clock();
	//cout << end - begin << "ms" << endl;

	//③不用連線池的4執行緒
	//不能在多執行緒中同時連線資料庫，是非法的,需要先在外面宣告連線
	//Connection conn;
	//conn.connect("127.0.0.1", 3306, "root", "root", "chat");
	//clock_t begin = clock();
	//thread t1([]() {
	//	for (int i = 0; i < 250; ++i) {
	//		Connection conn;
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		conn.connect("127.0.0.1", 3306, "root", "root", "chat");
	//		conn.update(sql);
	//	}
	//	});
	//thread t2([]() {
	//	for (int i = 0; i < 250; ++i) {
	//		Connection conn;
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		conn.connect("127.0.0.1", 3306, "root", "root", "chat");
	//		conn.update(sql);
	//	}
	//	});
	//thread t3([]() {
	//	for (int i = 0; i < 250; ++i) {
	//		Connection conn;
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		conn.connect("127.0.0.1", 3306, "root", "root", "chat");
	//		conn.update(sql);
	//	}
	//	});
	//thread t4([]() {
	//	for (int i = 0; i < 250; ++i) {
	//		Connection conn;
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		conn.connect("127.0.0.1", 3306, "root", "root", "chat");
	//		conn.update(sql);
	//	}
	//	});
	//t1.join();
	//t2.join();
	//t3.join();
	//t4.join();
	//clock_t end = clock();
	//cout << end - begin << "ms" << endl;

	//④用連線池的4執行緒
	//clock_t begin = clock();
	//thread	t1([]() {
	//	ConnectionPool* cp = ConnectionPool::getConnectionPool();
	//	for (int i = 0; i < 250; ++i) {
	//		shared_ptr<Connection> sp = cp->getConnection();
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		sp->update(sql);
	//	}
	//	});
	//thread	t2([]() {
	//	ConnectionPool* cp = ConnectionPool::getConnectionPool();
	//	for (int i = 0; i < 250; ++i) {
	//		shared_ptr<Connection> sp = cp->getConnection();
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		sp->update(sql);
	//	}
	//	});
	//thread	t3([]() {
	//	ConnectionPool* cp = ConnectionPool::getConnectionPool();
	//	for (int i = 0; i < 250; ++i) {
	//		shared_ptr<Connection> sp = cp->getConnection();
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		sp->update(sql);
	//	}
	//	});
	//thread	t4([]() {
	//	ConnectionPool* cp = ConnectionPool::getConnectionPool();
	//	for (int i = 0; i < 250; ++i) {
	//		shared_ptr<Connection> sp = cp->getConnection();
	//		char sql[1024] = { 0 };
	//		sprintf(sql, "insert into user(name, age, sex) values ('%s', %d, '%s')", "zhang san", 20, "male");
	//		sp->update(sql);
	//	}
	//	});
	//t1.join();
	//t2.join();
	//t3.join();
	//t4.join();
	//clock_t end = clock();
	//cout << end - begin << "ms" << endl;

	return 0;
}

剛開始連線速度，插入速度極慢的原因：

是因為 mysql8.0 一些設定是預設開啟的(5.7 是預設關閉的)，而這些設定可能會嚴重影響資料庫效能

執行以下優化：

• 在檔案 my.ini 或 /etc/my.cnf 中，修改 mysqld 節點的內容，關閉 log-bin 功能；
• [優化]https://blog.csdn.net/weixin_42122881/article/details/113941793)

最後我還是換成了5.7的版本進行測試：

資料量	未使用連線池所耗時間	使用連線池所耗時間
1000	單執行緒：1886ms 四執行緒：495ms	單執行緒：1078ms 四執行緒：406ms
5000	單執行緒：10032ms 四執行緒：2368ms	單執行緒：5328ms 四執行緒：2033ms
10000	單執行緒：19407ms 四執行緒：4579ms	單執行緒：10532ms四執行緒：4041ms

鍛鍊的技術點：MySQL資料庫程式設計、單例模式、queue佇列容器、C++11多執行緒程式設計、執行緒互斥、執行緒同步通訊和 unique_lock、基於CAS的原子整形、智慧指標shared_ptr、lambda表示式、生產者-消費者執行緒模型；