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