Mysql version: 5.7.18 (Innodb)
I have a table with the following specification
Structure:
CREATE TABLE `dummybigint10` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT 'A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so.', `num1` bigint(20) DEFAULT NULL, `num2` bigint(20) DEFAULT NULL, `num3` bigint(20) DEFAULT NULL, `num4` bigint(20) DEFAULT NULL, `num5` bigint(20) DEFAULT NULL, `num6` bigint(20) DEFAULT NULL, `num7` bigint(20) DEFAULT NULL, `num8` bigint(20) DEFAULT NULL, `num9` bigint(20) DEFAULT NULL, `num10` bigint(20) DEFAULT NULL, PRIMARY KEY (`pk`) ) ENGINE=InnoDB AUTO_INCREMENT=2163184 DEFAULT CHARSET=utf8
Row count: 1049088
The B+ tree of this table has 19104 leaf nodes (out of which 19075 used) and 20 internal nodes (out of which 20 is used). [Thanks to innodb_ruby project]
I fired a query to cause a full table scan as follows:
select count(*) from dummybigint10 where num9 is not null;
I observed for the values in 'innodb_buffer_pool_reads from performance_schema.
Observed values are:
innodb_buffer_pool_reads: 98
Some resource available online say that, 'innodb_buffer_pool_reads' is the number of pages which are loaded by Innodb from disk, which in my case , a full table scan on the given table has to cause at least 19104 but the actual value reported by Innodb is 98. I think that resource is misleading the interpretation of innodb_buffer_pool_reads. Can any one explain what does innodb_buffer_pool_reads actually mean?
Best Answer