show processlist 命令详解，MySQL优化看这一篇就够了

转载

mb600becf249518 2021-01-29 10:23:21

老哥哔哔叨（求点赞）

我们已经写了很多 MySQL 的文章了，比如索引优化、数据库锁、主从复制等等。今天在来和大家学习一个优化方法：show processlist——查看当前所有数据库连接的 session 状态。帮助我们查看每个 SQL 线程的运行状态，是运行正常呀，还是 sleep 了，还是其他什么情况。

show processlist 简介

语法

不同用户之间只能查看自己的数据，如果想查看所有的请用管理员查询

show processlist;
复制代码

返回结果字段说明

id
SQL 的 ID 标识，需要 kill 这个 SQL 进程的时候可以使用
User
当前连接用户
Host
所属的 IP 和端口
db
数据库名
command
连接状态，一般是休眠（sleep），查询（query），连接（connect），如果一条 SQL 语句是query状态，而且time时间很长，说明存在问题
time
连接状态持续的时间，单位是秒（s）
state（重点分析）
当前 SQL 语句的状态，是优化的重要参数
info
显示当前所执行的 SQL 语句

state 详解

state 在优化中是很重要的字段，能提供给我们很多这条 SQL 线程的当前状态，帮助我们能定位分析问题。下面列举出 state 的一些常见的字段。

state
解释：代表资源未释放，如果通过连接池连接数据库，那么 state 应该是一个稳定的范围。如果有大量的 SQL 请求忘记关闭数据库连接，会造成大量连接请求阻塞，数据库挂掉。
checking table
解释：正在检查数据数据表，这个操作是系统自动的
closing tables
解释：表示正在将表中修改的数据刷新到磁盘中去，然后关闭用完的表，这是一个很快的操作。
优化建议：如果这个过程很慢，那就需要看看磁盘是否满了，或者磁盘在进行大量的 IO 操作等等
connect out
解释：主从复制里，从服务器正在连接主服务器
creating tmp table
解释：正在创建临时表，临时存放查询结果
copying to tmp table on disk
解释：当使用 order by、group by 或者 join 查询时，会出创建临时表的情况，当数据太大，会把内存中的临时表数据存储到硬盘上。
优化建议：一：优化索引，尽量减少创建临时表。二：优化 SQL 语句逻辑,可以用 Java 代码实现部分耗时的 SQL 逻辑。三：可以调节tmp_table_size和max_heap_table_size两个参数，增大内存中临时表的大小。
flushing tables
在执行刷新表，等待其他线程关闭数据库表
killed
解释：发送了一个 kill 请求给某线程，那么这个线程将会检查 kill 标志位，同时会放弃下一个 kill 请求。MySQL 会在每次的主循环中检查 kill 标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么 kill 请求会在锁释放时马上生效。
sending data
解释：这个字段字面上很容易误导人，大部分人觉得他仅仅是发送数据给客户端，但其实是收集 + 发送。当 MySQL 使用索引查询完后，得到一堆行的 id，如果有的查询列不在索引中，那么 MySQL 需要到 id 所在的数据行，将数据取出来返回给客户端。
sorting for group / order
解释：SQL 语句中使用了 group 和 order 进行排序
优化建议：如果出现了创建临时表或者文件内排序的情况，比较耗时的情况下需要优化索引
Waiting for net / reading from net / writing to net
解释：主要是网络状态的描述，如大量出现，要检查数据库网络连接状态和流量
优化建议：比如外挂流量攻击数据库时，会导致网络带宽被占满，大量的连接请求打到数据库，造成数据库崩溃，建议进行防流量攻击。
locked
解释：SQL 被锁住了，如表锁，行锁，间隙锁等等。
优化建议：正确使用索引，避免索引失效升级为表锁。使用 innodb 搜索引擎，不要用 myisam。
Opening tables
解释：一个 SQL 线程正在尝试打开数据表，这个过程正常的情况是很快的，但是如果有人在 alter table，或者 lock table 语句之前完之前，其他线程无法打开这个数据表。
Waiting for tables
解释：该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。
以下几种情况下会产生这个通知：FLUSH TABLES tbl_name、 ALTER TABLE、 RENAME TABLE、 REPAIR TABLE、 ANALYZE TABLE、或 OPTIMIZE TABLE。
System lock
解释：正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加--skip-external-locking 参数来禁止外部系统锁。默认情况下这个参数是关闭的。