mysql的连接池和线程池

本文讲解视频

Mysql连接池和线程池_哔哩哔哩_bilibili

连接层

Mysql连接池

连接池 Connection Pool 一个用户连上对应会话 一个会话对应一个连接 一个连接对应就是一个线程用线程服务操纵体系的调度来一个连接就建一个连接操纵体系做调度上下文切换创建烧毁开销很大以是搞了一个连接池。
mysql是关系型数据库基于（c/s计划），每一个用户用户的请求到达服务器都可能有增删改查的操纵首先进行网络连接TCP三次握手、mysql进行连接认证、mysql server关闭回收资源和TCP四次挥手。一个连接占一个线程，写连接池就是为了把这四个步骤的时间镌汰！
个人理解：连接池就是为了节省资源因为线程资源很宝贵，并且创建必要时间，如果我（客户端）每次连接mysql都必要创建一个线程，不如我在连接池里面创建好了，假如我要连接mysql直接在连接池里面拿取就可以了。那我要怎么设置线程池呢，我们肯定得初始化线程池在里面放一些数据连接对象。我要拿我就直接去线程池里面拿对吧！关于事件处理，一条线程应该有一个连接对象不能两条线程共享一个连接对象，这个很好理解把！比如一个学生对象我第一条线程把他改成小王，第二条线程又改成小李那不就乱了吗？
对了！用户登岸以后，连接池里面还会获取到用户的权限，并且根据用户的权限来判断用户是否可以执行下令。
短连接与长连接

简单理解就是短链接就是连接mysql然后断开mysql速度很快，执行sql下令较少。长连接则是执行sql下令较多镌汰了建立连接和断开连接的过程。一般都是长连接，但是长连接会占用内存，这些连接对象资源只有断开时才会释放。
   以是我们要解决长连接的占用内存的题目！
   以是我们要定期断开长连接释放占用的内存资源
数据库连接池的理解和使用_谈一谈你对数据库连接池的理解-CSDN博客
【MySQL】——mysql最大连接数_mysql最大连接数是多少-CSDN博客
MySql数据库连接池专题 - aspirant - 博客园 (cnblogs.com)
执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)
查看最大连接数
max_connections参数用来设置最大连接（用户）数。连接池的最大线程数可以通过参数max-connections来控制，如果到来的客户端连接超出该值时，新到来的连接都会被拒绝。

1. mysql> show variables like "%max_connections%";
2. ±----------------±------+
3. | Variable_name  | Value|
4. ±----------------±------+
5. | max_connections|  151 |
6. ±----------------±------+
7. 1 row in set (0.00 sec)
9. mysql> set global max_connections = 2;
10. Query OK, 0 rows affected (0.00 sec)
12. mysql> show variables like 'max_connections';
13. +-----------------+-------+
14. | Variable_name   | Value |
15. +-----------------+-------+
16. | max_connections | 2     |
17. +-----------------+-------+
18. 1 row in set (0.00 sec)
20. [root@mysql57 ~]# mysql -uroot -p
21. Enter password:
22. ERROR 1040 (HY000): Too many connections
25. mysql> set global max_connections = 151;
26. Query OK, 0 rows affected (0.00 sec)

复制代码

 查看服务器响应的最大连接数

1. mysql> show global status like 'Max_used_connections';
2. +----------------------+-------+
3. | Variable_name        | Value |
4. +----------------------+-------+
5. | Max_used_connections | 11    |
6. +----------------------+-------+
7. 1 row in set (0.00 sec)

复制代码

 这里的话max_connections是管理最大连接数然服务器管理的是响应最大连接数，服务器的响应连接数肯定要小于最大连接数
max_user_connections不能设的太大会占用内存
一个连接会占内存在服务器最小512字节-最大64Mb
最小消耗：1000个连接 * 512字节
根据业务场景有10台比如用的jdbc应用连接池配的50个连接一共有10台就是500个连接 
连接设太多会占内存，要把操纵体系内存预留出来
查看历史连接mysql数不管乐成与否 connections

1. mysql> show global status like '%connections%';
2. +-----------------------------------+---------------------+
3. | Variable_name                     | Value               |
4. +-----------------------------------+---------------------+
5. | Connection_errors_max_connections | 0                   |
6. | Connections                       | 9                   |
7. | Max_used_connections              | 1                   |
8. | Max_used_connections_time         | 2024-08-05 06:21:45 |
9. | Mysqlx_connections_accepted       | 0                   |
10. | Mysqlx_connections_closed         | 0                   |
11. | Mysqlx_connections_rejected       | 0                   |
12. +-----------------------------------+---------------------+
13. 7 rows in set (0.00 sec)

复制代码

MySQL 界说了空闲连接的最大空闲时长默认是8小时28800 / 60 / 60 = 8

1. mysql> show variables like 'wait_timeout';
2. +---------------+-------+
3. | Variable_name | Value |
4. +---------------+-------+
5. | wait_timeout  | 28800 |
6. +---------------+-------+
7. 1 row in set (0.01 sec)

复制代码

