mysql - 了解MySQL读取数据的状态统计

Question

我检查了状态，然后执行了一些进程，然后再次检查了状态。正如您将在结果中看到的，data_read、pages_reads 以及其他一些变量没有变化……但是 rows_read 和 buffer_pool_read_requests 增加了。这怎么可能？

mysql> show status like '%read%';
+------------------------------------------+-----------+
| Variable_name                            | Value     |
+------------------------------------------+-----------+
| Com_ha_read                              | 0         |
| Delayed_insert_threads                   | 0         |
| Handler_read_first                       | 4         |
| Handler_read_key                         | 4         |
| Handler_read_last                        | 0         |
| Handler_read_next                        | 0         |
| Handler_read_prev                        | 0         |
| Handler_read_rnd                         | 0         |
| Handler_read_rnd_next                    | 733       |
| Innodb_buffer_pool_read_ahead_rnd        | 0         |
| Innodb_buffer_pool_read_ahead            | 0         |
| Innodb_buffer_pool_read_ahead_evicted    | 0         |
| Innodb_buffer_pool_read_requests         | 70346368  |
| Innodb_buffer_pool_reads                 | 147       |
| Innodb_data_pending_reads                | 0         |
| Innodb_data_read                         | 4591616   |
| Innodb_data_reads                        | 314       |
| Innodb_pages_read                        | 146       |
| Innodb_rows_read                         | 203648780 |
| Key_read_requests                        | 2         |
| Key_reads                                | 1         |
| Performance_schema_thread_classes_lost   | 0         |
| Performance_schema_thread_instances_lost | 0         |
| Slow_launch_threads                      | 0         |
| Threads_cached                           | 1         |
| Threads_connected                        | 9         |
| Threads_created                          | 54        |
| Threads_running                          | 1         |
+------------------------------------------+-----------+
28 rows in set (0.00 sec)

mysql> show status like '%read%';
+------------------------------------------+-----------+
| Variable_name                            | Value     |
+------------------------------------------+-----------+
| Com_ha_read                              | 0         |
| Delayed_insert_threads                   | 0         |
| Handler_read_first                       | 4         |
| Handler_read_key                         | 4         |
| Handler_read_last                        | 0         |
| Handler_read_next                        | 0         |
| Handler_read_prev                        | 0         |
| Handler_read_rnd                         | 0         |
| Handler_read_rnd_next                    | 733       |
| Innodb_buffer_pool_read_ahead_rnd        | 0         |
| Innodb_buffer_pool_read_ahead            | 0         |
| Innodb_buffer_pool_read_ahead_evicted    | 0         |
| Innodb_buffer_pool_read_requests         | 70358196  |
| Innodb_buffer_pool_reads                 | 147       |
| Innodb_data_pending_reads                | 0         |
| Innodb_data_read                         | 4591616   |
| Innodb_data_reads                        | 314       |
| Innodb_pages_read                        | 146       |
| Innodb_rows_read                         | 203652780 |
| Key_read_requests                        | 2         |
| Key_reads                                | 1         |
| Performance_schema_thread_classes_lost   | 0         |
| Performance_schema_thread_instances_lost | 0         |
| Slow_launch_threads                      | 0         |
| Threads_cached                           | 1         |
| Threads_connected                        | 9         |
| Threads_created                          | 54        |
| Threads_running                          | 1         |
+------------------------------------------+-----------+
28 rows in set (0.00 sec)

Answer 1

我使用“Handler%”更改来为我提供关于 SELECT 必须执行多少操作的良好线索:

FLUSH STATUS;
SELECT ...
SHOW VARIABLES LIKE 'Handler%';

InnoDB 在后台执行很多操作，因此当您不执行任何操作时，“Innodb%”值可能会发生变化:

写入内容被刷新到磁盘。

处理索引操作。

撤消日志被清除。

预读发生，尽管它的实用性值得怀疑。