mysql连接池总结&单连接性能优化
连接池简介
池化技术
连接池也是池化技术的一种应用,池化技术能够减少资源对象的创建次数,提高程序的响应性能,特别是在高并发下这种提高更加明显。
使用池化技术缓存的资源对象有如下共同特点:
- 对象创建时间长;
- 对象创建需要大量资源;
- 对象创建后可被重复使用
像常见的线程池、内存池、连接池、句柄池具有以上的共同特点。
数据库连接池
什么是数据库连接池
数据库连接池(Connection pool)是程序启动时建立足够的数据库连接,并将这些连接组成
一个连接池,由程序动态地对池中的连接进行申请,使用,释放。
如果你的模块压根就一两个连接,且有独立的句柄维护,并且持久复用,就不需要连接池;
如果你的模块有很多用户会访问,每次访问都需要进行三次握手,数据库的认证,传输,mysql语句的query以及连接断开,那连接池就很有必要了(避免无意义的带宽和时间浪费以及过高的网络IO)。
数据库连接池的优点
结合上图,可以了解连接池有以下优点:
- 资源复用
由于数据库连接得到复用,避免了频繁的创建、释放连接引起的性能开销,在减少系统消耗的基础上,另一方面也增进了系统运行环境的平稳性(减少内存碎片以及数据库临时进程/线程的数
量)。 - 更快的系统响应速度
数据库连接池在初始化过程中,往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言,直接利用现有可用连接,避免了从数据库连接初始化和释放过程的开销,从而缩减了系统整体响应时间。 - 统一的连接管理,避免数据库连接泄露
在较为完备的数据库连接池实现中,可根据预先的连接占用超时设定,强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄露。
连接池的使用
- 从连接池获取或创建可用连接;
- 使用完毕之后,把连接返回给连接池;
- 在系统关闭前,断开所有连接并释放连接占用的系统资源;
连接池的应用范例
- Apache Tomcat:Java Web 应用服务器,使用连接池来管理数据库连接。
- HikariCP:一个轻量级的高性能 JDBC 连接池,被广泛用于 Java 应用开发。
- Django:Python Web 框架,支持使用连接池来管理数据库连接。
- MySQL Connector/J:MySQL 官方的 JDBC 驱动程序,提供了连接池的支持。
mysql连接池的优化效果
(结果取自Darren@0voice)
经验公式:连接数 = ((核心数 * 2) + 有效磁盘数)
CPU总核数 = 物理CPU个数 * 每颗物理CPU的核数
总逻辑CPU数 = 物理CPU个数 * 每颗物理CPU的核数 * 超线程数
题外话,单连接mysql性能优化
Linux c接口中的mysql的性能优化是题主最近需要解决的问题;
c语言对于mysql的业务逻辑支持并不好,一方面mysql的sql语句组合起来较为麻烦,
另一方面mysql_query是同步的mysql接口,一次任务中如果需要更新多表多列则需要拼接大量的mysql语句并且query,此间会浪费大量的cpu(组sql)以及等待mysql结果的返回。
见上图可知,mysql语句的执行时最大的性能瓶颈。
简单调研后提供了如下优化思路:
- 减少mysql_query的次数,优化业务逻辑,争取一次query更新尽可能多的表项
- 增加mysql预处理的注册,减少重复性的sql占用资源
- 实时性不强的query交由其他线程去做,封装简单的异步接口
- 优化sql语句。
mysql预处理简介
来源:mysql中文手册
对于多次执行的语句,预处理执行比直接执行快,主要原因在于,仅对查询执行一次解析操作。
- 在直接执行的情况下,每次执行语句时,均将进行查询。此外,由于每次执行预处理语句时仅需发送参数的数据,从而减少了网络通信量。
- 预处理语句的另一个优点是,它采用了二进制协议,从而使得客户端和服务器之间的数据传输更有效率。
下述语句可用作预处理语句:CREATE TABLE、DELETE、DO、INSERT、REPLACE、SELECT、SET、UPDATE、以及大多数SHOW语句。在MySQL 5.1中,不支持其他语句
mysql预处理demo
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#define DB_HOST "localhost"
#define DB_USER "your_username"
#define DB_PASS "your_password"
#define DB_NAME "your_database"
int main() {
MYSQL *conn;
MYSQL_STMT *stmt;
MYSQL_BIND param[2];
char query[256] = "INSERT INTO users (name, age) VALUES (?, ?)";
char name[50];
int age;
// 初始化连接
conn = mysql_init(NULL);
if (conn == NULL) {
fprintf(stderr, "Failed to initialize MySQL connection\n");
return 1;
}
// 连接到 MySQL 服务器
if (mysql_real_connect(conn, DB_HOST, DB_USER, DB_PASS, DB_NAME, 0, NULL, 0) == NULL) {
fprintf(stderr, "Failed to connect to MySQL database: Error: %s\n", mysql_error(conn));
mysql_close(conn);
return 1;
}
// 准备预处理语句
stmt = mysql_stmt_init(conn);
if (stmt == NULL) {
fprintf(stderr, "Failed to initialize MySQL statement: Error: %s\n", mysql_error(conn));
mysql_close(conn);
return 1;
}
// 编译和绑定参数
if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0) {
fprintf(stderr, "Failed to prepare MySQL statement: Error: %s\n", mysql_error(conn));
mysql_stmt_close(stmt);
mysql_close(conn);
return 1;
}
// 绑定参数
memset(param, 0, sizeof(param));
param[0].buffer_type = MYSQL_TYPE_STRING;
param[0].buffer = name;
param[0].is_null = 0;
param[0].length = &name_length;
param[1].buffer_type = MYSQL_TYPE_LONG;
param[1].buffer = &age;
param[1].is_null = 0;
param[1].length = NULL;
if (mysql_stmt_bind_param(stmt, param) != 0) {
fprintf(stderr, "Failed to bind parameters: Error: %s\n", mysql_error(conn));
mysql_stmt_close(stmt);
mysql_close(conn);
return 1;
}
// 设置参数值
printf("Enter name: ");
scanf("%s", name);
printf("Enter age: ");
scanf("%d", &age);
// 执行预处理语句
if (mysql_stmt_execute(stmt) != 0) {
fprintf(stderr, "Failed to execute MySQL statement: Error: %s\n", mysql_error(conn));
mysql_stmt_close(stmt);
mysql_close(conn);
return 1;
}
printf("Record inserted successfully!\n");
// 释放资源
mysql_stmt_close(stmt);
mysql_close(conn);
return 0;
}