Mysql慢查询日志文件转Excel

最近公司生产环境需要排查慢SQL，导出日志txt文件后排查混乱，查找相关资料后并没有找到方便快捷的格式化处理工具,于是自己编写了一套Java读取慢SQL日志转为Excel小工具。

@Data
public class SlowQuery {
 private double queryTime;
 private double lockTime;
 private String sqlQuery;
 private String tableName;
 private Date executionDate;
}

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());
 }
 }