mysql - 在 MySQL 中重新创建 SHOW PROFILES 变量的代码

Question

嗨，我正在使用 MySQL，

SHOW PROFILES;

返回一个长列表，其中包含如下行:

 |Query_ID|Duration     |Query
>| 9    |    0.00028550 |   INSERT INTO Duration(Status,DURATION,Query_ID)|

我正在尝试复制此内容，因此我可以将结果插入表中并添加 WHERE 子句。

所以我尝试了这个

SELECT Query_ID, STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 9;

但这会返回较低级别的 View (我不想要):

starting 0.000053 Opening tables 0.000088

这与:

相同

SHOW PROFILE;

我如何重新创建显示配置文件？所以我可以执行这样的操作:

INSERT INTO Duration(Query_ID,Status,DURATION)
SELECT * FROM SHOW PROFILE
WHERE PROFILE.QUERY_ID = 9;

谢谢

Answer 1

按QUERY_ID分组。要获取状态，您需要带有 groupwise maximum 的记录的 STATE SEQ；要获取持续时间，只需将组中每个记录的 DURATION 相加即可:

INSERT INTO Duration
  (Query_ID, Status, DURATION)
SELECT QUERY_ID, STATE, t.DURATION
FROM   INFORMATION_SCHEMA.PROFILING JOIN (
  SELECT   QUERY_ID, MAX(SEQ) AS SEQ, SUM(DURATION) AS DURATION
  FROM     INFORMATION_SCHEMA.PROFILING
  WHERE    QUERY_ID = 9
  GROUP BY QUERY_ID  -- superfluous in the presence of the above filter
) t USING (QUERY_ID, SEQ)