Handler_read_*的總結

在分析一個SQL的效能好壞時，除了執行計劃，另外一個常看的指標是"Handler_read_*"相關變數。

• Handler_read_key

• Handler_read_first

• Handler_read_last

• Handler_read_next

• Handler_read_prev

• Handler_read_rnd

• Handler_read_rnd_next

這七個變數，官方文件也有講解，但很多人看完後，還是一頭霧水。

下面結合具體的示例，來看看這七個變數的具體含義和區別。

 

Handler

首先說說什麼是handler。

handler是一個類，裡面按不同的功能模組定義了若干介面（具體可參考sql/handler.h）。其中，

DML操作相關的介面有：

• write_row()

• update_row()

• delete_row()

• delete_all_rows()

• start_bulk_insert()

• end_bulk_insert()

 

索引掃描相關的介面有：

• index_read_map()

• index_init()

• index_end()

• index_read_idx_map()

• index_next()

• index_prev()

• index_first()

• index_last()

• index_next_same()

• index_read_last_map()

• read_range_first()

• read_range_next()

其它相關介面可參考sql/handler.h，sql/handler.cc檔案。

 

如此設計，有兩點顯而易見的好處：

1.  Server層與儲存引擎層解耦。MySQL Server層在與儲存引擎層互動時，無需關心儲存引擎層的實現細節，直接呼叫handler物件的相關方法即可。

2.  降低了新引擎的引入門檻。如MyRocks。

 

測試資料

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(20) NOT NULL DEFAULT '',
  `pad` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> select * from t1 limit 6;
+----+---+--------+----------+
| id | k | c      | pad      |
+----+---+--------+----------+
|  1 | 1 | test_c | test_pad |
|  2 | 1 | test_c | test_pad |
|  3 | 1 | test_c | test_pad |
|  4 | 4 | test_c | test_pad |
|  5 | 5 | test_c | test_pad |
|  6 | 6 | test_c | test_pad |
+----+---+--------+----------+
6 rows in set (0.00 sec)

 

Handler_read_key

首先看看官檔的解釋

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.

簡而言之，即基於索引來定位記錄，該值越大，代表基於索引的查詢越多。

 

看看下面這個Demo。

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
+----+---+--------+----------+
| id | k | c      | pad      |
+----+---+--------+----------+
|  1 | 1 | test_c | test_pad |
+----+---+--------+----------+
1 row in set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| 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     |
+-----------------------+-------+
7 rows in set (0.00 sec)

測試中有兩點發現：

1.  無論是基於主鍵，還是二級索引進行等值查詢，Handler_read_key都會加1。

2.  對於二級索引，如果返回了N條記錄，Handler_read_next會相應加N。

 

Handler_read_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).

讀取索引的第一個值，該值越大，代表涉及索引全掃描的查詢越多。

但是，這並不意味著查詢利用到了索引，還需要結合其它的Handler_read_xxx來分析。

 

看看下面這個Demo

mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1 where c='0';
Empty set (0.10 sec)

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 | 101   |
+-----------------------+-------+
7 rows in set (0.01 sec)

 基於c來查詢，c不是索引，故走的是全表掃描（通過Handler_read_rnd_next的值和表的總行數也可判斷出來），但Handler_read_first和 Handler_read_key同樣也增加了。

 

下面再看看另外一個Demo

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2 where c='0';
Empty set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+
7 rows in set (0.00 sec)

t2和t1基本一樣，只不過t2是MyISAM表，此時只增加了Handler_read_rnd_next。

之所以會這樣，是因為t1是Innodb表，而Innodb是索引組織表，全表掃描實際上是基於主鍵來做的，所以Handler_read_first和Handler_read_key都會相應加1。

而t2是MyISAM表，MyISAM是堆表。

所以，單憑Handler_read_first很難評估查詢的優劣。

 

Handler_read_last

首先看看官檔的解釋

The number of requests to read the last key in an index. With ORDER BY, the server issues a first-key request followed by several next-key requests, whereas with ORDER BY DESC, the server issues a last-key request followed by several previous-key requests.

和Handler_read_first相反，是讀取索引的最後一個值。

該值增加基本上可以判定查詢中使用了基於索引的order by desc子句。

 

看看下面兩個Demo

1. 基於主鍵的正向排序

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 order by id limit 10;
...
10 rows in set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 9     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

可以看到，增加的還是Handler_read_first和Handler_read_nex t。

 

2. 基於主鍵的反向排序

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 order by id desc limit 10;
...
10 rows in set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 9     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

此時增加的是Handler_read_last和Handler_read_ prev。

 

Handler_read_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. 

根據索引的順序來讀取下一行的值，常用於基於索引的範圍掃描和order by limit子句中。

 

看看下面兩個Demo

1. 基於索引的範圍查詢

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where k < 2;
+----+---+--------+----------+
| id | k | c      | pad      |
+----+---+--------+----------+
|  1 | 1 | test_c | test_pad |
|  2 | 1 | test_c | test_pad |
|  3 | 1 | test_c | test_pad |
+----+---+--------+----------+
3 rows in set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 3     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

 

2. 基於索引的order by子句

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 force index(k) order by k limit 10;
...
10 rows in set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 9     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

注意：該查詢使用了hint，強制索引，如果沒用的話，會走全表掃描。

 

Handler_read_prev

首先看看官檔的解釋

The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC.

根據索引的順序來讀取上一行的值。一般用於基於索引的order by desc子句中。

具體示例可參考Handler_read_last。

 

Handler_read_rnd

首先看看官檔的解釋

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.

基於固定位置來讀取記錄。

 

關於固定位置的定義，不同的儲存引擎有不同的說法

