mysql 简单的连接池实现

一个mysql客户端项目中，所有的参数都保存到mysql中，需要频繁的访问mysql server，其实使用一个mysql长连接也可以满足要求，但为了更好的利用多核CPU尝试使用连接池来充分发挥多线程的优势(电脑配置 i7-4核8线程)。

下面是mysql_pool实现的源码，

1. 头文件 mysql_pool.h

 1 // 
 2 // 创建人： levy
 3 // 创建时间：Jun 5, 2017
 4 // 功能：mysql_pool.h
 5 // Copyright (c) 2017 levy. All Rights Reserved.
 6 // Ver          变更日期        负责人                           变更内容
 7 // ──────────────────────────────────────────────────────────────────────────
 8 // V0.01         Jun 5, 2017                  levy          初版
 9 // 
10 
11 
12 #ifndef MYSQL_POOL_H_
13 #define MYSQL_POOL_H_
14 #include <mysql.h>
15 #include <pthread.h>
16 #define MAX_KEPP_CONNECTIONS 8
17 typedef struct mysql_conn        //mysql连接链表结构体定义
18 {
19     struct mysql_conn * next;
20     struct mysql_conn * prev;
21     MYSQL conn;
22 
23 }mysql_conn;
24 
25 typedef struct mysql_pool        //mysql连接池结构体定义
26 {
27     char host[64];                //主机名称
28     char username[32];            //用户名
29     char passwd[32];            //密码
30     char database[32];            //默认数据库
31     int s_port;                    //端口号，默认3306
32     int max_connections;        //保持开启的mysql最大连接数
33     int free_connections;        //当前空闲的mysql连接数
34     int is_idle_block;            //是否开启了无可用连接阻塞
35     pthread_mutex_t lock;        //mysql链表锁
36     pthread_cond_t idle_signal;    //等待可用连接的条件变量
37     mysql_conn * mysql_list;    //mysql连接池链表
38 }mysql_pool;
39 
40 void mysql_pool_init();
41 void destory_mysql_pool();
42 void destory_mysql_connection(mysql_conn *conn);
43 void release_mysql_connection(mysql_conn *conn);
44 mysql_conn * get_mysql_connection();
45 
46 MYSQL_RES* mysql_execute_query(const char *sql,unsigned long length,int *flag);
47 
48 #endif /* MYSQL_POOL_H_ */

