参考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数据库并选择好制定数据库实例之后,执行下面指令。
source before.sql
source后面空一格再写脚本的绝对路径,这条指令是bash的格式,但是可以在MySQL数据库的SQL命令行中运行。
(2)执行目标SQL语句。
(3)目标SQL语句执行完成之后,执行下面指令。
source after.sql
(4)效果图如下:
我们可以看到,图中左下方的表格包含了所需的物理读和逻辑读。
感谢为我提供帮助的朋友们,也感谢耐心看到这里的你(^_^)