MySQL基础知识：MySQL Connection和Session

在connection的生命里，会一直有一个user thread（以及user thread对应的THD）陪伴它。

Connection和Session概念

来自Stackoverflow的一个回答：

 A session is just a result of a successful connection. 
Any MySQL client requires some connection settings to establish a connection, 
and after the connection has been established,
it acquires a connection id (thread id) and some context which is called session.
 

来自官方团队的描述：

 Connections correspond to Sessions in SQL standard terminology. 
A client connects to the MySQL Server and stays connected until it does a disconnect. 
 

MySQL Client和MySQL Server建立连接的过程

Connection Phase

Connection Requests: 是一个简单的TCP-IP连接消息，发送到MySQL Server的端口（如：3306）; Receiver Thread：唯一职责是创建 user thread ；要么新建一个OS thread，要么重用 thread cache里的可用thread； User Thread: client-server protocol 处理器，比如返回 handshake packet ，接收查询、返回结果等等； THD THD： 表示connection上下文的数据结构；连接建立后被创建，断开连接后被销毁； 用户的connection和THD是一一对应的，THD不会被connection共用； THD数据结构的大小约为 ~10KB，注意用来跟踪query执行状态各个方面；

注意：THD 一直没查到是什么的简写。从查阅的资料看，THD应该也可以被认为是 Session 或者 connection的状态/上下文 。

Command Phase

当connection phase一切安好后， user thread 会进入 command phase ；开始忙碌的一生。 断开连接

Client发送 COM_QUIT 命令开始断开连接操作。

User Thread开始做清理工作：

释放THD； thread cache 还有空位置： 把自己 放到 thread cache 里并标记为 suspended 状态； thread cache 没有空位置：结束线程。

查看MySQL Sessions/Active Connections

MySQL的连接信息，记录在 information_schema 和 performance_schema 数据库中。

  desc information_schema.processlist;
 

 +---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| ID      | bigint(21) unsigned | NO   |     |         |       |
| USER    | varchar(32)         | NO   |     |         |       |
| HOST    | varchar(64)         | NO   |     |         |       |
| DB      | varchar(64)         | YES  |     |         |       |
| COMMAND | varchar(16)         | NO   |     |         |       |
| TIME    | int(7)              | NO   |     |         |       |
| STATE   | varchar(64)         | YES  |     |         |       |
| INFO    | varchar(65535)      | YES  |     |         |       |
+---------+---------------------+------+-----+---------+-------+
 

 desc performance_schema.hosts;
 

 +---------------------+------------+------+-----+---------+-------+
| Field               | Type       | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| HOST                | char(60)   | YES  | UNI | NULL    |       |
| CURRENT_CONNECTIONS | bigint(20) | NO   |     | NULL    |       |
| TOTAL_CONNECTIONS   | bigint(20) | NO   |     | NULL    |       |
+---------------------+------------+------+-----+---------+-------+
 

查看连接

方法1：

 show status where variable_name = ‘threads_connected‘;
 

方法2：

 show processlist;
 

方法3：

 select id,
       user,
       host,
       db,
       command,
       time,
       state,
       info
from information_schema.processlist;
 

查看每个host的当前连接数和总连接数

 select * FROM performance_schema.hosts;
 

参考资料

MySQL show status - active or total connections? MySQL concepts: session vs connection 推荐： MySQL Connection Handling and Scaling Connection Phase Command Phase MySQL Error: Too many connections 5.1.10 Server Status Variables

MySQL基础知识：MySQL Connection和Session

标签：ali   结束   connected   setting   call   简单   signed   end   资料   

查看更多关于MySQL基础知识：MySQL Connection和Session的详细内容...