Mysql慢查询日志文件转Excel
最近公司生产环境需要排查慢SQL，导出日志txt文件后排查混乱，查找相关资料后并没有找到方便快捷的格式化处理工具,于是自己编写了一套Java读取慢SQL日志转为Excel小工具。
1 2 3 4 5 6 7 8 | @Datapublic class SlowQuery { private double queryTime; private double lockTime; private String sqlQuery; private String tableName; private Date executionDate;} |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | public class MySQLSlowQueryLogParser { // 正则表达式匹配 慢日志内容格式抓取 private static final Pattern QUERY_TIME_PATTERN = Pattern.compile("# Query_time: (\\d+\\.\\d+)"); private static final Pattern LOCK_TIME_PATTERN = Pattern.compile(" Lock_time: (\\d+\\.\\d+)"); private static final Pattern TIMESTAMP_PATTERN = Pattern.compile("SET timestamp=(\\d+);"); public static void main(String[] args) { MySQLSlowQueryLogParser parser = new MySQLSlowQueryLogParser(); // 慢查询日志存放路径 String filePath = "D:\\日常\\2.OA\\OASERVERLANDB-slow.log"; // 导出Excel路径 String excelPath = "D:\\日常\\2.OA\\slow_queries.xlsx"; // 读取慢查询日志 List<SlowQuery> slowQueries = parser.readSlowQueryLog(filePath); // 写入本地Excel中 parser.writeQueriesToExcel(slowQueries, excelPath); } /** * 读取慢查询日志 返回List对象 * @param filePath 慢查询日志文件路径 * @return List<SlowQuery> 解析结果 * */ public List<SlowQuery> readSlowQueryLog(String filePath) { List<SlowQuery> slowQueries = new ArrayList<>(); // 转流 try (BufferedReader br = new BufferedReader(new FileReader(filePath))) { String line; StringBuilder queryBuilder = new StringBuilder(); // 设定默认值 double queryTime = 0; double lockTime = 0; boolean isSlowQuery = false; long timestamp = 0; // 用于存储时间戳 while ((line = br.readLine()) != null) { if (line.startsWith("# Query_time")) { // 如果前一个查询存在，添加到列表 if (isSlowQuery) { addSlowQuery(slowQueries, queryTime, lockTime, queryBuilder.toString().trim(), timestamp); } // 解析查询时间和锁定时间 Matcher queryTimeMatcher = QUERY_TIME_PATTERN.matcher(line); if (queryTimeMatcher.find()) { queryTime = Double.parseDouble(queryTimeMatcher.group(1)); } Matcher lockTimeMatcher = LOCK_TIME_PATTERN.matcher(line); if (lockTimeMatcher.find()) { lockTime = Double.parseDouble(lockTimeMatcher.group(1)); } // 开始新的慢查询 isSlowQuery = true; // 清空缓存 queryBuilder.setLength(0); } else if (line.startsWith("SET timestamp")) { // 提取时间戳 Matcher timestampMatcher = TIMESTAMP_PATTERN.matcher(line); if (timestampMatcher.find()) { timestamp = Long.parseLong(timestampMatcher.group(1)); // 获取时间戳 } } else if (line.startsWith("#") || line.trim().isEmpty()) { // 忽略注释行和空行 continue; } else { // 记录当前慢查询的内容 if (isSlowQuery) { queryBuilder.append(line).append("\n"); } } } // 处理最后一个慢查询 if (queryBuilder.length() > 0) { addSlowQuery(slowQueries, queryTime, lockTime, queryBuilder.toString().trim(), timestamp); } } catch (IOException e) { System.out.printf(e.toString()); } return slowQueries; } /** * 添加慢查询对象 * @param slowQueries List<SlowQuery> 慢查询对象集合 * @param queryTime 查询时间 * @param lockTime 锁定时间 * @param sqlQuery Sql执行时间 * @param timestamp 时间戳 * */ private void addSlowQuery(List<SlowQuery> slowQueries, double queryTime, double lockTime, String sqlQuery, long timestamp) { SlowQuery slowQuery = new SlowQuery(); slowQuery.setQueryTime(queryTime); slowQuery.setLockTime(lockTime); slowQuery.setSqlQuery(sqlQuery); // 提取表名 slowQuery.setTableName(extractTableName(sqlQuery)); // 设置执行日期 slowQuery.setExecutionDate(new Date(timestamp * 1000)); slowQueries.add(slowQuery); } /** * 通过Sql语句中 提取出表名 * @param sqlQuery 执行的Sql语句 * @return 表名 * */ private String extractTableName(String sqlQuery) { Pattern pattern = Pattern.compile("FROM\\s+([\\w.]+)", Pattern.CASE_INSENSITIVE); Matcher matcher = pattern.matcher(sqlQuery); if (matcher.find()) { return matcher.group(1); } return ""; } /** * 通过处理后的集合生成到指定路径 * @param slowQueries 数据集合 * @param filePath 导出的Excel路径 * */ public void writeQueriesToExcel(List<SlowQuery> slowQueries, String filePath) { final int MAX_CELL_LENGTH = 32767; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 日期格式化 try (Workbook workbook = new XSSFWorkbook()) { Sheet sheet = workbook.createSheet("Slow Queries"); // 创建标题行 Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Query Time (s)"); headerRow.createCell(1).setCellValue("Lock Time (s)"); headerRow.createCell(2).setCellValue("SQL Query"); headerRow.createCell(3).setCellValue("Table Name"); headerRow.createCell(4).setCellValue("Execution Date"); // 填充数据行 int rowNum = 1; for (SlowQuery slowQuery : slowQueries) { Row row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(slowQuery.getQueryTime()); row.createCell(1).setCellValue(slowQuery.getLockTime()); // 确保这里写入的是原始 double 值 String sqlQuery = slowQuery.getSqlQuery(); if (sqlQuery.length() > MAX_CELL_LENGTH) { sqlQuery = sqlQuery.substring(0, MAX_CELL_LENGTH); } row.createCell(2).setCellValue(sqlQuery); row.createCell(3).setCellValue(slowQuery.getTableName()); row.createCell(4).setCellValue(dateFormat.format(slowQuery.getExecutionDate())); } // 写入到文件 try (FileOutputStream fileOut = new FileOutputStream(filePath)) { workbook.write(fileOut); } } catch (IOException e) { System.out.printf(e.toString()); } } |
