最近在学习webserver项目,前置知识有数据库连接池,其实跟线程池是类似的东西,我们提前存储连接,然后每次有连接请求再进行分配就可以了,这样就减少了每次关闭连接所涉及到的资源的消耗
我们首先来封装一个数据库操作模块
MysqlConn.h
#ifndef MYSQLCONN_H
#define MYSQLCONN_H
#pragma once
#include <iostream>
#include <mysql/mysql.h>
#include <cstring>
#include <chrono>
using namespace std;
using namespace chrono;
class MysqlConn {
public:
//初始化数据库
MysqlConn();
~MysqlConn();
//连接数据库
bool connect(string user, string passwd, string dbName, string ip, unsigned short port = 3306);
//更新数据库 insert,updata,delete
bool updata(string sql);
//查询数据库
bool query(string sql);
//遍历查询得到的结果集
bool next();
//得到结果集中的字段值
string value(int index);
//事务操作
bool transaction();
//提交事务
bool commit();
//事务回滚
bool rollback();
//刷新起始的空闲时间
void refreshAliveTime();
//计算连接的存活总时长
long long getAliveTime();
private:
void freeResult();
MYSQL* m_conn = nullptr;
MYSQL_RES* m_result = nullptr;
MYSQL_ROW m_row = nullptr;
steady_clock::time_point m_alivetime;
};
#endif
MysqlConn.cpp
#include "MysqlConn.h"
#include <cstring>
#include <iostream>
using namespace std;
MysqlConn::MysqlConn() {
m_conn = mysql_init(nullptr);
mysql_set_character_set(m_conn, "utf8");
}
MysqlConn::~MysqlConn() {
if (m_conn != nullptr) {
mysql_close(m_conn);
}
freeResult();
}
bool MysqlConn::connect(string user, string passwd, string dbName, string ip, unsigned short port) {
MYSQL* ptr = mysql_real_connect(m_conn, ip.c_str(), user.c_str(), passwd.c_str(), dbName.c_str(), port, nullptr, 0);
return ptr != nullptr;
}
bool MysqlConn::updata(string sql) {
if (mysql_query(m_conn, sql.c_str()))
return false;
return true;
}
bool MysqlConn::query(string sql) {
freeResult();
if (mysql_query(m_conn, sql.c_str())) {
return false;
}
m_result = mysql_store_result(m_conn);
return true;
}
bool MysqlConn::next() {
if (m_result != nullptr) {
m_row = mysql_fetch_row(m_result);
return m_row!= nullptr;
}
return false;
}
string MysqlConn::value(int index) {
int rowCount = mysql_num_fields(m_result);
if (index >= rowCount || index < 0) {
return string();
}
char *val = m_row[index];
unsigned long length = mysql_fetch_lengths(m_result)[index];
return string(val, length); //防止以\0为结束符号
}
bool MysqlConn::transaction() {
return mysql_autocommit(m_conn, false); //设置手动提交
}
bool MysqlConn::commit() {
return mysql_commit(m_conn);
}
bool MysqlConn::rollback() {
return mysql_rollback(m_conn);
}
void MysqlConn::refreshAliveTime() {
m_alivetime = steady_clock::now();
}
long long MysqlConn::getAliveTime() {
nanoseconds res = steady_clock::now() - m_alivetime;
milliseconds millsec = duration_cast<milliseconds>(res); //高精度向低精度转换
return millsec.count();
}
void MysqlConn::freeResult() {
if (m_result) {
mysql_free_result(m_result);
m_result = nullptr;
}
return;
}
数据库的连接信息我们是存储在 j s o n json json文件里面的,需要有需要也可能封装为 x m l xml xml文件
然后我们就需要实现连接池的封装
整个连接池是基于生产者-消费者线程模型来设计,使用了线程间的同步通信机制条件变量和互斥锁,因为实现的功能比较简单,所以我们只采用了同一种条件变量
因为连接池只需要一个实例,所以我们采用单例模式设计
每次消费者获取连接的时候,我们用智能指针来管理,这样做的好处就是我们可以来定义连接释放后的操作,把连接归还给线程池)
ConnectionPoll.h
#ifndef CONNECTIONPOLL_H
#define CONNECTIONPOLL_H
#pragma once
#include <iostream>
#include <mysql/mysql.h>
#include "MysqlConn.h"
#include <queue>
#include <mutex>
#include <condition_variable>
using namespace std;
using namespace std;
class ConnectionPoll {
public:
static ConnectionPoll* getConnectionPoll();
ConnectionPoll(const ConnectionPoll& obj) = delete;
ConnectionPoll& operator=(const ConnectionPoll& obj) = delete;
shared_ptr<MysqlConn> getConnection();
~ConnectionPoll();
private:
ConnectionPoll();
bool parseJsonFile();
void produceConnection();
void recycleConnection();
void addConection();
queue<MysqlConn*> m_connectionQ;
string m_ip;
string m_user;
string m_passwd;
string m_dbName;
unsigned short m_port;
int m_minSize;
int m_maxSize;
int m_timeout;
int m_maxIDleTime;
mutex m_mutexQ;
condition_variable m_cond;
};
#endif
ConnectionPoll.cpp
#include "ConnectionPoll.h"
#include <cstring>
#include <json/json.h>
#include <iostream>
#include <fstream>
#include <thread>
using namespace Json;
using namespace std;
ConnectionPoll::ConnectionPoll() {
//加载json文件
if (!parseJsonFile()) {
return;
}
for (int i = 0; i < m_minSize; i++) {
addConection();
}
thread prodecer(&ConnectionPoll::produceConnection, this);
thread recycler(&ConnectionPoll::recycleConnection, this);
prodecer.detach();
recycler.detach();
}
ConnectionPoll::~ConnectionPoll() {
while (m_connectionQ.size()) {
MysqlConn*conn = m_connectionQ.front();
m_connectionQ.pop();
delete conn;
}
}
shared_ptr<MysqlConn> ConnectionPoll::getConnection() {
unique_lock<mutex> locker(m_mutexQ);
while (m_connectionQ.empty()) {
if(cv_status::timeout == (m_cond.wait_for(locker, chrono::milliseconds(m_timeout)))) {
if (m_connectionQ.empty()) {
continue;
}
}
}
shared_ptr<MysqlConn> connptr(m_connectionQ.front(), [this](MysqlConn* conn) {
lock_guard<mutex> locker(m_mutexQ);
conn->refreshAliveTime();
m_connectionQ.push(conn);
}); //归还MysqlConn,操作析构函数
m_connectionQ.pop();
m_cond.notify_all();
return connptr;
}
void ConnectionPoll::addConection() {
if (m_connectionQ.size() >= m_maxSize) return;
MysqlConn* conn = new MysqlConn();
conn->connect(m_user, m_passwd, m_dbName, m_ip, m_port);
conn->refreshAliveTime();
m_connectionQ.push(conn);
}
ConnectionPoll *ConnectionPoll::getConnectionPoll() {
static ConnectionPoll poll; //单例模式,只有一个实例,只会创建一次
return &poll;
}
bool ConnectionPoll::parseJsonFile() {
ifstream ifs("../dbconf.json");
Reader rd;
Value root;
rd.parse(ifs, root);
if (root.isObject()) {
m_ip = root["ip"].asString();
m_user = root["userName"].asString();
m_passwd = root["password"].asString();
m_dbName = root["dbName"].asString();
m_port = root["port"].asInt();
m_minSize = root["minSize"].asInt();
m_maxSize = root["maxSize"].asInt();
m_timeout = root["timeout"].asInt();
m_maxIDleTime = root["maxIDleTime"].asInt();
return true;
}
return false;
}
void ConnectionPoll::produceConnection() {
while (true) {
unique_lock<mutex> lock(m_mutexQ);
while(m_connectionQ.size() >= m_minSize) { //条件判断最好使用while循环而不是if判断
m_cond.wait(lock);
}
addConection();
m_cond.notify_all();
}
}
void ConnectionPoll::recycleConnection() {
while (true) {
this_thread::sleep_for(chrono::milliseconds(500));
lock_guard<mutex> locker(m_mutexQ);
while(m_connectionQ.size() > m_minSize) { //条件判断最好使用while循环而不是if判断
MysqlConn* conn = m_connectionQ.front();
if (conn->getAliveTime() >= m_maxIDleTime) {
m_connectionQ.pop();
delete conn;
} else {
break;
}
}
}
}
测试函数
#include "MysqlConn.h"
#include "ConnectionPoll.h"
#include <iostream>
#include <unistd.h>
#include <thread>
using namespace std;
int query() {
MysqlConn *conn = new MysqlConn();
conn->connect();
string sql = "";
bool flag = conn->updata(sql);
sql = "select * from collegeid";
conn->query(sql);
return 0;
}
void op1(int begin, int end) {
for (int i = begin; i < end; i++) {
MysqlConn *conn = new MysqlConn();
conn->connect();
string sql = "";
conn->updata(sql);
delete conn;
}
}
void op2(ConnectionPoll* pool, int begin, int end) {
for (int i = begin; i < end; i++) {
shared_ptr<MysqlConn> conn = pool->getConnection();
string sql = "";
conn->updata(sql);
}
}
void test1() {
#if 0
steady_clock::time_point begin = steady_clock::now();
op1(0, 5000);
steady_clock::time_point end = steady_clock::now();
auto length = end - begin;
cout << "非连接池 单线程 用时" << length.count() << "纳秒," << length.count()/ 1000000 << "毫秒" << endl;
#else
ConnectionPoll *pool = ConnectionPoll::getConnectionPoll();
steady_clock::time_point begin = steady_clock::now();
op2(pool, 0, 5000);
steady_clock::time_point end = steady_clock::now();
auto length = end - begin;
cout << "连接池 单线程 用时" << length.count() << "纳秒," << length.count()/ 1000000 << "毫秒" << endl;
#endif
}
void test2() {
#if 1
MysqlConn* conn = new MysqlConn();
conn->connect();
steady_clock::time_point begin = steady_clock::now();
thread t1(op1, 0, 1000);
thread t2(op1, 1000, 2000);
thread t3(op1, 2000, 3000);
thread t4(op1, 3000, 4000);
thread t5(op1, 4000, 5000);
t1.join();
t2.join();
t3.join();
t4.join();
t5.join();
steady_clock::time_point end = steady_clock::now();
auto length = end - begin;
cout << "非连接池 多线程 用时" << length.count() << "纳秒," << length.count()/ 1000000 << "毫秒" << endl;
#else
ConnectionPoll *pool = ConnectionPoll::getConnectionPoll();
steady_clock::time_point begin = steady_clock::now();
thread t1(op2, pool, 0, 1000);
thread t2(op2, pool, 1000, 2000);
thread t3(op2, pool, 2000, 3000);
thread t4(op2, pool, 3000, 4000);
thread t5(op2, pool, 4000, 5000);
t1.join();
t2.join();
t3.join();
t4.join();
t5.join();
steady_clock::time_point end = steady_clock::now();
auto length = end - begin;
cout << "连接池 多线程 用时" << length.count() << "纳秒," << length.count()/ 1000000 << "毫秒" << endl;
#endif
}
int main() {
test1();
test2();
}
测试结构
非连接池 单线程 用时6528492979纳秒,6528毫秒
连接池 单线程 用时3820757698纳秒,3820毫秒
非连接池 多线程 用时1601066109纳秒,1601毫秒
连接池 多线程 用时1102565644纳秒,1102毫秒
可以看出来线程池的效率还是比较可观的,