慢查询

编程技术  /  houtizong 发布于 3年前   137

分析归类后的慢查询日志如下。
______________________________________________________________________ 004 ___
Count: 378(6.04%)
Time: 2604 s total, 6.888889 s avg, 3 s to 65 s max(7.63%)
95% of Time : 2031 s total, 5.657382 s avg, 3 s to 17 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max(0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent: 5 avg, 0 to 10 max(3.24%)
Rows examined : 2.81k avg, 92 to 24.91k max(27.61%)
Database: docresource
Users:
ett_oldboy@ 10.0.1.28 : 100.00% (378) of query, 100.00% (6256) of all users
Query abstract:
SELECT h.boy_id ,MAX(h.boy_his_edit_time) AS boy_his_edit_time FROM t_boy_his h WHERE h.boy_his_isteammate=N AND h.boy_his_state=N AND h.boy_his_editor_user_id_encrypt='S' GROUP BY h.boy_id ORDER BY h.boy_his_edit_time DESC LIMIT N,N;
Query sample:
selecth.boy_id ,max(h.boy_his_edit_time) from t_boy_his hwhere h.boy_his_editor_user_id_encrypt='pGVpWQVlYdglTaQ0Z' and h.boy_his_isteammate=1 and h.boy_his_state=1 group by h.boy_id order by h.boy_his_edit_time desc limit 0,4;
最终解决方案：
alter table t_boy_his add index editor_user_iden_docid(boy_his_editor_user_id_encrypt, boy_id);
1,sql改成：SELECT h.boy_id ,MAX(h.boy_his_edit_time) AS boy_his_edit_time FROM t_boy_his h WHERE h.boy_his_isteammate='pGVpWQVlYdglTaQ0Z' AND h.boy_his_state=1 AND h.boy_his_editor_user_id_encrypt='S' GROUP BY h.boy_id ORDER BY null
2，再在程序里，用对boy_his_edit_time排序。