MySQL单条SQL语句性能评估

MySQL单条SQL语句性能评估

-基于《High Performance MySQL》第五章Profiling a Single Query

很不幸，大部分MySQL指令对于评估一条SQL语句都不是很给力。虽然MySQL在这方面正在努力，不过目前为止，实际最好用的只有这两个命令：SHOW STATUS和SHOW PROFILE。

SHOW PROFILE命令

这个命令来自社区贡献，由Jeremy Cole提供，集成到了5.1以后的版本中。默认是没有开启这个命令支持的，需要用SET profiling = 1;来开启。开启以后，会输出任何命令的耗时和执行过程中的状态变化。每次执行命令的时候，评估数据会被记录到一个临时表中，这个表可以通过SHOW PROFILES;来看：

接着可以 SHOW PROFILE FOR QUERY 1；

评估表保存了SQL语句执行的每一步子过程耗时。这样直接从头到尾列出来不是很容易发现问题，可以用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;

得到：

这样更容易发现最耗时的操作并针对性得调整SQL语句。有些过程比如”Sending Data”，很难对应到具体如何优化，因为每一步操作基本都会涉及到发送数据，这种就只能先放放了。虽然我们得到了更细化的执行耗时，但其实这个命令不会告诉我们某个子工程为什么耗时。比如想要知道为什么”copying to tmp table”拷贝数据到临时表花了那么多时间，需要更进一步对这个子过程进行评估。

SHOW STATUS命令

SHOW STATUS返回各种计数，这些值有全局的(整个MySQL服务器)，也有Session的(本次连接)。而如果用SHOW GLOBAL STATUS返回的计数都是全局的。SHOW STATUS返回的计数中哪些是全局的，哪些是本Session的，需要参考MySQL手册。

SHOW STATUS不是真的评估工具，它仅仅返回MySQL的各种活动的计数。这些计数中只有一个Innodb_row_lock_time表示的时间，并且计数是全局的，所以也没啥用。

所以我们要结合一个SQL语句执行的前后计数来猜测哪些动作比较耗费时间。最重要的计数是handler counters 和 temporary file and table counters。让我们先把计数清零，看看这个例子：

> FLUSH STATUS;

> SELECT * FROM sakila.nicer_but_slower_film_list;

> SHOW STATUS WHERE Variable_name LIKE ‘Handler%’

OR Variable_name LIKE ‘Created%’;

大概可以看出，这个SQL执行过程中，用了3次临时表——其中两个表在磁盘上，做了很多没有索引加速的都操作(Handler_read_rnd_next)。从这个结果猜测，可能这个select查询了一个view，此view需要执行一个未加索引的join。

要注意SHOW STATUS操作本身自己就会出发一些计数增加，比如说写两次表格，所以前面一条SQL语句对应的临时表的操作计数应该在结果上减去2。

这得注意的是，SHOW STATUS得到的信息可能和EXPLAIN差不多，但EXPLAIN只是预测，并不是真是执行的结果。EXPLAIN不会告诉你临时表是在内存还是在磁盘这种重要信息。

耗时SQL语句日志

MySQL可以在日志中记录耗时的SQL操作，Percona Server(MySQL fork出来的另外开源实现)记录的更细。以SHOW PROFILE章节中的SQL语句为例，Log是这样的：

从中可以看出，这条语句执行过程中创建了3个临时表，其中两个表在磁盘上。

相比于SHOW STATUS和SHOW PROFILE，耗时SQL语句日志记录更详细的信息，特别是配合pt-query-digest日志分析工具，你可以从日志中方便找到对应的SQL语句的位置。Pt-query-digist可以为SQL语句输出统计头：

# Query 1: 0 QPS, 0x concurrency, ID 0xEE758C5E0D7EADEE at byte 3214 _____

然后用shell命令就能从日志文件中拉出对应的日志：

tail -c +3214 /path/to/query.log | head -n100

全局性能统计(Performance Schema)

全局性能统计在MySQL5.5中开始引入，虽然5.5版本中不支持SQL语句级别的统计，但还是能提供一些有用的信息，比如：

> SELECT event_name, count_star, sum_timer_wait

-> FROM events_waits_summary_global_by_event_name

-> ORDER BY sum_timer_wait DESC LIMIT 5;

得到：

5.6以后的版本，应该会更有用。