c mysql连接池_mysql 简单的连接池实现

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

下面是mysql_pool实现的源码，

1. 头文件 mysql_pool.h

1 //

2 //创建人： levy3 //创建时间：Jun 5, 20174 //功能：mysql_pool.h5 //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

15 #include

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 voidmysql_pool_init();41 voiddestory_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 //创建人： levy3 //创建时间：Jun 5, 20174 //功能：mysql_pool.c5 //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

13 #include

14 #include

15 #include

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 returnNULL;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 returnNULL;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 returnNULL;43 }44 conn->next =NULL;45 conn->prev =NULL;46 returnconn;47 }48

49 /*向链接池中压入一个mysql连接conn50 **/

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 returnconn;88 }89

90 /*初始化mysql连接池*/

91 voidmysql_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

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 returnconn;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 returnconn;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 voiddestory_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 intres;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 returnNULL;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 returnNULL;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 returnres_ptr;210 }2113.测试主函数main.c，同时创建10000个线程1 2 //创建人： levy

3 //创建时间：May 25, 20174 //功能：main.c5 //Copyright (c) 2016 levy. All Rights Reserved.6 //Ver 变更日期 负责人 变更内容7 //──────────────────────────────────────────────────────────────────────────8 //V0.01 May 25, 2017 levy 初版9 //

10

11 #include

12 #include

13 #include

14 #include

15 #include

16 #include

17 #include

18 #include

19 #include "mysql_pool.h"

20

21 const char *test_sql = "SELECT ID,Name,Value,RecordTime FROM test where ID <1024";22 extern unsigned intquery_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 structtimeval 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

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