mysql innodb_rows_read_MySQL:Innodb Handler_read_* 变量解释

本文为学习笔记，有误请指出。

本文使用源码版本：Percona 5.7.14

一、Handler_read_*值的实质

内部表示如下：

{"Handler_read_first", (char*) offsetof(STATUS_VAR, ha_read_first_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

{"Handler_read_key", (char*) offsetof(STATUS_VAR, ha_read_key_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

{"Handler_read_last", (char*) offsetof(STATUS_VAR, ha_read_last_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

{"Handler_read_next", (char*) offsetof(STATUS_VAR, ha_read_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

{"Handler_read_prev", (char*) offsetof(STATUS_VAR, ha_read_prev_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

{"Handler_read_rnd", (char*) offsetof(STATUS_VAR, ha_read_rnd_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

{"Handler_read_rnd_next", (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},

实际上这些变量都是MySQL层定义出来的，因为MySQL可以包含多个存储引擎。因此这些值如何增加需要在引擎层的接口中自行实现，也就是说各个引擎都有自己的实现，在MySQL层进行汇总，因此这些值不是某个引擎特有的，打个比方如果有Innodb和MyISAM引擎，那么这些值是两个引擎的总和。本文将以Innodb为主要学习对象进行解释。

二、各个值的解释

1、Handler_read_key

内部表示：ha_read_key_count

Innodb更改接口：ha_innobase::index_read

文档解释：The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

源码函数解释：Positions an index cursor to the index specified in the handle. Fetches the row if any.

作者解释：这个函数是访问索引的时候定位到值所在的位置用到的函数，因为必须要知道读取索引的开始位置才能向下访问。

2、Handler_read_next

内部表示：ha_read_next_count

Innodb更改接口：ha_innobase::index_next_same ha_innobase::index_next

文档解释：The number of requests to read the next row in key order. This value is incremented if you are

an index column with a range constraint or if you are doing an index scan.

源码函数解释：

index_next - Reads the next row from a cursor, which must have previously been positioned using index_read.

index_next_same - Reads the next row matching to the key value given as the parameter.

作者解释：访问索引的下一条数据封装的ha_innobase::general_fetch函数，index_next_same和index_next不同在于访问的方式不一样，比如范围range查询需要用到和索引全扫描也会用到index_next，而ref访问方式会使用index_next_same

3、Handler_read_first

内部表示：ha_read_first_count

Innodb更改接口：ha_innobase::index_first

文档解释：The number of times the first entry in an index was read. If this value is high, it suggests that the

is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1

is indexed

源码函数解释：Positions a cursor on the first record in an index and reads the corresponding row to buf.

作者解释：定位索引的第一条数据，实际上也是封装的ha_innobase::index_read 函数(如全表扫描/全索引扫描调用)

4、Handler_read_rnd_next

内部表示：ha_read_rnd_next_count

Innodb更改接口：ha_innobase::rnd_next

文档解释：The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries

not written to take advantage of the indexes you have.

源码函数解释：Reads the next row in a table scan (also used to read the FIRST row in a table scan).

作者解释：全表扫描访问下一条数据，实际上也是封装的ha_innobase::general_fetch，在访问之前会调用ha_innobase::index_first

5、Handler_read_rnd

内部表示：ha_read_rnd_count

Innodb更改接口：ha_innobase::rnd_pos

Memory更改接口：ha_heap::rnd_pos

文档解释：The number of requests to read a row based on a fixed position. This value is high if you are d