Handler_read_*的总结

优化SQL，只懂执行计划？不行滴！

在分析一个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文件。

 

如此设计，有两点显而易见的好处：

 Server层与存储引擎层解耦。MySQL Server层在与存储引擎层交互时，无需关心存储引擎层的实现细节，直接调用handler对象的相关方法即可。

 降低了新引擎的引入门槛。如MyRocks。

 

测试数据

mysql> show create table t1G
*************************** 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)

测试中有两点发现：

  无论是基于主键，还是二级索引进行等值查询，Handler_read_key都会加1。

  对于二级索引，如果返回了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

基于主键的正向排序

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。

 

基于主键的反向排序

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

基于索引的范围查询

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)

 

基于索引的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

  全表扫描，带有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)

 

  全表扫描

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
  }
}

接着，来分析下输出结果

 对t_1表进行全表扫描，全表扫描对应的状态值是Handler_read_first = 1，Handler_read_key = 1，Handle r_read_rnd_next = 101。

 因为t_1表有100行，所以会对t_2基于k值进行100次查询，对应的，Handler_read_key = 100。

 观察t1表k值的分布，当id=1，2，3时，k的值均为1，其它id的k值不相同。所以一共会返回106条记录，对应的，Handler_read_next = 106。

 

总结

Handler_read_key的值越大越好，代表基于索引的查询较多。

Handler_read_first，Handler_read_last，Handler_read_next，Handler_read_prev都会利用索引。但查询是否高效还需要结合其它Handler_read值来判断。

Handler_read_rnd不宜过大。

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