logstash 解析mysql slow log
2024-09-26 20:08:08
# 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"
]
]
}
最新文章
- EQueue 2.3.2版本发布（支持高可用）
- <三>JDBC_面向对象思想的体现
- FileInputStream类
- 详解 CSS 属性 - :before && :after
- scala学习笔记(9)：Scala函数(2)
- 在Ubuntu 14.04 64bit上安装numpy和matplotlib库
- android从应用到驱动之—camera(1)---程序调用流程
- JavaScript标准Selection操作
- error LNK2019
- 电脑开机失败提示"用户配置文件服务登录失败"的三种解决办法
- Maven运行报错
- python学习：输入中文
- HQL详解
- 学爬虫，需要掌握哪些Python基础？
- 使用配置文件自定义Ribbon配置
- python 文本分类
- easyUi 表头排序按钮 bug
- Java内部类引用外部类中的局部变量为何必须是final问题解析
- 洛谷 P2659 美丽的序列 解题报告
- KVC的底层实现原理