最近公司生產環境需要排查慢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());
}
}