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

首先看看官档的解释

简而言之，即基于索引来定位记录，该值越大，代表基于索引的查询越多。

看看下面这个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

首先看看官档的解释

读取索引的第一个值，该值越大，代表涉及索引全扫描的查询越多。

但是，这并不意味着查询利用到了索引，还需要结合其它的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

首先看看官档的解释

和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

首先看看官档的解释

根据索引的顺序来读取下一行的值，常用于基于索引的范围扫描和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

首先看看官档的解释

根据索引的顺序来读取上一行的值。一般用于基于索引的order by desc子句中。

具体示例可参考Handler_read_last。

Handler_read_rnd

首先看看官档的解释

基于固定位置来读取记录。

关于固定位置的定义，不同的存储引擎有不同的说法

下面看看Handler_read_rnd的使用场景

大意是对记录基于某种标准进行排序，然后再根据它们的位置信息来遍历排序后的结果，这往往会导致表的随机读。

看看下面这个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

首先看看官档的解释

读取下一行记录的次数，常用于全表扫描中。 

实现原理

看看下面两个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