2.连接池实现  1 //

  2 // 创建人： levy
  3 // 创建时间：Jun 5, 2017
  4 // 功能：mysql_pool.c
  5 // Copyright (c) 2016 levy. All Rights Reserved.
  6 // Ver          变更日期        负责人                           变更内容
  7 // ──────────────────────────────────────────────────────────────────────────
  8 // V0.01         Jun 5, 2017                  levy          初版
  9 // 
 10 
 11 #include "mysql_pool.h"
 12 #include <stdlib.h>
 13 #include <string.h>
 14 #include <errno.h>
 15 #include <stdio.h>
 16 static mysql_pool pool_mysql;//连接池定义
 17 unsigned int query_times = 0;//mysql所有的查询次数，用于测试
 18 
 19 /*创建一个新的mysql连接
 20  * @return NULL代表创建失败。
 21  */
 22 mysql_conn * mysql_new_connection()
 23 {
 24     mysql_conn * conn=malloc(sizeof(mysql_conn));
 25     if(mysql_init(&conn->conn)==NULL)
 26     {
 27         printf("can not init mysql: [%s]\n",strerror(errno));
 28         free(conn);
 29         return NULL;
 30     }
 31     if(mysql_options(&conn->conn, MYSQL_SET_CHARSET_NAME, "UTF8")!=0)
 32     {
 33         printf("can not set mysql options[errno = %d]: [%s]\n",mysql_errno(&conn->conn),mysql_error(&conn->conn));
 34         free(conn);
 35         return NULL;
 36     }
 37     //连接到mysql服务端，设置CLIENT_MULTI_STATEMENTS通知服务器客户端可以处理由多语句或者存储过程执行生成的多结果集
 38     if(mysql_real_connect(&conn->conn,pool_mysql.host,pool_mysql.username,pool_mysql.passwd,pool_mysql.database,pool_mysql.s_port,NULL, CLIENT_MULTI_STATEMENTS)==NULL)
 39     {
 40         printf("can not connect  mysql server[errno = %d]: [%s]\n",mysql_errno(&conn->conn),mysql_error(&conn->conn));
 41         free(conn);
 42         return NULL;
 43     }
 44     conn->next = NULL;
 45     conn->prev = NULL;
 46     return conn;
 47 }
 48 
 49 /*向链接池中压入一个mysql连接conn
 50  * */
 51 void conn_push(mysql_conn * conn)
 52 {
 53     mysql_conn *lc = pool_mysql.mysql_list;
 54     if(lc==NULL)
 55     {
 56         pool_mysql.mysql_list=conn;
 57 
 58     }else
 59     {
 60         while(lc->next)
 61         {
 62             lc=lc->next;
 63         }
 64         lc->next = conn;
 65         conn->prev = lc;
 66 
 67     }
 68     pool_mysql.free_connections++;
 69 }
 70 
 71 /*从连接池中出栈一个mysql连接
 72  * @return NULL表示连接池中没有可用的连接
 73  * */
 74 mysql_conn * conn_pop()
 75 {
 76     mysql_conn*conn = pool_mysql.mysql_list;
 77     if(conn != NULL)
 78     {
 79 
 80         pool_mysql.mysql_list = conn->next;
 81         if(pool_mysql.mysql_list)
 82         {
 83             pool_mysql.mysql_list->prev = NULL;
 84         }
 85         pool_mysql.free_connections--;
 86     }
 87     return conn;
 88 }
 89 
 90 /*初始化mysql连接池*/
 91 void mysql_pool_init()
 92 {
 93     mysql_conn * conn;
 94     strncpy(pool_mysql.host,"localhost",sizeof(pool_mysql.host));
 95     strncpy(pool_mysql.username,"test",sizeof(pool_mysql.username));
 96     strncpy(pool_mysql.passwd,"test",sizeof(pool_mysql.passwd));
 97     strncpy(pool_mysql.database,"test",sizeof(pool_mysql.database));
 98     pool_mysql.s_port = 3306;
 99     pool_mysql.max_connections=MAX_KEEP_CONNECTIONS;
100     pool_mysql.free_connections = 0;
101     pool_mysql.mysql_list = NULL;
102     pool_mysql.is_idle_block = 0;
103     pthread_mutex_init(&pool_mysql.lock,NULL);
104     pthread_cond_init(&pool_mysql.idle_signal,NULL);
105     pthread_mutex_lock(&pool_mysql.lock);
106     for(int i=0;i<pool_mysql.max_connections;i++)
107     {
108         conn=mysql_new_connection();
109         if(conn)
110         {
111             conn_push(conn);
112         }else
113         {
114 
115         }
116 
117     }
118     pthread_mutex_unlock(&pool_mysql.lock);
119 }
120 
121 /*从连接池中获取一个mysql连接*/
122 mysql_conn * get_mysql_connection()
123 {
124     pthread_mutex_lock(&pool_mysql.lock);
125     mysql_conn *conn = conn_pop();
126     pthread_mutex_unlock(&pool_mysql.lock);
127     return conn;
128 }
129 
130 /*从连接池中获取一个mysql连接，如果连接池为空，测阻塞*/
131 mysql_conn * get_mysql_connection_block()
132 {
133     pthread_mutex_lock(&pool_mysql.lock);
134     mysql_conn *conn = conn_pop();
135     while(conn == NULL)
136     {
137         pool_mysql.is_idle_block ++;
138         pthread_cond_wait(&pool_mysql.idle_signal,&pool_mysql.lock);
139         conn = conn_pop();
140         pool_mysql.is_idle_block --;
141     }
142     query_times++;
143     pthread_mutex_unlock(&pool_mysql.lock);
144     return conn;
145 }
146 
147 
148 /*回收一个mysql连接到连接池*/
149 void release_mysql_connection(mysql_conn *conn)
150 {
151     pthread_mutex_lock(&pool_mysql.lock);
152     conn->next = NULL;
153     conn->prev = NULL;
154     conn_push(conn);
155     if(pool_mysql.is_idle_block)
156     {
157         pthread_cond_signal(&pool_mysql.idle_signal);
158     }
159     pthread_mutex_unlock(&pool_mysql.lock);
160 }
161 
162 /*销毁一个mysql连接，并释放其占用的资源*/
163 void destory_mysql_connection(mysql_conn *conn)
164 {
165     mysql_close(&conn->conn);
166     free(conn);
167 }
168 
169 //销毁连接池中的所有连接
170 void destory_mysql_pool()
171 {
172     mysql_conn *conn;
173     pthread_mutex_lock(&pool_mysql.lock);
174     conn = conn_pop();
175     for(;conn;conn = conn_pop())
176     {
177         destory_mysql_connection(conn);
178     }
179     pthread_mutex_unlock(&pool_mysql.lock);
180 }
181 
182 
183 MYSQL_RES* mysql_execute_query(const char *sql,unsigned long length,int *flag)
184 {
185     int res;
186     MYSQL_RES *res_ptr;
187     mysql_conn*con = get_mysql_connection_block();
188     if(con == NULL)
189     {
190         printf("can not get mysql connections from the pools\n");
191         *flag =-2;
192         return NULL;
193     }
194     *flag=0;
195     res=mysql_real_query(&con->conn,sql,length);
196     if(res!=0)
197     {
198         printf("mysql_real_query error [errno = %d]: [%s]\n",mysql_errno(&con->conn),mysql_error(&con->conn));
199         release_mysql_connection(con);
200         *flag=res;
201         return NULL;
202     }
203     res_ptr = mysql_store_result(&con->conn);
204     if(res_ptr == NULL)
205     {
206         printf("mysql_store_result error [errno = %d]: [%s]\n",mysql_errno(&con->conn),mysql_error(&con->conn));
207     }
208     release_mysql_connection(con);
209     return res_ptr;
210 }
2113.测试主函数main.c，同时创建10000个线程 1  2 // 创建人： levy

 3 // 创建时间：May 25, 2017
 4 // 功能：main.c
 5 // Copyright (c) 2016 levy. All Rights Reserved.
 6 // Ver          变更日期        负责人                           变更内容
 7 // ──────────────────────────────────────────────────────────────────────────
 8 // V0.01         May 25, 2017                  levy          初版
 9 // 
