Recently, the company's production environment needs to check for slow SQL. After exporting the log txt file, the problem was checked for chaos. After searching for relevant information, I did not find a convenient and fast formatting tool, so I wrote a set of Java to read slow SQL logs and converted it into an Excel widget.
@Data public class SlowQuery { private double queryTime; private double lockTime; private String sqlQuery; private String tableName; private Date executionDate; }
public class MySQLSlowQueryLogParser { // Regular expression matching Slow log content format crawling private static final Pattern QUERY_TIME_PATTERN = ("# Query_time: (\\d+\\.\\d+)"); private static final Pattern LOCK_TIME_PATTERN = (" Lock_time: (\\d+\\.\\d+)"); private static final Pattern TIMESTAMP_PATTERN = ("SET timestamp=(\\d+);"); public static void main(String[] args) { MySQLSlowQueryLogParser parser = new MySQLSlowQueryLogParser(); // Slow query log storage path String filePath = "D:\\Daily\\\\\"; // Export Excel path String excelPath = "D:\\Daily\\\slow_queries.xlsx"; // Read slow query log List<SlowQuery> slowQueries = (filePath); // Write to local Excel (slowQueries, excelPath); } /** * Read the slow query log and return the List object * @param filePath Slow query log file path * @return List<SlowQuery> parsing results * */ public List<SlowQuery> readSlowQueryLog(String filePath) { List<SlowQuery> slowQueries = new ArrayList<>(); // Transfer try (BufferedReader br = new BufferedReader(new FileReader(filePath))) { String line; StringBuilder queryBuilder = new StringBuilder(); // Set the default value double queryTime = 0; double lockTime = 0; boolean isSlowQuery = false; long timestamp = 0; // Used to store timestamps while ((line = ()) != null) { if (("# Query_time")) { // If the previous query exists, add to the list if (isSlowQuery) { addSlowQuery(slowQueries, queryTime, lockTime, ().trim(), timestamp); } // parse query time and lock time Matcher queryTimeMatcher = QUERY_TIME_PATTERN.matcher(line); if (()) { queryTime = ((1)); } Matcher lockTimeMatcher = LOCK_TIME_PATTERN.matcher(line); if (()) { lockTime = ((1)); } // Start a new slow query isSlowQuery = true; // Clear the cache (0); } else if (("SET timestamp")) { // Extract the timestamp Matcher timestampMatcher = TIMESTAMP_PATTERN.matcher(line); if (()) { timestamp = ((1)); // Get the timestamp } } else if (("#") || ().isEmpty()) { // Ignore comment lines and empty lines continue; } else { // Record the content of the current slow query if (isSlowQuery) { (line).append("\n"); } } } // Process the last slow query if (() > 0) { addSlowQuery(slowQueries, queryTime, lockTime, ().trim(), timestamp); } } catch (IOException e) { (()); } return slowQueries; } /** * Add a slow query object * @param slowQueries List<SlowQuery> SlowQuery object collection * @param queryTime query time * @param lockTime Lock Time * @param sqlQuery Sql execution time * @param timestamp timestamp * */ private void addSlowQuery(List<SlowQuery> slowQueries, double queryTime, double lockTime, String sqlQuery, long timestamp) { SlowQuery slowQuery = new SlowQuery(); (queryTime); (lockTime); (sqlQuery); // Extract table name (extractTableName(sqlQuery)); // Set execution date (new Date(timestamp * 1000)); (slowQuery); } /** * Extract the table name from the Sql statement * @param sqlQuery Sql statement executed * @return Table name * */ private String extractTableName(String sqlQuery) { Pattern pattern = ("FROM\\s+([\\w.]+)", Pattern.CASE_INSENSITIVE); Matcher matcher = (sqlQuery); if (()) { return (1); } return ""; } /** * Generate to the specified path through the processed collection * @param slowQueries Data collection * @param filePath Exported Excel path * */ public void writeQueriesToExcel(List<SlowQuery> slowQueries, String filePath) { final int MAX_CELL_LENGTH = 32767; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // Date formatting try (Workbook workbook = new XSSFWorkbook()) { Sheet sheet = ("Slow Queries"); // Create a title line Row headerRow = (0); (0).setCellValue("Query Time (s)"); (1).setCellValue("Lock Time (s)"); (2).setCellValue("SQL Query"); (3).setCellValue("Table Name"); (4).setCellValue("Execution Date"); // Fill in the data rows int rowNum = 1; for (SlowQuery slowQuery : slowQueries) { Row row = (rowNum++); (0).setCellValue(()); (1).setCellValue(()); // Make sure that the original double value is written here String sqlQuery = (); if (() > MAX_CELL_LENGTH) { sqlQuery = (0, MAX_CELL_LENGTH); } (2).setCellValue(sqlQuery); (3).setCellValue(()); (4).setCellValue((())); } // Write to file try (FileOutputStream fileOut = new FileOutputStream(filePath)) { (fileOut); } } catch (IOException e) { (()); } }
This is the article about the method of converting Mysql slow query log file to Excel. For more related contents of Mysql slow query log file, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!