# User@Host: zjzc_app[zjzc_app] @ [10.252.148.16xx] Id: 6043127 # Query_time: 2.581184 Lock_time: 0.000336 Rows_sent: 6 Rows_examined: 6331310 SET timestamp=1472536079; select temp.* from ( SELECT * FROM (SELECT c.sn, c.userNick, c.mobilePhone, date_format(c.registerTime,'%Y/%m/%d') registerTime, cp.personName, cp.personCardNo, cp.gender, cp.personAuthStatus, asm.clientManagerSn, (select personName from ClientPersonalInfo where clientSn = asm.clientManagerSn) as clientManagerName, IF(1600=asm.clientManagerSn or 1600 IS null, 1, 0) as isMyself, IFNULL(cei.policyAreaSn,IF( LENGTH(SUBSTRING_INDEX(cm.`governAreas`,';',2))=LENGTH(cm.`governAreas`),SUBSTRING_INDEX(cm.`governAreas`,';',1),NULL )) policyAreaSn, area.areaName as policyAreaName, (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) FROM view_client_invest_summary WHERE status =2 AND clientSn=c.`sn` AND DATEDIFF(settlementDate,CURDATE()) <=7 AND DATEDIFF(settlementDate,CURDATE()) >=0 ) investBalance, (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) FROM view_client_invest_summary pr WHERE pr.clientSn = c.sn AND pr.status = 2 ) unclearedBalance, (SELECT IFNULL(SUM(capitalBalance), 0) + cai.availableBalance + cai.frozenWithDraw +tra.availableBalance FROM view_client_invest_summary WHERE clientSn = c.sn AND status in(1,2)) totalBalance, (cai.frozenWithDraw + cai.frozenPay) frozenBalance,(cai.availableBalance+tra.availableBalance) availableBalance FROM Client c LEFT JOIN ClientExtraInfo cei on c.sn=cei.clientSn LEFT JOIN Area area on cei.policyAreaSn=area.sn LEFT JOIN AssignClientManager asm ON asm.clientSn=c.sn LEFT JOIN ClientManager cm on cm.sn = asm.clientManagerSn LEFT JOIN TransferClientAssetInfo tra on c.sn=tra.clientSn, ClientPersonalInfo cp, ClientAssetInfo cai WHERE c.sn = cp.clientSn AND asm.status='1' AND c.sn = cai.clientSn AND exists (select * from ClientInvestOrder where clientSn = c.sn and status =3) AND c.sn IN (SELECT asm.clientSn FROM AssignClientManager asm WHERE asm.clientManagerSn IN ( 1600 ) AND asm.status='1') ) t ORDER BY t.clientManagerSn desc,t.sn desc ) temp limit 10,10; # Time: 160830 14:22:27 (?m)\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+\s+)*\S+)\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\s* SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\S+\s+).*)\s*#\s+Time:\s+.* { "user": [ [ "zjzc_app" ] ], "clientip": [ [ "10.252.148.164" ] ], "id": [ [ "Id: 6043127" ] ], "Query_time": [ [ "2.581184" ] ], "lock_time": [ [ "0.000336" ] ], "rows_sent": [ [ "6" ] ], "rows_examined": [ [ "6331310" ] ], "timestamp": [ [ "1472536079" ] ], "query": [ [ "select temp.* from ( SELECT \n\t\t *\n\t\tFROM\n\t\t (SELECT \n\t\t c.sn,\n\t c.userNick,\n\t c.mobilePhone,\n\t date_format(c.registerTime,'%Y/%m/%d') registerTime,\n\t cp.personName,\n\t cp.personCardNo,\n\t cp.gender,\n\t cp.personAuthStatus,\n\t asm.clientManagerSn,\n\t (select personName from ClientPersonalInfo where clientSn = asm.clientManagerSn) as clientManagerName,\n\t IF(1600=asm.clientManagerSn or 1600 IS null, 1, 0) as isMyself,\n\t IFNULL(cei.policyAreaSn,IF( LENGTH(SUBSTRING_INDEX(cm.`governAreas`,';',2))=LENGTH(cm.`governAreas`),SUBSTRING_INDEX(cm.`governAreas`,';',1),NULL )) policyAreaSn,\n\t area.areaName as policyAreaName,\n\t\t\t (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)\n\t\t\t\tFROM view_client_invest_summary \n\t\t\t\tWHERE status =2 AND clientSn=c.`sn` AND DATEDIFF(settlementDate,CURDATE()) <=7 \n\t\t\t\tAND DATEDIFF(settlementDate,CURDATE()) >=0 ) investBalance,\n\t\t\t (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)\n\t\t\t\tFROM view_client_invest_summary pr\n\t\t\t\tWHERE pr.clientSn = c.sn AND pr.status = 2 ) unclearedBalance,\n\t\t\t (SELECT IFNULL(SUM(capitalBalance), 0) + cai.availableBalance + cai.frozenWithDraw +tra.availableBalance\n\t\t\t\tFROM view_client_invest_summary\n\t\t\t\tWHERE clientSn = c.sn AND status in(1,2)) totalBalance,\n\t\t\t (cai.frozenWithDraw + cai.frozenPay) frozenBalance,(cai.availableBalance+tra.availableBalance) availableBalance\n\t\t FROM\n\t\t Client c \n\t\t LEFT JOIN ClientExtraInfo cei on c.sn=cei.clientSn \n\t\t LEFT JOIN Area area on cei.policyAreaSn=area.sn\n\t\t LEFT JOIN AssignClientManager asm ON asm.clientSn=c.sn\n\t\t LEFT JOIN ClientManager cm on cm.sn = asm.clientManagerSn\n\t\t LEFT JOIN TransferClientAssetInfo tra on c.sn=tra.clientSn, \n\t\t ClientPersonalInfo cp, ClientAssetInfo cai\n\t\t WHERE\n\t\t c.sn = cp.clientSn AND asm.status='1'\n\t\t AND c.sn = cai.clientSn\n\t\t \n\t\t \t\t \n\t\t \t\t\tAND exists (select * from ClientInvestOrder where clientSn = c.sn and status =3)\n\t\t \t\t \n\t\t \t\t \n\t\t AND c.sn IN (SELECT \n\t\t asm.clientSn\n\t\t FROM\n\t\t AssignClientManager asm\n\t\t WHERE\n\t\t asm.clientManagerSn IN \n\t\t ( \n \t\t 1600\n \t\t ) \n \t\t \n \t\tAND asm.status='1')\t\t\n\t\t ) t\n\t\t \n\t\tORDER BY\n\t\t \n\t\tt.clientManagerSn desc,t.sn desc ) temp limit 10,10;\n" ] ] }
logstash 解析mysql slow log
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
上一篇:7u21 与 8u20
下一篇:回车提交JS
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
logstash解析mysql log logstash解析电子邮件日志,获取meaasageid
有没有一种简单的方法可以将电子邮件索引到 Elasticsearch? Logstash 是答案。 Logstash 是一个开放源代码的服务器端数据处理管道,它同时从多个源中提取数据,进行转换,然后将其发送到你喜欢的“存储(stash)”中。 在这里,“存储(stash)”是指诸如 Elasticsearch,PagerDuty,电子邮件,Nagios,Jira 等产品。Logstash 事件处理
logstash解析mysql log Elasticsearch Elastic elasticsearch 大数据