本文为《高性能MySQL(第三版)》一书的摘要总结
性能剖析一般分为两步:
- 测量任务所花费时间
- 对结果进行统计和排序,将重要的任务排在前面
性能剖析工具的工作方式:
- 在任务开始时启动计时器
- 在任务结束时停止解释器
- 结束时间减开始时间得到 响应时间
任务剖析报告:每行记录一个任务,包括任务名称,任务的执行时间、任务的消耗时间、任务的平均执行时间以及该任务执行时间占全部时间的百分比。
剖析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 |