Mysql连接池的设计与实现_sql连接池设计-优快云博客

众所周知，MySQL数据库是一个典型的C/S结构，即：客户端和服务器端。如果我们部署好了MySQL服务器，想要在客户端访问服务器端的数据，在编写程序的时候就可以通过官方提供的C语言的API来实现。

连接池的作用：

对于一个数据库操作都是访问数据时创建连接，访问完毕后断开连接。在高并发的场景下，频繁创建断开消耗大量资源和时间

连接池创建：

建立通讯连接的TCP三次握手
数据库服务器的连接认证
数据库服务器关闭连接的资源回收
断开通讯连接的TCP四次握手

在程序中连接Mysql服务器步骤：

1、初始化连接环境

2、连接mysql服务器，需要提高如下连接数据

服务器的IP地址
服务器监听的端口（3306）
连接服务器使用的用户名，和密码
要操作的数据库名称

3、连接已经建立，后续操作就是对数据库进行增删改查

这些操作都是需要sql语句完成的
数据查询：通过调用api执行一个查询sql语句
数据修改（添加/删除/更新）：通过调用api执行一个修改数据的sql语句

4、如果进行数据添加/删除/更新需要进行的事务处理

需要对执行结果进行判断：成功（提交事务）；失败（数据回滚）

5、数据库读操作->查询->得到结果

6、遍历结果

7、释放资源

Mysql 的API

初始化连接环境

MYSQL *mysql_init(MYSQL *mysql) ;

连接mysql服务器

/*
返回值: 
    成功: 返回MYSQL*连接句柄, 对于成功的连接，返回值与第1个参数的值相同。返回值指向的内存和第一个参数指针指向的内存一样
    失败，返回NULL。
    句柄: 是windows中的一个概念, 句柄可以理解为一个实例(或者对象)
*/ 
MYSQL *mysql_real_connect(
    MYSQL *mysql,           // mysql_init() 函数的返回值
    const char *host,       // mysql服务器的主机地址, 写IP地址即可
                            // localhost, null -> 代表本地连接
    const char *user,       // 连接mysql服务器的用户名, 默认: root 
    const char *passwd,     // 连接mysql服务器用户对应的密码, root用户的密码
    const char *db,         // 要使用的数据库的名字
    unsigned int port,      // 连接的mysql服务器监听的端口
                            // 如果==0, 使用mysql的默认端口3306, !=0, 使用指定的这个端口
    const char *unix_socket,// 本地套接字, 不使用指定为 NULL
    unsigned long client_flag); // 通常指定为0

执行sql语句

int mysql_query(MYSQL *mysql, const char *query);

返回值：查询成功返回0，查询成功结果在mysql对象中。如果错误返回非0值

获取结果集

MYSQL_RES *mysql_store_result(MYSQL *mysql);

将结果集从 mysql(参数) 对象中取出。

返回值: 具有多个结果的MYSQL_RES结果集合。如果出现错误，返回NULL。

得到结果集的列数

unsigned int mysql_num_fields(MYSQL_RES *result)

返回值: 结果集中的列数

获取表头->列名

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result);

参数: 调用 mysql_store_result() 得到的返回值
返回值: MYSQL_FIELD* 指向一个结构体

举例说明：

// 得到存储头信息的数组的地址
MYSQL_FIELD* fields = mysql_fetch_fields(res);
// 得到列数
int num = mysql_num_fields(res);
// 遍历得到每一列的列名
for(int i=0; i<num; ++i)
{
    printf("当前列的名字: %s\n", fields[i].name);
}

获取集合字段的长度

unsigned long *mysql_fetch_lengths(MYSQL_RES *result);

返回结果集内当前行的列的长度:
如果打算复制字段值，使用该函数能避免调用strlen()。
如果结果集包含二进制数据，必须使用该函数来确定数据的大小，原因在于，对于包含Null字符的任何字段，strlen()将返回错误的结果。

遍历结果集合

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

资源回收

// 释放结果集
void mysql_free_result(MYSQL_RES *result);

// 关闭mysql实例
void mysql_close(MYSQL *mysql);

提供github代码地址：https://github.com/huangbh798/hbh_first.git

经过我们简单测试，使用连接池要优于非连接池，为了提高连接池的效率可以结合多线程提高性能。

我们结合TCP心跳检测，建立了一个简单CS架构测试，通过客户端自动写入SQL，服务端去处理sql。为了保证连接的安全性。我们采用心跳检测机制来保存TCP连接（客户端定时发送一个心跳包，服务器接收后立即返回，保证连接的持续性。设置超时重传机制来保证真实断开连接）。

具体实现如下：

在先前数据库连接池的前提下，进行的。

