《slowlog处理方案》由会员分享，可在线阅读，更多相关《slowlog处理方案（8页珍藏版）》请在金锄头文库上搜索。
1、Slow LogSlow log大多都是order by引起的，explain语句有Using where; Using temporary; Using filesort的信息，有这个情况就会效率低了。解决这个的关键要在order by的字段上做索引。请看下面的例子：Report for slow logs: /data/3306/slow-log.log.16.26k queries total, 135 uniqueSorted by t_sumGrand Totals: Time 34.11k s, Lock 0 s, Rows sent 57.67k, Rows Examined 3
2、.84M_ 001 _Count : 2.17k (34.64%)Time : 11031 s total, 5.090448 s avg, 3 s to 18 s max (32.34%) 95% of Time : 9605 s total, 4.667153 s avg, 3 s to 11 s maxLock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%) 95% of Lock : 0 total, 0 avg, 0 to 0 maxRows sent : 10 avg, 0 to 10 max (36.35%)Rows examined
3、: 20 avg, 0 to 37 max (1.11%)Database : docresourceUsers : drhd 192.168.1.18 : 100.00% (2167) of query, 100.00% (6256) of all usersQuery abstract:SELECT t.cor_id,t.doc_title_upper,t.cor_doc_title,t.cor_doc_order,t.cor_doc_description,t.cor_doc_state FROM t_wiki_cordoc t WHERE t.cor_doc_state=N AND t
4、.doc_title_upper=S ORDER BY t.cor_doc_order ASC LIMIT N;Query sample:SELECT T.COR_ID,T.DOC_TITLE_UPPER,T.COR_DOC_TITLE,T.COR_DOC_ORDER,T.COR_DOC_DESCRIPTION,T.COR_DOC_STATE FROM t_wiki_cordoc T where T.COR_DOC_STATE=1 and T.DOC_TITLE_UPPER=铔嬪僵鐢? ORDER BY T.COR_DOC_ORDER ASC LIMIT 10;解决方案：用java在内存排序。
5、_ 002 _Count : 911 (14.56%)Time : 5342 s total, 5.863886 s avg, 3 s to 63 s max (15.66%) 95% of Time : 4381 s total, 5.06474 s avg, 3 s to 14 s maxLock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%) 95% of Lock : 0 total, 0 avg, 0 to 0 maxRows sent : 9 avg, 1 to 10 max (14.53%)Rows examined : 759 avg
6、, 20 to 13.61k max (17.99%)Database : docresourceUsers : drhd 192.168.1.18 : 100.00% (911) of query, 100.00% (6256) of all usersQuery abstract:SELECT doc_id,doc_class,doc_title,doc_creator_user_id,doc_creator_user_nick,doc_latest_edition,doc_latest_url,doc_created_time,doc_latest_edition_time,doc_in
7、ner_pic_count,doc_outer_pic_count,doc_keywords,doc_click_count,doc_his_count,doc_attendee_count,doc_summary,doc_state,doc_goodcount,doc_badcount,doc_first_img,doc_title_upper,doc_import_tag,doc_topic_count,doc_post_count,doc_creator_user_id_encrypt,doc_id_encrypt,doc_score,last_edit_user_id_en,champ
8、ion_user_nick,champion_user_id_en,champion_credit FROM t_wiki_doc WHERE doc_state IN (N4) AND doc_creator_user_id_encrypt=S ORDER BY doc_latest_edition_time DESC LIMIT N,N;Query sample:select doc_id,doc_class,doc_title,doc_creator_user_id,doc_creator_user_nick,doc_latest_edition,doc_latest_url,doc_c
9、reated_time,doc_latest_edition_time,doc_inner_pic_count,doc_outer_pic_count,doc_keywords,doc_click_count,doc_his_count,doc_attendee_count,doc_summary,doc_state,doc_goodcount,doc_badcount,doc_first_img,doc_title_upper,doc_import_tag,doc_topic_count,doc_post_count,doc_creator_user_id_encrypt,doc_id_en
10、crypt,doc_score,last_edit_user_id_en,champion_user_nick,champion_user_id_en,champion_credit from t_wiki_doc where doc_state in (1,2,3,4) and doc_creator_user_id_encrypt=tAQREAwRXWkJBUAoL order by DOC_LATEST_EDITION_TIME DESC limit 0,10;解决方案：alter table t_wiki_doc add index idx_create_user_iden_editt
11、ime(doc_creator_user_id_encrypt, doc_latest_edition_time);_ 003 _Count : 1.00k (16.06%)Time : 5114 s total, 5.088557 s avg, 3 s to 17 s max (14.99%) 95% of Time : 4426 s total, 4.639413 s avg, 3 s to 10 s maxLock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%) 95% of Lock : 0 total, 0 avg, 0 to 0 maxR
12、ows sent : 10 avg, 8 to 10 max (17.28%)Rows examined : 10 avg, 8 to 10 max (0.26%)Database : docresourceUsers : drhd 192.168.1.18 : 100.00% (1005) of query, 100.00% (6256) of all usersQuery abstract:SELECT doc_id,doc_class,doc_title,doc_creator_user_id,doc_creator_user_nick,doc_latest_edition,doc_la
13、test_url,doc_created_time,doc_latest_edition_time,doc_inner_pic_count,doc_outer_pic_count,doc_keywords,doc_click_count,doc_his_count,doc_attendee_count,doc_summary,doc_state,doc_goodcount,doc_badcount,doc_first_img,doc_title_upper,doc_import_tag,doc_topic_count,doc_post_count,doc_creator_user_id_enc
14、rypt,doc_id_encrypt,doc_score,last_edit_user_id_en,champion_user_nick,champion_user_id_en,champion_credit FROM t_wiki_doc WHERE doc_title_upper IN (S10);Query sample:select doc_id,doc_class,doc_title,doc_creator_user_id,doc_creator_user_nick,doc_latest_edition,doc_latest_url,doc_created_time,doc_lat
15、est_edition_time,doc_inner_pic_count,doc_outer_pic_count,doc_keywords,doc_click_count,doc_his_count,doc_attendee_count,doc_summary,doc_state,doc_goodcount,doc_badcount,doc_first_img,doc_title_upper,doc_import_tag,doc_topic_count,doc_post_count,doc_creator_user_id_encrypt,doc_id_encrypt,doc_score,last_edit_user_id_en,cham
