本文揭示了MySQL中processlist和innodb_trx的区别，指出COMMAND为Query的进程并不全在执行状态，大部分可能在排队等待。通过实例演示如何通过这两个表关联查看真正运行在InnoDB中的SQL，以深入了解查询调度过程。
问题：MySQL数据库中 show processlist 命令或者 information_schema.processlist的输出中，如果COMMAND为Query，并不代表它就是真的处于执行状态。事实上，它可能还没有进入到innodb，而是在排队。如果真想看正在innodb里执行的SQL，可以查看表 information_schema.innodb_trx ，这个表展示的是已经进入到 innodb 的事务的状态。
这个问题比较容易重现，假设表 sbtest.mqs 中有一定数量的记录，可以使用下面的工具模拟200个并发，然后分别查询 processlist 表和 innodb_trx 表，就会发现两者输出中的SQL存在较大的差异，在这个例子中， information_schema.processlist 显示有190个应用在进行Query，但 information_schema.innodb_trx 显示innodb中只有40个事务。
mysqlslap -uroot -pxxxx --concurrency=200 --create-schema=sbtest --query="select * from mqs;select count(*) from mqs;"
mysql> select * from information_schema.processlist;
+--------+-----------+---------------------+--------+------------------+---------+---------------------------------------------------------------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+--------+-----------+---------------------+--------+------------------+---------+---------------------------------------------------------------+----------------------------------------------+
| 234318 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 233338 | root | localhost | sbtest | Query | 0 | executing | select * from information_schema.processlist |
| 234342 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234377 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234335 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234356 | root | localhost | sbtest | Query | 4 | Sending data | select count(*) from mqs |
| 234358 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234420 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234382 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234313 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234412 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234405 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234421 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234427 | root | localhost | sbtest | Query | 4 | Sending data | select count(*) from mqs |
| 234435 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234266 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234443 | root | localhost | sbtest | Query | 2 | Sending data | select count(*) from mqs |
| 234422 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234270 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234428 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234278 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234417 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234385 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234386 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234340 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234254 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234388 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234258 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234399 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234353 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234269 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234252 | root | localhost | sbtest | Query | 3 | Sending data | select count(*) from mqs |
| 234363 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234396 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234373 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234431 | root | localhost | sbtest | Query | 9 | Sending data | select count(*) from mqs |
| 234325 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234430 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234303 | root | localhost | sbtest | Query | 3 | Sending data | select count(*) from mqs |
| 234330 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234302 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234320 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234341 | root | localhost | sbtest | Query | 3 | Sending data | select count(*) from mqs |
| 234344 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234319 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234316 | root | localhost | sbtest | Query | 5 | Sending data | select count(*) from mqs |
| 234255 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234311 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234280 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234296 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234376 | root | localhost | sbtest | Query | 6 | Sending data | select count(*) from mqs |
| 234383 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234244 | root | localhost | NULL | Sleep | 16 | | NULL |
| 234442 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234259 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234429 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234274 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234326 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234403 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234249 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234253 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234246 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234395 | root | localhost | sbtest | Query | 6 | Sending data | select count(*) from mqs |
| 234277 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234362 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234407 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234400 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234374 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234432 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234357 | root | localhost | sbtest | Query | 10 | Sending data | select count(*) from mqs |
| 234409 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234308 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234312 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234345 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234263 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234392 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234378 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234359 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234292 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234310 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234424 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234289 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234365 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234398 | root | localhost | sbtest | Query | 8 | Sending data | select count(*) from mqs |
| 234384 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234439 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234368 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234408 | root | localhost | sbtest | Query | 13 | Sending data | select count(*) from mqs |
| 234307 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234370 | root | localhost | sbtest | Query | 9 | Sending data | select count(*) from mqs |
| 234304 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234301 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234444 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234389 | root | localhost | sbtest | Query | 6 | Sending data | select count(*) from mqs |
| 234328 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234347 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234256 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234387 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234281 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234346 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234309 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234360 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234348 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234261 | root | localhost | sbtest | Query | 13 | Sending data | select count(*) from mqs |
| 234276 | root | localhost | sbtest | Query | 16 | Sending to client | select * from mqs |
| 234355 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234285 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234268 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234354 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234306 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234425 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234272 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234295 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234293 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234314 | root | localhost | sbtest | Query | 0 | Sending data | select count(*) from mqs |
| 234375 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234286 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234337 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234394 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234372 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234273 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234305 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234436 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234317 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234406 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234291 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234332 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234288 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234297 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234416 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234437 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234418 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234401 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234434 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234361 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234248 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234262 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234411 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234366 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234287 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234391 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234282 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234350 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234271 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234390 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234251 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234352 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234245 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234327 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234298 | root | localhost | sbtest | Query | 16 | Sending to client | select * from mqs |
| 234260 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234402 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234334 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234283 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234279 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234250 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234404 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234414 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234257 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234343 | root | localhost | sbtest | Query | 1 | Sending data | select count(*) from mqs |
| 234275 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234264 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234300 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234324 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234426 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234419 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234351 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234284 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234315 | root | localhost | sbtest | Query | 4 | Sending data | select count(*) from mqs |
| 234336 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234321 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234433 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234247 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234440 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234393 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234441 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234299 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234339 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234410 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234415 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234267 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234349 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234338 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234322 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234265 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234397 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
| 234381 | root | localhost | sbtest | Query | 16 | Sending data | select * from mqs |
+--------+-----------+---------------------+--------+------------------+---------+---------------------------------------------------------------+----------------------------------------------+
190 rows in set (0.00 sec)
mysql> select trx_id,trx_state,trx_rows_modified,trx_mysql_thread_id,trx_query,trx_operation_state from information_schema.innodb_trx;
+-----------------+-----------+-------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------+---------------------------------+
| trx_id | trx_state | trx_rows_modified | trx_mysql_thread_id | trx_query | trx_operation_state |
+-----------------+-----------+-------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------+---------------------------------+
| 96563860 | RUNNING | 0 | 234370 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304172592 | RUNNING | 0 | 234443 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304170768 | RUNNING | 0 | 234431 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304165296 | RUNNING | 0 | 234429 | select * from mqs | fetching rows |
| 281475304149792 | RUNNING | 0 | 234426 | select * from mqs | fetching rows |
| 281475304147056 | RUNNING | 0 | 234427 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304144320 | RUNNING | 0 | 234276 | select * from mqs | sleeping before entering InnoDB |
| 281475304136112 | RUNNING | 0 | 234399 | select * from mqs | NULL |
| 281475304127904 | RUNNING | 0 | 234412 | select * from mqs | sleeping before entering InnoDB |
| 281475304126992 | RUNNING | 0 | 234377 | select * from mqs | sleeping before entering InnoDB |
| 281475304115136 | RUNNING | 0 | 234408 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304106928 | RUNNING | 0 | 234406 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304102368 | RUNNING | 0 | 234374 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304100544 | RUNNING | 0 | 234389 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304098720 | RUNNING | 0 | 234398 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304095984 | RUNNING | 0 | 234246 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304091424 | RUNNING | 0 | 234395 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304088688 | RUNNING | 0 | 234376 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304077744 | RUNNING | 0 | 234366 | select * from mqs | NULL |
| 281475304073184 | RUNNING | 0 | 234359 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304071360 | RUNNING | 0 | 234356 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304070448 | RUNNING | 0 | 234293 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304066800 | RUNNING | 0 | 234357 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304064976 | RUNNING | 0 | 234316 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304049472 | RUNNING | 0 | 234343 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304042176 | RUNNING | 0 | 234341 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304034880 | RUNNING | 0 | 234279 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304031232 | RUNNING | 0 | 234314 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304028496 | RUNNING | 0 | 234262 | select * from mqs | NULL |
| 281475304025760 | RUNNING | 0 | 234315 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304020288 | RUNNING | 0 | 234254 | select * from mqs | sleeping before entering InnoDB |
| 281475304018464 | RUNNING | 0 | 234271 | select * from mqs | sleeping before entering InnoDB |
| 281475304017552 | RUNNING | 0 | 234303 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475304012992 | RUNNING | 0 | 234295 | select * from mqs | NULL |
| 281475303996576 | RUNNING | 0 | 234258 | select * from mqs | sleeping before entering InnoDB |
| 281475303995664 | RUNNING | 0 | 234263 | select * from mqs | sleeping before entering InnoDB |
| 281475303994752 | RUNNING | 0 | 234269 | select * from mqs | sleeping before entering InnoDB |
| 281475303993840 | RUNNING | 0 | 234266 | select * from mqs | sleeping before entering InnoDB |
| 281475303992016 | RUNNING | 0 | 234261 | select count(*) from mqs | sleeping before entering InnoDB |
| 281475303992928 | RUNNING | 0 | 233338 | select trx_id,trx_state,trx_rows_modified,trx_mysql_thread_id,trx_query,trx_operation_state from information_schema.innodb_trx | NULL |
+-----------------+-----------+-------------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------+---------------------------------+
40 rows in set (0.00 sec)这也说明多数的应用/SQL其实是在排队，等着进入innodb，要想查看正在排队的应用/SQL，可以关联 PROCESSLIST 表和 INNODB_TRX 表，如下：
select * from information_schema.PROCESSLIST where id not in (select trx_mysql_thread_id from information_schema.INNODB_TRX)
参考：mysql reference manual
扫一扫
799
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
