mysql连接池以及单连接性能优化思路_连接池连接有效性优化

连接池简介

池化技术

连接池也是池化技术的一种应用，池化技术能够减少资源对象的创建次数，提高程序的响应性能，特别是在高并发下这种提高更加明显。

使用池化技术缓存的资源对象有如下共同特点：

1. 对象创建时间长；
2. 对象创建需要大量资源；
3. 对象创建后可被重复使用
   像常见的线程池、内存池、连接池、句柄池具有以上的共同特点。

数据库连接池

什么是数据库连接池

数据库连接池（Connection pool）是程序启动时建立足够的数据库连接，并将这些连接组成
一个连接池，由程序动态地对池中的连接进行申请，使用，释放。
如果你的模块压根就一两个连接，且有独立的句柄维护，并且持久复用，就不需要连接池；

如果你的模块有很多用户会访问，每次访问都需要进行三次握手，数据库的认证，传输，mysql语句的query以及连接断开，那连接池就很有必要了（避免无意义的带宽和时间浪费以及过高的网络IO）。

数据库连接池的优点

结合上图，可以了解连接池有以下优点：

1. 资源复用
   由于数据库连接得到复用，避免了频繁的创建、释放连接引起的性能开销，在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数
   量）。
2. 更快的系统响应速度
   数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了从数据库连接初始化和释放过程的开销，从而缩减了系统整体响应时间。
3. 统一的连接管理，避免数据库连接泄露
   在较为完备的数据库连接池实现中，可根据预先的连接占用超时设定，强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄露。

连接池的使用

1. 从连接池获取或创建可用连接；
2. 使用完毕之后，把连接返回给连接池；
3. 在系统关闭前，断开所有连接并释放连接占用的系统资源；
   

连接池的应用范例

1. Apache Tomcat：Java Web 应用服务器，使用连接池来管理数据库连接。
2. HikariCP：一个轻量级的高性能 JDBC 连接池，被广泛用于 Java 应用开发。
3. Django：Python Web 框架，支持使用连接池来管理数据库连接。
4. 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语句的执行时最大的性能瓶颈。
简单调研后提供了如下优化思路：

1. 减少mysql_query的次数，优化业务逻辑，争取一次query更新尽可能多的表项
2. 增加mysql预处理的注册，减少重复性的sql占用资源
3. 实时性不强的query交由其他线程去做，封装简单的异步接口
4. 优化sql语句。

mysql预处理简介

来源：mysql中文手册
对于多次执行的语句，预处理执行比直接执行快，主要原因在于，仅对查询执行一次解析操作。

1. 在直接执行的情况下，每次执行语句时，均将进行查询。此外，由于每次执行预处理语句时仅需发送参数的数据，从而减少了网络通信量。
2. 预处理语句的另一个优点是，它采用了二进制协议，从而使得客户端和服务器之间的数据传输更有效率。

下述语句可用作预处理语句：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;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

c/cpp提升