在实际的数据库管理和性能优化工作中,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') # 用分隔线隔开不同查询
代码说明
- 读取文件内容: 使用
open
函数读取慢查询日志文件的内容。 - 拆分查询: 利用正则表达式将日志文件按照每个查询的起始位置拆分成列表。
- 提取 SELECT 查询: 通过检查每个查询是否包含关键词
'select'
,过滤出慢查询中的 SELECT 语句。 - 去除不必要信息: 使用正则表达式去除查询中的用户信息和
SET
语句。 - 排序查询: 根据查询的执行时间进行排序,降序排列。
- 写入文件: 将整理后的查询写入输出文件,每个查询之间用分隔线隔开。