客户端

初始化套接字
连接服务器
开始通讯：创建子线程接收数据、发送心跳包、发送数据查询
关闭连接

服务端

创建Socket套接字
给这个socket绑定一个端口
给这个socket开启监听属性（这个只能用来接收和连接）
等待客户端连接
开始通讯（处理心跳包、消息、sql语句）
关闭连接

代码实现

socket.h

主要对socket所需函数进行封装。


#include <arpa/inet.h>
#include <asm-generic/errno-base.h>
#include <asm-generic/socket.h>
#include <cerrno>
#include <cstddef>
#include <cstdio>
#include <cstdlib>
#include <cstring>
#include <iostream>
#include <netinet/in.h>
#include <sys/socket.h> 
#include <unistd.h>

/*
数据包格式
数据长度    数据包类型  数据块
int 4     char 1字节     N字节
*/
enum Type{Heart, Message, SQL};

// 初始化套接字
int initSocket()
{
    int lfd = socket(AF_INET, SOCK_STREAM, 0);
    if(lfd == -1)
    {
        perror("socket");
        return -1;
    }
    return lfd;
}
// 初始化socket addr结构体
void initSocketaddr(struct sockaddr* addr, unsigned port, const char* ip)
{
    struct sockaddr_in* addrin = (struct sockaddr_in*)addr;
    addrin->sin_family = AF_INET;
    addrin->sin_port = htons(port);
    addrin->sin_addr.s_addr = inet_addr(ip);


}
// 设置监听
int setListen(int lfd, unsigned port)
{
    struct sockaddr addr;
    initSocketaddr(&addr, port, "0.0.0.0");
    //设置端口复用
    int opt = 1;
    setsockopt(lfd, SOL_SOCKET, SO_REUSEADDR, &opt, sizeof(opt));
    int ret = bind(lfd, &addr, sizeof(addr));
    if(ret == -1)
    {
        perror("bind");
        return -1;
    }
    ret = listen(lfd, 128);
    if(ret == -1)
    {
        perror("listen");
        return -1;
    }
    return 0;
}
// 接收客户端连接
int acceptConnect(int lfd ,struct sockaddr* addr)
{
    int connfd;
    if (addr == NULL) {
        connfd = accept(lfd, NULL, NULL);
    }
    else {
        socklen_t len = sizeof(struct sockaddr);
        connfd = accept(lfd, addr, &len);
    }

    if(connfd == -1)
    {
        perror("accept");
        return -1;
    }
    return connfd; 
}

// 连接服务器
int connectToHost(int fd, unsigned port, const char* ip)
{
    struct sockaddr addr;
    initSocketaddr(&addr, port, ip);
    int ret = connect(fd, &addr, sizeof(addr));
    if(ret == -1)
    {
        perror("connect");
        return -1;
    }
    return 0;
}
// 读出指定字节数
int readn(int fd, char* buffer, int size)
{
    int left = size;
    int readBytes = 0;
    char* ptr = buffer;
    while (left) {
        readBytes = read(fd, ptr, left);
        if(readBytes == -1)
        {
            if(errno == EINTR)
            {
                readBytes = 0;
            }
            else
            {
                perror("read");
                return -1;
            }
        }
        else if(readBytes == 0)
        {
            std::cout<<"对方主动断开连接"<<std::endl;
            return -1;
        }
        ptr += readBytes;
        left -= readBytes;
    }
    return size - left;
}

// 写入指定字节数
int writen(int fd, char* buffer, int length)
{
    int left = length;
    int writeBytes = 0;
    char* ptr = buffer;
    while (left) {
        writeBytes = write(fd, ptr, left);
        if(writeBytes <= 0)
        {
            if(errno == EINTR)
            {
                writeBytes = 0;
            }
            else
            {
                perror("write");
                return -1;
            }
        }
        ptr += writeBytes;
        left -= writeBytes;
    }
    return length;
}
// 发送数据
bool sendMessage(int fd, const char* buffer, int length, enum Type t)
{
    int dataLen = length + 1 + sizeof(int);
    char* data = (char*) malloc(dataLen);
    if(data == NULL)
    {
        return false;
    }
    int netlen = htonl(length + 1);
    memcpy(data, &netlen, sizeof(int));
    // const char* ch = t == Heart ? "H" : "M";
    const char* ch = (t == Heart) ? "H" :
                 (t == Message)  ? "M" :
                                "S";
    memcpy(data + sizeof(int), ch, sizeof(char));
    memcpy(data + sizeof(int) + 1, buffer, length);
    int ret = writen(fd, data, dataLen);
    free(data);
    return ret == dataLen;
}