For MyISAM, position really means a byte offset from the beginning of the file. For InnoDB, it means to read a row based on a primary key value.

 

下面看看Handler_read_rnd的使用場景

Usually Handler_read_rnd is called when a sort operation gathers a list of tuples and their “position” values, sorts the tuples by some criterion, and then traverses the sorted list, using the position to fetch each one. This is quite likely to result in retrieving rows from random points in the table, although that might not actually result in random IO if the data is all in memory. 

大意是對記錄基於某種標準進行排序，然後再根據它們的位置資訊來遍歷排序後的結果，這往往會導致表的隨機讀。

 

看看下面這個Demo

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1 order by rand() limit 10;
...
10 rows in set (0.00 sec)

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      | 10    |
| Handler_read_rnd_next | 202   |
+-----------------------+-------+
7 rows in set (0.00 sec)

這裡使用了order by rand()來生成隨機記錄。雖然只生成了10條記錄，但Handler_read_rnd_next卻呼叫了202次，比全表掃描還多，所以線上不建議使用order by rand()來生成隨機記錄。

 

Handler_read_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.

讀取下一行記錄的次數，常用於全表掃描中。 

 

實現原理

Handler_read_rnd_next is incremented when handler::rnd_next() is called. This is basically a cursor operation: read the "next" row in the table. The operation advances the cursor position so the next time it’s called, you get the next row.

 

看看下面兩個Demo

1.  全表掃描，帶有limit條件

mysql> flush status;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t1 limit 50;
...
50 rows in set (0.00 sec)

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 | 50    |
+-----------------------+-------+
7 rows in set (0.01 sec)

 

2.  全表掃描

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
...
100 rows in set (0.00 sec)

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 | 101   |
+-----------------------+-------+
7 rows in set (0.00 sec)

細心的童鞋可能會發現，limit 50時Handler_read_rnd_next為50，而不帶limit條件時，Handler_read_rnd_next卻為101，不是隻有100行資料麼？

實際上，在做全表掃描時，MySQL也並不知道表有多少行，它會不斷呼叫handler::rnd_next()函式，直至記錄返回完畢。

所以最後一次呼叫雖然為空，但畢竟呼叫了這個函式，故Handler_read_rnd_next需在表的總行數的基礎上加1。

 

綜合案例

最後，來個綜合一點的案例，看看兩表關聯查詢，各狀態值又是怎樣的呢？

在這裡，會涉及到MySQL的Nest Loop演算法。

mysql> flush status;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1 t_1,t1 t_2 where t_1.k=t_2.k;
+-----+-----+--------+----------+-----+-----+--------+----------+
| id  | k   | c      | pad      | id  | k   | c      | pad      |
+-----+-----+--------+----------+-----+-----+--------+----------+
|   1 |   1 | test_c | test_pad |   1 |   1 | test_c | test_pad |
|   1 |   1 | test_c | test_pad |   2 |   1 | test_c | test_pad |
|   1 |   1 | test_c | test_pad |   3 |   1 | test_c | test_pad |
|   2 |   1 | test_c | test_pad |   1 |   1 | test_c | test_pad |
|   2 |   1 | test_c | test_pad |   2 |   1 | test_c | test_pad |
|   2 |   1 | test_c | test_pad |   3 |   1 | test_c | test_pad |
|   3 |   1 | test_c | test_pad |   1 |   1 | test_c | test_pad |
|   3 |   1 | test_c | test_pad |   2 |   1 | test_c | test_pad |
|   3 |   1 | test_c | test_pad |   3 |   1 | test_c | test_pad |
|   4 |   4 | test_c | test_pad |   4 |   4 | test_c | test_pad |
|   5 |   5 | test_c | test_pad |   5 |   5 | test_c | test_pad |
|   6 |   6 | test_c | test_pad |   6 |   6 | test_c | test_pad |
...
106 rows in set (0.01 sec)

mysql> desc select * from t1 t_1,t1 t_2 where t_1.k=t_2.k;
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref            | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------+
|  1 | SIMPLE      | t_1   | ALL  | k             | NULL | NULL    | NULL           |  100 | NULL  |
|  1 | SIMPLE      | t_2   | ref  | k             | k    | 4       | slowtech.t_1.k |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------+
2 rows in set (0.00 sec)

mysql> show status like '%Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 101   |
| Handler_read_last     | 0     |
| Handler_read_next     | 106   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+
7 rows in set (0.00 sec)

通過執行計劃可以看出，該查詢的處理流程大致如下：

for each row in t_1  {
  for each row in t_2 where t_2.k = each_row.k {
         send to client
  }
}

接著，來分析下輸出結果

1.  對t_1表進行全表掃描，全表掃描對應的狀態值是Handler_read_first = 1，Handler_read_key = 1，Handle r_read_rnd_next = 101。

2.  因為t_1表有100行，所以會對t_2基於k值進行100次查詢，對應的，Handler_read_key = 100。

3.  觀察t1表k值的分佈，當id=1，2，3時，k的值均為1，其它id的k值不相同。所以一共會返回106條記錄，對應的，Handler_read_next = 106。

 

總結

1. Handler_read_key的值越大越好，代表基於索引的查詢較多。

2. Handler_read_first，Handler_read_last，Handler_read_next，Handler_read_prev都會利用索引。但查詢是否高效還需要結合其它Handler_read值來判斷。

3. Handler_read_rnd不宜過大。

4. Handler_read_rnd_next不宜過大，過大的話，代表全表掃描過多，要引起足夠的警惕。

 

參考資料

https://www.percona.com/blog/2010/06/15/what-does-handler_read_rnd-mean/

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Handler_read_first