在开发的过程中,我们经常需要查看一下当前数据库的执行情况,比如如果我们select一个数据,很长时间都没有返回,我们在想,是不是什么sql语句把表锁着了,或者什么sql把资源占完了,那么我们怎么查这条sql呢?
办法肯定是有的:
show full processlist
mysql> show full processlist;
+-------+----------+----------------------+----------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+----------------------+----------------+---------+------+-------+-----------------------+
| 25 | tuan_sql | 192.168.100.39:60161 | tao800 | Sleep | 18 | | NULL |
| 12196 | tuan_sql | 192.168.100.39:34410 | tao800 | Sleep | 89 | | NULL |
| 13067 | tuan_sql | 192.168.100.39:40964 | tao800 | Sleep | 123 | | NULL |
| 13158 | tuan_sql | 192.168.100.39:41496 | zhe_oem | Sleep | 3213 | | NULL |
| 13181 | tuan_sql | 192.168.100.39:41574 | tao800 | Sleep | 86 | | NULL |
| 13620 | tuan_sql | 192.168.100.39:44335 | tao800 | Sleep | 1 | | NULL |
| 13795 | tuan_sql | 192.168.100.39:45502 | tao800 | Sleep | 261 | | NULL |
| 14961 | tuan_sql | 192.168.100.39:55012 | tao800 | Sleep | 46 | | NULL |
| 15244 | tuan_sql | 192.168.100.39:57875 | tao800 | Sleep | 122 | | NULL |
| 15305 | tuan_sql | 192.168.100.39:58398 | zhe800_lottery | Sleep | 2705 | | NULL |
| 15306 | tuan_sql | 192.168.100.39:58399 | zhe800_lottery | Sleep | 2723 | | NULL |
| 15334 | tuan_sql | 192.168.100.39:58712 | tao800 | Sleep | 2151 | | NULL |
| 15422 | tuan_sql | 192.168.100.39:59571 | tao800 | Sleep | 27 | | NULL |
| 15434 | tuan_sql | 192.168.100.39:59633 | tao800 | Sleep | 18 | | NULL |
| 15521 | tuan_sql | 192.168.100.39:60435 | tao800 | Sleep | 1671 | | NULL |
| 15598 | tuan_sql | 192.168.100.39:32768 | tao800 | Sleep | 1151 | | NULL |
| 15661 | tuan_sql | 192.168.100.39:33489 | tao800 | Sleep | 643 | | NULL |
| 15677 | tuan_sql | 192.168.100.39:33663 | tao800 | Sleep | 264 | | NULL |
| 15682 | tuan_sql | 192.168.100.39:33716 | tao800 | Sleep | 264 | | NULL |
| 15690 | tuan_sql | 192.168.100.39:33791 | tao800 | Query | 0 | NULL | show full processlist |
| 15707 | tuan_sql | 192.168.100.39:34000 | zhe_oem | Sleep | 285 | | NULL |
| 15708 | tuan_sql | 192.168.100.39:34002 | zhe_oem | Sleep | 285 | | NULL |
+-------+----------+----------------------+----------------+---------+------+-------+-----------------------+
22 rows in set (0.00 sec)
我们详细介绍一下每个属性是什么意思吧
id
User
用户名
Host
连接的地址和端口,那么你可能会问,为什么我贴的例子的地址都是同一个host呢,很明显是用了代理,感兴趣的可以看看这篇文章http://www.phpv.net/html/1583.html,这里就不详细解释了。
db
此连接所连接使用的数据库名。
command
Time
State
info
mysql> show full processlist;
+-------+----------+----------------------+---------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+----------------------+---------+---------+------+-------+-----------------------+
| 25 | tuan_sql | 192.168.100.39:60161 | tao800 | Sleep | 2 | | NULL |
| 12196 | tuan_sql | 192.168.100.39:34410 | tao800 | Sleep | 43 | | NULL |
| 13067 | tuan_sql | 192.168.100.39:40964 | tao800 | Sleep | 77 | | NULL |
| 13181 | tuan_sql | 192.168.100.39:41574 | tao800 | Sleep | 69 | | NULL |
| 13620 | tuan_sql | 192.168.100.39:44335 | tao800 | Sleep | 2 | | NULL |
| 13795 | tuan_sql | 192.168.100.39:45502 | tao800 | Sleep | 215 | | NULL |
| 14961 | tuan_sql | 192.168.100.39:55012 | tao800 | Sleep | 0 | | NULL |
| 15244 | tuan_sql | 192.168.100.39:57875 | tao800 | Sleep | 76 | | NULL |
| 15334 | tuan_sql | 192.168.100.39:58712 | tao800 | Sleep | 3305 | | NULL |
| 15422 | tuan_sql | 192.168.100.39:59571 | tao800 | Sleep | 281 | | NULL |
| 15434 | tuan_sql | 192.168.100.39:59633 | tao800 | Sleep | 272 | | NULL |
| 15521 | tuan_sql | 192.168.100.39:60435 | tao800 | Sleep | 2825 | | NULL |
| 15598 | tuan_sql | 192.168.100.39:32768 | tao800 | Sleep | 2305 | | NULL |
| 15690 | tuan_sql | 192.168.100.39:33791 | tao800 | Query | 0 | NULL | show full processlist |
| 15813 | tuan_sql | 192.168.100.39:35257 | zhe_oem | Sleep | 239 | | NULL |
| 15814 | tuan_sql | 192.168.100.39:35258 | zhe_oem | Sleep | 239 | | NULL |
+-------+----------+----------------------+---------+---------+------+-------+-----------------------+
16 rows in set (0.00 sec)
mysql> kill 15814;
Query OK, 0 rows affected (0.00 sec)
哈哈,还是可以的。