实际应用中,分析单条查询SQL性能,有3种方法:show status、show profile和慢查询日志。
一、使用SHOW PROFILE
1、默认是禁用的,在会话中开启
#开启profile
set profiling=1;
2、当一个查询到达服务器时,此工具会把分析信息记录到一张临时表,并且给查询赋予一个从1开始的整数标识符,可以通过show profiles查看。
3、上面只显示了时间上的精度,但是有些时候我们需要更多的信息那么,我们可以使用
show profile for query 1;来看具体信息
报告给出了每个步骤及其花费的时间,但是无法快度定位哪个步骤花费的时间最多,因为输出无法按花费时间来排序。
当然我们可以通过INFORMATION_SCHEMA中对应的表来输出我们想要的信息。比如
#要查询的SQL
SET @query_id = 1;
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;
就可以看到下面的格式
通过结果,可以清楚看到消耗时间最多的是“发送数据(Sending data)”,这里主要是因为我查询了一个有500W记录的表,并且id没有使用索引。
二、使用SHOW STATUS
show status 命令返回了一些计数器。既有服务器级别的全局计数器,也有基于某个连接的会话级别的计数器。不同的计数器可见范围不一样,全局的计数器也会出现在show status的结果中,容易被误认为是会话级别的。
show session status/show status 会话级别
show global status 全局级别
show status 结果只是计数器,无法给出消耗多少时间。
比如查看会话或者从服务器启动以来的增删改查的次数
show status like "com_insert";
show status like "com_delete";
show status like "com_update";
show status like "com_select";
下面清空一下,再来做一次原来的查询
这个数字说明这个查询,没有使用到索引,影响了500W条数据。
三、期待Performance Schema早日实现和普及吧!