10 
11 #include <stdlib.h>
12 #include <stdio.h>
13 #include <unistd.h>
14 #include <time.h>
15 #include <sys/time.h>
16 #include <pthread.h>
17 #include <string.h>
18 #include <errno.h>
19 #include "mysql_pool.h"
20 
21 const char *test_sql = "SELECT ID,Name,Value,RecordTime FROM test where ID <1024";
22 extern unsigned int query_times ;
23 
24 
25 void * test_thread(void *arp)
26 {
27     int flag=0;
28     int count=0;
29     MYSQL_RES * resptr;
30     //for(int i=0;i<10;i++)
31     {
32         resptr=mysql_execute_query(test_sql,strlen(test_sql),&flag);
33         if(resptr)
34         {
35             count++;
36             mysql_free_result(resptr);
37         }else
38         {
39             perror("error");
40         }
41     }
42     //printf("I has finsh %d query.\n",count);
43     pthread_exit(NULL);
44 }
45 
46 
47 
48 #define thread_nums 10000
49 int  main(void )
50 {
51     mysql_pool_init();
52     pthread_t th[thread_nums];
53     struct timeval tnow,tnow1,tnow2;
54     gettimeofday(&tnow1, NULL);
55     printf("start time tv_sec = %ld,tv_usec = %ld \n",tnow1.tv_sec,tnow1.tv_usec);
56     for(int i=0;i<thread_nums;i++)
57     {
58         pthread_create(&th[i],NULL,test_thread,NULL);
59     }
60     for(int i=0;i<thread_nums;i++)
61     {
62         pthread_join(th[i],NULL);
63     }
64     gettimeofday(&tnow2, NULL);
65     timersub(&tnow2,&tnow1,&tnow);
66     printf("Max keep connections = %d,max_thread_nums=%d,total query times=%d,use %ld ms",MAX_KEPP_CONNECTIONS,thread_nums,query_times,tnow.tv_sec*1000+tnow.tv_usec/1000);

67     //printf("total time tv_sec = %ld.%3ld s \n",);
68     destory_mysql_pool();
69     pthread_exit(NULL);
70 }

 

测试结果

1.Max keep connections = 1,max_thread_nums=10000,total query times=10000,use 6114 ms

2.Max keep connections = 2,max_thread_nums=10000,total query times=10000,use 3404 ms

3.Max keep connections = 4,max_thread_nums=10000,total query times=10000,use 1958 ms

4.Max keep connections = 8,max_thread_nums=10000,total query times=10000,use 1794 ms

5.Max keep connections = 16,max_thread_nums=10000,total query times=10000,use 1787 ms

 总结：从测试结果可以看出保持最大连接数目为cpu的核心数能够充分发挥cpu多核的优势。

编辑日期：2017-06-05 16:55:39 

转载于:https://www.cnblogs.com/villow/p/6945855.html