前言
在安装MySQL的时候会默认初始化几个MySQL运行所需的数据库:mysql, sys, information_schema, performance_schema.这几个库存储了MySQL在运行过程中的配置信息,运行信息,参数配置,数据库信息,表信息等等。今天这个要查看正在运行的SQL主要用到的是information_schema和performance_schema这两个库。
processlist
processlist表位于information_schema库中,主要是存储的MySQL线程的一些基本信息。我们使用
desc information_schema.processlist来查看表结构:
- ID: 线程的id
- USER: 线程属于哪一个用户
- HOST:客户端的host信息:hostname+端口
- DB:线程在哪一个数据库下
- COMMAND:线程使用哪一种命令在执行,空闲的线程状态为sleep
- TIME:线程已经运行的时间,秒为单位
- STATE:线程正在做什么:当前的状态,行为,或者事件
- INFO:线程正在执行的语句,但是这个并不是很准确,所以需要使用其他的方式来完成我们的目标。
使用show processlist 或者 select * from information_schema.processlist查看processlist表
threads
threads 位于performance_schema库中,每一行记录的是一条服务器线程。当performance_schema初始化的时候,它会根据当时存在的线程填充线程表,之后每当服务器创建线程时,都会添加一条新数据。当线程结束线程表中也会删除这条数据。使用 desc performance_schema.threads 来查看表结构:
- THREAD_ID:线程唯一id
- NAME:与服务器中的线程监测代码相关联的名称
- TYPE:线程类型。分为前台和后台。用户连线程是前台线程,与内部服务器活动相关的线程是后端线程。例如InnoDB内部线程。
- PROCESSLIST_ID:上边的processlist的id
- PROCESSLIST_USER:上边的processlist的user
- PROCESSLIST_HOST:上边的processlist的host
- PROCESSLIST_DB:上边的processlist的DB
- PROCESSLIST_COMMAND:上边的processlist的COMMAND
- PROCESSLIST_TIME:上边的processlist的TIME
- PROCESSLIST_STATE:上边的processlist的STATE
- PROCESSLIST_INFO:上边的processlist的INFO
- PARENT_THREAD_ID:衍生线程的id值
- ROLE:未使用
- INSTRUMENTED:线程执行的事件是否插装,(YES or ON)
- HISTORY:是否为线程记录历史事件
- CONNECTION_TYPE:用于建立连接的协议,或者NULL用于后台线程。
- THREAD_OS_ID:MySQL线程对应于操作系统的线程id
events_statements_current
events_statements_current 位于performance_schema库中,它存储的是当前的语句事件,表为每个线程存储一行,显示贤臣哥最近监视的语句事件的当前状态。使用desc performance_schema.events_statements_current查看表结构:
- THREAD_ID:与事件关联的线程id,与上边threads的id对应
- EVENT_ID:事件启动时的线程当前事件号
THREAD_ID和EVENT_ID一起标志唯一一行,没有两行具有相同的键值对
- END_EVENT_ID:在事件开始时设置null,并在事件结束时更新为线程当前事件号
- EVENT_NAME:事件的名称
- SOURCE:包含生成事件的监测代码的源文件名称和监测发生所在文件的行号
- TIMER_START,TIMER_END,TIMER_WAIT:事件的时间信息,开始时间,结束时间,事件的运行时间,单位是皮秒(万分之一秒)。
- LOCK_TIME:等待表锁花费的时间。微妙为单位
- SQL_TEXT:SQL语句的文本,对于没有关联SQL语句的命令为null
- DIGEST:MD5的32个字符
- DIGEST_TEXT:规范化语句摘要文本。
- CURRENT_SCHEMA:语句的默认数据库
- OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME :对于嵌套语句这些列包含有关父语句的信息
- OBJECT_INSTANCE_BEGIN:语句在内存的对象地址
- MYSQL_ERRNO:语句错误号
- RETURNED_SQLSTATE:sql状态返回
- MESSAGE_TEXT:错误信息
- ERRORS:该语句是否发生错误。
- WARNINGS:警告次数
- ROWS_AFFECTED:受语句影响的行数
- ROWS_SENT:语句返回的行数
- ROWS_EXAMINED:服务器层检查的行数
- CREATED_TMP_DISK_TABLES
- CREATED_TMP_TABLES
- SELECT_FULL_JOIN
- SELECT_FULL_RANGE_JOIN
- SELECT_RANGE
- SELECT_RANGE_CHECK
- SELECT_SCAN
- SORT_MERGE_PASSES
- SORT_RANGE
- SORT_ROWS
- SORT_SCAN
- NO_INDEX_USED
- NO_GOOD_INDEX_USED
- NESTING_EVENT_ID
- NESTING_EVENT_TYPE
- NESTING_EVENT_LEVEL
如何查看正在运行的SQL
1、processlist表记录的是MySQL正在运行的线程信息,而每一个线程在threads表中都有用线程的一个唯一id >>> thread_id。events_statements_current表中记录着唯一线程id和该线程对应的SQL语句sql_text.
2、所以我们可以先在processlist拿到processlist对应的id
3、通过threads表的字段分析,我们之后一个processlist_id和thread_id一一对应,所以之后在threads表中通过processlist_id拿到thread_id
4、最后一步就是关键,我们通过thread_id在events_statements_current表中拿到sql_text,也就是我们需要拿到的sql语句。
拿到正在执行的processlist_id
select id from information_schema.processlist
拿到与processlist_id对应的thread_id
select thread_id from performance_schema.threads where processlist_id in (上一步拿到的processlist_id列表)
拿到正在执行的sql语句
select thread_id, sql_text from performance_schema.events_statements_current where thread_id in (上一步拿到的thread_id列表)
完整SQL
SELECT a.*, c.thread_id, c.sql_text from information_schema.processlist a LEFT JOIN performance_schema.threads b on a.id = b.PROCESSLIST_ID LEFT JOIN performance_schema.events_statements_current c on c.THREAD_ID = b.THREAD_ID