mysql preparing状态_观察MySQL的运行状态

这部分主要讲解两个命令：show status，show processlist。

一、SHOW STATUS

SHOW STATUS命令提供了MySQL Server运行的基本信息，使用此命令不需要任何额外的权限分配，能正常连上MySQL Server即可。

完整的语法结构如下所示：

SHOW [GLOBAL | SESSION] STATUS

[ LIKE ‘

pattern‘ | WHERE

expr]

不难看出，该命令提供了两个主要的过滤功能：

1、GLOBAL | SESSION，全局 or 当前会话；

2、LIKE | WHERE，状态结果集的过滤，也就是可以从众多服务指标中过滤我们想要看的。

看一个实例：

mysql> SHOW GLOBAL STATUS LIKE ‘%connections%’;

+———————————–+——–+

| Variable_name | Value |

+———————————–+——–+

| Connection_errors_max_connections | 0 |

| Connections | 243534 |

| Max_used_connections | 158 |

+———————————–+——–+

3 rows in set

STATUS变量有上百个，篇幅所限，就不逐一解释，重点介绍如下变量：

1、Aborted_clients。由于客户端没有正常关闭MySQL连接数量，如果该数值过高，尤其是出现阶段性异常，需要重新审视数据库连接相关的操作，审查代码是否正确关闭了数据库连接。

2、Aborted_connects。由于客户端尝试连接，而被MySQL Server拒绝的数量。

3、Bytes_received，Bytes_sent，分别表示MySQL Server从客户端接收的字节数和发送给客户端的字节数，也能一定程度上反映MySQL服务的繁忙程度。

4、Com_xxxx。其中，xxxx指代的是一种statement，比如select，insert，update，truncate等，甚至是show status，show processlist这类命令也有。这个参数值表示statement被执行的次数。

5、Connections。尝试连接MySQL Server的连接数量，成功和失败的连接都算在内。

6、Max_used_connections。自MySQL Server启动以来，最多有多少个连接同时在使用。

7、Open_files。打开的文件数量，不包括socket文件和pipe文件。确切来说，是客户端要求MySQL Server打开的文件数量，而不包括MySQL Server内部需要打开的文件。

8、Open_tables。正在被打开的表的数量。

9、Opened_tables。打开过的表的数量。

10、Queries。记录的是存储过程执行statement的数量。

11、Questions。发往MySQL Server的statement的数量，不包含存储过程执行的statement的数量。

12、Slow_queries。慢查询的数量，MySQL有个名为long_query_time的参数，单位是秒，超过此阈值就视为慢查询。

13、Threads_connected。当前打开的连接的数量。

14、Threads_running。不在睡眠的线程数量。

15、Threads_cached。线程池中的存量线程，线程池缓存大小可以通过thread_cache_size参数进行调整。

16、Threads_created。创建了多少个线程用于处理客户端连接，这个数值的升高，非常不利于线程池的缓存，通过 Threads_created/Connections 可以计算缓存失效率。

17、Uptime。MySQL Server运行的时间，单位是秒。

TIPS:

MySQL提供了FLUSH STATUS命令，通常用于性能调试，可以将当前会话保持的状态进行重置(归零)，部分数值将会累加到GLOBAL的作用域中。

二、SHOW PROCESSLIST

SHOW PROCESSLIST命令可以认为是线程级别的，也就是可以查看当前MySQL Server上运行的线程。

与SHOW STATUS命令不同，SHOW PROCESSLIST命令是有权限分别的。有PROCESS 权限的用户，可以看到所有线程状态，否则，只能看到当前用户所创建的线程状态。

完整的语法结构如下所示：

SHOW [FULL] PROCESSLIST

要注意加不加FULL的区别，不加FULL并不是只获取前100行，而是显示INFO字段的前100个字符，而INFO字段的内容就是该线程正在执行的Statement(包括但不限于SQL语句)。

经笔者运行比对，在阿里云的RDS上，加不加FULL关键字，得到的结果还有一个明显的差别，就是加了FULL关键字后，会多出若干列，比如Memory_used，Logical_read等资源消耗指标。

下面着重介绍共同返回的8个字段：

1、Id，线程标识(ID)。

2、User，打开线程的用户，或者是线程的Owner。

3、Host，客户端连接地址，表示的是连接MySQL Server的客户端上的TCP套接字。

4、db，线程所连接的数据库名称。

5、Command，表示线程正在执行的命令，可能的值多达30多种，下面列举几个命令作为重点了解：

Binlog Dump。在MySQL主从结构中，此命令表示Master节点正在发送binlog给Slave节点，实际上是一个数据同步的过程。

Connect/Connect Out。前者是Slave节点已经连接上了对应的Master节点，后者则表示正在连接中的状态。

Close stmt。相当于是statement.close()，意思是关闭一个statement句柄。

Execute。正在执行statement预编译。前期的预编译是对SQL语法的检查，形成MySQL内部可执行的形式，能提高后续SQL语句的执行效率。

Fetch/Long Data。都是获取Execute的结果，但是后者是专门针对一大串数据获取的状态。

Query。真正是在执行statement。

Create DB/Drop DB。正在执行create database/drop database操作。

Shutdown。该线程正要关闭MySQL Server。

Sleep。等待客户端发送新的statement过来，大部分线程是处于此状态的。将Time结合起来，可以发现一个线程空闲了多长时间。

6、State，表示Statement执行中的某一个瞬时状态。状态的切换都是非常迅速的，如果长期停滞在某个状态，则极有可能存在问题。MySQL对线程的状态管理非常细致，多达70多种，下面列举几种状态作为重点了解：

creating table/altering table/Checking table/Opening tables/closing tables，这一组是有关于表操作的，涉及了表的创建，修改，检查，开启和关闭。

Killed。线程接收到了kill命令，当下个轮回检查kill标识位的时候，该线程将会被终止。如果线程是在被锁住的过程中接收到了kill命令，那么一旦锁被释放了，线程就立马被终止了。

executing。线程正在执行Statement。

logging slow query。正在记录一个慢查询语句。

NULL。只有SHOW PROCESSLIST命令才能出现的状态。

optimizing。准备要进入查询语句的优化阶段了。

preparing。正处于查询语句的优化阶段。

Reading from net。MySQL Server正在读取网络报文。

Writing to net。MySQL Server正在写入网络报文。

removing tmp table。正在移除SELECT语句产生的内部临时表，通常是在JOIN的时候产生的中间表。

Rolling back。正在进行事务回滚。

Sending data。把SELECT语句的执行结果发送给客户端，这个状态存在的时间是最长的，因为涉及到了磁盘的读取访问。因此，缩短这个状态持续的时间是非常必要的，比如，尽量少产生临时表，避免filesort等。

Sorting for group。正在为GROUP BY做排序。

Sorting for order。正在为ORDER BY做排序。

update/Updating。前者表示准备要进行更新操作了，后者表示寻找要更新的记录，进而更新之。

Waiting for tables。数据表结构一旦发生了变化，线程会接受到通知，需要重新获取最新的数据结构，也就会触发重新打开表(reopen table)的操作。但是这个操作可能不会立马得到执行，因为其他线程执行的某些操作会将表锁住，这个状态就表示正在等待锁释放，以重新获取数据表结构。

Waiting for xxxx lock。等待获取锁，xxxx表示锁的类型。数据库锁将在单独分篇讲述。

7、Time，在该state状态下所持续的时间。

8、Info，线程当前所执行的Statement，NULL表示未执行任何语句。