How does MySQL obtain the SQL statement currently executed?

In the mysql clientShow [full] processlistWe can see the status of all the current threads and threads, including the SQL statements in execution, but the displayed statements are incomplete, and many Idle threads may cause interference.

Here we will introduce a very useful open-source tool.Innotop(SourceForge is already on the wall. You need to find a ladder first.) because it is written in Perl, you need to install the relevant Perl module, includingDBI,DBD: MySQL,Term: readkeyAndTime: hires, These modules can goCPANFind.

UseInnotopThe Q mode can perfectly solve the problem of obtaining the currently running SQL. After innotop-m q or innotop is entered, press SHIFT + Q to enter the query list mode:

Query List (? for help) mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logCXN    When   Load  QPS     Slow  QCacheHit  KCacheHit  BpsIn    BpsOutmy120  Now    0.00  774.20     0     40.22%    100.00%  207.98k    1.46Mmy120  Total  0.00  212.69     2     29.70%    100.00%   56.90k  402.15kCXN     Cmd    ID      User    Host           DB      Time   Querymysql01  Query   20936  poster  192.168.1.1    poster  00:00select a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.no

Then press E and enter the thread ID to display the execution plan, press F to display the complete SQL statement, or press o to display the system-optimized statements (MySQL version must support explain extended ). In my personal opinion, e is the most useful. The other two options are a little chicken.

Query List (? for help) mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logEXPLAIN PARTITIONSselect a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.notes,a.type, a.indexed, a.user_id, a.user_nick, b.pic_pathfrom poster.poster_pic a inner join poster.picture b on(a.pic_id = b.id) where a.poster_id = 3390 order by a.indexed______________ Sub-Part 1 ______________  ________ Sub-Part 1 ________Select Type: SIMPLE                       Select Type: SIMPLE      Table: a                                  Table: b Partitions:                               Partitions:       Type: ref                                 Type: eq_ref Poss. Keys: PRIMARY                       Poss. Keys: PRIMARY      Index: PRIMARY                            Index: PRIMARY Key Length: 4                             Key Length: 8  Index Ref: const                          Index Ref: poster.a.PIC_ID  Row Count: 14                             Row Count: 1    Special: Using where; Using filesort      Special:                Press e to explain, f for full query, o for optimized query

Query List (? for help) mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logselect a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.notes,a.type, a.indexed, a.user_id, a.user_nick, b.pic_pathfrom poster.poster_pic a inner join poster.picture b on(a.pic_id = b.id) where a.poster_id = 3390 order by a.indexedPress e to explain, f for full query, o for optimized query

Query List (? for help) mysql01, 75+03:16:16, 774.20 QPS, 83 thd, 5.1.24-rc-logselect a.poster_id, a.pic_id, a.gmt_create, a.gmt_modified, a.url, a.notes,a.type, a.indexed, a.user_id, a.user_nick, b.pic_pathfrom poster.poster_pic a inner join poster.picture b on(a.pic_id = b.id) where a.poster_id = 3390 order by a.indexedNote:select `poster`.`a`.`POSTER_ID` AS `poster_id`,`poster`.`a`.`PIC_ID` AS`pic_id`,`poster`.`a`.`GMT_CREATE` AS `gmt_create`,`poster`.`a`.`GMT_MODIFIED` AS `gmt_modified`,`poster`.`a`.`URL` AS `url`,`poster`.`a`.`NOTES` AS `notes`,`poster`.`a`.`TYPE` AS `type`,`poster`.`a`.`INDEXED` AS `indexed`,`poster`.`a`.`USER_ID` AS `user_id`,`poster`.`a`.`user_nick` AS`user_nick`,`poster`.`b`.`PIC_PATH` AS `pic_path`from `poster`.`poster_pic` `a` join `poster`.`picture` `b`where ((`poster`.`b`.`ID` = `poster`.`a`.`PIC_ID`) and(`poster`.`a`.`POSTER_ID` = 3390))order by `poster`.`a`.`INDEXED`Press e to explain, f for full query, o for optimized query

SoInnotopWhere is the data obtained? It should be throughInformation_schema.processlistTo obtain the complete SQL statement, and filter out Idle threads according to command.

mysql> desc information_schema.processlist;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| ID      | bigint(4)   | NO   |     | 0       |       || USER    | varchar(16) | NO   |     |         |       || HOST    | varchar(64) | NO   |     |         |       || DB      | varchar(64) | YES  |     | NULL    |       || COMMAND | varchar(16) | NO   |     |         |       || TIME    | bigint(7)   | NO   |     | 0       |       || STATE   | varchar(64) | YES  |     | NULL    |       || INFO    | longtext    | YES  |     | NULL    |       |+---------+-------------+------+-----+---------+-------+8 rows in set (0.00 sec)

URL:Http://www.ningoo.net/html/2008/how_mysql_get_current_running_ SQL _statements.html