temp-tickets/tickets
10
0
Code Issues 453 Packages Projects 4 Wiki

义乌妇幼慢的页面，和执行慢的sql #214

New Issue
Open
opened 2025-12-24 03:53:16 +08:00 by bigtran · 0 comments
bigtran commented 2025-12-24 03:53:16 +08:00
Owner
Copy Link
Copy Source

1、慢的页面和接口

3dec41f13d406ca02e19f185f512aa02.png

2、慢的sql分布

de54460399c872743384a7c8a5cdd4e4.png

3、慢的SQL

root@pho-server:/var/webman-dev/ectms-web/runtime/logs# awk -F'[][]' '/^\[SQL\]/ {gsub(" ms", "", $6); print $6, $0}' sql2.log | sort -nr | head -n 50 
2719.43  SQL   connection:plugin.admin.mysql  select count(*) as aggregate from `v_machine_record_with_endoscope`  2719.43 
1849.59  SQL   connection:plugin.admin.mysql  select * from `v_machine_record_with_endoscope` order by `created_at` desc limit 10 offset 0  1849.59 
1845.53  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where action_type_name = '机洗（加强）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1845.53 
1755.99  SQL   connection:plugin.admin.mysql  select * from `v_machine_record_with_endoscope` order by `created_at` desc limit 10 offset 0  1755.99 
1736.63  SQL   connection:plugin.admin.mysql  select count(*) as aggregate from `v_machine_record_with_endoscope`  1736.63 
1703.54  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-01-31 23:59:59'  1703.54 
1669.42  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where action_type_name = '手工洗（加强）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1669.42 
1647.5  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where action_type_name = '手工洗' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1647.5 
1354.51  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-07-01 00:00:00' and op_starttime <= '2025-07-31 23:59:59'  1354.51 
1215.36  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-06-01 00:00:00' and op_starttime <= '2025-06-30 23:59:59'  1215.36 
1159  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1159 
1154.41  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-05-01 00:00:00' and op_starttime <= '2025-05-31 23:59:59'  1154.41 
1147.37  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-02-01 00:00:00' and op_starttime <= '2025-02-28 23:59:59'  1147.37 
1137.86  SQL   connection:plugin.admin.mysql  select count(*) as aggregate from `v_clean`  1137.86 
1112.33  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-11-01 00:00:00' and op_starttime <= '2025-11-30 23:59:59'  1112.33 
1094.76  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where action_type_name = '手工洗（晨洗）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1094.76 
1081.33  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where action_type_name = '机洗' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1081.33 
1075.45  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-03-01 00:00:00' and op_starttime <= '2025-03-31 23:59:59'  1075.45 
1054.07  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-08-01 00:00:00' and op_starttime <= '2025-08-31 23:59:59'  1054.07 
1052.05  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-04-01 00:00:00' and op_starttime <= '2025-04-30 23:59:59'  1052.05 
1019.89  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where action_type_name = '机洗（晨洗）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04'  1019.89 
1019.02  SQL   connection:plugin.admin.mysql  select count(*) as aggregate from `v_clean`  1019.02 
1009.46  SQL   connection:plugin.admin.mysql  select * from `v_clean` limit 10 offset 0  1009.46 
1003.33  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-12-01 00:00:00' and op_starttime <= '2025-12-31 23:59:59'  1003.33 
1001.24  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 37 )  1001.24 
1000.08  SQL   connection:plugin.admin.mysql  select * from `v_clean` limit 10 offset 0  1000.08 
999.61  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-09-01 00:00:00' and op_starttime <= '2025-09-30 23:59:59'  999.61 
989.79  SQL   connection:plugin.admin.mysql  select count(*) from v_clean where op_starttime >= '2025-10-01 00:00:00' and op_starttime <= '2025-10-31 23:59:59'  989.79 
927.17  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 )  927.17 
925.22  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 60 )  925.22 
912.83  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 60 )  912.83 
908.59  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 )  908.59 
901.23  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 16 )  901.23 
900.59  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 )  900.59 
899.41  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 9 )  899.41 
897.85  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 )  897.85 
896.88  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 )  896.88 
895.8  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 )  895.8 
880.63  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 )  880.63 
879.25  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 )  879.25 
878.71  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 37 )  878.71 
876.98  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 53 )  876.98 
871.26  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 16 )  871.26 
870.8  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 )  870.8 
870.66  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 )  870.66 
868.34  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 9 )  868.34 
864.09  SQL   connection:default  SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 53 )  864.09 
389.81  SQL   connection:plugin.admin.mysql  select count(*) as aggregate from `v_report_clean` where `action_type_name_1` <> '测漏'  389.81 
389.69  SQL   connection:plugin.admin.mysql  select count(*) as aggregate from `v_report_byoperator_byetype_day`  389.69 
378.47  SQL   connection:plugin.admin.mysql  select * from `v_report_byoperator_byetype_day` limit 10 offset 0  378.47
# 1、慢的页面和接口 <img width="601" alt="3dec41f13d406ca02e19f185f512aa02.png" src="attachments/c1d04d35-b3b3-4361-b83c-04cd980c8f7b"> # 2、慢的sql分布 <img width="555" alt="de54460399c872743384a7c8a5cdd4e4.png" src="attachments/561987ab-3564-4d51-8251-f6bb339d544d"> # 3、慢的SQL ``` root@pho-server:/var/webman-dev/ectms-web/runtime/logs# awk -F'[][]' '/^\[SQL\]/ {gsub(" ms", "", $6); print $6, $0}' sql2.log | sort -nr | head -n 50 2719.43 SQL connection:plugin.admin.mysql select count(*) as aggregate from `v_machine_record_with_endoscope` 2719.43 1849.59 SQL connection:plugin.admin.mysql select * from `v_machine_record_with_endoscope` order by `created_at` desc limit 10 offset 0 1849.59 1845.53 SQL connection:plugin.admin.mysql select count(*) from v_clean where action_type_name = '机洗（加强）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1845.53 1755.99 SQL connection:plugin.admin.mysql select * from `v_machine_record_with_endoscope` order by `created_at` desc limit 10 offset 0 1755.99 1736.63 SQL connection:plugin.admin.mysql select count(*) as aggregate from `v_machine_record_with_endoscope` 1736.63 1703.54 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-01-31 23:59:59' 1703.54 1669.42 SQL connection:plugin.admin.mysql select count(*) from v_clean where action_type_name = '手工洗（加强）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1669.42 1647.5 SQL connection:plugin.admin.mysql select count(*) from v_clean where action_type_name = '手工洗' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1647.5 1354.51 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-07-01 00:00:00' and op_starttime <= '2025-07-31 23:59:59' 1354.51 1215.36 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-06-01 00:00:00' and op_starttime <= '2025-06-30 23:59:59' 1215.36 1159 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1159 1154.41 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-05-01 00:00:00' and op_starttime <= '2025-05-31 23:59:59' 1154.41 1147.37 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-02-01 00:00:00' and op_starttime <= '2025-02-28 23:59:59' 1147.37 1137.86 SQL connection:plugin.admin.mysql select count(*) as aggregate from `v_clean` 1137.86 1112.33 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-11-01 00:00:00' and op_starttime <= '2025-11-30 23:59:59' 1112.33 1094.76 SQL connection:plugin.admin.mysql select count(*) from v_clean where action_type_name = '手工洗（晨洗）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1094.76 1081.33 SQL connection:plugin.admin.mysql select count(*) from v_clean where action_type_name = '机洗' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1081.33 1075.45 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-03-01 00:00:00' and op_starttime <= '2025-03-31 23:59:59' 1075.45 1054.07 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-08-01 00:00:00' and op_starttime <= '2025-08-31 23:59:59' 1054.07 1052.05 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-04-01 00:00:00' and op_starttime <= '2025-04-30 23:59:59' 1052.05 1019.89 SQL connection:plugin.admin.mysql select count(*) from v_clean where action_type_name = '机洗（晨洗）' and op_starttime >= '2025-01-01 00:00:00' and op_starttime <= '2025-12-24 03:41:04' 1019.89 1019.02 SQL connection:plugin.admin.mysql select count(*) as aggregate from `v_clean` 1019.02 1009.46 SQL connection:plugin.admin.mysql select * from `v_clean` limit 10 offset 0 1009.46 1003.33 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-12-01 00:00:00' and op_starttime <= '2025-12-31 23:59:59' 1003.33 1001.24 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 37 ) 1001.24 1000.08 SQL connection:plugin.admin.mysql select * from `v_clean` limit 10 offset 0 1000.08 999.61 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-09-01 00:00:00' and op_starttime <= '2025-09-30 23:59:59' 999.61 989.79 SQL connection:plugin.admin.mysql select count(*) from v_clean where op_starttime >= '2025-10-01 00:00:00' and op_starttime <= '2025-10-31 23:59:59' 989.79 927.17 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 ) 927.17 925.22 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 60 ) 925.22 912.83 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 60 ) 912.83 908.59 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 ) 908.59 901.23 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 16 ) 901.23 900.59 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 ) 900.59 899.41 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 9 ) 899.41 897.85 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 ) 897.85 896.88 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 ) 896.88 895.8 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 ) 895.8 880.63 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 ) 880.63 879.25 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 ) 879.25 878.71 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 37 ) 878.71 876.98 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 53 ) 876.98 871.26 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 16 ) 871.26 870.8 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 65 ) 870.8 870.66 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 63 ) 870.66 868.34 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 9 ) 868.34 864.09 SQL connection:default SELECT max(op_batchno) as op_batchno_max FROM `v_clean` WHERE (SUBSTR(`op_batchno`,1,8) = 20251222) AND ( `endoscope_id` = 53 ) 864.09 389.81 SQL connection:plugin.admin.mysql select count(*) as aggregate from `v_report_clean` where `action_type_name_1` <> '测漏' 389.81 389.69 SQL connection:plugin.admin.mysql select count(*) as aggregate from `v_report_byoperator_byetype_day` 389.69 378.47 SQL connection:plugin.admin.mysql select * from `v_report_byoperator_byetype_day` limit 10 offset 0 378.47 ```
3dec41f13d406ca02e19f185f512aa02.png
57 KiB
de54460399c872743384a7c8a5cdd4e4.png
26 KiB
bigtran changed title from 义乌妇幼执行慢的sql to 义乌妇幼慢的页面，和执行慢的sql 2025-12-24 23:16:09 +08:00
bigtran added the 6-now-commit-to-fix label 2025-12-27 22:36:25 +08:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
1-feature
2-bug
Something is not working
 3-enhancement
New feature
 4-document
5-bloody-version-diff
搞死人的版本差异
 6-now-commit-to-fix
下定决心解决，用行动证明，用结果证明
 duplicate
This issue or pull request already exists
 invalid
Something is wrong
 wontfix
This won't be fixed
No Label 6-now-commit-to-fix
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
bigtran (张大川) chenyingjie fanjc huangyanxue liuning panzs qilisong yanyanyan
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: temp-tickets/tickets#214
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?