MySQL-查看运行的线程-SHOW PROCESSLIST

1：目标明确，通过如下命令都能实现，具体效果如下图所示

show full processlist;

select * from information_schema.processlist;

 

SELECT * FROM sys.processlist order by conn_id;

 

2：字段含义，从名称上也能猜出一二，具体信息如下

CREATEALGORITHM = TEMPTABLEDEFINER = `mysql.sys` @`localhost` SQLSECURITYINVOKERVIEW `sys`.`processlist`(
  `thd_id`,
  `conn_id`,
  `user`,
  `db`,
  `command`,
  `state`,
  `time`,
  `current_statement`,
  `statement_latency`,
  `progress`,
  `lock_latency`,
  `rows_examined`,
  `rows_sent`,
  `rows_affected`,
  `tmp_tables`,
  `tmp_disk_tables`,
  `full_scan`,
  `last_statement`,
  `last_statement_latency`,
  `current_memory`,
  `last_wait`,
  `last_wait_latency`,
  `source`,
  `trx_latency`,
  `trx_state`,
  `trx_autocommit`,
  `pid`,
  `program_name`
) ASselect `pps`.`THREAD_ID` AS `thd_id`,
`pps`.`PROCESSLIST_ID` AS `conn_id`,
if(
  (
    `pps`.`NAME` in(
      'thread/sql/one_connection',
      'thread/thread_pool/tp_one_connection'
    )
  ),
  concat(
    `pps`.`PROCESSLIST_USER`,
    '@',
    convert(`pps`.`PROCESSLIST_HOST` usingutf8mb4)
  ),
  replace(`pps`.`NAME`, 'thread/', '')
) AS `user`,
`pps`.`PROCESSLIST_DB` AS `db`,
`pps`.`PROCESSLIST_COMMAND` AS `command`,
`pps`.`PROCESSLIST_STATE` AS `state`,
`pps`.`PROCESSLIST_TIME` AS `time`,
`sys`.`format_statement`(`pps`.`PROCESSLIST_INFO`) AS `current_statement`,
if(
  (`esc`.`END_EVENT_ID` isnull),
  `sys`.`format_time`(`esc`.`TIMER_WAIT`),
  NULL
) AS `statement_latency`,
if(
  (`esc`.`END_EVENT_ID` isnull),
  round(
    (
      100 *(`estc`.`WORK_COMPLETED` / `estc`.`WORK_ESTIMATED`)
    ),
    2
  ),
  NULL
) AS `progress`,
`sys`.`format_time`(`esc`.`LOCK_TIME`) AS `lock_latency`,
`esc`.`ROWS_EXAMINED` AS `rows_examined`,
`esc`.`ROWS_SENT` AS `rows_sent`,
`esc`.`ROWS_AFFECTED` AS `rows_affected`,
`esc`.`CREATED_TMP_TABLES` AS `tmp_tables`,
`esc`.`CREATED_TMP_DISK_TABLES` AS `tmp_disk_tables`,
if(
  (
    (`esc`.`NO_GOOD_INDEX_USED` > 0)
    or(`esc`.`NO_INDEX_USED` > 0)
  ),
  'YES',
  'NO'
) AS `full_scan`,
if(
  (`esc`.`END_EVENT_ID` isnotnull),
  `sys`.`format_statement`(`esc`.`SQL_TEXT`),
  NULL
) AS `last_statement`,
if(
  (`esc`.`END_EVENT_ID` isnotnull),
  `sys`.`format_time`(`esc`.`TIMER_WAIT`),
  NULL
) AS `last_statement_latency`,
`sys`.`format_bytes`(`mem`.`current_allocated`) AS `current_memory`,
`ewc`.`EVENT_NAME` AS `last_wait`,
if(
  (
    (`ewc`.`END_EVENT_ID` isnull)
    and(`ewc`.`EVENT_NAME` isnotnull)
  ),
  'Still Waiting',
  `sys`.`format_time`(`ewc`.`TIMER_WAIT`)
) AS `last_wait_latency`,
`ewc`.`SOURCE` AS `source`,
`sys`.`format_time`(`etc`.`TIMER_WAIT`) AS `trx_latency`,
`etc`.`STATE` AS `trx_state`,
`etc`.`AUTOCOMMIT` AS `trx_autocommit`,
`conattr_pid`.`ATTR_VALUE` AS `pid`,
`conattr_progname`.`ATTR_VALUE` AS `program_name`
from(
    (
      (
        (
          (
            (
              (
                `performance_schema`.`threads``pps` leftjoin `performance_schema`.`events_waits_current``ewc` on((`pps`.`THREAD_ID` = `ewc`.`THREAD_ID`))
              ) leftjoin `performance_schema`.`events_stages_current``estc` on((`pps`.`THREAD_ID` = `estc`.`THREAD_ID`))
            ) leftjoin `performance_schema`.`events_statements_current``esc` on((`pps`.`THREAD_ID` = `esc`.`THREAD_ID`))
          ) leftjoin `performance_schema`.`events_transactions_current``etc` on((`pps`.`THREAD_ID` = `etc`.`THREAD_ID`))
        ) leftjoin `sys`.`x$memory_by_thread_by_current_bytes``mem` on((`pps`.`THREAD_ID` = `mem`.`thread_id`))
      ) leftjoin `performance_schema`.`session_connect_attrs``conattr_pid` on(
        (
          (
            `conattr_pid`.`PROCESSLIST_ID` = `pps`.`PROCESSLIST_ID`
          )
          and(`conattr_pid`.`ATTR_NAME` = '_pid')
        )
      )
    ) leftjoin `performance_schema`.`session_connect_attrs``conattr_progname` on(
      (
        (
          `conattr_progname`.`PROCESSLIST_ID` = `pps`.`PROCESSLIST_ID`
        )
        and(`conattr_progname`.`ATTR_NAME` = 'program_name')
      )
    )
  ) orderby `pps`.`PROCESSLIST_TIME` desc,
  `last_wait_latency` desc

 

3：每个字段的详细含义，官方文档和其他博文已经比较清楚，我就不在重复了，详情可参看如下链接

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

https://dev.mysql.com/doc/refman/5.6/en/thread-commands.html

https://zhuanlan.zhihu.com/p/30743094

https://www.jb51.net/article/156313.htm

 

4：我核心想知道的就是那些链接现在处在什么情况？为了观察数据库连接池的连接状态信息，什么时候创建连接？什么时候销毁连接？什么时候重新创建连接？连接的存活时间是多少？等等都可以通过这个命令来观察，并且可以通过数据库来过滤自己想看的数据库连接信息。