mysqlsla 按avg time排序如何实现？

zl624867243

mysqlsla 提取出的日志，我想让它按avg time降序排列（注意ms和s），也就是 Time 那一行的avg，请问如何实现？

1. Report for slow logs: slowlog.log
   
2. 21.39k queries total, 243 unique
   
3. Sorted by 't_sum'
   
4. Grand Totals: Time 4.00k s, Lock 3 s, Rows sent 6.48M, Rows Examined 4.72G
   
5. 
   
6. 
   
7. ______________________________________________________________________ 001 ___
   
8. Count         : 9.83k  (45.95%)
   
9. Time          : 2418.882604 s total, 246.046 ms avg, 209.407 ms to 392.407 ms max  (60.55%)
   
10.   95% of Time : 2266.720447 s total, 242.716 ms avg, 209.407 ms to 293.257 ms max
    
11. Lock Time (s) : 548.854 ms total, 56 vg, 30 o 29.527 ms max  (19.94%)
    
12.   95% of Lock : 450.749 ms total, 48 vg, 30 o 95 ax
    
13. Rows sent     : 403 avg, 0 to 18.83k max  (61.09%)
    
14. Rows examined : 415.27k avg, 415.21k to 415.33k max  (86.46%)
    
15. Database      : testtt
    
16. Users         :
    
17.         star@ 10.160.12.203 : 100.00% (9831) of query, 70.21% (15020) of all users
    
18. 
    
19. Query abstract:
    
20. SELECT dbid,id, message_id, created_date FROM t_crm_email_receive_log WHERE user_id=N AND account_id=N AND is_deleted='S';
    
21. 
    
22. Query sample:
    
23. SELECT dbid,id, message_id, created_date FROM t_crm_email_receive_log WHERE user_id=100000133030 AND account_id=100203900022 AND is_deleted='N';
    
24. 
    
25. ______________________________________________________________________ 002 ___
    
26. Count         : 85  (0.40%)
    
27. Time          : 771.363914 s total, 9.07487 s avg, 3.213125 s to 60.178968 s max  (19.31%)
    
28.   95% of Time : 517.756963 s total, 6.471962 s avg, 3.213125 s to 43.969085 s max
    
29. Lock Time (s) : 11.068 ms total, 130 vg, 43 o 615 ax  (0.40%)
    
30.   95% of Lock : 9.307 ms total, 116 vg, 43 o 227 ax
    
31. Rows sent     : 2.38k avg, 2.16k to 2.42k max  (3.12%)
    
32. Rows examined : 1.10M avg, 1.05M to 1.42M max  (1.97%)
    
33. Database      : testtt
    
34. Users         :
    
35.         star@ 10.162.38.176 : 100.00% (85) of query, 4.14% (885) of all users
    
36. 
    
37. Query abstract:
    
38. SELECT CONCAT(dbid, 'S', campaignid) AS key1, COUNT(DISTINCT dbid, campaignid, recipient) FROM t_edm_deliveredlog WHERE dbid = N GROUP BY campaignid;
    
39. 
    
40. Query sample:
    
41. SELECT CONCAT(dbid, '-', campaignid) AS key1, COUNT(DISTINCT dbid, campaignid, recipient) FROM t_edm_deliveredlog WHERE dbid = 100436 GROUP BY campaignid;
    
42. 
    
43. ______________________________________________________________________ 003 ___
    
44. Count         : 10  (0.05%)
    
45. Time          : 117.100069 s total, 11.710007 s avg, 11.70469 s to 11.717984 s max  (2.93%)
    
46.   95% of Time : 105.382085 s total, 11.709121 s avg, 11.70469 s to 11.713554 s max
    
47. Lock Time (s) : 1.865 ms total, 186 vg, 97 o 393 ax  (0.07%)
    
48.   95% of Lock : 1.472 ms total, 164 vg, 97 o 343 ax
    
49. Rows sent     : 1.50k avg, 1.50k to 1.50k max  (0.23%)
    
50. Rows examined : 104.16k avg, 104.16k to 104.16k max  (0.02%)
    
51. Database      : testtt
    
52. Users         :
    
53.         star@ 10.162.38.176 : 100.00% (10) of query, 4.14% (885) of all users
    
54. 
    
55. Query abstract:
    
56. SELECT CONCAT(dbid, 'S', campaignid) AS key1, COUNT(DISTINCT dbid, campaignid, recipient) FROM t_edm_bouncedlog WHERE dbid = N GROUP BY campaignid;
    
57. 
    
58. Query sample:
    
59. SELECT CONCAT(dbid, '-', campaignid) AS key1, COUNT(DISTINCT dbid, campaignid, recipient) FROM t_edm_bouncedlog WHERE dbid = 100294 GROUP BY campaignid;
    
60. 
    
61. ______________________________________________________________________ 004 ___
    
62. Count         : 308  (1.44%)
    
63. Time          : 115.21593 s total, 374.078 ms avg, 840 o 2.191249 s max  (2.88%)
    
64.   95% of Time : 95.587075 s total, 327.353 ms avg, 840 o 1.056697 s max
    
65. Lock Time (s) : 68.492 ms total, 222 vg, 126 o 593 ax  (2.49%)
    
66.   95% of Lock : 62.091 ms total, 213 vg, 126 o 323 ax
    
67. Rows sent     : 21 avg, 0 to 163 max  (0.10%)
    
68. Rows examined : 557.82k avg, 26 to 1.39M max  (3.64%)
    
