连接池的原理及设计与实现
什么是连接池？
为了加快和数据库交互而设计出来的一种池式结构，避免每次申请数据库重新连接而进行的重复操作。加快数据库的交互。
连接池原理
一次性请求多个数据库的连接进行管理，有业务需求时进行分配。
我设计的连接池
封装好对数据库的单次连接操作，以及可能进行的各种交互。使用池，预先申请DB_MIN_COUNT（我设置为2）次连接，连接满了后增加连接（增加 = 当前连接数 * DB_ADD_RATE，我设置为1），直到DB_MAX_COUNT（不会超过，我设置为100）。每当调用释放时（逻辑释放），判断是否低于删除点（连接占用比，DB_DEL_POINT，我设置为0.25），低于则释放连接（删除 = 当前连接数 * DB_DEL_RATE，我设置为0.5），直到DB_MIN_COUNT（不会低于）。
代码实现
mysql_pool.h
/******************************************************************************
*
* Copyright (C), 2001-2005, Huawei Tech. Co., Ltd.
*
*******************************************************************************
* File Name : mysql_pool.h
* Version : Initial Draft
* Author : sst
* Created : 2021/7/30
* Last Modified :
* Description : mysql_pool.cpp header file
* Function List :
*
*
* History:
*
* 1. Date : 2021/7/30
* Author : sst
* Modification : Created file
*
******************************************************************************/
#ifndef __MYSQL_POOL_H__
#define __MYSQL_POOL_H__
/*==============================================*
* include header files *
*----------------------------------------------*/
#include <iostream>
#include <string>
#include <list>
#include <map>
#include <mutex>
#include <chrono>
#include <condition_variable>
using namespace std;
#include <mysql/mysql.h>
#include <stdio.h>
#include <stdint.h>
#include <string.h>
/*==============================================*
* constants or macros define *
*----------------------------------------------*/
#define DB_MIN_COUNT 2
#define DB_MAX_COUNT 100
#define DB_DEL_POINT 0.25// delete connect when used / sum < it
#define DB_ADD_RATE 1 // add count = current count * it
#define DB_DEL_RATE 0.5 // del count = current count * it
#ifdef INFO_FLAG
#define INFO_PATH "infomation.txt"
#define INFO_APPLY "apply mysql connect"
#define INFO_RELEASE "release mysql connect"
#define INFO_ADD "add mysql connect"
#define INFO_DEL "del mysql connect"
#endif
/*==============================================*
* project-wide global variables *
*----------------------------------------------*/
/*==============================================*
* routines' or functions' implementations *
*----------------------------------------------*/
class CResultSet {
public:
CResultSet(MYSQL_RES *res);
~CResultSet();
bool Next(); // 获取下一行结果
int GetInt(const char *key); // 按照列名获取整形
char* GetString(const char *key); // 按照列名获取字符串
private:
int _GetIndex(const char *key); // 按照列名得到对应的列号
MYSQL_RES* m_res; // 结果对象
MYSQL_ROW m_row; // 一行
map<string, int> m_key_map; // key:列名，value:列号
};
class CDBPool;
class CMysqlConn {
public:
CMysqlConn(CDBPool *db_pool);
~CMysqlConn();
int Init(); // 初始化
bool ExecuteCreate(const char *sql_query); // 创建表
bool ExecuteDrop(const char *sql_query); // 删除表
CResultSet* ExecuteSelect(const char *sql_query); // 查询
int ExecuteDelete(const char *sql_query); // 删除, 返回影响行数
int ExecuteUpdate(const char *sql_query); // 更新, 返回影响行数
uint32_t GetInsertId(); // 返回上一步insert产生的id
bool StartTransaction(); // 开启事务
bool Commit(); // 提交事务
bool Rollback(); // 回滚事务
const char* GetPoolName(); // 获取连接池名
MYSQL* GetMysql(); // 返回自身连接
private:
inline int _Execute(const char *sql_query); // 执行sql命令，返回影响行数
CDBPool* m_db_pool; // 对应的连接池
MYSQL* m_mysql; // mysql连接
};
class CInfo {
public:
CInfo();
virtual ~CInfo();
void InfoWrite(CDBPool *db_pool, const char* flag, void *data, int size);
private:
void _InfoClose();
void _InfoOpen(const char *info_path);
mutex m_mutex_info;
FILE* m_info_fd;
};
class CDBPool {
public:
CDBPool(const char* pool_name, const char* db_server_ip, int db_server_port,
const char* username, const char* password, const char* db_name,
int db_max_count = DB_MAX_COUNT, int db_min_count = DB_MIN_COUNT);
virtual ~CDBPool();
CMysqlConn* GetMysqlConn(const int timeout_ms = -1); // 获取连接资源
CMysqlConn* DelMysqlConn(CMysqlConn* pConn); // 归还连接资源
const char* GetPoolName();
const char* GetDBServerIP();
uint16_t GetDBServerPort();
const char* GetUsername();
const char* GetPasswrod();
const char* GetDBName();
int GetCurrentCount();
int GetFreeCount();
int GetUsedCount();
private:
int _CreateMysqlConn(int count);// 创建mysql连接
int _FreeMysqlConn(int count); // 删除mysql连接（仅空闲）
string m_pool_name; // 连接池名称
string m_db_server_ip; // 数据库ip
uint16_t m_db_server_port; // 数据库端口
string m_username; // 用户名
string m_password; // 用户密码
string m_db_name; // db名称
int m_db_current_count; // 当前启用的连接数量
int m_db_max_count; // 最大连接数量
list<CMysqlConn*> m_free_list; // 空闲的连接
list<CMysqlConn*> m_used_list; // 已经被请求的连接
mutex m_mutex; // C++11互斥锁
condition_variable m_cond_var; // C++11条件变量
bool m_abort_request = false;
CInfo m_info;
};
#endif /* __MYSQL_POOL_H__ */
mysql_pool.cpp
#include "mysql_pool.h"
// class CResultSet
// 初始化map，将列名和列号对应写入。
CResultSet::CResultSet(MYSQL_RES *res) {
m_res = res;
int num_fields = mysql_num_fields(m_res);
MYSQL_FIELD *fields = mysql_fetch_fields(m_res);
for(int i = 0; i < num_fields; ++i) {
m_key_map.insert(make_pair(fields[i].name, i));
}
}
CResultSet::~CResultSet() {
if(m_res) {
mysql_free_result(m_res);
m_res = nullptr;
}
}
// 从结果集中取出一行
bool CResultSet::Next() {
m_row = mysql_fetch_row(m_res);
if(m_row) {
return true;
}
return false;
}
// class CMysqlConn
// 存在列名返回对应列号字符串，不存在返回NULL
int CResultSet::GetInt(const char *key) {
int idx = _GetIndex(key);
if(idx == -1) {
return 0;
}
return atoi(m_row[idx]);
}
// 存在列名返回对应列号字符串，不存在返回NULL
char* CResultSet::GetString(const char *key) {
int idx = _GetIndex(key);
if(idx == -1) {
return nullptr;
}
return m_row[idx];
}
// 存在列名返回对应列号，不存在返回-1
int CResultSet::_GetIndex(const char *key) {
map<string, int>::iterator it = m_key_map.find(key);
if(it == m_key_map.end()) {
return -1;
}
return it->second;
}
CMysqlConn::CMysqlConn(CDBPool *db_pool) {
m_db_pool = db_pool;
m_mysql = nullptr;
}
CMysqlConn::~CMysqlConn() {
if(m_mysql) {
mysql_close(m_mysql);
}
}
int CMysqlConn::Init() {
m_mysql = mysql_init(nullptr);
if(!m_mysql) {
printf("mysql_init failed\n");
return -1;
}
my_bool reconnect = true;
mysql_options(m_mysql, MYSQL_OPT_RECONNECT, &reconnect); // 配合mysql_ping实现自动重连
mysql_options(m_mysql, MYSQL_SET_CHARSET_NAME, "utf8"); // utf8mb4和utf8区别
// ip 端口 用户名 密码 数据库名
if(!mysql_real_connect(m_mysql, m_db_pool->GetDBServerIP(), m_db_pool->GetUsername(),
m_db_pool->GetPasswrod(), m_db_pool->GetDBName(),
m_db_pool->GetDBServerPort(), nullptr, 0)) {
printf("mysql_real_connect failed: %s\n", mysql_error(m_mysql));
return -1;
}
return 0;
}
bool CMysqlConn::ExecuteCreate(const char *sql_query) {
return _Execute(sql_query) != -1;
}
bool CMysqlConn::ExecuteDrop(const char *sql_query) {
return _Execute(sql_query) != -1;
}
CResultSet *CMysqlConn::ExecuteSelect(const char *sql_query) {
mysql_ping(m_mysql);
if(mysql_real_query(m_mysql, sql_query, strlen(sql_query))) {
printf("mysql_real_query failed: %s, sql: %s\n", mysql_error(m_mysql), sql_query);
return nullptr;
}
MYSQL_RES *res = mysql_store_result(m_mysql);
if(!res) {
printf("mysql_store_result failed: %s\n", mysql_error(m_mysql));
return nullptr;
}
CResultSet *result_set = new CResultSet(res); // 存储到CResultSet
return result_set;
}
int CMysqlConn::ExecuteDelete(const char *sql_query) {
return _Execute(sql_query);
}
int CMysqlConn::ExecuteUpdate(const char *sql_query) {
return _Execute(sql_query);
}
uint32_t CMysqlConn::GetInsertId() {
return (uint32_t)mysql_insert_id(m_mysql);
}
bool CMysqlConn::StartTransaction() {
return _Execute("start transaction\n") != -1;
}
bool CMysqlConn::Commit() {
return _Execute("commit\n") != -1;
}
bool CMysqlConn::Rollback() {
return _Execute("rollback\n") != -1;
}
const char* CMysqlConn::GetPoolName() {
return m_db_pool->GetPoolName();
}
MYSQL* CMysqlConn::GetMysql() {
return m_mysql;
}
inline int CMysqlConn::_Execute(const char *sql_query) {
mysql_ping(m_mysql);
if(mysql_real_query(m_mysql, sql_query, strlen(sql_query))) {
printf("mysql_real_query failed: %s, sql: %s\n", mysql_error(m_mysql), sql_query);
return -1;
}
return mysql_affected_rows(m_mysql);
}
// class CMysqlConn
CDBPool::CDBPool(const char *pool_name, const char *db_server_ip, int db_server_port,
const char *username, const char *password, const char *db_name,
int db_max_count, int db_min_count) {
m_pool_name = pool_name;
m_db_server_ip = db_server_ip;
m_db_server_port = (uint16_t)db_server_port;
m_username = username;
m_password = password;
m_db_name = db_name;
m_db_max_count = db_max_count; // 最大连接数量
m_db_current_count = 0;
int ret = _CreateMysqlConn(db_min_count); // 创建最小连接数的连接
#ifdef INFO_FLAG
m_info.InfoWrite(this, INFO_ADD, nullptr, ret);
#endif
}
// 释放连接池
CDBPool::~CDBPool() {
lock_guard<mutex> lock(m_mutex);
m_abort_request = true; // 请求释放
m_cond_var.notify_all(); // 通知所有在等待的
for (auto it = m_free_list.begin(); it != m_free_list.end(); ++it) {
CMysqlConn *pConn = *it;
delete pConn;
}
m_free_list.clear();
}
CMysqlConn* CDBPool::GetMysqlConn(const int timeout_ms) {
unique_lock<mutex> lock(m_mutex);
if(m_abort_request) {
printf("have abort\n");
return nullptr;
}
if (m_free_list.empty()) {
if (m_db_current_count >= m_db_max_count) {
if(timeout_ms < 0) {
m_cond_var.wait(lock, [this] {
return (!m_free_list.empty()) | m_abort_request;
});
} else {
m_cond_var.wait_for(lock, chrono::milliseconds(timeout_ms), [this] {
return (!m_free_list.empty()) | m_abort_request;
});
if(m_free_list.empty()) { // timeout
return nullptr;
}
}
if(m_abort_request) {
printf("have aboort\n");
return nullptr;
}
} else {
int count = m_db_current_count * DB_ADD_RATE;
if(count + m_db_current_count > DB_MAX_COUNT) {
count = DB_MAX_COUNT - m_db_current_count;
}
int ret = _CreateMysqlConn(count);
if(!ret) return nullptr;
#ifdef INFO_FLAG
m_info.InfoWrite(this, INFO_ADD, nullptr, ret);
#endif
}
}
CMysqlConn *pConn = m_free_list.front(); // 获取连接
m_free_list.pop_front(); // STL 吐出连接，从空闲队列删除
m_used_list.push_back(pConn); // 加入到被使用队列
#ifdef INFO_FLAG
m_info.InfoWrite(this, INFO_APPLY, (void*)pConn, 1);
#endif
return pConn;
}
CMysqlConn* CDBPool::DelMysqlConn(CMysqlConn *pConn) {
lock_guard<mutex> lock(m_mutex);
auto it = m_free_list.begin();
for (; it != m_free_list.end() && *it != pConn; ++it);
if (it == m_free_list.end()) {
m_used_list.remove(pConn);
m_free_list.push_back(pConn);
m_cond_var.notify_one(); // 通知取队列
} else {
return nullptr;
}
if(1.0 * m_used_list.size() / m_db_current_count < DB_DEL_POINT) {
int count = m_db_current_count * DB_DEL_RATE;
if(m_db_current_count - count < DB_MIN_COUNT) {
count = m_db_current_count - DB_MIN_COUNT;
}
_FreeMysqlConn(count);
}
#ifdef INFO_FLAG
m_info.InfoWrite(this, INFO_RELEASE, (void*)pConn, 1);
#endif
return nullptr;
}
const char* CDBPool::GetPoolName() {
return m_pool_name.c_str();
}
const char* CDBPool::GetDBServerIP() {
return m_db_server_ip.c_str();
}
uint16_t CDBPool::GetDBServerPort() {
return m_db_server_port;
}
const char* CDBPool::GetUsername() {
return m_username.c_str();
}
const char* CDBPool::GetPasswrod() {
return m_password.c_str();
}
const char* CDBPool::GetDBName() {
return m_db_name.c_str();
}
int CDBPool::GetCurrentCount() {
return m_db_current_count;
}
int CDBPool::GetFreeCount() {
return m_free_list.size();
}
int CDBPool::GetUsedCount() {
return m_used_list.size();
}
int CDBPool::_CreateMysqlConn(int count) {
for (int i = 0; i < count; i++) {
CMysqlConn *pDBConn = new CMysqlConn(this);
int ret = pDBConn->Init();
if(ret) {
m_db_current_count += i; // 当前分配失败，相当于减一
delete pDBConn;
return i;
}
m_free_list.push_back(pDBConn);
}
m_db_current_count += count;
return count;
}
int CDBPool::_FreeMysqlConn(int count) {
for(int i = 0; i < count; ++i) {
delete m_free_list.back();
m_free_list.pop_back();
}
m_db_current_count -= count;
return 0;
}
#ifdef INFO_FLAG
CInfo::CInfo() {
_InfoOpen(INFO_PATH);
}
CInfo::~CInfo() {
_InfoClose();
}
void CInfo::_InfoOpen(const char *info_path) {
m_info_fd = fopen(info_path, "w");
if(m_info_fd == nullptr)
perror("fopen error : ");
}
void CInfo::_InfoClose() {
if(m_info_fd)
fclose(m_info_fd);
}
void CInfo::InfoWrite(CDBPool *db_pool, const char *flag, void *data, int size) {
char buffer[1024];
if(data != nullptr) {
sprintf(buffer, "%s, size : %d, mysql_conn : %lx\n",
flag, size, (unsigned long int)data);
} else {
sprintf(buffer, "%s, size : %d\n",
flag, size);
}
sprintf(buffer, "%spool name : %s, max count : %-3d, free count : %-3d, used count : %-3d\n\n",
buffer, db_pool->GetPoolName(), db_pool->GetCurrentCount(),
db_pool->GetFreeCount(), db_pool->GetUsedCount());
lock_guard<mutex> lock(m_mutex_info);
if(fwrite(buffer, strlen(buffer), 1, m_info_fd) == 0)
perror("write error : ");
}
#endif
test_project.cpp
#include <stdio.h>
#include <string.h>
#include <mysql/mysql.h>
#include <sys/time.h>
#include "mysql_pool.h"
// spend time = current time - old time
// day + hours + minute + second + millisecond + mincrosecond + info
void TimeCurrentSpend(struct timeval *time_old, char *info)
{
struct timeval now;
gettimeofday(&now, NULL);
if(now.tv_usec < time_old->tv_usec)
{
now.tv_sec--;
now.tv_usec += 1000000;
}
time_t sec = now.tv_sec - time_old->tv_sec;
char info_old[strlen(info) + 1];
strcpy(info_old, info);
sprintf(info, "%2ldd,%2ldh,%2ldm,%2lds,%4ldms,%4ldus : %s",
sec /8640, // day
(sec / 3600) % 24, // hours
(sec / 60) % 60, // minute
sec % 60, // second
(now.tv_usec - time_old->tv_usec) / 1000, // millisecond
(now.tv_usec - time_old->tv_usec) % 1000, // mincrosecond
info_old); // info
}
void WorkTime(void (*work)(void *args), void *args, const char *info) {
char time_buffer[1024];
struct timeval old;
gettimeofday(&old, NULL);
work(args);
sprintf(time_buffer, "%s", info);
TimeCurrentSpend(&old, time_buffer);
printf("%s", time_buffer);
}
void TestCreate(CMysqlConn *mysql_conn, bool show) {
char sql_query[1024];
sprintf(sql_query,
"CREATE TABLE `testDB`.`test` (\
`id` int NOT NULL AUTO_INCREMENT,\
`name` varchar(30) NOT NULL,\
`password` varchar(30) NOT NULL,\
PRIMARY KEY (`id`)\
);");
if(mysql_conn->ExecuteCreate(sql_query) && show) {
printf("create sql execute success!\n");
}
}
void TestDrop(CMysqlConn *mysql_conn, bool show) {
char sql_query[1024];
sprintf(sql_query, "drop table test;");
if(mysql_conn->ExecuteDrop(sql_query) && show) {
printf("drop sql execute success!\n");
}
}
void TestInsert(CMysqlConn *mysql_conn, bool show) {
char sql_query[1024];
int rows = 0;
string name, password;
for(char id = 'a'; id <= 'z'; ++id) {
name = string("") + id + id + id + id;
password = string("") + id + id + id + id;
sprintf(sql_query,
"insert into `testDB`.`test` (`name`, `password`) \
values ('%s', '%s');", name.c_str(), password.c_str());
rows += mysql_conn->ExecuteUpdate(sql_query);
}
if(rows && show) {
printf("insert sql execute affected rows : %d\n", rows);
}
}
void TestDelete(CMysqlConn *mysql_conn, bool show) {
char sql_query[1024];
int rows = 0;
string name;
for(char id = 'a'; id <= 'z'; ++id) {
name = string("") + id + id + id + id;
sprintf(sql_query,
"delete from `testDB`.`test` WHERE (`name` = '%s');", name.c_str());
rows += mysql_conn->ExecuteDelete(sql_query);
}
if(rows && show) {
printf("delete sql execute affected rows : %d\n", rows);
}
}
void TestUpdate(CMysqlConn *mysql_conn, bool show) {
char sql_query[1024];
int rows = 0;
string name, password;
for(char id = 'a'; id <= 'z'; ++id) {
name = string("") + id + id + id + id;
char p = (25 - (id - 'a')) + 'a';
password = string("") + p + p + p + p;
sprintf(sql_query,
"update `testDB`.`test` set `password` = '%s' \
where (`name` = '%s');", name.c_str(), password.c_str());
rows += mysql_conn->ExecuteUpdate(sql_query);
}
if(rows && show) {
printf("update sql execute affected rows : %d\n", rows);
}
}
void TestSelect(CMysqlConn *mysql_conn, bool show) {
char sql_query[1024];
sprintf(sql_query, "select * from test;");
CResultSet *result_set = mysql_conn->ExecuteSelect(sql_query);
if(show) {
if(result_set != nullptr) {
while(result_set->Next()) {
cout << result_set->GetInt("id") << "\t";
cout << result_set->GetString("name") << "\t";
cout << result_set->GetString("password") << endl;
}
delete result_set;
result_set = nullptr;
}
}
}
typedef struct _TestArgs {
CDBPool *db_pool;
int show;
int times;
} TestArgs;
void TestMain(void *args) {
TestArgs *test_args = (TestArgs*)args;
for(int i = 0; i < test_args->times; ++i) {
CMysqlConn *mysql_conn = test_args->db_pool->GetMysqlConn(-1);
/*
TestDelete(mysql_conn, test_args->show);
TestSelect(mysql_conn, test_args->show);
TestDrop(mysql_conn, test_args->show);
TestCreate(mysql_conn, test_args->show);
TestInsert(mysql_conn, test_args->show);
TestSelect(mysql_conn, test_args->show);
TestUpdate(mysql_conn, test_args->show);
*/
TestSelect(mysql_conn, test_args->show);
mysql_conn = test_args->db_pool->DelMysqlConn(mysql_conn);
}
}
void NormalMain(void *args) {
TestArgs *test_args = (TestArgs*)args;
for(int i = 0; i < test_args->times; ++i) {
CMysqlConn *mysql_conn = new CMysqlConn(test_args->db_pool);
mysql_conn->Init();
/*
TestDelete(mysql_conn, test_args->show);
TestSelect(mysql_conn, test_args->show);
TestDrop(mysql_conn, test_args->show);
TestCreate(mysql_conn, test_args->show);
TestInsert(mysql_conn, test_args->show);
TestSelect(mysql_conn, test_args->show);
TestUpdate(mysql_conn, test_args->show);
*/
TestSelect(mysql_conn, test_args->show);
delete mysql_conn;
}
}
void testPool(CDBPool &db_pool) {
CMysqlConn *mysql_conn[100];
for(int i = 0; i < 100; ++i) {
mysql_conn[i] = db_pool.GetMysqlConn(-1);
}
for(int i = 0; i < 100; ++i) {
mysql_conn[i] = db_pool.DelMysqlConn(mysql_conn[i]);
}
}
int main(int argc, char *argv[])
{
if(argc < 2) {
printf("./software ip\n");
return 0;
}
int times = 1000;
CDBPool db_pool("0_pool", argv[1], 3306, "连接名", "连接密码", "数据库名");
TestArgs test_args;
test_args.db_pool = &db_pool;
test_args.show = false;
test_args.times = times;
WorkTime(TestMain, (void*)&test_args, "database connect pool test\n");
WorkTime(NormalMain, (void*)&test_args, "database normal execute test\n");
return 0;
}
测试效率
本地连接mysql，1000次耗时3.5秒左右，使用线程池可以加快数据库的交互。
368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
