logstash 解析mysql slow log

# 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*
SETs+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 
		    *
		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;
"
    ]
  ]
}