MySQL优化工具之Show profiles分析SQL性能

使用show profiles分析SQL性能

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL刨析。该参数开启后，后续执行的SQL语句都将记录其资源开销，例如IO、上下文切换、CPU、Memory等等。根据这些开销进一步分析SQL瓶颈从而进行优化与调整。

方法/步骤

show profiles是5.0.37之后添加的，如果想使用此功能，确保版本在5.0.37之后。

1.查看数据库版本

SELECT VERSION()

2.查看是否打开profile功能

SHOW VARIABLES LIKE "PRO%";

profiling默认是OFF，需要开启这个功能。
3. 开启profile功能

SET PROFILING = 1;

再次执行SHOW VARIABLES LIKE "PRO%"；看到profiling变为ON。
4.测试
获取profile的帮助： help profile；

# 1. 执行一条语句
SELECT * FROM CAR ORDER BY ID DESC LIMIT 100;

# 2. 查看当前session中所有的profile
SHOW PROFILES;
SELECT * FROM information_schema.PROFILING;

# 3. 找到Query_ID并执行(显示该语句所有开销)
SHOW PROFILE ALL FOR QUERY 357[QUERY_ID];
SHOW PROFILE CPU,BLOCK IO FOR QUERY 357[QUERY_ID];
......

5. show profile 的常用查询参数

参数	描述
ALL	显示所有的开销信息
BLOCK IO	显示块IO开销
CONTENT SWITCHES	上下文切换开销
CPU	显示CPU开销信息
IPC	显示发送和接收开销信息
MEMORY	显示内存开销信息
PAGE FAULTS	显示页面错误开销信息
SOURCE	显示和Source_function, Source_file, Source_line相关的开销i信息
SWAPS	显示交换次数开销信息

6. 注意：

• converting HAMP to MyISAM： 查询结果太大，内存不够，数据往磁盘上放。
• Creating temp table：创建临时表。先拷贝数据到临时表，用完再删除临时表。
• Coping to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
• locked
  如果在show profile诊断结果中出现以上任意一条，则SQL语句需要优化。

# 查询Query_ID为2的SQL开销，且按最大耗用时间倒序排列
SELECT STATE, SUM(DURATION) AS Total_R,  
ROUND(  
     100 * SUM(DURATION) /  
        (SELECT SUM(DURATION)  
         FROM INFORMATION_SCHEMA.PROFILING  
         WHERE QUERY_ID = @query_id  
     ), 2) AS Pct_R,  
  COUNT(*) AS Calls,  
  SUM(DURATION) / COUNT(*) AS "R/Call"  
FROM INFORMATION_SCHEMA.PROFILING  
WHERE QUERY_ID = @query_id  
GROUP BY STATE  
ORDER BY Total_R DESC;  

相关内容

MySQL优化：https://blog.csdn.net/weixin_43932609/article/details/106156679

=========================================================

人生得意须尽欢，莫使金樽空对月！
__一个热爱说唱的程序员。

=========================================================