本文為學習筆記，有誤請指出。
本文使用原始碼版本：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
querying 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
server 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
are 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 doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.
作者解釋：這個狀態值在我測試期間只發現對臨時表做排序的時候會用到，而且是Memory引擎的，具體只能按照文件理解了。

6、其他

最後2個簡單說一下

Handler_read_prev
Innodb介面為 ha_innobase::index_prev 訪問索引的上一條資料，實際上也是封裝的 ha_innobase::general_fetch 函式，用於ORDER BY DESC 索引掃描避免排序，內部狀態值ha_read_prev_count增加。
Handler_read_last
Innodb介面為ha_innobase::index_last 訪問索引的最後一條資料作為定位，實際上也是封裝的 ha_innobase::index_read 函式，用於ORDER BY DESC 索引掃描避免排序，內部狀態值ha_read_last_count增加。

三、常用查詢測試

1、測試用例

mysql> show create table z1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| z1    | CREATE TABLE `z1` (  `a` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> show create table z10;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| z10   | CREATE TABLE `z10` (  `a` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,
  KEY `a_idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> select count(*) from z1;
+----------+
| count(*) |
+----------+
|    56415 |
+----------+1 row in set (5.27 sec)
mysql> select count(*) from z10;
+----------+
| count(*) |
+----------+
|       10 |
+----------+1 row in set (0.00 sec)

2、全表掃描

mysql> desc select * from z1;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | z1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 56650 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)
mysql> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'mysql> flush status;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from z1;56415 rows in set (4.05 sec)
mysql> pager;
Default pager wasn't set, using stdout.
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 56416 |
+-----------------------+-------+
7 rows in set (0.01 sec)

Handler_read_first增加1次用於初次定位，Handler_read_key增加1次，Handler_read_rnd_next增加掃描行數。我們前面說過因為ha_innobase::index_first也是封裝的 ha_innobase::index_read 因此都需要+1。

3、全索引掃描

mysql> desc select a from z1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | z1    | NULL       | index | NULL          | a    | 5       | NULL | 56650 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.12 sec)
mysql> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'mysql> select a from z1;56415 rows in set (4.57 sec)
mysql> pager
Default pager wasn't set, using stdout.
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 56415 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

Handler_read_first增加1次用於初次定位，Handler_read_key增加1次，Handler_read_next增加掃描行數用於連續訪問接下來的行。我們前面說過因為ha_innobase::index_first也是封裝的 ha_innobase::index_read 因此都需要+1。

4、索引ref訪問

我這裡因為是測試索引全是等於10的加上了force index

mysql>  desc select  * from z1 force index(a) where a=10;
+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | z1    | NULL       | ref  | a             | a    | 5       | const | 28325 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.01 sec)
mysql> flush status;
Query OK, 0 rows affected (0.13 sec)
mysql> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'mysql> select  * from z1 force index(a) where a=10;56414 rows in set (32.39 sec)
mysql> pager
Default pager wasn't set, using stdout.
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 56414 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.06 sec)

Handler_read_key增加1次這是用於初次定位，Handler_read_next增加掃描行數次數用於接下來的資料訪問。

5、索引range訪問

mysql> desc select  * from z1 force index(a) where a>9 and a<12;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | z1    | NULL       | range | a             | a    | 5       | NULL | 28325 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)
mysql>  pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'mysql> select  * from z1 force index(a) where a>9 and a<12;56414 rows in set (47.54 sec)
mysql> show status like 'Handler_read%';7 rows in set (0.03 sec)
mysql>  pager
Default pager wasn't set, using stdout.
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 56414 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.02 sec)