MySQL查询性能剖析

本文为《高性能MySQL（第三版）》一书的摘要总结

性能剖析一般分为两步：

1. 测量任务所花费时间
2. 对结果进行统计和排序，将重要的任务排在前面

性能剖析工具的工作方式：

1. 在任务开始时启动计时器
2. 在任务结束时停止解释器
3. 结束时间减开始时间得到 响应时间

任务剖析报告：每行记录一个任务，包括任务名称，任务的执行时间、任务的消耗时间、任务的平均执行时间以及该任务执行时间占全部时间的百分比。

剖析MySQL查询

慢查询

慢查询日志可以帮助我们剖析并找出代价高的查询。

查看是否开启慢查询日志

mysql> show variables like '%show_query_log%';

Variable_name	Value
slow_query_log	OFF
slow_query_log_file	/var/lib/mysql/localhost-slow.log

• 通过set开启记录慢查询日志（服务器重启后失效）：

mysql> set global slow_query_log=1;

查看哪些慢查询会被记录：

mysql> show variables like 'long_query_time';

Variable_name	Value
long_query_time	10.000000

上面的结果表示查询时间超过10秒的SQL会被就到慢查询日志中。
我们也可以通过set对该参数进行调整。

然后我们就可以使用pt-query-digest 这样的工具来剖析慢查询日志了，工具的安装方法可参考博客：https://www.cnblogs.com/zishengy/p/6852280.html

生成的剖析结果：

剖析单条查询

Show Profile

开启PROFILE：

mysql> set profiling = 1;

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

mysql>select * from employees e ;
mysql>show profiles;

在执行一个查询之后，通过show profiles来查看该查询的Query_ID:

Query_ID	Duration	Query

   1|  0.000094|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / SET SQL_SELECT_LIMIT=DEFAULT          |
   2| 0.0000795|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / SHOW WARNINGS                         |
   3|0.00011425|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / SELECT DATABASE()                     |
   4|   0.00034|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / select * from employees e LIMIT 0, 200|
   5|  0.000078|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / SHOW WARNINGS                         |
   6|0.00011425|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / SELECT DATABASE()                     |
   7|0.00009525|/* ApplicationName=DBeaver 7.1.0 - SQLEditor <Script.sql> * / SET SQL_SELECT_LIMIT=200              |

然后查看单条查询语句的具体剖析结果:

mysql>show profile for query 4;

Status	Duration
starting	0.000060
Executing hook on transaction	0.000004
starting	0.000006
checking permissions	0.000006
Opening tables	0.000022
init	0.000005
System lock	0.000022
optimizing	0.000005
statistics	0.000011
preparing	0.000011
executing	0.000128
end	0.000005
query end	0.000004
waiting for handler commit	0.000006
closing tables	0.000006
freeing items	0.000032
cleaning up	0.000011

该结果是按照查询执行顺序排序，而不是按花费时间排序的，我们可以直接查询INFORMATION_SCHAME中的表，则可以按照需要格式化输出：

set @query_id = 1;
SELECT
	p.state,
	sum(p.duration) as Total_R,
	ROUND(100 * sum(duration)/(select sum(DURATION) from information_schema.PROFILING p2 where QUERY_ID = @query_id), 2) as Pct_R,
	count(*) as Calls
from
	information_schema.PROFILING p
where
	QUERY_ID = @query_id
group by
	state
order by
	Total_R desc;

state	Total_R	Pct_R	Calls
executing	0.000114	35.29	1
starting	0.000048	14.86	2
freeing items	0.000037	11.46	1
logging slow query	0.000029	8.98	1
Opening tables	0.000022	6.81	1
cleaning up	0.000011	3.41	1
statistics	0.000010	3.10	1
preparing	0.000009	2.79	1
System lock	0.000007	2.17	1
waiting for handler commit	0.000006	1.86	1
closing tables	0.000005	1.55	1
checking permissions	0.000005	1.55	1
init	0.000005	1.55	1
optimizing	0.000004	1.24	1
Executing hook on transaction	0.000004	1.24	1
end	0.000004	1.24	1
query end	0.000003	0.93	1

SHOW STATUS

MySQL的SHOW STATUS返回一些 计数器 ，既有服务器级别的全局计数器，也有某个连接的会话级别的计数器。

SHOW STATUS无法提供基于时间的统计，但是对于一些执行完查询后观察某些计数器的值还是有帮助的。通过计数器的值，我们可以猜测哪些操作代价较高或者花费时间较长。最有用的计数器包括：句柄计数器、临时文件和表计数器等。

flush status;
select * from employees;
show status where variables_name like 'handler%' or variable_name like 'Created%';

其中fulsh status是清空计数器。

Variable_name	Value
Created_tmp_disk_tables	0
Created_tmp_files	6
Created_tmp_tables	36
Handler_commit	52
Handler_delete	0
Handler_discover	0
Handler_external_lock	728
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	52
Handler_read_key	130
Handler_read_last	0
Handler_read_next	221
Handler_read_prev	0
Handler_read_rnd	18
Handler_read_rnd_next	2371
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	1
Handler_write	605