MySQL查询性能剖析

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

性能剖析一般分为两步:

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

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

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

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

剖析MySQL查询

慢查询

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

查看是否开启慢查询日志

mysql> show variables like '%show_query_log%';
Variable_nameValue
slow_query_logOFF
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_nameValue
long_query_time10.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_IDDurationQuery
   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;
StatusDuration
starting0.000060
Executing hook on transaction0.000004
starting0.000006
checking permissions0.000006
Opening tables0.000022
init0.000005
System lock0.000022
optimizing0.000005
statistics0.000011
preparing0.000011
executing0.000128
end0.000005
query end0.000004
waiting for handler commit0.000006
closing tables0.000006
freeing items0.000032
cleaning up0.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;
stateTotal_RPct_RCalls
executing0.00011435.291
starting0.00004814.862
freeing items0.00003711.461
logging slow query0.0000298.981
Opening tables0.0000226.811
cleaning up0.0000113.411
statistics0.0000103.101
preparing0.0000092.791
System lock0.0000072.171
waiting for handler commit0.0000061.861
closing tables0.0000051.551
checking permissions0.0000051.551
init0.0000051.551
optimizing0.0000041.241
Executing hook on transaction0.0000041.241
end0.0000041.241
query end0.0000030.931

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_nameValue
Created_tmp_disk_tables0
Created_tmp_files6
Created_tmp_tables36
Handler_commit52
Handler_delete0
Handler_discover0
Handler_external_lock728
Handler_mrr_init0
Handler_prepare0
Handler_read_first52
Handler_read_key130
Handler_read_last0
Handler_read_next221
Handler_read_prev0
Handler_read_rnd18
Handler_read_rnd_next2371
Handler_rollback0
Handler_savepoint0
Handler_savepoint_rollback0
Handler_update1
Handler_write605
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值