Python - 整理 MySQL 慢查询日志

在实际的数据库管理和性能优化工作中，MySQL 慢查询日志（slow query log）是一个重要的工具。当系统中的 SQL 查询花费的时间超过阈值时，MySQL 会将这些查询记录在慢查询日志中，方便进行性能分析和调优。

本文将介绍如何使用 Python 和正则表达式来整理 MySQL 慢查询日志，提取并排序 SELECT 查询，并生成一个更易读的输出文件。

背景

慢查询日志是 MySQL 中的一个功能，可以记录执行时间超过指定阈值的 SQL 查询。日志中包含了每次慢查询的执行时间、查询语句、用户信息等。但有时候，日志文件可能包含大量无关紧要的信息，如用户信息、时间戳等，而我们关心的可能只是查询语句和执行时间。

代码解析

以下是整理 MySQL 慢查询日志的 Python 代码：

import re

# 本地文档文件路径
file_path = "1-16-1-22.txt"

# 读取本地文档
with open(file_path, 'r', encoding='utf-8') as file:
    query_document = file.read()

# 将文档按每个查询的起始位置拆分
queries = re.split(r'# Time: [\d-]+T[\d:.]+Z', query_document)[1:]

# 提取并过滤SELECT查询
select_queries = []
seen_queries = set()  # 用于追踪已经出现过的查询
for query in queries:
    if 'select' in query.lower():
        # 使用正则表达式提取 Query_time
        query_time_match = re.search(r'# Query_time: (\d+.\d+)', query)
        query_time = float(query_time_match.group(1)) if query_time_match else 0.0

        # 将 # Query_time: ... 与后续 SQL 查询语句分隔开
        query_lines = re.split(r'# Query_time: \d+.\d+.*?\n', query, flags=re.DOTALL)

        # 过滤掉空行
        query_lines = [line.strip() for line in query_lines if line.strip()]

        # 如果有多行 SQL 查询语句，加上换行
        formatted_query = "\n".join(query_lines)

        # 去除包含 # User@Host: ... 和 SET ... 的行
        formatted_query = re.sub(r'# User@Host:.*?\n', '', formatted_query)
        formatted_query = re.sub(r'SET.*?\n', '', formatted_query)

        # 检查是否已经出现过这个查询，如果没有则添加到输出
        if formatted_query not in seen_queries:
            seen_queries.add(formatted_query)
            select_queries.append((formatted_query, query_time))

# 根据 Query_time 对 SELECT 查询进行排序
sorted_select_queries = sorted(select_queries, key=lambda x: x[1], reverse=True)

# 将排序后的 SELECT 查询写入文件
with open('output.txt', 'w', encoding='utf-8') as output_file:
    for query, query_time in sorted_select_queries:
        output_file.write(f"Query_time: {query_time}\n")
        output_file.write(query + '\n')
        output_file.write("#" * 50 + '\n')  # 用分隔线隔开不同查询

代码说明

1. 读取文件内容： 使用 open 函数读取慢查询日志文件的内容。
2. 拆分查询： 利用正则表达式将日志文件按照每个查询的起始位置拆分成列表。
3. 提取 SELECT 查询： 通过检查每个查询是否包含关键词 'select'，过滤出慢查询中的 SELECT 语句。
4. 去除不必要信息： 使用正则表达式去除查询中的用户信息和 SET 语句。
5. 排序查询： 根据查询的执行时间进行排序，降序排列。
6. 写入文件： 将整理后的查询写入输出文件，每个查询之间用分隔线隔开。