69. Database      : testtt
    
70. Users         :
    
71.         star@ 10.160.12.203 : 97.40% (300) of query, 70.21% (15020) of all users
    
72.         star@ 10.252.87.79 : 2.60% (8) of query, 3.43% (733) of all users
    
73. 
    
74. Query abstract:
    
75. SELECT im_message.id AS id, im_message.sender_id AS sender_id, im_message.sender_name AS sender_name, im_message.group_id AS group_id, im_message.created_time AS created_time, im_message.content AS content, im_message.destination AS destination, im_message.msg_type AS msg_type, _rer.receiver_id AS receiver_id, _rer.is_read AS is_read ,g.is_two_group AS istwogroup , IF(g.is_two_group=N,im_message.sender_name,g.name) AS groupname FROM ( SELECT dbid, message_id, receiver_id, is_read FROM t_im_message_receiver WHERE dbid = N AND receiver_id=N AND is_read='S' ) AS _rer JOIN t_im_message im_message ON _rer.message_id=im_message.id AND _rer.dbid = im_message.dbid INNER JOIN t_im_group g ON im_message.group_id = g.id AND im_message.dbid = g.dbid ORDER BY im_message.created_time ASC;
    
76. 
    
77. Query sample:
    
78. SELECT im_message.id AS id, im_message.sender_id AS sender_id, im_message.sender_name AS sender_name, im_message.group_id AS group_id, im_message.created_time AS created_time, im_message.content AS content, im_message.destination AS destination, im_message.msg_type AS msg_type, _rer.receiver_id AS receiver_id, _rer.is_read AS is_read ,g.is_two_group as isTwoGroup , IF(g.is_two_group=1,im_message.sender_name,g.name) as groupName FROM ( SELECT dbid, message_id, receiver_id, is_read FROM t_im_message_receiver WHERE dbid = 1000141  and receiver_id=100000012600 AND is_read='N' ) AS _rer JOIN t_im_message im_message ON _rer.message_id=im_message.id and _rer.dbid = im_message.dbid  INNER JOIN t_im_group g on im_message.group_id = g.id and im_message.dbid = g.dbid  ORDER BY im_message.created_time ASC;
    
79. 
    
80. ______________________________________________________________________ 005 ___
    
81. Count         : 330  (1.54%)
    
82. Time          : 104.14257 s total, 315.584 ms avg, 280.908 ms to 447.91 ms max  (2.61%)
    
83.   95% of Time : 97.424078 s total, 311.259 ms avg, 280.908 ms to 370.794 ms max
    
84. Lock Time (s) : 23.976 ms total, 73 vg, 46 o 166 ax  (0.87%)
    
85.   95% of Lock : 21.605 ms total, 69 vg, 46 o 125 ax
    
86. Rows sent     : 0 avg, 0 to 6 max  (0.00%)
    
87. Rows examined : 415.27k avg, 415.21k to 415.33k max  (2.90%)
    
88. Database      : testtt
    
89. Users         :
    
90.         star@ 10.160.12.203 : 100.00% (330) of query, 70.21% (15020) of all users
    
91. 
    
92. Query abstract:
    
93. SELECT emailrecei0_.dbid AS dbid3_, emailrecei0_.id AS id3_, emailrecei0_.account_id AS account3_3_, emailrecei0_.created_date AS created4_3_, emailrecei0_.data_id AS data5_3_, emailrecei0_.is_deleted AS is6_3_, emailrecei0_.email_id AS email7_3_, emailrecei0_.message_id AS message8_3_, emailrecei0_.message_num AS message9_3_, emailrecei0_.status AS status3_, emailrecei0_.user_id AS user11_3_ FROM t_crm_email_receive_log emailrecei0_ WHERE emailrecei0_.status='S' LIMIT N;
    
94. 
    
95. Query sample:
    
96. select emailrecei0_.dbid as dbid3_, emailrecei0_.id as id3_, emailrecei0_.account_id as account3_3_, emailrecei0_.created_date as created4_3_, emailrecei0_.data_id as data5_3_, emailrecei0_.is_deleted as is6_3_, emailrecei0_.email_id as email7_3_, emailrecei0_.message_id as message8_3_, emailrecei0_.message_num as message9_3_, emailrecei0_.status as status3_, emailrecei0_.user_id as user11_3_ from t_crm_email_receive_log emailrecei0_ where emailrecei0_.status='N' limit 50;

复制代码

zl624867243

也就是文本块  按 ”Time          : 115.21593 s total, 374.078 ms avg, 840 o 2.191249 s max  (2.88%)“  这一行的avg 进行排序， 大家有思路没？

jason680

回复 1# zl624867243

How about this way

$ awk '
function put(t,d){
  sub(/\n+$/,"",d);
  a[sprintf("%020d",10**12-t)] = d;
}
BEGIN{
  at["ms"] = 1;
  at["s"] = 1000;
}
/[0-9]+ ___$/{
  if(t=="") h = str;
  else put(t,str);
  str = "";
}
match($0,/^Time .+ ([0-9.]+) (m?s) avg/,m){
  t=m[1]*at[m[2]];
}
{
  str = str N $0;
  N = "\n";
}
END{
  put(t,str);
  item = asorti(a,b);
  printf h;
  for(n=1;n<=item;n++)
    print a[b[n]]
}' mysqlsla.log

zl624867243

回复 3# jason680


    一个字 牛掰 感觉把awk 玩得出神入化了。