Linux中显示某个具体线程信息有以下几种方法：
1）TOP -H显示线程信息
top命令可以实时显示各个线程情况，调用top命令的“-H”选项，该选项会列出所有Linux线程。加上-p会筛选具体进程下面的线程信息，如下所示：
# top -H -p 1479
top - 17:10:44 up 11 min, 2 users, load average: 0.00, 0.05, 0.05
Threads: 39 total, 0 running, 39 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 1867024 total, 1051452 free, 495804 used, 319768 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 1185628 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1479 mysql 20 0 1318236 367380 16280 S 0.0 19.7 0:01.55 mysqld
1527 mysql 20 0 1318236 367380 16280 S 0.0 19.7 0:00.00 mysqld
1528 mysql 20 0 1318236 367380 16280 S 0.0 19.7 0:00.00 mysqld
2）在ps命令中，“-T”选项可以开启线程查看
下面的命令列出了由进程号为1479的进程创建的所有线程。
ps -aT -p <pid>
# ps -aT -p 1479
PID SPID TTY TIME CMD
1479 1479 ? 00:00:01 mysqld
1479 1527 ? 00:00:00 mysqld
1479 1528 ? 00:00:00 mysqld
3）命令ps -Lef查看线程
# ps -Lef|grep 1479
UID PID PPID LWP C NLWP STIME TTY TIME CMD
mysql 1479 1090 1479 0 39 16:59 ? 00:00:01 usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=tango-GDB-DB01.err --pid-file=/usr/local/mysql/data/tango-GDB-DB01.pid --socket=/tmp/mysql.sock
mysql 1479 1090 1527 0 39 16:59 ? 00:00:00 usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=tango-GDB-DB01.err --pid-file=/usr/local/mysql/data/tango-GDB-DB01.pid --socket=/tmp/mysql.sock
上述命令中PID给出进程号、LWP显示线程ID、C表示CPU使用率、NLWP表示 线程组内线程的个数。
1.4 pstack 查看线程堆栈信息
命令pstack可用来查看进程中的堆栈信息，需要注意的是运行pstack会短暂阻塞mysqld进程，所以请切勿在业务高峰期执行。
pstack 1479
2、系统线程ID和MySQL线程ID对应关系
2.1 MySQL中的ProcessID和ThreadID
1）连接MySQL，并执行以下语句
# mysql -h192.168.112.121 -P3306 -uroot -p –A
mysql> begin;
mysql> begin;select count(1),sleep(2000) from tango.t2 for update;
2）show processlist查看processlist信息，找到processlist_id
mysql> show processlist;
+----+-----------------+----------------------+-------+---------+------+------------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------------+-------+---------+------+------------------------+-------------------------------------------+ |
| 10 | root | tango-GDB-DB01:50620 | tango | Query | 52 | User sleep | select count(1),sleep(2000) from tango.t2 for update |
| 11 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+----------------------+-------+---------+------+------------------------+-------------------------------------------+
3 rows in set (0.00 sec)
3）查找MySQL内部线程对应的系统线程ID
从MySQL 5.7开始，performance_schema.threads 表增加THREAD_OS_ID列，用于记录MySQL内部线程对应的系统线程ID。
mysql> select * from performance_schema.threads where processlist_id=10\G
*************************** 1. row ***************************
THREAD_ID: 49
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 10
PROCESSLIST_USER: root
PROCESSLIST_HOST: tango-GDB-DB01
PROCESSLIST_DB: tango
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 84
PROCESSLIST_STATE: User sleep
PROCESSLIST_INFO: select count(1),sleep(2000) from tango.t2 for update
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: SSL/TLS
THREAD_OS_ID: 1657
RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)
以上找到MySQL内部的thread_id和对应操作系统的线程ID：THREAD_OS_ID
4）找到对应的操作系统线程信息
# top -H -p 1479
top - 17:41:22 up 41 min, 3 users, load average: 0.00, 0.01, 0.05
Threads: 39 total, 0 running, 39 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.0 us, 1.5 sy, 0.0 ni, 98.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 1867024 total, 483128 free, 513864 used, 870032 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 1158844 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1657 mysql 20 0 1318236 377188 16536 S 0.0 20.2 0:00.04 mysqld
通过top -H可以看到对应线程的CPU使用情况，包括内存和CPU的使用
2.2 查询当前statements和历史statements
1）查询当前statements表
mysql> select * from performance_schema.events_statements_current WHERE THREAD_ID = 49\G
*************************** 1. row ***************************
THREAD_ID: 49
EVENT_ID: 20
END_EVENT_ID: NULL
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 10615744184481000
TIMER_END: 10743215769959000
TIMER_WAIT: 127471585478000
LOCK_TIME: 121000000
SQL_TEXT: select count(1),sleep(2000) from tango.t2 for update
DIGEST: 91558228446c9877c86805735096b85b8afe5a8148c4ea9c315a1948464ab27f
DIGEST_TEXT: SELECT COUNT (?) , `sleep` (?) FROM `tango` . `t2` FOR UPDATE
CURRENT_SCHEMA: tango
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: 18
NESTING_EVENT_TYPE: TRANSACTION
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 37
1 row in set (0.00 sec)
2）执行SHOW ENGINE INNODB STATUS\G查看事务状态：
---TRANSACTION 2715147, ACTIVE 480 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 10, OS thread handle 140127942276864, query id 37 tango-GDB-DB01 192.168.112.121 root User sleep
select count(1),sleep(2000) from tango.t2 for update
Trx read view will not see trx with id >= 2715147, sees < 2715147
MySQL连接ID=10，OS线程句柄 = 140127942276864
3）查看历史thread_statement信息
mysql> select * from performance_schema.events_statements_history WHERE THREAD_ID = 49 limit 1 \G
*************************** 1. row ***************************
THREAD_ID: 49
EVENT_ID: 17
END_EVENT_ID: 18
EVENT_NAME: statement/sql/begin
SOURCE: init_net_server_extension.cc:96
TIMER_START: 10288037810168000
TIMER_END: 10288037917673000
TIMER_WAIT: 107505000
LOCK_TIME: 0
SQL_TEXT: begin
DIGEST: 55fa5810fbb2760e86d578526176c1497b134d4ef3dd0863dd78b1c5e819848c
DIGEST_TEXT: BEGIN
CURRENT_SCHEMA: tango
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: 00000
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 28
1 row in set (0.00 sec)
2.3 kill长时间运行或高CPU的thread
1）show processlists找到长时间运行的SQL
mysql> show processlist;
+----+-----------------+----------------------+-------+---------+-------+------------------------+------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------------+-------+---------+-------+------------------------+------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 11423 | Waiting on empty queue | NULL |
| 10 | root | tango-GDB-DB01:50620 | tango | Query | 816 | User sleep | select count(1),sleep(2000) from tango.t2 for update |
2）通过TOP -H找到CPU高消耗的thread
top -H -p 1479
反向查找到processlist id
mysql> select * from performance_schema.threads WHERE THREAD_OS_ID = 1657\G
*************************** 1. row ***************************
THREAD_ID: 49
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 10 <<<<<<<<processlist id
PROCESSLIST_USER: root
PROCESSLIST_HOST: tango-GDB-DB01
PROCESSLIST_DB: tango
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 1050
PROCESSLIST_STATE: User sleep
PROCESSLIST_INFO: select count(1),sleep(2000) from tango.t2 for update
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: SSL/TLS
THREAD_OS_ID: 1657
RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)
3）在mysql客户端kill processlist id
mysql> kill 10;
Query OK, 0 rows affected (0.00 sec)
杀掉当前长事务或者thread
3、OS thread handle和操作系统
在2.2步骤中查到的OS thread handle 140127942276864（OS thread handle是进程内部用于识别各个线程的内部ID），这里是个十进制的数值，需要先转成十六进制：
mysql> select lower(conv(140127942276864, 10, 16));
+--------------------------------------+
| lower(conv(140127942276864, 10, 16)) |
+--------------------------------------+
| 7f721438f700 |
+--------------------------------------+
1 row in set (0.00 sec)
2）利用 pstack 查询该句柄和操作系统线程ID的关联：
# pstack 1479 |grep 7f721438f700
Thread 3 (Thread 0x7f721438f700 (LWP 1657)):
可以看到 LWP=1657，对应上面的THREAD_OS_ID值，LWP是Light-Weight Processes的缩写。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
