slowlog处理方案

Slow Log Slow log 大多都是 order by 引起的 explain 语句有 Using where Using temporary Using filesort 的信息 有这个情况就会效率低了 解决这个的关键要在 order by 的字段上做索引 请看下面的例子 Report for slow logs data 3306 slow log log 1 6 26k queries total 135 unique Sorted by t sum Grand Totals Time 34 11k s Lock 0 s Rows sent 57 67k Rows Examined 3 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 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 10 avg 0 to 10 max 36 35 Rows examined 20 avg 0 to 37 max 1 11 Database docresource Users drhd 192 168 1 18 100 00 2167 of query 100 00 6256 of all users Query 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 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 在内存排序 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 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 9 avg 1 to 10 max 14 53 Rows examined 759 avg 20 to 13 61k max 17 99 Database docresource Users drhd 192 168 1 18 100 00 911 of query 100 00 6256 of all users Query abstract SELECT doc id doc class doc title doc creator user id doc creator user nick doc latest edition doc lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 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 lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 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 edittime 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 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 10 avg 8 to 10 max 17 28 Rows examined 10 avg 8 to 10 max 0 26 Database docresource Users drhd 192 168 1 18 100 00 1005 of query 100 00 6256 of all users Query abstract SELECT doc id doc class doc title doc creator user id doc creator user nick doc latest edition doc lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 銆婄 鍏 劅濂崇 銆 灏斿啲鍗 銆婂 骞介瓊 2 銆 銆婄旱妯 洓娴枫 榛勭 鐢 棣欐腐鐢靛奖閲戝儚濂 銆婁笢閭 姣掋 鐜嬬 璐 绉 矝 寰愬厠 解决方案 有用到 doc title upper 的索引 慢的原因再观察 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 drhd 192 168 1 18 100 00 378 of query 100 00 6256 of all users Query abstract SELECT h doc id MAX h doc his edit time AS doc his edit time FROM t wiki doc his h WHERE h doc his isteammate N AND h doc his state N AND h doc his editor user id encrypt S GROUP BY h doc id ORDER BY h doc his edit time DESC LIMIT N N Query sample select h doc id max h doc his edit time from t wiki doc his h where h doc his editor user id encrypt pGVpWQVlYdglTaQ0Z and h doc his isteammate 1 and h doc his state 1 group by h doc id order by h doc his edit time desc limit 0 4 解决方案 alter table t wiki doc his add index editor user iden docid doc his editor user id encrypt doc id 1 sql 改成 SELECT h doc id MAX h doc his edit time AS doc his edit time FROM t wiki doc his h WHERE h doc his isteammate pGVpWQVlYdglTaQ0Z AND h doc his state 1 AND h doc his editor user id encrypt S GROUP BY h doc id ORDER BY null 2 再在程序里 用 java 对 doc his edit time 排序 005 Count 211 3 37 Time 1218 s total 5 772512 s avg 3 s to 30 s max 3 57 95 of Time 1022 s total 5 11 s avg 3 s to 13 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 2 avg 0 to 2 max 0 72 Rows examined 3 61k avg 110 to 19 59k max 19 81 Database Users drhd 192 168 1 18 100 00 211 of query 100 00 6256 of all users Query abstract SELECT h doc id MAX h doc his edit time AS doc his edit time FROM t wiki doc his h WHERE h doc his isteammate N AND h doc his state N AND h doc his editor user id N GROUP BY h doc id ORDER BY h doc his edit time DESC LIMIT N N Query sample select h doc id max h doc his edit time as doc his edit time from t wiki doc his h where h doc his isteammate 1 and h doc his state 1 and h doc his editor user id 300000178518 group by h doc id order by h doc his edit time desc limit 0 2 解决方案 此 sql 由查前面一个 sql 代替 删除 006 Count 30 0 48 Time 940 s total 31 333333 s avg 3 s to 77 s max 2 76 95 of Time 790 s total 28 214286 s avg 3 s to 69 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 1 avg 1 to 1 max 0 05 Rows examined 10 96k avg 1 82k to 28 12k max 8 56 Database docresource Users drhd 192 168 1 18 100 00 30 of query 100 00 6256 of all users Query abstract SELECT COUNT FROM t wiki doc his WHERE doc his check expert id en S AND doc his check state N AND doc his deal time BETWEEN S AND S Query sample SELECT COUNT FROM T WIKI DOC HIS WHERE DOC HIS CHECK EXPERT ID EN uAwgHUUNYU1lfUFkJ AND DOC HIS CHECK STATE 1 AND DOC HIS DEAL TIME BETWEEN 2009 02 16 00 00 00 AND 2009 02 24 00 00 00 解决方案 此 sql 有用到 DOC HIS CHECK EXPERT ID EN 的索引 再观察 007 Count 134 2 14 Time 823 s total 6 141791 s avg 3 s to 23 s max 2 41 95 of Time 698 s total 5 496063 s avg 3 s to 15 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 10 avg 2 to 10 max 2 31 Rows examined 792 avg 53 to 9 55k max 2 76 Database Users drhd 192 168 1 18 100 00 134 of query 100 00 6256 of all users Query abstract SELECT doc id doc class doc title doc creator user id doc creator user nick doc latest edition doc lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 state IN N4 AND doc creator user id encrypt S ORDER BY doc goodcount 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 lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 state in 1 2 3 4 and doc creator user id encrypt lGwdcWgJBBAtVCnER order by DOC GOODCOUNT desc limit 380 10 解决方案 跟产品确认已经没用的功能 去掉 008 Count 130 2 08 Time 730 s total 5 615385 s avg 3 s to 21 s max 2 14 95 of Time 614 s total 4 99187 s avg 3 s to 14 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 10 avg 5 to 10 max 2 24 Rows examined 763 avg 32 to 4 16k max 2 58 Database Users drhd 192 168 1 18 100 00 130 of query 100 00 6256 of all users Query abstract SELECT doc id doc class doc title doc creator user id doc creator user nick doc latest edition doc lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 state IN N4 AND doc creator user id encrypt S ORDER BY doc goodcount ASC LIMIT N N Query sample select doc id doc class doc title doc creator user id doc creator user nick doc latest edition doc lat est 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 good count doc badcount doc first img doc title upper doc import tag doc topic count doc post co unt doc creator user id encrypt 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 state in 1 2 3 4 and doc creator user id encrypt bAQReRlFZX0BZW1oD order by DOC GOODCOUNT asc limit 370 10 解决方案 跟产品确认已经没用的功能 去掉 009 Count 83 1 33 Time 527 s total 6 349398 s avg 3 s to 29 s max 1 54 95 of Time 408 s total 5 230769 s avg 3 s to 16 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 1 avg 1 to 1 max 0 14 Rows examined 1 08k avg 96 to 3 84k max 2 34 Database Users drhd 192 168 1 18 100 00 83 of query 100 00 6256 of all users Query abstract SELECT COUNT DISTINCT h doc id FROM t wiki doc his h WHERE h doc his isteammate N AND h doc his editor user id N Query sample SELECT COUNT DISTINCT H DOC ID FROM T WIKI DOC HIS H WHERE H DOC HIS ISTEAMMATE 1 AND H DOC HIS EDITOR USER ID 300000328622 解决方案 使用 editor user iden docid 索引 1 改 sql SELECT h doc id FROM t wiki doc his h WHERE h doc his isteammate N AND h DOC HIS EDITOR USER ID ENCRYPT N group by doc id 2 取得前一 sql 返回的结果集的 size 010 Count 94 1 50 Time 511 s total 5 43617 s avg 3 s to 21 s max 1 50 95 of Time 428 s total 4 808989 s avg 3 s to 11 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 10 avg 3 to 10 max 1 60 Rows examined 747 avg 32 to 5 14k max 1 83 Database Users drhd 192 168 1 18 100 00 94 of query 100 00 6256 of all users Query abstract SELECT doc id doc class doc title doc creator user id doc creator user nick doc latest edition doc lat est url doc created time doc latest edition time doc inner pic count doc outer pic count do