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;

参考资料

1. MySQL show status - active or total connections?
2. MySQL concepts: session vs connection
3. 推荐： MySQL Connection Handling and Scaling
4. Connection Phase
5. Command Phase
6. MySQL Error: Too many connections
7. 5.1.10 Server Status Variables