可以通过kill connection + id方式断开空闲的连接

1. mysql> show processlist;
2. +----+-----------------+-----------+------+---------+------+------------------------+------------------+
3. | Id | User            | Host      | db   | Command | Time | State                  | Info             |
4. +----+-----------------+-----------+------+---------+------+------------------------+------------------+
5. |  5 | event_scheduler | localhost | NULL | Daemon  |  229 | Waiting on empty queue | NULL             |
6. |  8 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
7. |  9 | root            | localhost | NULL | Sleep   |   17 |                        | NULL             |
8. +----+-----------------+-----------+------+---------+------+------------------------+------------------+
9. 3 rows in set (0.00 sec)
11. mysql> kill connection + 9;
12. Query OK, 0 rows affected (0.00 sec)
14. 被kill的连接不会马上知道执行一个命令才会
15. mysql> use test;
16. No connection. Trying to reconnect...
17. Connection id:    10
18. Current database: *** NONE ***
20. Reading table information for completion of table and column names
21. You can turn off this feature to get a quicker startup with -A
23. Database changed

复制代码

2. mysql> show global status like '%threads%';
3. +------------------------------+-------+
4. | Variable_name                | Value |
5. +------------------------------+-------+
6. | Delayed_insert_threads       | 0     |
7. | Mysqlx_worker_threads        | 2     |
8. | Mysqlx_worker_threads_active | 0     |
9. | Slow_launch_threads          | 0     |
10. | Threads_cached               | 0     |
11. | Threads_connected            | 1     |
12. | Threads_created              | 1     |
13. | Threads_running              | 2     |
14. +------------------------------+-------+
15. 8 rows in set (0.00 sec)

复制代码

• Threads_cached：现在空闲的数据库连接数。
  
• Threads_connected：当前数据库存活的数据库连接数。
  
• Threads_created：MySQL-Server运行至今，累计创建的连接数。
  
• Threads_running：现在正在执行的数据库连接数
  

对于几个字段很容易理解，额外要阐明的一点是Threads_cached这个字段，从名称上来看，好像跟缓存有关系，其实也没错，因为这里是有一个数据库内部的优化机制。当一个客户端连接断开后，对于数据库连接却不会立马烧毁，而是会先放入到一个缓存连接池当中。这样就能在下次新连接到来时，省去了创建线程、分配栈空间等一系列动作，但这个值不会是无限大的，一般都在32左右。
MySql线程池

MySQL 线程池[2021-06-26]_mysql 加线程池-CSDN博客

1. mysql> show variables like 'thread%';
2. +-------------------+---------------------------+
3. | Variable_name     | Value                     |
4. +-------------------+---------------------------+
5. | thread_cache_size | 9                         |
6. | thread_handling   | one-thread-per-connection |
7. | thread_stack      | 286720                    |
8. +-------------------+---------------------------+

复制代码

thread_handling = one-thread-per-connection代表不接纳线程池

近来研究的MySQL线程池题目，都整理在这了 - 简书 (jianshu.com)
MySQL :: MySQL 5.7 Reference Manual :: 5.5.3.1 Thread Pool Elements

1. mysql> show processlist;
2. +----+-----------------+-----------+------+---------+------+------------------------+------------------+---------+-----------+---------------+
3. | Id | User            | Host      | db   | Command | Time | State                  | Info             | Time_ms | Rows_sent | Rows_examined |
4. +----+-----------------+-----------+------+---------+------+------------------------+------------------+---------+-----------+---------------+
5. |  5 | event_scheduler | localhost | NULL | Daemon  | 1411 | Waiting on empty queue | NULL             | 1411218 |         0 |             0 |
6. |  8 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |       0 |         0 |             0 |
7. +----+-----------------+-----------+------+---------+------+------------------------+------------------+---------+-----------+---------------+
8. 2 rows in set, 1 warning (0.00 sec)

复制代码

• Id：当火线程的ID值，可以使用这个ID，使用kill强杀线程。
  
• User：当火线程维护的数据库连接，与之对应的用户是谁。
  
• Host：与当火线程保持连接关系的客户端地点（IP+Port）。
  
• db：目火线程在哪个数据库中执行SQL。
  
• Command：当火线程正在执行的SQL范例，如：
  
  
  
  • Create DB：正在执行创建数据库的操纵。
    
  • Drop DB：正在执行删除数据库的操纵。
    
  • Execute：正在执行预编译的SQL（PreparedStatement）。
    
  • Close Stmt：正在关闭一个PreparedStatement。
    
  • Query：正在执行平凡的SQL语句。
    
  • Sleep：正在等候客户端发送SQL语句。
    
  • Quit：当前客户端正在退出连接。
    
  • Shutdown：正在关闭MySQL服务端。
    
  
  
• Time：表示当火线程处于现在状态的时间，单元是秒。
  
