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;
