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;

+---------+---------------------+------+-----+---------+-56c------+| 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,infofrom 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