MySQL数据库中如何监控（查看）某条SQL语句的物理读和逻辑读（附带SQL脚本实现方法）

最新推荐文章于 2024-08-15 01:26:58 发布

晨韵曦晔

最新推荐文章于 2024-08-15 01:26:58 发布

阅读量587

点赞数 5

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_62759414/article/details/137524375

版权

参考AI回答，融合个人理解，给大家分享一套鄙人的解决办法，如有不当之处，烦请各位技术大佬指正。

一、原理

1.总原理

记录该SQL语句执行前后系统表performance_schema.global_status 中对应参数的值，后着减去前者的差值即所需参数。

2.逻辑读、物理读和缓存访问之间的关系（MySQL中）

逻辑读+物理读=缓存访问总次数

3.系统表performance_schema.global_status 中对应参数

（1）Innodb_buffer_pool_reads：缓存不能满足的访问请求

CPU访问缓存，在缓存中未找到所需数据，然后缓存从磁盘中调入所需数据供CPU访问，即物理读。

（2）Innodb_buffer_pool_read_requests：缓存访问总次数

顾名思义，CPU执行语句时访问缓存的总次数。

二、具体操作

1.查看物理读

（1）用mysql -uroot -p连接MySQL数据库并选择好制定数据库实例之后，执行下面语句。

SELECT variable_value INTO @initial_total_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads';

这条语句将会记录（2）中的SQL语句执行前的Innodb_buffer_pool_reads参数的值。

（2）执行要监控（查看）的SQL语句。

（3）执行下面语句，记录（2）中SQL语句执行后的Innodb_buffer_pool_reads参数。

SELECT variable_value INTO @final_total_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads';

（4）计算差值，即可得到（2）中SQL语句的物理读。

SELECT (@final_physical_reads - @initial_physical_reads) AS 物理读;

2.查看逻辑读（可以与1同步进行）

（1）用mysql -uroot -p连接MySQL数据库并选择好制定数据库实例之后，执行下面语句。

SELECT variable_value INTO @initial_total_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests';

（2）执行目标SQL语句。

（3）执行下面语句，记录（2）中SQL语句执行后的Innodb_buffer_pool_reads参数。

SELECT variable_value INTO @final_total_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests';

（4）计算差值。

SELECT (@final_total_reads - @initial_total_reads) AS 缓存访问总次数;

根据原理，逻辑读=缓存访问总次数-物理读，我们就可以求出（2）中SQL语句的逻辑读了。

三、编写SQL脚本快速查看物理读、逻辑读和缓存访问总次数

1.编写before.sql，内容如下：

SELECT variable_value INTO @initial_total_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests';
SELECT variable_value INTO @initial_physical_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads';

2.编写after.sql，内容如下：

SELECT variable_value INTO @final_total_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests';
SELECT variable_value INTO @final_physical_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads';
SELECT (@final_physical_reads - @initial_physical_reads) INTO @physical_reads;
SELECT (@final_total_reads - @initial_total_reads) INTO @total_reads;
SELECT @total_reads AS 缓存访问总次数,@physical_reads AS 物理读,(@total_reads - @physical_reads) AS 逻辑读;

3.运行方法

（1）用mysql -uroot -p连接MySQL数据库并选择好制定数据库实例之后，执行下面指令。