• State：表示当火线程的状态，有如下几种：
  
  
  
  • Updating：当前正在执行update语句，匹配数据做修改操纵。
    
  • Sleeping：正在等候客户端发送新的SQL语句。
    
  • Starting：现在正在处理客户端的请求。
    
  • Checking table：现在正在表中查询数据。
    
  • Locked：当火线程被阻塞，其他线程获取了执行必要的锁资源。
    
  • Sending Data：现在执行完成了Select语句，正在将结果返回给客户端。
    
  
  
• Info：一般记载当火线程正在执行的SQL，默认体现前一百个字符，查看完整的SQL可以使用show full processlist;下令。
  

(一)全解MySQL之架构篇：自顶向下深入剖析MySQL团体架构！ - 掘金 (juejin.cn)
 thread_cache_size参数

 线程缓存未命中率=Threads_created /Connections

1. mysql> show variables like 'thread_cache_size%';
2. +-------------------+-------+
3. | Variable_name     | Value |
4. +-------------------+-------+
5. | thread_cache_size | 9     |
6. +-------------------+-------+
7. 1 row in set (0.00 sec)

复制代码

 Threads_cached是当前缓存中空闲的线程连接数（是一个状态变量不可修改），而thread_cache_size是线程缓存的上限值。
Threads_cached反映了当前缓存的使用情况，而thread_cache_size则用于控制缓存的巨细。
计算thread_cache_size巨细 这个是引用官方线程池的计算公式
8 + (max_connections / 100)
thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是烧毁(条件是缓存数未达上限)
即可以重新使用保存在缓存中线程的数目，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善体系性能
如果是短连接，得当设置大一点，因为短连接往往必要不绝创建，不绝烧毁，如果大一点，连接线程都处于取用状态，不必要重新创建和烧毁，以是对性能肯定是比较大的提拔。
对于长连接，不能包管连接的稳固性，以是设置这参数还是有肯定必要，可能连接池的题目，会导致连接数据库的不稳固性，也会出现频繁的创建和烧毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。
mysql体系调优之thread_cache_size_mysql thread-cache-size-CSDN博客
Mysql线程池配置Percona Server for MySQL安装

Software Downloads - Percona
安装 Percona Distribution for MySQL - Percona Distribution for MySQL --- Install Percona Distribution for MySQL - Percona Distribution for MySQL
情况是Linux操纵体系加上centos7.9镜像

1. sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
2. sudo percona-release setup ps80
3. sudo yum install percona-server-server -y
4. sudo yum install gnupg2 curl -y
5. sudo yum -y install epel-release
6. sudo percona-release enable pdps-8x-innovation
7. sudo yum -y install epel-release
8. sudo systemctl start mysql
10. cat /var/log/mysqld.log
11. root@localhost: +Aool)m6yT5B 输入密码
12. mysql -uroot -p
13. alter user 'root'@'localhost' identified with mysql_native_password by '密码';
15. etc/my.cnf
16. thread_handling=pool-of-threads   ## 默认one-thread-per-connection
17. max_connections=2048
19. sudo systemctl start mysql

复制代码

Mysql连接池和Mysql线程池的区别

连接池 (Connection Pool)

• 位置：连接池通常位于客户端或应用步调服务器上，而不是数据库服务器上。
  
• 目的：连接池的重要目的是为了镌汰建立和烧毁数据库连接的开销。每次从应用步调向数据库发送请求时，如果必要重新创建连接，这个过程可能会非常耗时。通过使用连接池，可以复用已经存在的连接，从而提高性能。
  
• 工作原理：连接池维护了一组预创建的数据库连接，并将这些连接作为对象存储在一个池中。当应用步调必要访问数据库时，它可以从池中获取一个空闲连接，使用完毕后，连接被归还给池，而不是关闭。
  

线程池 (Thread Pool)

• 位置：线程池可以在客户端也可以在服务器端实现，详细取决于应用场景。在数据库服务器上，线程池用于管理并发查询处理。
  
• 目的：线程池用于管理执行使命所需的线程资源。它镌汰了频繁创建和烧毁线程所带来的开销，提高了体系的响应速度和吞吐量。
  
• 工作原理：线程池预先创建一组线程，并将它们保持在就绪状态。当有新的使命（如数据库查询）到来时，线程池中的空闲线程会被分配来处理该使命。一旦使命完成，线程返回到线程池中等候下一个使命。
  

概念对比

• 连接池：重要关注的是数据库连接的生命周期管理，镌汰连接创建和关闭的时间消耗。
  
• 线程池：关注的是线程的生命周期管理，镌汰线程创建和烧毁的时间消耗，并且可以或许更好地控制并发水平。
  

总的来说，连接池和线程池都是为了提拔体系性能和响应能力而计划的机制，但它们解决的题目不同。连接池专注于数据库连接的高效使用，而线程池则侧重于使命执行的线程管理。
连接池一般在客户端设置，而线程池是在DB服务器上配置；别的连接池可以取到避免了连接频繁创建和烧毁，但是无法取到控制MySQL活动线程数的目的，在高并发场景下，无法取到保护DB的作用。比较好的方式是将连接池和线程池结合起来使用。
总结：

本人根本上是个人理解加参考其他大佬的肯定有很多题目接待指正，我会实时修改。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。