show profile调优

MySQL show profile 调优：深入分析 SQL 执行性能

当 EXPLAIN 执行计划显示 SQL 已命中索引但仍执行缓慢时，show profile 可进一步分析 SQL 执行过程中的资源消耗（如 CPU、IO、内存等），定位具体性能瓶颈。本文详细介绍其配置、使用方法及优化实践。

show profile 简介

show profile 是 MySQL 提供的会话级性能分析工具，用于记录当前会话中 SQL 语句的执行阶段及各阶段的资源消耗，包括：

• 时间消耗（Duration）
• CPU 消耗（CPU_user、CPU_system）
• IO 操作（Block_ops_in、Block_ops_out）
• 内存、上下文切换、页面错误等

通过分析这些细节，可精准定位 SQL 执行中的低效环节（如临时表创建、磁盘 IO 频繁等）。

show profile 配置

查看当前配置

show variables like '%profiling%';

输出结果：

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |  # 表示支持 profile 功能
| profiling              | OFF   |  # 是否开启（默认关闭）
| profiling_history_size | 15    |  # 保留的历史 SQL 记录数（默认 15 条）
+------------------------+-------+

开启 show profile

set profiling = 1;  # 开启当前会话的 profile 功能（仅对当前会话有效）

show profile 基本使用

1. 执行目标 SQL 并查看历史记录

先执行需要分析的 SQL，再通过 show profiles 查看历史记录：

-- 执行目标 SQL（示例）
select * from user where age > 30 order by register_time;

-- 查看历史 SQL 记录（包含 query_id 和执行时间）
show profiles;

输出结果：

+----------+------------+----------------------------------------------------+
| Query_ID | Duration   | Query                                              |
+----------+------------+----------------------------------------------------+
|        1 | 0.00234578 | select * from user where age > 30 order by register_time |
+----------+------------+----------------------------------------------------+

• Query_ID：SQL 语句的唯一标识，用于后续分析。
• Duration：SQL 总执行时间（秒）。

2. 分析指定 SQL 的执行细节

通过 show profile [选项] for query [query_id] 查看具体阶段的资源消耗：

常用选项（指定分析维度）：

• all：显示所有资源消耗。
• cpu：显示 CPU 消耗（用户态 + 系统态）。
• block io：显示块 IO 操作（输入 / 输出次数）。
• memory：显示内存消耗（部分版本支持）。
• page faults：显示页面错误（内存分页相关）。

示例：

-- 分析 query_id=1 的 SQL 的 CPU 和 IO 消耗
show profile cpu, block io for query 1;

输出结果：

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000084 | 0.000067 |   0.000013 |            0 |             0 |
| checking permissions | 0.000014 | 0.000008 |   0.000005 |            0 |             0 |
| Opening tables       | 0.000039 | 0.000036 |   0.000003 |            0 |             0 |
| init                 | 0.000017 | 0.000015 |   0.000002 |            0 |             0 |
| System lock          | 0.000009 | 0.000008 |   0.000001 |            0 |             0 |
| optimizing           | 0.000004 | 0.000003 |   0.000001 |            0 |             0 |
| statistics           | 0.000011 | 0.000010 |   0.000001 |            0 |             0 |
| preparing            | 0.000009 | 0.000008 |   0.000001 |            0 |             0 |
| executing            | 0.000003 | 0.000002 |   0.000001 |            0 |             0 |
| Sending data         | 0.001839 | 0.001237 |   0.000601 |            0 |             0 |  # 耗时最长的阶段
| end                  | 0.000004 | 0.000003 |   0.000002 |            0 |             0 |
| query end            | 0.000007 | 0.000006 |   0.000001 |            0 |             0 |
| closing tables       | 0.000008 | 0.000007 |   0.000001 |            0 |             0 |
| freeing items        | 0.000016 | 0.000007 |   0.000009 |            0 |             0 |
| cleaning up          | 0.000015 | 0.000013 |   0.000001 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

关键阶段解读：

• Sending data：将数据返回给客户端的阶段，耗时较长可能因返回数据量大或网络延迟。
• Creating tmp table：创建临时表（若出现，可能因排序 / 分组无合适索引）。
• Copying to tmp table on disk：临时表从内存写入磁盘（性能较差，需优化）。
• Waiting for table lock：表锁等待（可能因 MyISAM 引擎或长事务）。

3. 通过 information_schema.PROFILING 表分析

information_schema.PROFILING 表存储了 profile 详细数据，支持复杂查询（如按耗时排序）：

-- 分析 query_id=1 的各阶段耗时占比
select 
  state, 
  sum(duration) as total_duration,  -- 总耗时
  round(100 * sum(duration) / (select sum(duration) from information_schema.PROFILING where query_id = 1), 2) as pct  -- 占比（%）
from information_schema.PROFILING 
where query_id = 1 
group by state 
order by total_duration desc;

输出结果可快速定位耗时占比最高的阶段（如 Sending data 占 90%）。

需重点优化的异常情况

若 show profile 中出现以下状态，通常意味着存在性能问题：

1. converting HEAP to MyISAM
   • 原因：内存临时表大小超过 tmp_table_size 或 max_heap_table_size，转为磁盘临时表。
   • 优化：增大临时表大小配置，或优化 SQL 避免临时表（如添加合适索引）。
2. Creating tmp table / Copying to tmp table on disk
   • 原因：排序、分组或连接操作需创建临时表，且内存不足转磁盘。
   • 优化：通过索引优化排序 / 分组（避免临时表），或增大 tmp_table_size。
3. Locked
   • 原因：表锁或行锁等待（如 MyISAM 表写操作阻塞读，或 InnoDB 行锁冲突）。
   • 优化：改用 InnoDB 引擎，减少锁持有时间，或调整事务隔离级别。
4. Sending data 耗时过长
   • 原因：返回数据量过大，或全表扫描导致数据读取缓慢。
   • 优化：限制返回字段（避免 SELECT *），添加索引减少扫描行数。

官方推荐：使用 performance_schema 替代

MySQL 5.7+ 中，show profile 已被标记为废弃，官方推荐使用 performance_schema 进行更全面的性能分析。

基本使用：

-- 确认 performance_schema 开启（默认开启）
select * from performance_schema.setup_actors;

-- 查询最近执行的 SQL 及其耗时（类似 show profiles）
select 
  event_id, 
  timer_wait / 1000000000 as duration_ms,  -- 耗时（毫秒）
  sql_text 
from performance_schema.events_statements_history_long 
order by event_id desc 
limit 10;

优势：

• 支持全局监控（非会话级），可分析所有连接的 SQL。
• 提供更细粒度的事件分类（如阶段事件、等待事件）。
• 可持久化配置，重启后生效