如何查看逻辑读和物理读块数

Question

在linux系统上的mysql中，如何分别查看一条语句执行过程中的物理读和逻辑读的块数？

Answer 1

引自免费微信小程序：皆我百晓生

要查看MySQL中一条SQL语句执行过程中的物理读和逻辑读的块数，您可以利用MySQL的性能分析工具，如EXPLAIN、SHOW PROFILE（适用于MySQL 5.0至5.6版本）或Performance Schema（适用于MySQL 5.6及更高版本）。这里提供两种方法供您参考：

方法一：使用EXPLAIN和SHOW STATUS命令

1. 逻辑读：逻辑读通常指查询过程中访问索引和数据页的数量。虽然EXPLAIN命令本身不直接显示逻辑读块数，但可以借助SHOW STATUS命令来间接获取相关信息。执行以下步骤：

   a. 在执行待分析的SQL语句前，记录Innodb_rows_read状态变量的值：

   SELECT variable_value INTO @initial_logical_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_rows_read';
   

   b. 执行SQL语句。

   c. 再次查询并记录Innodb_rows_read状态变量的值：

   SELECT variable_value INTO @final_logical_reads FROM performance_schema.global_status WHERE variable_name = 'Innodb_rows_read';
   

   d. 计算逻辑读块数差值：

   SELECT (@final_logical_reads - @initial_logical_reads) AS logical_read_blocks;
   

2. 物理读：物理读通常指从磁盘读取数据文件（包括数据页和索引页）的次数。同样，先记录初始值，执行SQL语句后再次查询并计算差值：

   a. 记录Innodb_buffer_pool_reads状态变量的初始值：

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

   b. 执行SQL语句。

   c. 记录Innodb_buffer_pool_reads状态变量的最终值：

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

   d. 计算物理读块数差值：

   SELECT (@final_physical_reads - @initial_physical_reads) AS physical_read_blocks;
   

方法二：使用Performance Schema（MySQL 5.6及以上版本）

性能模式（Performance Schema）提供了更详细的查询执行信息，包括逻辑读和物理读。以下是一个示例：

1. 确保performance_schema已启用，并启用相关事件监视器（通常在MySQL服务器配置文件中设置，或使用SQL语句动态修改）：

   SET GLOBAL performance_schema = ON;
   UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%events_statements_%';
   

2. 执行待分析的SQL语句。

3. 使用performance_schema.events_statements_summary_by_digest视图查询逻辑读和物理读信息：

   SELECT SUM(ROWS_EXAMINED) AS logical_read_blocks,
          SUM(NO_GOOD_INDEX_USED) + SUM(NO_INDEX_USED) AS full_table_scans, -- 这里估算物理读，可能与实际略有差异
          SUM(DIRECTORY_OPERATION) + SUM(FILE_OPEN) + SUM(FILE_READ) AS physical_read_operations
   FROM performance_schema.events_statements_summary_by_digest
   WHERE digest_text = '<将此处替换为您的SQL语句>';
   

   注意：上述查询返回的是估算的物理读操作次数，而非确切的块数。MySQL官方文档并未明确提供通过Performance Schema直接获取物理读块数的方法。在实际应用中，物理读块数可能与Innodb_buffer_pool_reads状态变量的变化值接近。

总结：以上两种方法可以帮助您在MySQL中查看一条SQL语句执行过程中的逻辑读和物理读情况。方法一利用SHOW STATUS和EXPLAIN命令，适合大多数MySQL版本；方法二利用Performance Schema，适用于MySQL 5.6及以上版本，能提供更详细的查询执行信息，但物理读块数估算可能不够精确。根据您的MySQL版本和需求选择合适的方法进行分析。