使用EXPLAIN结合profiling工具定位线上系统MySQL慢查询与执行计划EXPLAIN慢查询索引命中缺陷

使用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输出字段详解

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 --> L

3.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输出字段详解

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 = 1000

4.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: 0"

4.3 慢查询日志格式解析

# Time: 2024-06-01T10:30:45.123456Z # User@Host: 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 timestamp=1717234245; 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 --> E

5.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(r'Query_time:\s+([\d.]+)', line) if match: query_time = float(match.group(1)) if 'Lock_time:' in line: match = re.search(r'Lock_time:\s+([\d.]+)', line) if match: lock_time = float(match.group(1)) if 'Rows_sent:' in line: match = re.search(r'Rows_sent:\s+(\d+)', line) if match: rows_sent = int(match.group(1)) if 'Rows_examined:' in line: match = re.search(r'Rows_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 = f"mysql -h{self.mysql_host} -u{self.mysql_user} -p{self.mysql_pass} -e \"EXPLAIN {query}\"" result = subprocess.run(cmd, shell=True, capture_output=True, text=True) # 解析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_host='192.168.1.10', mysql_user='admin', mysql_pass='password' ) # 解析慢查询日志 queries = analyzer.parse_slow_log('/var/log/mysql/slow.log') # 找出最慢的5条 top_slow = sorted(queries, key=lambda q: q.query_time, reverse=True)[: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(f"SQL: {q.query[:100]}...") diagnosis = analyzer.diagnose(q.query) if diagnosis['issues']: print(f"发现问题: {', '.join(diagnosis['issues'])}") print(f"建议: {', '.join(diagnosis['recommendations'])}")

六、优化实战案例

6.1 案例一：索引缺失导致全表扫描

问题SQL：

SELECT * 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: None

6.2 案例二：函数导致索引失效

问题SQL：

SELECT * 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慢查询的性能瓶颈。

核心要点：

1. EXPLAIN是分析执行计划的基础工具，重点关注type、key、rows、Extra字段
2. profiling用于分析查询各阶段的耗时分布，定位具体瓶颈
3. 慢查询日志是发现问题的入口，配合分析工具形成完整诊断闭环
4. 索引优化是最常见的优化手段，但要注意最左前缀原则和索引覆盖
5. 建立持续监控机制，及时发现和解决慢查询问题

慢查询优化是一个持续的过程，需要结合工具分析、代码优化和架构调整，才能达到最佳效果。