C++实现数据库连接池

最近在学习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;
}

数据库的连接信息我们是存储在$json$文件里面的，需要有需要也可能封装为$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毫秒

可以看出来线程池的效率还是比较可观的，