// 接收数据
int recvMessage(int fd, char** buffer,  Type* t)
{
    int dataLen = 0;
    int ret = readn(fd, (char*)&dataLen, sizeof(int));
    if(ret == -1)
    {
        *buffer = NULL;
        return -1;
    }
    dataLen = ntohl(dataLen);
    char ch;
    readn(fd, &ch, 1);
    // *t = ch == 'H' ? Heart : Message;
    *t = (ch == 'H') ? Heart :
     (ch == 'M') ? Message :
                   SQL; 
    char* tmpbuf = (char*)calloc(dataLen, sizeof(char));
    if(tmpbuf == NULL)
    {
        *buffer = NULL;
       return -1;
    }
    ret = readn(fd, tmpbuf, dataLen -1);
    if(ret != dataLen - 1)
    {
        free(tmpbuf);
        *buffer = NULL;
        return -1;
    }
    *buffer = tmpbuf;
    std::cout << "about to call recvMessage, end" << fd << std::endl;
    return ret;
}

clientlish.h

将客户端连接封装成一个链表。实现多用户与客户端交互，客户端的加入与离开对应着添加节点和删除节点，维护链表即维护在线用户。


// 定义链表的节点

#include <cstdlib>
#include <iostream>
#include <pthread.h>
struct ClientInfo
{
    int fd;
    int count;
    pthread_t pid_;
    struct ClientInfo* next;
};
//创建一个链表
struct ClientInfo* createList()
{
    struct ClientInfo* head = (struct ClientInfo*) malloc(sizeof(struct ClientInfo));
    return head;
};
// 添加一个节点（头插法）
struct ClientInfo* prependNode(struct ClientInfo* head, int fd)
{
    struct ClientInfo* node = (struct ClientInfo*) malloc(sizeof(struct ClientInfo));
    node->next = head->next;
    head->next = node;
    node->fd = fd;
    node->count = 0;
    return node;
}

// 删除一个节点
bool removeNode(struct ClientInfo* head, int fd)
{
    struct ClientInfo* p = head;
    struct ClientInfo* q = head->next;   
    while (q) {
        if(q->fd == fd)
        {
            p->next = q->next;
            free(q);
            std::cout<<"成功删除"<<std::endl;
            return true;
        }
        p = q;
        q = q->next;
    }
    return false;
}

// 销毁链表
void freeCliList(struct ClientInfo* head)
{
    struct ClientInfo* p = head;
    while (p) {
        struct ClientInfo* tmp = p;
        p = p->next;
        free(tmp);
    }
}

服务端

处理来自客户端的消息

#include "socket.h"
#include <cstddef>
#include <cstdio>
#include <cstdlib>
#include <cstring>
#include <iostream>
#include <netinet/in.h>
#include <unistd.h>
#include <pthread.h>
#include "clientlist.h"
#include "ConnectionPool.h"
#include "MysqlConn.h"
pthread_mutex_t mutex;
struct FdInfo
{
    int fd;
    int count;
};
void* parseRecvMessage(void* arg)
{
    struct ClientInfo *info = (struct ClientInfo*) arg;
    std::cout<<"服务器执行 parseRecvMessage" <<std::endl;
    while (1) {
        char* buffer;
        enum Type t;
        std::cout<<"服务器执行 while" <<std::endl;
        int len = recvMessage(info->fd, &buffer, &t);
        std::cout<<"服务器执行 recvMessage" <<std::endl;
        if(buffer == NULL)
        {
            std::cout<<"通信子进程退出。。。。。。fd = "<<info->fd <<std::endl;
            pthread_exit(NULL);
        }
        else
        {   
            std::cout<<"服务器接收信息。。。。。。fd = "<<info->fd <<std::endl;
            if(t == Heart)
            {
                std::cout<<"心跳包"<< buffer <<std::endl;
                pthread_mutex_lock(&mutex);
                info->count = 0;
                pthread_mutex_unlock(&mutex);
                sendMessage(info->fd, buffer , len, Heart);
            }
            else if(t == Message){
                const char* pt = "世界和平";
                std::cout<<"数据包，"<< buffer <<std::endl;
                sendMessage(info->fd, pt , strlen(pt), Message);
            }
            else {
                MysqlConn conn;
                bool ok = conn.connect("hbh", "123456", "testdb", "192.168.80.107");
                if (!ok) {
                    std::cerr << "数据库连接失败！" << std::endl;
                } else {
                    std::cout << "数据库连接成功！" << std::endl;
                }
                std::string sql = buffer;
                std::cout<<"SQL语句:"<< buffer <<std::endl;
                bool flag = conn.update(sql);
                std::cout<<"SQL语句 flag = "<<flag<<std::endl;
            }
            free(buffer);
        }
    }
    return NULL;
}

