前言
日常开发中,我们经常会查看慢SQL日志,来确定哪些SQL语句需要优化、哪些表需要加索引等。但是慢SQL日志文件的格式特别不便于阅读,一条SQL记录可能会占很多行,而且还有很多空行,所以用代码实现其格式化可以提供适当的便利。
(这是我实习的第一次写代码的任务,所以记录一下)
这里先看看慢SQL文件的内容,可以看出一条记录的篇幅太大,特别不方便阅读。
再看看格式化后的效果,明显能看出好了很多,并且按SQL的部分语句排序,将相似的SQL放到一起,更能体现哪些表的哪些操作形成的慢SQL。
一、主要作用:
1.将单条记录打印为单行
2.仅打印主要字段即可(时间、用户、主机名、线程ID、操作的数据库、SQL执行时间、SQL语句查询返回的行数和检索的行数、SQL语句)
二、代码实现:
主要是根据慢SQL日志单条记录的特点,进行字符串分割,提取所需字段来实现
2.1 单条记录类(LogStatement ):
public class LogStatement { private String date; //日期 private String time; //时间 private String user; //用户 private String host; //主机名 private String TheadId; //线程ID private String schema; //查询的数据库 private String queryTime; //查询时间 private String row_sent; //返回的行数 private String row_examined;//检索的行数 private String sql; //SQL语句 private String orderFlag; //排序字段 @Override //格式化打印语句 public String toString() { return date+"-"+time+" "+user+"@"+host+" thead_id:"+TheadId+" "+schema+" "+queryTime +"s Rows_sent/Rows_examined:"+row_sent+"/"+row_examined+"————"+sql; } //构造方法,可根据实际情况生成其他的构造方法 public LogStatement(String date, String time) { this.date = date; this.time = time; } //后面都是getter和setter public String getDate() { return date; } public void setDate(String date) { this.date = date; } public String getTime() { return time; } public void setTime(String time) { this.time = time; } public String getUser() { return user; } public void setUser(String user) { this.user = user; } public String getHost() { return host; } public void setHost(String host) { this.host = host; } public String getTheadId() { return TheadId; } public void setTheadId(String theadId) { TheadId = theadId; } public String getSchema() { return schema; } public void setSchema(String schema) { this.schema = schema; } public String getQueryTime() { return queryTime; } public void setQueryTime(String queryTime) { this.queryTime = queryTime; } public String getRow_sent() { return row_sent; } public void setRow_sent(String row_sent) { this.row_sent = row_sent; } public String getRow_examined() { return row_examined; } public void setRow_examined(String row_examined) { this.row_examined = row_examined; } public String getSql() { return sql; } public void setSql(String sql) { this.sql = sql; } public String getOrderFlag() { return orderFlag; } public void setOrderFlag(String orderFlag) { this.orderFlag = orderFlag; } }
2.2 逻辑处理类(MySQLSlowLogParser):
2.2.1 成员变量
private static int totalSlowSQL; //总的慢SQL条数 //后面截取SQL的排序字段时,需要根据SQL类型定义不同的分割符进行截取 private static final String INSERT_STM = "insert"; private static final String UPDATE_STM = "update"; private static final String SELECT_STM = "select"; private static final List records = new ArrayList(); //存单条记录的集合 private static final List logs = new ArrayList(); //格式化后的记录
2.2.2 main方法:
public static void main(String[] args) { Scanner scan = new Scanner(System.in); System.out.println("请输入要解析的 MySQL/MariaDB 慢SQL的全路径:"); if (scan.hasNextLine()) { String filePath = scan.nextLine(); //读取文件路径 parse(filePath); //解析对应文件 } getResult(); //提取每条记录的关键信息 sortResult(); //将结果进行排序 printResult(); //打印结果 }
2.2.3 parse方法:
作用是解析文件,读取每一行的内容,合并单条记录的内容,把多行合并为一行,并存入单条记录的集合records。例如第一条慢SQL记录会转为两条记录存入:
# Time: 221026 0:19:59
User@Host: msg[msg] @ [172.27.6.20] Thread_id: 2766408 Schema: trs_hycloud_msg QC_hit: No Query_time: 5.192931 Lock_time: 0.000422 Rows_sent: 1 Rows_examined: 150436 Rows_affected: 0 Bytes_sent: 60 //后面的就省略了
我这里的处理是将时间也作为单条记录存起来,因为慢SQL日志中会出现多条记录为同一时间执行的,方便后面为这种情况的记录的时间赋值(文章末尾我会将我的慢SQL文件的完整内容附上,便于理解)
private static void parse(String filePath) { System.out.println("开始解析:" + filePath); //声明流对象 InputStream is = null; Reader reader = null; BufferedReader bufferedReader = null; try { //以缓冲流的的方式读取数据 is = new FileInputStream(new File(filePath)); reader = new InputStreamReader(is, "utf-8"); bufferedReader = new BufferedReader(reader); String singleSQL = ""; //用来存完整的单条慢SQL记录 String line; //用来存每一行读取的数据 //根据慢日志文件的单条记录的特点进行处理 while ((line = bufferedReader.readLine()) != null) { if (line.startsWith("# Time:")) { //当前行以“# Time:”开头的情况 covertAndAddStatement(singleSQL); //那么之前的语句为一条完整记录,将singleSQL进行转换 while(line.contains(" ")) //将多个空格保留为1个 line = line.replace(" "," "); records.add(line); //直接将当前行的时间存入记录的集合,因为有的记录共享一个时间 singleSQL = ""; //处理完前一条后,要重新拼记录,令singleSQL为空 } else if (line.startsWith("# User@Host")){ //当前行以“# User@Host”开头的情况 covertAndAddStatement(singleSQL); //那么之前的语句也为一条完整记录,将singleSQL进行转换 singleSQL = line; //当前行作为新的记录的开头 }else { singleSQL += line + " "; //不满足前两个,则直接把当前行加入,作为单条记录的一部分 } //末尾加空格是为了将两行之间以空格隔开 } //还要处理最后一句,因为最后一条记录的后面没有“# Time:”或“# User@Host”,while循环不会执行到最后一句 covertAndAddStatement(singleSQL); } catch (IOException e) { System.err.println("Error:" + e); } finally { //释放资源 try {if (bufferedReader != null) bufferedReader.close();} catch (IOException e) { System.err.println("Error:" + e); } try {if (reader != null) reader.close();} catch (IOException e) { System.err.println("Error:" + e); } try {if (is != null) is.close();} catch (IOException e) { System.err.println("Error:" + e); } } }
2.2.4 covertAndAddStatement方法:
作用是将records中的记录进行初步的格式化,将多个空格替换为一个,并将“#”号去掉。
private static void covertAndAddStatement(String statement){ if(statement.equals("")) //空字符串直接不处理 return; //去掉“#”号 statement = statement.replace("#"," "); //多个空格替换为1个,因为文件中两个单词之间的空格个能不止一个, //就算将多行合并为一行,也会有多个空格的存在 while(statement.contains(" ")) statement = statement.replace(" "," "); records.add(statement); }
2.2.5 getResult方法:
主要作用是将格式化后的单条记录,提取关键字段,用对象封装,并存入结果集
private static void getResult(){ //遍历单条记录,处理结果,加入结果集 String date = ""; //有多条记录的时间相同,所以要把时间放循环体外,方便为多条记录赋值 String time = ""; for (String record : records) { if (record.contains("# Time")){ //更新即将处理的记录的时间 String[] tmp = record.split(" "); date = tmp[2]; time = tmp[3]; if (time.length()
2.2.2.5 getTags方法:
主要作用是提取除SQL以外的所需字段,封装到对象中
经过前面的处理,tags数组的内容形式大致如下,根据字段所在位置,就可以得到想要的字段
tags = {"", "User@Host:", "msg[msg]", "@", "[172.27.6.20]", "Thread_id:", "2766408", "Schema:", "trs_hycloud_msg", "QC_hit:", "No Query_time:", "5.192931", "Lock_time:", "0.000422", "Rows_sent:", "1", "Rows_examined:", "150436", "Rows_affected:", "0", "Bytes_sent:", "60", ...}
private static void getTags(LogStatement log, String info){ String[] tags = info.split(" "); //用户 log.setUser(tags[2]); //主机 log.setHost(tags[4]); //Threadid log.setTheadId(tags[6]); //操作的数据库 log.setSchema(tags[8]); //查询时间 log.setQueryTime(tags[12]); //执行成功后返回的行数 log.setRow_sent(tags[16]); //检索的行数 log.setRow_examined(tags[18]); }
2.2.2.6
作用是获取SQL语句,封装到对象
下面方法中的statemen的内容形式大致如下,截取第一个分号后的语句则可以得到SQL语句
statement="=1666743599; select count(* from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE";
private static void getSQL(LogStatement log, String statement){ //第一个分号后就是SQL,所以从第一个分号出现的位置+1分割字符串就可以得到SQL int subIndex = statement.indexOf(';'); String sql = statement.substring(subIndex+1).trim(); //提取SQL log.setSql(sql); getOrderFlag(log, sql); }
2.2.2.7 getOrderFlag方法:
主要作用是提取排序字段,装入对象中,提取方式是根据不同种类SQL语句的特点,截取部分字段。
private static void getOrderFlag(LogStatement log, String sql){ //提取部分SQL作为排序字段 sql = sql.toLowerCase(); //先转小写,避免大小写不统一的情况 int index = sql.indexOf(";"); //先令sql分割点为末尾 if (sql.startsWith(INSERT_STM)){ //根据sql语句来定分割点 index = !sql.contains("values") ? index : sql.indexOf("values"); }else if (sql.startsWith(UPDATE_STM)){ index = !sql.contains("set") ? index : sql.indexOf("set"); }else if (sql.startsWith(SELECT_STM)){ index = !sql.contains("where") ? index : sql.indexOf("where"); } log.setOrderFlag(sql.substring(0, index)); //将截取后的sql语句设置为排序字段 }
2.2.2.8 sortResult方法:
作用是将结果集根据排序字段排序
private static void sortResult(){ // 排序方法 logs.sort(new Comparator() { @Override public int compare(LogStatement o1, LogStatement o2) { return o1.getOrderFlag().compareTo(o2.getOrderFlag()); } }); }
2.2.2.9 打印结果:
private static void printResult(){ //打印结果 System.out.println("慢总SQL条数:" + "t" + totalSlowSQL); System.out.println(); for (LogStatement log : logs) { System.out.println(log); } }
2.3完整代码
import java.io.*; import java.util.ArrayList; import java.util.Comparator; import java.util.List; import java.util.Scanner; public class MySQLSlowLogParser { private static int totalSlowSQL; //总的慢SQL条数 private static final String INSERT_STM = "insert"; private static final String UPDATE_STM = "update"; private static final String SELECT_STM = "select"; private static final List records = new ArrayList(); //存单条记录的集合 private static final List logs = new ArrayList(); //存筛选字段后的记录 public static void main(String[] args) { Scanner scan = new Scanner(System.in); System.out.println("请输入要解析的 MySQL/MariaDB 慢SQL的全路径:"); if (scan.hasNextLine()) { String filePath = scan.nextLine(); //读取文件路径 // System.out.println(filePath); parse(filePath); //解析对应文件 } getResult(); //提取每条记录的关键信息 sortResult(); //将结果进行排序 printResult(); //打印结果 } private static void parse(String filePath) { System.out.println("开始解析:" + filePath); InputStream is = null; Reader reader = null; BufferedReader bufferedReader = null; try { //以缓冲流的的方式读取数据 is = new FileInputStream(new File(filePath)); reader = new InputStreamReader(is, "utf-8"); bufferedReader = new BufferedReader(reader); String singleSQL = ""; //用来存完整的单条慢SQL记录 String line; while ((line = bufferedReader.readLine()) != null) { if (line.startsWith("# Time:")) { //当前行以“# Time:”开头的情况 covertAndAddStatement(singleSQL); //那么之前的语句为一条完整记录,将singleSQL进行转换 while(line.contains(" ")) //将多个空格保留为1个 line = line.replace(" "," "); records.add(line); //直接将当前行的时间存入记录的集合,因为有的记录共享一个时间 singleSQL = ""; //处理完前一条后,要重新拼记录,令singleSQL为空 } else if (line.startsWith("# User@Host")){ //当前行以“# User@Host”开头的情况 covertAndAddStatement(singleSQL); //那么之前的语句也为一条完整记录,将singleSQL进行转换 singleSQL = line; //当前行作为新的记录的开头 }else { singleSQL += line + " "; //不满足前两个,则直接把当前行加入,作为单条记录的一部分 } //末尾加空格是为了将两行之间以空格隔开 } //还要处理最后一句,因为最后一条记录的后面没有“# Time:”或“# User@Host”,while循环不会执行到最后一句 covertAndAddStatement(singleSQL); } catch (IOException e) { System.err.println("Error:" + e); } finally { //释放资源 try {if (bufferedReader != null) bufferedReader.close();} catch (IOException e) { System.err.println("Error:" + e); } try {if (reader != null) reader.close();} catch (IOException e) { System.err.println("Error:" + e); } try {if (is != null) is.close();} catch (IOException e) { System.err.println("Error:" + e); } } } private static void covertAndAddStatement(String statement){ if(statement.equals("")) //空字符串直接不处理 return; //去掉“#”号 statement = statement.replace("#"," "); //多个空格替换为1个 while(statement.contains(" ")) statement = statement.replace(" "," "); records.add(statement); } private static void getResult(){ //遍历单条记录,处理结果,加入结果集 String date = ""; //有多条记录的时间相同,所以要把时间放循环体外,方便为多条记录赋值 String time = ""; for (String record : records) { if (record.contains("# Time")){ //若当前的record为时间,则更新即将处理的记录的时间 String[] tmp = record.split(" "); date = tmp[2]; time = tmp[3]; if (time.length()() { @Override public int compare(LogStatement o1, LogStatement o2) { return o1.getOrderFlag().compareTo(o2.getOrderFlag()); } }); } private static void printResult(){ //打印结果 System.out.println("慢总SQL条数:" + "t" + totalSlowSQL); System.out.println(); for (LogStatement log : logs) { System.out.println(log); } } }
最后附上SQL文件的内容,便于有兴趣的小伙伴进行调试,可以直接新建txt文件,赋值粘贴进去,保存后。调试时输入该文件的全路径即可。
从下面的内容也可以看出,MySQL自动生成的慢SQL文件多么难以阅读
# Time: 221026 0:19:59 # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2766408 Schema: trs_hycloud_msg QC_hit: No # Query_time: 5.192931 Lock_time: 0.000422 Rows_sent: 1 Rows_examined: 150436 # Rows_affected: 0 Bytes_sent: 60 use trs_hycloud_msg; SET timestamp=1666743599; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '889' , '894' , '899' , '902' , '905' , '1190' , '1191' , '1192' , '1193' , '1447' , '1703' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '712' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '13' , '851' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-05-15 11:20:18' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '712' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids; # Time: 221026 0:36:21 # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2766515 Schema: trs_hycloud_msg QC_hit: No # Query_time: 2.585773 Lock_time: 0.004551 Rows_sent: 1 Rows_examined: 5360 # Rows_affected: 0 Bytes_sent: 56 SET timestamp=1666744581; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '570' , '577' , '578' , '580' , '583' , '586' , '746' , '1174' , '1536' , '1537' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '170' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '305' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-04-08 10:32:51' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '170' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids; # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2766408 Schema: trs_hycloud_msg QC_hit: No # Query_time: 6.523476 Lock_time: 0.000227 Rows_sent: 1 Rows_examined: 5360 # Rows_affected: 0 Bytes_sent: 56 SET timestamp=1666744581; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '570' , '577' , '578' , '580' , '583' , '586' , '746' , '1174' , '1536' , '1537' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '170' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '305' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-04-08 10:32:51' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '170' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids; # Time: 221026 0:49:59 # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2766515 Schema: trs_hycloud_msg QC_hit: No # Query_time: 2.193934 Lock_time: 0.000306 Rows_sent: 1 Rows_examined: 142368 # Rows_affected: 0 Bytes_sent: 60 SET timestamp=1666745399; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '746' , '747' , '842' , '845' , '849' , '855' , '856' , '857' , '858' , '859' , '860' , '861' , '1233' , '1326' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '620' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '11' , '918' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-05-12 08:50:15' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '620' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids; # Time: 221026 1:21:40 # User@Host: ids[ids] @ [172.27.9.48] # Thread_id: 2766762 Schema: trs_ids QC_hit: No # Query_time: 4.315032 Lock_time: 0.000072 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 1 Bytes_sent: 14 use trs_ids; SET timestamp=1666747300; insert into `IDSLOG` (`LOGTIME`, `LOGUSER`, `LOGTYPE`, `LOGDESC`, `HOSTIP`, `IDSSESS`, `COAPP`, `COSESS`, `LOGRESULT`, `DETAIL`, `CALLER`, `PROXYIPS`, `USERAGENT`, `SIGNATURE`, `REGFROM`, `REGCOAPP`, `ELAPSEDTIME`) values ('2022-10-26 09:24:14', '营山县发展公司_报送', 1, '用户登录协作应用', '172.27.4.227', '95997EFDEA8E3688D77E67D9F89D7935-172.27.9.48', 'IIP', '2D6ECDA7219778FC64BF21786251A1BF', 0, 'N/A', 'com.trs.idm.model.logging.LogManager.logLoginEvent(LogManager.java:177)0; # Time: 221026 19:24:59 # User@Host: igi[igi] @ [172.27.7.22] # Thread_id: 2770625 Schema: trs_hycloud_igi QC_hit: No # Query_time: 3.789189 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 11 SET timestamp=1666812299; commit; # User@Host: mas[mas] @ [172.27.10.89] # Thread_id: 2418700 Schema: trs_mas QC_hit: No # Query_time: 3.527154 Lock_time: 0.050343 Rows_sent: 0 Rows_examined: 4 # Rows_affected: 0 Bytes_sent: 1498 use trs_mas; SET timestamp=1666812299; select processjob0_.`ID` as ID1_32_, processjob0_.`CREATEDTIME` as CREATEDT2_32_, processjob0_.`CREATEDUSER` as CREATEDU3_32_, processjob0_.`CREATEDUSERID` as CREATEDU4_32_, processjob0_.`CREATEDUSERNICKNAME` as CREATEDU5_32_, processjob0_.`LASTMODIFIEDTIME` as LASTMODI6_32_, processjob0_.`LASTMODIFIEDUSER` as LASTMODI7_32_, processjob0_.`LASTMODIFIEDUSERID` as LASTMODI8_32_, processjob0_.`CREATORNODEKEY` as CREATORN9_32_, processjob0_.`MARKERNODEKEY` as MARKERN10_32_, processjob0_.`DETAIL` as DETAIL11_32_, processjob0_.`DOMAINOBJID` as DOMAINO12_32_, processjob0_.`MARKTIME` as MARKTIME13_32_, processjob0_.`PROCESSORDER` as PROCESS14_32_, processjob0_.`SOURCETYPE` as SOURCETYPE15_32_, processjob0_.`STATE` as STATE16_32_, processjob0_.`STATUS` as STATUS17_32_, processjob0_.`TYPE` as TYPE18_32_ from MAS_PROCESSJOB processjob0_ where processjob0_.`STATE`='NEW' order by processjob0_.`PROCESSORDER` asc limit 1; # User@Host: ipm[ipm] @ [172.27.6.71] # Thread_id: 1173218 Schema: trs_hycloud_ipm QC_hit: No # Query_time: 4.021290 Lock_time: 0.050219 Rows_sent: 1 Rows_examined: 1 # Rows_affected: 0 Bytes_sent: 529 use trs_hycloud_ipm; SET timestamp=1666812299; SELECT * FROM QRTZ_SCHEDULER_STATE WHERE SCHED_NAME = 'kpi'; # User@Host: mas[mas] @ [172.27.10.89] # Thread_id: 2412354 Schema: trs_mas QC_hit: No # Query_time: 3.266587 Lock_time: 0.050510 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 4382 use trs_mas; SET timestamp=1666812299; select live0_.`ID` as ID1_46_, live0_.`CREATEDTIME` as CREATEDT2_46_, live0_.`CREATEDUSER` as CREATEDU3_46_, live0_.`CREATEDUSERID` as CREATEDU4_46_, live0_.`CREATEDUSERNICKNAME` as CREATEDU5_46_, live0_.`LASTMODIFIEDTIME` as LASTMODI6_46_, live0_.`LASTMODIFIEDUSER` as LASTMODI7_46_, live0_.`LASTMODIFIEDUSERID` as LASTMODI8_46_, live0_.`ATTACHEDPIC` as ATTACHED9_46_, live0_.`AUDIOBITRATE` as AUDIOBI10_46_, live0_.`AUDIOCHANNELS` as AUDIOCH11_46_, live0_.`AUDIOCODEC` as AUDIOCODEC12_46_, live0_.`AUDIOFORMAT` as AUDIOFO13_46_, live0_.`AUDIOSAMPLERATE` as AUDIOSA14_46_, live0_.`BITRATE` as BITRATE15_46_, live0_.`DEMUXER` as DEMUXER16_46_, live0_.`DURATION` as DURATION17_46_, live0_.`DURATIONOFDOUBLE` as DURATIO18_46_, live0_.`FPS` as FPS19_46_, live0_.`FRAMERATE` as FRAMERATE20_46_, live0_.`HEIGHT` as HEIGHT21_46_, live0_.`IFRAMES` as IFRAMES22_46_, live0_.`mediaType` as mediaType23_46_, live0_.`NBFRAMES` as NBFRAMES24_46_, live0_.`PIXELFORMAT` as PIXELFO25_46_, live0_.`ROTATE` as ROTATE26_46_, live0_.`VIDEOCODEC` as VIDEOCODEC27_46_, live0_.`VIDEOFORMAT` as VIDEOFO28_46_, live0_.`VIDEOLEVEL` as VIDEOLEVEL29_46_, live0_.`VIDEOPROFILE` as VIDEOPR30_46_, live0_.`WIDTH` as WIDTH31_46_, live0_.`IOSLIVENAME` as IOSLIVE32_46_, live0_.`LIVE_STATUS` as LIVE33_46_, live0_.`NAME` as NAME34_46_, live0_.`PREDICTION` as PREDICTION35_46_, live0_.`STATUS` as STATUS36_46_, live0_.`STREAMCOUNT` as STREAMC37_46_, live0_.`SUPPORTIOSDEVICE` as SUPPORT38_46_, live0_.`TITLE` as TITLE39_46_, live0_.`ENDTIME` as ENDTIME40_46_, live0_.`ISLIVEVIDEOONLIVE` as ISLIVEV41_46_, live0_.`ISSTARTFFMPEG` as ISSTART42_46_, live0_.`LIVE_DEVICE` as LIVE43_46_, live0_.`LIVEROLETYPE` as LIVEROL44_46_, live0_.`LIVE_TYPE` as LIVE45_46_, live0_.`LOGONAME` as LOGONAME46_46_, live0_.`ORIGINLIVEID` as ORIGINL47_46_, live0_.`PLAYCOUNT` as PLAYCOUNT48_46_, live0_.`PROBLEMATIC` as PROBLEM49_46_, live0_.`RECORDCATEGORYID` as RECORDC50_46_, live0_.`RECORDVIDEOID` as RECORDV51_46_, live0_.`RECORDVIDEOTITLE` as RECORDV52_46_, live0_.`RECORDING` as RECORDING53_46_, live0_.`REGION` as REGION54_46_, live0_.`RELATEDVIDEOID` as RELATED55_46_, live0_.`RELATEDVIDEOTITLE` as RELATED56_46_, live0_.`SRCTRANSPARAM` as SRCTRAN57_46_, live0_.`SRCTYPE` as SRCTYPE58_46_, live0_.`SRCURL` as SRCURL59_46_, live0_.`STARTTIME` as STARTTIME60_46_, live0_.`TIMING_END` as TIMING61_46_, live0_.`TIMING_START` as TIMING62_46_ from MAS_LIVE live0_ order by live0_.`ID` desc limit 1000; # User@Host: igi[igi] @ [172.27.7.23] # Thread_id: 2773636 Schema: trs_hycloud_igi QC_hit: No # Query_time: 4.038223 Lock_time: 0.000073 Rows_sent: 2 Rows_examined: 2 # Rows_affected: 0 Bytes_sent: 637 use trs_hycloud_igi; SET timestamp=1666812299; SELECT * FROM qrtz_SCHEDULER_STATE WHERE SCHED_NAME = 'quartzScheduler'; # Time: 221026 19:25:02 # User@Host: ipm[ipm] @ [172.27.6.71] # Thread_id: 1173218 Schema: trs_hycloud_ipm QC_hit: No # Query_time: 2.912537 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 11 use trs_hycloud_ipm; SET timestamp=1666812302; commit; # User@Host: igi[igi] @ [172.27.7.23] # Thread_id: 2773636 Schema: trs_hycloud_igi QC_hit: No # Query_time: 2.912705 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 11 use trs_hycloud_igi; SET timestamp=1666812302; commit; # Time: 221026 19:25:03 # User@Host: mas[mas] @ [172.27.10.89] # Thread_id: 2418710 Schema: trs_mas QC_hit: No # Query_time: 2.682208 Lock_time: 0.050374 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 4382 use trs_mas; SET timestamp=1666812303; select live0_.`ID` as ID1_46_, live0_.`CREATEDTIME` as CREATEDT2_46_, live0_.`CREATEDUSER` as CREATEDU3_46_, live0_.`CREATEDUSERID` as CREATEDU4_46_, live0_.`CREATEDUSERNICKNAME` as CREATEDU5_46_, live0_.`LASTMODIFIEDTIME` as LASTMODI6_46_, live0_.`LASTMODIFIEDUSER` as LASTMODI7_46_, live0_.`LASTMODIFIEDUSERID` as LASTMODI8_46_, live0_.`ATTACHEDPIC` as ATTACHED9_46_, live0_.`AUDIOBITRATE` as AUDIOBI10_46_, live0_.`AUDIOCHANNELS` as AUDIOCH11_46_, live0_.`AUDIOCODEC` as AUDIOCODEC12_46_, live0_.`AUDIOFORMAT` as AUDIOFO13_46_, live0_.`AUDIOSAMPLERATE` as AUDIOSA14_46_, live0_.`BITRATE` as BITRATE15_46_, live0_.`DEMUXER` as DEMUXER16_46_, live0_.`DURATION` as DURATION17_46_, live0_.`DURATIONOFDOUBLE` as DURATIO18_46_, live0_.`FPS` as FPS19_46_, live0_.`FRAMERATE` as FRAMERATE20_46_, live0_.`HEIGHT` as HEIGHT21_46_, live0_.`IFRAMES` as IFRAMES22_46_, live0_.`mediaType` as mediaType23_46_, live0_.`NBFRAMES` as NBFRAMES24_46_, live0_.`PIXELFORMAT` as PIXELFO25_46_, live0_.`ROTATE` as ROTATE26_46_, live0_.`VIDEOCODEC` as VIDEOCODEC27_46_, live0_.`VIDEOFORMAT` as VIDEOFO28_46_, live0_.`VIDEOLEVEL` as VIDEOLEVEL29_46_, live0_.`VIDEOPROFILE` as VIDEOPR30_46_, live0_.`WIDTH` as WIDTH31_46_, live0_.`IOSLIVENAME` as IOSLIVE32_46_, live0_.`LIVE_STATUS` as LIVE33_46_, live0_.`NAME` as NAME34_46_, live0_.`PREDICTION` as PREDICTION35_46_, live0_.`STATUS` as STATUS36_46_, live0_.`STREAMCOUNT` as STREAMC37_46_, live0_.`SUPPORTIOSDEVICE` as SUPPORT38_46_, live0_.`TITLE` as TITLE39_46_, live0_.`ENDTIME` as ENDTIME40_46_, live0_.`ISLIVEVIDEOONLIVE` as ISLIVEV41_46_, live0_.`ISSTARTFFMPEG` as ISSTART42_46_, live0_.`LIVE_DEVICE` as LIVE43_46_, live0_.`LIVEROLETYPE` as LIVEROL44_46_, live0_.`LIVE_TYPE` as LIVE45_46_, live0_.`LOGONAME` as LOGONAME46_46_, live0_.`ORIGINLIVEID` as ORIGINL47_46_, live0_.`PLAYCOUNT` as PLAYCOUNT48_46_, live0_.`PROBLEMATIC` as PROBLEM49_46_, live0_.`RECORDCATEGORYID` as RECORDC50_46_, live0_.`RECORDVIDEOID` as RECORDV51_46_, live0_.`RECORDVIDEOTITLE` as RECORDV52_46_, live0_.`RECORDING` as RECORDING53_46_, live0_.`REGION` as REGION54_46_, live0_.`RELATEDVIDEOID` as RELATED55_46_, live0_.`RELATEDVIDEOTITLE` as RELATED56_46_, live0_.`SRCTRANSPARAM` as SRCTRAN57_46_, live0_.`SRCTYPE` as SRCTYPE58_46_, live0_.`SRCURL` as SRCURL59_46_, live0_.`STARTTIME` as STARTTIME60_46_, live0_.`TIMING_END` as TIMING61_46_, live0_.`TIMING_START` as TIMING62_46_ from MAS_LIVE live0_ order by live0_.`ID` desc limit 1000; # User@Host: igi[igi] @ [172.27.7.23] # Thread_id: 2773253 Schema: trs_hycloud_igi QC_hit: No # Query_time: 2.627624 Lock_time: 0.050452 Rows_sent: 1 Rows_examined: 10 # Rows_affected: 0 Bytes_sent: 4782 use trs_hycloud_igi; SET timestamp=1666812303; select interview0_.id as id1_48_, interview0_.create_date as create_d2_48_, interview0_.modify_date as modify_d3_48_, interview0_.category as category4_48_, interview0_.channel_id as channel_5_48_, interview0_.interview_comment as intervie6_48_, interview0_.cr_user as cr_user7_48_, interview0_.delete_content as delete_c8_48_, interview0_.end_time as end_time9_48_, interview0_.enter_company as enter_c10_48_, interview0_.ex_link as ex_link11_48_, interview0_.ext_audio_url as ext_aud12_48_, interview0_.ext_video_url as ext_vid13_48_, interview0_.guests as guests14_48_, interview0_.interview_flag as intervi15_48_, interview0_.invalid_time as invalid16_48_, interview0_.is_auto_publish as is_auto17_48_, interview0_.is_need_advance as is_need18_48_, interview0_.is_need_item_memoir as is_need19_48_, interview0_.is_public as is_publ20_48_, interview0_.keyword as keyword21_48_, interview0_.live_url as live_ur22_48_, interview0_.online_company as online_23_48_, interview0_.oper_ip as oper_ip24_48_, interview0_.oper_user as oper_us25_48_, interview0_.propaganda as propaga26_48_, interview0_.publish_error_reason as publish27_48_, interview0_.publish_url as publish28_48_, interview0_.record_audio_url as record_29_48_, interview0_.record_video_url as record_30_48_, interview0_.site_id as site_id31_48_, interview0_.sort_order as sort_or32_48_, interview0_.sort_top_order as sort_to33_48_, interview0_.start_time as start_t34_48_, interview0_.status as status35_48_, interview0_.subtitle as subtitl36_48_, interview0_.title as title37_48_, interview0_.top_type as top_typ38_48_, interview0_.type as type39_48_ from trs_interview interview0_ where (interview0_.site_id in (48)) and interview0_.is_public=1 and interview0_.status=0 order by interview0_.start_time desc limit 1; # User@Host: ids[ids] @ [172.27.9.48] # Thread_id: 2773707 Schema: trs_ids QC_hit: No # Query_time: 2.767730 Lock_time: 0.000157 Rows_sent: 86 Rows_examined: 172 # Rows_affected: 0 Bytes_sent: 21507 use trs_ids; SET timestamp=1666812303; select this_.`TABLENAME` as TABLENAME1_36_0_, this_.`FIELDNAME` as FIELDNAME2_36_0_, this_.`DISPLAYNAME` as DISPLAYN3_36_0_, this_.`DESCRIPTION` as DESCRIPT4_36_0_, this_.`MINLENGTH` as MINLENGTH5_36_0_, this_.`MAXLENGTH` as MAXLENGTH6_36_0_, this_.`STATUS` as STATUS7_36_0_, this_.`DATATYPE` as DATATYPE8_36_0_, this_.`NEEDSYNC` as NEEDSYNC9_36_0_, this_.`NEEDHEAVYQUERY` as NEEDHEA10_36_0_, this_.`LASTMODIFIEDUSER` as LASTMOD11_36_0_, this_.`LASTMODIFIEDTIME` as LASTMOD12_36_0_, this_.`HBMDEFINITION` as HBMDEFI13_36_0_, this_.`UNIQUE` as UNIQUE14_36_0_, this_.`NOTNULL` as NOTNULL15_36_0_, this_.`VALIDATORTYPE` as VALIDAT16_36_0_, this_.`FROMELEMENTTYPE` as FROMELE17_36_0_, this_.`FORMELEMENTDEFAULTVALUES` as FORMELE18_36_0_, this_.`FORMELEMENTOPTIONVALUES` as FORMELE19_36_0_, this_.`NEEDIMPORT` as NEEDIMPORT20_36_0_, this_.`BASICATTRIBUTE` as BASICAT21_36_0_, this_.`NEEDAUDIT` as NEEDAUDIT22_36_0_, this_.`NEEDEXPORT` as NEEDEXPORT23_36_0_, this_.`NEEDSEARCH` as NEEDSEARCH24_36_0_, this_.`DEFAULTREADPERMIT` as DEFAULT25_36_0_, this_.`DISPLAYORDER` as DISPLAY26_36_0_, this_.`SEARCHFORMELEMENTTYPE` as SEARCHF27_36_0_, this_.`DISPLAYINREGPAGE` as DISPLAY28_36_0_, this_.`DISPLAYINSELFPAGE` as DISPLAY29_36_0_, this_.`DISPLAYINADMINREADPAGE` as DISPLAY30_36_0_, this_.`DISPLAYINADMINADDPAGE` as DISPLAY31_36_0_, this_.`DISPLAYINADMINEDITPAGE` as DISPLAY32_36_0_, this_.`LENGTH` as LENGTH33_36_0_, this_.`DISPLAYINCOAPPAPPLY` as DISPLAY34_36_0_, this_.`DEVELOPERNECESSARY` as DEVELOP35_36_0_, this_.`SYSTEMWRITE` as SYSTEMW36_36_0_, this_.`SENSITIVE` as SENSITIVE37_36_0_, this_.`SENDTYPE` as SENDTYPE38_36_0_, this_.`REGEXEXPRESSION` as REGEXEX39_36_0_, this_.`VALUEGENERATORCLASS` as VALUEGE40_36_0_, this_.`CHECKFILTERWORD` as CHECKFI41_36_0_, this_.`INTEGRITYWEIGHT` as INTEGRI42_36_0_, this_.`SUFFIX` as SUFFIX43_36_0_, this_.`NEEDACTIVATE` as NEEDACT44_36_0_, this_.`BOCONSTRUCTORDEFINITIONID` as BOCONST45_36_0_, this_.`NEEDBATCHSEARCH` as NEEDBAT46_36_0_ from `IDSCUSTOMFIELD` this_ where this_.`TABLENAME`='User' order by this_.`DISPLAYORDER` asc limit 10000; # User@Host: igi[igi] @ [172.27.7.22] # Thread_id: 2773312 Schema: trs_hycloud_igi QC_hit: No # Query_time: 2.698749 Lock_time: 0.050396 Rows_sent: 5 Rows_examined: 63700 # Rows_affected: 0 Bytes_sent: 17045 use trs_hycloud_igi; SET timestamp=1666812303; select govmsgbox0_.id as id1_31_, govmsgbox0_.create_date as create_d2_31_, govmsgbox0_.modify_date as modify_d3_31_, govmsgbox0_.accept_time as accept_t4_31_, govmsgbox0_.address as address5_31_, govmsgbox0_.agent_user as agent_us6_31_, govmsgbox0_.app_id as app_id7_31_, govmsgbox0_.arepublic as arepubli8_31_, govmsgbox0_.area as area9_31_, govmsgbox0_.attachs as attachs10_31_, govmsgbox0_.cardid as cardid11_31_, govmsgbox0_.cardtype as cardtyp12_31_, govmsgbox0_.career as career13_31_, govmsgbox0_.city as city14_31_, govmsgbox0_.content as content15_31_, govmsgbox0_.count_remain_day_start_time as count_r16_31_, govmsgbox0_.crip as crip17_31_, govmsgbox0_.cruser as cruser18_31_, govmsgbox0_.dealdeptid as dealdep19_31_, govmsgbox0_.dealdeptname as dealdep20_31_, govmsgbox0_.dealuserid as dealuse21_31_, govmsgbox0_.delay_apply_time as delay_a22_31_, govmsgbox0_.delay_flag as delay_f23_31_, govmsgbox0_.delete_reason as delete_24_31_, govmsgbox0_.district_code as distric25_31_, govmsgbox0_.doc_desc as doc_des26_31_, govmsgbox0_.doc_id as doc_id27_31_, govmsgbox0_.doc_username as doc_use28_31_, govmsgbox0_.email as email29_31_, govmsgbox0_.examine_dept_id as examine30_31_, govmsgbox0_.examine_user_id as examine31_31_, govmsgbox0_.external_id as externa32_31_, govmsgbox0_.finishtime as finisht33_31_, govmsgbox0_.forward_dept_id as forward34_31_, govmsgbox0_.forward_user_name as forward35_31_, govmsgbox0_.govmsgbox_desc as govmsgb36_31_, govmsgbox0_.govmsgboxflag as govmsgb37_31_, govmsgbox0_.govmsgboxtype as govmsgb38_31_, govmsgbox0_.govmsgboxtype1 as govmsgb39_31_, govmsgbox0_.handle_time as handle_40_31_, govmsgbox0_.htmlcontent as htmlcon41_31_, govmsgbox0_.initial_app_id as initial42_31_, govmsgbox0_.initial_is_public as initial43_31_, govmsgbox0_.initial_site_id as initial44_31_, govmsgbox0_.is_agent as is_agen45_31_, govmsgbox0_.is_anonymous as is_anon46_31_, govmsgbox0_.is_anonymous_letter as is_anon47_31_, govmsgbox0_.isapply as isapply48_31_, govmsgbox0_.is_auto_reply as is_auto49_31_, govmsgbox0_.is_back as is_back50_31_, govmsgbox0_.is_blacklisted as is_blac51_31_, govmsgbox0_.is_deadline as is_dead52_31_, govmsgbox0_.is_deleted as is_dele53_31_, govmsgbox0_.is_forward as is_forw54_31_, govmsgbox0_.is_magor_msg as is_mago55_31_, govmsgbox0_.ispublic as ispubli56_31_, govmsgbox0_.is_reassign as is_reas57_31_, govmsgbox0_.is_rejected as is_reje58_31_, govmsgbox0_.isreply as isreply59_31_, govmsgbox0_.is_supervise_flag as is_supe60_31_, govmsgbox0_.is_union_dept_all_reply as is_unio61_31_, govmsgbox0_.is_wait_do_turn_multi_apply as is_wait62_31_, govmsgbox0_.last_cooperate_targe_typ as last_co63_31_, govmsgbox0_.last_reply_time as last_re64_31_, govmsgbox0_.location as locatio65_31_, govmsgbox0_.native_place as native_66_31_, govmsgbox0_.nick_name as nick_na67_31_, govmsgbox0_.open_scope as open_sc68_31_, govmsgbox0_.operip as operip69_31_, govmsgbox0_.operuser as operuse70_31_, govmsgbox0_.parent_id as parent_71_31_, govmsgbox0_.phone as phone72_31_, govmsgbox0_.province as provinc73_31_, govmsgbox0_.publictime as publict74_31_, govmsgbox0_.publish_error_reason as publish75_31_, govmsgbox0_.publish_url as publish76_31_, govmsgbox0_.query_number as query_n77_31_, govmsgbox0_.query_pwd as query_p78_31_, govmsgbox0_.region as region79_31_, govmsgbox0_.rejected_reason as rejecte80_31_, govmsgbox0_.remind as remind81_31_, govmsgbox0_.score as score82_31_, govmsgbox0_.setting_selected as setting83_31_, govmsgbox0_.sex as sex84_31_, govmsgbox0_.signvalue as signval85_31_, govmsgbox0_.siteid as siteid86_31_, govmsgbox0_.smart_record_id as smart_r87_31_, govmsgbox0_.smart_turn_data_id as smart_t88_31_, govmsgbox0_.smart_turn_flag as smart_t89_31_, govmsgbox0_.smart_turn_result as smart_t90_31_, govmsgbox0_.status as status91_31_, govmsgbox0_.street as street92_31_, govmsgbox0_.submit_time as submit_93_31_, govmsgbox0_.thumb_status as thumb_s94_31_, govmsgbox0_.thumbnails as thumbna95_31_, govmsgbox0_.tidy_status as tidy_st96_31_, govmsgbox0_.time_left as time_le97_31_, govmsgbox0_.title as title98_31_, govmsgbox0_.toassign_time as toassig99_31_, govmsgbox0_.toexamine_time as toexam100_31_, govmsgbox0_.toreply_time as torepl101_31_, govmsgbox0_.total_days as total_102_31_, govmsgbox0_.trash_time as trash_103_31_, govmsgbox0_.username as userna104_31_ from trs_govmsgbox govmsgbox0_ where (govmsgbox0_.siteid in (48)) and (govmsgbox0_.app_id in (9)) and govmsgbox0_.ispublic=1 and govmsgbox0_.arepublic=1 and govmsgbox0_.parent_id=0 and govmsgbox0_.status=7 order by govmsgbox0_.submit_time desc limit 5; # Time: 221026 20:06:33 # User@Host: ipm[ipm] @ [172.27.6.71] # Thread_id: 1173216 Schema: trs_hycloud_ipm QC_hit: No # Query_time: 2.563619 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 11 use trs_hycloud_ipm; SET timestamp=1666814793; commit; # Time: 221027 0:09:17 # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2775622 Schema: trs_hycloud_msg QC_hit: No # Query_time: 4.824891 Lock_time: 0.000443 Rows_sent: 1 Rows_examined: 23368 # Rows_affected: 0 Bytes_sent: 59 use trs_hycloud_msg; SET timestamp=1666829357; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '927' , '934' , '935' , '1183' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '528' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '569' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-05-11 19:18:48' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '528' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids; # Time: 221027 0:21:55 # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2775622 Schema: trs_hycloud_msg QC_hit: No # Query_time: 7.405037 Lock_time: 0.000635 Rows_sent: 1 Rows_examined: 5460 # Rows_affected: 0 Bytes_sent: 57 SET timestamp=1666830115; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '570' , '577' , '578' , '580' , '583' , '586' , '746' , '1174' , '1536' , '1537' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '170' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '305' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-04-08 10:32:51' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '170' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids; # Time: 221027 0:24:08 # User@Host: msg[msg] @ [172.27.6.20] # Thread_id: 2775622 Schema: trs_hycloud_msg QC_hit: No # Query_time: 2.092562 Lock_time: 0.000559 Rows_sent: 1 Rows_examined: 150596 # Rows_affected: 0 Bytes_sent: 60 SET timestamp=1666830248; select count(*) from (select DISTINCT d.id from msg_detail d join msg_receiver r on r.msg_id = d.id WHERE ( ( (r.receiver_type = 203 and r.receiver_id in ( '889' , '894' , '899' , '902' , '905' , '1190' , '1191' , '1192' , '1193' , '1447' , '1703' , '2' ) ) or (r.receiver_type = 204 and r.receiver_id in ( '712' ) ) or (r.receiver_type = 201 and r.receiver_id in ( '13' , '851' ) ) ) ) and (d.notice_status = 'published' or d.notice_status is null) and d.pub_time >= '2022-05-15 11:20:18' and not exists ( SELECT rd.id from msg_reader rd WHERE rd.reader_id in ( '712' ) and rd.reader_type = 204 and d.id = rd.msg_id ) ) msg_ids;
总结
到此这篇关于Java实现格式化打印慢SQL日志的文章就介绍到这了,更多相关Java格式化打印慢SQL日志内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部!