使用EXPLAIN结合profiling工具定位线上系统MySQL慢查询与执行计划EXPLAIN慢查询索引命中缺陷一、MySQL慢查询定位概述1.1 慢查询定位的定义慢查询定位是指通过工具和分析方法找出MySQL数据库中执行时间超过阈值的SQL语句并分析其执行计划找出性能瓶颈的过程。1.2 慢查询定位的价值性能优化快速定位性能瓶颈资源节约减少数据库资源浪费用户体验提升系统响应速度成本控制降低硬件扩容需求稳定性保障避免因慢查询导致的系统雪崩可维护性建立持续优化机制1.3 慢查询定位的特点多维度结合EXPLAIN、profiling、慢查询日志实时性支持线上实时诊断精准性定位到具体索引和执行步骤可量化提供具体的性能指标二、EXPLAIN执行计划深度解析2.1 EXPLAIN架构图flowchart TD subgraph SQL解析 A[SQL语句] -- B[语法解析] B -- C[语义分析] end subgraph 优化器 C -- D[查询重写] D -- E[索引选择] E -- F[连接顺序] F -- G[执行计划生成] end subgraph 执行器 G -- H[存储引擎接口] H -- I[索引扫描] H -- J[全表扫描] I -- K[回表查询] J -- K K -- L[结果返回] end E -.-|EXPLAIN输出| M[执行计划分析]2.2 EXPLAIN输出字段详解字段含义重点关注优化建议id查询标识子查询嵌套深度减少子查询嵌套select_type查询类型SIMPLE/PRIMARY/SUBQUERY避免复杂子查询table表名临时表/派生表尽量使用物化type访问类型ALL index range ref eq_ref const避免ALL全表扫描possible_keys可能使用的索引是否有可用索引添加缺失索引key实际使用的索引与possible_keys对比分析索引选择原因key_len索引使用长度是否完整使用索引优化索引设计ref比较的列/常量是否使用常量优化查询条件rows扫描行数与实际返回行数对比减少扫描范围Extra额外信息Using filesort/Using temporary避免文件排序和临时表2.3 访问类型性能对比flowchart LR A[const] --|1行| B[最优] C[eq_ref] --|1行/表| D[优秀] E[ref] --|多行| F[良好] G[range] --|范围| H[一般] I[index] --|全索引| J[较差] K[ALL] --|全表| L[最差]2.4 EXPLAIN实战分析-- 示例1全表扫描问题 EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status PAID; -- 输出分析 -- type: ALL (全表扫描最差) -- rows: 5000000 (扫描500万行) -- Extra: Using where (需要过滤) -- 问题缺少(user_id, status)复合索引 -- 示例2索引未命中 EXPLAIN SELECT * FROM users WHERE YEAR(created_at) 2024; -- 输出分析 -- type: ALL -- key: NULL (索引未使用) -- 问题函数包裹了索引列导致索引失效 -- 修复WHERE created_at 2024-01-01 AND created_at 2025-01-01 -- 示例3索引最左前缀失效 EXPLAIN SELECT * FROM orders WHERE status PAID AND user_id 12345; -- 假设索引为 idx_user_status(user_id, status) -- 输出分析 -- key: NULL (索引未使用) -- 问题查询条件顺序与索引顺序不匹配 -- 修复(user_id, status)复合索引需要user_id在前三、profiling工具深度使用3.1 profiling架构图flowchart TD subgraph 查询执行 A[Query] -- B[Parsing] B -- C[Preprocessing] C -- D[Optimization] D -- E[Copying to tmp table] E -- F[Creating index] F -- G[Sorting] G -- H[Executing] H -- I[Sending data] end subgraph 时间统计 I -- J[总耗时统计] B -- K[各阶段耗时] C -- K D -- K E -- K F -- K G -- K H -- K end K -- L[profiling结果] J -- L3.2 profiling开启与使用-- 开启profiling SET profiling 1; -- 执行待分析的SQL SELECT * FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.user_id 12345 AND o.created_at 2024-01-01; -- 查看profiling结果 SHOW PROFILES; -- 查看详细耗时 SHOW PROFILE FOR QUERY 1; -- 查看CPU使用情况 SHOW PROFILE CPU FOR QUERY 1; -- 查看阻塞情况 SHOW PROFILE STATUS FOR QUERY 1; -- 查看锁等待情况 SHOW PROFILE LOCKS FOR QUERY 1; -- 关闭profiling SET profiling 0;3.3 profiling输出字段详解状态含义耗时占比高时的优化方向Starting初始化连接建立慢检查网络Checking permissions权限检查权限表过大Opening tables打开表表定义缓存不足init初始化执行查询复杂度高optimizing优化器工作统计信息不准确executing执行中扫描行数过多Sending data发送数据网络传输慢或数据量大end结束正常query end查询结束正常closing tables关闭表表缓存不足freeing items释放资源正常cleaning up清理正常Creating tmp table创建临时表避免临时表Copying to tmp table写入临时表优化GROUP BY/ORDER BYSorting result排序结果避免filesortUsing filesort文件排序添加索引避免排序statistics统计信息更新表统计信息3.4 profiling实战诊断-- 诊断慢查询创建临时表 SHOW PROFILE FOR QUERY 1; -- 输出: -- -------------------------------- -- | Status | Duration | -- -------------------------------- -- | Creating tmp table | 0.002341 | -- 耗时较长 -- | Copying to tmp table | 1.234567 | -- 主要瓶颈 -- | Sorting result | 0.567890 | -- 排序耗时 -- -------------------------------- -- 优化方案 -- 1. 将大表JOIN改为小表驱动 -- 2. 添加索引避免文件排序 -- 3. 减少SELECT *只取必要列 -- 诊断慢查询锁等待 SHOW PROFILE STATUS FOR QUERY 1; -- 输出: -- | Table lock | 2.345678 | -- 表锁等待 -- | Waiting for lock | 1.234567 | -- 行锁等待 -- 优化方案 -- 1. 检查是否有长事务未提交 -- 2. 优化事务粒度减小锁范围 -- 3. 调整隔离级别四、慢查询日志分析4.1 慢查询日志配置# my.cnf配置 [mysqld] # 开启慢查询日志 slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log # 慢查询阈值秒 long_query_time 2 # 记录未使用索引的查询 log_queries_not_using_indexes 1 # 记录不执行全表扫描的查询 log_slow_slave_statements 1 # 最小记录行数 min_examined_row_limit 10004.2 慢查询日志分析工具# 使用mysqldumpslow分析 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # -s t: 按时间排序 # -t 10: 显示前10条 # 使用pt-query-digest深度分析 pt-query-digest /var/log/mysql/slow.log analysis.txt # 实时监控系统慢查询 mysql -e SHOW PROCESSLIST; | grep -v Sleep | grep -v Time: 04.3 慢查询日志格式解析# Time: 2024-06-01T10:30:45.123456Z # UserHost: app_user[app_user] app_server [192.168.1.100] # Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1500 Rows_examined: 5000000 # Rows_affected: 0 Bytes_sent: 123456 use database_name; SET timestamp1717234245; SELECT * FROM orders WHERE user_id 12345 AND created_at 2024-01-01;五、综合诊断流程5.1 诊断流程图flowchart TD A[发现慢查询] -- B[查看慢查询日志] B -- C{是否重复出现?} C --|否| D[单次异常监控观察] C --|是| E[EXPLAIN分析执行计划] E -- F{type是否为ALL?} F --|是| G[添加缺失索引] F --|否| H[profiling详细分析] H -- I{瓶颈在哪?} I --|Copying to tmp table| J[优化GROUP BY/ORDER BY] I --|Using filesort| K[添加排序索引] I --|锁等待| L[优化事务/隔离级别] I --|Sending data| M[减少返回数据量] G -- N[验证优化效果] J -- N K -- N L -- N M -- N N -- O{达标?} O --|否| P[进一步调优] O --|是| Q[上线监控] P -- E5.2 诊断脚本#!/usr/bin/env python3 MySQL慢查询自动诊断工具 import subprocess import re from dataclasses import dataclass from typing import List, Optional dataclass class SlowQuery: query: str query_time: float lock_time: float rows_sent: int rows_examined: int timestamp: str class MySQLSlowQueryAnalyzer: 慢查询自动分析器 def __init__(self, mysql_host: str, mysql_user: str, mysql_pass: str): self.mysql_host mysql_host self.mysql_user mysql_user self.mysql_pass mysql_pass def parse_slow_log(self, log_path: str) - List[SlowQuery]: 解析慢查询日志 queries [] current_query [] in_query False with open(log_path, r) as f: for line in f: if line.startswith(# Time:): if current_query: queries.append(self._parse_query_entry(current_query)) current_query [line] in_query True elif in_query: current_query.append(line) if line.startswith(# Row): in_query False if current_query: queries.append(self._parse_query_entry(current_query)) return queries def _parse_query_entry(self, lines: List[str]) - SlowQuery: 解析单条慢查询条目 query_time lock_time rows_sent rows_examined 0.0 for line in lines: if Query_time: in line: match re.search(rQuery_time:\s([\d.]), line) if match: query_time float(match.group(1)) if Lock_time: in line: match re.search(rLock_time:\s([\d.]), line) if match: lock_time float(match.group(1)) if Rows_sent: in line: match re.search(rRows_sent:\s(\d), line) if match: rows_sent int(match.group(1)) if Rows_examined: in line: match re.search(rRows_examined:\s(\d), line) if match: rows_examined int(match.group(1)) query \n.join( l.strip() for l in lines if not l.startswith(#) and l.strip() ) timestamp for line in lines: if line.startswith(# Time:): timestamp line.replace(# Time:, ).strip() break return SlowQuery(query, query_time, lock_time, rows_sent, rows_examined, timestamp) def diagnose(self, query: str) - dict: 诊断单条SQL # 执行EXPLAIN explain_result self._run_explain(query) diagnosis { query: query, explain: explain_result, issues: [], recommendations: [] } # 分析执行计划 for row in explain_result: if row[type] ALL: diagnosis[issues].append(全表扫描) diagnosis[recommendations].append( f为条件列添加索引: {row[key]} 未命中 ) if Using temporary in str(row.get(Extra, )): diagnosis[issues].append(使用临时表) diagnosis[recommendations].append( 优化GROUP BY或子查询避免创建临时表 ) if Using filesort in str(row.get(Extra, )): diagnosis[issues].append(文件排序) diagnosis[recommendations].append( 为ORDER BY列添加索引 ) if int(row.get(rows, 0)) 100000: diagnosis[issues].append(f扫描行数过多: {row[rows]}) diagnosis[recommendations].append( 检查索引覆盖情况考虑分区表 ) return diagnosis def _run_explain(self, query: str) - List[dict]: 执行EXPLAIN并解析结果 cmd fmysql -h{self.mysql_host} -u{self.mysql_user} -p{self.mysql_pass} -e \EXPLAIN {query}\ result subprocess.run(cmd, shellTrue, capture_outputTrue, textTrue) # 解析EXPLAIN输出简化版 lines result.stdout.strip().split(\n) if len(lines) 2: return [] headers lines[0].split(\t) rows [] for line in lines[1:]: values line.split(\t) row dict(zip(headers, values)) rows.append(row) return rows # 使用示例 if __name__ __main__: analyzer MySQLSlowQueryAnalyzer( mysql_host192.168.1.10, mysql_useradmin, mysql_passpassword ) # 解析慢查询日志 queries analyzer.parse_slow_log(/var/log/mysql/slow.log) # 找出最慢的5条 top_slow sorted(queries, keylambda q: q.query_time, reverseTrue)[:5] for q in top_slow: print(f\n{*60}) print(f查询时间: {q.query_time:.3f}s) print(f扫描行数: {q.rows_examined:,}) print(f返回行数: {q.rows_sent:,}) print(fSQL: {q.query[:100]}...) diagnosis analyzer.diagnose(q.query) if diagnosis[issues]: print(f发现问题: {, .join(diagnosis[issues])}) print(f建议: {, .join(diagnosis[recommendations])})六、优化实战案例6.1 案例一索引缺失导致全表扫描问题SQLSELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 10;EXPLAIN结果type: ALL key: NULL rows: 5000000 Extra: Using where; Using filesort优化方案-- 添加复合索引 CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC); -- 优化后EXPLAIN type: ref key: idx_user_status_created rows: 15 Extra: None6.2 案例二函数导致索引失效问题SQLSELECT * FROM users WHERE DATE(created_at) 2024-06-01;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where优化方案-- 改为范围查询 SELECT * FROM users WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00; -- 优化后EXPLAIN type: range key: idx_created_at rows: 5000 Extra: None七、总结通过EXPLAIN结合profiling工具我们可以精准定位MySQL慢查询的性能瓶颈。核心要点EXPLAIN是分析执行计划的基础工具重点关注type、key、rows、Extra字段profiling用于分析查询各阶段的耗时分布定位具体瓶颈慢查询日志是发现问题的入口配合分析工具形成完整诊断闭环索引优化是最常见的优化手段但要注意最左前缀原则和索引覆盖建立持续监控机制及时发现和解决慢查询问题慢查询优化是一个持续的过程需要结合工具分析、代码优化和架构调整才能达到最佳效果。
使用EXPLAIN结合profiling工具定位线上系统MySQL慢查询与执行计划EXPLAIN慢查询索引命中缺陷
发布时间：2026/6/2 1:44:46
使用EXPLAIN结合profiling工具定位线上系统MySQL慢查询与执行计划EXPLAIN慢查询索引命中缺陷一、MySQL慢查询定位概述1.1 慢查询定位的定义慢查询定位是指通过工具和分析方法找出MySQL数据库中执行时间超过阈值的SQL语句并分析其执行计划找出性能瓶颈的过程。1.2 慢查询定位的价值性能优化快速定位性能瓶颈资源节约减少数据库资源浪费用户体验提升系统响应速度成本控制降低硬件扩容需求稳定性保障避免因慢查询导致的系统雪崩可维护性建立持续优化机制1.3 慢查询定位的特点多维度结合EXPLAIN、profiling、慢查询日志实时性支持线上实时诊断精准性定位到具体索引和执行步骤可量化提供具体的性能指标二、EXPLAIN执行计划深度解析2.1 EXPLAIN架构图flowchart TD subgraph SQL解析 A[SQL语句] -- B[语法解析] B -- C[语义分析] end subgraph 优化器 C -- D[查询重写] D -- E[索引选择] E -- F[连接顺序] F -- G[执行计划生成] end subgraph 执行器 G -- H[存储引擎接口] H -- I[索引扫描] H -- J[全表扫描] I -- K[回表查询] J -- K K -- L[结果返回] end E -.-|EXPLAIN输出| M[执行计划分析]2.2 EXPLAIN输出字段详解字段含义重点关注优化建议id查询标识子查询嵌套深度减少子查询嵌套select_type查询类型SIMPLE/PRIMARY/SUBQUERY避免复杂子查询table表名临时表/派生表尽量使用物化type访问类型ALL index range ref eq_ref const避免ALL全表扫描possible_keys可能使用的索引是否有可用索引添加缺失索引key实际使用的索引与possible_keys对比分析索引选择原因key_len索引使用长度是否完整使用索引优化索引设计ref比较的列/常量是否使用常量优化查询条件rows扫描行数与实际返回行数对比减少扫描范围Extra额外信息Using filesort/Using temporary避免文件排序和临时表2.3 访问类型性能对比flowchart LR A[const] --|1行| B[最优] C[eq_ref] --|1行/表| D[优秀] E[ref] --|多行| F[良好] G[range] --|范围| H[一般] I[index] --|全索引| J[较差] K[ALL] --|全表| L[最差]2.4 EXPLAIN实战分析-- 示例1全表扫描问题 EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND status PAID; -- 输出分析 -- type: ALL (全表扫描最差) -- rows: 5000000 (扫描500万行) -- Extra: Using where (需要过滤) -- 问题缺少(user_id, status)复合索引 -- 示例2索引未命中 EXPLAIN SELECT * FROM users WHERE YEAR(created_at) 2024; -- 输出分析 -- type: ALL -- key: NULL (索引未使用) -- 问题函数包裹了索引列导致索引失效 -- 修复WHERE created_at 2024-01-01 AND created_at 2025-01-01 -- 示例3索引最左前缀失效 EXPLAIN SELECT * FROM orders WHERE status PAID AND user_id 12345; -- 假设索引为 idx_user_status(user_id, status) -- 输出分析 -- key: NULL (索引未使用) -- 问题查询条件顺序与索引顺序不匹配 -- 修复(user_id, status)复合索引需要user_id在前三、profiling工具深度使用3.1 profiling架构图flowchart TD subgraph 查询执行 A[Query] -- B[Parsing] B -- C[Preprocessing] C -- D[Optimization] D -- E[Copying to tmp table] E -- F[Creating index] F -- G[Sorting] G -- H[Executing] H -- I[Sending data] end subgraph 时间统计 I -- J[总耗时统计] B -- K[各阶段耗时] C -- K D -- K E -- K F -- K G -- K H -- K end K -- L[profiling结果] J -- L3.2 profiling开启与使用-- 开启profiling SET profiling 1; -- 执行待分析的SQL SELECT * FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN products p ON oi.product_id p.id WHERE o.user_id 12345 AND o.created_at 2024-01-01; -- 查看profiling结果 SHOW PROFILES; -- 查看详细耗时 SHOW PROFILE FOR QUERY 1; -- 查看CPU使用情况 SHOW PROFILE CPU FOR QUERY 1; -- 查看阻塞情况 SHOW PROFILE STATUS FOR QUERY 1; -- 查看锁等待情况 SHOW PROFILE LOCKS FOR QUERY 1; -- 关闭profiling SET profiling 0;3.3 profiling输出字段详解状态含义耗时占比高时的优化方向Starting初始化连接建立慢检查网络Checking permissions权限检查权限表过大Opening tables打开表表定义缓存不足init初始化执行查询复杂度高optimizing优化器工作统计信息不准确executing执行中扫描行数过多Sending data发送数据网络传输慢或数据量大end结束正常query end查询结束正常closing tables关闭表表缓存不足freeing items释放资源正常cleaning up清理正常Creating tmp table创建临时表避免临时表Copying to tmp table写入临时表优化GROUP BY/ORDER BYSorting result排序结果避免filesortUsing filesort文件排序添加索引避免排序statistics统计信息更新表统计信息3.4 profiling实战诊断-- 诊断慢查询创建临时表 SHOW PROFILE FOR QUERY 1; -- 输出: -- -------------------------------- -- | Status | Duration | -- -------------------------------- -- | Creating tmp table | 0.002341 | -- 耗时较长 -- | Copying to tmp table | 1.234567 | -- 主要瓶颈 -- | Sorting result | 0.567890 | -- 排序耗时 -- -------------------------------- -- 优化方案 -- 1. 将大表JOIN改为小表驱动 -- 2. 添加索引避免文件排序 -- 3. 减少SELECT *只取必要列 -- 诊断慢查询锁等待 SHOW PROFILE STATUS FOR QUERY 1; -- 输出: -- | Table lock | 2.345678 | -- 表锁等待 -- | Waiting for lock | 1.234567 | -- 行锁等待 -- 优化方案 -- 1. 检查是否有长事务未提交 -- 2. 优化事务粒度减小锁范围 -- 3. 调整隔离级别四、慢查询日志分析4.1 慢查询日志配置# my.cnf配置 [mysqld] # 开启慢查询日志 slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log # 慢查询阈值秒 long_query_time 2 # 记录未使用索引的查询 log_queries_not_using_indexes 1 # 记录不执行全表扫描的查询 log_slow_slave_statements 1 # 最小记录行数 min_examined_row_limit 10004.2 慢查询日志分析工具# 使用mysqldumpslow分析 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # -s t: 按时间排序 # -t 10: 显示前10条 # 使用pt-query-digest深度分析 pt-query-digest /var/log/mysql/slow.log analysis.txt # 实时监控系统慢查询 mysql -e SHOW PROCESSLIST; | grep -v Sleep | grep -v Time: 04.3 慢查询日志格式解析# Time: 2024-06-01T10:30:45.123456Z # UserHost: app_user[app_user] app_server [192.168.1.100] # Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1500 Rows_examined: 5000000 # Rows_affected: 0 Bytes_sent: 123456 use database_name; SET timestamp1717234245; SELECT * FROM orders WHERE user_id 12345 AND created_at 2024-01-01;五、综合诊断流程5.1 诊断流程图flowchart TD A[发现慢查询] -- B[查看慢查询日志] B -- C{是否重复出现?} C --|否| D[单次异常监控观察] C --|是| E[EXPLAIN分析执行计划] E -- F{type是否为ALL?} F --|是| G[添加缺失索引] F --|否| H[profiling详细分析] H -- I{瓶颈在哪?} I --|Copying to tmp table| J[优化GROUP BY/ORDER BY] I --|Using filesort| K[添加排序索引] I --|锁等待| L[优化事务/隔离级别] I --|Sending data| M[减少返回数据量] G -- N[验证优化效果] J -- N K -- N L -- N M -- N N -- O{达标?} O --|否| P[进一步调优] O --|是| Q[上线监控] P -- E5.2 诊断脚本#!/usr/bin/env python3 MySQL慢查询自动诊断工具 import subprocess import re from dataclasses import dataclass from typing import List, Optional dataclass class SlowQuery: query: str query_time: float lock_time: float rows_sent: int rows_examined: int timestamp: str class MySQLSlowQueryAnalyzer: 慢查询自动分析器 def __init__(self, mysql_host: str, mysql_user: str, mysql_pass: str): self.mysql_host mysql_host self.mysql_user mysql_user self.mysql_pass mysql_pass def parse_slow_log(self, log_path: str) - List[SlowQuery]: 解析慢查询日志 queries [] current_query [] in_query False with open(log_path, r) as f: for line in f: if line.startswith(# Time:): if current_query: queries.append(self._parse_query_entry(current_query)) current_query [line] in_query True elif in_query: current_query.append(line) if line.startswith(# Row): in_query False if current_query: queries.append(self._parse_query_entry(current_query)) return queries def _parse_query_entry(self, lines: List[str]) - SlowQuery: 解析单条慢查询条目 query_time lock_time rows_sent rows_examined 0.0 for line in lines: if Query_time: in line: match re.search(rQuery_time:\s([\d.]), line) if match: query_time float(match.group(1)) if Lock_time: in line: match re.search(rLock_time:\s([\d.]), line) if match: lock_time float(match.group(1)) if Rows_sent: in line: match re.search(rRows_sent:\s(\d), line) if match: rows_sent int(match.group(1)) if Rows_examined: in line: match re.search(rRows_examined:\s(\d), line) if match: rows_examined int(match.group(1)) query \n.join( l.strip() for l in lines if not l.startswith(#) and l.strip() ) timestamp for line in lines: if line.startswith(# Time:): timestamp line.replace(# Time:, ).strip() break return SlowQuery(query, query_time, lock_time, rows_sent, rows_examined, timestamp) def diagnose(self, query: str) - dict: 诊断单条SQL # 执行EXPLAIN explain_result self._run_explain(query) diagnosis { query: query, explain: explain_result, issues: [], recommendations: [] } # 分析执行计划 for row in explain_result: if row[type] ALL: diagnosis[issues].append(全表扫描) diagnosis[recommendations].append( f为条件列添加索引: {row[key]} 未命中 ) if Using temporary in str(row.get(Extra, )): diagnosis[issues].append(使用临时表) diagnosis[recommendations].append( 优化GROUP BY或子查询避免创建临时表 ) if Using filesort in str(row.get(Extra, )): diagnosis[issues].append(文件排序) diagnosis[recommendations].append( 为ORDER BY列添加索引 ) if int(row.get(rows, 0)) 100000: diagnosis[issues].append(f扫描行数过多: {row[rows]}) diagnosis[recommendations].append( 检查索引覆盖情况考虑分区表 ) return diagnosis def _run_explain(self, query: str) - List[dict]: 执行EXPLAIN并解析结果 cmd fmysql -h{self.mysql_host} -u{self.mysql_user} -p{self.mysql_pass} -e \EXPLAIN {query}\ result subprocess.run(cmd, shellTrue, capture_outputTrue, textTrue) # 解析EXPLAIN输出简化版 lines result.stdout.strip().split(\n) if len(lines) 2: return [] headers lines[0].split(\t) rows [] for line in lines[1:]: values line.split(\t) row dict(zip(headers, values)) rows.append(row) return rows # 使用示例 if __name__ __main__: analyzer MySQLSlowQueryAnalyzer( mysql_host192.168.1.10, mysql_useradmin, mysql_passpassword ) # 解析慢查询日志 queries analyzer.parse_slow_log(/var/log/mysql/slow.log) # 找出最慢的5条 top_slow sorted(queries, keylambda q: q.query_time, reverseTrue)[:5] for q in top_slow: print(f\n{*60}) print(f查询时间: {q.query_time:.3f}s) print(f扫描行数: {q.rows_examined:,}) print(f返回行数: {q.rows_sent:,}) print(fSQL: {q.query[:100]}...) diagnosis analyzer.diagnose(q.query) if diagnosis[issues]: print(f发现问题: {, .join(diagnosis[issues])}) print(f建议: {, .join(diagnosis[recommendations])})六、优化实战案例6.1 案例一索引缺失导致全表扫描问题SQLSELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY created_at DESC LIMIT 10;EXPLAIN结果type: ALL key: NULL rows: 5000000 Extra: Using where; Using filesort优化方案-- 添加复合索引 CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC); -- 优化后EXPLAIN type: ref key: idx_user_status_created rows: 15 Extra: None6.2 案例二函数导致索引失效问题SQLSELECT * FROM users WHERE DATE(created_at) 2024-06-01;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where优化方案-- 改为范围查询 SELECT * FROM users WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00; -- 优化后EXPLAIN type: range key: idx_created_at rows: 5000 Extra: None七、总结通过EXPLAIN结合profiling工具我们可以精准定位MySQL慢查询的性能瓶颈。核心要点EXPLAIN是分析执行计划的基础工具重点关注type、key、rows、Extra字段profiling用于分析查询各阶段的耗时分布定位具体瓶颈慢查询日志是发现问题的入口配合分析工具形成完整诊断闭环索引优化是最常见的优化手段但要注意最左前缀原则和索引覆盖建立持续监控机制及时发现和解决慢查询问题慢查询优化是一个持续的过程需要结合工具分析、代码优化和架构调整才能达到最佳效果。
