SoFunction
Updated on 2025-04-08

How to convert Mysql slow query log file to Excel

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&lt;SlowQuery&gt; readSlowQueryLog(String filePath) {
        List&lt;SlowQuery&gt; slowQueries = new ArrayList&lt;&gt;();
        // 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 (() &gt; 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&lt;SlowQuery&gt; 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&lt;SlowQuery&gt; 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 (() &gt; 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!