void* heartBeat(void* arg)
{
    struct ClientInfo *head = (struct ClientInfo*) arg;
    struct ClientInfo* p = NULL;
    while (1) {
        p = head->next;
        while (p) {
            pthread_mutex_lock(&mutex);
            p->count++;
            printf("server heartbeat debug: count=%d\n", p->count);
            if(p->count > 5)
            {
                std::cout<<"客户端与服务器断开连接" <<std::endl;
                close(p->fd);
                pthread_cancel(p->pid_);
                removeNode(head, p->fd);
            }
            pthread_mutex_unlock(&mutex);
            p = p->next;
        }
        sleep(3);
    }

    return NULL;
}
int main()
{
    unsigned short port = 8888;
    const char* ip = "127.0.0.1";
    int lfd = initSocket();
    setListen(lfd, port);
    
    struct ClientInfo* head = createList();
    pthread_mutex_init(&mutex, NULL);

    //添加心跳包线程
    pthread_t pid1;
    pthread_create(&pid1, NULL, heartBeat, head);

    //创建sql线程池
    // ConnectionPool* pool = ConnectionPool::getConnectionPool();

    while (1) {
        int sockfd = acceptConnect(lfd, NULL);
        if(sockfd == -1)
        {
            continue;
        }
         struct ClientInfo* node = prependNode(head, sockfd);
        //创建接收数据的子线程
        
        pthread_create(&node->pid_, NULL, parseRecvMessage, node);
        pthread_detach(node->pid_);
        
    }
    pthread_join(pid1, NULL);
    pthread_mutex_destroy(&mutex);
    close(lfd);
    return 0;
}

客户端

#include "socket.h"
#include <cstddef>
#include <cstdio>
#include <cstdlib>
#include <cstring>
#include <iostream>
#include <netinet/in.h>
#include <unistd.h>
#include <pthread.h>

pthread_mutex_t mutex;
struct FdInfo
{
    int fd;
    int count;
};
void* parseRecvMessage(void* arg)
{
    struct FdInfo *info = (struct FdInfo*) arg;
    while (1) {
        char* buffer;
        enum Type t;
        recvMessage(info->fd, &buffer, &t);
        if(buffer == NULL)
        {
            continue;
        }
        else
        {
            if(t == Heart)
            {
                std::cout<<"心跳包"<< buffer <<std::endl;
                pthread_mutex_lock(&mutex);
                info->count = 0;
                pthread_mutex_unlock(&mutex);
            }
            else if(t== Message){
                std::cout<<"数据包，"<< buffer <<std::endl;
            }
            else {
                std::cout<<"SQL语句，"<< buffer <<std::endl;
            }
            free(buffer);
        }
    }
    return NULL;
}

void* heartBeat(void* arg)
{
    struct FdInfo *info = (struct FdInfo*) arg;
    sleep(3); 
    while (1) {
        pthread_mutex_lock(&mutex);
        info->count++;
        printf("heartbeat debug: count=%d\n", info->count); 
        if(info->count > 5)
        {
            std::cout<<"客户端与服务器断开连接 fd = " << info->fd<<std::endl;
            close(info->fd);
            
            exit(0);
        }
        pthread_mutex_unlock(&mutex);
        sendMessage(info->fd, "hello", 5, Heart);
        sleep(3);
    }

    return NULL;
}
int main()
{
    struct FdInfo info;
    unsigned short port = 8888;
    const char* ip = "127.0.0.1";
    info.fd = initSocket();
    info.count = 0;
    connectToHost(info.fd, port, ip);
    pthread_mutex_init(&mutex, NULL);
    //创建接收数据的子线程
    pthread_t pid;
    pthread_create(&pid, NULL, parseRecvMessage, &info);

    pthread_t pid1;
    pthread_create(&pid1, NULL, heartBeat, &info);

    while (1) {
        // const char* data = "你好，huang";
        // sendMessage(info.fd , data, strlen(data), Message);
        // sleep(2);

        const char* data1 = "INSERT INTO `person` (`age`, `sex`, `name`) VALUES (33, 'male', 'hbh')";
        sendMessage(info.fd , data1, strlen(data1), SQL);

        sleep(2);
    }

    pthread_join(pid, NULL);
    pthread_join(pid1, NULL);
    pthread_mutex_destroy(&mutex);
    return 0;
}

目前该项目只支C/S简单架构的数据传输，并没有设计到复杂的聊天通讯问题。在此基础上我们可以通过高性能服务器结合我们的数据库连接池实现高性能数据传输，后续将结合websocket实现一个简单的聊天系统。当前AI比较火，我们可以设置一个AI助手聊天室，提高项目的b格。