Mysql慢查询日志文件转Excel

勤劳的胖子

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

1. @Data
2. public class SlowQuery {
3.     private double queryTime;
4.     private double lockTime;
5.     private String sqlQuery;
6.     private String tableName;
7.     private Date executionDate;
9. }

复制代码

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

复制代码

来源:https://www.cnblogs.com/shangjianan/p/18517599
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！