mysql中使用 SHOW PROFILE与 SHOW STATUS做单条语句剖析

使用 SHOW PROFILE

SHOW PROFILE命令是在mysql5.1以后引入的。来源于MySQL开源社区


默认是禁用的，可以通过服务器会话(链接)级别动态修改。


mysql> SET profiling = 1;


然后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。


SHOW PROFILE; 显示所有执行过的查询


SHOW PROFILE FOR QUERT 1; 剖析报告给出了查询执行的每个步骤及花费的时间。


看结果很难快速低确定哪个步骤话费的时间最多。因为输出是按照执行顺序排序，而不是按花费的时间排序。


那么通过查找INFORMATION_SCHEMA中对应的表，则可以按照需要格式化输出：


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;

使用 SHOW STATUS

SHOW STATUS只是一个计数器，可以显示如读索引的频繁程度等信息。但无法给出消耗了多少时间。结果中只有一条，指的是操作的时间(Innodb_row_lock_time),而且只能是全局级别的，所以还是无法测量会话级别的工作。


mysql> FLUSH STATUS;


mysql> SELECT * FROM sakila.nicer_but_slower_film_list;


mysql> SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%';


* 其中 Handler_read_rnd_next 没有用到索引索引的读操作。


需要主意的是 SHOW STATUS 本身也会创建一个临时表，而且也会用过句柄操作访问这个临时表，这回影响到SHOW STATUS结果中对应的数字， 而且不同的版本可能行为也不尽相同。


对比EXPLAIN， EXPLAIN是通过评估得到结果，而通过计数器则是实际的测量结果。 例如EXPLAIN无法得知临时表是否是磁盘表。这和内存临时表的性能差别是很大的。