Reputation: 11
I am monitoring the Innodb_buffer_pool_read_requests before and after executing my query on a MySQL, and I noticed that it increases by approximately 400, even though my query is very simple.
Here is my query:
SELECT *
FROM my_table t
WHERE t.a = 20000
LIMIT 1;
The column a has an index, and the query is confirmed to use the index correctly. Below is the status difference before and after executing the query:
┌──────────────────────────────────┬────────┐
│ (index) │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 400 │
│ Innodb_buffer_pool_reads │ 0 │
│ Handler_read_first │ 0 │
│ Handler_read_key │ 1 │
│ Handler_read_last │ 0 │
│ Handler_read_next │ 0 │
│ Handler_read_prev │ 0 │
│ Handler_read_rnd │ 0 │
│ Handler_read_rnd_next │ 0 │
└──────────────────────────────────┴────────┘
I don’t understand why Innodb_buffer_pool_read_requests increases by 400 when Handler_read_key only increases by 1. This behavior is consistent across multiple executions.
Can someone explain why this happens?
November 28 Update
Here is my table schema:
CREATE TABLE my_table
(
id INT UNSIGNED NOT NULL
PRIMARY KEY,
a INT UNSIGNED NOT NULL,
b INT UNSIGNED NOT NULL,
contents VARCHAR(255) NOT NULL,
deleted_at DATETIME NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE INDEX ix_a
ON my_table (a);
CREATE INDEX ix_b
ON my_table (b);
I am running MySQL version 8.0.40 in a Docker container. No configurations have been modified from the default. It is running on my local PC, and I am the only user.
Upvotes: 0
Views: 55